Công thức chung
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Tóm lược
Để đánh dấu các ô chứa một trong nhiều chuỗi văn bản, bạn có thể sử dụng công thức dựa trên các hàm ISNUMBER và SEARCH, cùng với hàm SUMPRODUCT. Trong ví dụ được hiển thị, định dạng có điều kiện được áp dụng cho B4: B11 dựa trên công thức sau:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Giải trình
Làm việc từ trong ra ngoài, phần này của công thức tìm kiếm từng ô trong B4: B11 cho tất cả các giá trị trong phạm vi được đặt tên là "things":
--ISNUMBER(SEARCH(things,B4)
Hàm SEARCH trả về vị trí của giá trị nếu được tìm thấy và và lỗi #VALUE nếu không được tìm thấy. Đối với B4, kết quả trả về trong một mảng như sau:
(8;#VALUE!;#VALUE!)
Hàm ISNUMBER thay đổi tất cả kết quả thành TRUE hoặc FALSE:
(TRUE;FALSE;FALSE)
Số âm kép phía trước ISNUMBER buộc TRUE / FALSE thành 1/0:
(1;0;0)
Sau đó, hàm SUMPRODUCT cộng các kết quả, được kiểm tra bằng 0:
=SUMPRODUCT((1;0;0))>0
Mọi kết quả khác 0 có nghĩa là ít nhất một giá trị được tìm thấy, vì vậy công thức trả về TRUE, kích hoạt quy tắc.
Bỏ qua những thứ trống rỗng
Để bỏ qua các ô trống trong phạm vi được đặt tên là "things", bạn có thể thử một công thức đã sửa đổi như sau:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Điều này hoạt động miễn là các giá trị văn bản bạn đang kiểm tra không chứa chuỗi "FALSE". Nếu có, bạn có thể mở rộng hàm IF để bao gồm một giá trị nếu sai được biết là không xảy ra trong văn bản (tức là "zzzz", "####", v.v.)
Tùy chọn phân biệt chữ hoa chữ thường
SEARCH không phân biệt chữ hoa chữ thường. Để kiểm tra cả trường hợp, hãy thay thế SEARCH bằng FIND như sau:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Ngăn chặn kết quả phù hợp sai
Một vấn đề với phương pháp này là bạn có thể thấy các kết quả trùng khớp sai do các chuỗi con xuất hiện bên trong các từ dài hơn. Ví dụ: nếu bạn cố gắng kết hợp "dr", bạn cũng có thể tìm thấy "Andrea", "uống", "khô", v.v. vì "dr" xuất hiện bên trong những từ này. Điều này xảy ra bởi vì TÌM KIẾM tự động thực hiện đối sánh "chứa".
Để sửa một phần, bạn có thể thêm khoảng trắng xung quanh các từ tìm kiếm (tức là "dr" hoặc "dr") để tránh bắt "dr" trong một từ khác. Nhưng điều này sẽ không thành công nếu "dr" xuất hiện đầu tiên hoặc cuối cùng trong một ô hoặc xuất hiện bên cạnh dấu chấm câu. Điều này có thể được giải quyết một phần bằng cách thêm không gian xung quanh văn bản gốc. Để thêm khoảng trắng vào đầu và cuối của cả hai cùng một lúc, bạn có thể thử một công thức như sau:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Tuy nhiên, điều này sẽ không khắc phục được sự cố do dấu câu gây ra.
Nếu bạn cần một giải pháp hoàn chỉnh hơn, một tùy chọn là chuẩn hóa văn bản trước trong cột trợ giúp, chú ý thêm khoảng trắng ở đầu và cuối. Sau đó, bạn có thể tìm kiếm toàn bộ các từ được bao quanh bởi dấu cách.
