Công thức chung
=COUNTIF(list,A1)=0
Tóm lược
Để so sánh các danh sách và đánh dấu các giá trị tồn tại trong một danh sách mà không tồn tại trong một danh sách khác, bạn có thể áp dụng định dạng có điều kiện với công thức dựa trên hàm COUNTIF. Ví dụ: để đánh dấu các giá trị A1: A10 không tồn tại C1: C10, hãy chọn A1: A10 và tạo quy tắc định dạng có điều kiện dựa trên công thức này:
=COUNTIF($C$1:$C$10,A1)=0
Lưu ý: với định dạng có điều kiện, điều quan trọng là phải nhập công thức liên quan đến "ô hiện hoạt" trong vùng chọn, được giả định là A1 trong trường hợp này.
Giải trình
Công thức này được đánh giá cho mỗi ô trong số 10 ô trong A1: D10. A1 sẽ thay đổi thành địa chỉ của ô đang được đánh giá, trong khi C1: C10 được nhập dưới dạng địa chỉ tuyệt đối, vì vậy nó sẽ không thay đổi.
Chìa khóa của công thức này là = 0 ở cuối, "lật" logic của công thức. Đối với mỗi giá trị trong A1: A10, COUNTIF trả về số lần giá trị xuất hiện trong C1: C10. Miễn là giá trị xuất hiện ít nhất một lần trong C1: C10, COUNTIF sẽ trả về một số khác 0 và công thức sẽ trả về FALSE.
Nhưng khi không tìm thấy giá trị trong C1: C10, COUNTIF trả về 0 và vì 0 = 0, công thức sẽ trả về TRUE và định dạng có điều kiện sẽ được áp dụng.
Các phạm vi được đặt tên cho cú pháp đơn giản
Nếu bạn đặt tên cho danh sách bạn đang tìm kiếm (trong trường hợp này là C1: C10) bằng một dải ô đã đặt tên, thì công thức sẽ đơn giản hơn để đọc và hiểu:
=COUNTIF(list,A1)=0
Điều này hoạt động vì các phạm vi được đặt tên tự động là tuyệt đối.
Phiên bản phân biệt chữ hoa chữ thường
Nếu bạn cần số lượng phân biệt chữ hoa chữ thường, bạn có thể sử dụng công thức như sau:
=SUMPRODUCT((--EXACT(A1,list)))=0
Hàm EXACT thực hiện đánh giá phân biệt chữ hoa chữ thường và hàm SUMPRODUCT sẽ đánh dấu kết quả. Như với COUNTIF, công thức này sẽ trả về khi kết quả bằng 0. Bởi vì bài kiểm tra phân biệt chữ hoa chữ thường, "apple" sẽ hiển thị là bị thiếu ngay cả khi "Apple" hoặc "APPLE" xuất hiện trong danh sách thứ hai. Xem trang này để được giải thích chi tiết hơn.
