Công thức chung
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Tóm lược
Để phân loại văn bản bằng cách sử dụng các từ khóa có kết hợp "chứa", bạn có thể sử dụng chức năng TÌM KIẾM, với sự trợ giúp từ INDEX và MATCH. Trong ví dụ được hiển thị, công thức trong C5 là:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
trong đó từ khóa là phạm vi được đặt tên E5: E14 và danh mục là phạm vi được đặt tên F5: F14.
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.
Giải trình
Về cốt lõi, đây là một hàm INDEX và MATCH.
Bên trong hàm MATCH, chúng tôi sử dụng hàm TÌM KIẾM để tìm kiếm các ô trong cột B cho mọi từ khóa được liệt kê trong các từ khóa phạm vi được đặt tên (E5: E14):
SEARCH(keywords,B5)
Bởi vì chúng tôi đang tìm kiếm nhiều mục (trong phạm vi từ khóa được đặt tên ), chúng tôi sẽ nhận được nhiều kết quả như sau:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Giá trị! xảy ra lỗi khi SEARCH không thể tìm thấy văn bản. Khi SEARCH tìm thấy một kết quả phù hợp, nó sẽ trả về một số tương ứng với vị trí của văn bản bên trong ô.
Để thay đổi những kết quả này thành một định dạng dễ sử dụng hơn, chúng tôi sử dụng hàm ISNUMBER, hàm này chuyển đổi tất cả các giá trị thành TRUE / FALSE như sau:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Mảng này đi vào hàm MATCH dưới dạng lookup_array, với lookup_value được đặt là TRUE. MATCH trả về vị trí của TRUE đầu tiên mà nó tìm thấy trong mảng (7 trong trường hợp này) được cung cấp cho hàm INDEX dưới dạng row_num:
=INDEX(categories,7)
INDEX trả về mục thứ 7 trong danh mục , "Tự động", là kết quả cuối cùng.
Với XLOOKUP
Với hàm XLOOKUP, công thức này có thể được đơn giản hóa phần nào. XLOOKUP có thể sử dụng cùng một logic được sử dụng bên trong hàm MATCH ở trên, vì vậy công thức tương đương là:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP định vị TRUE đầu tiên trong mảng và trả về giá trị tương ứng từ các danh mục .
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, v.v.
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 có thể tìm kiếm toàn bộ các từ được bao quanh bởi dấu cách.
