Công thức Excel: Đếm các giá trị duy nhất với tiêu chí
Công thức Excel: Đếm các giá trị duy nhất với tiêu chí

Công thức chung
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Tóm lược

Để đếm các giá trị duy nhất với một hoặc nhiều điều kiện, bạn có thể sử dụng công thức dựa trên ĐỘC NHẤTBỘ LỌC. Trong ví dụ được hiển thị, công thức trong H7 là:

=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))

trả về 3, vì có ba tên duy nhất trong B6: B15 được liên kết với dự án Omega.

Lưu ý: công thức này yêu cầu Công thức mảng động, chỉ có ở Excel 365. Với phiên bản Excel cũ hơn, bạn có thể sử dụng công thức thay thế phức tạp hơn.

Giải trình

Về cơ bản, công thức này sử dụng hàm UNIQUE để trích xuất các giá trị duy nhất và hàm FILTER áp dụng các tiêu chí.

Làm việc từ trong ra ngoài, Chức năng FILTER được sử dụng để áp dụng các tiêu chí và chỉ trích xuất các tên có liên quan đến dự án “Omega”:

FILTER(B6:B15,C6:C15=H6) // Omega names only

Kết quả từ FILTER là một mảng như thế này:

{"Jim";"Jim";"Carl";"Sue";"Carl"}

Tiếp theo, Hàm UNIQUE được sử dụng để loại bỏ các bản sao:

UNIQUE({"Jim";"Jim";"Carl";"Sue";"Carl"})

kết quả là một mảng mới như thế này:

{"Jim";"Carl";"Sue"} // after UNIQUE

Tại thời điểm này, chúng tôi có một danh sách duy nhất các tên liên quan đến Omega, và chúng tôi chỉ cần đếm chúng. Vì lý do được giải thích bên dưới, chúng tôi thực hiện điều này với hàm LEN và hàm SUM. Để làm cho mọi thứ rõ ràng, trước tiên chúng tôi sẽ viết lại công thức để bao gồm danh sách duy nhất:

=SUM(--(LEN({"Jim";"Carl";"Sue"})>0))

Các Hàm LEN lấy độ dài của mỗi mục trong danh sách và trả về một mảng độ dài:

LEN({"Jim";"Carl";"Sue"}) // returns {3;4;3}

Tiếp theo, chúng tôi kiểm tra xem độ dài có lớn hơn 0 hay không:

LEN({3;4;3)>0 // returns {TRUE;TRUE;TRUE}

Và sử dụng một Âm kép để ép buộc các giá trị TRUE và FALSE thành 1s và 0s:

--({TRUE;TRUE;TRUE}) // returns {1;1;1}

Cuối cùng, chúng tôi cộng các kết quả với Hàm SUM:

=SUM({1;1;1}) // returns 3

Mảng này được phân phối trực tiếp đến hàm COUNTA, hàm này trả về tổng số cuối cùng:

=COUNTA({"Jim";"Carl";"Sue"}) // returns 3

Lưu ý rằng vì chúng tôi đang kiểm tra độ dài của mỗi mục được trả về bởi UNIQUE, ô trống hoặc ô trống đáp ứng tiêu chí sẽ bị bỏ qua. Công thức này là động và sẽ tính toán lại ngay lập tức nếu dữ liệu nguồn được thay đổi.

Đếm số duy nhất với nhiều tiêu chí

Để đếm các giá trị duy nhất dựa trên nhiều tiêu chí, có thể mở rộng logic “bao gồm” bên trong FILTER. Ví dụ: để đếm các tên riêng cho dự án Omega chỉ trong tháng 6, hãy sử dụng:

=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))

Đây là một ví dụ về việc sử dụng logic boolean để áp dụng nhiều hơn một điều kiện. Cách tiếp cận là giải thích chi tiết hơn ở đây.

Để biết thêm chi tiết, hãy xem video đào tạo này: Cách lọc với nhiều tiêu chí.

COUNTA

Có thể viết một công thức đơn giản hơn để trả lời trên Hàm COUNTA. Tuy nhiên, một lưu ý quan trọng là COUNTA sẽ trả về 1 khi không có giá trị phù hợp. Điều này là do hàm FILTER trả về lỗi khi không có dữ liệu nào phù hợp với tiêu chí và lỗi này sẽ được tính bởi hàm COUNTA. Công thức COUNTA cơ bản trông giống như sau:

=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))

Một lần nữa, công thức này sẽ trả về 1 khi không có dữ liệu phù hợp. Nó cũng sẽ bao gồm các ô trống đáp ứng tiêu chí. Công thức dựa trên LEN và SUM là một lựa chọn tốt hơn.

Không có mảng động

Nếu bạn đang sử dụng phiên bản Excel cũ hơn mà không hỗ trợ mảng động, bạn có thể sử dụng công thức phức tạp hơn. Để có một cuộc thảo luận chung hơn về các lựa chọn thay thế mảng động, hãy xem: Các lựa chọn thay thế cho công thức mảng động.

https://exceljet.net/formula/count-unique-values-with-criteria