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

 

[Excel Công Cụ Tuyệt Vời Của Bạn]

@HTMT: Access có công cụ đo hiệu quả câu lệnh không nhỉ?

Đang chờ đợi bạn hướng dẫn sử dụng cái công cụ đó nè.

 

[VetMini]

Còn về vấn đề "không đảm bảo trật tự của mã hàng trong bảng mẫu báo cáo trước khi tổng hợp" như tôi đã nêu ra trước đây là đúng hay sai? Nếu đúng thì bạn có cách khắc phục chưa?

Ở đây nó sẽ tự sắp xếp theo thứ tự tăng dần, theo bạn thì phải sắp xếp như thế nào mới hợp lý.

Theo kỹ thuật tổng hợp thì bộ máy SQL sẽ dủng bảng index (đại khái gọi vậy, chứ là btree hay bảng băm, bảng gì thì là chuyện nội bộ của nó) để lọc GROUP. Và vì vậy kết quả sẽ ra theo thứ tự mã tăng dần (đây là hậu quả của kỹ thuật, chứ tiêu chuẩn không bắt buộc).

Khi copy recordset trở lại vào sheet báo cáo thì cũng theo thứ tự này. Như vậy mẫu báo cáo sẽ có khả băng bị xáo trộn.

Điểm rắc rối là theo chỗ tôi biết thì Access không có hàm đặt số thứ tự cho record.

(@HLMT: xin lỗi tôi chỉ giải thích phần trên rườm rà cho những người mới học SQL)

Nếu đề tài bắt phải giữ thứ tự của mẫu thì bắt buộc phải dựa vào cột A của bảng kết quả để sắp xếp. Cột này chứa số thứ tự của mã hàng.

 

[Cá ngừ F1]

Quá lâu em lôi lại chủ đề này, không phải để thi gì đâu ạ. Các cách dùng Dictionary, Collection của các cao thủ đã là quá tuyệt vời rồi, tốc độ phải gọi là khủng khiếp.

Vì thấy chủ để Power Query này hay quá, https://www.giaiphapexcel.com/diend...de-căn-bản-trong-kế-toán.153017/#post-1003719

Chế từ bài #20 của lão chết tiệt @ptm0412 , e lấy bài của a @Nguyễn Duy Tuân và dùng giải pháp Power Query để giải bài tập của a Tuân xem sao.
Kết quả để ở Sheet TongHop
Code gốc của Anh Tuân để sheet THXNT.

Rất mong các anh/chị vào góp ý thêm về cách làm, như em khởi tạo mấy Query thấy còn thô sơ quá.
Em cảm ơn

 

[ngocmaipretty]

Rất mong các anh/chị vào góp ý thêm về cách làm, như em khởi tạo mấy Query thấy còn thô sơ quá.

1. Tồn đầu kỳ tính thiếu: Dòng lệnh filter row chỉ tính nhập không tính xuất. Kết quả may là DateFrom là 1/8 chứ nếu 1/9 hay lớn hơn là sai. Từ ngày chuyển số tồn vào 31/7 đến ngày DateFrom chả lẽ chỉ nhập mà không xuất


2. Nhiều query quá: 3 query sau gộp thành một



3. Có thể gộp 4 dòng lệnh replace thành một

ReplaceNull= Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"HH_TonDK.SLNhap","HH_TonDK.STienNhap","HH_Xuat.SLNhap","HH_Xuat.STienNhap"}),
    //#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"HH_TonDK.SLNhap"}),
    //#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"HH_TonDK.STienNhap"}),
    //#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,0,Replacer.ReplaceValue,{"HH_Xuat.SLNhap"}),
    //#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,0,Replacer.ReplaceValue,{"HH_Xuat.STienNhap"}),

4. Đặt tên column dỏm: Query HH_Xuat mà tên cột là nhập

 

[Cá ngừ F1]

1. Tồn đầu kỳ tính thiếu: Dòng lệnh filter row chỉ tính nhập không tính xuất. Kết quả may là DateFrom là 1/8 chứ nếu 1/9 hay lớn hơn là sai. Từ ngày chuyển số tồn vào 31/7 đến ngày DateFrom chả lẽ chỉ nhập mà không xuất

2. Nhiều query quá: 3 query sau gộp thành một

View attachment 249922

3. Có thể gộp 4 dòng lệnh replace thành một

4. Đặt tên column dỏm: Query HH_Xuat mà tên cột là nhập

Em rất cảm ơn bác Phù Thủy,
Bám theo các góp ý của bác, e có sửa lại các Query.

Ngoài ra, e cũng muốn hỏi thêm 1 í nữa, sau khi click thực hiện thì lệnh Refresh Query được thực hiện.
Theo cấu trúc sau khi refresh xong thì Background Query mới chạy.
Mà dòng lệnh đo thời gian chỉ đo khi lệnh refresh thực hiện xong, mà e muốn đo cả thời gian Background Query thì cú pháp phải thay đổi như thế nào ạ?

Em cảm ơn,

 

[ngocmaipretty]

Em rất cảm ơn bác Phù Thủy,
Bám theo các góp ý của bác, e có sửa lại các Query.

Sửa xong nhưng lòi ra cái sai chết người (hên là đúng do dữ liệu ẹ quá)
1. Cái sai chết người:
- HHTonDK chỉ join 2 query nhập và xuất, không join bảng danh mục, nên không thấy những mặt hàng không nhập xuất
- 2 query nhập đầu kỳ và xuất trong kỳ lại join kiểu left outer, thì chỉ những mặt hàng có nhập có xuất và có nhập không xuất mới được lấy, mặt hàng không nhập có xuất không được lấy => đầu kỳ sai
2. Dữ liệu ẹ:
- suốt 1 năm rưỡi mà chỉ thấy nhập xuất ở 4 tháng, trong đó 3 tháng chỉ có mỗi tháng 1 ngày. Không test theo kiểu nối đuôi được (là test 1 khoảng thời gian bất kỳ, lấy cuối kỳ, test tiếp khoảng thời gian liền kề đối chiếu đầu kỳ này và cuối kỳ trước
- Suốt từ 1/8/2005 đến 31/7/2006 chỉ mua bán 4 mặt hàng, nguyên tháng 8/ 2006 thêm được 2 mặt hàng nữa. Đây chính là lý do dù có left join, right join, full join, inner join kiểu gì trước ngày 1 tháng 8/2006 cũng không sai kiểu thiếu mặt hàng.

e muốn đo cả thời gian Background Query thì cú pháp phải thay đổi như thế nào ạ?

Tỷ tỷ không biết rành VBA đâu, hỏi mấy lão cao thủ cao nhơn á. Có điều trong khi test thấy chậm lắm, đừng đo cho xấu hổ

 

[Cá ngừ F1]

Sửa xong nhưng lòi ra cái sai chết người (hên là đúng do dữ liệu ẹ quá)
1. Cái sai chết người:
- HHTonDK chỉ join 2 query nhập và xuất, không join bảng danh mục, nên không thấy những mặt hàng không nhập xuất
- 2 query nhập đầu kỳ và xuất trong kỳ lại join kiểu left outer, thì chỉ những mặt hàng có nhập có xuất và có nhập không xuất mới được lấy, mặt hàng không nhập có xuất không được lấy => đầu kỳ sai
2. Dữ liệu ẹ:
- suốt 1 năm rưỡi mà chỉ thấy nhập xuất ở 4 tháng, trong đó 3 tháng chỉ có mỗi tháng 1 ngày. Không test theo kiểu nối đuôi được (là test 1 khoảng thời gian bất kỳ, lấy cuối kỳ, test tiếp khoảng thời gian liền kề đối chiếu đầu kỳ này và cuối kỳ trước
- Suốt từ 1/8/2005 đến 31/7/2006 chỉ mua bán 4 mặt hàng, nguyên tháng 8/ 2006 thêm được 2 mặt hàng nữa. Đây chính là lý do dù có left join, right join, full join, inner join kiểu gì trước ngày 1 tháng 8/2006 cũng không sai kiểu thiếu mặt hàng.


Tỷ tỷ không biết rành VBA đâu, hỏi mấy lão cao thủ cao nhơn á. Có điều trong khi test thấy chậm lắm, đừng đo cho xấu hổ

Em cảm ơn Tỷ tỷ, bộ môn này mới quá, em mới thử làm. Ngôn ngữ nhìn cũng khó nhằn, thấy cứ join được là join đã. Có lẽ phải xin dữ liệu test chuẩn hơn để làm ạ.

 

[ptm0412]

Em cảm ơn Tỷ tỷ, bộ môn này mới quá, em mới thử làm. Ngôn ngữ nhìn cũng khó nhằn, thấy cứ join được là join đã. Có lẽ phải xin dữ liệu test chuẩn hơn để làm ạ.

Dữ liệu chuẩn đây nhé, 1 triệu dòng ngày tháng đầy đủ đã sắp xếp, mặt hàng 13 ngàn mua bán đều đặn
Dữ liệu mẫu 1 triệu dòng

 

[thuyyeu99]

Dữ liệu chuẩn đây nhé, 1 triệu dòng ngày tháng đầy đủ đã sắp xếp, mặt hàng 13 ngàn mua bán đều đặn
Dữ liệu mẫu 1 triệu dòng

Cho em xin nhé