Поиск ключевых слов в тексте

Поиск ключевых слов в исходном тексте - одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере:

Постановка задачи

Предположим, что у нас с вами есть список ключевых слов - названия автомобильных марок - и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если запчасть подходит больше, чем к одной марке автомобиля. Наша задача состоит в том, чтобы найти и вывести все обнаруженные ключевые слова в соседние ячейки через заданный символ-разделитель (например, запятую).

Способ 1. Power Query

Само-собой, сначала превращаем наши таблицы в динамические ("умные") с помощью сочетания клавиш Ctrl+T или команды Главная - Форматировать как таблицу (Home - Format as Table), даём им имена (например Марки и Запчасти) и загружаем по очереди в редактор Power Query, выбрав на вкладке Данные - Из таблицы/диапазона (Data - From Table/Range). Если у вас старые версии Excel 2010-2013, где Power Query установлена как отдельная надстройка, то нужная кнопка будет на вкладке Power Query. Если у вас совсем новая версия Excel 365, то кнопка Из таблицы/диапазона называется там теперь С листа (From Sheet).

После загрузки каждой таблицы в Power Query возвращаемся обратно в Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в... - Только создать подключение (Home - Close & Load - Close & Load to... - Only create connection).

Теперь создадим дубликат запроса Запчасти, щёлкнув по нему правой кнопкой мыши и выбрав команду Дублировать запрос (Duplicate query), затем переименуем получившийся запрос-копию в Результаты и дальше будем работать уже с ним.

Логика действий следующая:

  1. На вкладке Добавление столбца выбираем команду Настраиваемый столбец (Add column - Custom column) и вводим формулу =Марки. После нажатия на ОК получим новый столбец, где в каждой ячейке будет вложенная таблица со списком наших ключевых слов - марок автопроизводителей:

    Добавляем столбец с марками

  2. Кнопкой с двойными стрелками в шапке добавленного столбца разворачиваем все вложенные таблицы. Строки с описаниями запчастей при этом размножатся кратно количеству марок, и мы получим все возможные пары-сочетания "запчасть-марка":

    Развернутые таблицы

  3. На вкладке Добавление столбца выбираем команду Условный столбец (Conditional column) и задаём условие на проверку вхождения ключевого слова (марки) в исходный текст (описание запчасти):

    Проверяем вхождение марки в описание

  4. Чтобы поиск был регистроНЕчувствительный, добавляем вручную в строке формул третий аргумент Comparer.OrdinalIgnoreCase к функции проверки вхождения Text.Contains (если строки формул не видно, то её можно включить на вкладке Просмотр):

    Избавляемся от регистрочувствительности в поиске

  5. Фильтруем получившуюся таблицу, оставляя только единички в последнем столбце, т.е. совпадения и удаляем ненужный больше столбец Вхождения.
  6. Группируем одинаковые описания командой Группировать по на вкладке Преобразование (Transform - Group by). В качестве агрегирующей операции выбираем Все строки (All rows). На выходе получаем столбец с таблицами, куда собраны все подробности по каждой запчасти, включая необходимые нам марки автопроизводителей:

    Сгруппированные описания

  7. Чтобы извлечь марки для каждой запчасти, добавляем еще один вычисляемый столбец на вкладке Добавление столбца - Настраиваемый столбец (Add column - Custom column) и используем формулу, состоящую из таблицы (они у нас располагаются в столбце Подробности) и имени извлекаемого столбца:

    Вытаскиваем столбец с марками

  8. Щёлкаем по кнопке с двойными стрелками в шапке получившегося столбца и выбираем команду Извлечь значения (Extract values), чтобы вывести марки через любой желаемый символ-разделитель:

    Выводим все марки через запятую

  9. Удаляем ненужный больше столбец Подробности.
  10. Чтобы добавить к получившейся таблице исчезнувшие из неё запчасти, где в описаниях не было найдено ни одной марки - выполним процедуру объединения запроса Результат с исходным запросом Запчасти кнопкой Объединить на вкладке Главная (Home - Merge queries). Тип соединения - Внешнее соединение справа (Right outer join):

    Объединяем запросы Результаты и Запчасти

  11. Останется удалить лишние столбцы и переименовать-переместить оставшиеся - и наша задача решена:

    Результаты

Способ 2. Формулы

Если у вас версия Excel 2016 или новее, то нашу проблему можно весьма компактно и изящно решить с помощью новой функции ОБЪЕДИНИТЬ (TEXTJOIN):

Поиск и извлечение ключевых слов формулой

Логика работы этой формулы проста:

  • Функция ПОИСК (FIND) ищет вхождение по очереди каждой марки в текущее описание запчасти и выдаёт либо порядковый номер символа, начиная с которого марка была найдена, либо ошибку #ЗНАЧ! если марки в описании нет.
  • Затем при помощи функции ЕСЛИ (IF) и ЕОШИБКА (ISERROR) мы заменяем ошибки на пустую текстовую строку "", а порядковые номера символов - на сами названия марок.
  • Полученный массив из пустых ячеек и найденных марок собирается в единую строку через заданный символ-разделитель с помощью функции ОБЪЕДИНИТЬ (TEXTJOIN).

Сравнение быстродействия и буферизация запроса Power Query для ускорения

Для тестирования быстродействия возьмем в качестве исходных данных таблицу из 100 000 описаний запчастей. На ней получаем следующие результаты:

  • Время пересчета формулами (Способ 2) - 9 сек. при первом копировании формулы на весь столбец и 2 сек. при повторном (сказывается буферизация, видимо).
  • Время обновления запроса Power Query (Способ 1) гораздо хуже - 110 сек.

Само-собой, многое зависит от "железа" отдельно взятого ПК и установленной версии Office и обновлений, но общая картина, думаю, понятна.

Для ускорения запроса Power Query давайте буферизуем таблицу-справочник Марки, т.к. она у нас не меняется в процессе выполнения запроса и постоянно пересчитывать её (как это де-факто делает Power Query) не нужно. Для этого используем функцию Table.Buffer из встроенного в Power Query языка М.

Для этого откроем запрос Результаты и на вкладке Просмотр нажмём на кнопку Расширенный редактор (View - Advanced Editor). В открывшемся окне добавим строку с новой переменной Марки2, которая будет буферизованной версией нашего справочника автопроизводителей и используем эту новую переменную далее в следующей команде запроса:

Буферизуем справочник в запросе Power Query

После такой доработки скорость обновления нашего запроса возрастает почти в 7 раз - до 15 сек. Совсем другое дело :)

Ссылки по теме




18.06.2021 10:17:29
Николай, как всегда статья огонь. Спасибо.
Немножко занудства про Table.Buffer, про которое вы скорее всего знаете, но решили людям мозг чересчур не загружать.
Это не обязательно делать в запросе с запчастями, можно в буфер "завернуть" последний шаг в запросе Марки. Эффект будет достигнут аналогичный, при этом если запрос сложный и в нем имеются обращения к таблице с м арками из других запросов, то и для них таблица будет доступна также напрямую из оперативной памяти.
22.06.2021 11:00:03
А как это правильно написать в запросе "Марки"?
Вот он:
let
   Источник = Excel.CurrentWorkbook(){[Name="Марки"]}[Content],
   #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Марка", type text}})
in
   #"Измененный тип"
24.06.2021 09:17:48
Вот так:

let
   Источник = Excel.CurrentWorkbook(){[Name="Марки"]}[Content],
   #"Измененный тип" = Table.Buffer( Table.TransformColumnTypes(Источник,{{"Марка", type text}}) )
in
   #"Измененный тип"

22.06.2021 11:11:37
К сожалению в 2016 офисе нет функции "ОБЪЕДИНИТЬ" :cry:
24.06.2021 09:20:40
Появится, если вы приобретете подписку О365. Кажется так.
Да и фиг с ней, зато PQ имеется.
25.06.2021 15:07:17
Николай, добрый день, отличная работа, спасибо!!! Если есть возможность объясните, почему данный метод не работает когда, например в банковской выписке в формате Excel по списку с номерами договоров необходимо найти совпадения в столбце "назначение платежа"? На компьютере установлен Excel 365 по подписке. Спасибо!
Наверх