Нещодавно до мене звернувся один мій друг з проханням допомогти згенерувати всі можливі фрази, що складаються з набору заданих слів. Подібні проблеми можуть виникнути при складанні списків ключових слів і фраз для інтернет-реклами та SEO-просування, коли потрібно перебрати всі можливі перестановки слів у пошуковому запиті:
У математиці ця операція називається Декартовий продукт. Офіційне визначення таке: декартів добуток множин A і B — це множина всіх пар, перша компонента яких належить множині A, а друга — множині B. Причому елементами множин можуть бути як числа і текст.
У перекладі на людську мову це означає, що якщо в множині А ми маємо, наприклад, слова «білий» і «червоний», а в множині B «БМВ» і «Мерседес», то після декартового добутку цих двох множин ми отримати на виході набір усіх можливих варіантів фраз, складених зі слів обох списків:
- білий bmw
- червоний bmw
- білий мерседес
- червоний мерседес
… тобто саме те, що нам потрібно. Давайте розглянемо пару способів вирішення цього завдання в Excel.
Спосіб 1. Формули
Почнемо з формул. Припустимо, що в якості вихідних даних ми маємо три списки оригінальних слів у стовпцях A, B і C відповідно, і кількість елементів у кожному списку може бути різною:
Спочатку зробимо три колонки з індексами, тобто порядковими номерами слів з кожного списку в усіх можливих комбінаціях. Перший рядок одиниць (E2:G2) буде введено вручну, а для решти скористаємося такою формулою:
Логіка тут проста: якщо індекс у верхній попередній комірці вже досяг кінця списку, тобто дорівнює кількості елементів у списку, обчисленій функцією, COUNT (COUNTA), то знову починаємо нумерацію. В іншому випадку ми збільшуємо індекс на 1. Зверніть особливу увагу на розумну фіксацію діапазонів знаками долара ($), щоб можна було скопіювати формулу вниз і праворуч.
Тепер, коли ми маємо порядкові номери потрібних нам слів з кожного списку, ми можемо витягти самі слова за допомогою функції ІНДЕКС (ІНДЕКС) у три окремі колонки:
Якщо ви раніше не стикалися з цією функцією у своїй роботі, то настійно раджу вивчити її хоча б по діагоналі – вона виручає в багатьох ситуаціях і корисна не менше (а навіть більше!) VPR (ВПР).
Ну а після цього залишиться тільки склеїти отримані фрагменти рядок за рядком за допомогою символу конкатенації (&):
… або (якщо у вас остання версія Excel) за допомогою зручної функції СПІЛЬНО (ТЕКСТ ПРИЄДНАТИ), який може склеювати весь вміст вказаних комірок через заданий роздільник (пробіл):
Спосіб 2. Через Power Query
Power Query — це потужна надбудова для Microsoft Excel, яка виконує два основних завдання: 1. завантаження даних у Excel практично з будь-якого зовнішнього джерела та 2. усілякі перетворення завантажених таблиць. Power Query вже вбудований в Excel 2016-2019, а для Excel 2010-2013 встановлений як окрема надбудова (її можна безкоштовно завантажити з офіційного сайту Microsoft). Якщо ви ще не почали використовувати Power Query у своїй роботі, то пора про це подумати, тому що перетворення, подібні описаним вище, там виконуються легко і природно, всього за пару рухів.
По-перше, давайте завантажимо вихідні списки як окремі запити в Power Query. Для цього для кожної таблиці виконайте такі дії:
- Перетворимо столи на «розумні» за допомогою кнопки Відформатувати як таблицю таб Головна (Домашня сторінка — форматувати як таблицю) або комбінацію клавіш Ctrl+T. Кожній таблиці буде автоматично надано назву Таблиця1,2,3…, який, однак, можна змінити за бажанням на вкладці Конструктор (Дизайн).
- Задавши активну комірку в таблиці, натисніть кнопку Зі столу (З таблиці) таб дані (Дата) або на вкладці PowerQuery (якщо він встановлений як окрема надбудова для Excel 2010-2013).
- У вікні редактора запитів, що відкриється, виберіть команду Головна — Закрийте та завантажте — Закрийте та завантажте в… (Домашня сторінка — Close&Load — Close&Load to..) а потім варіант Просто створіть зв'язок (Створити лише з'єднання). Це залишить завантажену таблицю в пам’яті та дозволить отримати до неї доступ у майбутньому.
Якщо ви все зробите правильно, то в правій панелі має вийти три запити в режимі Тільки підключення з нашими назвами таблиць:
Тепер клацніть правою кнопкою миші на першому запиті та виберіть команду посилання (Довідка)щоб створити його оновлювану копію, а потім додати додатковий стовпець до даних за допомогою команди Додавання стовпця ž – користувацький стовпець (Додати стовпець -ž настроюваний стовпець). У вікні введення формули введіть назву нового стовпця (наприклад, Фрагмент2) і дуже простий вираз у вигляді формули:
=Таблиця2
… тобто, іншими словами, назва другого запиту:
Натиснувши на OK ми побачимо новий стовпець, у кожній клітинці якого буде вкладена таблиця з фразами з другої таблиці (ви можете побачити вміст цих таблиць, якщо клацнути на фоні клітинки біля слова таблиця):
Залишилося розгорнути весь вміст цих вкладених таблиць за допомогою кнопки з подвійними стрілками в заголовку отриманого стовпця і зняти галочку Використовуйте оригінальну назву стовпця як префікс (Використовуйте оригінальну назву стовпця як префікс):
… і ми отримуємо всі можливі комбінації елементів з перших двох наборів:
Далі все аналогічно. Додайте ще один обчислюваний стовпець із формулою:
=Таблиця3
…, а потім знову розгорніть вкладені таблиці – і тепер у нас вже є всі можливі варіанти перестановки слів із трьох наборів відповідно:
Залишилося виділити всі три колонки зліва направо, тримаючи Ctrl, і об’єднати їхній вміст, розділивши їх пробілами, за допомогою команди Об’єднати стовпці (Об’єднати стовпці) із вкладки Перетворення (Трансформація):
Отримані результати можна вивантажити назад на аркуш за допомогою вже знайомої команди Головна — Закрийте та завантажте — Закрийте та завантажте в… (Домашня сторінка — Close&Load — Close&Load to..):
Якщо в майбутньому в наших вихідних таблицях з фрагментами щось зміниться, то достатньо буде просто оновити згенерований запит, клацнувши правою кнопкою миші на отриманій таблиці та вибравши команду Update & Save (Оновити) або натиснувши комбінацію клавіш Ctrl+інший+F5.
- Що таке Power Query, Power Pivot, Power Map і Power BI і навіщо їм потрібен користувач Excel
- Створення діаграми Ганта в Power Query
- 5 способів використання функції INDEX