Công thức chung
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Tóm lược
Để đếm các ô không bằng bất kỳ thứ nào trong số nhiều thứ, bạn có thể sử dụng công thức dựa trên các hàm MATCH, ISNA và SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong ô F5 là:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
trong đó "dữ liệu" là phạm vi được đặt tên B5: B16 và "loại trừ" là phạm vi được đặt tên D5: D7.
Giải trình
Đầu tiên, một chút bối cảnh. Thông thường, nếu bạn chỉ có một vài thứ bạn không muốn đếm, bạn có thể sử dụng COUNTIFS như sau:
=COUNTIFS(range,"apple",range,"orange")
Nhưng quy mô này không tốt lắm nếu bạn có một danh sách nhiều thứ, vì bạn sẽ phải thêm một cặp phạm vi / tiêu chí bổ sung cho mỗi thứ bạn không muốn đếm. Sẽ dễ dàng hơn rất nhiều khi xây dựng danh sách và chuyển tham chiếu đến danh sách này như một phần của tiêu chí. Đó chính xác là những gì công thức trên trang này làm.
Về cơ bản, công thức này sử dụng hàm MATCH để tìm các ô không bằng "a", "b" hoặc "c" với biểu thức sau:
MATCH(data,exclude,0)
Lưu ý rằng giá trị tra cứu và mảng tra cứu được "đảo ngược" so với cấu hình bình thường - chúng tôi cung cấp tất cả các giá trị từ phạm vi được đặt tên là "dữ liệu" dưới dạng giá trị tra cứu và cung cấp tất cả các giá trị chúng tôi muốn loại trừ trong phạm vi được đặt tên là "loại trừ". Bởi vì chúng tôi cung cấp MATCH nhiều hơn một giá trị tra cứu, chúng tôi nhận được nhiều hơn một kết quả trong một mảng như sau:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Về cơ bản, MATCH cung cấp cho chúng ta vị trí của các giá trị khớp dưới dạng một số và trả về # N / A cho tất cả các giá trị khác.
Kết quả # N / A là những kết quả mà chúng tôi quan tâm, vì chúng đại diện cho các giá trị không bằng "a", "b" hoặc "c". Theo đó, chúng tôi sử dụng ISNA để buộc các giá trị này thành TRUE và các số thành FALSE:
ISNA(MATCH(data,exclude,0)
Sau đó, chúng tôi sử dụng một số âm kép để buộc TRUE thành 1 và FALSE thành 0. Mảng kết quả, bên trong SUMPRODUCT trông giống như sau:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Chỉ với một mảng để xử lý, SUMPRODUCT tính tổng và trả về kết quả cuối cùng, 4.
Lưu ý: Sử dụng SUMPRODUCT thay vì SUM sẽ tránh phải sử dụng điều khiển + shift + enter.
Đếm trừ trận đấu
Một cách khác để đếm các ô không bằng một số thứ là đếm tất cả các giá trị và trừ các kết quả phù hợp. Bạn có thể làm điều này với một công thức như sau:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Ở đây, COUNTA trả về tổng số tất cả các ô không trống. Hàm COUNTIF, được cung cấp cho phạm vi được đặt tên là "loại trừ" sẽ trả về ba số, một cho mỗi mục trong danh sách. SUMPRODUCT cộng tổng và số này bị trừ khỏi tổng số tất cả các ô không trống. Kết quả cuối cùng là số ô không có giá trị bằng nhau trong "loại trừ".
Literal chứa logic loại
Công thức trên trang này được tính bằng logic "bằng với". Nếu bạn cần đếm các ô không chứa nhiều chuỗi, nơi chứa có nghĩa là một chuỗi có thể xuất hiện ở bất kỳ đâu trong ô, bạn sẽ cần một công thức phức tạp hơn.
