Поиск ключевых слов в тексте
Поиск ключевых слов в исходном тексте - одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере:
Предположим, что у нас с вами есть список ключевых слов - названия автомобильных марок - и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если запчасть подходит больше, чем к одной марке автомобиля. Наша задача состоит в том, чтобы найти и вывести все обнаруженные ключевые слова в соседние ячейки через заданный символ-разделитель (например, запятую).
Способ 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), затем переименуем получившийся запрос-копию в Результаты и дальше будем работать уже с ним.
Логика действий следующая:
- На вкладке Добавление столбца выбираем команду Настраиваемый столбец (Add column - Custom column) и вводим формулу =Марки. После нажатия на ОК получим новый столбец, где в каждой ячейке будет вложенная таблица со списком наших ключевых слов - марок автопроизводителей:
- Кнопкой с двойными стрелками в шапке добавленного столбца разворачиваем все вложенные таблицы. Строки с описаниями запчастей при этом размножатся кратно количеству марок, и мы получим все возможные пары-сочетания "запчасть-марка":
- На вкладке Добавление столбца выбираем команду Условный столбец (Conditional column) и задаём условие на проверку вхождения ключевого слова (марки) в исходный текст (описание запчасти):
- Чтобы поиск был регистроНЕчувствительный, добавляем вручную в строке формул третий аргумент Comparer.OrdinalIgnoreCase к функции проверки вхождения Text.Contains (если строки формул не видно, то её можно включить на вкладке Просмотр):
- Фильтруем получившуюся таблицу, оставляя только единички в последнем столбце, т.е. совпадения и удаляем ненужный больше столбец Вхождения.
- Группируем одинаковые описания командой Группировать по на вкладке Преобразование (Transform - Group by). В качестве агрегирующей операции выбираем Все строки (All rows). На выходе получаем столбец с таблицами, куда собраны все подробности по каждой запчасти, включая необходимые нам марки автопроизводителей:
- Чтобы извлечь марки для каждой запчасти, добавляем еще один вычисляемый столбец на вкладке Добавление столбца - Настраиваемый столбец (Add column - Custom column) и используем формулу, состоящую из таблицы (они у нас располагаются в столбце Подробности) и имени извлекаемого столбца:
- Щёлкаем по кнопке с двойными стрелками в шапке получившегося столбца и выбираем команду Извлечь значения (Extract values), чтобы вывести марки через любой желаемый символ-разделитель:
- Удаляем ненужный больше столбец Подробности.
- Чтобы добавить к получившейся таблице исчезнувшие из неё запчасти, где в описаниях не было найдено ни одной марки - выполним процедуру объединения запроса Результат с исходным запросом Запчасти кнопкой Объединить на вкладке Главная (Home - Merge queries). Тип соединения - Внешнее соединение справа (Right outer join):
- Останется удалить лишние столбцы и переименовать-переместить оставшиеся - и наша задача решена:
Способ 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, которая будет буферизованной версией нашего справочника автопроизводителей и используем эту новую переменную далее в следующей команде запроса:
После такой доработки скорость обновления нашего запроса возрастает почти в 7 раз - до 15 сек. Совсем другое дело :)
Ссылки по теме
- Нечёткий текстовый поиск в Power Query
- Массовая замена текста формулами
- Массовая замена текста в Power Query функцией List.Accumulate
Немножко занудства про Table.Buffer, про которое вы скорее всего знаете, но решили людям мозг чересчур не загружать.
Это не обязательно делать в запросе с запчастями, можно в буфер "завернуть" последний шаг в запросе Марки. Эффект будет достигнут аналогичный, при этом если запрос сложный и в нем имеются обращения к таблице с м арками из других запросов, то и для них таблица будет доступна также напрямую из оперативной памяти.
Вот он:
let
Источник = Excel.CurrentWorkbook(){[Name="Марки"]}[Content],
#"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Марка", type text}})
in
#"Измененный тип"
let Источник = Excel.CurrentWorkbook(){[Name="Марки"]}[Content], #"Измененный тип" = Table.Buffer( Table.TransformColumnTypes(Источник,{{"Марка", type text}}) ) in #"Измененный тип"Да и фиг с ней, зато PQ имеется.
а можно ли список (в данном примере марок авто) сформировать непосредственно в PQ?
у меня задача следующая - есть список сотрудников, и описание активностей каждого сотрудника с указанием времени, которое тратится - грубо говоря - фотография рабочего дня (несколько больших подразделений - под 20 тыс.записей всего в объединенной таблице). понятно, что по каждому сотруднику может быть несколько строк, в каждой строке (к сожалению формат и требования задавал не я) описание работ каждый делал по своему, соответственно встречается описание достаточно разноплановых работ в одной строке
нужно:
1. сформировать список работ (возможно по частоте встречаемости)
2. "классифицировать" работы, исходя из списка по п.1.
до этой статьи я сделал эту работу вручную сформировав список, а потом добавил условный столбец "тип работ" и путем 30 условий сделал эту классификацию - понятно, что при следующем запросе, воспользуюсь этим способом, но если возможно, подскажите плиз способ формирования перечня активностей
спасибо
Но можно немного упростить как работу, так и код.
В запросе Результаты нет необходимости в первом удалении столбца, а объединение запросов имеет смысл делать в запросе Запчасти (тогда не придется удалять, переставлять и переименовывать столбцы) и, соответственно, запрос Результаты оставить только, как подключение. Так еще быстрее происходит загрузка/обновление данных.
Запчасти
let Источник = Excel.CurrentWorkbook(){[Name="Запчасти"]}[Content], #"Объединенные запросы" = Table.NestedJoin(Источник,{"Описание"},Результаты,{"Описание"},"Результаты",JoinKind.LeftOuter), #"Развернутый элемент Результаты" = Table.ExpandTableColumn(#"Объединенные запросы", "Результаты", {"Все марки"}, {"Все марки"}) in #"Развернутый элемент Результаты"let Источник = Table.Buffer(Excel.CurrentWorkbook(){[Name="Марки"]}[Content]) in Источникlet Источник = Excel.CurrentWorkbook(){[Name="Запчасти"]}[Content], #"Добавлен пользовательский объект" = Table.AddColumn(Источник, "Пользовательский", each Марки), #"Развернутый элемент Пользовательский" = Table.ExpandTableColumn(#"Добавлен пользовательский объект", "Пользовательский", {"Марка"}, {"Марка"}), #"Условный столбец добавлен" = Table.AddColumn(#"Развернутый элемент Пользовательский", "Вхождение", each if Text.Contains([Описание], [Марка], Comparer.OrdinalIgnoreCase) then 1 else 0), #"Строки с примененным фильтром" = Table.SelectRows(#"Условный столбец добавлен", each ([Вхождение] = 1)), #"Сгруппированные строки" = Table.Group(#"Строки с примененным фильтром", {"Описание"}, {{"Подробности", each _, type table [Артикул=nullable text, Описание=nullable text, Марка=text]}}), #"Добавлен пользовательский объект1" = Table.AddColumn(#"Сгруппированные строки", "Все марки", each [Подробности][Марка]), #"Извлеченные значения" = Table.TransformColumns(#"Добавлен пользовательский объект1", {"Все марки", each Text.Combine(List.Transform(_, Text.From), ", "), type text}) in #"Извлеченные значения"