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