Công thức chung
=IF(COUNTIF(list,value),"OK","Missing")
Tóm lược
Để xác định các giá trị trong một danh sách bị thiếu trong danh sách khác, bạn có thể sử dụng một công thức đơn giản dựa trên hàm COUNTIF với hàm IF. Trong ví dụ được hiển thị, công thức trong G6 là:
=IF(COUNTIF(list,F6),"OK","Missing")
trong đó "danh sách" là phạm vi được đặt tên B6: B11.
Giải trình
Hàm COUNTIF đếm các ô đáp ứng tiêu chí, trả về số lần xuất hiện được tìm thấy. Nếu không có ô nào đáp ứng tiêu chí, COUNTIF trả về 0. Bạn có thể sử dụng hành vi trực tiếp bên trong câu lệnh IF để đánh dấu các giá trị có số 0 (tức là các giá trị bị thiếu). Trong ví dụ được hiển thị, công thức trong G6 là:
=IF(COUNTIF(list,F6),"OK","Missing")
trong đó "danh sách" là một phạm vi được đặt tên tương ứng với phạm vi B6: B11.
Hàm IF yêu cầu kiểm tra logic để trả về TRUE hoặc FALSE. Trong trường hợp này, hàm COUNTIF thực hiện kiểm tra logic. Nếu giá trị được tìm thấy trong danh sách , hàm COUNTIF trả về một số trực tiếp cho hàm IF. Kết quả này có thể là bất kỳ số nào… 1, 2, 3, v.v.
Hàm IF sẽ đánh giá bất kỳ số nào là TRUE, khiến IF trả về "OK". Nếu giá trị không được tìm thấy trong danh sách , COUNTIF trả về không (0), được đánh giá là FALSE và IF trả về "Thiếu".
Thay thế với MATCH
Bạn cũng có thể kiểm tra các giá trị bị thiếu bằng cách sử dụng hàm MATCH. MATCH tìm vị trí của một mục trong danh sách và sẽ trả về lỗi # N / A khi không tìm thấy giá trị. Bạn có thể sử dụng hành vi này để xây dựng công thức trả về "Thiếu" hoặc "OK" bằng cách kiểm tra kết quả MATCH với hàm ISNA. ISNA chỉ trả về TRUE khi nhận được lỗi # N / A.
Để sử dụng MATCH như được hiển thị trong ví dụ trên, công thức là:
=IF(ISNA(MATCH(F6,list,0)),"Missing","OK")
Lưu ý rằng MATCH phải được định cấu hình để khớp chính xác. Để thực hiện việc này, hãy đảm bảo đối số thứ ba là 0 hoặc FALSE.
Thay thế với VLOOKUP
Vì hàm VLOOKUP cũng trả về lỗi # N / A khi giá trị không tròn, bạn có thể xây dựng công thức với hàm VLOOKUP hoạt động giống như tùy chọn MATCH. Như với MATCH, bạn phải cấu hình VLOOKUP để sử dụng khớp chính xác, sau đó kiểm tra kết quả với ISNA. Cũng lưu ý rằng chúng tôi chỉ cung cấp cho hàm VLOOKUP một cột duy nhất (cột B) cho mảng bảng.
