Đếm giá trị khác biệt với hàm COUNTIF

Trong bài viết về hàm SUMPRODUCT, ta đã được thấy ví dụ về việc sử dụng SUMPRODUCT kết hợp COUNTIF để đếm giá trị khác biệt. Dưới đây Học Excel Online sẽ giải thích cùng ví dụ cụ thể để hiểu cách sử dụng COUNTIF trong trường hợp đếm những giá trị độc nhất như vậy.

Xem nhanh

Đếm giá trị khác biệt với hàm COUNTIF

Bài toán “các loại bi”

“Trong một hộp nhựa có 5 viên bi vàng, 3 viên bi xanh, 4 viên bi đỏ. Hỏi có bao nhiêu loại bi?”

Câu trả lời ở đây là: 3 loại bi Vàng – Đỏ – Xanh.

Trong Excel, ta có thể sử dụng các công thức như lọc kết hợp xóa dữ liệu trùng, đếm không trùng… để đưa ra đáp án. Bởi đây là bài viết về hàm COUNTIF, nên ta sẽ chỉ sử dụng COUNTIF/COUNTIFS cho bài toán này mà thôi.

Nhưng logic nào để đưa ra con số 3 trong Sheet với công thức 1/COUNTIF?

Có thể giải thích như sau:

Có thể dễ dàng thấy rằng, công thức =COUNTIF(A2:A13,A2:A13) sẽ trả về kết quả là một mảng tương ứng như sau: {4;3;4;5;3;3;5;4;4;5;5;5}. Đó là số lần lặp lại của các loại bi: bi vàng 5 lần, bi xanh 3 lần, bi đỏ 4 lần.

Vậy ta suy luận như sau: Nếu bi vàng xuất hiện 5 lần thì mỗi ô trong sheet sẽ là 1/5 lần, với bi đỏ là 1/4 lần, bi xanh là 1/3 lần. Cộng lại ta sẽ có 3 loại bi riêng biệt.

Bởi vậy, ta sẽ thực hiện phép chia:

=1/COUNTIF(A2:A13,A2:A13&””)

Cụm &”” được thêm vào để tránh trường hợp ô chứa giá trị rỗng, kết quả tại ô gặp lỗi chia cho 0 (#DIV/0).

Lúc đó, mảng nhận được sẽ là: {1/4;1/3;1/4;1/5;1/3;1/3;1/5;1/4;1/4;1/5;1/5;1/5}

Và bước cuối cùng, ta cần một hàm có chức năng tính tổng các phần tử trong mảng. Lựa chọn ở đây có thể là SUM với công thức mảng, hoặc SUMPRODUCT:

Mở rộng bài toán với COUNTIFS

Tương tự COUNTIF, ta có thể sử dụng COUNTIFS để đếm giá trị khác biệt theo nhiều điều kiện hơn. Với bài toán bi trên, ta mở rộng ra như sau:

“Trong một hộp nhựa có 5 viên bi vàng bao gồm: 2 viên vàng nhạt, 3 viên vàng đậm, 3 viên bi xanh đậm, 4 viên bi đỏ bao gồm: 1 viên đỏ nhạt, 3 viên đỏ đậm. Hỏi có bao nhiêu loại bi?

Câu trả lời ở đây là 5: vàng nhạt + vàng đậm + xanh đậm + đỏ nhạt + đỏ đậm. Làm thế nào để có được kết quả này trong sheet, ta hãy áp dụng COUNTIFS vào thay cho COUNTIF nhé.

=SUMPRODUCT(1/COUNTIFS(A2:A13,A2:A13&””,B2:B13,B2:B13&””))

Ta hoàn toàn có thể làm tương tự với 3, 4, 5,… n điều kiện bằng cách áp dụng COUNTIFS.

Tham khảo thêm

Một số bài viết về COUNTIF/COUNTIFS:

Một số cách đếm giá trị khác biệt khác:

Cảm ơn các bạn đã theo dõi bài viết đến đây. Học Excel Online luôn mong muốn các bạn có được những kiến thức bổ ích cùng vơi chúng tôi. Trân trọng!