Có gì mới?
Blue
Red
Green
Orange
Voilet
Slate
Dark

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

Administrator
Staff member
Tham gia
Bài viết
11,605
Điểm tương tác
85
Offline
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:
    Rich (BB code):
    =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:

    Rich (BB code):
    =SUMPRODUCT((Vùng 1=dk1)*(Vùng 2=dk2)*(..........)*(Vùng n=dkn))
Ví dụ:
1627529115330.png

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....
 

Đính kèm

  • TinhTong_Nhieu_DieuKien__P1.xls
    26.5 KB · Xem: 15
Last edited:

Quick87

HR Assitant
Tham gia
Bài viết
945
Điểm tương tác
7
Offline
(*) 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:

tinh tong bo qua cac gia tri loi.png


Để tính tổng, tại ô D7 [VISITOR][/VISITOR] nhập công thức:
PHP:
=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

1627551938408.png

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:
PHP:
=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.
 

Đính kèm

  • TinhTongBoQuaGiaTriLoi.xlsx
    9.8 KB · Xem: 8
  • TinhTongCacSoTrongChuoi #fixed.xlsx
    9.8 KB · Xem: 8
Last edited:

Quick87

HR Assitant
Tham gia
Bài viết
945
Điểm tương tác
7
Offline
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:
PHP:
=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
 

Quick87

HR Assitant
Tham gia
Bài viết
945
Điểm tương tác
7
Offline
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:
PHP:
=MOD(A1-1,9)+1
 

Quick87

HR Assitant
Tham gia
Bài viết
945
Điểm tương tác
7
Offline
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:
    Rich (BB code):
    =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:

    Rich (BB code):
    =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:

Tong hop ngay cong.png


Sử dụng công thức SUMPRODUCT:
PHP:
=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:
PHP:
=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.
 

Đính kèm

  • TinhTongNgayNghi__fixed.xls
    29 KB · Xem: 8

Quick87

HR Assitant
Tham gia
Bài viết
945
Điểm tương tác
7
Offline
Ứ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:
PHP:
=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:
PHP:
=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].
 

Quick87

HR Assitant
Tham gia
Bài viết
945
Điểm tương tác
7
Offline
Ứ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:
TinhTongTheoHangVaCot.png


Công thức SUMPRODUCT:
PHP:
=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à:
Rich (BB code):
=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:
PHP:
=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:
PHP:
=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:
PHP:
=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.
 

Đính kèm

  • TinhTongTheoHangCot _fixed.xlsx
    13.5 KB · Xem: 10
Last edited:

Quick87

HR Assitant
Tham gia
Bài viết
945
Điểm tương tác
7
Offline
Ứ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:

TinhTongNhieuCotTheoDK.png


Công thức tính tổng:
PHP:
=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.
 

Đính kèm

  • TinhTongNhieuCotTheoDK _fixed.xlsx
    16.2 KB · Xem: 9

Quick87

HR Assitant
Tham gia
Bài viết
945
Điểm tương tác
7
Offline
Ứ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:
TinhTongNhieuCotTheoTG.png


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:
PHP:
=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.
 

Đính kèm

  • TinhTongNhieuCotTheoDK_TG _fixed.xlsx
    12.4 KB · Xem: 9

codevn_fb_comment

Top Bottom