Tổng hợp các hàm tính tổng theo điều kiện từ cơ bản đến nâng cao

[hrspring.tides]

Chủ đề này xin giới thiệu với [VISITOR][/VISITOR] cách sử dụng các hàm tổng hợp theo điều kiện từ đơn giản đến nâng cao.

1. Hàm SUMIFS (Excel 2007+): Tính tổng các ô thỏa nhiều điều kiện chỉ định
   
   Cú pháp:
   

   =SUMIFS(Vùng cần tính tổng, Vùng 1, điều kiện 1, Vùng 2, điều kiện 2,.........,Vùng điều kiện n, điều kiện n)

   
   
2. Hàm SUMPRODUCT: trả về tổng của tổng của các dải ô hoặc mảng tương ứng
   Cú pháp:
   

   =SUMPRODUCT((Vùng 1=dk1)*(Vùng 2=dk2)*(..........)*(Vùng n=dkn))

Ví dụ:

Chi tiết, [VISITOR][/VISITOR] có thể tham khảo file đính kèm.

Nội dung sẽ tiếp tục được cập nhật....

 

[trandominhtrang]

(*) Công thức tính tổng bỏ qua các giá trị lỗi hoặc chuỗi trong vùng.
Giả sử có dữ liệu như hình:

Để tính tổng, tại ô D7 [VISITOR][/VISITOR] nhập công thức:

=SUM(IFERROR(B3:B12,0))

Sau đó kết thúc bằng đồng thời 3 phím kết hợp: Ctrl Shift Enter.

(**) Mở rộng cho trường hợp Tính tổng các số có trong chuỗi theo điều kiện như ví dụ hình đính kèm

Yêu cầu:

Tính tổng trong cột C17 nếu đếm tay là 126, sử dụng công thức tính tổng C18, C19

Công thức mảng:

=SUM(IF(RIGHT($C$2:$C$8,2)=$F2,--LEFT($C$2:$C$8,LEN($C$2:$C$8)-LEN($F2))))
=SUM(IFERROR(--SUBSTITUTE($C$2:$C$8,$F2,""),0))
=SUM(IF((ISERROR(VALUE(SUBSTITUTE($C$2:$C$8,$F2,""))))*1=0,VALUE(SUBSTITUTE($C$2:$C$8,$F2,"")),0))

Sau đó kết thúc bằng đồng thời 3 phím kết hợp: Ctrl Shift Enter.
Chi tiết, [VISITOR][/VISITOR]có thể tham khảo file đính kèm.

 

[trandominhtrang]

Tỉnh tổng các chữ số trong một số:
Giả sử:
Ô [A1] = 12345678
Ô [B1] cần tính = 1+2+3+4+5+6+7+8 = 36

Công thức tại ô B1 [VISITOR][/VISITOR] có thể sử dụng như sau:

=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

 

[trandominhtrang]

Mở rộng thêm chút, bài #3

Yêu cầu tính tổng các số trong một số sao cho đến kết quả cuối cùng còn một chữ số.
Ví dụ : Cho số 214598
thì sẽ cộng như sau : 2+1+4+5+9+8=29
tiếp 2+9=11
tiếp 1+1=2
như vậy mới kết thúc.

Thao tác, giả sừ gõ số 214598 tại A1, công thức có thể thực hiện tại B1:

=MOD(A1-1,9)+1

 

[trandominhtrang]

Chủ đề này xin giới thiệu với [VISITOR][/VISITOR] cách sử dụng các hàm tổng hợp theo điều kiện từ đơn giản đến nâng cao.

1. Hàm SUMIFS (Excel 2007+): Tính tổng các ô thỏa nhiều điều kiện chỉ định
   
   Cú pháp:
   

   =SUMIFS(Vùng cần tính tổng, Vùng 1, điều kiện 1, Vùng 2, điều kiện 2,.........,Vùng điều kiện n, điều kiện n)

   
   
2. Hàm SUMPRODUCT: trả về tổng của tổng của các dải ô hoặc mảng tương ứng
   Cú pháp:
   

   =SUMPRODUCT((Vùng 1=dk1)*(Vùng 2=dk2)*(..........)*(Vùng n=dkn))

Ví dụ:
View attachment 1590
Chi tiết, [VISITOR][/VISITOR] có thể tham khảo file đính kèm.

Nội dung sẽ tiếp tục được cập nhật....

Ứng dụng trong việc tổng hợp ngày công:

Sử dụng công thức SUMPRODUCT:

=SUMPRODUCT(($B3:$K3={"p";"np"})*{1;0.5})  
=SUMPRODUCT(($B4:$K4={"k";"nk"})*{1;0.5})

Sử dụng Công thức mảng:

=SUM(COUNTIF($B3:$L3,{"p","np"})*{1,0.5})
=SUM(COUNTIF($B3:$L3,{"k","nk"})*{1,0.5})

Kết thúc bằng đồng thời 3 phím kết hợp: Ctrl Shift Enter.
Chi tiết, [VISITOR][/VISITOR] có thể tham khảo file đính kèm.

 

[trandominhtrang]

Ứng dụng tiếp cho trường hợp ký hiệu chấm công bao gồm cả số và chữ:
Cụ thể như sau:

Trong bảng chấm công từ ngày 1 đến ngày 30 có chấm công tăng ca đêm với ký tự là D1=1 giờ tăng ca, D2.5=2.5 giờ ...Mình muốn tính tổng số giờ tăng ca từ ngày 1 đến ngày 30 thì phải làm như nào?

Trường hợp này có thể sử dụng công thức sau:

=SUMPRODUCT((LEFT(B4:S4,1)="D")*(0&SUBSTITUTE(B4:S4,"D","")))

Trường hợp bị lỗi, sử dụng công thức mảng sau:

=SUM(IF(LEFT(B4:S4,1)="D",--SUBSTITUTE(B4:S4,"D","")))

Kết thúc bằng đồng thời 3 phím kết hợp: Ctrl Shift Enter.

Thay vùng B4:S4 bằng vùng chấm công thực tế của [VISITOR][/VISITOR].

 

[trandominhtrang]

Ứng dụng Sumif, Sumproduct tổng hợp sản lượng cho từng bộ phận theo điều kiện hàng và cột.
Ví dụ như file đính kèm:

Công thức SUMPRODUCT:

=SUMPRODUCT(($B$3:$P$3=B$16)*1,$B5:$P5)

Để tránh bị lỗi value có thể tham khảo cách xử lý như sau:

Bạn nên chú ý kỹ công thức cách 1: công thức đúng là:

=SUMPRODUCT(($B$3:$Q$3=B$16)* ($A$5:$A$11=$A17) , $B$5:$Q$11)

Nếu bạn thay dấu ',' trước B5:Q11 thành dấu nhân '*' thì công thức sẽ báo lỗi ngay, lý do: dữ liệu của bạn có nhiều ô rỗng không chứa giá trị, để dấu ',' thì Sumproduct() sẽ phớt lờ các ô rỗng đó, coi như nó =0, do đó nhân với mảng điều kiện đằng trước vẫn 'bình an vô sự'

Công thức SUMIF:

=SUMIF($B$3:$P$3,G$16,$B5:$P5)

Một vài Công thức mở rộng có thể ứng dụng cho yêu cầu này, tại ô B17:

=SUMIF($B$3:$P$3;B$16;OFFSET($B$4:$P$4;MATCH($A17;$A$5:$A$11;0);))
=SUMPRODUCT(($B$3:$Q$3=B$16)*($A$5:$A$11=$A17),$B$5:$Q$11)
=SUMPRODUCT(N($B$3:$Q$3=B$16),OFFSET($B$4:$Q$4,MATCH($A17,$A$5:$A$11,0),))
=SUMPRODUCT(($A17=$A$5:$A$11)*SUMIF($B$3:$Q$3,B$16,OFFSET($B$4:$Q$4,ROW($1:$7),)))

Công thức mảng, tại ô B17:

=IFERROR(SUM(IF((B$16=($A$3:$Q$3))=TRUE,INDEX($A$5:$Q$11,MATCH($A17,$A$5:$A$11,0),0),0)),0)

Kết thúc bằng đồng thời 3 phím kết hợp: Ctrl Shift Enter.

 

[trandominhtrang]

Ứng dụng tính tổng nhiều Cột theo 02 điều kiện:
Ví dụ dữ liệu như hình đính kèm:

Công thức tính tổng:

=SUMPRODUCT(--($C$7:$C$17=$D23)*($E$7:$E$17=$D$22)*($I$6:$R$6=$F$6),$I$7:$R$17)
=SUMPRODUCT(($C$7:$C$17=D23)*($E$7:$E$17=$D$22)*($I$7:$I$17+$L$7:$L$17+$O$7:$O$17+$R$7:$R$17))

Chi tiết, [VISITOR][/VISITOR] có thể tham khảo file đính kèm.

 

[trandominhtrang]

Ứng dụng tính tổng theo điều kiện cột trong khoảng thời gian cho trước.
Ví dụ như hình đính kèm:

Yêu cầu tổng hợp (1) Số kg, (2) Thời gian, (3) Đơn giá cho từng người trong khoảng thời gian cho trước.
Công thức tại ô AD4:

=SUMPRODUCT(($C$5:$AC$5=AD$5)*($C6:$AC6)*($C$4:$AC$4>=$B$2)*($C$4:$AC$4<=$B$3))       
=SUMPRODUCT(($C$5:$AC$5=AE$5)*($C6:$AC6)*($C$4:$AC$4>=$B$2)*($C$4:$AC$4<=$B$3))   
=SUMPRODUCT(($C$5:$AC$5=AF$5)*($C6:$AC6)*($C$4:$AC$4>=$B$2)*($C$4:$AC$4<=$B$3))

Rồi kéo qua phải và kéo xuống dưới.
Chi tiết, [VISITOR][/VISITOR] tham khảo file đính kèm.