Công thức chung
=SUMPRODUCT(1/COUNTIF(data,data))
Tóm lược
Để đếm số lượng giá trị duy nhất trong một dải ô, bạn có thể sử dụng công thức dựa trên hàm COUNTIF và hàm SUMPRODUCT. Trong ví dụ hiển thị, công thức trong F6 là:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Giải trình
Làm việc từ trong ra ngoài, COUNTIF được định cấu hình thành các giá trị trong phạm vi B5: B14, sử dụng tất cả các giá trị giống nhau này làm tiêu chí:
COUNTIF(B5:B14,B5:B14)
Vì chúng tôi cung cấp 10 giá trị cho tiêu chí, chúng tôi nhận lại một mảng có 10 kết quả như sau:
(3;3;3;2;2;3;3;3;2;2)
Mỗi số đại diện cho một số đếm - "Jim" xuất hiện 3 lần, "Sue" xuất hiện 2 lần, v.v.
Mảng này được định cấu hình là một số chia với 1 là tử số. Sau khi chia, chúng ta nhận được một mảng khác:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Bất kỳ giá trị nào xuất hiện chỉ một lần trong phạm vi sẽ xuất hiện dưới dạng 1s, nhưng các giá trị xuất hiện nhiều lần sẽ xuất hiện dưới dạng giá trị phân số tương ứng với bội số. (tức là một giá trị xuất hiện 4 lần trong dữ liệu sẽ tạo ra 4 giá trị = 0,25).
Cuối cùng, hàm SUMPRODUCT tính tổng tất cả các giá trị trong mảng và trả về kết quả.
Xử lý các ô trống
Một cách để xử lý các ô trống hoặc ô trống là điều chỉnh công thức như sau:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Bằng cách nối một chuỗi trống ("") với dữ liệu, chúng tôi ngăn các số không kết thúc trong mảng được tạo bởi COUNTIF khi có các ô trống trong dữ liệu. Điều này rất quan trọng vì số 0 trong ước số sẽ khiến công thức gặp lỗi # DIV / 0. Nó hoạt động vì sử dụng một chuỗi trống ("") cho tiêu chí sẽ đếm các ô trống.
Tuy nhiên, mặc dù phiên bản này của công thức sẽ không xuất hiện lỗi # DIV / 0 khi có các ô trống, nhưng nó sẽ bao gồm các ô trống trong số lượng. Nếu bạn muốn loại trừ các ô trống khỏi số lượng, hãy sử dụng:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Điều này có tác dụng loại bỏ việc đếm các ô trống bằng cách làm cho tử số bằng 0 cho các số liên quan.
Hiệu suất chậm?
Đây là một công thức tuyệt vời và thanh lịch, nhưng nó tính toán chậm hơn nhiều so với các công thức sử dụng FREQUENCY để đếm các giá trị duy nhất. Đối với các tập dữ liệu lớn hơn, bạn có thể muốn chuyển sang công thức dựa trên hàm FREQUENCY. Đây là công thức cho giá trị số và một công thức cho giá trị văn bản.
