Как создать список или сгенерировать все возможные комбинации в Excel?

При работе с данными в Excel вы можете столкнуться с ситуациями, когда необходимо создать все возможные комбинации из нескольких списков. Например, если у вас есть два или более столбца значений, ручное перечисление каждой комбинации может быть утомительным и подверженным ошибкам, особенно по мере увеличения количества значений. В этом руководстве мы рассмотрим различные методы для быстрого создания списка или генерации всех возможных комбинаций в Excel.

Создание списка или генерация всех возможных комбинаций из двух списков с помощью формулы

Создание списка или генерация всех возможных комбинаций из трех или более списков с использованием кода VBA

Создание списка или генерация всех возможных комбинаций из нескольких списков с помощью Kutools для Excel

Создание списка или генерация всех возможных комбинаций из двух списков с помощью формулы

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

1. Введите или скопируйте приведенную ниже формулу в пустую ячейку, в данном случае я введу её в ячейку D2, а затем нажмите клавишу "Enter", чтобы получить результат, см. скриншот:

=IF(ROW()-ROW($D$2)+1>COUNTA($A$2:$A$5)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$5,INT((ROW()-ROW($D$2))/COUNTA($B$2:$B$4)+1))&"-"&INDEX($B$2:$B$4,MOD(ROW()-ROW($D$2),COUNTA($B$2:$B$4))+1))

2. Затем выберите ячейку D2 и перетащите маркер заполнения вниз, пока не появятся пустые ячейки. Все возможные комбинации будут перечислены на основе двух списков значений. См. скриншот:

Создание списка или генерация всех возможных комбинаций из трех или более списков с использованием кода VBA

Если применение приведенной выше формулы кажется сложным, особенно при работе с несколькими столбцами данных, её модификация может оказаться затруднительной. В таких случаях использование кода VBA предоставляет более быстрое и гибкое решение.

1. Удерживайте клавиши "ALT + F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно "Модуль".

Код VBA: Генерация всех комбинаций из 3 или нескольких столбцов

Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg  As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A5")  'First column data
Set xDRg2 = Range("B2:B4")  'Second column data
Set xDRg3 = Range("C2:C4")  'Third column data
xStr = "-"   'Separator
Set xRg = Range("E2")  'Output cell
For xFN1 = 1 To xDRg1.Count
    xSV1 = xDRg1.Item(xFN1).Text
    For xFN2 = 1 To xDRg2.Count
        xSV2 = xDRg2.Item(xFN2).Text
      For xFN3 = 1 To xDRg3.Count
        xSV3 = xDRg3.Item(xFN3).Text
        xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
        Set xRg = xRg.Offset(1, 0)
       Next
    Next
Next
End Sub

3. Затем нажмите клавишу "F5", чтобы запустить этот код, и все комбинации из 3 столбцов будут сгенерированы сразу же, см. скриншот:

Создание списка или генерация всех возможных комбинаций из нескольких списков с помощью Kutools для Excel

Если существует несколько списков значений, для которых нужно перечислить все возможные комбинации, возможно, вам будет сложно модифицировать код. Здесь я могу порекомендовать мощный инструмент — "Kutools для Excel", который содержит удобную функцию "Список всех комбинаций", позволяющую быстро составить список всех возможных комбинаций на основе заданных списков данных.

1. Нажмите "Kutools" > "Вставить" > "Список всех комбинаций", см. скриншот:

2. В диалоговом окне "Список всех комбинаций" выполните операции, как показано в демонстрации ниже:

3. Затем все указанные значения и разделители будут перечислены в диалоговом окне, см. скриншот:

4. После этого нажмите кнопку "ОК", и появится всплывающее окно, которое напомнит вам выбрать ячейку для вывода результата, см. скриншот:

5. Нажмите "OK", и все возможные комбинации на основе заданных списков будут сгенерированы на листе, как показано на следующем скриншоте:

Больше связанных статей: