Có gì mới?

Welcome to Quản trị - Phát triển Con người & Tổ chức - SprinGO Consultant

Join us now to get access to all our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, and so, so much more. It's also quick and totally free, so what are you waiting for?

Latest Thread

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

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: 3
Last edited:
(*) 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: 1
  • TinhTongCacSoTrongChuoi #fixed.xlsx
    9.8 KB · Xem: 1
Last edited:
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))
 
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
 
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: 1
Ứ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].
 
Ứ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: 2
Last edited:
Ứ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: 1
Ứ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: 1

Facebook Comment


Nội quy khi thảo luận:

Dù bạn có cố tình spam bằng cách nào khi BQT diễn đàn phát hiện sẽ lập tức banned nick và xoá toàn bộ bài viết của bạn. Ngoài ra khi phát hiện ra Spam hãy gửi thông báo cho BQT diễn đàn. Hãy suy nghĩ trước khi hành động..!
✓ Khi muốn trả lời ai đó, bạn gõ @ cộng thêm nick diễn đàn của người đó phía sau @, giống như tag tên trên Facebook.
✓ Yêu cầu khi bình luận, bạn phải gõ chữ rõ ràng, không viết tắt, gõ tiếng Việt đầy đủ dấu câu.
✓ Nên dùng font chữ mặc định của diễn đàn, không tô màu lòe loẹt hay dùng size chữ quá lớn khi bình luận.
✓ Bài viết, comment... không được phép quảng cáo dịch vụ, rao vặt, pr... Loại trừ ở chuyên mục Rao vặt đã cho phép.
✓ Nghiêm cấm các chủ đề dạng: Cứu em với, help me, giật tít, câu view... dưới mọi hình thức.
✓ Tất cả các thành viên tham gia diễn đàn cần đọc kỹ Nội quy chung và nghiêm túc tuân thủ.


  • Thẻ
    sumproduct 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
  • Top Bottom
    Liên hệ
    Chat ngay