Tạo sổ TH NXT với tốc độ nhanh nhất, dữ liệu hơn 1 triệu dòng.

Liên hệ QC

ptm0412

Bad Excel Member
Thành viên BQT
Administrator
Tham gia
4/11/07
Bài viết
13,762
Được thích
36,257
Donate (Momo)
Donate
Giới tính
Nam
Nghề nghiệp
Consultant
Topic này chưa nên khép lại (khà khà)

Hiện tại tôi đã tạo dữ liệu mẫu chuẩn hơn:
- Ngày liên tục hơn
- Ngày theo thứ tự và rải ra trong khoảng từ 01/01/2013 đến 31/12/2013
- Danh mục 1.326 dòng
- Data 1.048.000 dòng

1. Test code và so sánh với dũ liệu cũ 65 ngàn dòng:


Tôi dùng lại thuật toán cũ, không sửa chút nào, trừ việc lấy vùng dữ liệu và vùng danh mục dài ra thôi. Kết quả: Dic nhanh hơn Collection.





Ngoài ra hiệu quả của Dic cũng cao hơn: thời gian chạy chỉ tăng 10.4 lần so với cũ, trong khi thời gian chạy collection gấp gần 21 lần so với cũ.

File nặng quá nên đang up lên mediafire: http://www.mediafire.com/view/tc45crxg0c656gz/MillionRow-VBA-Dictionary.xlsm

2. Thêm 1 bảng số dư đầu kỳ (đầu năm 2013) và test với cả việc có số dư đầu kỳ.
Hiện tôi đang dùng thuật toán cũ lấy thêm số dư đầu kỳ, chưa thay đồi thuật toán (tôi đã đưa ý tưởng thay đổi thuật toán lên rồi). KHi có kết quả tôi sẽ thông báo.

Mọi người thử tìm cách tính thêm số dư đầu kỳ vào và cho biết tốc độ ra sao.
 
Cảm ơn anh Hậu, e đọc tài liệu và bài tập thực hành vẫn còn lơ tơ mơ quá ạ.

Tỷ tỷ mắng chuẩn, ngày báo cáo cần phải lấy từ sheet báo cáo mới cơ động, ko thể lấy từ Data, e sẽ sửa ngay, sửa ngay.
Sau khi query 5 table thì nên dùng power pivot để tính, anh thử thấy nhanh hơn hẳn, CPU cũng chỉ hoạt động tới 50%.
 
Upvote 0
Sau khi query 5 table thì nên dùng power pivot để tính, anh thử thấy nhanh hơn hẳn, CPU cũng chỉ hoạt động tới 50%.
Power pivot liệu có show hết được cả bảng danh mục hàng hoá lên ko anh? Như có tỷ tỷ góp ý, những mặt hàng ko phát sinh j sẽ ko lên báo cáo.
 
Upvote 0
Anh tưởng nhập xuất tồn thì phải báo cáo tất chứ, những mặt hàng không phát sinh thì vẫn "tồn" mà. Nhưng thôi tỷ tỷ đã nói là đúng, mình dân ngoại đạo phải tiếp thu học hỏi. Thêm cột phụ cộng mấy số nhập xuất xem có bằng 0 không rồi lọc. Danh mục vẫn xuất được mà.
 

File đính kèm

  • MillionRow-VBA-Dictionary-PQ_OnlineFromOneDrive - Copy.xlsm
    3.9 MB · Đọc: 23
Upvote 0
Power pivot liệu có show hết được cả bảng danh mục hàng hoá lên ko anh? Như có tỷ tỷ góp ý, những mặt hàng ko phát sinh j sẽ ko lên báo cáo.
Power query view trước, nên mọi tính toán nó sẽ load lại hết nên nó rất phụ thuộc vào connect, dùng Sum mà nó còn delay khá lâu, đối vối dữ liệu như vầy dùng Dax+modeling (power pivot) là chuẩn.

Anh tưởng nhập xuất tồn thì phải báo cáo tất chứ, những mặt hàng không phát sinh thì vẫn "tồn" mà. Nhưng thôi tỷ tỷ đã nói là đúng, mình dân ngoại đạo phải tiếp thu học hỏi. Thêm cột phụ cộng mấy số nhập xuất xem có bằng 0 không rồi lọc. Danh mục vẫn xuất được mà.
Bạn dùng Power pivot tạo relationship sao còn tạo calculated column chi cho load nặng, nếu dùng mọi tính toán nên dùng mearsure, có relationship mà bạn dùng earliervà lookupvalue là hạn chế tốc độ rồi, với cái nữa là nên tạo các table key để hạn chế filter các bảng 1 triệu dòng và vài chục triệu
Một cách dùng power pivot (mượn file của bạn) , khi thay đổi thời gian load table thời gian không load lại toàn bộ source (do source đã được import), máy tôi load tầm 1s xong (tính từ lần refresh thứ 2)
1606528670287.png
 

File đính kèm

  • MillionRow-VBA-Dictionary.xlsx
    3.8 MB · Đọc: 40
Upvote 0
Anh tưởng nhập xuất tồn thì phải báo cáo tất chứ, những mặt hàng không phát sinh thì vẫn "tồn" mà. Nhưng thôi tỷ tỷ đã nói là đúng, mình dân ngoại đạo phải tiếp thu học hỏi. Thêm cột phụ cộng mấy số nhập xuất xem có bằng 0 không rồi lọc. Danh mục vẫn xuất được mà.
Mặt hàng có tồn không phát sinh vẫn lên báo cáo
Mặt hàng không tồn mà có phát sinh cũng lên báo cáo
Chỉ có mặt hàng không tồn cũng không phát sinh mới không lên báo cáo. Những mặt hàng không tồn không phát sinh lâu ngày coi như mặt hàng chết sẽ bị xoá hoặc đánh dấu xoá khỏi danh mục.
máy mình mở lên giờ bị lỗi này
mấy cái API
File này không chạy trên Excel 64 bit Thầy ơi, em dùng Excel 2013
Xoá hết module đó luôn cũng được, module kia thì chỉ giữ lại sub Lapso, các sub kia xoá luôn. Muốn ghi thời gian thì tự viết vài dòng lệnh Msgbox thời gian. Chủ đề này khá lâu rồi, bây giờ @Cá ngừ F1 đào lại chạy Power query nên không cần code nữa đâu.
 
Upvote 0
Power query view trước, nên mọi tính toán nó sẽ load lại hết nên nó rất phụ thuộc vào connect, dùng Sum mà nó còn delay khá lâu, đối vối dữ liệu như vầy dùng Dax+modeling (power pivot) là chuẩn.
Bạn dùng Power pivot tạo relationship sao còn tạo calculated column chi cho load nặng, nếu dùng mọi tính toán nên dùng mearsure, có relationship mà bạn dùng earliervà lookupvalue là hạn chế tốc độ rồi, với cái nữa là nên tạo các table key để hạn chế filter các bảng 1 triệu dòng và vài chục triệu
Một cách dùng power pivot (mượn file của bạn) , khi thay đổi thời gian load table thời gian không load lại toàn bộ source (do source đã được import), máy tôi load tầm 1s xong (tính từ lần refresh thứ 2)
Như đã nói bên chủ đề bài thực hành, nhờ bạn @excel_lv1.5 khi nào rảnh viết tài liệu về DAX cho mọi người học hỏi. Power query có thế mạnh của nó nhưng phải phối hợp cùng anh em của nó là Data model và DAX mới phát huy sức mạnh tuyệt đỉnh. Cám ơn bạn trước.
 
Upvote 0
Như đã nói bên chủ đề bài thực hành, nhờ bạn @excel_lv1.5 khi nào rảnh viết tài liệu về DAX cho mọi người học hỏi. Power query có thế mạnh của nó nhưng phải phối hợp cùng anh em của nó là Data model và DAX mới phát huy sức mạnh tuyệt đỉnh. Cám ơn bạn trước.
Nên tổ chức 1 buổi thảo luận (trao đổi) online cho mọi người hợp sức cùng hiểu và phát triển.
Vì ngày càng nhiều bài viết cần xử lý dữ liệu nhiều (lớn) - và ứng dụng Power pivot, query- làm người dùng phải quen và buộc làm theo sự chuẩn hóa về dữ liệu hoặc biết học cách chuẩn hóa dữ liệu hơn
 
Upvote 0
Web KT
Back
Top Bottom