Công thức Excel: Nếu ô chứa một trong nhiều thứ
Công thức Excel: Nếu ô chứa một trong nhiều thứ

Công thức chung
{=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0))}
Tóm lược

Để kiểm tra một ô cho một trong một số chuỗi và trả về kết quả tùy chỉnh cho kết quả phù hợp đầu tiên được tìm thấy, bạn có thể sử dụng Công thức INDEX / MATCH dựa vào Chức năng tìm kiếm. Trong ví dụ được hiển thị, công thức trong C5 là:

{=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0))}

Ở đâu nhiều thứ (E5: E8) và các kết quả (F5: F8) là phạm vi được đặt tên.

Đây là công thức mảng và phải được nhập bằng Control + Shift + Enter.

Giải trình

Công thức này sử dụng hai phạm vi được đặt tên: nhiều thứ, và các kết quả. Nếu bạn đang chuyển trực tiếp công thức này, hãy đảm bảo sử dụng các dải ô được đặt tên có cùng tên (được xác định dựa trên dữ liệu của bạn). Nếu bạn không muốn sử dụng các dải ô đã đặt tên, hãy sử dụng tài liệu tham khảo tuyệt đối thay thế.

Cốt lõi của công thức này là đoạn mã này:

ISNUMBER(SEARCH(things,B5)

Điều này dựa trên một công thức khác (giải thích chi tiết tại đây) để kiểm tra một ô cho 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.

Bởi vì chúng tôi đang cho Chức năng tìm kiếm nhiều thứ để tìm kiếm, trong phạm vi được đặt tên nhiều thứ, nó sẽ cung cấp cho chúng ta nhiều hơn một kết quả, trong một mảng trông giống như sau:

{#VALUE!;9;#VALUE!;#VALUE!}

Các con số đại diện cho các trận đấu trong nhiều thứ, lỗi đại diện cho các mục không được tìm thấy.

Để đơn giản hóa mảng, chúng tôi sử dụng Hàm ISNUMBER để chuyển đổi tất cả các mục trong mảng thành TRUE hoặc FALSE. Mọi số hợp lệ đều trở thành TRUE và bất kỳ lỗi nào (tức là không tìm thấy thứ) sẽ trở thành FALSE. Kết quả là một mảng như thế này:

{FALSE;TRUE;FALSE;FALSE}

đi vào hàm MATCH dưới dạng lookup_array đối số, với một lookup_value Của sự thật:

MATCH(TRUE,{FALSE;TRUE;FALSE;FALSE},0) // returns 2

MATCH sau đó trả về vị trí của TRUE đầu tiên được tìm thấy, 2 trong trường hợp này.

Cuối cùng, chúng tôi sử dụng Hàm INDEX để lấy kết quả từ phạm vi đã đặt tên các kết quả tại cùng một vị trí:

=INDEX(results,2) // returns "found red"

Bạn có thể tùy chỉnh các kết quả phạm vi với bất kỳ giá trị nào có ý nghĩa trong trường hợp sử dụng của bạn.

Ngăn chặn kết quả phù hợp sai

Một vấn đề với phương pháp này với phương pháp ISNUMBER + TÌM KIẾM là bạn có thể nhận được kết quả phù hợp sai từ các kết quả phù hợp từng phần bên trong các từ dài hơn. Ví dụ: nếu bạn cố gắng so khớp “dr”, bạn cũng có thể tìm thấy “Andrea”, “đã uống”, “nhỏ giọt”, 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 “loại chứa”.

Để khắc phục nhanh, bạn có thể bọc các từ tìm kiếm trong các ký tự khoảng trắng (tức là “dr” hoặc “dr”) để tránh tìm thấy “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 ô.

Nếu bạn cần một giải pháp mạnh mẽ hơn, một lựa chọn là bình thường hóa văn bản đầu tiên trong một cột trợ giúpvà thêm khoảng trắng ở đầu và cuối. Sau đó, sử dụng công thức trên trang này trên văn bản trong cột trợ giúp, thay vì văn bản gốc.

https://exceljet.net/formula/if-cell-contains-one-of-many-things