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

 

[Lê Duy Thương]

anh hải gửi bài đi chứ.
HTN để đến hết ngày 15 hãy tiếp tục tranh luận nhé.

 

[ndu96081631]

E nghĩ thầy k để ý nhiều đến chuyện thắng thua đâu.Và cái e mong đợi không phải xem ai là người giỏi nhất,e mong thấy giải thuật của thầy thế nào.Vì e thấy ai nộp bài ở đây điều là giỏi và có kiến thức cả rồi .Hi vọng thầy cũng tham gia để mọi người cùng trao đổi thầy nhé.

Mình đùa tí thôi! Thật ra là dạo này bận quá, không thể làm gì được
Viết code phải có hứng và cần có thời gian. Tính mình vốn cẩn thận, thà không viết thì thôi, nếu đã viết thì phải đàng hoàng, không thể viết bừa cho có được
(một code đơn giản có khi phải sửa đi sửa lại vài chục lần mà vẫn chưa vừa ý)

 

[ptm0412]

Mình thấy thuật toán bài 112 là rất hợp lý rồi. Đơn giản, dễ hiểu và tốc độ cũng cực nhanh. Mình cũng code bài này gần như giống bài 112. Tuy nhiên lúc xem cách khai báo cũng có hơi thắc mắc chút và mình nghĩ chắc là kỹ thuật là chỗ này nên code có vẻ nhanh hơn code mình nhiều.

Ban đầu tôi khai báo cả 2 biến là Long, sau đó sửa lại 1 biến thành Date là để kiểm tra dữ liệu ngày tháng xem sau khi tính lại có bị thay đổi kết quả không. Vì trước đây có lần tôi khai báo biến Long không được, biến date cũng không được, phải dùng CLng() tất tần tật để so sánh mới xong. Lần này tôi test xem nếu dữ liệu chuẩn thì có bị lỗi như lần trước hay không.

Kết luận là nếu dữ liệu chuẩn thì biến nào cũng OK. Tuy nhiên cũng có khi do định dạng sai 1 vài ô, do lỗi bản thân anh Bill, phải xoay sở chán chê mới được.

Cuối cùng là test thấy vẫn ok nên không sửa.

 

[Quang_Hải]

anh hải gửi bài đi chứ.
HTN để đến hết ngày 15 hãy tiếp tục tranh luận nhé.

Mình có đặc điểm là viết code cực nhanh và cực ẩu luôn, khai báo biến thì tá lả và chẳng bao giờ có chú thích trong code vì chẳng có biết chú thích. Bài này mình thử rồi hôm qua, thuật toán 99% giống bài 112. Thôi thì mạn phép anh PTM cho mình mược code đó thi luôn nha.

Nhưng mà xem thời gian của bài 102 thì chẳng còn ý chí thi thố gì nữa cả. Hỏng biết là tà thuật kiểu gì trong code nữa. Cảm giác nghi ngờ nhiều lắm. Vì code kiểu gì cũng không thể nào được như thế. Khiếp.

 

[ptm0412]

Mình có đặc điểm là viết code cực nhanh và cực ẩu luôn, khai báo biến thì tá lả và chẳng bao giờ có chú thích trong code vì chẳng có biết chú thích. Bài này mình thử rồi hôm qua, thuật toán 99% giống bài 112. Thôi thì mạn phép anh PTM cho mình mược code đó thi luôn nha.

Nhưng mà xem thời gian của bài 102 thì chẳng còn ý chí thi thố gì nữa cả. Hỏng biết là tà thuật kiểu gì trong code nữa. Cảm giác nghi ngờ nhiều lắm. Vì code kiểu gì cũng không thể nào được như thế. Khiếp.

1% còn lại có lẽ là các chỗ sau:
- Cột 1 Số thứ tự của kết quả ăn gian theo biến k
- Mảng tạm của tôi chỉ có 7 cột, không có 2 cột tồn cuối
- Công thức Check để loại
- Số lần IF

 

[Hoàng Trọng Nghĩa]

Nhưng mà xem thời gian của bài 102 thì chẳng còn ý chí thi thố gì nữa cả. Hỏng biết là tà thuật kiểu gì trong code nữa. Cảm giác nghi ngờ nhiều lắm. Vì code kiểu gì cũng không thể nào được như thế. Khiếp.

Bài của Lê Duy Thương rất nhanh, dùng Pivot thì lọc lẹ nhất, mình cho rằng bạn hiền của mình biết cách dùng "Thủ Thuật" chứ không phải gọi là "Ăn Gian".

 

[Lê Duy Thương]

Mình có đặc điểm là viết code cực nhanh và cực ẩu luôn, khai báo biến thì tá lả và chẳng bao giờ có chú thích trong code vì chẳng có biết chú thích. Bài này mình thử rồi hôm qua, thuật toán 99% giống bài 112. Thôi thì mạn phép anh PTM cho mình mược code đó thi luôn nha.

Nhưng mà xem thời gian của bài 102 thì chẳng còn ý chí thi thố gì nữa cả. Hỏng biết là tà thuật kiểu gì trong code nữa. Cảm giác nghi ngờ nhiều lắm. Vì code kiểu gì cũng không thể nào được như thế. Khiếp.

thực ra em cũng không biết nhiều về cái xnt này . nên em nghĩ vãn lỗi mà em không biết.còn tốc độ thì đúng là thật anh .
Hải à.
EM ĐANG KIỂM TRA LẦN CUỐI ĐÚNG NGÀY 15 GƯI LUÔN BẢN FINAL

 

[ptm0412]

thực ra em cũng không biết nhiều về cái xnt này . nên em nghĩ vãn lỗi mà em không biết.còn tốc độ thì đúng là thật anh .
Hải à.
EM ĐANG KIỂM TRA LẦN CUỐI ĐÚNG NGÀY 15 GƯI LUÔN BẢN FINAL

Chắc chắn là có ăn gian, vì với pivot table:
- Với data này phải dùng ít ra là 2 pivot table chứ không phải 1. Nếu muốn ép về 1 Pivot table sẽ phải tạo cột phụ trong data
- Một lần refresh Pivot ít nhất 450 ms. Để nguyên nhấn nút hoài thì nhanh,, chứ sửa ngày bên sheet setting là phải refresh rồi mới tính.

Nhân tiện, code của mọi người dự thi phải chạy tốt và đúng khi thay dổi ngày bên setting, kể cả ngày bắt đầu là 01/07/2005 trở về trước (tồn đầu = 0)

 

[vodoi2x]

thực ra em cũng không biết nhiều về cái xnt này . nên em nghĩ vãn lỗi mà em không biết.còn tốc độ thì đúng là thật anh .
Hải à.
EM ĐANG KIỂM TRA LẦN CUỐI ĐÚNG NGÀY 15 GƯI LUÔN BẢN FINAL

Không rõ ý kiến chủ topic sao?

Còn theo như ở đây, tôi nghĩ các bạn lên post lên đây luôn, mọi người tiện so sánh, đánh giá, và góp ý hơn, chứ sau đó thì có khi không khí đã nguội thì ít người góp ý hơn,

Mọi người cho ý kiến, tôi sẽ viết code theo thuần VBA (không sử dụng các tools sẵn của excel như sort, pivot, filter ...vv) và gửi lên đây luôn? nên không?

 

[SA_DQ]

[thongbao]Mọi người cho ý kiến, tôi sẽ viết code theo thuần VBA (không sử dụng các tools sẵn của excel như sort, pivot, filter ...vv) và gửi lên đây luôn? nên không?[/thongbao]
Nên quá chứ lị!
Có thể ai đó chưa thực sự vừa lòng; nhưng với số đông thì muốn được chia sẻ & học tập mà bạn!
& cũng có những bài đưa code lên rồi đó thay!
Thân ái & mong tin từ bạn!

 

[Lê Duy Thương]

Mọi người cho ý kiến, tôi sẽ viết code theo thuần VBA (không sử dụng các tools sẵn của excel như sort, pivot, filter ...vv) và gửi lên đây luôn? nên không?

không sao cả anh .tất cả giải pháp đều được hoan nghênh trong topic này.anh cứ gửi bai cho anh tuân. sau ngày 15 chắc chắn anh tuân sẽ post tất cả các bài lên topic này

 

[Nguyễn Duy Tuân]

Không rõ ý kiến chủ topic sao?

Còn theo như ở đây, tôi nghĩ các bạn lên post lên đây luôn, mọi người tiện so sánh, đánh giá, và góp ý hơn, chứ sau đó thì có khi không khí đã nguội thì ít người góp ý hơn,

Mọi người cho ý kiến, tôi sẽ viết code theo thuần VBA (không sử dụng các tools sẵn của excel như sort, pivot, filter ...vv) và gửi lên đây luôn? nên không?

Là do như các anh ở trên đã nói, thêm nữa là muốn các tác giả có nghiên cứu độc lập trong ví dụ này nên mới gửi bài riêng vào mail và công bố sau. Cũng chỉ còn 2 ngày nữa thôi nên vẫn theo như công bố ban đầu không vấn đề gì anh ạ. CÒn anh hay anh ptm muốn gửi code trực tiếp lên đây để mọi người xem và trao đổi luôn cũng được. Đặc biệt với giải pháp code VBA thuần túy, không sử dụng các công cụ hỗ trợ mạnh của Excel cũng là rất tốt cho người học VBA nắm rõ hơn về ngôn ngữ VBA.

 

[siwtom]

OK, mỗi người có một lý do, một thuật toán để làm, vậy câu 1 tại sao cố tình đặt biến này, biến kia vậy? Hỏi để học kiểu biến này. Có gì đặc biệt nên cố tình làm thế sao Sư phụ? Nhầm lẫn thì bình thường, còn cố tình thì rất không hiểu tại sao!

Chuyện góp ý là chuyện bình thường. Nhưng tôi thấy bạn "đeo bám" những cái tủn mủn quá.
Thế nếu không khai là Long mà khai là Variant thì có thấy kỳ không? Vì thực ra ngày tháng là kiểu Variant.
Nói trắng ra là ngày tháng được lưu ở dạng số (numeric) nên chuyện khai báo biến là Long chả có gì là ngồ ngộ cả. Thậm chí khai báo là Double cũng chả có gì là ngồ ngộ. Vì thực chất ngày giờ được lưu ở dạng đó. Chỉ có điều là ở đây ta chỉ làm việc với ngày tháng, không có thời gian, tức làm việc với số nguyên nên không cần tới Double mà chỉ cần Long. Nhu cầu chỉ cần tới numeric kiểu Long (4 bai) thì dùng Long là hợp lý. Đâu có cần, trong trường hợp chỉ có ngày mà không có giờ, tới kiểu Date - Variant - Double (8 bai)?

 

[VetMini]

Xin lỗi trước, tôi quote cái này cốt chỉ nói chuyện cách thức thôi chứ không muốn phê bình code hay thủ thuật. Và hoàn toàn không có ý liên quan cá nhân.

Khi làm một chương trình thì người thực hiện phải hoạch định xem mình sẽ làm gì để chương trình chạy mượt mà hơn.

Thứ hai là phải cân nhắc giữa cái nhanh và việc lường trước các lỗi phát sinh.

Với bài tập này, thay vì tôi chọn code chạy 450 ms thì tôi sẽ chọn loại 550 ms.

Lý do:

Kể từ đây về sau, các đoạn văn màu nâu là quote của tác giả, các đoạn màu xanh là diễn giải của tôi.

1) Tôi phải kiểm tra dữ liệu nguồn, cụ thể là sheet KHO có trạng thái AutoFilter hay không, nếu có thì tôi sẽ bỏ chế độ này. Một cơ sở dữ liệu mà đang bị Filter thì có khả năng chúng ta không thể gán vào Array đầy đủ.

Đây chỉ là bài toán chơi cho vui, cho nên chỉ cần phần code chính nằm gọn một chỗ là được. Khi áp dụng vào thực tế nguời copy code sẽ tự biết chèn thêm các phần kiểm soát.

2) Kiểm tra xem dữ liệu trong sheet KHO đã được nhập dòng nào chưa, nếu chưa nhập thì thông báo.

Đây chỉ là lập một bảng báo cáo/tóm tắt. Nếu cái gì cũng dùng message box để thông báo thì chạy mãi cũng không xong. Thường thì người ta có một cái log. Những gì trục trặc thì ghi vào log này. Người chạy code sẽ đọc cái log này để tìm ra những chỗ trục trặc. Chỉnh sửa và chạy lại.

Chỉ khi nào cần lấy kết quả làm đầu vào cho một chương trình khác thì mới bắt buộc phải chạy một lần thông suốt.

3) Kiểm tra xem Từ ngày, Đến ngày đã được nhập vào hay chưa, nếu chưa nhập hoặc không phải là dạng ngày cũng phải thông báo.

Nếu bài này đưa vào thực tế thì người ta sửa lại thành 1 sub, nhận các tham số:
- Vùng dữ liệu cần đọc (lưu ý là nếu làm trên thực tế, người ta sẽ có khuynh hướng dùng dữ liệu từ một file khác. Một hàm nào đó sẽ mở file này và tìm vùng dữ liệu. Cách này hữu hiệu hơn cho việc dùng ADO)
- Ngày (hoặc tháng, quý... thì có một hàm khác chuyển ra ngày) cần lọc
- Vùng dữ liệu chứa bảng mẫu báo cáo. Cách làm đúng là không viết lên bảng mẫu mà phải copy lại bảng mẫu rồi viết lên đó.

Trên thực tế, người ta sẽ có cái mà dân chuyên lập trình gọi là "Data Dictionary" (tiếng cũ) hoặc "Metadata" (tiếng cận đại hơn). Tức là một phần code chuyên diễn tả dữ liệu (các CSDL đều có phần này để định dạng dữ liệu)h. Tôi không quen VB cho nên không biết lập như thế nào là chuẩn nhất. Trước mắt nếu bắt buộc phải làm trên VBA thì tôi đặt một số hằng string xác định tên (headings) các cột trong bảng dữ liệu (Nhap, Xuat, ThanhTien, vv...). Sau đó dò tên cột để lấy các thông số về vị trí cột.

4) Xóa dữ liệu cũ trên biểu mẫu, bởi khi dữ liệu sắp nhập vào ít hơn dữ liệu cũ trên biểu mãu sẽ bị trộn dữ liệu.

Như đã đề cập ở trên. Cách làm đúng là không viết lên bảng mẫu mà phải copy lại bảng mẫu rồi viết lên đó.

5) Trong biểu mẫu, tôi luôn bảo toàn một số hàng nhất định, trong trường hợp này, tôi bảo toàn số hàng là 15 dòng. Vì vậy, tôi phải kiểm tra trước xem biểu mẫu đó có đủ 15 dòng chưa, nếu đủ thì thôi, không đủ thì Insert thêm, còn nếu hơn thì Delete đi, làm sao cho Insert hoặc Delete phải bảo toàn 15 dòng. Mặt khác ta phải xem số hàng mà ta sắp gán vào biểu mẫu có nhiều hơn 15 dòng hay không, nếu nhiều hơn thì ta Insert thêm (vẫn đảm bảo cấu trúc định dạng), không để tình trạng dữ liệu tràn.

Đúng như bạn nói, nếu code không thể đảm bảo các trường hợp bị tràn hoặc các trường hợp trục trặc thì đem so sánh tốc độ với nhau chưa hẳn là công bình.

------------------------------------------------
Tôi vẫn dùng Dictionary để thực hiện code vì nó đảm bảo mã vật tư không trùng.

Cách nào cũng được. Công cụ gì tiện lợi thì dùng.

Xin lỗi lần nữa là tôi cũng không biết nên post ở đây hay bên bài "Kỹ năng lập trình" mới đúng.

 

[vodoi2x]

Đồng ý với VietMini: đây chỉ là 1 sub quá nhỏ, là 1 phần trong bài toán lớn, nếu quá xét nét (phải xét đến từng cột Dữ liệu chuẩn) thì có xét cả năm, cần phải nhớ đầy chỉ là 1 sub và lại đang cần test tốc độ thời gian nữa.

Với dữ liệu nhiều Bài này chưa hẳn Dictionary đã là lựa chọn tốt đâu Hoàng Trọng Nghĩa ah,

Là do như các anh ở trên đã nói, thêm nữa là muốn các tác giả có nghiên cứu độc lập trong ví dụ này nên mới gửi bài riêng vào mail và công bố sau. Cũng chỉ còn 2 ngày nữa thôi nên vẫn theo như công bố ban đầu không vấn đề gì anh ạ. CÒn anh hay anh ptm muốn gửi code trực tiếp lên đây để mọi người xem và trao đổi luôn cũng được. Đặc biệt với giải pháp code VBA thuần túy, không sử dụng các công cụ hỗ trợ mạnh của Excel cũng là rất tốt cho người học VBA nắm rõ hơn về ngôn ngữ VBA.

Độc lập cũng là giải pháp hay, nhưng mọi người cứ đưa lên, đọc code người khác hiểu, và sáng tạo lại (dĩ nhiên có ghi chú rõ là trích ...) thì cũng là đáng quý, và dù sao cuộc thi cũng kết quả là chia sẻ, và cũng không có chi phải bí mật (hàng ngày vẫn giúp thành viên bài bày ra ngay còn được) - giờ open cùng được bàn thảo phải hay biết bao

 

[Hoàng Trọng Nghĩa]

Đồng ý với VietMini: đây chỉ là 1 sub quá nhỏ, là 1 phần trong bài toán lớn, nếu quá xét nét (phải xét đến từng cột Dữ liệu chuẩn) thì có xét cả năm, cần phải nhớ đầy chỉ là 1 sub và lại đang cần test tốc độ thời gian nữa.

Với dữ liệu nhiều Bài này chưa hẳn Dictionary đã là lựa chọn tốt đâu Hoàng Trọng Nghĩa ah,

Có thể là Dict chưa hẳn đã tốt, nhưng mình viết code chạy tương tự thì không nhanh hơn nó nên đành chọn nó vậy.

 

[vodoi2x]

Có thể là Dict chưa hẳn đã tốt, nhưng mình viết code chạy tương tự thì không nhanh hơn nó nên đành chọn nó vậy.

Tuy thế Giải pháp 1, up lên đây cũng là sử dụng Dictionary,

Tốc độ có cải thiện nhiều lần: thử trên laptop của tôi khoảng 330-340 mls cho chạy lần đầu, chạy từ lần 2 trở đi chỉ khoảng 230-240 mls

chú ý: Phương châm là nghiên cứu tốc độ là chính, nên coi như dữ liệu Nhập là đã chuẩn hóa (vì sub này chỉ là 1 phần code thử nghiệm mà thui)

Mong nhận được đóng góp

Private Sub LapSo()
    ''Code lap so th nxt
    ''Su Dung Dictionary
    ''nguoi Lap: vodoi2x
    ''email: vodoi909090@yahoo.com
    
    Application.ScreenUpdating = False
    Dim DicH, arrRes(), soDM()
    Dim Day1 As Long, Day2 As Long, i As Long, k As Long, p As Long, c1 As Long, c2 As Long
    Static DicDM, Ngay(), MaSoHH(), SoLG(), LoaiPhieu(), ThanhTien()
    
    ''Nhap du lieu ngay tinh toan
    Day1 = Range("NGAY1").Value2
    Day2 = Range("NGAY2").Value2
    
    ''Neu la lan chay dau tien / hoac khi sheet KHO co sua chua  thay doi, thi
    '' nap cac du lieu tinh toan Ngay, MaSoHH, ...., ThanhTien
    If Not Run1K Then
        With Range("KHO").Resize(Range("KHO").Rows.Count - 1, 1).Offset(, 1)
            Ngay = .Value2
            MaSoHH = .Offset(, 5).Value2
            SoLG = .Offset(, 6).Value2
            LoaiPhieu = .Offset(, 8).Value2
            ThanhTien = .Offset(, 9).Value2
        End With
        Run1K = True ''khang dinh da chay 1 lan doc du lieu tu Sheet KHO
    End If
    
    ''Neu la lan chay dau tien / hoac khi sheet DM VLSPHH co sua chua thay doi, thi
    '' nap cac du lieu tinh toan cho SoDM, va DicDM dung luu gia tri Key
    If Run1D Then
        p = DicDM.Count
    Else
        soDM = Range("DMVLSPHH").Offset(1).Resize(Range("DMVLSPHH").Rows.Count - 1, 3).Value2
        p = UBound(soDM)
        
        ''Khoi tao Dictionary DicDM luu giu Key la MaHH va Item la gia tri Ten, DVT trong sheet DM VLSPHH
        Set DicDM = CreateObject("Scripting.Dictionary")
        For i = 1 To p
            DicDM(soDM(i, 1)) = Array(soDM(i, 2), soDM(i, 3))
        Next i
        Run1D = True
    End If

    ReDim arrRes(1 To p + 10, 1 To 12) ''Mang chua ket qua gom 12 cot
    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
            If Ngay(i, 1) < Day1 Then       ''ton dau ky
                c1 = 5:     c2 = 6
                If LoaiPhieu(i, 1) Like "X" Then
                    SoLG(i, 1) = -SoLG(i, 1)
                    ThanhTien(i, 1) = -ThanhTien(i, 1)
                End If
            Else                           ''trong ky
                If LoaiPhieu(i, 1) Like "N" Then
                        c1 = 7:     c2 = 8
                Else:   c1 = 9:     c2 = 10:    End If
            End If
            
            If DicH.exists(MaSoHH(i, 1)) Then   ''Truong hop CO MaHH trong Dictionary DicH, nen ta lay vi tri va gan gia tri vao arrRes
                p = DicH.Item(MaSoHH(i, 1))
                arrRes(p, c1) = arrRes(p, c1) + SoLG(i, 1)
                arrRes(p, c2) = arrRes(p, c2) + ThanhTien(i, 1)
            Else                                ''Truong hop CHUA CO MaHH trong Dictionary DicH, nen ta cong vao, va gan gia tri vao arrRes
                k = k + 1
                DicH.Add MaSoHH(i, 1), k
                arrRes(k, 2) = MaSoHH(i, 1)
                arrRes(k, c1) = SoLG(i, 1)
                arrRes(k, c2) = ThanhTien(i, 1)
            End If
        End If
    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
    For i = 1 To k
        arrRes(i, 1) = i
        If DicDM.exists(arrRes(i, 2)) Then
            arrRes(i, 3) = DicDM.Item(arrRes(i, 2))(0)
            arrRes(i, 4) = DicDM.Item(arrRes(i, 2))(1)
        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)
        arrRes(p, 12) = arrRes(p, 12) + arrRes(i, 12)
    Next i
    
    ''Xuat ket qua ra Sheet
    With Range("KetQuaNXT").Offset(1)
        .Resize(13, 12).ClearContents
        If k Then .Resize(p, 12) = arrRes
    End With
End Sub

cuối cùng cũng đã Upload file lên trực tiếp GPE

Các bạn nên down load file về
vì Sub có tối ưu lần chạy thứ 2 code sẽ không load dữ liệu lại từ KHO, và sheet "DM VLSPHH" nữa

Tuy thế, nếu có thay đổi Dữ liệu ở 2 sheet trên thì sẽ đọc lại (thông qua sự kiện worksheet change) --> khi đó tốc độ thời gian lại như lần 1 - Thông qua 2 biến public chung Run1K và Run1D

vậy, các bạn thử chạy và báo lại tốc độ xem sao và mong nhận được góp ý, xin cám ơn

 

[dhn46]

Đọc bài anh Vodoi2x em vỡ ra một điều mà mình rất ít chú ý đó là biến tạm.

Khi dùng biến tạm trên Code của em thì thấy tốc độ tăng đáng kể mặc dù còn "Rùa" quá

 

[vodoi2x]

Đọc bài anh Vodoi2x em vỡ ra một điều mà mình rất ít chú ý đó là biến tạm.

Khi dùng biến tạm trên Code của em thì thấy tốc độ tăng đáng kể mặc dù còn "Rùa" quá

Và bạn nên chú ý: .Value2 thay vì chỉ .Value khi lấy dữ liệu, cũng như chỉ nhập những dữ liệu cần vào thay vì load kiểu gán mảng 2 chiều rộng (chứa các dữ liệu không cần, vì KHO có nhiều dòng, dẫn đến giảm tốc độ),

Cũng như cố gắng khai báo khai báo tường minh các biến, ví dụ như biến mảng thêm () sau tên biến

Cứ thử test sẽ thấy các điều đó

 

[Hoàng Trọng Nghĩa]

Và bạn nên chú ý: .Value2 thay vì chỉ .Value khi lấy dữ liệu, cũng như chỉ nhập những dữ liệu cần vào thay vì load kiểu gán mảng 2 chiều rộng (chứa các dữ liệu không cần, vì KHO có nhiều dòng, dẫn đến giảm tốc độ),

Cũng như cố gắng khai báo khai báo tường minh các biến, ví dụ như biến mảng thêm () sau tên biến

Cứ thử test sẽ thấy các điều đó

Hay lắm bạn vodoi2x, chỉ cần thay Value thành Value2 đã thấy tốc độ được cải thiện!