Phát triển bài toán tổng hợp xuất nhập tồn hàng hóa bằng các ngôn ngữ lập trình khác

Chia sẻ bởi:hands

★★★★★

‹ Phân bổ doanh số thỏa mãn 3... Cách tính nhập xuất tồn bằng VBA? ›

Quảng cáo

Gửi các anh/chị,
Từ khá lâu, khởi nguồn từ một [URL='www.giaiphapexcel.com/diendan/threads/thi-t%E1%BA%A1o-s%E1%BB%95-th-nxt-v%E1%BB%9Bi-t%E1%BB%91c-%C4%91%E1%BB%99-nhanh-nh%E1%BA%A5t-d%E1%BB%AF-li%E1%BB%87u-65-532-d%C3%B2ng.89723/page-10#post-561110']Cuộc thi tạo sổ tổ hợp nhập xuất tồn trong Excel tốc độ nhanh nhất của anh @Nguyễn Duy Tuân. Với việc tổng hợp offline (dữ liệu đã có trên sheet) thì Dictionary trong VBA đúng là vô địch về tốc độ.

Tuy nhiên, giờ đây với tiêu chí online, các dữ liệu đều được đưa lên mây. Do đó em có đưa dữ liệu file này lên OneDrive, File dữ liệu tại đường dẫn: [URL='onedrive.live.com/download?resid=9D22195809ABDE05%215236&authkey=AH8ldUa8sYEK9m4&em=2']onedrive.live.com/download?resid=9D22195809ABDE05%215236&authkey=AH8ldUa8sYEK9m4&em=2
File này gồm 3 sheet: (1) Mã HHDV; (2) Tồn đầu kỳ; (3) Phát sinh trong kỳ. File này anh @ptm0412 cấu trúc lại thành hơn 1tr dòng.

Sổ tổng hợp xuất nhập tồn em làm trong File Report bằng Power Query, get data trực tiếp từ đường dẫn trên.
Nguyên tắc tính các trường trong sổ (như bài gốc):
DateFrom (ô D1), DateTo (ô D2)
– Lượng Tồn đầu = lượng nhập với ngày < DateFrom – lượng xuất với ngày < DateFrom
– Lượng Nhập trong kỳ = lượng nhập với ngày >= DateFrom và ngày <= DateTo
– Lượng Xuất trong kỳ = lượng xuất với ngày >= DateFrom và ngày <= DateTo
– Lượng tồn cuối = Lượng Tồn đầu + Lượng Nhập trong kỳ – Lượng Xuất trong kỳ

2199

Quả thật, làm offline bằng Power Query cũng chậm không thể so sánh được với VBA trong bài toán này. Mà làm online thì cũng không nhanh. Như File em làm lấy cả năm thì mất khoảng 4 phút.

Gần đây, em thấy rất nhiều thành viên bàn luận sôi nổi về các ứng dụng lập trình cao cấp như Delphi/Python/Dax/PowerBI/SQL…
Nên em mạnh dạn đưa lại bài này, mong các thành viên có thể xử lý bằng các chương trình lập trình cao cấp trên để bài toán chạy nhanh hơn. Làm sao lấy dữ liệu từ onedrive (đường link như e đã viết ở trên), tính toán, dán xuống sheet như Form của báo cáo trong file excel.

Kể cả với bài PowerQuery này, chắc chắn Code cũng chưa tối ưu nên mới chậm vậy, em mong các thành viên hỗ trợ chỉnh sửa để nó nhanh hơn.

Em cảm ơn.

File trong link dưới đây là file có số dư từng tháng, tạo ra theo code của chủ đề "tạo file nhập xuất tồn 1 triệu dòng". Trong chủ đề đó nhờ tạo các bảng tồn từng tháng nên số tồn đầu báo cáo được tính từ bảng tồn gần nhất, giảm thiểu được vòng lặp và tốc độ rất nhanh: Xử lý 1 triệu dòng bằng VBA, ra báo cáo 13 ngàn dòng trong khoảng 3 – 3.5 giây.

link file data trên [URL='1drv.ms/x/s!Arrm69a6GMS5il4V0yN-GZgB5eDh']OneDrive,
link để kết nối Power query: [URL='onedrive.live.com/download?resid=B9C418BAD6EBE6BA%211374&authkey=AKLqnHtbkOGWJy0&em=2']onedrive.live.com/download?resid=B9C418BAD6EBE6BA!1374&authkey=AKLqnHtbkOGWJy0&em=2

Và cũng theo xu hướng online ( 🙂 🙂 )như @Cá ngừ F1 nói, tôi cũng thử làm bằng Power Query, tốc độ khoảng 1 phút. Trong đó cũng dùng những bảng số dư từng tháng như khi dùng VBA, đặc biệt query tồn đầu này là query động theo kỳ báo cáo, thậm chí là theo ngày đầu báo cáo để lấy bảng số dư gần nhất.
Phần code chính cũng theo thuật toán của VBA:
– Lấy tồn đầu gần nhất so với kỳ báo cáo (query TonDau)
– Tính nhập trừ xuất (phát sinh) trong khoảng từ ngày tồn đầu đến ngày đầu báo cáo (query DataPrevious)
– Lấy Tồn đầu gần nhất cộng phát sinh trước kỳ BC để ra tồn đầu kỳ báo cáo
– Tính nhập và xuất trong kỳ báo cáo (query DataCurrent)
– Tính tồn cuối kỳ.
– Lấy hết mặt hàng trong danh mục, nhưng lọc bỏ những mặt hàng không tồn và cũng không phát sinh, chỉ để lại những mặt hàng có tồn đầu và/ hoặc có phát sinh.

Tốc độ không ảnh hưởng khi chọn kỳ báo cáo đầu năm hay cuối năm, kỳ báo cáo ngắn vài ngày hay báo cáo cả năm, vì không sử dụng vòng lặp nên không giảm số lần lặp như VBA

Tôi thử làm offline trên Access. Query có SQL là:

Select dm.* , iif(dk.SL01 > 0,dk.SL01,0) +iif(pre.pssl > 0,pre.pssl,0) as SLdkBC,
iif(dk.ttien01>0,dk.ttien01,0)+ iif( pre.pstt>0,pre.pstt,0) as TTDkBC, dtps.sln,dtps.ttn, dtps.slx,dtps.ttx,
iif(SLdkBC>0,SLdkBC,0)+iif(dtps.sln>0,dtps.sln,0) - iif(dtps.slx,dtps.slx,0) as SLCky,
iif(TTDkBC>0,TTDkBC,0) + iif(dtps.ttn>0,dtps.ttn,0) - iif(dtps.ttx >0,dtps.ttx,0) as TTCky
from ( (danhmuc dm left join Ton1 dk on dm.MA_VLSPHH = dk.Ma01)
left join (select dt.MA_VLSPHH as Ma,sum(iif(dt.loai_phieu="N",dt.slg,- dt.slg)) as PSSL, 
    sum(iif(dt.loai_phieu="N",dt.THANH_TIEN,- dt.THANH_TIEN)) as PStt
    from data dt where dt.ngay_ct < dateserial(2013,2,1) group by dt.MA_VLSPHH ) as Pre  on pre.Ma=dm.MA_VLSPHH )
left join (select dt.MA_VLSPHH as Ma,sum(iif(dt.loai_phieu="N",dt.slg,0)) as sln,  sum(iif(dt.loai_phieu="N",dt.THANH_TIEN,0)) as ttn,
 sum(iif(dt.loai_phieu="X",dt.slg,0)) as slx,  sum(iif(dt.loai_phieu="X",dt.THANH_TIEN,0)) as ttx
    from data dt where dt.ngay_ct >= dateserial(2013,2,1) and dt.ngay_ct <= dateserial(2013,2,28) group by dt.MA_VLSPHH ) as dtps
     on dtps.Ma=dm.MA_VLSPHH

Trong đó DateFrom là dateserial(2013,2,1) và DateTo là dateserial(2013,2,28) (nguyên tháng 2)
Từ SQL view sửa DateTo thành ngày khác (thí dụ dateserial(2013,3,31), nhấn nút xem DataSheet view thì khoảng 2 giây.

Hình 1: SQL view, nhấn nút datasheet view
2202

Hình 2: Datasheet view

2201

www.giaiphapexcel.com/diendan/threads/ph%C3%A1t-tri%E1%BB%83n-b%C3%A0i-to%C3%A1n-t%E1%BB%95ng-h%E1%BB%A3p-xu%E1%BA%A5t-nh%E1%BA%ADp-t%E1%BB%93n-h%C3%A0ng-h%C3%B3a-b%E1%BA%B1ng-c%C3%A1c-ng%C3%B4n-ng%E1%BB%AF-l%E1%BA%ADp-tr%C3%ACnh-kh%C3%A1c.157974/