Xin code VBA tổng hợp dữ liệu tích lũy thành dữ liệu mỗi ngày

[Thanh Binh111]

Xin chào mọi người！

Mình có bài toán này nhờ mọi người giúp đỡ:

-Dữ liệu đầu vào của mình là mỗi ngày một file dữ liệu chứa danh sách tài khoản và số tiền nộp tích lũy của mỗi tài khoản tính tới hôm đó

-Sẽ có những trường hợp tài khoản có trong file ngày 1 nhưng ngày 3 không còn tồn tại và xuất hiện nữa

-Cũng sẽ có trường hợp tài khoản ngày 3 mới lập nên chỉ có trong danh sách ngày 3 về sau và không xuất hiện trong file ngày 1 và ngày 2

-File dữ liệu mỗi ngày lên tới hàng trăm ngàn dòng

Nhu cầu của mình là: thiết kế 1 file TỔNG, mỗi ngày đưa vào file dữ liệu danh sách nộp tiền tích lũy ngày đó, VBA tự động tính toán thành biểu như sheet “KET QUA MONG MUON”. Trong đó danh sách mỗi ngày phải cập nhật lại, đảm bảo tất cả tài khoản từ ngày 1 tới ngày hiện tại đều cần hiển thị và hiển thị số tiền nộp tương ứng mỗi ngày (tiền nộp trong ngày = tiền nộp tích lũy tới ngày đó – tiền nộp tích lũy tới ngày hôm qua). Đồng thời sau khi hoàn thành chỉ lưu lại sheet “KET QUA MONG MUON” là được.

Xin cảm ơn rất nhiều！

 

[ongke0711]

Ngâm cứu dùng Pivot table đi bạn.

 

[Thanh Binh111]

Ngâm cứu dùng Pivot table đi bạn.

Vâng, mình cảm ơn ạ

 

[HUONGHCKT]

Xin chào mọi người！

Mình có bài toán này nhờ mọi người giúp đỡ:

-Dữ liệu đầu vào của mình là mỗi ngày một file dữ liệu chứa danh sách tài khoản và số tiền nộp tích lũy của mỗi tài khoản tính tới hôm đó

-Sẽ có những trường hợp tài khoản có trong file ngày 1 nhưng ngày 3 không còn tồn tại và xuất hiện nữa

-Cũng sẽ có trường hợp tài khoản ngày 3 mới lập nên chỉ có trong danh sách ngày 3 về sau và không xuất hiện trong file ngày 1 và ngày 2

-File dữ liệu mỗi ngày lên tới hàng trăm ngàn dòng

Nhu cầu của mình là: thiết kế 1 file TỔNG, mỗi ngày đưa vào file dữ liệu danh sách nộp tiền tích lũy ngày đó, VBA tự động tính toán thành biểu như sheet “KET QUA MONG MUON”. Trong đó danh sách mỗi ngày phải cập nhật lại, đảm bảo tất cả tài khoản từ ngày 1 tới ngày hiện tại đều cần hiển thị và hiển thị số tiền nộp tương ứng mỗi ngày (tiền nộp trong ngày = tiền nộp tích lũy tới ngày đó – tiền nộp tích lũy tới ngày hôm qua). Đồng thời sau khi hoàn thành chỉ lưu lại sheet “KET QUA MONG MUON” là được.

Xin cảm ơn rất nhiều！

Hỏi chủ thớt: Có thích chơi giải pháp " bấm một phát" ăn ngay bằng VBA không?

 

[Cá ngừ F1]

Ngâm cứu dùng Pivot table đi bạn.

Chắc phải Power Query mới được ạ.

 

[ongke0711]

Chắc phải Power Query mới được ạ.

Tôi cũng chưa xem kỹ cái sheet dữ liệu nên cứ tưởng dữ liệu từng ngày được đổ về cùng một sheet. Nếu dữ liệu dạng dữ liệu mỗi ngày mỗi sheet kiểu này thì phải PQ mới gộp được.
@Thớt: Bạn chuẩn hóa dữ liệu đầu vào đi thì việc xử lý nó đơn giản hơn và tôi nghĩ nó không khó cho chuyện này
- Thêm cột Ngày cho dữ liệu.
- Dữ liệu 1 ngày vài trăm ngàn dòng một ngày mà nghĩ dùng Excel lưu trữ thì phải suy nghĩ chọn giải pháp lại đi nhe. Ví dụ lưu dữ liệu thô dưới dạng file Text (CSV) rồi dùng PQ xử lý.

 

[Cá ngừ F1]

Tôi cũng chưa xem kỹ cái sheet dữ liệu nên cứ tưởng dữ liệu từng ngày được đổ về cùng một sheet. Nếu dữ liệu dạng dữ liệu mỗi ngày mỗi sheet kiểu này thì phải PQ mới gộp được.
@Thớt: Bạn chuẩn hóa dữ liệu đầu vào đi thì việc xử lý nó đơn giản hơn và tôi nghĩ nó không khó cho chuyện này
- Thêm cột Ngày cho dữ liệu.
- Dữ liệu 1 ngày vài trăm ngàn dòng một ngày mà nghĩ dùng Excel lưu trữ thì phải suy nghĩ chọn giải pháp lại đi nhe. Ví dụ lưu dữ liệu thô dưới dạng file Text (CSV) rồi dùng PQ xử lý.

View attachment 295491

Đúng là tối thiểu chủ thớt cần thêm 1 trường ngày và Data, chứ không để dữ liệu ngày theo tên Sheet thế kia.
Tên Sheet cũng không tường minh "Du lieu ngay 1", ngay 1 này là của tháng nào? năm nào?

 

[Thanh Binh111]

Hỏi chủ thớt: Có thích chơi giải pháp " bấm một phát" ăn ngay bằng VBA không?

Được thế thì quá tốt bác ạ

Bài đã được tự động gộp: 7/10/23

Tôi cũng chưa xem kỹ cái sheet dữ liệu nên cứ tưởng dữ liệu từng ngày được đổ về cùng một sheet. Nếu dữ liệu dạng dữ liệu mỗi ngày mỗi sheet kiểu này thì phải PQ mới gộp được.
@Thớt: Bạn chuẩn hóa dữ liệu đầu vào đi thì việc xử lý nó đơn giản hơn và tôi nghĩ nó không khó cho chuyện này
- Thêm cột Ngày cho dữ liệu.
- Dữ liệu 1 ngày vài trăm ngàn dòng một ngày mà nghĩ dùng Excel lưu trữ thì phải suy nghĩ chọn giải pháp lại đi nhe. Ví dụ lưu dữ liệu thô dưới dạng file Text (CSV) rồi dùng PQ xử lý.

View attachment 295491

Chào bác, tại cái này nó liên quan đến lưu trình công việc nữa, thông thường mỗi ngày sẽ có người khác tải dữ liệu mỗi ngày gửi cho bên em, bên em cần theo đó để tổng hợp lại thành biểu hiển thị mỗi tài khoản tương ứng số tiền nạp mỗi ngày ạ, cảm ơn bác đã chia sẻ

Bài đã được tự động gộp: 7/10/23

Đúng là tối thiểu chủ thớt cần thêm 1 trường ngày và Data, chứ không để dữ liệu ngày theo tên Sheet thế kia.
Tên Sheet cũng không tường minh "Du lieu ngay 1", ngay 1 này là của tháng nào? năm nào?

Chào bác, mấy cái sheet kia là em thêm vào cho mọi người biết dữ liệu kiểu như thế nào, chứ trên thực tế mỗi ngày nó là các file khác nhau ạ.
Và em muốn mỗi ngày nhận được file tích lũy nạp tiền hôm đó, thì mình đưa nó vào file TỔNG HỢP để tổng hợp ra danh sách nộp tiền mỗi ngày ạ

 

[Cá ngừ F1]

Nếu mỗi ngày là 1 file khác nhau thì lại là một bài toán khác rồi đó bạn.
Nếu bạn lại đưa file vào Sheet thế này thì không thể giải quyết tận gốc vấn đề rồi.
Nếu có tổng hợp thì tổng hợp luôn từ các file, Power Query có thể làm được việc đó.

 

[Thanh Binh111]

Nếu mỗi ngày là 1 file khác nhau thì lại là một bài toán khác rồi đó bạn.
Nếu bạn lại đưa file vào Sheet thế này thì không thể giải quyết tận gốc vấn đề rồi.
Nếu có tổng hợp thì tổng hợp luôn từ các file, Power Query có thể làm được việc đó.

Vâng, mình sẽ nghiên cứu thêm về Power Query, cảm ơn chia sẻ của bạn!

 

[Thanh Binh111]

Nếu mỗi ngày là 1 file khác nhau thì lại là một bài toán khác rồi đó bạn.
Nếu bạn lại đưa file vào Sheet thế này thì không thể giải quyết tận gốc vấn đề rồi.
Nếu có tổng hợp thì tổng hợp luôn từ các file, Power Query có thể làm được việc đó.

Chào bạn, mình có nghiên cứu qua về Power Query nhưng giới hạn số dòng là tầm 1 triệu dòng, mà mỗi ngày file của mình đã lên tới gần 200 ngàn dòng, vậy thì chỉ đủ chứa khoảng 5 ngày, vậy có cách gì khắc phục vấn đề này không ạ?

 

[Cá ngừ F1]

Chào bạn, mình có nghiên cứu qua về Power Query nhưng giới hạn số dòng là tầm 1 triệu dòng, mà mỗi ngày file của mình đã lên tới gần 200 ngàn dòng, vậy thì chỉ đủ chứa khoảng 5 ngày, vậy có cách gì khắc phục vấn đề này không ạ?

Tốt nhất là bạn cho 6 file nguồn ví dụ lên đây xem sao bạn?
Dữ liệu cho vào Data model thì thoải mái, khi dữ liệu Paste xuống sheets thì chỉ theo "Tài khoản" thôi, mà tài khoản đâu đến được 1tr tài khoản.

 

[Thanh Binh111]

Tốt nhất là bạn cho 6 file nguồn ví dụ lên đây xem sao bạn?
Dữ liệu cho vào Data model thì thoải mái, khi dữ liệu Paste xuống sheets thì chỉ theo "Tài khoản" thôi, mà tài khoản đâu đến được 1tr tài khoản.

Vâng, để mình thử xem sao, cảm ơn bạn đã hướng dẫn!

 

[Cá ngừ F1]

Vâng, để mình thử xem sao, cảm ơn bạn đã hướng dẫn!

Nếu file cần bảo mật. Bạn up vài file. Trong file chứa vài bản ghi, quan trọng là file đúng cấu trúc là được

 

[Cá ngừ F1]

Nếu thực tế dữ liệu nguồn của bạn đang là như vậy.
Tôi dùng Power Query để bạn tham khảo. Nội dung này tôi học từ đúng bài 1 của Topic này của tác giả @ptm0412 , cũng có dùng thêm chút chức năng Unpipot để xử lý:

Tổng hợp (gộp) nhiều sheet của 1 file Excel, nhiều file trong folder bằng Power Query nâng cao

I. Tổng hợp (gộp) nhiều sheet trong 1 file Thông thường và với trình độ căn bản, khi muốn gộp (tổng hợp) nhiều sheet trên cùng 1 file Excel bằng Power query, các bạn tạo mỗi sheet 1 query con, sau đó Append chúng lại với nhau. Như vậy sẽ có nhiều query con mất công quản lý chúng. Các cách làm...

giaiphapexcel.com

Bạn Load 2 file này về máy
Mở File TongHop:
- Ở sheet Source, bạn sửa ô bôi vàng là đường dẫn, và tên file nguồn, tên file ở đây lưu ý save as thành đuôi xlsx.
- Ở Sheet KetQua, click phải chuột vào vùng dữ liệu chọn Refresh
Giờ dữ liệu nguồn bạn có bổ sung thêm Sheet mới, hoặc ở Sheet cũ bổ sung thêm bản ghi. Thì ở sheet KetQua chỉ cần Click phải chuột chọn Refresh là kết quả tự động cập nhật.
Các cột ở đây theo đúng tên từng Sheet, vì dữ liệu của bạn không có ngày.

 

[Thanh Binh111]

Nếu file cần bảo mật. Bạn up vài file. Trong file chứa vài bản ghi, quan trọng là file đúng cấu trúc là được

Chào bạn, theo hướng dẫn của bạn, mình dùng Data model và Pivot table tổng hợp thì được kết quả như hình 1, nhưng:

-Hiện kết quả chỉ hiển thị số tiền nạp TÍCH LŨY mỗi ngày, mình cần biến đổi thành số tiền nạp TRONG NGÀY (= số tiền nạp tích lũy tới hôm nay - số tiền nạp tích lũy tới hôm qua).

-Để xử lý vấn đề trên mình (biết một chút VBA) dùng 1 file khác copy kết quả biểu TÍCH LŨY như hình 1 về và dùng phép tính để biến đổi từ giá trị TÍCH LŨY thành giá trị TRONG NGÀY của mỗi ngày, cách này hơi bất tiện cũng như "lằng nhằng", như hình 2.

Vậy, cho mình hỏi:

1.Nếu mình muốn hiển thị kết quả nạp tiền TRONG NGÀY ngay luôn trong pivot table hình 1 thì có cách gì không và làm như thế nào?

2.Nếu vấn đề 1 không thể giải quyết thì có cách gì giải quyết tối ưu nhất không?

Mình xin gửi kèm theo 5 file dữ liệu mỗi ngày, nhờ bạn tham khảo hỗ trợ ạ, cảm ơn nhiều!

Hình 1



Hình 2

Bài đã được tự động gộp: 8/10/23

Nếu thực tế dữ liệu nguồn của bạn đang là như vậy.
Tôi dùng Power Query để bạn tham khảo. Nội dung này tôi học từ đúng bài 1 của Topic này của tác giả @ptm0412 , cũng có dùng thêm chút chức năng Unpipot để xử lý:

Tổng hợp (gộp) nhiều sheet của 1 file Excel, nhiều file trong folder bằng Power Query nâng cao

I. Tổng hợp (gộp) nhiều sheet trong 1 file Thông thường và với trình độ căn bản, khi muốn gộp (tổng hợp) nhiều sheet trên cùng 1 file Excel bằng Power query, các bạn tạo mỗi sheet 1 query con, sau đó Append chúng lại với nhau. Như vậy sẽ có nhiều query con mất công quản lý chúng. Các cách làm...

giaiphapexcel.com

Bạn Load 2 file này về máy
Mở File TongHop:
- Ở sheet Source, bạn sửa ô bôi vàng là đường dẫn, và tên file nguồn, tên file ở đây lưu ý save as thành đuôi xlsx.
- Ở Sheet KetQua, click phải chuột vào vùng dữ liệu chọn Refresh
Giờ dữ liệu nguồn bạn có bổ sung thêm Sheet mới, hoặc ở Sheet cũ bổ sung thêm bản ghi. Thì ở sheet KetQua chỉ cần Click phải chuột chọn Refresh là kết quả tự động cập nhật.
Các cột ở đây theo đúng tên từng Sheet, vì dữ liệu của bạn không có ngày.

Cảm ơn bạn đã hỗ trợ, nhưng vì dữ liệu mình mỗi ngày số dòng lên tới hàng trăm ngàn dòng tương đối lớn, nên mình hi vọng có thể dữ liệu mỗi ngày lưu ở 1 file, dữ liệu 1 tháng gồm 30 file sẽ lưu trong 1 thư mục, và dùng power query liên kết sau đó dùng pivot table để xử lý.
Note: mình có thể thêm 1 cột Ngày vào các file để phân biệt số liệu mỗi ngày.
Cảm ơn ạ!

 

[Cá ngừ F1]

Không biết có đúng ý bạn không
Tất cả các File nguồn kia bạn cho vào 1 Folder trên máy.
Download File này về. Ở Sheet Source, đổi đường dẫn đến Folder chứa các File nguồn trên (Ô D5)
Sang Sheet KetQua, click phải chuột vùng dữ liệu chọn Refresh.
Giờ bạn có thể copy file mới vào Folder, rồi Refresh như trên để cập nhật.

 

[Thanh Binh111]

Không biết có đúng ý bạn không
Tất cả các File nguồn kia bạn cho vào 1 Folder trên máy.
Download File này về. Ở Sheet Source, đổi đường dẫn đến Folder chứa các File nguồn trên (Ô D5)
Sang Sheet KetQua, click phải chuột vùng dữ liệu chọn Refresh.
Giờ bạn có thể copy file mới vào Folder, rồi Refresh như trên để cập nhật.

Chào bạn, cảm ơn bạn đã hỗ trợ!

Mình làm giống hướng dẫn của bạn nhưng sau khi Refresh thì nó báo lỗi như hình.

Vấn đề quan trọng hơn là, theo cách làm của bạn thì mới chỉ đến bước hiển thị danh sách hội viên và số tiền nạp tích lũy mỗi ngày, cái mình cần là phải biến đổi thành danh sách hội viên và số tiền nạp trong ngày của mỗi ngày (= số tiền nạp tích lũy tới hôm nay - số tiền nạp tích lũy tới hôm qua) ạ.

Trong tin nhắn mình rep lúc "Hôm nay lúc 17:38" thì mình đã làm được tới bước như bạn, hiện đang chưa tìm ra phương án "biến đổi thành danh sách hội viên và số tiền nạp trong ngày của mỗi ngày" tối ưu, (hiện thì sau khi mình tổng hợp đc danh sách tổng nạp tích lũy mỗi ngày thì mình dùng file khác copy sang và biến đổi, cách này phải làm thêm 1 file mới và tăng thêm thao tác và thời gian, mình hi vọng có thể biến đổi ngay trong file bảng Pivot luôn).

Cảm ơn bạn!

 

[Cá ngừ F1]

Khó hiểu nhỉ, tôi chạy và load các File của bạn ko vấn đề gì? Có thể 1 file nào đó đang khác cấu trúc với các File còn lại.
Quả thật là tôi làm ra đúng như KET QUA MONG MUỐN từ bài #1.
Còn lại bạn muốn cộng dồn thì thêm 1 cột tổng nữa là được.
Tôi cũng đang hơi rối để hiểu ý của bạn, có lẽ đợi thêm thành viên khác xem thế nào.
Cảm ơn bạn,

 

[Thanh Binh111]

Khó hiểu nhỉ, tôi chạy và load các File của bạn ko vấn đề gì? Có thể 1 file nào đó đang khác cấu trúc với các File còn lại.
Quả thật là tôi làm ra đúng như KET QUA MONG MUỐN từ bài #1.
Còn lại bạn muốn cộng dồn thì thêm 1 cột tổng nữa là được.
Tôi cũng đang hơi rối để hiểu ý của bạn, có lẽ đợi thêm thành viên khác xem thế nào.
Cảm ơn bạn,

Chào bạn, bài #1 thật ra do dữ liệu mình nhiều quá nên là mình đơn giản hóa vấn đề để mọi người hỗ trợ phương hướng, là do mình thuyết minh vấn đề trong #1 không chuẩn xác, mình xin phép nêu lại vấn đề của mình:

-Mình có dữ liệu đầu vào là số liệu tích lũy nạp tiền của mỗi tài khoản mỗi ngày, số liệu mỗi ngày lưu ở 1 file riêng biệt (do số lượng bản ghi tương đối lớn) lưu trong 1 folder.

-Mong muốn dữ liệu đầu ra: một biểu liệt kê danh sách tất cả tài khoản từng tồn tại và số tiền nạp mỗi ngày tương ứng của mỗi tài khoản.

Hiện theo hướng dẫn của bạn, dùng Power Query và Pivot Table thì mình đã hoàn thành đến bước Pivot Table liệt kê mỗi tài khoản và số tiền tích lũy tương ứng mỗi ngày, còn bước cuối cùng là chuyển đổi từ dữ liệu tiền nạp TÍCH LŨY thành tiền nạp PHÁT SINH TRONG NGÀY thì mình đang dùng VBA, cụ thể là mình tạo thêm 1 sheet "BIEN DOI" và dùng VBA để chuyển đổi.

Mình xin gửi file mình hiện đang làm lên, do dung lượng file lớn nên mình đã xóa kết nối PQ và chuyển dữ liệu sau khi PQ và Pivot thành giá trị, nhờ bạn cũng như mọi người cho mình lời khuyên để tối ưu hóa lưu trình tổng hợp này.

Xin cảm ơn ạ!