Thay thế nhiều sumifs thành 1 công thức khác?

[Hoàng Nhật Phương]

Xin chào các bạn,
Như tiêu đề OT đã nêu, hiện OT đang làm thao tác sum(sumif1,sumif2,sumif3...)
Nhờ các bạn giúp đỡ OT công thức khác thay công thức này với ạ.

 

[huonglien1901]

Xin chào các bạn,
Như tiêu đề OT đã nêu, hiện OT đang làm thao tác sum(sumif1,sumif2,sumif3...)
Nhờ các bạn giúp đỡ OT công thức khác thay công thức này với ạ.

Bạn có thể giải thích ở 2 cột Sumifs1 và Sumifs2 được không?

 

[Hoàng Nhật Phương]

Bạn có thể giải thích ở 2 cột Sumifs1 và Sumifs2 được không?

Cảm ơn bạn đã quan tâm, 2 cột tổng này có chỗ nhiều cột gần nhau cộng lại nên thay vì sum từng cột thì có thể sum luôn một vùng được không?

 

[VetMini]

Thử dùng Pivot Table với Slicers

 

[Hoàng Nhật Phương]

Thử dùng Pivot Table với Slicers

Con chào Bác,nhứt đựn phải là công thức Bác ơi

 

[huonglien1901]

Cảm ơn bạn đã quan tâm, 2 cột tổng này có chỗ nhiều cột gần nhau cộng lại nên thay vì sum từng cột thì có thể sum luôn một vùng được không?

Phải vậy không?

 

[Ba Tê]

Cảm ơn bạn đã quan tâm, 2 cột tổng này có chỗ nhiều cột gần nhau cộng lại nên thay vì sum từng cột thì có thể sum luôn một vùng được không?

Thử công thức này cho ô B2 coi sao:

=SUM(OFFSET(Data!$B$1;MATCH($A2;Data!$A$2:$A$11962;0);;COUNTIF(Data!$A$2:$A$11962;$A2);4))

Có bạn @VetMini ở đây, dùng Offset ngại quá.

 

[Phuocam]

Con chào Bác,nhứt đựn phải là công thức Bác ơi

Hoặc thử:

1. Liền nhau:

=SUMPRODUCT((Data!$A$2:$A$10000=A2)*Data!$B$2:$E$10000)

2. Vùng không liền nhau:

=SUMPRODUCT((Data!$A$2:$A$10000=A2)*((Data!$B$2:$B$10000)+(Data!$C$2:$C$10000)+(Data!$D$2:$D$10000)+(Data!$E$2:$E$10000)))

 

[dazkangel]

Làm bằng offset là khá ổn rồi:

B2=SUM(SUMIF(Data!A$2:A$100000,A2,OFFSET(Data!B$2,,{0,1,2,3})))
C2=SUM(SUMIF(Data!A$2:A$100000,A2,OFFSET(Data!C$2,,{0,1,2,3,4})))

 

[VetMini]

...Có bạn @VetMini ở đây, dùng Offset ngại quá.

Phần lớn các Offset đều có thể thay bằng Index.
ví dụ:
Thay vì Offset(A1, , 2)
Dùng Index(A1:C1, , 3)

 

[Hoàng Nhật Phương]

Thử công thức này cho ô B2 coi sao:

=SUM(OFFSET(Data!$B$1;MATCH($A2;Data!$A$2:$A$11962;0);;COUNTIF(Data!$A$2:$A$11962;$A2);4))

Có bạn @VetMini ở đây, dùng Offset ngại quá.

Con chào Thầy ạ,Thầy xài công thức công thức hay không kém gì VBA luôn, con cảm ơn Thầy ạ.

Hoặc thử:

1. Liền nhau:

=SUMPRODUCT((Data!$A$2:$A$10000=A2)*Data!$B$2:$E$10000)

2. Vùng không liền nhau:

=SUMPRODUCT((Data!$A$2:$A$10000=A2)*((Data!$B$2:$B$10000)+(Data!$C$2:$C$10000)+(Data!$D$2:$D$10000)+(Data!$E$2:$E$10000)))

Cảm ơn Bạn @phuocam nhiều ạ, OT cũng loay hoay mãi với SUMPRODUCT, khi nãy còn viết thế này mà không ra:
=SUMPRODUCT(Data!$A$2:$A$10000=A2,Data!$B$2:$E$10000)
Nhờ bạn mà OT đã hiểu hơn về SUMPRODUCT rồi hihi

Làm bằng offset là khá ổn rồi:

B2=SUM(SUMIF(Data!A$2:A$100000,A2,OFFSET(Data!B$2,,{0,1,2,3})))
C2=SUM(SUMIF(Data!A$2:A$100000,A2,OFFSET(Data!C$2,,{0,1,2,3,4})))

Cảm ơn Bạn @dazkangel rất nhiều công thức của bạn rất mới mẻ với OT,,

 

[Ba Tê]

Phần lớn các Offset đều có thể thay bằng Index.
ví dụ:
Thay vì Offset(A1, , 2)
Dùng Index(A1:C1, , 3)

Ở đây tôi không biết làm sao dùng INDEX() để xác định được vùng SUM() của B2 (điều kiện 01/01/2020) là bắt đầu từ B2 sheets Data, cao 992 dòng, rộng 4 cột.
Tương tự vùng SUM() của B13 là bắt đầu từ B10883, cao 1080 dòng, rộng 4 cột.
Mỗi công thức chỉ SUM() 1 vùng xác định chứ không muốn "chơi" hết cả 11962 dòng.

 

[VetMini]

Nếu tôi làm công thức bài này thì tôi đặt cả bảng bên Data vào name động.
Mà bài này nếu làm đúng thì cái bảng Data nên cho vào Table.
(Và làm chuyên nghiêp thì có trên 10 ngàn dòng thế này là Data Model và Power Pivot. Nhưng bài #5 thớt đã nói là công thức nên tôi sẽ không nhắc tới nữa.)

 

[Hoàng Nhật Phương]

Nếu tôi làm công thức bài này thì tôi đặt cả bảng bên Data vào name động.
Mà bài này nếu làm đúng thì cái bảng Data nên cho vào Table.

Bác ơi,dữ liệu có bao nhiêu nó xuất ra đổ xuống sheet bây nhiêu Bác, mà con cũng ít sử dụng table hình như Table nặng hơn là dữ liệu thường thì phải ạ.
Công thức của Thầy @Ba Tê và Bạn @dazkangel sử dụng sum + offset hình như nhe hơn là sử dụng SUMPRODUCT phải không Bác @VetMini,ah con thấy SUMPRODUCT sẽ bị lỗi nếu lấy cả vùng tiêu đề (text)

 

[VetMini]

Tôi không tin là Table nặng hơn bảng thường. Nếu có nặng thì nó làm cho file "nặng ký" hơn (lớn hơn).
Table là một xảo thuật (trick) khéo léo của Microsoft để xếp dữ liệu vào đúng chỗ, dễ truy cập, dễ thống kê.
Sở dĩ Table không lên đỉnh cao là vì khi ra đến phiên bản 2016 thì Microsoft đã khéo léo đưa vào Data Model. Data Model xếp dữ liệu theo dạng chuẩn để có thể sử dụng kỹ thuật của SQL Server Express cho nên truy vấn và thống kê hết sức hiệu quả (trước Power Query thì Data Model là cách duy nhất để có thể VLookup bảng hàng triệu dòng).