Загальна формула
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Резюме
Щоб класифікувати текст за ключовими словами з відповідністю "містить", ви можете скористатися функцією ПОШУК за допомогою INDEX та MATCH. У наведеному прикладі формула в С5 має вигляд:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
де ключові слова - іменований діапазон E5: E14, а категорії - іменований діапазон F5: F14.
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Пояснення
По суті, це функція INDEX і MATCH.
Усередині функції MATCH ми використовуємо функцію SEARCH для пошуку комірок у стовпці B по кожному ключовому слову в названих ключових словах діапазону (E5: E14):
SEARCH(keywords,B5)
Оскільки ми шукаємо декілька елементів (у названих ключових словах діапазону ), ми повернемо кілька результатів, подібних до цього:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Значення! Помилка виникає, коли ПОШУК не може знайти текст. Коли SEARCH знаходить відповідність, він повертає число, яке відповідає положенню тексту всередині комірки.
Щоб змінити ці результати у більш зручний формат, ми використовуємо функцію ISNUMBER, яка перетворює всі значення в TRUE / FALSE приблизно так:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Цей масив переходить у функцію MATCH як lookup_array, а значення lookup_value має значення TRUE. MATCH повертає позицію першої TRUE, яку він знаходить у масиві (у цьому випадку 7), який надається функції INDEX як номер_ рядка:
=INDEX(categories,7)
ІНДЕКС повертає 7-й пункт у категоріях "Авто" як остаточний результат.
З XLOOKUP
За допомогою функції XLOOKUP цю формулу можна дещо спростити. XLOOKUP може використовувати ту саму логіку, що використовується всередині функції MATCH вище, тому еквівалентна формула:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP знаходить перше значення TRUE в масиві та повертає відповідне значення з категорій .
Запобігання помилковим збігам
Однією з проблем цього підходу є те, що ви можете отримати помилкові збіги з підрядків, які з’являються всередині довших слів. Наприклад, якщо ви намагаєтесь відповідати "dr", ви також можете знайти "Andrea", "drink", "dry" тощо, оскільки в цих словах з'являється "dr". Це відбувається тому, що ПОШУК автоматично виконує збіг "містить".
Для швидкого злому ви можете додати простір навколо пошукових слів (наприклад, "dr", або "dr"), щоб уникнути вловлювання "dr" в іншому слові. Але це не вдасться, якщо "dr" з'явиться першим або останнім у клітинці, або з'явиться з пунктуацією тощо.
Якщо вам потрібне більш точне рішення, одним із варіантів є нормалізація тексту спочатку у допоміжній колонці, подбавши також про те, щоб додати провідний та кінцевий пробіл. Тоді ви можете шукати цілі слова, оточені пробілами.
