Công thức chung
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Tóm lược
Để kiểm tra một ô cho một trong nhiều chuỗi, trong khi loại trừ các chuỗi khác, bạn có thể sử dụng công thức dựa trên các hàm SEARCH, ISNUMBER và SUMPRODUCT. Trong ví dụ được hiển thị, công thức ở C5 là:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
trong đó "bao gồm" là phạm vi được đặt tên E5: E9 và "loại trừ" là phạm vi được đặt tên G5: G6.
Giải trình
Về cốt lõi, công thức này sử dụng hàm TÌM KIẾM để tìm kiếm nhiều chuỗi bên trong một ô. Bên trong SUMPRODUCT bên trái, TÌM KIẾM tìm kiếm tất cả các chuỗi trong phạm vi được đặt tên "bao gồm".
Trong SUMPRODUCT bên phải, TÌM KIẾM tìm kiếm tất cả các chuỗi trong phạm vi được đặt tên "loại trừ".
Trong cả hai phần của công thức, TÌM KIẾM trả về vị trí số khi tìm thấy chuỗi và lỗi khi không tìm thấy. Hàm ISNUMBER chuyển đổi các số thành TRUE và lỗi thành FALSE, và âm kép chuyển đổi các giá trị TRUE FALSE thành 1 và 0.
Kết quả tại thời điểm này trông như thế này:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Sau đó:
=(1>0)*(0=0) =TRUE*TRUE =1
Lưu ý: công thức này trả về 1 hoặc 0, được xử lý giống như TRUE và FALSE trong công thức, định dạng có điều kiện hoặc xác thực dữ liệu.
