Общая формула
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Резюме
Чтобы классифицировать текст с помощью ключевых слов с соответствием "содержит", вы можете использовать функцию ПОИСК с помощью ИНДЕКС и ПОИСКПОЗ. В показанном примере формула C5 имеет следующий вид:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
где ключевые слова - это именованный диапазон E5: E14, а категории - именованный диапазон F5: F14.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
По сути, это функции ИНДЕКС и ПОИСКПОЗ.
Внутри функции MATCH, мы используем функцию поиска для поиска ячеек в столбце B для каждого из перечисленных ключевых слов в именованный диапазон ключевых слов (Е5: Е14):
SEARCH(keywords,B5)
Поскольку мы ищем несколько элементов (в ключевых словах именованного диапазона ), мы получим несколько таких результатов:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Значение! ошибка возникает, когда ПОИСК не может найти текст. Когда ПОИСК находит совпадение, он возвращает число, соответствующее положению текста внутри ячейки.
Чтобы преобразовать эти результаты в более удобный формат, мы используем функцию ISNUMBER, которая преобразует все значения в TRUE / FALSE следующим образом:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Этот массив входит в функцию ПОИСКПОЗ как lookup_array, при этом lookup_value устанавливается как TRUE. ПОИСКПОЗ возвращает позицию первого значения ИСТИНА, которое он находит в массиве (в данном случае 7), который предоставляется функции ИНДЕКС как row_num:
=INDEX(categories,7)
В качестве окончательного результата INDEX возвращает седьмой элемент в категории «Авто».
С XLOOKUP
С помощью функции XLOOKUP эту формулу можно несколько упростить. XLOOKUP может использовать ту же логику, что и вышеупомянутая функция ПОИСКПОЗ, поэтому эквивалентная формула:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP находит первое значение ИСТИНА в массиве и возвращает соответствующее значение из категорий .
Предотвращение ложных совпадений
Одна из проблем этого подхода заключается в том, что вы можете получить ложные совпадения из подстрок, которые появляются внутри более длинных слов. Например, если вы попытаетесь сопоставить «dr», вы также можете найти «Andrea», «drink», «dry» и т. Д., Поскольку «dr» появляется внутри этих слов. Это происходит потому, что ПОИСК автоматически находит совпадение "содержит".
Для быстрого взлома вы можете добавить пробел вокруг поисковых слов (например, «dr» или «dr»), чтобы не поймать «dr» в другом слове. Но это не удастся, если "dr" появляется первым или последним в ячейке, или отображается с пунктуацией и т. Д.
Если вам нужно более точное решение, один из вариантов - сначала нормализовать текст во вспомогательном столбце, позаботившись также о добавлении начального и конечного пробелов. Затем вы можете искать целые слова, окруженные пробелами.
