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

Cá ngừ F1 · 8/10/21

Gửi các anh/chị,
Từ khá lâu, khởi nguồn từ một 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: https://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ỳ

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.

ptm0412 · 8/10/21

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 OneDrive,
link để kết nối Power query: https://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

excel_lv1.5 · 9/10/21

Cá ngừ F1 đã viết:
Gửi các anh/chị,
Từ khá lâu, khởi nguồn từ một 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: https://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ỳ

View attachment 267396

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.

Tôi làm lại bằng power query refresh khoảng hơn 1 phút thôi, Dax/PowerBI nó giống power pivot tôi có làm rồi, Power query nó là dạng query ra kết quả luôn, nên thời gian của nó sẽ bằng thời gian load dữ liệu+thời gian tính toán, mà phần lớn là load dữ liệu nên power query không hề chậm, ví dụ bạn để dữ liệu trên onedrive thì nó sẽ phụ thuộc vào mạng, thì dùng ngôn ngữ hay tool nào thì cũng phải load load theo giới hạn của mạng thôi, còn với bài này thì nên dùng power pivot (không cần làm mới source), power query thì nên sử dụng khi cần real time (làm mới source liên tục). Python thì VBA chậm hơn khá nhiều dữ liệu càng lớn thì chênh lệch càng cao, tôi chạy với bài này mất khoảng 1.1s (0.7s connect dữ liệu, 0.4s tính +xuất excel). Python get dữ liệu từ onedrive được nhưng nó phụ thuộc vào mạng, nên tôi làm local để so với VBA luôn

Maika8008 · 9/10/21

Vụ này thì VBA thua chắc về tốc độ rồi.

Cá ngừ F1 · 9/10/21

excel_lv1.5 đã viết:
Tôi làm lại bằng power query refresh khoảng hơn 1 phút thôi, Dax/PowerBI nó giống power pivot tôi có làm rồi, Power query nó là dạng query ra kết quả luôn, nên thời gian của nó sẽ bằng thời gian load dữ liệu+thời gian tính toán, mà phần lớn là load dữ liệu nên power query không hề chậm, ví dụ bạn để dữ liệu trên onedrive thì nó sẽ phụ thuộc vào mạng, thì dùng ngôn ngữ hay tool nào thì cũng phải load load theo giới hạn của mạng thôi, còn với bài này thì nên dùng power pivot (không cần làm mới source), power query thì nên sử dụng khi cần real time (làm mới source liên tục). Python thì VBA chậm hơn khá nhiều dữ liệu càng lớn thì chênh lệch càng cao, tôi chạy với bài này mất khoảng 1.1s (0.7s connect dữ liệu, 0.4s tính +xuất excel). Python get dữ liệu từ onedrive được nhưng nó phụ thuộc vào mạng, nên tôi làm local để so với VBA luôn View attachment 267403

Python mà offline tốc độ gớm thật.
Anh up file power query cho mọi người tham khảo M code với ạ.

Maika8008 · 9/10/21

Cá ngừ F1 đã viết:
Python mà offline tốc độ gớm thật.
Anh up file power query cho mọi người tham khảo M code với ạ.

Bạn cho tôi hỏi: để tải được cái file ấy dùng code VBA nào?

Cá ngừ F1 · 9/10/21

Maika8008 đã viết:
Bạn cho tôi hỏi: để tải được cái file ấy dùng code VBA nào?

Click thẳng vào cái link đó là download file về mà nhỉ? Đưa link đó vô VBA không được hả anh?

befaint · 9/10/21

Maika8008 đã viết:
Bạn cho tôi hỏi: để tải được cái file ấy dùng code VBA nào?

Nếu link trỏ tới đích là file luôn thì dùng api urldownloadtofile.

ongke0711 · 9/10/21

Cá ngừ F1 đã viết:
Từ khá lâu, khởi nguồn từ một 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 độ.

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.

Ngoài lề Excel một chút, tôi xử lý offline bằng Access Query thì mất khoảng 17s, nếu xuất ra Excel luôn thì 21s.

Cá ngừ F1 · 9/10/21

ongke0711 đã viết:
Ngoài lề Excel một chút, tôi xử lý offline bằng Access Query thì mất khoảng 17s, nếu xuất ra Excel luôn thì 21s.

View attachment 267408

View attachment 267409

Query trên access quá nhanh đó anh ạ. PowerQuery trực tiếp trên excel mất khoảng 1 phút.

ptm0412 · 9/10/21

ongke0711 đã viết:
Ngoài lề Excel một chút, tôi xử lý offline bằng Access Query thì mất khoảng 17s, nếu xuất ra Excel luôn thì 21s.

Theo tôi nghĩ thì query đang có sẵn, và tính toán sẵn, thời gian 21s chủ yếu là xuất ra excel. Nếu test trực tiếp trên Access thì nhanh hơn nhiều chứ không phải 17s:
- Mở query tại access (datasheet view)
- Code: Thay tham số ngày bắt đầu và/ hoặc ngày kết thúc, requery, tính giờ

Maika8008 · 9/10/21

Cá ngừ F1 đã viết:
Click thẳng vào cái link đó là download file về mà nhỉ? Đưa link đó vô VBA không được hả anh?

Tưởng phải download để xử lý bằng vba (và các code search trên mạng đều không tải được nếu không có url gồm tên file tường minh) nhưng sau đó tôi thử mở trực tiếp bằng Workbooks.Open thì được.

Cá ngừ F1 · 9/10/21

Maika8008 đã viết:
Tưởng phải download để xử lý bằng vba (và các code search trên mạng đều không tải được nếu không có url gồm tên file tường minh) nhưng sau đó tôi thử mở trực tiếp bằng Workbooks.Open thì được.

Vâng anh,
Nếu workbooks.open về, dùng ADO để get data về xong chạy, không biết có nhọc không?

thuyyeu99 · 9/10/21

Cá ngừ F1 đã viết:
Vâng anh,
Nếu workbooks.open về, dùng ADO để get data về xong chạy, không biết có nhọc không?

Bạn thử là biết mà
Còn thao tác với file của google hay microsoft thì phải có api của hãng theo cú pháp của tùy công ty quy định để bảo mật.

Cá ngừ F1 · 9/10/21

thuyyeu99 đã viết:
Bạn thử là biết mà

Tôi thấy quãng đường đi là khá xa nếu dùng ADO. Tôi thấy anh có dùng Delphi, anh thử online xem sao anh?

thuyyeu99 · 9/10/21

Cá ngừ F1 đã viết:
Tôi thấy quãng đường đi là khá xa nếu dùng ADO. Tôi thấy anh có dùng Delphi, anh thử online xem sao anh?

Mình thì chắc từ hôm qua đến tuần sau chắc không có thời gian rồi, đang làm cái phổ cập

excel_lv1.5 · 9/10/21

Cá ngừ F1 đã viết:
Python mà offline tốc độ gớm thật.
Anh up file power query cho mọi người tham khảo M code với ạ.

Đây bạn, chủ yếu dùng object trên thanh công cụ là đủ không code nhiều!

Maika8008 · 9/10/21

Cá ngừ F1 đã viết:
Vâng anh,
Nếu workbooks.open về, dùng ADO để get data về xong chạy, không biết có nhọc không?

À! Bạn đặt tên trường Số lượng và Thành tiền của bảng TonDK có dấu tiếng Việt nên hơi khó cho việc truy vấn SQL.

Cá ngừ F1 · 9/10/21

excel_lv1.5 đã viết:
Tôi làm lại bằng power query refresh khoảng hơn 1 phút thôi, Dax/PowerBI nó giống power pivot tôi có làm rồi, Power query nó là dạng query ra kết quả luôn, nên thời gian của nó sẽ bằng thời gian load dữ liệu+thời gian tính toán, mà phần lớn là load dữ liệu nên power query không hề chậm, ví dụ bạn để dữ liệu trên onedrive thì nó sẽ phụ thuộc vào mạng, thì dùng ngôn ngữ hay tool nào thì cũng phải load load theo giới hạn của mạng thôi, còn với bài này thì nên dùng power pivot (không cần làm mới source), power query thì nên sử dụng khi cần real time (làm mới source liên tục). Python thì VBA chậm hơn khá nhiều dữ liệu càng lớn thì chênh lệch càng cao, tôi chạy với bài này mất khoảng 1.1s (0.7s connect dữ liệu, 0.4s tính +xuất excel). Python get dữ liệu từ onedrive được nhưng nó phụ thuộc vào mạng, nên tôi làm local để so với VBA luôn View attachment 267403

Như code này của anh @excel_lv1.5 thì phải save as các sheet thành File CSV anh nhỉ? có thể Get trực tiếp File excel không anh?

ptm0412 · 9/10/21

ptm0412 đã viết:
Theo tôi nghĩ thì query đang có sẵn, và tính toán sẵn, thời gian 21s chủ yếu là xuất ra excel. Nếu test trực tiếp trên Access thì nhanh hơn nhiều chứ không phải 17s:

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

SQL:

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

Hình 2: Datasheet view

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

( ͡° ͜ʖ ͡°)

File đính kèm

Bad Excel Member

File đính kèm

Thành viên tích cực

Thành viên gạo cội

( ͡° ͜ʖ ͡°)

Thành viên gạo cội

( ͡° ͜ʖ ͡°)

|||||||||||||

Thành viên gắn bó

( ͡° ͜ʖ ͡°)

Bad Excel Member

Thành viên gạo cội

( ͡° ͜ʖ ͡°)

Trùm Nhiều Chuyện

( ͡° ͜ʖ ͡°)

Trùm Nhiều Chuyện

Thành viên tích cực

File đính kèm

Thành viên gạo cội

( ͡° ͜ʖ ͡°)

Bad Excel Member

Facebook

Group