Привет, друзья. Сегодня интересная, нестандартная тема – категоризация текста по ключевому слову из списка. Звучит не очень понятно, сейчас объясню. Например, есть список адресов: город, дом, квартира и т.п. И есть список соответствий, в каких регионах какие располагаются города. Необходимо каждому адресу проставить его регион.

регионы неизвестны

Использовать ВПР здесь не получится, т.к. эта функция не умеет из всего адреса выделять лишь город. Формула будет сложнее обычного, но я поясню, как это работает:

=ИНДЕКС(категории ; ПОИСКПОЗ( ИСТИНА ; ЕЧИСЛО(ПОИСК( ключи ; текст )); 0 )),

где категории – список категорий, соответствующих ключам, ключи – перечень ключевых слов, текст – ссылка на текст, которому нужно определить категорию.

В нашем примере, для первой строки формула будет такой: =ИНДЕКС(категории;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(ключи;B3));0)), где

Этот метод использует формулу массива, после ввода нажмите Ctrl+Shift+Enter вместо Enter. Программа поставит фигурные скобки вокруг формулы!

Как видим, это работает. Алгоритм таков:

  1. Сначала отработает функция ПОИСК(ключи;B3), которая проверит вхождение каждого из ключевых слов в текст ячейки B3 Результатом будет массив примерно такого вида: { #ЗНАЧ!, #ЗНАЧ!, 21, #ЗНАЧ!, #ЗНАЧ!}. Это будет означать, что в проверяемой строке нет первого, второго, четвертого и пятого ключа. А третье ключевое слово встречается в нем начиная с 21 символа. То есть, в тексте найден третий ключ
  2. Теперь полученный массив нужно привести к комбинации логических значений ИСТИНА, ЛОЖЬ. Это сделает функция ЕЧИСЛО. Она проверит каждый элемент массива. Там, где содержится число – будет ИСТИНА, в остальных ячейках – ЛОЖЬ. Вот так: {ЛОЖЬ, ЛОЖЬ, ИСТИНА, ЛОЖЬ, ЛОЖЬ}
  3. Далее функция ПОИСКПОЗ в этом массиве будет искать порядковый номер первого истинного значения
  4. Теперь ИНДЕКС вернет из списка регионов элемент под номером, полученным в предыдущем пункте.

Вот и вся процедура. Немного замысловато, а если разобраться – не сложно.

Но в этом расчете есть фактор риска – ложные срабатывания. Это связано с тем, что некоторые короткие ключевые слова могут совпадать с фрагментами других, более длинных. Например, города Омск и Томск. Здесь может возникнуть путаница, и функции вернут ошибочные результаты.

Чтобы этого избежать – я часто нормализую тексты перед тем, как осуществлять поиск. Статью о нормализации ждите уже скоро!

И ещё. Если ключ для какой-то из строк не будет найден, формула вернет ошибку #Н/Д, которую рекомендую обработать с помощью функций-перехватчиков ошибок. Формула станет немного длиннее, но результат будет более практичным!

На этом всё, спасибо за прочтение!