Công thức chung
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Tóm lược
Để kiểm tra một ô để xem ô đó có chứa một trong nhiều chuỗi hay không, bạn có thể sử dụng công thức dựa trên các hàm SEARCH, ISNUMBER và SUMPRODUCT. Công thức trong C5, được sao chép xuống, là:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
trong đó mọi thứ là phạm vi được đặt tên E5: E9.
Giải trình
Chúng tôi muốn kiểm tra từng ô trong B5: B11 để xem liệu nó có chứa bất kỳ chuỗi nào trong phạm vi được đặt tên hay không (E5: E9). Công thức chúng tôi đang sử dụng trong C5, được sao chép xuống, là:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Công thức này dựa trên một công thức (giải thích ở đây) kiểm tra một ô để tìm một chuỗi con. Nếu ô chứa chuỗi con, công thức trả về TRUE. Nếu không, công thức trả về FALSE:
ISNUMBER(SEARCH(things,B5))
Tuy nhiên, trong trường hợp này, chúng tôi cung cấp cho TÌM KIẾM một danh sách các chuỗi. Vì có 5 chuỗi trong mọi thứ , SEARCH trả về 5 kết quả trong một mảng như thế này:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Khi SEARCH tìm thấy một chuỗi, nó sẽ trả về vị trí của chuỗi đó. Nếu SEARCH không tìm thấy một chuỗi, nó sẽ trả về lỗi #VALUE! lỗi. Bởi vì "màu vàng" xuất hiện như là từ đầu tiên trong B5, chúng tôi thấy một 1. Vì các chuỗi khác không được tìm thấy, 4 mục còn lại là lỗi.
Mảng này được trả về trực tiếp cho hàm ISNUMBER. Sau đó ISNUMBER trả về một mảng các giá trị TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Nếu chúng ta có thậm chí một TRUE trong mảng, chúng ta biết một ô chứa ít nhất một trong các chuỗi mà chúng ta đang tìm kiếm. Cách dễ nhất để kiểm tra ĐÚNG là cộng tất cả các giá trị lại với nhau. Chúng ta có thể làm điều đó với SUMPRODUCT, nhưng trước tiên, chúng ta cần buộc các giá trị TRUE / FALSE thành 1s và 0s với một âm kép (-) như sau:
--ISNUMBER(SEARCH(things,B5))
Điều này tạo ra một mảng mới chỉ chứa 1 và 0:
(1;0;0;0;0)
được gửi trực tiếp đến SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Chỉ với một mảng để xử lý, SUMPRODUCT sẽ thêm các mục trong mảng và trả về một kết quả. Bất kỳ kết quả nào khác 0 có nghĩa là chúng tôi có một "lần truy cập", vì vậy chúng tôi thêm> 0 để buộc kết quả cuối cùng là TRUE hoặc FALSE:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Với một danh sách được mã hóa cứng
Không cần thiết phải sử dụng một phạm vi cho danh sách các chuỗi cần tìm. Bạn cũng có thể sử dụng một hằng số mảng. Ví dụ: để kiểm tra "đỏ", "xanh dương" và "xanh lá cây", bạn có thể sử dụng công thức như sau:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>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ể nhận được kết quả phù hợp sai từ 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".
Để hack nhanh, 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 với dấu chấm câu.
Nếu bạn cần một giải pháp chính xác 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 sử dụng công thức trên trang này trên văn bản kết quả.
