Создание перекрёстного соединения (все комбинации) из 2 столбцов в Excel — Полное руководство
При работе с двумя списками в Excel — например, названиями товаров и размерами, регионами и торговыми представителями или студентами и курсами — часто возникает необходимость создать все возможные комбинации элементов из этих списков. Такая операция называется перекрёстным соединением (Crossjoin), или декартовым произведением. В этом руководстве вы найдёте пошаговые инструкции, плюсы и минусы каждого подхода, а также реальные примеры, которые помогут выбрать метод, наилучшим образом соответствующий вашему рабочему процессу.
Что такое перекрёстное соединение?
Перекрёстное соединение (также известное как декартово произведение) — это операция, которая создаёт все возможные комбинации элементов двух списков. В Excel это означает, что каждый элемент списка А сопоставляется с каждым элементом списка B, формируя полную матрицу сочетаний.
Перекрёстные соединения чрезвычайно полезны во многих реальных сценариях работы с данными, например:
Варианты товаров
Объединяйте цвета, размеры и стили, чтобы создать полный каталог продукции.
Анализ продаж
Создавайте комбинации регионов × торговых представителей × кварталов.
Планирование и составление расписаний
Создайте все возможные комбинации сотрудников × смен или студентов × курсов.
Тестирование и моделирование
Создавайте различные комбинации сценариев для моделирования, прогнозирования или проверки.
Пример:
Если у вас есть:
Результат Crossjoin будет:
Выполнение перекрёстного соединения в Excel
Excel предлагает несколько способов создания перекрёстного соединения, и выбор оптимального метода зависит от версии Excel, вашего уровня комфорта при работе с формулами или инструментами, а также от объёма данных. Ниже — четыре практичных и эффективных подхода: от простых формул до продвинутых решений, таких как Power Query и VBA. Каждый из них имеет свои преимущества, так что вы сможете выбрать тот, что лучше всего соответствует вашему рабочему процессу, объёму данных и требованиям к автоматизации.
Метод 1: перекрёстное соединение с помощью формулы (Excel 365)
1.Подготовьте данные. Разместите первый список в одном столбце (например, A2:A5 для товаров), а второй — в другом столбце (например, C2:C5 для цветов).
2.Введите следующую формулу в пустую ячейку, куда нужно вывести результат, и нажмите клавишу Enter. Формула автоматически заполнит диапазон и сразу вернёт все сгенерированные комбинации. См. снимок экрана:
=TEXTSPLIT(TEXTJOIN(",", TRUE, A2:A5 &, "|" &, TRANSPOSE(C2:C5)), "|", ",")
Объяснение этой формулы:
- A2:A5 & «|» & ТРАНСП(C2:C5): Создаёт пары каждого значения из столбца A со всеми значениями из столбца C.
- ТЕКСТОБЪЕД(",", ИСТИНА, …): Объединяет все пары в одну длинную текстовую строку с разделителями-запятыми.
- ТЕКСТРАЗБ(…, «|», ","): Разделяет текст обратно на таблицу из двух столбцов.
Коротко говоря, формула создаёт пары вида A|C, объединяет их в одну текстовую строку, а затем разделяет обратно на структурированную таблицу из двух столбцов — тем самым формируя все возможные комбинации.
Советы:
Помимо уже приведённой формулы, вы можете воспользоваться следующей — она даст тот же результат.
=LET(a,A2:A5,b,C2:C5,
MAKEARRAY(ROWS(a)*ROWS(b),2,
LAMBDA(r,c,
IF(c=1, INDEX(a, 1+INT((r-1)/ROWS(b))), INDEX(b, 1+MOD(r-1, ROWS(b))))
)
))
Преимущества
- Полностью динамичный
- Не требует вспомогательных столбцов
- Автоматически обновляется при изменении исходных списков
Недостатки
- Требуется Excel 365
- Формула выглядит сложно для начинающих
✨ Список всех комбинаций — Создайте все возможные комбинации одним щелчком!
Устали писать сложные формулы, чтобы получить все возможные комбинации? С помощью Kutools для Excel вы можете мгновенно получить все комбинации из нескольких столбцов или значений — без формул, без Power Query, всего несколькими щелчками!
Метод 2: перекрёстное соединение с помощью Power Query
Power Query предлагает простой и интуитивно понятный подход без написания кода, который поддерживается во всех современных версиях Excel. С его помощью вы легко создадите перекрёстное соединение всего несколькими щелчками — просто выбирайте нужные действия в интерфейсе, без необходимости писать формулы. Следуйте пошаговым инструкциям ниже.
Шаг 1: создайте таблицы для данных каждого столбца
1.Выделите первый список данных, нажмите Вставка > Таблица. В диалоговом окне Создание таблицы нажмите ОК. Вы получите первую таблицу.
2.На вкладке Конструктор таблиц присвойте таблице понятное имя — так с ней будет проще работать в дальнейшем.
3. Повторите те же действия, чтобы преобразовать данные другого столбца в таблицу и задать ей имя.
Шаг 2: импортируйте таблицы и загрузите их как подключения
1.Выделите первую таблицу и нажмите Данные > Из таблицы/диапазона. См. снимок экрана:
2. В открывшемся окне Power Query Editor нажмите Закрыть и загрузить > Закрыть и загрузить на вкладке Главная.
3. Появится диалоговое окно Импорт данных. Выберите параметр Только создать подключение и нажмите ОК.
4.На правой панели откроется область Запросы и подключения, в которой будет указано, что один запрос добавлен только как подключение.
5.Повторите те же действия, чтобы загрузить вторую таблицу как запрос только для подключения, и вы увидите её рядом с первой в области Запросы и подключения.
Шаг 3: создайте эталонный запрос и пользовательский столбец
1.Щёлкните правой кнопкой мыши запрос, который вы хотите использовать в качестве первой таблицы в перекрёстном соединении, и выберите Ссылка.
2. В окне Power Query Editor перейдите на вкладку Добавить столбец и нажмите Пользовательский столбец, см. снимок экрана:
3. В диалоговом окне Пользовательский столбец в поле Формула пользовательского столбца введите имя другой таблицы, которую вы хотите использовать для перекрёстного соединения. Затем нажмите кнопку ОК.
Примечание:
Если имя запроса содержит пробелы (например, «Цвет товара»), его необходимо заключить в синтаксис #«Имя запроса» при вводе в поле формулы пользовательского столбца. Например, для «Цвет товара» следует ввести #«Цвет товара».
4. Появится новый пользовательский столбец — нажмите кнопку Развернуть, чтобы отобразить его содержимое.
5. В раскрывающейся панели убедитесь, что выбран нужный вам столбец, затем нажмите ОК.
6.Теперь вы увидите все комбинации, сгенерированные из двух таблиц.
Шаг 4: загрузите данные на лист
Перейдите на вкладку Главная, щёлкните Закрыть и загрузить > Закрыть и загрузить. Таблица со всеми комбинациями будет загружена на новый лист.
→
Преимущества
- Работа с большими данными: превосходная производительность даже при обработке тысяч строк.
- Многократное использование и обновление: добавьте новые данные в исходный диапазон — и после обновления запроса результаты автоматически обновятся.
Недостатки
- Немного больше шагов
- Требуются базовые знания Power Query
Метод 3: перекрёстное соединение с помощью Сводная таблица
Этот метод — косвенный, но может удивительно эффективно решить задачу генерации всех комбинаций без написания формул. Он особенно полезен для пользователей, которые предпочитают визуальный, кликабельный подход, а также для тех, кто работает в версиях Excel без поддержки динамических массивных формул.
1.Создайте две отдельные таблицы для списков данных и присвойте им имена, выполнив те же действия, что описаны в шаге 1 метода 2.
2.Выделите таблицу, которую хотите использовать в качестве первого столбца. Затем перейдите на вкладку Вставка и нажмите Сводная таблица. См. снимок экрана:
3. В диалоговом окне Сводная таблица из таблицы или диапазона выберите Существующий лист, укажите расположение сводной таблицы, установите флажок Добавить эти данные в модель данных и нажмите ОК.
4.Когда справа появится область Поля сводной таблицы, установите флажок напротив имени столбца из таблицы — и он автоматически добавится в область Строки.
5.Затем перейдите на вкладку Все в области Поля сводной таблицы, выберите таблицу, которую хотите использовать как второй столбец для Crossjoin, и установите флажок рядом с её именем столбца, чтобы добавить его в область Строки. Вы получите сводную таблицу, как показано на снимке экрана ниже:
6. Щёлкните любую ячейку сводной таблицы, перейдите на вкладку Конструктор, выберите Макет отчёта > Показывать в табличной форме — и ваша сводная таблица примет табличный вид. См. снимок экрана:
7. Затем нажмите Макет отчёта > Повторять все подписи элементов, чтобы отображать все элементы в каждой строке.
8. Наконец, щёлкните Итоги > Откл. для строк и столбцов.
Теперь сводная таблица демонстрирует чёткий список всех комбинаций — без итоговых строк и столбцов.
Преимущества
- Не требует формул или Power Query
- Очень простой и наглядный
- Подходит для быстрого анализа
Недостатки
- Не динамичен
- Требует ручных действий
- Результат не связан с исходными данными
Метод 4: перекрёстное соединение с помощью пользовательской функции (Excel 365 / Excel 2021 и новее)
Если вам часто нужно генерировать все возможные комбинации из двух списков, пользовательская функция (UDF) станет чистым, многократно используемым и исключительно гибким решением. В Excel 365 или Excel 2021 динамический вывод автоматически заполняет результаты, делая их полностью интерактивными и обеспечивая мощный, удобный способ автоматизации операций перекрёстного соединения.
1. Нажмите Alt + F11, чтобы открыть редактор VBA.
2.Затем щёлкните Вставка > Модуль и скопируйте приведённый ниже код в пустой модуль.
Function CrossJoin(list1 As Range, list2 As Range)
'Updateby Extendoffice
Dim arr1, arr2, result()
Dim i As Long, j As Long, r As Long
arr1 = list1.Value
arr2 = list2.Value
ReDim result(1 To UBound(arr1, 1) * UBound(arr2, 1), 1 To 2)
r = 1
For i = 1 To UBound(arr1, 1)
For j = 1 To UBound(arr2, 1)
result(r, 1) = arr1(i, 1)
result(r, 2) = arr2(j, 1)
r = r + 1
Next j
Next i
CrossJoin = result
End Function
3. Вернитесь на лист Excel, введите приведённую ниже формулу и нажмите клавишу Enter — Excel автоматически выведет все комбинации.
=CrossJoin(A2:A5, C2:C5)
Заключение
В заключение, выполнение перекрёстного соединения в Excel предлагает множество гибких и эффективных решений, позволяя выбрать наиболее подходящий метод в зависимости от ваших конкретных задач и рабочей среды:
- Формула использует динамические массивные формулы в Excel 365, чтобы мгновенно получать результаты без программирования — идеальное решение для тех, кто предпочитает стандартные формулы.
- Power Query предлагает простой и многократно используемый процесс для обработки больших наборов данных, что делает его идеальным решением для очистки данных и автоматизированной отчётности.
- Метод Сводная таблица может показаться менее прямолинейным, но на деле оказывается чрезвычайно эффективным и интуитивно понятным для знакомой аудитории.
- Пользовательская функция VBA обеспечивает максимальную гибкость настройки и идеально подходит для сценариев, требующих интеграции в сложный макрокод.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек