Hướng dẫn sử dụng Hàm SUMIFS trong Excel

Thứ năm - 31/10/2013 23:00
Hàm SUMIFS() được dùng để tính tổng các dãy ô thỏa mãn 1 hoặc nhiều điều kiện được thiết lập trong nội hàm rất dễ dàng
Chúng ta đã hàm SumIF để tính tổng một dãy ô theo nếu thỏa mãn một điều kiện nào đó; Tuy nhiên, Khi bạn muốn tính tổng một dãy ô có nhiều hơn một điều kiện thì Hàm Sumif không thực hiện được.

Vậy để đáp ứng được nhu cầu xử lý các phép tính cộng với nhiều điều kiện trong excel, hôm nay tôi giới thiệu đến các bạn một hàm nữa dùng để tính tổng cho một dãy ô trong một cột nhưng có thêm nhiều điều kiện ràng buộc kết quả tính toán, đó là Hàm SUMIFS().

Cú pháp của hàm này như sau:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Ta thấy cặp tham số thứ 2 nằm trong dấu ngoặc vuông [], nên ta hiểu cặp tham số này là tùy chọn, nếu có thêm thì sẽ tăng thêm điều kiện cần thỏa mãn trước khi thực hiện phép tính tổng, và ta có thể thêm đến 127 cặp tham số này để giới hạn kết quả tính tổng trong Sum_Range.

Khi chúng ta bỏ qua cặp tham số [criteria_range2, criteria2] thì Hàm SumIFS có chức năng tính toán giống như Hàm Sumif (dạng có đủ 3 tham số). Tuy nhiên thứ tự của các tham số này của Hàm Sumifs lúc này hơi khác với so với hàm SumIF.

Ví dụ: 
Hoa quả bán ra Loại hoa quả Ngày bán
55 Bananas 1
43 Carrots 2
35 Apples 1
31 Artichokes 2
42 Apples 1
72 Carrots 2
100 Bananas 1
53 Artichokes 2

A10=SUMIFS(A2:A9,B2:B9,"A*"): Tính tổng cho dãy (B2:B9) với điều kiện là các giá trị trong dãy A2:A9 bắt đầu bằng A
A11=SUMIF(B2:B9,"A*",A2:A9): Tính tổng cho dãy (B2:B9) với điều kiện là các giá trị trong dãy A2:A9 bắt đầu bằng A

Kết quả là A10=A11 = 161

Lưu ý: Độ lớn các dãy ô trong sum_range, criteria_range1 và criteria_range2 (nếu có thêm cặp tham số thứ 2) phải bằng nhau, nếu không hàm sẽ trả về #VALUE.
Ví du  A10=SUMIFS(A2:A9,B3:B9,"A*") hoặc A10=SUMIFS(A2:A8,B2:B9,"A*") thì hàm đều trả về giá trị lỗi #VALUE.

Khi ta bắt đầu thêm cặp tham số [criteria_range2, criteria2] thì hàm sẽ tính tổng các giá trị trong dãy Sum_Range nếu thỏa tất cả các điều kiện thiết lập từ tham số này trở đi.

Ví dụ: 
A12=SUMIFS(A2:A9,B2:B9,"A*",C2:C9,1)

Hàm sẽ đi tính tổng số lượng hoa quả bán ra ở dãy Sum_Range nếu giá trị đó nằm cùng hàng với tiêu chí Criteria_Range1 và Criteria1 được thỏa mãn. sau đó nó xét duyệt đến tiêu chí thứ 2 ở cặp Criteria_Range2 và Criteria2 và tiếp tục lọc cho đến hết các điều kiện

Ta thấy ở điều kiện thứ nhất, các ô B4, B5, B6 và B9 là thỏa điều kiện thứ nhất là bắt đầu bằng chữ A. Sau đó Hàm sẽ xét duyệt đến điều kiện đếm thứ 2 trong số các ô thỏa điều kiện thứ nhất (B4, B5, B6 và B9).

Cụ thể Hàm sẽ gióng giá trị B4, B5, B6 và B9 sang giá trị của các ô C4, C5, C6 và C9 (cùng số thứ tự tính từ trái sang phải, từ trên xuống dưới hay còn gọi là chỉ mục của các phần từ) trong dãy Range_Criteria2 C2:C9 có trá trị là 1 rồi bắt đầu tính tổng.
Lúc này ta thấy chỉ có cặp ô B4 & C4, C6 & C6 là thỏa cả 2 điều kiện. và kết quả của hàm Sumifs sẽ là tính tổng của ô A4 và A6 = 35+42=77.

Vậy quy luật tính của nó 100% giống với  Hàm CountIFS ở bài học trước.

Ở ví dụ trên các tham số của hàm được lấy theo 1 dãy các ô trong cùng một cột đơn. Nếu ta muốn Sum_Range là dãy các ô trong một hàng ngang hoặc nhiều hàng ngang thì độ lớn của các tham số criteria_range1, và criteria_range2 phải bằng nhau và bằng với độ lớn của tham số Sum_Range, nếu không hàm sẽ trả về giá trị #Value.

Ta xét ví dụ sau: Khi tham số được tham chiếu với độ lớn là 2 dòng hàng ngang.

Ta tính tổng lượng mưa trong 12 tiếng, với điều kiện là nhiệt độ trung bình là từ 40 độ F và sức gió trung bình là ít hơn 10 dặm 1 giờ.
 
Thời gian đo
Sáng -Chiều
Ngày đầu Ngày thứ 2 Ngày thứ 3 Ngày thứ 4
Sáng (In) 1.3 0 1.5 3
Chiều (In) 2 0.8 4 2.5
Nhiệt độ Trung bình (Sáng) 56 44 40 38
Nhiệt độ Trung bình (Sáng) 54 34 38 77
Sức gió Trung bình (Sáng) 13 6 8 1
Sức gió Trung bình (Chiều) 0 33 4 12

A10=SUMIFS(B2:E3,B4:E5,">=40",B6:E7,"<10")
Theo công thức trên thì chỉ có 3 ô được tính tổng, đó là B3, C2, và D2 vì các ô đối ứng (Theo thứ tự của các phần tử trong dãy)
Ô đối ứng với ô B3 là B5 và B7,
Ô đối ứng với ô C3 là C4 và C6,
Ô đối ứng với ô D2 là D4 và D6,

Vậy tổng các ô B3, C2, và D2 là 2+0+1.5 =3.5 In

Lưu ý với vùng dữ liệu 2 hàng trở lên thì hàm sẽ Index các phần tử trong các tham số Sum_Range, criteria_range1, và criteria_range2 thành số thứ tự từ 1 cho đến phần tử cuối cùng (ô cuối cùng) trong dãy đó rồi so sánh giá trị của các ô theo từng cặp chỉ mục với nhau.

Ví dụ trên hàm sẽ lập chỉ mục (Index) như sau:
Sum_range      : B2=1,C2=2,D2=3,E2=4,B3=5,C3=6,D3=7,E3=8,
Range_Criteria1: B4=1,C4=2,D4=3,E4=4,B5=5,C5=6,D5=7,E5=8,
Range_Criteria2: B6=1,C6=2,D6=3,E6=4,B7=5,C7=6,D7=7,E7=8,

Và tiến hành tính tổng các ô ở Sum Range có cùng chỉ mục với các ô thỏa tất cả các điều kiện. 

Chính vì hình thức lập chỉ mục để so sánh nên khi ta đưa giá trị mảng vào 3 tham số trên, miễn là cùng độ lớn các phần tử trong mảng thì hàm cũng sẽ tìm ra các giá trị cần tính tổng.

Ví dụ sau: địa chỉ của các vùng trong các tham số trên bị lệch đi một cột

A10=SUMIFS(C2:F3,B4:E5,">=40",B6:E7,"<10")
lúc này chỉ mục số mục của
Sum_range : C2=1,D2=2,E2=3,F2=4,C3=5D3=6,E3=7,F3=8

Và căn cứ trên 2 điều kiện trên thì hàm sẽ chỉ tính tổng ô 
C3, D2, và E2 là 0.8+1.5+3 = 5.3 In

Chúc bạn thành công nhé.

 

Tác giả bài viết: Minh Phú

Chú ý: Việc đăng lại bài viết trên ở website hoặc các phương tiện truyền thông khác mà không ghi rõ nguồn http://aneedz.com là vi phạm bản quyền

Tổng số điểm của bài viết là: 24 trong 5 đánh giá

Xếp hạng: 4.8 - 5 phiếu bầu
Click để đánh giá bài viết

  Ý kiến bạn đọc

Mã bảo mật   
Thống kê
  • Đang truy cập42
  • Hôm nay7,632
  • Tháng hiện tại43,649
  • Tổng lượt truy cập1,564,501
Bạn đã không sử dụng Site, Bấm vào đây để duy trì trạng thái đăng nhập. Thời gian chờ: 60 giây