Как в эксель сделать поиск по тексту - Учим Эксель

Поиск главных слов в тексте

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

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

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

Метод 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) и вводим формулу =Марки . Опосля нажатия на ОК получим новейший столбец, где в каждой ячейке будет вложенная таблица со перечнем наших главных слов — марок автопроизводителей:

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

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

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

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

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

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

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

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

Метод 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 языка М.

Интересно почитать:  Форматирование таблиц в excel

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

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

Опосля таковой доработки скорость обновления нашего запроса увеличивается практически в 7 раз — до 15 сек. Совершенно другое дело 🙂

Ссылка на основную публикацию
Adblock
detector