Генератор фраз из заданных фрагментов

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

В математике эта операция называется Декартово произведение. Официальное определение таково: декартово произведение множеств A и B — это множество всех пар, первый компонент которого принадлежит множеству A, а второй компонент — множеству B. При этом элементами множеств могут быть как цифры и текст.

В переводе на человеческий язык это означает, что если в множестве А у нас есть, например, слова «белый» и «красный», а в множестве Б «БМВ» и «Мерседес», то после декартова произведения этих двух множеств мы на выходе получим набор всех возможных вариантов фраз, составленный из слов обоих списков:

• белый БМВ
• красный БМВ
• белый Мерседес
• красный мерседес
  

… то есть именно то, что нам нужно. Давайте рассмотрим пару способов решения этой задачи в Excel.

Способ 1. Формулы

Начнем с формул. Предположим, что в качестве исходных данных мы имеем три списка исходных слов в столбцах A, B и C соответственно, причем количество элементов в каждом списке может варьироваться:

Для начала сделаем три столбца с индексами, т.е. порядковыми номерами слов из каждого списка во всех возможных комбинациях. Первая строка единиц (E2:G2) будет введена вручную, а для остальных мы будем использовать следующую формулу:

Логика здесь проста: если индекс в вышестоящей предыдущей ячейке уже достиг конца списка, т.е. равен количеству элементов в списке, рассчитанному функцией СЧИТАТЬ (СЧЁТКА), затем возобновляем нумерацию. В противном случае мы увеличиваем индекс на 1. Особое внимание обратите на хитроумное фиксирование диапазонов знаками доллара ($), чтобы можно было скопировать формулу вниз и вправо.

Теперь, когда у нас есть порядковые номера нужных нам слов из каждого списка, мы можем извлечь сами слова с помощью функции ИНДЕКС (ПОКАЗАТЕЛЬ) на три отдельных столбца:

Если вы раньше не сталкивались в своей работе с этой функцией, то настоятельно советую изучить ее хотя бы по диагонали – она выручает во многих ситуациях и полезна не меньше (а даже больше!) ВПР (ВПР).

Ну а после этого останется только склеить полученные фрагменты построчно с помощью символа конкатенации (&):

… или (если у вас последняя версия Excel) с помощью удобной функции КОМБИНАТ (ТЕКСТ ПРИСОЕДИНЯЙТЕСЬ), который может склеить все содержимое указанных ячеек через заданный символ-разделитель (пробел):

Способ 2. Через Power Query

Power Query — мощная надстройка для Microsoft Excel, выполняющая две основные задачи: 1. загрузка данных в Excel практически из любого внешнего источника и 2. всевозможные преобразования загруженных таблиц. Power Query уже встроен в Excel 2016-2019, а для Excel 2010-2013 установлен как отдельная надстройка (скачать ее можно бесплатно с официального сайта Microsoft). Если вы еще не начали использовать Power Query в своей работе, то пора задуматься, ведь преобразования, подобные описанным выше, там выполняются легко и естественно, всего за пару движений.

Сначала давайте загрузим списки источников как отдельные запросы в Power Query. Для этого для каждой таблицы выполните следующие действия:

1. Превратим столы в «умные» с помощью кнопки Форматировать как таблицу таб Главная (Главная страница — Форматировать как таблицу) или сочетание клавиш Ctrl+T. Каждой таблице автоматически будет присвоено имя Таблица1,2,3…, который, впрочем, при желании можно изменить на вкладке Конструктор (Дизайн).
   
2. Установив активную ячейку в таблице, нажмите кнопку Из таблицы (из таблицы) таб Данные (Дата) или на вкладке Power Query (если он у вас установлен как отдельная надстройка для Excel 2010-2013).
3. В открывшемся окне редактора запросов выберите команду На главную — Закрыть и загрузить — Закрыть и загрузить… (Главная страница — Закрыть и загрузить — Закрыть и загрузить в..) а потом вариант Просто создайте соединение (Создать только соединение). Это оставит загруженную таблицу в памяти и позволит получить к ней доступ в будущем.

Если вы все делаете правильно, то на выходе в правой панели должно быть три запроса в режиме Только подключение с именами наших таблиц:

Теперь щелкните правой кнопкой мыши по первому запросу и выберите команду Ссылка (Ссылка)сделать его обновляемую копию, а затем добавить к данным дополнительный столбец с помощью команды Добавление столбца ž – Пользовательский столбец (Добавить столбец – ž Пользовательский столбец). В окне ввода формулы введите имя нового столбца (например, Фрагмент2) и предельно простое выражение в виде формулы:

=Таблица2

… т.е., другими словами, имя второго запроса:

После нажатия на OK мы увидим новый столбец, в каждой ячейке которого будет вложенная таблица с фразами из второй таблицы (содержимое этих таблиц можно увидеть, если нажать на фон ячейки рядом со словом Настольные):

Осталось развернуть все содержимое этих вложенных таблиц, воспользовавшись кнопкой с двойными стрелками в заголовке полученного столбца и сняв галочку Использовать исходное имя столбца в качестве префикса (Используйте исходное имя столбца в качестве префикса):

… и получаем все возможные комбинации элементов из первых двух наборов:

Дальше все аналогично. Добавьте еще один вычисляемый столбец с формулой:

=Таблица3

…, а затем снова разверните вложенные таблицы — и теперь у нас уже есть все возможные варианты перестановки слов из трёх наборов соответственно:

Осталось выделить все три столбца слева направо, удерживая Ctrlи объединить их содержимое, разделенное пробелами, с помощью команды Объединить столбцы (Объединить столбцы) на вкладке трансформация (Преобразование):

Полученные результаты можно выгрузить обратно на лист уже знакомой командой На главную — Закрыть и загрузить — Закрыть и загрузить… (Главная страница — Закрыть и загрузить — Закрыть и загрузить в..):

Если в дальнейшем что-то изменится в наших исходных таблицах с фрагментами, то достаточно будет просто обновить сгенерированный запрос, щелкнув правой кнопкой мыши по полученной таблице и выбрав команду Обновить и сохранить (Обновить) или нажав сочетание клавиш Ctrl+другой+F5.

• Что такое Power Query, Power Pivot, Power Map и Power BI и зачем им нужен пользователь Excel
  
• Создание диаграммы Ганта в Power Query
• 5 способов использования функции ИНДЕКС