Содержание:
Поиск ключевых слов в исходном тексте — одна из наиболее частых задач при работе с данными. Рассмотрим ее решение несколькими способами на следующем примере:
Предположим, что у нас с вами есть список ключевых слов – названий марок автомобилей – и большая таблица всевозможных запчастей, где описания иногда могут содержать одну или сразу несколько таких марок, если запчасть подходит более чем к одной. марка автомобиля. Наша задача — найти и отобразить все обнаруженные ключевые слова в соседних ячейках через заданный символ-разделитель (например, запятую).
Способ 1. Power Query
Конечно, сначала мы превратим наши таблицы в динамические («умные») с помощью сочетания клавиш. Ctrl+T или команды Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу), дайте им имена (например Штампыи Запасные части) и загружайте по одному в редактор Power Query, выбрав на вкладке Данные – из таблицы/диапазона (Данные — из таблицы/диапазона). Если у вас более старые версии Excel 2010-2013, где Power Query установлен как отдельная надстройка, то нужная кнопка будет находиться на вкладке Power Query. Если у вас совершенно новая версия Excel 365, то кнопка Из таблицы/диапазона звонил туда сейчас С листьями (Из листа).
После загрузки каждой таблицы в Power Query возвращаемся обратно в Excel командой Главная страница — Закрыть и загрузить — Закрыть и загрузить в… — Только создать соединение (Главная страница — Закрыть и загрузить — Закрыть и загрузить в… — Только создать соединение).
Теперь создадим дубликат запроса Запасные частищелкнув по нему правой кнопкой мыши и выбрав Дубликат запроса (Дубликат запроса), затем переименуйте полученный запрос на копирование в Результаты и мы продолжим с ним работать.
Логика действий следующая:
- На вкладке Дополнительно Добавление столбца выбрать команду Пользовательский столбец (Добавить столбец — Пользовательский столбец) и введите формулу = Бренды. После нажатия на OK мы получим новый столбец, где в каждой ячейке будет вложенная таблица со списком наших ключевых слов — марок автопроизводителей:
- Используйте кнопку с двойными стрелками в заголовке добавленного столбца, чтобы развернуть все вложенные таблицы. При этом строки с описаниями запчастей умножатся на кратное числу марок, и мы получим все возможные пары-комбинации «запчасть-марка»:
- На вкладке Дополнительно Добавление столбца выбрать команду Условный столбец (Условный столбец) и задаем условие проверки появления ключевого слова (бренда) в исходном тексте (описании части):
- Чтобы сделать поиск нечувствительным к регистру, вручную добавьте третий аргумент в строку формул. Сравнить.OrdinalIgnoreCase к функции проверки вхождения Текст.Содержит (если строка формул не видна, то ее можно включить на вкладке Обзор):
- Фильтруем полученную таблицу, оставляя в последнем столбце только единицы, т.е. совпадения и удаляем ненужный столбец вхождений.
- Группировка одинаковых описаний командой Группа по таб трансформация (Преобразование — Группировка). В качестве операции агрегирования выберите Все строки (Все строки). На выходе мы получаем столбец с таблицами, в которых указаны все реквизиты по каждой запчасти, включая нужные нам марки автопроизводителей:
- Чтобы получить оценки для каждой части, добавьте на вкладку еще один вычисляемый столбец. Добавление столбца – Пользовательский столбец (Добавить столбец — Пользовательский столбец) и воспользуемся формулой, состоящей из таблицы (они расположены в нашем столбце Подробнее) и имя извлеченного столбца:
- Нажимаем на кнопку с двойными стрелками в заголовке полученного столбца и выбираем команду Извлечение значений (Извлечь значения)для вывода штампов с любым символом-разделителем:
- Удаление ненужного столбца Подробнее.
- Чтобы добавить в полученную таблицу исчезнувшие из нее детали, где в описаниях не обнаружено марок, выполняем процедуру объединения запроса Результат с оригинальным запросом Запасные части кнопка Сочетать таб Главная (Главная страница — Объединение запросов). Тип соединения - Внешнее соединение справа (Правое внешнее соединение):
- Остается только удалить лишние столбцы и переименовать-переместить оставшиеся — и наша задача решена:
Способ 2. Формулы
Если у вас версия Excel 2016 или новее, то нашу проблему можно очень компактно и элегантно решить с помощью новой функции КОМБИНАТ (ТЕКСТ ПРИСОЕДИНЯЙТЕСЬ):
Логика этой формулы проста:
- Функция ПОИСК (НАХОДИТЬ) ищет вхождение каждой марки по очереди в текущем описании детали и возвращает либо серийный номер символа, начиная с которого была найдена марка, либо ошибку #ЗНАЧЕНИЕ! если бренда нет в описании.
- Затем с помощью функции IF (ЕСЛИ) и ЭОШИБКА (ОШИБКА) ошибки заменяем пустой текстовой строкой «», а порядковые номера символов самими названиями брендов.
- Полученный массив пустых ячеек и найденных брендов собирается в одну строку через заданный символ-разделитель с помощью функции КОМБИНАТ (ТЕКСТ ПРИСОЕДИНЯЙТЕСЬ).
Сравнение производительности и буферизация запросов Power Query для ускорения
Для тестирования производительности в качестве исходных данных возьмем таблицу из 100 описаний запчастей. На нем мы получаем следующие результаты:
- Время пересчета по формулам (Метод 2) – 9 сек. при первом копировании формулы на весь столбец и 2 сек. при повторных (сказывается, наверное, буферизация).
- Время обновления запроса Power Query (Метод 1) значительно хуже — 110 секунд.
Конечно, многое зависит от аппаратной части конкретного ПК и установленной версии Офиса и обновлений, но общая картина, думаю, ясна.
Чтобы ускорить запрос Power Query, давайте буферизуем таблицу поиска. Штампы, поскольку он не меняется в процессе выполнения запроса и его не нужно постоянно пересчитывать (как это делает де-факто Power Query). Для этого мы используем функцию Таблица.Буфер из встроенного языка Power Query M.
Для этого откройте запрос Результаты и на вкладке Обзор нажми на кнопку Расширенный редактор (Вид — Расширенный редактор). В открывшемся окне добавьте строку с новой переменной Марки 2, которая будет буферизованной версией нашего каталога автопроизводителей, и используйте эту новую переменную позже в следующей команде запроса:
После такой доработки скорость обновления нашего запроса увеличивается почти в 7 раз — до 15 секунд. Совсем другое дело 🙂
- Нечеткий текстовый поиск в Power Query
- Массовая замена текста формулами
- Массовая замена текста в Power Query с помощью функции List.Accumulate