[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.
-----------------

 

[Hoàng Trọng Nghĩa]

Mặc dù đã qua các bước kiểm tra và thực hiện trên Excel 2003 và máy tính bàn WindowXP, thì tốc độ code vẫn đảm bảo 415 ms cho 3 lần thực hiện.

 

[siwtom]

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)

Sao máy của bạn nhanh thế? Máy của tôi là quãng 4200
------------
Theo tôi để đo tốc độ chỉ cần GetTickCount là đủ. Vì sao?
Một code dài thì bản thân code đó, chạy trên cùng một máy, ở thời điểm gần như nhau với trạng thái máy như nhau (các phần mềm đang chạy) đã cho các kết quả khác nhau rồi. Nếu code chạy mất vd. 800 ms và ta giả thiết là kết quả giữa các lần thử khác nhau là 25 phần nghìn thì có nghĩa là kết quả thực là 800 +- 20 ms.
Như thế sai số có thể là 20 ms. Vậy thì chả lý gì lại phải dùng QueryPerformanceFrequency + QueryPerformanceCounter. Với sai số cỡ đó thì dùng GetTickCount là đủ.

QueryPerformanceFrequency + QueryPerformanceCounter ta chỉ dùng khi mà phải đo tốc độ với độ chính xác cực lớn. Tức vd. đo tốc độ của code mà thời gian chạy nó là rất rất nhỏ. Nói cách khác dùng để đo khoảng thời gian cực ngắn.
------------
Ngay cả khi so sánh 2 code mà thời gian cho code 1 là 500 còn cho code 2 là 510 thì code nào nhanh hơn? Chạy cùng trên một máy, cùng "môi trường" nhưng chưa chắc đó là 2 môi trường như nhau. Vậy khi mà 2 kết quả khác nhau một lượng nằm trong giới hạn sai số do "dao động" trong system thì khó mà có thể phán code nào chạy nhanh hơn. Tất nhiên khi lượng khác nhau đó là lớn (tỉ lệ "lượng khác nhau" / thời gian gần đúng) thì có thể "yên tâm" đánh giá.
---
Vả lại nếu cần so sánh 2 gói đường mà chúng khác nhau ít nhất là cỡ 1 gam thì chả cần dùng cân có độ chính xác là 1 phần trăm (phần nghìn) gam

 

[Hoàng Trọng Nghĩa]

Tại sao máy laptop có cấu hình như hình dưới mà chạy chậm hơn máy tính bàn vậy các Anh Chị?

Laptop nó chậm hơn máy bàn 30-40% chứ không phải là ít đâu! Quá ngạc nhiên luôn!

Máy này:

So với máy bàn này:

 

[Lê Duy Thương]

ăn gian mà chạy như thế này được không bạn hiền. THÔNG THƯỜNG TÔI VIẾT CODE NÀY THÌ CHẠY KHOẢNG 1200--1500 . NHƯNG ĂN GIAN GIẢM 10 LẦN. MÀ CHẲNG ẢNH HƯỞNG ĐẾN KẾT QUẢ( TÀ ĐẠO)
ẸC ẸC

 

[Hoàng Trọng Nghĩa]

ăn gian mà chạy như thế này được không bạn hiền

Kinh quá! Chắc không dám nộp bài quá! Vì mình thấy chênh lệch giữa máy tính bàn và máy laptop khá xa! hihihihi.

 

[Lê Duy Thương]

kinh quá! Chắc không dám nộp bài quá! Vì mình thấy chênh lệch giữa máy tính bàn và máy laptop khá xa! Hihihihi.

tôi cung laptop nè . Nhưng khi test mấy lần đều không cho kết quả như nhau.chênh lêch khoảng 30%.
Nếu viết code tạo mới 1 pivot table kết hợp code tạo định dạng như báo cáo hoàn chỉnh là khoảng 1800--2400
NẾU ADD PIVOT VÀO THEO CÁCH CỦA SƯ PHỤ PTM0412 CODE REFRESH+... HET KHOẢNG 650---1100

CHỈ CÓ TÀ ĐẠO LÀ MỚI DƯỚI 100 THÔI.

 

[siwtom]

ăn gian mà chạy như thế này được không bạn hiền. THÔNG THƯỜNG TÔI VIẾT CODE NÀY THÌ CHẠY KHOẢNG 1200--1500 . NHƯNG ĂN GIAN GIẢM 10 LẦN. MÀ CHẲNG ẢNH HƯỞNG ĐẾN KẾT QUẢ( TÀ ĐẠO)
ẸC ẸC

Đã ăn gian thì tới bến luôn chứ sao lại nửa vời thế

Sub DoThoiGian()
Dim arr
    Dim T1@, T2@, Freq@, Overhead@
    
    arr = he
    Sheets("THNXT").[B12].Resize(UBound(arr), 12).Value = arr
    
    QueryPerformanceFrequency Freq
    QueryPerformanceCounter T1
    QueryPerformanceCounter T2
    Overhead = T2 - T1
    QueryPerformanceCounter T1
    QueryPerformanceCounter T2
    
    MsgBox "milliseconds(ms): " & (T2 - T1 - Overhead) / Freq * 1000
End Sub

Trong đó he là hàm làm "mọi việc" và trả về mảng kết quả.
---
96 đã là gì. Tôi chạy code trên vài lần, trong đó có lần cho kết quả 0.

 

[Lê Duy Thương]

đã ăn gian thì tới bến luôn chứ sao lại nửa vời thế

sub dothoigian()
dim arr
    dim t1@, t2@, freq@, overhead@
    
    arr = he
    sheets("thnxt").[b12].resize(ubound(arr), 12).value = arr
    
    queryperformancefrequency freq
    queryperformancecounter t1
    queryperformancecounter t2
    overhead = t2 - t1
    queryperformancecounter t1
    queryperformancecounter t2
    
    msgbox "milliseconds(ms): " & (t2 - t1 - overhead) / freq * 1000
end sub

trong đó he là hàm làm "mọi việc" và trả về mảng kết quả.

không anh ơi. Code này không đụng đến. Anh tuân đã comments rồi.
ĂN GIAN NHƯNG KẾT QUẢ PHẢI ĐÚNG CHỨ AI LẠI ĐI SỬA CODE DO THỜI GIAN
TRONG EXCEL KẾT QUẢ MỚI LÀ QUAN TRỌNG.GIỐNG NHƯ CHÚNG TA THAM GIA GIAO THÔNG THÔI. MÁY BAY,Ô TÔ, TÀU LỬA,XE MÁY .MỖI NGƯỜI CHỌN PHƯƠNG TIỆN ĐI KHÁC NHAU DO HOÀN CẢNH, NHƯNG CUỐI CÙNG CŨNG ĐẾN ĐÍCH
ẸC ẸC
Bài của em mà nộp nếu chấm điểm sẽ bị loại. Vì không thuộc tiêu chí nào..

THÔI TÔI VẪN NỘP BÀI.

 

[ptm0412]

Ăn gian vầy chắc được:

Sub taoso()
 msgbox "milliseconds(ms): " & 50.000001 & "he he"
End sub

Chụp hình đúng thời điểm và post lên

 

[Lê Duy Thương]

Em nộp bài rồi thầy.

 

[Nguyễn Duy Tuân]

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

1. Tất cả phải chạy trên cùng một máy tính
Phần cứng máy tính khác nhau, hệ điều hành khác nhau (Phiên bản Windows, 32, 64-bit), phiên bản 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.

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 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.

 

[Hoàng Trọng Nghĩa]

Cái bảng dữ liệu ở sheet kho như thế hình như vẫn sao sao ấy, không biết các kho vật tư như thế nào, riêng kho cảng thì nhập lô hàng nào, khi xuất thì thanh lý, cho nên làm tới đâu tính tới đó.

Trường hợp nhập CSDL thế này thì 2-3 năm kiểm tra lại, lấy số tồn của thời gian trước ngày TỪ NGÀY thì phải quét hết những năm trước luôn, khi dữ liệu xuất ra thì chắc chắn sẽ có mã hàng có TỒN CUỐI là 0. Với CSDL như vậy sẽ không được hay lắm thì phải.

Nếu lọc kiểu này thì phải thêm 1 vòng lặp nữa xét nếu mã nào có tồn cuối là 0 thì loại ra.

Đôi lời góp ý.

 

[Nguyễn Duy Tuân]

Cái bảng dữ liệu ở sheet kho như thế hình như vẫn sao sao ấy, không biết các kho vật tư như thế nào, riêng kho cảng thì nhập lô hàng nào, khi xuất thì thanh lý, cho nên làm tới đâu tính tới đó.

Trường hợp nhập CSDL thế này thì 2-3 năm kiểm tra lại, lấy số tồn của thời gian trước ngày TỪ NGÀY thì phải quét hết những năm trước luôn, khi dữ liệu xuất ra thì chắc chắn sẽ có mã hàng có TỒN CUỐI là 0. Với CSDL như vậy sẽ không được hay lắm thì phải.

Nếu lọc kiểu này thì phải thêm 1 vòng lặp nữa xét nếu mã nào có tồn cuối là 0 thì loại ra.

Đôi lời góp ý.

CSDL đơn giản để phục vụ cho làm bài tập này thôi. Còn để làm nghiệp chọn vẹn thì trong CSDL này phải thêm nhiều cột nữa. Anh Nghĩa cứ tạm thế nhé.

 

[ptm0412]

... chắc chắn sẽ có mã hàng có TỒN CUỐI là 0. Với CSDL như vậy sẽ không được hay lắm thì phải.

Nếu lọc kiểu này thì phải thêm 1 vòng lặp nữa xét nếu mã nào có tồn cuối là 0 thì loại ra.

Đôi lời góp ý.

Không phải tồn cuối bằng 0 thì loại ra, mà là đầu kỳ bằng 0, cả nhập cả xuất đều bằng 0 thì mới loại ra.
Nếu đầu kỳ = 0, nhập 100, xuất 100, tồn cuối = 0 mà loại ra thì sai.

 

[Nguyễn Duy Tuân]

Em nộp bài rồi thầy.

Mình đã nhận được bài của bạn Lê Duy Thưởng. Tốc độ code trong thủ tục "AN_GIAN" rất nhanh . Mình có chút góp ý mong bạn chỉnh thêm.

Trong sổ THNXT, nếu người dùng xóa từ dòng 12 đến một số dòng nào đó thì code sẽ báo lỗi. Có thể trong sheet THNXT bạn đã "iểm bùa" Pivot Table? Nếu là Pivot Table cũng là một giải pháp nhưng ta nên làm nhứ sau trong code để không bị "gian".
Viết code kiểm tra Pivot đã tồn tại chưa? Nếu chưa thì tạo nó. Trước khi chạy "Thực hiện" để đo tốc độ sheet THNXT phải chưa có Pivot.
Trong một chương trình ứng dụng, nếu mỗi báo cáo ta lưu cấu trúc Pivot dung lượng file sẽ nặng, vậy trước khi lệnh Save được thực hiện cần xóa Pivot (một trong các yếu tốt quan trọng làm cho file Excel chạy nhanh và dung lượng nhẹ là xóa liên kết, công thức). Vậy nên trong thực tế ứng dụng theo cách tạo pivot, lần đầu tạo sổ THNXT sẽ bị chậm vì phải tạo, còn lần sau (khi chưa lưu) chạy sẽ rất nhanh.

 

[Hoàng Trọng Nghĩa]

OK, đã gửi bài tham gia rồi Anh Tuân nhé! Hy vọng được bổ sung kiến thức từ mọi người qua cuộc thi này!

 

[hphuc0210]

Sao e chưa thấy thầy ndu96081631 và ThuNghi nạp bài vậy ta.Thầy ơi tranh thủ làm 1 bài nộp đi...

 

[ndu96081631]

Sao e chưa thấy thầy ndu96081631 và ThuNghi nạp bài vậy ta.Thầy ơi tranh thủ làm 1 bài nộp đi...

Bị... biết là có nộp bài cũng thua nên khỏi nộp cho nó chắc
Ẹc... Ẹc...

 

[Hoàng Trọng Nghĩa]

Sửa lại dùm em cái này anh Anh Tuân, copy mà không sửa lại:

    If m Then
        Dim ArrProcessing()
        ReDim ArrProcessing(1 To m, 1 To 12)
        For c = 1 To 12
            For r = 1 To m
                ArrProcessing(r, c) = ArrReport(GetRows(r), c)
            Next
        Next
        Call RowCorrect(m)
        Range("BasicName").Resize(m, 12) = [B][COLOR=#ff0000]ArrProcessing[/COLOR][/B]
    Else
        Call RowCorrect(n)
        Range("BasicName").Resize(n, 12) = ArrReport
    End If

Sửa lại chỗ màu đỏ anh nhé! Thanks.

 

[Nguyễn Duy Tuân]

OK, đã gửi bài tham gia rồi Anh Tuân nhé! Hy vọng được bổ sung kiến thức từ mọi người qua cuộc thi này!

Đã nhận được bài của anh Nghĩa. Tốc độ cũng thuộc hàng khủng đấy.