[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 · 10/2/14

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

Hai Lúa Miền Tây · 10/2/14

Em có thắc mắc chút xíu về dữ liệu:
Cột SO_CT 2 kí tự đầu là thể hiện việc nhập kho (NK) và xuất kho (XK), nhưng hình sau sẽ không phải như vậy???

Hai Lúa Miền Tây · 10/2/14

Hai Lúa Miền Tây đã viết:
Em có thắc mắc chút xíu về dữ liệu:
Cột SO_CT 2 kí tự đầu là thể hiện việc nhập kho (NK) và xuất kho (XK), nhưng hình sau sẽ không phải như vậy???

View attachment 116114

Nếu không dựa vào điều kiện 2 kí tự đầu ở cột SO_CT thì dựa vào cột LOAI_PHIEU + thêm 1 điều kiện nữa thì kết quả như sau:

SA_DQ · 10/2/14

Mình cho hiện kết quả tại [D1] là:
1039.252835

Nguyễn Duy Tuân · 10/2/14

Hai Lúa Miền Tây đã viết:
Em có thắc mắc chút xíu về dữ liệu:
Cột SO_CT 2 kí tự đầu là thể hiện việc nhập kho (NK) và xuất kho (XK), nhưng hình sau sẽ không phải như vậy???

View attachment 116114

Xin lỗi các anh chị vì số chứng từ và ký hiệu chứng từ không được khớp. Do là mình phải copy pase để tạo nhiều dữ liệu nên số lượng các mặt hàng nhập xuất nó không được đẹp nên đã tự ý đổi "LOẠI_PHIEU" mà không đổi số phiếu cho logic. Vậy các anh chị và các thành viên chỉ dựa vào cột "LOẠI_PHIEU" để xác định phiếu nhập hay phiếu xuất, còn SO_CT bỏ qua.

Bác HYen17 làm nhanh thật, tốc độ cũng nhanh đó.

Trân trọng.

SA_DQ · 10/2/14

Nhờ ăn gian đó: 973.707795

PHP:

Sub LapSo()
 Dim Rws As Long, J As Byte, W As Byte
 Dim SNh As Double, TNh As Double
 Dim Rng As Range, Sh As Worksheet, WF As Object
 ReDim sArr(1 To 12, 1 To 1):           ReDim dArr(1 To 12, 1 To 6)
     
5 Set Sh = ThisWorkbook.Worksheets("KHO")
 Rws = Sh.[b4].CurrentRegion.Rows.Count
7 Set Rng = Sh.[b3:B65535].Resize(, 10)
 sArr() = [C12].Resize(12).Value
9 Set WF = Application.WorksheetFunction
 For J = 1 To 12
11    Sh.[ac4].Value = sArr(J, 1)
    For W = 1 To 2
13       Sh.[ad2].Value = Choose(W, "N", "X")
        If W = 1 Then
15            SNh = WF.DSum(Rng, Sh.[H3], Sh.Range("AA1:AD2"))
            TNh = WF.DSum(Rng, Sh.[k3], Sh.Range("AA1:AD2"))
17            dArr(J, 3) = WF.DSum(Rng, Sh.[H3], Sh.Range("Ac1:Af2"))
            dArr(J, 4) = WF.DSum(Rng, Sh.[k3], Sh.Range("Ac1:Af2"))
19        Else
            dArr(J, 1) = SNh - WF.DSum(Rng, Sh.[H3], Sh.Range("AA1:AD2"))
21            dArr(J, 2) = TNh - WF.DSum(Rng, Sh.[k3], Sh.Range("AA1:AD2"))
            dArr(J, 5) = WF.DSum(Rng, Sh.[H3], Sh.Range("Ac1:Af2"))
23            dArr(J, 6) = WF.DSum(Rng, Sh.[k3], Sh.Range("Ac1:Af2"))
        End If
25    Next W
 Next J
27 [f12].Resize(12, 6).Value = dArr()
End Sub

Nguyễn Duy Tuân · 10/2/14

HYen17 đã viết:

PHP:

Sub LapSo()
 Dim Rws As Long, J As Byte, W As Byte
 Dim SNh As Double, TNh As Double
 Dim Rng As Range, Sh As Worksheet, WF As Object
 ReDim sArr(1 To 12, 1 To 1):           ReDim dArr(1 To 12, 1 To 6)
     
 Set Sh = ThisWorkbook.Worksheets("KHO")
 Rws = Sh.[b4].CurrentRegion.Rows.Count
 Set Rng = Sh.[b3:B65535].Resize(, 10)
 sArr() = [C12].Resize(12).Value
 Set WF = Application.WorksheetFunction
 For J = 1 To 12
    Sh.[ac4].Value = sArr(J, 1)
    For W = 1 To 2
        Sh.[ad2].Value = Choose(W, "N", "X")
        If W = 1 Then
            SNh = WF.DSum(Rng, Sh.[H3], Sh.Range("AA1:AD2"))
            TNh = WF.DSum(Rng, Sh.[k3], Sh.Range("AA1:AD2"))
            dArr(J, 3) = WF.DSum(Rng, Sh.[H3], Sh.Range("Ac1:Af2"))
            dArr(J, 4) = WF.DSum(Rng, Sh.[k3], Sh.Range("Ac1:Af2"))
        Else
            dArr(J, 1) = SNh - WF.DSum(Rng, Sh.[H3], Sh.Range("AA1:AD2"))
            dArr(J, 2) = TNh - WF.DSum(Rng, Sh.[k3], Sh.Range("AA1:AD2"))
            dArr(J, 5) = WF.DSum(Rng, Sh.[H3], Sh.Range("Ac1:Af2"))
            dArr(J, 6) = WF.DSum(Rng, Sh.[k3], Sh.Range("Ac1:Af2"))
        End If
    Next W
 Next J
 [f12].Resize(12, 6).Value = dArr()
End Sub

Ủa, code của bác chạy nhanh chư chớp, tốc độ của nó là "296.xxxx". Quá nhanh so với quy định

.
Bác kiểm tra giúp sao em copy vào file dữ liệu để chạy thì toàn ra số 0?

Hai Lúa Miền Tây · 10/2/14

Nguyễn Duy Tuân đã viết:
Ủa, code của bác chạy nhanh chư chớp, tốc độ của nó là "296.xxxx". Quá nhanh so với quy định .
Bác kiểm tra giúp sao em copy vào file dữ liệu để chạy thì toàn ra số 0?

Có tạo thêm vùng dữ liệu phụ ở ngoài đó anh Tuân ơi.

ChanhTQ@ · 10/2/14

Làm sao Tuân có thiết trí vùng [AA1:Af5 ] của trang thẻ kho, nó nè

| aa | ab | ac | ad | ae | af 1 |ngay_ct|ngay_ct|ma_vlsphh|loai_phieu|ngay_ct|ngay_ct
2 |=">"&aa4|="<"&ab4|="=" &ac4||=">="&ab4|="<="&ab5
3 ||
4 |=min(kho!b:b)-1|=ngay1| ;;;;;;;;;;;

|||
5 ||=ngay2||||

Nguyễn Duy Tuân · 10/2/14

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

Mong các thành viên có kinh nghiệm tham gia nhiều hơn để các thành viên khác được học hỏi.

Nguyễn Duy Tuân · 10/2/14

Hai Lúa Miền Tây đã viết:
Có tạo thêm vùng dữ liệu phụ ở ngoài đó anh Tuân ơi.

Đâu anh nhỉ. Bác HYến gửi code thôi mà?

Nguyễn Duy Tuân · 10/2/14

ChanhTQ@ đã viết:
| aa | ab | ac | ad | ae | af 1 |ngay_ct|ngay_ct|ma_vlsphh|loai_phieu|ngay_ct|ngay_ct
2 |=">"&aa4|="<"&ab4|="=" &ac4||=">="&ab4|="<="&ab5
3 ||
4 |=min(kho!b:b)-1|=ngay1||||
5 ||=ngay2||||

Anh làm vào file rồi gửi file kế quả em chạy xem được không?

Cảm ơn anh.

SA_DQ · 10/2/14

File đây, xin mời down nhanh nha!

)(&&@@

Nhấn cảm ơn giúp, một khi đã down file!

Hai Lúa Miền Tây · 10/2/14

Nguyễn Duy Tuân đã viết:
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ị...

Mong các thành viên có kinh nghiệm tham gia nhiều hơn để các thành viên khác được học hỏi.

Dùng ADO chỉ có thể ra kết quả có thời gian như trên thôi, em xem như thua cuộc, đang đợi học hỏi đây anh ơi.

Nguyễn Duy Tuân · 10/2/14

SA_DQ đã viết:
Nhấn cảm ơn giúp, một khi đã down file!

Bác làm tuyệt thật. Nhưng bác có thể nâng cấp thêm để tự động hóa 100%. Tức là người dùng chỉ cần nhấn nút "Thực hiện" là xong.
+ Bổ sung thêm code để tự điền công thức hoặc giá trị vào các cột B,C,D,E,L,M
+ Khi danh mục hàng có thể lên tới hàng trăm mã hàng, nhưng trong sổ KHO phát sinh với số mã có thể ít hơn. Vậy trong sổ THNXT chỉ hiện những mã có phát sinh chứng từ mà thôi.

Bác chuẩn bị giúp viết bài hướng dẫn cách làm sổ theo phương pháp của bác để thành viên được hiểu hơn nhé. Tuần sau em tổng hợp lại rồi sẽ đăng tất cả. Cảm ơn bác.

Mong các thành viên tiếp tục làm và gửi kết quả nhé. Mình đã thấy có 2 cách giải quyết vấn đề rồi đó. Chắc chắn là còn nữa.

Nguyễn Duy Tuân · 10/2/14

Hai Lúa Miền Tây đã viết:
Dùng ADO chỉ có thể ra kết quả có thời gian như trên thôi, em xem như thua cuộc, đang đợi học hỏi đây anh ơi.

ADO cũng được, nhưng so sánh về thời gian mình chưa biết ai hơn ai trong topic này. Anh cứ tìm cách làm đi, chỉnh sửa code cho dễ hiểu rồi gửi. Quan trọng các thành viên sẽ có tập hợp các phương pháp hay để học hỏi.

Mình xin đính chính lại. Khi tổng kết các bài thi sẽ đánh giá bài nào đạt tốc độ cao nhất. 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.

SA_DQ · 10/2/14

Tuyệt thật. Nhưng bác có thể nâng cấp thêm để tự động hóa 100%. Tức là người dùng chỉ cần nhấn nút "Thực hiện" là xong.

Người ta đang đánh theo cách du kích, thầy lại bảo đánh theo cách hiện đại thì có mà toi với chàng ADO còn gì!

Bác chuẩn bị giúp bài viết hướng dẫn cách làm sổ theo phương pháp của bác để thành viên được hiểu hơn nhé. Tuần sau em tổng hợp lại rồi sẽ đăng tất cả.

Sẵn lòng thôi, cách này giống như người sơ cấp VBA cũng mần được tuốt mà.

Có nhiều người chưa gởi bài vì lúng túng khâu Nhập-Xuất-Tồn là chuyên môn sâu, cần nghiền ngẫm đó thôi!
Mình có bài sớm vì trước đây đã hỗ trợ file i chang cho thành viên trong cộng đồng chúng ta đó thôi.

Chúc xuân vui vẻ & nhiều thành công!

Hoàng Trọng Nghĩa · 10/2/14

Ở sheet KHO, có dòng "Dữ liệu nguồn - Không sửa", vậy cho hỏi có được sắp xếp (sort) được không vậy?

Hoàng Trọng Nghĩa · 10/2/14

SA_DQ đã viết:
Nhấn cảm ơn giúp, một khi đã down file!

Em chưa có down vì có file đâu mà down, mà cũng nhấn cảm ơn được không bác Sa?

Hai Lúa Miền Tây đã viết:
Dùng ADO chỉ có thể ra kết quả có thời gian như trên thôi, em xem như thua cuộc, đang đợi học hỏi đây anh ơi.

Chớ vội, ở máy này tốc độ cho khác, ở máy khác cho tốc độ khác, vì thế khi kiểm tra chỉ cần sử dụng 1 máy mới biết thủ tục của ai nhanh ai chậm.

ChanhTQ@ · 10/2/14

Em chưa có down vì có file đâu mà down, mà cũng nhấn cảm ơn được không bác Sa?

Nhấn hay không tùy Nghĩa thôi, nhưng mình nghỉ rằng Nghĩa làm & nộp bài đi đã;
Sau đó đọc code với thiết kế bên trên thì mò tiếp đi, không được lười!

Chớ vội, ở máy này tốc độ cho khác, ở máy khác cho tốc độ khác, vì thế khi kiểm tra chỉ cần sử dụng 1 máy mới biết thủ tục của ai nhanh ai chậm.

Hoàn toàn đúng; Mà không biết excel 2003 hay E2010-E2007 có ảnh hưởng không ta?

Dù gì cũng đã làm chàng ADO rúng động là mình vui rồi!

Chúc xuân vui vẻ!

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

Nghị Hách

File đính kèm

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

/(hông là gì!

Nghị Hách

/(hông là gì!

Nghị Hách

❆❆❆❆❆❆❆❆

0901452không62

Nghị Hách

Nghị Hách

Nghị Hách

/(hông là gì!

❆❆❆❆❆❆❆❆

Nghị Hách

Nghị Hách

/(hông là gì!

Chuyên gia GPE

Chuyên gia GPE

0901452không62