[THI] Tạo sổ TH NXT với tốc độ nhanh nhất, dữ liệu 65,532 dòng

[Nguyễn Duy Tuân]

Cuộc thi tạo sổ tổ hợp nhập xuất tồn trong Excel tốc độ nhanh nhất

MỤC ĐÍCH
Trao đổi học tập để cùng nâng cao trình độ lập trình VBA về tối ưu code chạy nhanh và rõ ràng.

ĐỐI TƯỢNG THAM GIA
Là tất cả các thành viên GPE từ thành viên thường đến các Admin của GPE
Tôi cũng tham gia. Thực tế tôi đã viết code lâu rồi để phục vụ công việc quản lý kho, bản thân thấy chạy khá nhanh nhưng vẫn tin nó chưa phải hoàn hảo.
Nếu code của ai tối ưu nhất hoặc rõ ràng nhất sẽ trình bày code và giải thích cặn kẽ kỹ thuật để làm được ra nó trong topic này để mọi người tham khảo và học hỏi.

GIẢI THƯỞNG
Giải thưởng là cho tất cả thành viên của diễn đàn GPE được các bài học tốt về lập trình VBA trong Excel trong việc làm sổ sách tổng hợp.

THỜI GIAN DỰ THI, GỬI BÀI VÀ CÔNG BỐ
Dự thi từ ngày 10/02/2014.
Bài gửi chậm nhất là 12hAM ngày 15/02/2014.
Thời gian công bố kết quả đánh giá 14h 17/02/2014
Tất cả các bài dự thi, kết quả đánh giá sẽ được upload lên trang đầu của topic này.

Các bạn nén file đáp án rồi gửi bài vào email:
duytuan@bluesofts.net hoặc email của một thành viên BQT GPE (tôi bổ sung sau)
(Tôi sẽ là người nộp sớm nhất không sợ copy của người khác )

ĐỀ BÀI:
Tôi cung cấp tập tin dữ liệu với 65,532 dòng cùng module chứa các hàm và thủ tục đo tốc tộ, cấu trúc lệnh.
Bảng dữ liệu:

Nếu các bạn thắc mắc về phương pháp lập sổ tôi sẽ giải thích bài sau
Cấu trúc code:
[GPECODE=vb]Sub DoThoiGian()
Dim T1@, T2@, Freq@, Overhead@
QueryPerformanceFrequency Freq
QueryPerformanceCounter T1
QueryPerformanceCounter T2
Overhead = T2 - T1
QueryPerformanceCounter T1

'Thủ tuc của bạn

LapSo 'Thủ tuc của bạn phải làm

'Kết thúc chạy, đo thời gian thực hiện
QueryPerformanceCounter T2
'Debug.Print (T2 - T1 - Overhead) / Freq * 1000; "milliseconds(ms)"
MsgBox "milliseconds(ms): " & (T2 - T1 - Overhead) / Freq * 1000
End Sub[/GPECODE]


DoThoiGian là thủ tục mẹ được gán vào nút lệnh "Thực hiện" trên bảng tính. Nội dung trong thủ tục này bạn không được sửa. Bạn cần phải tạo thủ tục LapSo để lập sổ tổng hợp NXT.

[GPECODE=vb]Sub LapSo()
'Code của bạn để tạo ra sổ
End Sub[/GPECODE]

Kết quả thực hiện phải ra được sổ có cấu trúc và dữ liệu như sau

Lưu ý, sổ mẫu đã được định dạng vì vậy bạn không cần viết code để định dạng để giảm các yếu tốt ảnh hưởng tới tốc độ của code.

(Nếu bạn không biết lập trình VBA có thể lập công thức Excel thông thường. Tuy nhiên nó có thể được dùng để so sánh giữa lập trình VBA "thiện chiến" thế nào với cách lập công thức Excel thông thường mà thôi).

[TIP]Hướng dẫn tính toán
Các thành viên lưu ý. Sheet "Setting" có thông tin về ngày lập sổ: Từ ngày...đến ngày với các name NGAY1, NGAY2. Điều kiện để lập sổ phải dựa vào thời gian và Loại_phieu

Lượng Tồn đầu = lượng nhập với ngày < NGAY1 - lượng xuất với ngày < NGAY1
Lượng Nhập trong kỳ = lượng nhập với ngày >= NGAY1 và ngày <= NGAY2
Lượng Xuất trong kỳ = lượng xuất với ngày >= NGAY1 và ngày <= NGAY2
Lượng tồn cuối = Lượng Tồn đầu + Lượng Nhập trong kỳ - Lượng Xuất trong kỳ

Tương tự khi tính giá trị...[/TIP]

TIÊU CHÍ ĐÁNH GIÁ
Tìm ra các code đạt tốc độ nhanh nhất. Các bài làm cố gắng trình bày dễ hiểu và kèm comment trong code để giải thích.
Tất cả các bài với các phương pháp khác nhau cũng sẽ đăng lên để chúng ta học được nhiều phương pháp từ đó có thể vận dụng linh hoạt trong các việc khác.

Xin nói trước với các bạn là ta có thể đánh giá ở mức tương đối. Tất cả các code sẽ chạy trên một máy tính. Excel sẽ được khởi động lại với mỗi code mới, mỗi code được chạy 3 lần rồi lấy tốc độ trung bình. Tất cả các bài dự thi được upload lên đây để tất cả mọi người tham khảo.

Với tinh thần cầu thị, tạo sân chơi chung cho mọi người tôi rất mong chúng ta cùng tham gia. Mong các thành viên đừng e ngại về trình độ của mình thế này thế khác, cứ xác định tham gia để học để biết mình đã làm được gì và cần cải tiến cái gì về lập trình VBA.

-----------------
Đã có bài tổng hợp kết quả test và các file có mã nguồn của các tác giả gửi. Các thành viên xem bài #175 để download.
-----------------

 

[Ba Tê]

pivot table thì thời gian vẫn chậm hơn của bạn 1 chút
hình đây
để tôi xem có thể nhanh hơn được nữa không . Nếu được mới gửi bài

Cũng chú ý kết quả tổng tồn (Thành tiền)
Trên màn hình:88.579.860.386
Trong khi: 75.600.000+303.140.240.252-214.560.379.866 = 88.655.460.386

 

[Nguyễn Duy Tuân]

Anh Tuân ơi, cho hỏi rằng khi đã hết thời gian nộp bài, vậy tất cả các code đã nộp có được đưa lên đây để mọi người tham khảo hay không?

Như mình đã viết tại trang đầu, tất cả các code sẽ gửi lên hết, các tác giả giải thích cách làm để toàn thể thành viên được học. Đây chính là bữa tiệc VBA - Phần thưởng cho tất cả mọi người.

Xin nói rõ lại với các thành viên là tôi mở ra cuộc thi và tổng hợp, đánh giá các bài một cách khách quan cho mọi người. Bản thân tôi cũng là người dự thi, đồ rằng có thể mang giải khuyến khích về.

 

[SA_DQ]

Bài dự thi viết code ngắn nhất

/-(ầu như chỉ với 1 dòng lệnh!

​

 

[vodoi2x]

Đọc sơ sơ , thì thấy giải thưởng lạ quá, thật là khó hiểu???
Giải thưởng là cho tất cả .... - nghĩa là gì đây, tức là giải thưởng sẽ phát cho tất cả các thành viên...
Hy vọng chủ topic giải thích cho rõ???
(hỏi vì thấy đọc lạ, còn giơ tay chào thua với kiểu bài vba này)

Đúng là cuộc thi lạ, và giải thưởng cũng lạ, Nhưng như thế mới là GPE bạn ah, chắc là lính mới cóc hiểu gì (?)

----------
Topic của cuộc thi, bài dạng này quá giản đơn - bác bate, concogia (và quanghai, chanhTQ) viết suốt giúp thành viên hàng ngày - sao lại lấy ra thi??

Tuy thế thời gian so sánh cũng là vấn đề vì chênh nhau chắc không nhiều, và tốc độ máy khác nhau

Để tiện so sánh chúng ta hãy chạy cùng một sub "test thời gian" (theo cách đo như code của NDT) trong file kèm sau, hãy bấm nút test thời gian, chạy 10 lần, lấy trung bình tại Q5 --> và báo con số lên đây (mở màn là bác bate nhé) --> khi đó mới có con số so sánh --> suy luận tốc độ so với ng khác -> làm căn cứ khi báo con số này cùng con số thời gian của sub lapso (lưu ý: chạy cùng ngay thời điểm đó nhé, vì windows xử lý đa luồng, nên tùy thuộc vào thời điểm bạn đang chạy thì nó chạy cung nhiều chương trình khác bên cạnh excel nữa)

 

[Ba Tê]

Đúng là cuộc thi lạ, và giải thưởng cũng lạ, Nhưng như thế mới là GPE bạn ah, chắc là lính mới cóc hiểu gì (?)

----------
Topic của cuộc thi, bài dạng này quá giản đơn - bác bate, concogia (và quanghai, chanhTQ) viết suốt giúp thành viên hàng ngày - sao lại lấy ra thi??

Tuy thế thời gian so sánh cũng là vấn đề vì chênh nhau chắc không nhiều, và tốc độ máy khác nhau

Để tiện so sánh chúng ta hãy chạy cũng một thủ tục test thời gian (theo cách đo như code của NDT) trong file kèm sau, hãy bấm nút test thời gian, chạy 10 lần, lấy trung bình tại Q5 --> và báo con số lên đây (mở màn là bác bate nhé) --> khi đó mới có con số so sánh --> suy luận tốc độ so với ng khác -> làm căn cứ khi báo con số này cùng con số thời gian của sub lapso (lưu ý: chạy cùng ngay thời điểm đó nhé, vì windows xử lý đa luồng, nên tùy thuộc vào thời điểm bạn đang chạy thì nó chạy cung nhiều chương trình khác bên cạnh excel nữa)

Hổng hiểu, nhưng bấm thử 10 lần, O5=ROUND(AVERAGE(O6:O35);3)=1212.16
Bấm nút Test THời gian 10 lần Q5=843.655

 

[vodoi2x]

Hổng hiểu, nhưng bấm thử 10 lần, O5=ROUND(AVERAGE(O6:O35);3)=1212.16

Và code của bác thì chạy con số bao nhiêu??? BẤM "Test thời gian" mới đúng bác nhé --> Kết quả ở ô Q5 (còn O5 là đê chạy code lapso sau khi lắp code vào - dĩ nhiên người lập trình phải ghép vào các sheet data)

ví dụ code của bác chạy 350 và test thời gian (Q5) là 1212.16
code của 1 anh A khác chạy 330 và test thời gian 1000

--> quy đổi nếu code của bác bate chạy trên máy tính của anh A là =350*1000/1212.16 = 288.74 mls - con số này nhỏ hơn 330mls (sub lapso của anh A) ==> code của bác bate thắng , chúc mừng chúc mừng

Ví dụ thế

 

[Ba Tê]

Và code của bác thì chạy con số bao nhiêu???

Code tôi 568.1472
569.1907
567.4234

 

[Nguyễn Duy Tuân]

/-(ầu như chỉ với 1 dòng lệnh!

​

Bác vẫn đang cố tìm cách ăn gian nhá

 

[Nguyễn Duy Tuân]

Đúng là cuộc thi lạ, và giải thưởng cũng lạ, Nhưng như thế mới là GPE bạn ah, chắc là lính mới cóc hiểu gì (?)

----------
Topic của cuộc thi, bài dạng này quá giản đơn - bác bate, concogia (và quanghai, chanhTQ) viết suốt giúp thành viên hàng ngày - sao lại lấy ra thi??

Có lẽ vẫn chưa hiểu lắm sao anh hỏi vậy? Ai lấy cái gì của ai đi thi?
Bài thi này có phải đố về độ khó để tìm ra kết quả đâu. Các thành viên dùng mọi cách có thể để làm sao tốc độ thực thi code chạy nhanh nhất.

 

[vodoi2x]

Có lẽ vẫn chưa hiểu lắm sao anh hỏi vậy? Ai lấy cái gì của ai đi thi?
Bài thi này có phải đố về độ khó để tìm ra kết quả đâu. Các thành viên dùng mọi cách có thể để làm sao tốc độ thực thi code chạy nhanh nhất.

ý là , lấy dạng bài này đó

Nếu đó là ý muốn của chủ topic thì không có gì để bàn cả, chỉ thấy đó là vấn đề đã giải nhiều trên GPE, và so sánh tốc độ ở bài dạng này, dường như không khác biệt nhau nhiều lắm, vài phần % giây nên Không rõ đánh giá thế nào, như thế khó tạo ra được cách biệt, ý tôi chỉ thế thôi,

Và muốn nói thế, để chỉ ra các thành viên khác đừng lo việc khi ai đó nói là đã chạy 400mls hay ít hơn , tiếp tục thử theo cách của riêng đi- vì nó phụ thuộc vào máy tính và trạng thái máy tính chạy ghi đó (RAM, CPU , các chương trình đang chạy vv) - - con nếu máy tính khác nhau thì đã khác nhau tốc độ là cái chắc - nên có khi chạy 400mls ở máy này thì sang máy khác lại là 600mls .

Có lẽ bạn chủ topic nên công bố thời gian chạy thử nghiệm các bài nộp đã gửi đến -- như thế thì mới dễ so sánh hơn (vì thử trên cùng 1 môi trường gần như nhau). và người có bài nộp cũng biết cần hoàn thêm để cải thiện tốc độ nữa hay không (?)

Đôi lời bàn vậy thôi.

 

[Nguyễn Duy Tuân]

Để so sánh thời gian với nhau nên đảm bảo các yếu tố như sau:

1. Phải chạy trên cùng một máy tính
2. Tắt tất cả các ứng dụng đang chạy, các chương trình thường trú cũng tắt đi nếu không liên quan đến Windows để giảm những tác động đến Windows và Excel.
3. Một một bài thi phải được test theo quy trình như sau
b1. Tắt Excel (nếu đang mở)->Mở Excel -->đảm bảo môi trường "sạch"
b2. Mở file Excel cần đo thời gian. Hãy đợi một lúc đảm bảo Excel đã thực hiện các công việc của nó xong. Hãy nhấn CTRL+ALT+DEL để mở "Task Manager", trong tab "Processes" đảm bảo dòng có EXCEL.EXE, CPU và Memory đang ở con số ổn định (không thay đổi liên tục).
b3. Nhấn chạy thủ tục và ghi nhận thời gian thực hiện. Nên thực hiện tối thiểu 3 lần chạy để lấy 3 kết quả khác nhau rồi tính trung bình. Mỗi lần trước bấm nút "Thực hiện" hãy đảm bảo trong Process, CPU và Memory của dòng EXCEL.EXE đang ổn định.

Đến code của bài dự thi khác lại lập lại từ b1.

 

[ptm0412]

Topic này tôi thấy ngay khi mới tạo, nhưng chưa kịp xem, thậm chí còn không thấy chữ [THI], nên cứ tưởng Tuân giới thiệu 1 thành quả nào đó của mình.

Mãi đến khi đọc fb mới thấy Nghĩa khích bác tận tên tôi, vào đây lại thấy khích nữa:

Em nghĩ rất nhiều cao thủ chưa ra tay, tại thấy mỗi lần tham gia "phê bình" ai đó rất tích cực, hoặc lý thuyết rất "dữ dội", chắc những người đó sẽ làm nhanh lắm! Em tin rằng sẽ có người cho kết quả chừng một nửa thời gian của em.

Tính tôi vẫn thế, sai thì tôi phê, không chính xác thì tôi nói, copy paste mà nhận là tự dịch thì tôi vạch ra. Ăn nói hồ đồ thì tôi mắng. Vậy đấy.

Còn bài trong topic này tôi viết theo kiểu truyền thống, máy Core 2 Dual 2.9, kết quả tốc độ 537.77551409. Viết xong thì khuya rồi nên chưa sửa, chưa comment.

 

[Nguyễn Duy Tuân]

Có lẽ bạn chủ topic nên công bố thời gian chạy thử nghiệm các bài nộp đã gửi đến -- như thế thì mới dễ so sánh hơn (vì thử trên cùng 1 môi trường gần như nhau). và người có bài nộp cũng biết cần hoàn thêm để cải thiện tốc độ nữa hay không (?)

Đôi lời bàn vậy thôi.

Hiện nay em mới có trong tay 2 bài gửi chính thức. Nên để sau 12hAM ngày 15 sẽ upload lên để mọi người test với điều kiện test thống nhất. Từ giờ đến lúc đó mọi người vẫn đi tìm giải pháp cho chính mình để gửi bản cuối cùng.

Việc máy tính khác nhau, hệ điều hành khác nhau, Excel khác nhau, các ứng dụng đang chạy thường trú khác nhau, quy trình test khác nhau chính là các yếu tố làm cho kết quả đo tốc độ giữa các máy là không giống nhau với cùng một code. Vậy các thành viên cứ tự mình tìm các giải pháp rồi tự so sánh trên máy mình rồi cho ra sản phẩm cuối cùng.

Các cách làm giống nhau (cùng phái võ), chỉ khác nhau chút ít thì tốc độ đương nhiên chênh lệch ít (nếu không có thêm bí quyết). Các thành viên đã có một số cách khác hẳn nhau, tìm được bí quyết chắc chắn sẽ là khác nhau.

 

[vodoi2x]

Hổng hiểu, nhưng bấm thử 10 lần, O5=ROUND(AVERAGE(O6:O35);3)=1212.16
Bấm nút Test THời gian 10 lần Q5=843.655
View attachment 116191

Q5=843.655 ==> máy tính của bác khá mạnh, laptop của em giá trị thời gian này là 1168.259
Hẳn nào code bạn chạy có chưa đến nửa giây cho khối lượng dữ liệu lớn vậy (>65000 dòng)

 

[ptm0412]

Test với các khoảng thời gian khác nhau để đảm bảo rằng code chỉ thể hiện các dòng có đầu kỳ và có phát sinh trong kỳ, thì tôi thấy số lượng tính toán khác nhau sẽ cho thời gian khác nhau. Dù rằng vẫn dùng vòng lặp duyệt bằng đó dòng.







Riêng Pivot table, nếu chỉ dùng code refresh để test tốc độ thì có lẽ rất nhanh.

 

[Nguyễn Duy Tuân]

Test với các khoảng thời gian khác nhau để đảm bảo rằng code chỉ thể hiện các dòng có đầu kỳ và có phát sinh trong kỳ, thì tôi thấy số lượng tính toán khác nhau sẽ cho thời gian khác nhau. Dù rằng vẫn dùng vòng lặp duyệt bằng đó dòng.

Vì thế nên em mới viết bổ sung về cách test và vì sao thực hiện ít nhất 3 lần rồi lấy trung bình tốc độ.

Để so sánh thời gian với nhau nên đảm bảo các yếu tố như sau:

1. Phải chạy trên cùng một máy tính
2. Tắt tất cả các ứng dụng đang chạy, các chương trình thường trú cũng tắt đi nếu không liên quan đến Windows để giảm những tác động đến Windows và Excel.
3. Một một bài thi phải được test theo quy trình như sau
b1. Tắt Excel (nếu đang mở)->Mở Excel -->đảm bảo môi trường "sạch"
b2. Mở file Excel cần đo thời gian. Hãy đợi một lúc đảm bảo Excel đã thực hiện các công việc của nó xong. Hãy nhấn CTRL+ALT+DEL để mở "Task Manager", trong tab "Processes" đảm bảo dòng có EXCEL.EXE, CPU và Memory đang ở con số ổn định (không thay đổi liên tục).
b3. Nhấn chạy thủ tục và ghi nhận thời gian thực hiện. Nên thực hiện tối thiểu 3 lần chạy để lấy 3 kết quả khác nhau rồi tính trung bình. Mỗi lần trước bấm nút "Thực hiện" hãy đảm bảo trong Process, CPU và Memory của dòng EXCEL.EXE đang ổn định.

Đến code của bài dự thi khác lại lập lại từ b1.

 

[ptm0412]

Ý của tôi là test với khoảng thời gian (kỳ báo cáo) bên sheet setting khác nhau. trong 3 hình trên, tôi test với những khoảng thời gian (kỳ báo cáo):
- 01/08/05 - 31/12/06
- 01/09/05 - 31/12/06
- 01/09/05 - 31/12/05

Số vòng lặp như nhau, nhưng khối lượng tính toán khác nhau.

Cũng code đó, bây giờ test lại thì 461.84781

 

[Nguyễn Duy Tuân]

Anh không thay đổi gì chạy mỗi lần một con số khác mà.

 

[ptm0412]

Anh không thay đổi gì chạy mỗi lần một con số khác mà.

Có thay đổi bên sheet setting. Tuân xem kỹ tiêu đề báo cáo sẽ thấy kỳ báo cáo khác nhau. Vả lại, trong hình 3, chỉ thể hiện 4 dòng. Các mặt hàng khác không phát sinh trong năm 2015.

Theo thuật toán của tôi, trong vòng lặp chính (lặp 65 ngàn lần), nếu ngày < ngay1 thì tính 2 cột tồn đầu, nếu ngày < ngay2 thì tính 4 cột nhập xuất. Do đó số lượng dòng có ngày < ngay1 càng nhiều thì tính càng nhanh do chỉ tính 2 cột.

Hai cột tồn cuối tính trong vòng lặp nhỏ, chỉ lặp 12 lần, không đáng kể.

Chỉnh sửa 1 chút, giảm số lần tính toán, và thay phép nhân bằng phép cộng, có giảm 1 chút: 418.898



Cách thực hiện trong code:
1 Dictionary
1 Array nguồn
1 Array tạm
1 Array kết quả
1 vòng lặp 65 ngàn vòng

Array tạm cũng đã là kết quả của việc tính tổng, nhưng chạy thêm 1 vòng lặp 12 vòng để kiểm tra mặt hàng không có phát sinh thì loại ra.

 

[Hoàng Trọng Nghĩa]

Khi làm một chương trình thì người thực hiện phải hoạch định xem mình sẽ làm gì để chương trình chạy mượt mà hơn.

Thứ hai là phải cân nhắc giữa cái nhanh và việc lường trước các lỗi phát sinh.

Với bài tập này, thay vì tôi chọn code chạy 450 ms thì tôi sẽ chọn loại 550 ms.

Lý do:

1) Tôi phải kiểm tra dữ liệu nguồn, cụ thể là sheet KHO có trạng thái AutoFilter hay không, nếu có thì tôi sẽ bỏ chế độ này. Một cơ sở dữ liệu mà đang bị Filter thì có khả năng chúng ta không thể gán vào Array đầy đủ.

2) Kiểm tra xem dữ liệu trong sheet KHO đã được nhập dòng nào chưa, nếu chưa nhập thì thông báo.

3) Kiểm tra xem Từ ngày, Đến ngày đã được nhập vào hay chưa, nếu chưa nhập hoặc không phải là dạng ngày cũng phải thông báo.

4) Xóa dữ liệu cũ trên biểu mẫu, bởi khi dữ liệu sắp nhập vào ít hơn dữ liệu cũ trên biểu mãu sẽ bị trộn dữ liệu.

5) Trong biểu mẫu, tôi luôn bảo toàn một số hàng nhất định, trong trường hợp này, tôi bảo toàn số hàng là 15 dòng. Vì vậy, tôi phải kiểm tra trước xem biểu mẫu đó có đủ 15 dòng chưa, nếu đủ thì thôi, không đủ thì Insert thêm, còn nếu hơn thì Delete đi, làm sao cho Insert hoặc Delete phải bảo toàn 15 dòng. Mặt khác ta phải xem số hàng mà ta sắp gán vào biểu mẫu có nhiều hơn 15 dòng hay không, nếu nhiều hơn thì ta Insert thêm (vẫn đảm bảo cấu trúc định dạng), không để tình trạng dữ liệu tràn.

------------------------------------------------
Tôi vẫn dùng Dictionary để thực hiện code vì nó đảm bảo mã vật tư không trùng.