Cập nhật thông tin chi tiết về Cách Sử Dụng Hàm Sumif Trong Excel mới nhất trên website Theindochinaproject.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.
Hàm sumif là hàm tính tổng cho một dãy số hoặc một dãy các ô số (Range) căn cứ vào một điều kiện (Criteria) nào đó.
Ta có công thức tổng quát sau:=SUMIF( Range, Criteria,[sum_range])
Trước tiên ta phân tích tham số Sum_Range. Tham số này đặt trong ngoặc vuông nên nó là tham số tùy chọn.
Khi Sum_Range bị bỏ qua thì hàm sẽ tính tổng cho vùng Range.
Nhưng khi bạn thêm tham số này vào thì Hàm sẽ tính tổng cho Sum_RangeVí du:=Sumif(A2:A7,”apple”,B2:B7) thì hàm sẽ tính tổng cho vùng B2:B7.=Sumif(A2:A7,”apple”) thì hàm sẽ tính tổng cho vùng A2:A7.
Range:– Là vùng chứa giá trị để tính tổng nếu tham số Sum_range được bỏ qua. Các ô chứa Text hoặc rỗng sẽ bị hàm bỏ qua.– Khi có tham số Sum_Range thì Range sẽ là vùng chứa giá trị để đánh giá cho Criteria.
Criteria: Các điều kiện thiết lập cho Hàm SumIF cũng tương tự như Hàm CountIF hoặc Hàm CountIFS ở bài học trước.
Lưu ý: Các điều kiện phải dựa vào giá trị của các ô chứa trong Range. Nếu Range chứa giá trị là Text thì điều kiện Criteria phải ở dạng Text nếu không nó sẽ không tìm thấy điều kiện nào khớp với với điều kiện được thiết lập và trả về giá trị là 0.
Ở ví dụ sau ta có 2 bảng nằm trong 1 sheet như sau:
Ô D2 và D3: Kết quả bằng 0, vì Criteria là lớn hơn giá trị ô B2 (lớn hơn 10) Tuy nhiên Range có giá trị trong dãy A2: A11 là dạng Text (Chữ) nên hàm bỏ qua.
Ô D4 và D5: Kết quả bằng 135, Tính tổng giá trị trong dãy (range) B2:B11 với Criteria là lớn hơn giá trị ô B2 (lớn hơn 10), và range B2: B11 là dạng số (Numberic), nên hàm sẽ tính tổng các ô B3:B7 vì các ô này có giá trị lớn hơn 10.
Ô D7: Khi có tham số Sum_Range (A15:A24) thì hàm sẽ tính tổng các ô nằm trong dãy A15:A24 (Sum_Range) với tiêu chí là giá trị ô A15 (Apple), Lúc này tuy chữ Apple là điều kiện thỏa giá trị nằm trong Range nhưng Sum_Range không chứa giá số nên kết quả vẫn là bằng 0.
Trong thực tế ta thường dùng hàm Sumif để tính tổng các giá trị trên 1 bảng dựa trên tiêu chí xuất hiện trên một bảng.
Vi dụ: Ta có bảng kết quả bán hàng của cửa hàng hoa quả theo từng ngày, cứ mỗi một lượt khách mua một mặt hàng thì sẽ ghi chép vào 1 dòng theo thứ tự từ trên xuống dưới, và lúc này các mặt hàng thường xuyên lặp đi lặp lại. Bây giờ ta tạo một bảng thứ 2 có liệt kê tất cả các loại hoa quả mà cửa hàng đang bán (4 loại bên dưới). Ta dùng hàm Sumif để tính tổng số tiền bán được trên một loại hoa quả như sau:
Mẹo Sử Dụng Hàm Sumifs Trong Excel
Lượt Xem:936
Khi bạn có danh sách dữ liệu và bạn cần thực hiện tổng của một vài ô trong danh sách này, hàm SUMIFS là hàm để sử dụng.
Vâng, trên thực tế, có 2 chức năng để làm điều này:
Giống như sự khác biệt giữa COUNTIF và COUNTIFS , sự khác biệt là số lượng tiêu chí
Với COUNTIF, bạn có thể đếm các hàng cho một tiêu chí
= COUNTIF ( Phạm vi 1, Tiêu chí1 )
Với COUNTIFS bạn có thể đếm các hàng cho 256 tiêu chí
= COUNTIFS ( Phạm vi 1, Tiêu chí1, Phạm vi 2, Tiêu chí2, Phạm vi 3, …)
Việc xây dựng là như nhau, bạn chỉ cần thêm tiêu chí khác
Nhưng đối với SUMIF và SUMIFS, cách viết công thức cũng khác nhau
= SUMIF ( Phạm vi 1, Tiêu chí1, Cột để tính tổng )
= SUMIFS ( Cột để tổng hợp, Phạm vi 1, Tiêu chí1, Phạm vi 2, Tiêu chí2, … )
Với SUMIFS, cột để tổng hợp là tham số đầu tiên nhưng với hàm SUMIF, cột để tổng hợp là cột cuối cùng.
Bây giờ, hãy xem cách sử dụng hàm trong ví dụ. Hãy lấy bảng tính này, nơi chúng tôi có doanh số bán hàng cho tất cả khách hàng của chúng tôi, theo quốc gia, theo thể loại và số lượng của mỗi lần bán hàng.
Câu hỏi hay và câu trả lời rất dễ
Đầu tiên, chúng ta chọn cột để tính tổng (cột E)
= SUMIFS ($ E $ 2: $ E $ 263
Sau đó, chúng tôi chọn phạm vi tiêu chí đầu tiên (cột Quốc gia)
= SUMIFS ($ E $ 2: $ E $ 263, $ B $ 2: $ B $ 263
= SUMIFS ($ E $ 2: $ E $ 263, $ B $ 2: $ B $ 263, “Đức”
Sau đó, chúng tôi chọn phạm vi thứ hai của tiêu chí và giá trị thứ hai
= SUMIFS ($ E $ 2: $ E $ 263, $ B $ 2: $ B $ 263, “Đức”, $ D $ 2: $ D $ 263, “Uống”)
Lý do tốt nhất để sử dụng hàm SUMIFS là thực hiện lựa chọn giữa 2 ngày. Để làm điều này, chúng ta phải xem xét
Một lựa chọn vào một ngày lớn hơn (ngày đầu tiên)
Một lựa chọn khác vào một ngày thấp hơn (ngày cuối cùng)
Vì vậy, trong cùng một dữ liệu trước đó, nếu chúng ta muốn tất cả doanh thu từ ngày 01/01/2017 đến ngày 31/01/2017, công thức là
= SUMIFS ($ E $ 2: $ E $ 263
Và đối với tiêu chí đầu tiên
Và đối với tiêu chí thứ hai trên CỘT CÙNG
Nhìn vào tiêu chí thứ hai, dấu lôgic là <01/02/2017 . Nếu bạn muốn phải viết ngày cuối cùng của tháng, bài kiểm tra sẽ như sau: <= 31/01/2017
Như bạn có thể thấy ở đây, tham chiếu của cột được viết với tên của cột. Điều này là do chúng tôi đã chèn dữ liệu vào một Bảng.
Cách Sử Dụng Hàm Sumif Và Sumifs Để Tính Tổng Có Điều Kiện Trong Excel
Thực chất hàm SumIFs là hàm tính tổng có điều kiện nâng cấp của hàm SumIF, nếu như SumIF tính tổng với 1 điều kiện thì SumIFs cho phép ta tính tổng với nhiều điều kiện. Trong bài viết này, chúng ta sẽ cùng tìm hiểu cách dùng hàm tính tổng có điều kiện Sumif và SumIFs.
Cách dùng hàm SUMIF để tính tổng có điều kiện trong Excel
Công dụng: Hàm SUMIF là hàm tính tổng có điều kiện trong Excel
Cú pháp (syntax): =SUMIF (range, criteria, [sum_range])
Trong đó
Range: Phạm vi các ô bạn muốn đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số hoăc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn.
Criteria: Tiêu chí xác định các giá trị thêm vào.
Sum_range: (tùy chọn) Các giá trị được gắn thêm. Nếu sum_range bị bỏ quá, những ô trong phạm vi đánh giá sẽ được thay thế.
Chú ý:
_ Khi sum_range bị bỏ qua, các ô trong phạm vi sẽ được cộng lại.
_ Khu vực xác định có chứa chữ hay ký hiệu toán học đều phải được đặt trong dấu ngoặc kép.
_ Phạm vi xác định dạng số có thể được cung cấp là số sẽ không phải dùng đến dấu ngoặc.
_ Các ký tự ? và * đều có thể được sử dụng trong Criteria. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
Lưu ý: Để làm theo hướng dẫn các bạn có thể tải file Tại Đây
Một ví dụ cách sử dụng hàm SUMIF đểTính tổng cột Số lượng với Tên hàng là Red Apples, như vậy chúng ta nhập công thức như hướng dẫn hình sau:
Tính tổng các giá trị LỚN HƠN 200 trong các ô E4:E12.
<
=SUMIF(E4:E12,”<300″, B2:B10)
Tính tổng các giá trị trong các ô E4:E12 nếu giá trị tương ứng trong cột A NHỎ HƠN 300.
=
=SUMIF(D4:D12,E4)
Tính tổng các giá trị trong các ô D4:D12 mà có giá trị BẰNG với giá trị trong ô E4.
Tính tổng các giá trị trong các ô F4:F12 nếu giá trị tương ứng trong cột C KHÁC với giá trị trong ô I3.
Tính tổng các ô có giá trị Lớn hơn hoặc bằng 175 trong dải E4:E12
<=
=SUMIF(E4:E12,”<=150″, F4:F12)
Tính tổng các giá trị trong các ô F4:F12 nếu giá trị trong ô tương ứng trong cột E Nhỏ hơn hoặc bằng 150
Sử dụng SUMIF với các tiêu chí so sánh kiểu chuỗi
Tính tổng nếu bằng
Đối chiếu hoàn toàn: =SUMIF(C4:C12, “bananas” ,F4:F12)
Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa chính xác từ “bananas” và không dư một từ hay kí tự nào. Các ô chứa “green bananas”, “yellow bananas” không thoả điều kiện này nên không được tính.
Tính tổng nếu khác
Đối chiếu từng phần: =SUMIF(C4:C12, “*bananas*”, F4:F12)
Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa từ “bananas”, đứng độc lập hoặc đi kèm với những từ khác”. Các ô chứa “green bananas”, “yellow bananas” thoả điều kiện này nên được tính.
Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ hay kí tự khác, ví như “yellow bananas” hoặc “Green bananas” sẽ được tính tổng.
Giá trị tính tổng trong các ô F4:F12 nếu ô tương ứng trong cột C chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ khác. Các ô như “yellow bananas” hay “Green bananas” sẽ không được tính.
Cách dùng hàm SUMIFS để tính tổng có điều kiện trong Excel
Công dụng: Hàm SUMIFS là hàm tính tổng có nhiều điều kiện trong Excel
Cú pháp (syntax): =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).
Trong đó:
– sum_range: Vùng cần tính tổng.
– criteria_range1: Vùng điều kiện cần tính tổng, tham số bắt buộc.
– criteria1: Tiêu chí so sánh vùng điều kiện, là tham số bắt buộc.
– criteria_range2, criteria2: Tùy chọn các phạm vi và điều kiện bổ sung, cho phép tối đa 127 cặp criteria_range2, criteria2.
– Các ô giá trị trong vùng tính tổng sum_range chứa giá trị True được coi là 1, False được coi là 0.
– Đối số vùng điều kiện criteria_range phải có cùng kích thước với sum_range.
– Trong giá trị điều kiện criteria có thể dùng kí tự thay thế ? hoặc *.
Hãy tham khảo cách dùng hàm sumifs trong bảng sau: Tính tổng thành tiền với Tên hàng là Red Apples và có số lượng lớn hơn hoặc bằng 150.
Cách Sử Dụng Hàm Sumifs Và Sumif Với Nhiều Điều Kiện
Trong bài viết này, Học Excel Online sẽ giải thích sự khác biệt giữa các hàm SUMIF và SUMIFS theo cú pháp và cách sử dụng của chúng, bên cạnh đó, cũng cung cấp một số ví dụ về công thức để tính tổng các giá trị có nhiều điều kiện đồng thời xảy ra () hoặc tất cả không đồng thời xảy ra ( OR) trong phiên bản Excel 2013, 2010, 2007, 2003 và trước nữa.
range – dải của các ô được đánh giá theo điều kiện mà bạn đưa ra, mang tính bắt buộc.
criteria – điều kiện cần phải đáp ứng, mang tính bắt buộc
sum_range – các ô tính tổng nếu thỏa điều kiện, mang tính tùy chọn.
Như bạn thấy, cú pháp của hàm Excel SUMIF chỉ cho phép một điều kiện. Tuy nhiên, bên trên, chúng tôi có nói rằng Excel SUMIF có thể được sử dụng để tính tổng các giá trị với nhiều điều kiện. Làm thế nào mà có thể được? Thực tế, bạn cần thêm các kết quả của vài hàm SUMIF và sử dụng các công thức SUMIF với các điều kiện mảng, như trong ví dụ tiếp theo.
Hàm SUMIFS – cú pháp và cách sử dụng:
Bạn sử dụng SUMIFS trong Excel để tìm một tính tổng có giá trị các giá trị dựa trên nhiều điều kiện. Hàm SUMIFS đã được giới thiệu trong Excel 2007, vì vậy bạn có thể sử dụng nó trong tất cả các phiên bản của Excel 2013, 2010 và 2007.
So với SUMIF, thì cú pháp SUMIFS phức tạp hơn một chút:
sum_range – một hoặc nhiều ô tính tổng, mang tính bắt buộc. Đây có thể là một ô duy nhất, một dải ô hoặc dải có tên. Chỉ có các ô chỉ chứa số mới được tính tổng; Còn giá trị ô trống và giá trị văn bản thì sẽ bị bỏ qua.
Chú ý: Hàm SUMIFS hoạt động với biểu thức logic với AND, nghĩa là mỗi ô trong đối số sum_range chỉ được tính tổng nếu tất cả các điều kiện được chỉ định là đúng cho ô đó.
Bây giờ tập hợp các thông số trên, và bạn sẽ nhận được công thức SUMIFS sau:
= SUMIFS (C2: C9, A2: A9, F1, B2: B9, F2)
Sử dụng SUMIFS và SUMIF trong Excel – một số điều cần nhớ:
Mặc dù nét chung thì khá rõ ràng – tương đồng về điểm đến cho kết quả cuối cùng và các tham số – trong khi sự khác biệt dù không rõ rang nhưng vẫn rất cần thiết.
1. Trình tự của các đối số
Trong các hàm Excel SUMIF và SUMIFS, thứ tự các đối số là khác nhau. Cụ thể, sum_range là tham số thứ nhất trong SUMIFS, nhưng lại đứng thứ 3 trong công thức SUMIF.
Khi bắt đầu học, bạn sẽ cảm thấy có vẻ như Microsoft đã cố ý làm phức tạp hóa cho người học và người dùng nó. Tuy nhiên, khi xem xét kỹ hơn, bạn sẽ thấy lý do thực chất đằng sau nó. Vấn đề là sum_range là tùy chọn trong SUMIF. Nếu bạn bỏ qua nó, không có vấn đề, SUMIF công thức của bạn sẽ tính tổng các giá trị trong range (tham số đầu tiên).
Trong SUMIFS, sum_range là rất quan trọng và là bắt buộc, và đó là lý do tại sao nó đến trước. Có thể những người của Microsoft nghĩ rằng sau khi thêm các dải/điều kiện xem xét thứ 10 hoặc 100, thì có ai đó có thể quên xác định dải để tính tổng:)
Tóm lại, nếu bạn đang sao chép và chỉnh sửa các hàm này, hãy đảm bảo bạn đặt các thông số theo thứ tự đúng.
2. Kích thước của đối số sum_range và criteria_range
Trong hàm SUMIF, đối số sum_range không nhất thiết phải có cùng kích thước với đối số range, miễn là bạn có ô phía trên bên trái. Trong hàm SUMIFS, mỗi criteria_range phải chứa cùng một số hàng và cột như tham số sum_range.
Ví dụ, công thức = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại kết quả đúng vì Excel chỉ xem ô phía trên bên trái trong đối số sum_range (C2 trong ví dụ này là đúng) và sau đó bao gồm số cột và hàng giống như kích thước của đối số range.
Công thức SUMIFS: = SUMIFS (C2: C9, A2: A9, “apples”, B2: B10, “Pete”) sẽ báo vì criter_range2 (B2: B10) không khớp với criteria_range1 (A2: A9) và sum_range (C2: C9).
Có vẻ như chúng ta đã tiếp cận tạm xong lý thuyết rồi, nên mục sau chúng ta sẽ chuyển sang thao tác thực hành (chính là các ví dụ công thức:)
Đăng ký ngay: Học Excel dành cho người đi làm
Làm thế nào để sử dụng SUMIFS trong Excel – ví dụ công thức
Ví dụ 1. Công thức SUMIFS với toán tử so sánh
Trong trường hợp bạn muốn tính tổng các giá trị với nhiều điều kiện dựa trên ngày hiện tại, hãy sử dụng hàm TODAY () trong điều kiện của hàm SUMIFS của bạn, như được trình bày bên dưới. Công thức sau đây tính tổng giá trị trong cột D nếu ngày tương ứng trong cột C rơi vào khoảng thời gian 7 ngày vừa qua, có bao gồm ngày hôm nay:
Tương tự như vậy, bạn có thể sử dụng hàm SUMIF để tính tổng các giá trị trong một dải có dữ liệu là ngày xác định. Ví dụ: công thức SUMIFS sau sẽ thêm các giá trị trong các ô C2: C9 nếu ngày trong cột B rơi trong khoảng giữa ngày 1 tháng 10 năm 2014 và ngày 31 tháng 10 năm 2014, như sau:
Kết quả tương tự có thể đạt được bằng cách tính toán sự khác biệt của hai hàm SUMIF, như được minh họa trong ví dụ này – Cách sử dụng SUMIF để tính tổng các giá trị trong một dải ngày xác định. Tuy nhiên, bạn cũng thấy rằng công thức SUMIFS là dễ dàng hơn và dễ hiểu hơn nhiều, phải không nào?
Hướng dẫn học Excel cơ bản
Ví dụ 3. Hàm SUMIFS với ô trống và không trống
Khi phân tích báo cáo và dữ liệu khác, bạn thường cần phải tính tổng các giá trị tương ứng với ô trống hoặc không trống.
Những ô trống
“=”
Tính tổng các giá trị mà có ô trống tương ứng (hoàn toàn không chứa dũ liệu – không công thức, và chuỗi có 0 kí tự)
=SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”)
Tính tổng giá trị trong các ô C2:C10 nếu các ô tương ứng với nó trong cột A và B là ô hoàn toàn trống.
“”
Tính tổng các giá trị tương ứng với các ô trắng “nhận định trực quan”, bao gồm các giá trị chứa các chuỗi rỗng được trả về bởi một số hàm Excel khác (ví dụ: ô có công thức như = “”).
=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)
Tính tổng các giá trị trong các ô C2:C10 (có cùng các điều kiện) như công thức ở trên, nhưng có bao gồm các chuỗi trống.
Những ô không trống
Tính tổng các giá trị mà có các giá trị tương ứng là các ô không trống, và không bao gồm chuỗi có chiều dài bằng 0
Tính tổng giá trị trong các ô C2:C10 nếu các ô tương ứng với nó trong cột A và B không là ô trống, và không bao gồm các ô với chuỗi trống.
Giả sử bạn có ngày đặt hàng trong cột B, ngày giao hàng trong cột C và số lượng trong cột D. Làm thế nào để bạn tính được tổng số sản phẩm chưa được giao? Nghĩa là bạn muốn biết tổng các giá trị tương ứng với các ô không rỗng trong cột B và các ô rỗng trong cột C.
Giải pháp là sử dụng công thức SUMIFS với 2 điều kiện:
Như đã lưu ý ở phần đầu của bài này, thì hàm SUMIFS được thiết kế với AND. Nhưng nếu bạn cần tính tổng các giá trị có nhiều điều kiện OR, nghĩa là khi có ít nhất một trong các điều kiện sẽ được đáp ứng?
Ví dụ 1. SUMIF + SUMIF
Giải pháp đơn giản nhất là tính tổng các kết quả trả về bởi một số hàm SUMIF. Ví dụ: công thức sau đây sẽ minh họa cách tính tổng số sản phẩm do Mike và John cung cấp:
= SUMIF (C2: C9, “Mike”, D2: D9) + SUMIF (C2: C9, “John”, D2: D9)
Ví dụ 2. SUM & SUMIF với đối số mảng
Các giải pháp trên rất đơn giản và thực sự hiệu quả cao khi chỉ có một vài điều kiện. Nhưng một công thức SUMIF + SUMIF có thể được phát triển rất nhiều nếu bạn muốn tính tổng các giá trị với nhiều điều kiện OR. Trong trường hợp này, cách tiếp cận tốt hơn là sử dụng một đối số như điều kiện mảng trong hàm SUMIF, như sau:
Bạn có thể bắt đầu bằng cách liệt kê tất cả các điều kiện của bạn, được ngăn cách bởi dấu phẩy và sau đó đặt chúng trong {dấu ngoặc nhọn}, điều này được gọi là mảng.
Trong ví dụ trước, nếu bạn muốn tính tổng các sản phẩm do John, Mike và Pete cung cấp, điều kiện mảng của bạn sẽ như thế này {“John”, “Mike”, “Pete”}. Và hàm SUMIF hoàn chỉnh là: =SUMIF (C2: C9, {“John”, “Mike”, “Pete”}, D2: D9).
Đối số mảng bao gồm 3 giá trị bắt buộc phải có trong công thức SUMIF để trả về ba kết quả độc lập, nhưng vì chúng ta viết công thức trong một ô duy nhất, nó sẽ chỉ trả lại kết quả đầu tiên – tức là tổng số sản phẩm do John cung cấp. Để nó hoạt động, bạn phải sử dụng vài mẹo – lồng công thức SUMIF của bạn trong một hàm SUM, như sau:
= SUM (SUMIF (C2: C9, {“John”, “Mike”, “Pete”}, D2: D9))
Phương pháp này sẽ chạy với các con số hoặc các giá trị văn bản. Ví dụ: nếu thay vì bạn có tên các nhà cung cấp trong cột C, mà bạn chỉ có ID nhà cung cấp như 1, 2, 3, v.v … thì công thức SUMIF của bạn sẽ như sau:
= SUM (SUMIF (C2: C9, {1,2,3}, D2: D9))
Không giống như các giá trị văn bản, các con số không cần phải được bao gồm trong dấu nháy kép trong đối số mảng.
Trong trường hợp, Bạn thích liệt kê các điều kiện trong vài ô thay vì chỉ định chúng trực tiếp trong công thức, thì bạn có thể sử dụng SUMIF kết hợp với hàm SUMPRODUCT để nhân nhiều thành phần trong các mảng xác định rồi trả lại tổng số lượng của các sản phẩm đó.
Nhưng tất nhiên, không có gì ngăn cản nếu bạn muốn liệt kê các giá trị trong một điều kiện mảng của hàm SUMIF”
= SUMPRODUCT (SUMIF (C2: C9, {“Mike”, “John”, “Pete”}, D2: D9))
Kết quả trả về bởi cả hai công thức sẽ giống như những gì bạn thấy trong hình:
Như thường lệ, một ví dụ có thể giúp minh họa tốt hơn điều trên. Trong bảng các nhà cung cấp trái cây của chúng tôi, hãy thêm Delivery Date (Ngày giao hàng) (cột E) và tìm tổng số lượng được Mike, John và Pete giao vào tháng 10.
Công thức được tạo ra bởi cách phương pháp này bao gồm nhiều lần lặp đi lặp lại và trông có vẻ rườm rà, tuy vậy nhưng nó rất dễ hiểu, và điểm quan trọng nhất là nó hoạt động tốt 🙂
Tôi đã cố gắng giải thích bản chất của cách tiếp cận này trong ví dụ với SUMIF, vì vậy bây giờ chúng ta chỉ có thể sao chép công thức đó, thay đổi thứ tự các đối số (có sự khác nhau ở SUMIF và SUMIFS – như trên đã đề cập) và thêm các điều kiện bổ sung. Công thức kết quả sau sẽ nhỏ gọn hơn SUMIFS + SUMIFS:
Kết quả trả về bằng công thức này giống như những gì bạn thấy trong ảnh chụp màn hình ở trên.
Ví dụ 3. SUMPRODUCT & SUMIFS
Nếu như bạn nhớ, thì cách tiếp cận SUMPRODUCT khác với hai cách trước đó theo cách bạn nhập mỗi điều kiện của bạn vào một ô riêng biệt thay vì nhập chúng trực tiếp trong công thức. Trong trường hợp một số điều kiện, hàm SUMPRODUCT sẽ không đủ và bạn sẽ phải sử dụng hàm ISNUMBER và hàm MATCH.
Vì vậy, giả sử rằng các tên người cung cấp nằm trong các ô H1: H3, Ngày bắt đầu (Start date) nằm trong ô H4 và ngày kết thúc (End date) trong ô H5, công thức SUMPRODUCT của chúng tôi có dạng sau:
Tôi hy vọng lời giải thích trên có ý nghĩa với bạn. Và thậm chí nếu không, thì thực sự là bạn chỉ cần nhớ nguyên tắc này – sử dụng toán tử đôi (-) khi bạn sử dụng các toán tử so sánh trong các công thức SUMPRODUCT.
Sử dụng hàm SUM trong các công thức mảng
Như bạn đã biết, thì Microsoft đã thực hiện hàm SUMIFS trong Excel 2007. Nếu ai đó vẫn sử dụng Excel 2003, 2000 hoặc phiên bản trước đó, thì bạn sẽ phải sử dụng công thức mảng SUM để thêm các giá trị có nhiều điều kiện AND. Đương nhiên, phương pháp tiếp cận này cũng hoạt động trong các phiên bản hiện đại của Excel 2013 – 2007, và có thể được coi là sự lỗi thời của hàm SUMIFS.
Ví dụ 1. Sum với các điều kiện AND trong Excel 2003 và phiên bản trước đó:
Chúng ta hãy quay lại ví dụ đầu tiên mà chúng ta tính tổng số lượng ứng với một loại trái cây và nhà cung cấp nhất định:
= SUMIFS (C2: C9, A2: A9, “apples”, B2: B9, “Pete”)
Điều kiện 1: A2: A9 = “apples”
Điều kiện 2: B2: B9 = “Pete”
Sau đó, bạn viết một công thức SUM có nhân tất cả trong trường hợp: tương ứng với các cặp dải / điều kiện của bạn, mỗi cặp được đặt trong ngoặc đơn. Hệ số nhân cuối cùng là dải để tính tổng, là C2: C9 trong trường hợp này:
= SUM ((A2: A9 = “apples”) * (B2: B9 = “Pete”) * (C2: C9))
Như được minh họa trong hình bên dưới, thì công thức này hoạt động rất tốt trong phiên bản Excel 2013 mới nhất.
Ví dụ 2. Các công thức mảng SUM trong các phiên bản Excel hiện đại
Ngay cả trong các phiên bản hiện đại của Excel 2013, 2010 hoặc 2007, sức mạnh của hàm SUM không bao giờ bị đánh giá thấp. Công thức mảng SUM không chỉ đơn giản là luyện tập hướng suy nghĩ mà còn có giá trị thực tế, như trong ví dụ sau đây:
Giả sử bạn có hai cột, B và C, và bạn cần phải biết được cột C lớn hơn gấp bao nhiêu lần so với cột B, khi một giá trị trong cột C lớn hơn hoặc bằng 10. Một giải pháp mà bạn có thể nghĩ ra đo là sử dụng công thức mảng SUM:
Điều kiện 1: Giá trị trong cột B (Ordered items) lớn hơn 0
Điều kiện 2: Một giá trị trong cột C (Delivered) ít hơn trong cột B
Điều kiện 3: Ngày trong cột D (Due date) ít hơn 11/1/2014.
Đặt ba cặp range / điều kiện lại với nhau, bạn sẽ nhận được công thức sau:
Ngoài hàm SumIf, SumIfS giúp tính tổng theo nhiều điều kiện, chúng ta còn có thể sử dụng các hàm khác như SUMPRODUCT để làm việc này. Trong thực tế thì yêu cầu tính toán và đặc điểm dữ liệu sẽ quyết định tới việc chúng ta sẽ cần dùng hàm nào, dùng như thế nào. Do đó bạn cần bổ sung thêm cho mình càng nhiều kiến thức về hàm, về tổ chức dữ liệu thì càng thuận lợi khi làm việc.
Tiếp đó là việc cần phải kết hợp các công cụ của Excel để tăng hiệu quả công việc như: định dạng dữ liệu, định dạng theo điều kiện, trích lọc, sắp xếp, báo cáo bằng Pivot Table, vẽ biểu đồ…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel từ cơ bản đến chuyên gia dành cho người đi làm
Bạn đang xem bài viết Cách Sử Dụng Hàm Sumif Trong Excel trên website Theindochinaproject.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!