Hướng dẫn sử dụng hàm sumifs chi tiết nhất!
Hàm sumifs trong excel
Hàm sumifs là hàm tính toán cơ bản thường được dùng phổ biến trong excel. Tuy nhiên, không phải ai cũng hiểu rõ và biết cách sử dụng hàm sumifs như thế nào. Chúng ta hãy cùng nhau tìm hiểu về hàm sumifs, cũng như cách dùng hàm sumifs đơn giản nhất ở bài viết sau đây nhé!
Contents
Hàm sumifs là gì?
Hàm SUMIFS trong Excel là hàm được ghép từ hàm SUM và hàm IFS. Vì thế mà chức năng của hàm SUMIFS chính là sự kết hợp chức năng tính tổng của hàm SUM cùng với việc áp dụng nhiều điều kiện cùng lúc đối với dữ liệu của hàm IFS. Qua đó chúng ta có thể hiểu đơn giản rằng hàm SUMIFS là hàm tính tổng với nhiều điều kiện trong Excel.
Cú pháp hàm SUMIFS trong Excel
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).
Trong đó:
- Sum_range: là các ô dùng để tính tổng trong bảng dữ liệu, các giá trị trống và giá trị text được bỏ qua. Tham số bắt buộc cần phải có.
- Criteria_range1: Là phạm vi cần được kiểm tra bằng điều kiện criteria1, đây là giá trị bắt buộc.
- Criteria1: là điều kiện áp dụng đối với criteria_range1. Đó có thể là một số, biểu thức, tham chiếu ô nhằm để có thể xác định được ô nào trong criteria_range1 được tính tổng, đây cũng là giá trị bắt buộc.
- Criteria_range2,criteria2,…: tùy chọn các phạm vi, điều kiện bổ sung, tối đa là 127 cặp criteria_range, criteria.
XEM THÊM: Hướng dẫn bạn cách câu wifi nhà hàng xóm cực đơn giản
Lưu ý khi dùng hàm sumifs
Khi sử dụng hàm sumifs, bạn nên chú ý những điều cơ bản sau đây:
- Mỗi ô trong vùng, phạm vi sum_range sẽ được cộng tổng nếu như thỏa mãn tất cả các điều kiện tương ứng xác định là đúng đối với ô đó.
- Các ô trong sum_range có chứa TRUE sẽ là 1, các ô chứa FALSE sẽ được coi là 0.
- Mỗi criteria_range yêu cầu có cùng kích thước vùng chọn tương tự như sum_range, criteria_range và sum_range, có cùng số hàng và cùng số cột.
- Criteria có thể sử dụng ký tự là dấu hỏi chấm (?) thay cho ký tự đơn, dấu sao (*) thay cho một chuỗi. Điều kiện nếu là dấu hỏi chấm hay dấu sao đều cần phải nhập thêm dấu ~ ở phía trước. Giá trị điều kiện chính là văn bản để trong dấu “.
Hướng dẫn sử dụng hàm sumifs
Dùng các ký tự đại diện
Các ký tự đại diện nhe ‘*’ và ‘?’ có thể dùng trong đối số tiêu chí. Những ký tự này sẽ giúp bạn tìm kiếm được các kết quả trùng khớp tương tự nhưng không chính xác.
Dấu sao (*) sẽ khớp với bất kỳ chuỗi ký tự nào,bạn có thể dùng trước, sau hoặc với những tiêu chí xung quanh nhằm cho phép sử dụng một phần tiêu chí tìm kiếm.
Dấu chấm hỏi (?) sẽ phù hợp đối với bất kỳ ký tự đơn nào. Nếu như bạn áp dụng N?r làm tiêu chí. “?” sẽ thay thế đối với một ký tự duy nhất. N?r sẽ tương ứng với North, Nor, v.v… nhưng không tính đến Name.
Dùng Named Range với hàm SUMIFS
Named Range là tên mô tả đối với một tập hợp các ô, dải ô đối với một trang tính. Chúng ta có thể dùng Named Range khi sử dụng hàm SUMIFS.
Cách sử dụng hàm sumifs với những ví dụ cụ thể
Ví dụ 1:
Yêu cầu tính tổng sản lượng các lô hàng hoa quả đến từ nhà cung ứng Mike, điều kiện đáp ứng là các lô hàng lớn đó chứa từ 200 sản phẩm trở lên.
Hướng dẫn giải:
Ta sử dụng hàm SUMIFS để kết hợp so sánh “lớn hơn hoặc bằng” (>=) nhằm thoả mãn được điều kiện số lượng sản phẩm trong một lô hàng từ 200 đổ lên. Bạn nên để ý, đề bài không yêu cầu tính tổng một mặt hàng cụ thể, vì thế công thức hàm SUMIFS ở trường hợp này như sau:
=SUMIFS(C2:C9, B2:B9, “Mike”, C2:C9, “>=200”)
Tương tự ví dụ, nếu ta cần cần tính tổng các giá trị trong phạm vi C2:C9 thỏa mãn điều kiện nằm trong khoảng 200 và 300, sẽ dùng dấu “lớn hơn hoặc bằng” (>=) và “nhỏ hơn hoặc bằng” (<=). Theo đó, công thức để tính hàm SUMIFS như sau:
=SUMIFS(C2:C9, C2:C9, “>=200”, C2:C9, “<=300”)
Lưu ý: Đối với những biểu thức logic có chứa toán tử so sánh cần đặt ở trong cặp dấu ngoặc kép (“”).
Ví dụ 2:
Yêu cầu tính tổng sản lượng hoa quả từ các lô hàng được nhập kho trong thời gian 7 ngày qua, bao gồm cả ngày hôm nay.
Hướng dẫn giải:
Theo đề bài ta sẽ xác định được tham số criteria1 thể hiện điều kiện “7 ngày gần nhất bao gồm cả ngày hôm nay”. Có nghĩa là bắt đầu tính lô hàng là 7 ngày trước, ngày kết thúc sẽ là ngày hôm nay.
Ta sẽ sử dụng hàm TODAY đối với tham số criteria1 và trừ thêm 7 để biểu thị ngày bắt đầu. Với tham số criteria2, ta dùng đến hàm TODAY nhằm để ấn định ngày kết thúc tức là ngày hôm nay.
Công thức hàm SUMIFS lúc này sẽ như sau:
=SUMIFS(D2:D10, C2:C10, “>=” & TODAY() – 7, C2:C10, “<=” & TODAY ())
Tương tự công thức trên, bạn có thể kết hợp giữa dữ liệu ngày tháng để nhằm tính sản lượng hoa quả nhập kho với khoảng thời gian cụ thể. Nếu bạn muốn tính lượng hàng từ ngày 11/02/2021 đến ngày 23/03/2021, ta có công thức như sau:
=SUMIFS(C2:C9, B2:B9, “>=02/11/2021”, B2:B9, “<=03/23/2021”)
Lưu ý: Nếu áp dụng toán tử so sánh và dữ liệu thời gian trong một tham số điều kiện criteria, bạn cần nối các chuỗi với nhau bằng ký hiệu “&”. Ví dụ: “<=” & TODAY ()
Ví dụ 3:
Ví dụ ngày đặt hàng được thể hiện tại cột B, ngày nhận hàng là cột C, số lượng sản phẩm trong một lô hàng là cột D. Yêu cầu tính tổng sản lượng hàng đặt nhưng chưa được nhận.
Hướng dẫn giải:
- Công thức hàm SUMIFS với những ô không rỗng
Ta cần tính tổng giá trị tại cột D với điều kiện ô tương ứng tại cột B không phải ô rỗng. Lúc này cặp ký tự đặc biệt mà ta sử dụng là “<>” với công thức như sau:
=SUMIFS(C2:C10, A2:A10, “<>”, B2:B10, “<>”)
Bên cạnh đó bạn có thể sử dụng kèm hàm SUM để thực hiện thao tác tính tổng trong Excel với các ô tính có nội dung ô tính tương ứng khác rỗng:
=SUM(C2:C10) – SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)
- Công thức hàm SUMIFS đối với các ô rỗng
Cách dùng hàm SUMIFS với giá trị tại cột D theo điều kiện ô tương ứng tại cột C là ô rỗng. Ta sử dụng ký tự đặc biệt là “=” nhằm giúp thể hiện các ô trống hoàn toàn. Lúc này, ta có công thức hàm SUMIFS như sau:
=SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”)
Thay vì ký tự “=”, ta có thể dùng cặp dấu ngoặc kép “” nhằm để biểu diễn điều kiện chuỗi giá trị trong ô tương ứng là chuỗi rỗng.
=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)
Giờ đây ta có công thức hàm SUMIFS của chúng ta như sau:
=SUMIFS(D2:D10, B2:B10, “<>”, C2:C10, “=”)
Cách phân biệt hàm SUMIF và hàm SUMIFS
Cả hai hàm SUMIF và hàm SUMIFS trong Excel đều có mục đích là để tính tổng kèm theo điều kiện. Để có thể biết được chính xác nhất nên sử dụng hàm nào đối với bảng dữ liệu đang có, ta cần phân biệt được những điểm giống và khác nhau giữa 2 hàm tính tổng có điều kiện như sau:
Trình tự tham số của 2 hàm SUMIF và hàm SUMIFS
Hàm SUMIF và hàm SUMIFS bạn hãy để ý rằng trình tự các tham số được hiển thị sẽ hoàn toàn khác nhau. Nói một cách chi tiết hơn thì tham số sum_range là tham số đầu tiên trong cú pháp SUMIFS, chỉ xuất hiện ở vị trí thứ 3 trong cú pháp hàm SUMIF.
Về bản chất, sum_range là tham số tùy chọn ở cú pháp hàm SUMIF, có nghĩa là bạn có thể không điền tham số này nếu cảm thấy không cần thiết. Hàm vẫn sẽ thực hiện tính tổng trong Excel dựa trên phạm vi dữ liệu bạn đã xác định trước đó.
Còn đối với hàm SUMIF, sum_range là tham số quan trọng hàng đầu ở trong cú pháp hàm SUMIFS. Trước khi xác định các cặp phạm vi-điều kiện nhỏ lẻ, bạn cần xác định được phạm vi dữ liệu cần thực hiện để tính tổng với SUMIFS. Điều này lý giải tại sao sum_range là tham số đứng đầu ở trong cú pháp hàm SUMIFS trong Excel.
XEM THÊM: 4 cách tra cứu phạt nguội vi phạm giao thông cực dễ dàng
Kích thước tham số của 2 hàm SUMIF và hàm SUMIFS
Ở cú pháp của hàm SUMIF, tham số sum_range cùng tham số range không nhất thiết có cùng kích thước với nhau. Ngược lại, sự thống nhất trong kích thước của các tham số chính là yếu tố giúp quyết định cách sử dụng hàm SUMIFS. Tức là bạn cần phải đảm bảo mỗi criteria_range cần được chứa cùng số hàng và số cột với tham số sum_range.
Với những chia sẻ trên đây của chúng tôi hy vọng đã giúp cho người dùng có thể hiểu hơn về hàm hàm sumifs, cũng như cách sử dụng trong những trường hợp cụ thể. Đừng quên nhấn theo dõi dienmayhot.com của chúng tôi để cập nhật cho mình những thông tin hữu ích nhất nhé!
