Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

Как сгенерировать все комбинации из 3 или нескольких столбцов в Excel?

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

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

Создавайте все комбинации на основе 3 или нескольких столбцов данных с помощью кода VBA

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

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

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

1. Пожалуйста, щелкните ячейку, в которой нужно вывести результат, а затем скопируйте и вставьте в нее следующую формулу:

=IFERROR(INDEX($A$2:$A$4,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$6)*(COUNTA($C$2:$C$5)))))+1)&"-"&INDEX($B$2:$B$6,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$5)),COUNTA($B$2:$B$6))+1)&"-"&INDEX($C$2:$C$5,MOD((ROW(1:1)-1),COUNTA($C$2:$C$5))+1),"")

Внимание: В этой формуле: A2: A4, B2: B6, C2: C5 - это диапазоны данных, которые вы хотите использовать.

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

Создавайте все комбинации на основе 3 или нескольких столбцов данных с помощью кода VBA

Приведенную выше длинную формулу несколько сложно использовать, если необходимо использовать данные из нескольких столбцов, ее будет сложно изменить. Здесь я представлю код VBA, чтобы быстро с этим справиться.

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

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:A4")  'First column data
Set xDRg2 = Range("B2:B6")  'Second column data
Set xDRg3 = Range("C2:C5")  '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

Внимание: В приведенном выше коде A2: A4, B2: B6, C2: C5 диапазон данных, который вы хотите использовать, E2 - это выходная ячейка, в которой вы хотите найти результаты. Если вы хотите получить все комбинации из большего количества столбцов, измените и добавьте другие параметры в код по мере необходимости.

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

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

Если у вас есть Kutools for Excel, с его мощным Список всех комбинаций вы можете быстро и легко перечислить все комбинации нескольких столбцов.

Советы:Чтобы применить это Список всех комбинаций функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Нажмите Кутулс > Вставить > Список всех комбинаций, см. снимок экрана:

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

3. После установки данных и разделителя нажмите Ok , в следующем окне запроса выберите ячейку, чтобы найти результат, см. снимок экрана:

4. А затем нажмите OK все комбинации были сгенерированы немедленно, как показано ниже:

Нажмите, чтобы загрузить Kutools for Excel и бесплатную пробную версию сейчас!

  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...
вкладка kte 201905
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    sarah · 3 months ago
    Hello So this is a code for 9 columns :') 

    Sub ListAllCombinations()
    'Updateby Extendoffice
    Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5, xDRg6, xDRg7, xDRg8, xDRg9 As Range
    Dim xRg As Range
    Dim xStr As String
    Dim xFN1, xFN2, xFN3, xFN4, xFN5, xFN6, xFN7, xFN8, xFN9 As Integer
    Dim xSV1, xSV2, xSV3, xSV4, xSV5, xSV6, xSV7, xSV8, xSV9 As String
    Set xDRg1 = Range("A2:A3") 'First column data
    Set xDRg2 = Range("B2:B3") 'Second column data
    Set xDRg3 = Range("C2:C10") 'Third column data
    Set xDRg4 = Range("D2:D2") 'Third column data
    Set xDRg5 = Range("E2:E3") 'Third column data
    Set xDRg6 = Range("F2:F3") 'Third column data
    Set xDRg7 = Range("G2:G4") 'Third column data
    Set xDRg8 = Range("H2:H3") 'Third column data
    Set xDRg9 = Range("I2:I3") 'Third column data
    xStr = "-" 'Separator
    Set xRg = Range("K2") '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
    For xFN4 = 1 To xDRg4.Count
    xSV4 = xDRg4.Item(xFN4).Text
    For xFN5 = 1 To xDRg5.Count
    xSV5 = xDRg5.Item(xFN5).Text
    For xFN6 = 1 To xDRg6.Count
    xSV6 = xDRg6.Item(xFN6).Text
    For xFN7 = 1 To xDRg7.Count
    xSV7 = xDRg7.Item(xFN7).Text
    For xFN8 = 1 To xDRg8.Count
    xSV8 = xDRg8.Item(xFN8).Text
    For xFN9 = 1 To xDRg9.Count
    xSV9 = xDRg9.Item(xFN9).Text
    xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9
    Set xRg = xRg.Offset(1, 0)
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    End Sub
  • To post as a guest, your comment is unpublished.
    Jayanta · 6 months ago
    Hi
    In VBA code I used four column and range of the column are E2:E75, B2:B267, C2:C195 & D2:D267. Out put range is J2. In this case out put result was exceed row limit. Please help to solve the error
  • To post as a guest, your comment is unpublished.
    Betty · 11 months ago

    Thank you so much for this code. I have modified the code for the amount of column I need (25).

    Thanks,

  • To post as a guest, your comment is unpublished.
    H · 1 years ago
    Thank you so much. Exactly what I need :-)))
  • To post as a guest, your comment is unpublished.
    Karthik · 1 years ago
    =IFERROR(INDEX($A$2:$A$5,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$3)*(COUNTA($C$2:$C$3)* (COUNTA($D$2:$D$4)* (COUNTA($E$2:$E$6)* (COUNTA($H$2:$H$6)* (COUNTA($G$2:$G$6)* (COUNTA($H$2:$H$6))))))))))+1)&"-"&INDEX($B$2:$B$3,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$3)),COUNTA($B$2:$B$3))+1)&"-" &INDEX($C$2:$C$3,MOD(INT((ROW(1:1)-1)/COUNTA($D$2:$D$4)),COUNTA($C$2:$C$3))+1)&"-" &INDEX($D$2:$D$4,MOD(INT((ROW(1:1)-1)/COUNTA($E$2:$E$6)),COUNTA($D$2:$D$4))+1)&"-" &INDEX($E$2:$E$6,MOD(INT((ROW(1:1)-1)/COUNTA($F$2:$F$6)),COUNTA($E$2:$E$6))+1)&"-" &INDEX($F$2:$F$6,MOD(INT((ROW(1:1)-1)/COUNTA($G$2:$G$6)),COUNTA($F$2:$F$6))+1)&"-" &INDEX($G$2:$G$6,MOD(INT((ROW(1:1)-1)/COUNTA($H$2:$H$6)),COUNTA($G$2:$G$6))+1)&"-"&INDEX($H$2:$H$6,MOD((ROW(1:1)-1),COUNTA($H$2:$H$6))+1),"")
    • To post as a guest, your comment is unpublished.
      jen · 1 years ago
      i need this formula for 4 columns
  • To post as a guest, your comment is unpublished.
    Nikhil · 1 years ago
    HOw to do below formula for 5 columns? Trying to figure out but its giving error

    =IFERROR(INDEX($A$2:$A$4,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$6)*(COUNTA($C$2:$C$5)))))+1)&"-"&INDEX($B$2:$B$6,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$5)),COUNTA($B$2:$B$6))+1)&"-"&INDEX($C$2:$C$5,MOD((ROW(1:1)-1),COUNTA($C$2:$C$5))+1),"")
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Nikhil,
      For getting all combinations from 5 columns, may be the below VBA code can help you, please change the cell references to your data.

      Sub ListAllCombinations() 'Updateby Extendoffice Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 As Range Dim xRg As Range Dim xStr As String Dim xFN1, xFN2, xFN3, xFN4, xFN5 As Integer Dim xSV1, xSV2, xSV3, xSV4, xSV5 As String Set xDRg1 = Range("A2:A7") 'First column data Set xDRg2 = Range("B2:B7") 'Second column data Set xDRg3 = Range("C2:C7") 'Third column data Set xDRg4 = Range("D2:D7") 'Fourth column data Set xDRg5 = Range("E2:E7") 'Fifth column data xStr = "-" 'Separator Set xRg = Range("H2") '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 For xFN4 = 1 To xDRg4.Count xSV4 = xDRg4.Item(xFN4).Text For xFN5 = 1 To xDRg5.Count xSV5 = xDRg5.Item(xFN5).Text xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 Set xRg = xRg.Offset(1, 0) Next Next Next Next Next End Sub
      Please try, hope it can help you!
      • To post as a guest, your comment is unpublished.
        WELLINSGTON RAFAEL DEBORT · 4 days ago
        Hello skyyang, I changed the code for 7 rolls, but seeing Excel has only 1,048,576 rows the VBA code can't give all the combinations.
        Do you have an idea how I can continue on other columns?
        I think this code - Set xRg = xRg.Offset(1, 0)
        needed to change
         
      • To post as a guest, your comment is unpublished.
        Tikikus · 6 months ago
        Hello skyyang, I changed the code for 7 rolls, but seeing Excel has only 1,048,576 rows the VBA code can't give all the combinations.
        Do you have an idea how I can continue on other columns?
         I think this code  -  Set xRg = xRg.Offset(1, 0)
        needed to change 
  • To post as a guest, your comment is unpublished.
    anon · 1 years ago
    I CANNOT THANK YOU ENOUGH! SAVED ME SO MUCH TIME!
  • To post as a guest, your comment is unpublished.
    Tik · 1 years ago
    Thanks a lot for sharing this post. Thanks a lot to @Balaji for his/her formula
  • To post as a guest, your comment is unpublished.
    balaji · 2 years ago
    To generate any random combination we can use
    =INDEX($A$2:$A$4,RANDBETWEEN(1,3))&"-"&INDEX($B$2:$B$6,RANDBETWEEN(1,5))&"-"&INDEX($C$2:$C$5,RANDBETWEEN(1,4))