Tính tổng có nhiều điều kiện và ghi ra kết quả không vượt trần cho trước

[Binbo2020]

Mình có một bài toán tính tổng theo nhiều điều kiện trong cùng sheet (và điều kiện 2 là sheet max là 30 thì nếu tổng 20 điền 20 tổng 40 điền 30) và điền vào dòng đầu tiên phù hợp điều kiện; hiện mình đang điền bằng công thức, nhờ mọi người chỉ giúp xem có cách nào pivotable từ 2 bảng sang bảng như kết quả (để không cần viết công thức ở cột H,I do thực tế dữ liệu nhiều nên mình không thể làm công thức như trong bảng ví dụ được). hoặc bạn nào viết dùm mình cái code để ra luôn cột I không cần cột H .

 

[Phuocam]

để ra luôn cột I không cần cột H .

Gom 2 công thức thành 1:

=IF(COUNTIFS($A$3:A3,A3,$B$3:B3,B3,$D$3: D3,D3)=1,
MIN(VLOOKUP(D3,max!$A$3:$B$5,2,0),SUMIFS($G$3:$G$22,$A$3:$A$22,A3,$B$3:$B$22,B3,$D$3:$D$22,D3)),0)

 

[Binbo2020]

Gom 2 công thức thành 1:

=IF(COUNTIFS($A$3:A3,A3,$D$3: D3,D3)=1,MIN(VLOOKUP(D3,max!$A$3:$B$5,2,0),SUMIFS($G$3:$G$22,$A$3:$A$22,A3,$D$3:$D$22,D3)),0)

Dữ liệu của mình khoảng hơn 100k dòng nên dùng công thức không ổn bạn ạ. Tính dùng pivotable từ hai bảng nhưng xem hướng dẫn trên diễn đàn thì chỉ có 2 bảng cùng số cột, còn khác số cột không tìm được

 

[Phuocam]

Dữ liệu của mình khoảng hơn 100k dòng nên dùng công thức không ổn bạn ạ. Tính dùng pivotable từ hai bảng nhưng xem hướng dẫn trên diễn đàn thì chỉ có 2 bảng cùng số cột, còn khác số cột không tìm được

Bạn vui tính thiệt, dữ liệu hơn 100k dòng mà lưu được file .xls

 

[Binbo2020]

Bạn vui tính thiệt, dữ liệu hơn 100k dòng mà lưu được file .xls

hix mình tạo file vidu nên theo thói quen hay lưu xls thôi mà

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

Nếu dữ liệu ít thì mình cũng có thể dùng công thức này ra bảng kết quả luôn được mà =MIN(SUMIFS('Tong '!$G$3:$G$25;'Tong '!$A$3:$A$25;B$12;'Tong '!$D$3:$D$25;$A13);VLOOKUP($A13;max!$A$3:$B$5;2;0))

 

[VetMini]

Bạn vui tính thiệt, dữ liệu hơn 100k dòng mà lưu được file .xls

hờ-i-xờ, người ta dân xịn nên theo thói quen hay dùng dữ liệu khủng vậy mà.

Buồn thật, mình mỗi ngày xin bà xã cho từng ngàn gặm ổ bánh mì không, uống lý cà phê bí tất. Nghe thiên hạ nói chuyện toàn 100k và t không hà. Mấy con só này mỗi tháng mình mới thấy một lần lúc lần ký lĩnh lương, về tới nhà giao hết cho vợ, 29 ngày kế lại tiếp tục đếm số ngàn.

 

[HieuCD]

hờ-i-xờ, người ta dân xịn nên theo thói quen hay dùng dữ liệu khủng vậy mà.

Buồn thật, mình mỗi ngày xin bà xã cho từng ngàn gặm ổ bánh mì không, uống lý cà phê bí tất. Nghe thiên hạ nói chuyện toàn 100k và t không hà. Mấy con só này mỗi tháng mình mới thấy một lần lúc lần ký lĩnh lương, về tới nhà giao hết cho vợ, 29 ngày kế lại tiếp tục đếm số ngàn.

99% những người giao hết tiền lương cho vợ đều có quỹ đen, thậm chí có những bí mật riêng tư khó nói

 

[VetMini]

99% những người giao hết tiền lương cho vợ đều có quỹ đen, thậm chí có những bí mật riêng tư khó nói

Mấy người đó chưa nhìn thấy cảnh vợ miệng ru con tay xắc rau nấu cám lợn.
Một bên con đói oa oa đòi sữa mẹ, một bên lợn đói réo eng éc.

 

[Binbo2020]

Xin lỗi các bạn nhưng mình nghĩ bài mình đăng có gì sai đáng để cười không vậy, mình hay lưu xls là vì ở nhà dùng nhờ máy của người khác cài có office 2003 nên khi lưu ở máy công ty mình để về nhà thỉnh thoảng cần có thể xem được. Mình không biết thì mình hỏi và ngôn ngữ mình dùng cũng không có gì gọi là đang trêu đùa ai cả. Xin lỗi vì đã làm phiền

 

[VetMini]

Càng phân trần càng rối rắm.
Excel 2003 không thể chứa dữ liệu đến một trăm ka. Đã khoe là dữ liệu khủng mà còn thói quen là quen thế nào?

 

[phihndhsp]

Mình có một bài toán tính tổng theo nhiều điều kiện trong cùng sheet (và điều kiện 2 là sheet max là 30 thì nếu tổng 20 điền 20 tổng 40 điền 30) và điền vào dòng đầu tiên phù hợp điều kiện; hiện mình đang điền bằng công thức, nhờ mọi người chỉ giúp xem có cách nào pivotable từ 2 bảng sang bảng như kết quả (để không cần viết công thức ở cột H,I do thực tế dữ liệu nhiều nên mình không thể làm công thức như trong bảng ví dụ được). hoặc bạn nào viết dùm mình cái code để ra luôn cột I không cần cột H .

Nếu dữ liệu nhiều thì có thể dùng VBA để giải quyết. hoặc dùng Pivot sau đó thao tác vài bước cơ bản nó cũng ra được yêu cầu

 

[VetMini]

Nếu dữ liệu nhiều thì có thể dùng VBA để giải quyết. hoặc dùng Pivot sau đó thao tác vài bước cơ bản nó cũng ra được yêu cầu

Pivot không chịu nổi 100k dòng đâu.
Muốn thực sự nắm vững dữ liệu khủng thì phải dùng Data Model và Power Pivot.
Data Model dùng kỹ thuật SQL Server Express để tổng hợp số liệu cho nên rất hiệu quả.

 

[Binbo2020]

Càng phân trần càng rối rắm.
Excel 2003 không thể chứa dữ liệu đến một trăm ka. Đã khoe là dữ liệu khủng mà còn thói quen là quen thế nào?

Ý mình ở đây là khi gửi bài lên để hỏi mình hay lưu dạng xls để nếu có ai trả lời thì ở nhà mình cũng có thể mở ra xem và làm thử được vì khi làm file ví dụ chỉ có mấy dòng thôi.
Còn dữ liệu mình xuất từ phần mềm tổng hợp của Công ty ra 1 tháng có khoảng 10-12k dòng nên khi xuất cả năm nó lên nhiều thế. Nếu mình tách ra 12 tháng thành 12 sheet thì mỗi sheet độc lập khoảng 12k dòng thì có thể dùng pivot bình thường hoặc VBA không chứ hai cách Data Model và Power Pivot bạn nói mình không biết nó là gì.

 

[huuquy1196]

dữ liệu nhiều thế mình nghĩ đưa vào mảng vba là nhanh nhất

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

Nếu dữ liệu nhiều thì có thể dùng VBA để giải quyết. hoặc dùng Pivot sau đó thao tác vài bước cơ bản nó cũng ra được yêu cầu

pivot không chịu được dữ liệu lớn đến thế đâu

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

Ý mình ở đây là khi gửi bài lên để hỏi mình hay lưu dạng xls để nếu có ai trả lời thì ở nhà mình cũng có thể mở ra xem và làm thử được vì khi làm file ví dụ chỉ có mấy dòng thôi.
Còn dữ liệu mình xuất từ phần mềm tổng hợp của Công ty ra 1 tháng có khoảng 10-12k dòng nên khi xuất cả năm nó lên nhiều thế. Nếu mình tách ra 12 tháng thành 12 sheet thì mỗi sheet độc lập khoảng 12k dòng thì có thể dùng pivot bình thường hoặc VBA không chứ hai cách Data Model và Power Pivot bạn nói mình không biết nó là gì.

anh ấy nói đúng rồi bạn. Dùng Data Model là chuẩn nhất rồi với những dữ liệu lớn như vậy để xử lý cũng như quản lý. nhưng nếu chỉ đơn thuần tính tổng thì mình nghĩ dùng Vba là giải pháp đơn giản nhất

 

[phihndhsp]

Ý mình ở đây là khi gửi bài lên để hỏi mình hay lưu dạng xls để nếu có ai trả lời thì ở nhà mình cũng có thể mở ra xem và làm thử được vì khi làm file ví dụ chỉ có mấy dòng thôi.
Còn dữ liệu mình xuất từ phần mềm tổng hợp của Công ty ra 1 tháng có khoảng 10-12k dòng nên khi xuất cả năm nó lên nhiều thế. Nếu mình tách ra 12 tháng thành 12 sheet thì mỗi sheet độc lập khoảng 12k dòng thì có thể dùng pivot bình thường hoặc VBA không chứ hai cách Data Model và Power Pivot bạn nói mình không biết nó là gì.

Nếu xuất ra từng tháng tầm 20,000 dòng. thật ra 20,000 đó là chi tiết, nếu bạn không muốn dùng công cụ có sẵn thì bạn có thể viết bằng VBA. sử dụng Ado để tổng hợp từng file lại và sau đó gộp chung dữ liệu đã xử lý vào file tổng, lúc đó dữ liệu đã gom lại không nhiều lắm đâu, sau đó có thể dùng vba để thao tác theo yêu cầu. ở đây tôi nói đến trường hợp bạn không muốn dùng công cụ có sẵn nha

 

[VetMini]

Nếu xuất ra từng tháng tầm 20,000 dòng. thật ra 20,000 đó là chi tiết, nếu bạn không muốn dùng công cụ có sẵn thì bạn có thể viết bằng VBA. sử dụng Ado để tổng hợp từng file lại và sau đó gộp chung dữ liệu đã xử lý vào file tổng, lúc đó dữ liệu đã gom lại không nhiều lắm đâu, sau đó có thể dùng vba để thao tác theo yêu cầu. ở đây tôi nói đến trường hợp bạn không muốn dùng công cụ có sẵn nha

Thớt nói chuyện theo kiểu không phân biệt xlsx hay xls như vậy thì VBA là đùa với lửa.
ADO với lượng lớn như vậy đòi hỏi dữ liệu phải chuẩn. Người sử dụng phải nắm rất vững các trường hợp ngoại lệ mới có thể kiểm soát được dữ liệu.

Mà tôi đã nói bao nhiêu lần rồi. Với dữ liệu khủng thì cốt kiếc Ây Đi Ô gì chỉ là biện pháp chắp vá.
Làm việc thường xuyên với dữ liệu trên chục ngàn dòng thì phải học Power Query, Power Pivot, và nhóm hàm DAX. Không có con đường nào khác.

 

[Binbo2020]

pivot không chịu được dữ liệu lớn đến thế đâu

Sao lại không được hả bạn, mình không biết cách nào hay lên pivot tổng theo điều kiện rồi làm thủ công cái bảng phụ sang cạnh để tính mình vẫn được mà nhỉ có thấy sai đâu (130.000 dòng)
Mình không biết việc tổng 12 tháng vào một sheet với 100.000 khi viết VBA lại phức tạp vậy, vì mình đang tư duy theo hướng pivot từ 2 bảng để lấy giá trị min.
File hơn 14mb mình không đính kèm trực tiếp được
https://drive.google.com/open?id=1cG5ZBJruisYWaOv9Q3mwGi-zNVAm6-nG

 

[phihndhsp]

Mà tôi đã nói bao nhiêu lần rồi. Với dữ liệu khủng thì cốt kiếc Ây Đi Ô gì chỉ là biện pháp chắp vá.

Nhìn vào dữ liệu người ta gởi bên dưới mới hiểu được là như thế nào rồi đó thầy hihi. đúng là làm gì cũng thành chắp vá không bao giờ xử lý trọn vẹn được hihi

 

[truongvu317]

Làm việc thường xuyên với dữ liệu trên chục ngàn dòng thì phải học Power Query, Power Pivot, và nhóm hàm DAX. Không có con đường nào khác.

Trời! Sư phụ Vẹt nói làm cháu bị hút hồn mất tiêu rùi, cháu muốn nâng khả năng của mình lên, sư phụ phụ đạo cháu nhóe!

 

[VetMini]

Muốn gọi tôi là phụ thì cứ gọi đức phụ hoặc phụ đó phụ đê. Chứ mấy cái chức sư phụ chả ăn được gì, tôi chán lắm.