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

 

[Nguyễn Duy Tuân]

Ở 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?

Nếu sắp xếp thì phải dùng lệnh VBA để làm, lệnh này bị tính vào thời gian chạy . "Dữ liệu nguồn - Không sửa" ý là không sửa thủ công, nếu sửa thì phải bằng code mà chỉ liên quan đến Sort mà thôi.

 

[Nguyễn Duy Tuân]

Hướng dẫn nghiệp vụ lập sổ THNXT - Tổng hợp nhập xuất tồn

Hướng dẫn nghiệp vụ lập sổ THNXT - Tổng hợp nhập xuất tồn

Các cột trong sheet KHO (dữ liệu nguồn)
SO_CT: số phiếu nhập, phiếu xuất. Không cần quan tâm
NGAY_CT: ngày của các phiếu khi nhập, xuất kho. Cột này được dùng để biết các phiếu thực hiện thời gian nào.
MA_KH: Không cần quan tâm
MA_NB: Không cần quan tâm
MA_NV: Không cần quan tâm
KHO: Không cần quan tâm
MA_VLSPHH: Chỉ ra mã vật tư, hàng hóa nào nhập hoặc xuất
SLG: Số lượng hàng nhập, xuất. Được dùng để tổng hợp số lượng trong báo cáo.
DON_GIA: Là đơn giá nhập, xuất cho một đơn vị hàng hóa. Không cần quan tâm
LOAI_PHIEU: Chỉ ra dòng/bản ghi này thuộc loại phiếu nhập(N) hay là xuất(X)
THANH_TIEN: Tính bằng SLG*DON_GIA. Cột này được dùng để tính tổng số tiền nhập hoặc xuất.

Cách tính toán để đưa vào sổ th nxt
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ị...

Ví dụ: Nhìn vào hình trên ta tính cho HH001 với khoảng thời gian từ NGAY1(02/08/2005) đến NGAY2(31/08/2005)
+ Lượng Tồn đầu. Tra trên dòng những ngày < 02/08/2005 với mặt hàng HH001 ta có
Nhập=4
Xuất=3
Lượng Tồn đầu = 4-3 = 1
+ Lượng Nhập, Xuất trong kỳ. Tra trên dòng có ngày trong khoảng [02/08/2005-31/08/2005] với mặt hàng HH001 ta có
Nhập=2+4=6
Xuất=3

+ Lượng tồn cuối = 1 + 6 - 3 = 4

Hy vọng bài hướng dẫn này chúng ta đều nắm được yêu cầu tính toán để bắt tay vào code.

 

[SA_DQ]

[THongbao]Ở 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?[/Thongbao]

Nghĩa thử viết câu lệnh sắp xếp (SX) CSDL ấy lại xem tốn bao nhiêu là thời gian?
Riêng chuyện SX này cầm thua ADO là cái chắc luôn!

Riêng mình thì suýt sấp bẫy 2 mã hàng 'BE' & 'BE1'

Còn chuyện này hỏi luôn thầy Tuan nè:
Ở cột [THANH_TIEN] sao có mặt hàng nhập do được cho hay fải xuất đem hối lộ hay sao mà không có thu/chi tiền làm vậy?

 

[Nguyễn Duy Tuân]

[THongbao]Ở 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?[/Thongbao]

Nghĩa thử viết câu lệnh sắp xếp (SX) CSDL ấy lại xem tốn bao nhiêu là thời gian?
Riêng chuyện SX này cầm thua ADO là cái chắc luôn!

Riêng mình thì suýt sấp bẫy 2 mã hàng 'BE' & 'BE1'

Còn chuyện này hỏi luôn thầy Tuan nè:
Ở cột [THANH_TIEN] sao có mặt hàng nhập do được cho hay fải xuất đem hối lộ hay sao mà không có thu/chi tiền làm vậy?

Dạ, đó là đồ được biếu đấy bác à

 

[VetMini]

Những công cụ đặc thù như ADO (sắp xếp và tổng hợp qua cổ máy Access?) chúng có "vùng mượt" (sweet spot) của chúng. Tuỳ theo mật độ dữ liệu mà tốc độ có tối ưu hay không. Chỉ dùng một nhóm dữ liệu thì không thể kết luận gì khi so sánh được.

Các kỹ thuật lập trình cũng có nhiều môn như vậy. Ví dụ SX (sort) có đến vài phương pháp, tuỳ theo số lượng và tình trạng dữ liệu mà hiệu quả của chúng khác nhau.

 

[Nguyễn Duy Tuân]

Đã 70 lượt download dữ liệu rồi mà chưa thấy thêm người trao đổi gì? Các Admin và một số Smod hình như cũng không quan tâm?

Theo đánh giá của tôi thì với ví dụ về lập sổ này chắc chắn chúng ta sẽ học được nhiều về VBA. Cùng kết quả nhưng sẽ có nhiều hướng giải quyết. Với diễn đàn GPE sẽ có thêm loạt bài chất lượng và sống động thực sự!

 

[Hai Lúa Miền Tây]

Đã 70 lượt download dữ liệu rồi mà chưa thấy thêm người trao đổi gì? Các Admin và một số Smod hình như cũng không quan tâm?

Theo đánh giá của tôi thì với ví dụ về lập sổ này chắc chắn chúng ta sẽ học được nhiều về VBA. Cùng kết quả nhưng sẽ có nhiều hướng giải quyết. Với diễn đàn GPE sẽ có thêm loạt bài chất lượng và sống động thực sự!

Có thể đang tìm phương án tối ưu đó anh Tuân, theo em thời hạn chỉ có 5 ngày kể từ ngày 10/02/2014 thì nó quá ngắn phải không anh?

 

[Nguyễn Duy Tuân]

Có thể đang tìm phương án tối ưu đó anh Tuân, theo em thời hạn chỉ có 5 ngày kể từ ngày 10/02/2014 thì nó quá ngắn phải không anh?

Cũng có thể với 5 ngày là hơn ngắn. Hy vọng các thành viên tập trung nghiên cứu để làm được ví dụ này.

 

[Hoàng Trọng Nghĩa]

Tốc độ ghê vậy ta?

Tốc độ vậy được chứ Anh Tuân?

 

[Nguyễn Duy Tuân]

Tốc độ vậy được chứ Anh Tuân?

Quá được luôn. Hàng cũng khủng đấy

Đang lo mình được giải khuyến khích mất thôi.

 

[Hoàng Trọng Nghĩa]

Quá được luôn. Hàng cũng khủng đấy

Đang lo mình được giải khuyến khích mất thôi.

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.

 

[Nguyễn Duy Tuân]

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.

Với topic này chúng ta hoan nghênh những người tham gia thi và cổ vũ, động viên .

 

[ChanhTQ@]

[ThoNgBao]Quá được luôn. Hàng cũng khủng đấy

Đang lo mình được giải khuyến khích mất thôi. [/ThoNgBao]

Té ra thầy này vừa ra đề, vừa có bài thi thố à?
???????????????????

 

[Hoàng Trọng Nghĩa]

[ThoNgBao]Quá được luôn. Hàng cũng khủng đấy

Đang lo mình được giải khuyến khích mất thôi. [/ThoNgBao]

Té ra thầy này vừa ra đề, vừa có bài thi thố à?
???????????????????

Bài thi có lẽ gửi ở bài đầu tiên với file rar đó bác Chanh, nhưng file này đã bị khóa rồi.

 

[Nguyễn Duy Tuân]

Bài thi có lẽ gửi ở bài đầu tiên với file rar đó bác Chanh, nhưng file này đã bị khóa rồi.

Có tham gia thi và nộp bài đầu tiên tại trang đầu (hết hạn nộp bài thì mới báo cáo mật khẩu). Nhưng theo đánh giá hiện nay thì có thể chiếu cố được giải khuyến khích thôi

 

[dhn46]

Các anh chị viết Code nhanh quá, em cũng xin góp 1 tay trong topic này hy vọng sẽ có thêm 1 phương án cho các thành viên mới của GPE.

 

[Lê Duy Thương]

Cũng có thể với 5 ngày là hơn ngắn. Hy vọng các thành viên tập trung nghiên cứu để làm được ví dụ này.

được phép ăn gian không anh tuân ?

 

[Hoàng Trọng Nghĩa]

được phép ăn gian không anh tuân ?

Cậu định dùng PivotTable à?

 

[Hoàng Trọng Nghĩa]

Code đã chỉnh sửa lại hợp lý.

Đây là hình ảnh test trên máy tính để bàn:

 

[Nguyễn Duy Tuân]

được phép ăn gian không anh tuân ?

Thế bạn cứ gửi cái gian đó lên xem nào . Cái gian đó biếu đâu có ích cho nhiều thành viên khác .