Hàm COUNTIFS – Hàm thực hiện đếm số ô trong bảng dữ liệu thỏa mãn nhiều điều kiện trong Excel hiện là giải pháp cho bạn. Hàm này cho phép bạn đếm số lượng ô trong một khoảng hoặc bảng dữ liệu dựa trên nhiều điều kiện. Hàm COUNTIFS rất hữu ích khi bạn cần phân tích dữ liệu hoặc thống kê theo nhiều tiêu chí khác nhau trong Excel. Với một vài chi tiết, chúng tôi sẽ hướng dẫn bạn với bài viết của aptech nhé!
Đôi nét về Hàm COUNTIFS
Như đã đề cập, trong Excel, Hàm COUNTIFS – Hàm thực hiện đếm số ô trong bảng dữ liệu thỏa mãn nhiều điều kiện trong Excel được sử dụng để đếm các ô trên nhiều phạm vi dựa trên một hoặc nhiều điều kiện. Hàm COUNTIFS đã có sẵn trong Excel 2016, 2013, Excel 2010 và Excel 2007, cho phép bạn sử dụng các ví dụ sau trên bất kỳ phiên bản Excel nào.
Công thức của hàm COUNTIFS có dạng: =COUNTIFS(vùng_điều_kiện1, điều_kiện1, [vùng_điều_kiện2, điều_kiện2], …)
Chi tiết:
- vùng_điều_kiện1: Là phạm vi chứa điều kiện đầu tiên, đây là phần bắt buộc phải có.
- điều_kiện1: Điều kiện đầu tiên (thuộc trong vùng_điều_kiện1), có thể là số, ô tham chiếu, chuỗi văn bản, hàm Excel hoặc vùng Excel khác.
- vùng_điều_kiện2: Là phạm vi chứa điều kiện thứ hai (đòi hỏi vùng_điều_kiện2 phải có cùng kích thước với vùng_điều_kiện1).
- điều_kiện2, …: Điều kiện thứ hai (thuộc trong vùng_điều_kiện1). Trong công thức của bạn, bạn có thể chỉ định tối đa 127 cặp phạm vi và điều kiện.
Tương tự, các điều kiện phụ thuộc khác cũng được áp dụng theo cách tương tự như vậy.
Thực tế, bạn không cần nhớ cú pháp chính xác của hàm đếm có điều kiện COUNTIFS trong Excel. Khi bạn bắt đầu nhập, Microsoft Excel sẽ hiển thị các đối số của hàm và tô đậm đối số bạn đang nhập vào. Chỉ cần ghi nhớ rằng bạn muốn sử dụng Hàm COUNTIFS, một chức năng trong Excel giúp đếm số ô trong bảng dữ liệu thỏa mãn nhiều điều kiện khác nhau.
Lưu ý:
- Hàm COUNTIFS – Hàm thực hiện đếm số ô trong bảng dữ liệu thỏa mãn nhiều điều kiện trong Excel có thể được sử dụng để đếm các ô trong một phạm vi với một điều kiện duy nhất hoặc trong nhiều phạm vi với nhiều điều kiện. Chỉ có những ô thỏa mãn tất cả các điều kiện được chỉ định mới được tính.
- Bạn có thể biểu diễn các điều kiện (criteria) giống như ví dụ sau: 10, “<=32”, A4, “gord”…
- Tất cả các phạm vi bổ sung phải có cùng số hàng và cột như phạm vi ban đầu. Cả phạm vi liền kề và phạm vi không liền kề đều được cho phép.
- Nếu điều kiện là một tham chiếu đến ô trống, Hàm COUNTIFS – Hàm thực hiện đếm số ô trong bảng dữ liệu thỏa mãn nhiều điều kiện trong Excel sẽ coi nó như giá trị 0.
- Bạn có thể sử dụng ký tự đại diện trong điều kiện, bao gồm dấu hoa thị (*) và dấu hỏi (?).
Cách sử dụng hàm COUNTIFS với nhiều điều kiện
Dưới đây là một số ví dụ minh họa về cách sử dụng các Hàm COUNTIFS – Hàm thực hiện đếm số ô trong bảng dữ liệu thỏa mãn nhiều điều kiện trong Excel để đánh giá nhiều điều kiện. Thông qua các ví dụ này, bạn sẽ có cái nhìn rõ hơn về cách áp dụng các hàm này trong các trường hợp cụ thể, từ đó có thể sử dụng hàm COUNTIFS để đánh giá nhiều điều kiện khác nhau trong Excel.
Cách đếm ô với nhiều điều kiện
Công thức 1
Ví dụ, giả sử bạn có một danh sách các sản phẩm như hình ảnh màn hình dưới đây. Bạn muốn đếm số lượng các mặt hàng có sẵn trong kho (giá trị trong cột B lớn hơn 0) nhưng chưa được bán (giá trị trong cột C bằng 0). Để làm điều này, bạn có thể sử dụng công thức sau đây:
= SỐLƯỢNG.NẾU(B2:B7,”>0″, C2:C7,”=0″)
Với kết quả là số lượng là 2 (“Anh đào” và “Chanh”):
Công thức 2
Khi bạn muốn đếm các mục thỏa mãn cùng một điều kiện, bạn cần cung cấp từng cặp điều kiện_range/điều kiện tương ứng.
Ví dụ: Đây là công thức chính xác để đếm các mục có giá trị “0” cả trong cột B và cột C:
=SỐLƯỢNG.NẾU($B$2:$B$7,”=0″, $C$2:$C$7,”=0″)
Công thức SỐLƯỢNG.NẾU này sẽ trả về số “1” vì chỉ có mục “Nho” thỏa mãn cả hai điều kiện “” trong cột B và cột C.
Đếm ô dựa trên nhiều điều kiện (logic OR)
Như đã thấy trong các ví dụ trên, việc đếm các ô thỏa mãn tất cả các điều kiện chỉ định là dễ dàng nhờ vào Hàm COUNTIFS – Hàm thực hiện đếm số ô trong bảng dữ liệu thỏa mãn nhiều điều kiện trong Excel, vì nó cho phép xác định nhiều điều kiện cùng lúc.
Tuy nhiên, nếu bạn muốn đếm các ô thỏa mãn ít nhất một trong số các điều kiện đã chỉ định, tức là dựa trên logic OR, thì phải làm sao? Nói chung, có hai cách để thực hiện điều này – thông qua sử dụng các công thức COUNTIFS hoặc sử dụng công thức SUM COUNTIFS.
Công thức 1
Một ví dụ cụ thể là sử dụng công thức sau để tính số lượng đơn đặt hàng đã hủy và đang chờ xử lý cho sản phẩm Táo:
=SỐLƯỢNG.NẾU($A$2:$A$11, “Táo”, $C$2:$C$11,”Đã hủy”) + SỐLƯỢNG.NẾU($A$2:$A$11, “Táo”, $C$2:$C$11,”Đang chờ xử lý”)
Công thức 2
Trong những trường hợp khi bạn cần đánh giá nhiều điều kiện, cách trên có thể không phải là phương pháp tối ưu vì công thức sẽ trở nên dài dòng. Để thực hiện các phép tính tương tự một cách ngắn gọn hơn, hãy liệt kê tất cả các điều kiện của bạn trong một mảng hằng số và cung cấp mảng đó cho đối số điều kiện của hàm đếm có điều kiện COUNTIFS. Để giải quyết vấn đề này, ta có thể sử dụng công thức sau:
=SUM(COUNTIFS(range,{“criteria1″,”criteria2″,”criteria3”,…}))
Ví dụ: Trong bảng dưới đây, để đếm số lượng “Táo” có trạng thái là “Đã hủy” hoặc “Đang chờ xử lý” hoặc “Nhập kho“, công thức sẽ là:
=SUM(COUNTIFS($A$2:$A$11,”Táo”,$C$2:$C$11,{“Đã hủy”,”Đang chờ xử lý”,”Nhập kho”}))
Sử dụng đếm số trong giá trị chỉ định
Công thức 1
Để đếm số các ô nằm giữa hai giá trị 5 và 10 (không bao gồm 5 và 10) trong khoảng từ C2 đến C10, để giải quyết vấn đề này, bạn có thể sử dụng công thức sau đây:
= COUNTIFS (C2: C10, “>5”, C2: C10, “<10”)
Để đếm các số nằm trong khoảng từ 5 đến 10 trong một dãy số, bạn có thể sử dụng công thức sau:
=COUNTIFS(B2:B10, “>=5”, B2:B10, “<=10”)
Công thức 2
Bạn có thể áp dụng hai công thức sau:
- 1. =COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″) – Đếm số lượng các số lớn hơn 5 và nhỏ hơn 10 trong khoảng dữ liệu từ C2 đến C10.
- 2. =COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – Đếm số lượng các số từ 5 đến 10 trong khoảng dữ liệu từ C2 đến C10, bao gồm cả 5 và 10.
Trong đó:
- Công thức đầu tiên được sử dụng để đếm số lượng các số lớn hơn giá trị ngưỡng dưới (ví dụ như 5).
- Công thức thứ hai loại bỏ các số lớn hơn giá trị ngưỡng trên (ví dụ như 10).
Vì vậy, kết quả được thu được sẽ nằm trong khoảng giá trị đã được xác định.
Sử dụng tham số ô trong COUNTIFS
Ví dụ: Giả sử bạn có một bảng dữ liệu như sau và bạn muốn tính số đơn hàng cho sản phẩm “Táo” với giá trị lớn hơn 200$.
Để làm điều này, bạn áp dụng công thức sau: =COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)
Trong công thức này:
- criteria_range1 là cột A từ ô A2 đến A11.
- criteria_range2 là cột B từ ô B2 đến B11.
Lưu ý, việc sử dụng giá trị tuyệt đối cho tiêu chí và phạm vi tiêu chí giúp công thức vẫn đúng khi bạn sao chép nó sang các ô khác để tính toán.
Sử dụng COUNTIFS với ký tự đặc biệt
Trong Excel, có một số ký tự đặc biệt mà bạn có thể sử dụng trong các công thức để đếm ô theo các điều kiện cụ thể:
- Dấu “?”: Công thức này được sử dụng để đếm các ô có chứa hoặc kết thúc bằng một số ký tự cụ thể.
- Ký tự “*”: Được sử dụng để đếm các ô chứa một từ cụ thể hoặc một ký tự nào đó trong ô.
Ví dụ: Nếu bạn có một danh sách các dự án trong cột A và bạn muốn đếm số dự án đã ký kết, tức là cột B chứa tên người đã ký kết. Bạn cũng muốn thêm một điều kiện thứ hai – Ngày kết thúc trong cột D.
=COUNTIFS(B2:B10, “*”, D2:D10, “<>”)
Lưu ý, bạn không thể sử dụng ký tự đặc biệt trong tiêu chí thứ hai vì cột D chứa kiểu dữ liệu ngày tháng, không phải là dạng văn bản. Vì vậy, bạn sử dụng tiêu chí “<>” & “” để tìm các ô chứa dữ liệu.
Kết luận
Rốt cuộc, Hàm COUNTIFS – Hàm thực hiện đếm số ô trong bảng dữ liệu thỏa mãn nhiều điều kiện trong Excel là một công cụ hữu ích để đếm số ô trong bảng dữ liệu theo nhiều điều kiện khác nhau. Nếu bạn gặp vấn đề khi sử dụng hàm này hoặc cần biết thêm về các tính năng Excel khác, hãy để lại ý kiến của bạn dưới đây. Hãy chia sẻ bài viết này của aptech để giúp đỡ những người khác cùng khám phá thêm về hàm COUNTIFS và Excel nói chung.
