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

 

[ptm0412]

Khi đọc đề bài tôi đã có vài chỗ không hiểu nhưng do mọi người đang thi tôi không muốn hỏi vào vì không muốn mọi người mất tập trung.

Tôi không biết người ta ghi sổ sách như thế nào nhưng có vài điểm tôi không hiểu được.

1. Tồn đầu. Vẫn biết là Tuân đã cho khái niệm: Mọi Nhập (N) trước ngày 1 cộng với nhau, mọi Xuất (X) cũng cộng với nhau, và hiệu N - X sẽ là tồn đầu.
Theo tôi có vẻ phi thực tế.

2. ... Làm gì có chuyện thủ kho ngày 08-09-2005 đi làm và ở chỗ làm ghi vào sổ: Ngày 01-01-2007 nhập Honda 5 xe ... Và nếu thế thì chỉ cần đi từ dòng đầu cho tới ngày >= NGAY1 thì tính những dòng < NGAY1 ta có tồn đầu. Đồng thời có "dòng đầu" của kỳ báo cáo. Đi từ dòng cuối cùng đi lên loại tất cả các dòng trống (nếu có) và các dòng có ngày > NGAY2 ta sẽ có được "dòng cuối". Lúc này thì ta có thể không theo qui tắc

3. Theo tôi sổ sách được ghi theo trình tự thời gian, và theo trình tự các mã xuất hiện và mất đi. Ví dụ ngày xyz có hàng Nhập với mã chưa có thì lập tức mã đó được ghi vào sổ. Không có chuyện có mã trong sheet KHO mà lại không có trong sheet Danh Mục. Nếu sổ sách nghiêm chỉnh thì khi mặt hàng nào đó không còn thì mã tương ứng sẽ bị xóa. Khi đó thì mỗi mã trong sheet KHO sẽ có trong sheet Danh Muc, và ngược lại. Có đk này thì không phải xét từng mả một trong vòng lặp 65000. Vì lúc đó ta "nhắm mắt" mà cho Danh Mục vào Collection, đít thon, mảng.

Anh siwtom nói rất đúng. Tôi tham gia vì muốn góp 1 thuật toán mà tôi thường dùng, nhưng trong các bài viết tôi cũng nhắc đi nhắc lại 3 điều:
- data không có thứ tự ngày tháng nên phải duyệt đủ 65000 dòng
- Dự phòng có số dư đầu kỳ chứ không phải trước ngày 01/07/2005 kho rỗng không
- Dữ liệu phải kiểm tra mã ngay khi nhập vào, chứ không phải saucùng mới kiểm tra và thêm thêm. Mà thêm xong cũng chỉ có mã, không có tên và đơn vị tính.

Code của tôi viết cũng như anh nói: "nhắm mắt mà cho danh mục vào đít thon" và "nhắm mắt mà duyệt mảng không cần kiểm tra"

 

[Hoàng Trọng Nghĩa]

Một dữ liệu không được SORT hay sao các bác? Một người thích sort theo kiểu mã hàng/ tên hàng, một người sort theo ngày tháng, một người thích sort theo kiểu xyz nào đó, vậy trật tự có phải lộn xộn cả lên hay không? Muốn vậy thì các bác phải SORT lại theo ý mình và thực theo ý đồ của các bác chứ?

Đây là một dạng bài tập thôi, chứ CSDL thật có lẽ không phải là như vậy!

Theo tôi, thì cần phải có một cột tình trạng, nếu đã thanh lý lô hàng thì cột này ghi "thanh lý", sort cột này một cái là ra các hàng chưa thanh lý, thế lả tính toán thoải mái luôn.

Sau bao nhiêu năm chẳng hạn thì các hàng có thanh lý (theo kiểu FIFO) sẽ được dời đi (vào một CSDL nào đó chẳng hạn để lưu trữ). Cho nên luôn đảm bảo số việc truy xuất dữ liệu.

 

[ptm0412]

Một dữ liệu không được SORT hay sao các bác? Một người thích sort theo kiểu mã hàng/ tên hàng, một người sort theo ngày tháng, một người thích sort theo kiểu xyz nào đó, vậy trật tự có phải lộn xộn cả lên hay không? Muốn vậy thì các bác phải SORT lại theo ý mình và thực theo ý đồ của các bác chứ?

Đây là một dạng bài tập thôi, chứ CSDL thật có lẽ không phải là như vậy!

Theo tôi, thì cần phải có một cột tình trạng, nếu đã thanh lý lô hàng thì cột này ghi "thanh lý", sort cột này một cái là ra các hàng chưa thanh lý, thế lả tính toán thoải mái luôn.

Sau bao nhiêu năm chẳng hạn thì các hàng có thanh lý (theo kiểu FIFO) sẽ được dời đi (vào một CSDL nào đó chẳng hạn để lưu trữ). Cho nên luôn đảm bảo số việc truy xuất dữ liệu.

Nghĩa nên nhớ rằng Tuân không cho sort. Và nên đọc để biết rằng trong 1 bài nào đó Tuân nói dữ liệu dùng để ra đề đã được giả lập bằng cách copy nên nó thế, chứ không phải ban đầu nó thế. Phải chấp nhận để thi.
Dữ liệu gốc không ai sort tùm lum cả. Đúng tiêu chuẩn là nhập hàng ngày theo thứ tự ngày tháng. Muốn xem kiểu gì thì mở báo cáo đó lên xem: Filter, group, ... sẵn thành báo cáo đẹp đẽ luôn.

Còn vụ "thanh lý" thì tôi cũng nói rồi. Sau 1 năm người ta xóa bỏ hoặc đánh dấu những mặt hàng không còn sử dụng ra khỏi danh mục. Đó là dữ liệu "rác". Cũng chính vì thế nên người ta không nhập sẵn 10.000 mã chưa dùng đến vào danh mục, vì không xài tức là rác. Khi được chào hàng hoặc thông báo mã hàng mới, người ta sẽ lưu ở chỗ khác theo dạng catalogue để tham khảo, khi nào thực sự dùng mới add, dùng bao nhiêu, add bấy nhiêu.

Nghĩa còn hiểu sai về FIFO. Dù dùng FIFO hay dùng bất kỳ phương pháp tính giá nào, thì vẫn có những mặt hàng dùng mãi mãi, và cũng có mặt hàng chỉ dùng 1 thời gian rồi không dùng nữa. người ta loại mặt hàng không dùng, chứ không loại mặt hàng cũ nhất.

 

[siwtom]

Nghĩa nên nhớ rằng Tuân không cho sort. Và nên đọc để biết rằng trong 1 bài nào đó Tuân nói dữ liệu dùng để ra đề đã được giả lập bằng cách copy nên nó thế, chứ không phải ban đầu nó thế. Phải chấp nhận để thi.
Dữ liệu gốc không ai sort tùm lum cả. Đúng tiêu chuẩn là nhập hàng ngày theo thứ tự ngày tháng. Muốn xem kiểu gì thì mở báo cáo đó lên xem: Filter, group, ... sẵn thành báo cáo đẹp đẽ luôn.

Còn vụ "thanh lý" thì tôi cũng nói rồi. Sau 1 năm người ta xóa bỏ hoặc đánh dấu những mặt hàng không còn sử dụng ra khỏi danh mục. Đó là dữ liệu "rác". Cũng chính vì thế nên người ta không nhập sẵn 10.000 mã chưa dùng đến vào danh mục, vì không xài tức là rác. Khi được chào hàng hoặc thông báo mã hàng mới, người ta sẽ lưu ở chỗ khác theo dạng catalogue để tham khảo, khi nào thực sự dùng mới add, dùng bao nhiêu, add bấy nhiêu.

Nghĩa còn hiểu sai về FIFO. Dù dùng FIFO hay dùng bất kỳ phương pháp tính giá nào, thì vẫn có những mặt hàng dùng mãi mãi, và cũng có mặt hàng chỉ dùng 1 thời gian rồi không dùng nữa. người ta loại mặt hàng không dùng, chứ không loại mặt hàng cũ nhất.

Tôi cũng nghĩ là dữ liệu gốc được ghi theo trình tự thời gian và không có sort gì cả. Mọi nhu cầu: sort theo mã hàng, theo khách hàng (?), trích ra bảng con chỉ có một số cột, báo cáo ..., tất cả được làm ở sheet mới. Những cái đó có thể chỉ cần thiết nhất thời và sau đó có thể xóa đi. Riêng dữ liệu nguồn thì bất di bất dịch.

 

[Lê Duy Thương]

Máy chú tốt, test dùm code tôi mới sửa lại theo cách kết hợp giữa thuật toán lão chết tiệt làm, theo collection của vodoi2x và mảng trong mảng của tớ xem thời gian có khá hơn không nhé! Máy tớ cứ như rùa bò ấy!

đây là kết quả test code của hoang trọng nghĩa

 

[Hoàng Trọng Nghĩa]

đây là kết quả test code của hoang trọng nghĩa

Cám ơn đồng chí, máy mình ẹ quá, vậy mà chạy tới 850 ms luôn đấy! Dù là một số bẫy lỗi và thêm hàng tổng cộng mà code chạy như thế là tạm chấp nhận. Thôi, tới đây mình ngưng đề tài này nhé!

Chúc mọi người có một đêm ngon giấc!

 

[Nguyễn Duy Tuân]

Tôi không biết người ta ghi sổ sách như thế nào nhưng có vài điểm tôi không hiểu được.

Trích bài #22

1. Tồn đầu. Vẫn biết là Tuân đã cho khái niệm: Mọi Nhập (N) trước ngày 1 cộng với nhau, mọi Xuất (X) cũng cộng với nhau, và hiệu N - X sẽ là tồn đầu.
Theo tôi có vẻ phi thực tế. Vì nếu thế, theo vd. ta thấy ngày 31-07-2005 trong kho không có mặt hàng HH001. Tươnbg tự với các mặt hàng khác. Tức ở thời điểm ngày 31-07-2005 thì kho trống rỗng. Thực tế thì làm gì có kho nào như thế.
Theo cái lôgíc của tôi thì: Nếu tôi làm báo cáo cho khoảng NGAY1 - NGAY2 thì cũng có nghĩa là tôi phải biết được ở thời điểm (NGAY1 - 1) thì trong kho mỗi mặt hàng có bao nhiêu. Giả sử có k mặt hàng trong kho với số lượng là n1, n2, ..., nk thì theo tôi ta sẽ phải tạo vùng dữ liệu kho mà k dòng đầu có ở cột F (tồn đầu) các giá trị n1, n2, ..., nk, tiếp theo là những dòng nhập - tồn trong khoảng NGAY1 - NGAY2.
Nói cách khác ta coi lượng hàng hóa trong kho ở ngày (NGAY1 - 1) là lượng đầu kỳ cho khoảng báo cáo (NGAY1 - NGAY2), tức ta coi n1, n2, ..., nk là lượng hàng hóa mà ta "nhập" từ "kho cũ" sang "kho mới". Và mỗi dòng trong k dòng kể trên có trong cột J ký tự N. Làm gì có chuyện vừa xuất vừa nhập? Ngày 31-07-2005 có bao nhiêu thì ta Nhập (N) vào "kho mới" (trong tưởng tượng thôi) cho báo cáo mới. Thế thôi.

Công thức em đã gợi ý cho mọi người như sau:
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

Anh đang thắc mắc phần tồn đầu nên em giải thích thêm.
- Trong thực tế kiểm kho, số tồn đầu kỳ cụ thể luôn là nhập (LOAI_PHIEU="N"). Nói tồn đầu thì chỉ là tồn đầu và nhập không có xuất - Ý anh nói đúng.
- Với người làm nghiệp vụ kho. Báo cáo nộp định kỳ, ví dụ nộp cuối năm thì tồn đầu sẽ cố định ngày < 1/1/xx, và chắc chắn chỉ có loại nghiệp vụ là nhập không có xuất.
- Thực tế trong quá trình quản lý kho của người làm nghiệp vụ, cần theo dõi diễn biến của hàng hoá vật tư kho theo các khoảng thời gian bất kỳ mà họ muốn. NGAY1-NGAY2 là tuỳ ý. Không cứ NGAY1 phải là 1/1/xx. Vì thế nói tới đầu kỳ là nói "Tồn đầu kỳ" chứ không nói "Nhập đầu kỳ". "Nhập đầu kỳ" thì có thể chỉ cần xét phếu nhập, nhưng "Tồn đầu kỳ" (mà ngày đầu có thể bất kỳ) thì phải tính Nhập - Xuất (với ngày < NGAY1).

Không biết ở các đơn vị mọi người thế nào nhưng ở nhiều đơn vị mà công ty tôi hỗ trợ nghiệp vụ kho cho bộ phận quản lý kho họ đều đồng ý vơí cách làm làm báo cáo theo công thức như vậy.

Về dữ liệu đề ra. Như tôi đã nói, chỉ làm dữ liệu tính toán chứ nó không phải là thật. Cấu trúc bảng KHO được đơn giản hoá để làm báo cáo mà thôi. Dữ liệu được copy paste nhiều lần để cho nó có nhiều, vì thế thứ tự thời gian không theo và số phiếu trùng,.... Mà với đặc điểm của Excel thì thứ tự thời gian hay các cột khác cũng không theo được trình tự sắp xếp. Ví dụ sửa, xoá chứng từ mà người lập trình không muốn lệnh Insert, một lúc nào đó người ta đem Sort lại.....

Có lẽ chúng ta có thể trao đổi tiếp về các vấn đề liên quan đến code còn bàn luận về tính thực tế nghiệp vụ mỗi đơn vị ta tạm dừng lại vì khó để đi đến thống nhất cuối cùng và làm loãng topic.

 

[ptm0412]

Thực ra giả thuyết của sư phụ cũng rất đơn giản
Thì chỉ cần sửa lại 1 chut là xong
thầy thử test file xem có còn sai chỗ nào không giúp em.
thông thường em vẫn dùng pivot table để lấy kết quả sau đó copy vào mẫu báo cáo là xong
sếp chỉ cần xem kết quả khi in ra va ký duyệt.
Không biết có phải lê duy thương nghiện pivot table hay không mà khi gặp những dữ liệu lớn thường nghĩ ngay đến pivot table.sau đó mới đến công cụ khác.

Khi sửa ngày bắt đầu và ngày kết thúc còn lỗi lung tung.
Ngoài ra, chưa có lệnh refresh pivot table.
Sheet PV cần gì xóa, Pivot cũng không cần xóa, hoặc muốn xóa thì xóa pivot table, cần gì xóa cả sheet.

Download: http://www.mediafire.com/download/86m5ib7c14459cw/THNXT_FAST-LDT-EditByPtm.rar

 

[Nguyễn Duy Tuân]

A, a, aaaa, phải test thế này mới công bằng!

Chạy sub GetData trước rồi mới DoThoiGian!

Option Explicit
Declare Function QueryPerformanceCounter Lib "Kernel32" _
                        (x As Currency) As Boolean
Declare Function QueryPerformanceFrequency Lib "Kernel32" _
                        (x As Currency) As Boolean


Private Dict As Object, Collect As New Collection, Arr(), Check As Boolean


''Test cac thu tuc:
''----------------------------------------------------
Sub GetData()
    ''chay mot lan duy nhat!
    Set Dict = Nothing
    Set Collect = Nothing
    Erase Arr
    
    Dim i As Long
    Set Dict = CreateObject("Scripting.Dictionary")
    ReDim Arr(1 To 200000)
    For i = 1 To 200000
        Dict.Add "Nghia" & i, i
        Collect.Add i, "Nghia" & i
        Arr(i) = "Nghia" & i
    Next
End Sub


Sub DictTest()
    Check = Dict.Exists("Nghia" & 2000001)
End Sub


Sub CollTest()
    Check = Exists(Collect, "Nghia" & 2000001)
End Sub


Sub ArrTest()
    Check = Not (ItemExists("Nghia" & 2000001, Arr) = 0)
End Sub


''Cac ham kiem tra:
Function Exists(ByRef Collect As Collection, ByVal sKey As String) As Boolean
    Dim lCheck As Long
    On Error Resume Next
    lCheck = VarType(Collect.Item(sKey))
    If Err.Number = 0 Then
        Exists = True
    Else
        Exists = False
    End If
End Function


Function ItemExists(Item, Arr()) As Long
    Dim i&
    ItemExists = -1
    On Error GoTo lbDone
    If Not IsArray(Arr) Then Exit Function
    'Tim tu phan tu cuoi cung cua mang len dau se dat toc do tim nhanh neu du lieu nguon duoc sap xep tang dan
    For i = UBound(Arr) To LBound(Arr) Step -1
        If Arr(i) = Item Then
            ItemExists = i
            Exit For
        End If
    Next i
lbDone:
'Loi xay ra neu
End Function


Sub DoThoiGian()
    Dim T1@, T2@, Freq@, Overhead@
    QueryPerformanceFrequency Freq
    QueryPerformanceCounter T1
    QueryPerformanceCounter T2
    Overhead = T2 - T1
    QueryPerformanceCounter T1
    
    ''Tot nhat cho du lieu tu duoi 200 ngan dong:
    'DictTest
    
    ''Tu tren 200 ngan dong tro len:
    'CollTest
    
    'thay doi:
    ArrTest
    
    QueryPerformanceCounter T2
    Debug.Print (T2 - T1 - Overhead) / [COLOR=#ff0000][B]Freq * 1000000[/B][/COLOR]; "milliseconds(ms) " & Check
End Sub

Vẫn cảm thấy dùng mảng vẫn còn chậm trong nhiều trường hợp, kể cả khi đk "Nghia200000"

Anh Nghĩa làm thủ tục test chưa đầy đủ vì để so sánh tốc độ của của Array, Dictionary, Collection phải tính thời gian khi nạp dữ liệu vào nó (code ở bài cụ thể ta cũng phải nạp mà) và cả khi kiểm tra Exists có tồn tại và không tồn tại. Hàm ItemExists sẽ tìm nhanh trong trường hợp đã nêu ở bài trên.

Nếu có thời gian anh Nghĩa dùng Dictionary và Collection thay vào file của em ở bài tổng hợp xem khác nhau không?

 

[Nguyễn Duy Tuân]

Đã 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ả. Các thành viên xem bài #175 để download.

 

[Nguyễn Duy Tuân]

...
Hy vọng anh Tuân có nhiều đề tài để mọi người cùng tham gia, thảo luận để có thêm nhiều kiến thức cho anh em cùng tham khảo.

Qua bài này học được 2 vấn đề, thứ nhất là Collection, thứ 2 dùng mảng vẫn có thể test Exists nhanh chóng của Anh Tuân.

Cám ơn vì tất cả!

Qua đề topic dạng "THI" kiểu này cá nhân mình nhìn nhận chủ quan thì thấy nó bổ ích cho mọi người. Không biết nhiều người khác có thực sự thấy như vậy và hứng thú không? Nếu được thì sắp tới chúng ta lại có thêm một đề tài mới. Bài toán cũng vẫn sẽ là có vẻ đơn giản nhưng làm ta lại học thêm được một số cái mới.

 

[vodoi2x]

Đừng vội kết luận điều gì chỉ qua 1 bài ví dụ

Tôi nghĩ là: Đừng vội kết luận điều gì chỉ qua 1 bài ví dụ, cụ thể ở đây không thể nói Collection là tốt, hay Dictionary là không bằng, hay Array là giải pháp tốt,

Quả thực như thế, bài này về giá trị kết quả hay cả trong bảng danh mục (sheet DMVLSPHH) cũng chỉ tối đa có 12 mã hàng hóa khác nhau ==> dùng Dictionary, hay collection, hay array để lưu trữ / kiểm tra sự tồn tại (Exists) vào lúc tối đa số items/phần tử cũng chỉ là 12 ---> quá nhỏ để thấy sự khác biệt -- vì ví dụ ta dùng array thì cùng lắm 12 vòng lặp là chỉ ra itemExist hay không 12 vòng lặp với 1 phép kiểm tra tồn tại thì đâu tiêu tốn nhiều thời gian,

Mặt khác hiệu quả của các công cụ có sẵn như Dictionary, Collection thì lại cần 1 thời gian khởi nóng càng ít items thì hiệu quả của chúng về mặt thời gian sẽ giảm,

Bên cạnh đó, thì ta thấy các giải pháp ở đây chênh nhau rất ít vài chục đến vài trăm mili giây (tức là 1/5 1/4 giây) mà thôi, như thế thì cũng đâu quan trọng đâu, chớp mắt 2 cái là đã qua hơn 1 giây , còn uống tách cafe thì tính bằng đơn vị 15 phút (=900 giây), nên mọi so sánh là tương đối. Đôi khi với bài toán dạng này (thời gian tiêu tốn nhỏ) thì ta hy sinh yếu tố thời gian để code được sáng và ít lỗi hơn , sử dụng công cụ quen thuộc (tránh được các lỗi không lường thấy hoặc chưa rõ).

===> chưa kết luận được điều gì ở đây cả, chỉ có thể kết luận là có nhiều cách đi đến kết quả nhanh của chính bài này, và một số thủ thuật giảm thời gian tính mà thôi.

Nói vậy, để tránh trường hợp kết luận quá sớm, lại thành như mệnh đề sai lầm cho cả diến đàn , cứ mặc định nghĩ là đúng dù chúng có thể chỉ qua 1 2 bài test chưa đầy đủ.

 

[vodoi2x]

Qua đề topic dạng "THI" kiểu này cá nhân mình nhìn nhận chủ quan thì thấy nó bổ ích cho mọi người. Không biết nhiều người khác có thực sự thấy như vậy và hứng thú không? Nếu được thì sắp tới chúng ta lại có thêm một đề tài mới. Bài toán cũng vẫn sẽ là có vẻ đơn giản nhưng làm ta lại học thêm được một số cái mới.

Vội khép lại topic sớm vậy, tôi nghĩ vẫn còn các giải pháp khác như SQL với ADO , DAO mà, hay giải pháp Class

Dĩ nhiên có thể thời gian các giải pháp này kém hơn, nhưng quan trọng qua đó người xem (thành viên) thu lượm được các phương pháp tiếp cận khác nhau khi giải quyết 1 vấn đề / bài toán thực tế -- có như vậy mới tạo thành bữa tiệc đa sắc

 

[Nguyễn Duy Tuân]

Vội khép lại topic sớm vậy, tôi nghĩ vẫn còn các giải pháp khác như SQL với ADO , DAO mà, hay giải pháp Class

Dĩ nhiên có thể thời gian các giải pháp này kém hơn, nhưng quan trọng qua đó người xem (thành viên) thu lượm được các phương pháp tiếp cận khác nhau khi giải quyết 1 vấn đề / bài toán thực tế -- có như vậy mới tạo thành bữa tiệc đa sắc

Vâng. Là nói vậy chứ chưa khép mà anh. Em cung mong các thành viên tiếp tục gửi thêm giải pháp để cùng học hỏi.

 

[VetMini]

Vội khép lại topic sớm vậy, tôi nghĩ vẫn còn các giải pháp khác như SQL với ADO , DAO mà, hay giải pháp Class

Dĩ nhiên có thể thời gian các giải pháp này kém hơn, nhưng quan trọng qua đó người xem (thành viên) thu lượm được các phương pháp tiếp cận khác nhau khi giải quyết 1 vấn đề / bài toán thực tế -- có như vậy mới tạo thành bữa tiệc đa sắc

Tôi đã gợi ý rồi. Đo tốc độ tuyệt đối thì còn phải tuỳ thuộc vào tình trạng dữ liệu. VD: dùng ADO, chỉ riêng thời gian đợi nó kết nối cũng tuỳ thuộc vào kiến trúc của hệ thống vận hành rồi. Còn muôn vạn vấn đề khác như DAO nhanh hơn nhưng không bao quát và tiêu chuẩn bằng ADO, vv...

Nếu chỉ cần học cách uốn nắn cho code chạy nhanh thì như thế này là đủ rồi.
Những cái còn lại thuộc về thủ thuật phân tích vấn đề và trình bày code cho dễ hiểu, dễ sửa. Đôi khi làm cái này phải chấp nhận code chạy chậm một chút.

Riêng các công cụ đặc biệt như DAO và ADO,... Nhũng công cụ này dùng SQL để sắp xếp và tổng hợp dữ liệu cho nên chúng còn tuỳ thuộc vào bộ máy Access (hay SQL Server, vv...) có phần tối ưu hoá câu lệnh SQL hay không - hai câu lệnh viết khác nhau có thể bộ máy tự động được tối ưu hoá thành in hệt nhau.

 

[vodoi2x]

Giải pháp cập nhập cải tiến sử dụng Dictionary

Ở đây vẫn sử dụng giải pháp 2 dictionary (đảm bảo mọi mặt hàng phát sinh mới ở kho mà không có trong danh mục - dĩ nhiên điều này chỉ có tốt lên, và phòng trường hợp nhập mã sai vv.vvv)

So với giải pháp 2 Dictionary cũ (xem file tại bài 175) của chính tôi, thì giải pháp mới ở sub code sau đã giảm được khoảng 100mili giây (từ khoảng 330mls xuống 230mls tại lap của tôi), tiến tới chỉ kém giải pháp collection hiện đang có thời gian nhỏ nhất (xem tại bài 175) là (chậm hơn) khoảng10% (cụ thể tại lap tôi thì giải pháp Collection là khoảng 208mls, còn giải pháp 2 dictionary cải tiên mới này là khoảng 230mls - theo tôi dự thì trên máy tính test của Nguyễn Duy Tuân chắc khoảng 275mls)

Code của sub lapso với giải pháp cải tiến sử dụng 2 dictionary như sau

Private Sub LapSo()
    ''Code lap so th nxt
    ''Su Dung Dictionary
    ''nguoi Lap: vodoi2x
    ''email: vodoi909090@yahoo.com
    '' ngay cap nhap: 19.02.2014
    
    Application.ScreenUpdating = False
    Dim DicH, arrRes(), soDM()
    Dim Day1 As Long, Day2 As Long, i As Long, k As Long, p As Long
    Dim DicDM, Ngay(), MaSoHH(), SoLG(), LoaiPhieu(), ThanhTien()
    
    ''Nhap du lieu ngay tinh toan
    Day1 = Range("NGAY1").Value2
    Day2 = Range("NGAY2").Value2
       
    ''Nhap du lieu tu KHO
    With Range("KHO").Resize(Range("KHO").Rows.Count - 1, 1).Offset(1, 1)
        Ngay = .Value2
        MaSoHH = .Offset(, 5).Value2
        SoLG = .Offset(, 6).Value2
        LoaiPhieu = .Offset(, 8).Value2
        ThanhTien = .Offset(, 9).Value2
    End With
    
    ''nhap DL tu vung DM VLSPHH
    soDM = Range("DMVLSPHH").Resize(Range("DMVLSPHH").Rows.Count - 1, 3).Offset(1).Value2
    p = UBound(soDM)

    ReDim arrRes(1 To p + 10, 1 To 12) ''Mang chua ket qua gom 12 cot, 10 so ma du phong chua co trong DM VLSPHH
    Set DicH = CreateObject("Scripting.Dictionary") '' khoi tao Dictionary DicH dung de giu vi tri cua 1 MaHH trong mang arrRes
    k = 0
    For i = 1 To UBound(Ngay) ''Duyet tung dong chung tu cua Kho de xet ngay
        If Ngay(i, 1) <= Day2 Then
            
            p = DicH.Item(MaSoHH(i, 1))
            If p = 0 Then ''Truong hop CHUA CO MaHH trong Dictionary DicH, nen ta cong vao, va gan gia tri vao arrRes
                k = k + 1
                DicH(MaSoHH(i, 1)) = k
                arrRes(k, 2) = MaSoHH(i, 1)
                p = k
            End If '' If p = 0 Then
            
            If Ngay(i, 1) < Day1 Then       ''ton dau ky
                If LoaiPhieu(i, 1) Like "N" Then
                    arrRes(p, 5) = arrRes(p, 5) + SoLG(i, 1)
                    arrRes(p, 6) = arrRes(p, 6) + ThanhTien(i, 1)
                Else
                    arrRes(p, 5) = arrRes(p, 5) - SoLG(i, 1)
                    arrRes(p, 6) = arrRes(p, 6) - ThanhTien(i, 1)
                End If
            Else                           ''trong ky
                If LoaiPhieu(i, 1) Like "N" Then
                    arrRes(p, 7) = arrRes(p, 7) + SoLG(i, 1)
                    arrRes(p, 8) = arrRes(p, 8) + ThanhTien(i, 1)
                Else
                    arrRes(p, 9) = arrRes(p, 9) + SoLG(i, 1)
                    arrRes(p, 10) = arrRes(p, 10) + ThanhTien(i, 1)
                End If
            End If
            
        End If ''-------------------------------------------------------- If Ngay(i, 1) <= Day2 Then
    Next i
    
    
    ''tao DicDM de luu tru vi tri cua Ten hang hoa & Don vi tinh
    Set DicDM = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(soDM)
        DicDM(soDM(i, 1)) = i
    Next i
    
    ''tinh toan TON CUOI KY & tinh Tong GrandTotal cua cac cot Thanh tien: TonDK, NHAP, XUAT, & TON CUOI
    ''bang cach duyet cac dong cua arrRes
    p = k + 1
    Dim j As Long
    For i = 1 To k
        arrRes(i, 1) = i
        j = DicDM(arrRes(i, 2))
        If j > 0 Then
            arrRes(i, 3) = soDM(j, 2)
            arrRes(i, 4) = soDM(j, 3)
        End If
        arrRes(i, 11) = arrRes(i, 5) + arrRes(i, 7) - arrRes(i, 9)
        arrRes(i, 12) = arrRes(i, 6) + arrRes(i, 8) - arrRes(i, 10)
        
        arrRes(p, 6) = arrRes(p, 6) + arrRes(i, 6)
        arrRes(p, 8) = arrRes(p, 8) + arrRes(i, 8)
        arrRes(p, 10) = arrRes(p, 10) + arrRes(i, 10)
    Next i
    arrRes(p, 12) = arrRes(p, 6) + arrRes(p, 8) - arrRes(p, 10)
    
    ''Xuat ket qua ra Sheet
    With Sheet26.Range("b12")
        .Resize(13, 12).ClearContents
        If k Then .Resize(p, 12) = arrRes
    End With
    
End Sub

Vậy các bạn thử tìm ra đâu là thủ thuật cải tiến trong sub này? tìm ra sẽ thấy giải pháp rút ngắn thời gian tính với dictionary, thật đơn giản, và sẽ phát hiện ra cả hạn chế cuả thủ thuật này (?)

 

[VetMini]

Ở đây vẫn sử dụng giải pháp 2 dictionary (đảm bảo mọi mặt hàng phát sinh mới ở kho mà không có trong danh mục - dĩ nhiên điều này chỉ có tốt lên, và phòng trường hợp nhập mã sai vv.vvv)
...
Vậy các bạn thử tìm ra đâu là thủ thuật cải tiến trong sub này? tìm ra sẽ thấy giải pháp rút ngắn thời gian tính với dictionary, thật đơn giản, và sẽ phát hiện ra cả hạn chế cuả thủ thuật này (?)

Đọc hổng hiểu nổi (tôi hơi lười đọc chứ không phê bình cách viết) cho nên chả biêt cải tiến ở đâu.

Duy có một chút kiến thức cùi:
Dictionary là một class nằm trong Scripting Engine.
Theo nguyên tắc Lập Trình Hướng Đối Tuợng, ta có thể dùng class căn bản nhất là Object để "đa dạng hoá" ra một Object của class này.
dim meDic as Object
set meDic = CreateObject(....)
Tuy cách này rất dễ dùng và không đòi hỏi phải reference đến Script Engine, nhưng nó là cách kêt nối trế - compiler chưa biết dạng cuối cùng của meDic cho nên mối lần đề cập đến meDic thì nó phải kết nối lại.

Nếu dùng
dim yoDic as Scipting.Dictionary
set yoDic = New Scipting.Dictionary
Thì sẽ sử dụng cách kết nối sớm - compiler biết dạng cuối cùng của yoDic cho nên nó có thể kết nối trước.

Thông thường thì cách kết nối sớm hữu hiệu (và có thể chạy nhanh) hơn cách nối trễ. Tuy nhiên, đối với VBA thì muốn kết nối sớm phải reference đến Scripting. Một ví dụ cho thấy một trong những trường hợp hy sinh tốc độ để đạt sự tổng quát.

 

[Nguyễn Duy Tuân]

Ở đây vẫn sử dụng giải pháp 2 dictionary (đảm bảo mọi mặt hàng phát sinh mới ở kho mà không có trong danh mục - dĩ nhiên điều này chỉ có tốt lên, và phòng trường hợp nhập mã sai vv.vvv)

So với giải pháp 2 Dictionary cũ (xem file tại bài 175) của chính tôi, thì giải pháp mới ở sub code sau đã giảm được khoảng 100mili giây (từ khoảng 330mls xuống 230mls tại lap của tôi), tiến tới chỉ kém giải pháp collection hiện đang có thời gian nhỏ nhất (xem tại bài 175) là (chậm hơn) khoảng10% (cụ thể tại lap tôi thì giải pháp Collection là khoảng 208mls, còn giải pháp 2 dictionary cải tiên mới này là khoảng 230mls - theo tôi dự thì trên máy tính test của Nguyễn Duy Tuân chắc khoảng 275mls)

Code của sub lapso với giải pháp cải tiến sử dụng 2 dictionary như sau

Vậy các bạn thử tìm ra đâu là thủ thuật cải tiến trong sub này? tìm ra sẽ thấy giải pháp rút ngắn thời gian tính với dictionary, thật đơn giản, và sẽ phát hiện ra cả hạn chế cuả thủ thuật này (?)

Code này của anh chạy nhanh đấy. Vì công việc dở dang nên em không tắt các ứng dụng trên máy như lần test trước. Nhưng so với code của em ở bài #175 thì của anh chạy nhanh hơn.

 

[VetMini]

Qua đề topic dạng "THI" kiểu này cá nhân mình nhìn nhận chủ quan thì thấy nó bổ ích cho mọi người. Không biết nhiều người khác có thực sự thấy như vậy và hứng thú không? Nếu được thì sắp tới chúng ta lại có thêm một đề tài mới. Bài toán cũng vẫn sẽ là có vẻ đơn giản nhưng làm ta lại học thêm được một số cái mới.

Với trình độ cùn của tôi thì chỉ cần mấy trang đầu là đủ học hỏi nhiều lắm rồi. Chỗ còn lại quá đặc thù cho nên tôi chả học được gì cả.

Muốn học xa hơn thì nới rộng đề tài, hoặc lập một kỳ thi khác để giải những bài toán như thế này:

bảng tổng hợp nhập xuất tồn

Đề bài: viết code sao cho chỉ cần chỉnh sửa 1 vài chỗ nhất định nào đó sẽ giải quyết được hết những loại bài toán như trên. Điểm được chấm theo: ví dụ số dòng cần sửa,...
Những chỗ hardcode như [A1:A100] có thể châm chước bằng cách tính "Replace All" như 2 dòng (1 dòng là code cũ, 1 dòng là code mới)
Những chô cần chỉnh sửa phải được ghi chú. Nếu không ghi ra thì coi như chạy sai.
Code nào không có ghi chú những chỗ cần chỉnh sửa thì coi như không cần chỉnh sửa.

 

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

Vội khép lại topic sớm vậy, tôi nghĩ vẫn còn các giải pháp khác như SQL với ADO , DAO mà, hay giải pháp Class

Dĩ nhiên có thể thời gian các giải pháp này kém hơn, nhưng quan trọng qua đó người xem (thành viên) thu lượm được các phương pháp tiếp cận khác nhau khi giải quyết 1 vấn đề / bài toán thực tế -- có như vậy mới tạo thành bữa tiệc đa sắc

Đang đợi các thành viên khác viết xem để học hỏi thêm, tuy nhiên chưa thấy bài nào, mình xin mở đầu, như các bạn cũng đã biết tốc độ của nó sẽ kém hơn nhiều, nhưng ADO cũng là 1 giải pháp. Mong các bạn cải thiện để code ngày càng nhanh hơn.

[GPECODE=sql]Sub LapSo()
Dim cn As Object, rst As Object, strNgay1 As String, strNgay2 As String
Dim mySQL As String
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
strNgay1 = Format(Range("Ngay1"), "MM-dd-yyyy")
strNgay2 = Format(Range("Ngay2"), "MM-dd-yyyy")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
mySQL = "SELECT KHO.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.DVI, " & _
"Sum(IIf([kho].[Ngay_CT]<#" & strNgay1 & "# And [kho].[loai_phieu]='N',[KHO].[SLG],0)-IIf([kho].[ngay_ct]<#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[SLG],0)) AS TONDK, " & _
"Sum(IIf([kho].[Ngay_CT]<#" & strNgay1 & "# And [kho].[loai_phieu]='N',[KHO].[THANH_TIEN],0)-IIf([kho].[ngay_ct]<#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[THANH_TIEN],0)) AS TTDK, " & _
"Sum((IIf([kho].[ngay_ct] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [LOAI_PHIEU]='N',[KHO].[SLG],0))) AS NHAP, " & _
"Sum((IIf([kho].[Ngay_CT] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [LOAI_PHIEU]='N',[KHO].[THANH_TIEN],0))) AS TTNHAP, " & _
"Sum((IIf([kho].[ngay_ct] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[SLG],0))) AS XUAT, " & _
"Sum((IIf([kho].[ngay_ct] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[THANH_TIEN],0))) AS TTXUAT, " & _
"[TONDK]+[NHAP]-[XUAT] AS TONCK, " & _
"[TTDK]+[TTNHAP]-[TTXUAT] AS TTCUOI " & _
"FROM KHO INNER JOIN DMVLSPHH ON KHO.MA_VLSPHH = DMVLSPHH.MA_VLSPHH " & _
"GROUP BY KHO.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.DVI"
Set rst = cn.Execute(mySQL)
With Sheets("THNXT")
.[C12:M23].ClearContents
.[C12].CopyFromRecordset rst
End With
rst.Close: cn.Close
Set rst = Nothing: Set cn = Nothing

End Sub

[/GPECODE]