Note: The other languages of the website are Google-translated. Back to English

Как сгенерировать все комбинации из 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% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (15)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Чтобы сгенерировать любую случайную комбинацию, мы можем использовать
=INDEX($A$2:$A$4,RANDBETWEEN(1,3))&"-"&INDEX($B$2:$B$6,RANDBETWEEN(1,5))&"-"&INDEX($C$2:$C$5,RANDBETWEEN(1,4))
Этот комментарий был сведен к минимуму модератором на сайте
Большое спасибо за то, что поделились этим постом. Большое спасибо @Balaji за его/ее формулу
Этот комментарий был сведен к минимуму модератором на сайте
Я НЕ МОГУ ТЕБЯ ОТБЛАГОДАРИТЬ! СЭКОНОМИЛ МЕНЯ ОЧЕНЬ МНОГО ВРЕМЕНИ!
Этот комментарий был сведен к минимуму модератором на сайте
КАК сделать ниже формулу для 5 столбцов? Пытаюсь разобраться, но выдает ошибку
=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),"")
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Нихил. Для получения всех комбинаций из 5 столбцов может помочь приведенный ниже код VBA, пожалуйста, измените ссылки на ячейки на свои данные.
ПодсписокВсеКомбинации()
'Обновить Extendoffice
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 Как диапазон
Dim xRg как диапазон
Dim xStr как строка
Dim xFN1, xFN2, xFN3, xFN4, xFN5 как целое число
Dim xSV1, xSV2, xSV3, xSV4, xSV5 как строка
Set xDRg1 = Range("A2:A7") 'Данные первого столбца
Установить xDRg2 = Range("B2:B7") 'Данные второго столбца
Установить xDRg3 = Range("C2:C7") 'Данные третьего столбца
Set xDRg4 = Range("D2:D7") 'Данные четвертого столбца
Set xDRg5 = Range("E2:E7") 'Данные пятого столбца
xStr = "-" 'Разделитель
Установить xRg = Range("H2") 'Выходная ячейка
Для xFN1 = 1 To xDRg1.Count
xSV1 = xDRg1.Элемент(xFN1).Текст
Для xFN2 = 1 To xDRg2.Count
xSV2 = xDRg2.Элемент(xFN2).Текст
Для xFN3 = 1 To xDRg3.Count
xSV3 = xDRg3.Элемент(xFN3).Текст
Для xFN4 = 1 To xDRg4.Count
xSV4 = xDRg4.Элемент(xFN4).Текст
Для xFN5 = 1 To xDRg5.Count
xSV5 = xDRg5.Элемент(xFN5).Текст
xRg.Value = xSV1 и xStr и xSV2 и xStr и xSV3 и xStr и xSV4 и xStr и xSV5
Установите xRg = xRg.Offset (1, 0)
Далее
Далее
Далее
Далее
Далее
End SubПожалуйста, попробуйте, надеюсь, это поможет вам!
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Skyyang, я изменил код для 7 рулонов, но, поскольку в Excel всего 1,048,576 1 0 строк, код VBA не может дать все комбинации. У вас есть идеи, как я могу продолжить работу с другими столбцами? Я думаю, что этот код - Set xRg = xRg.Offset(XNUMX, XNUMX) необходимо изменить
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Skyyang, я изменил код для 7 рулонов, но, поскольку в Excel всего 1,048,576 XNUMX XNUMX строк, код VBA не может дать все комбинации.
У вас есть идея, как я могу продолжить работу с другими столбцами?
Я думаю, что этот код - Установить xRg = xRg.Offset (1, 0)
необходимо изменить 
Этот комментарий был сведен к минимуму модератором на сайте
=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),"")
Этот комментарий был сведен к минимуму модератором на сайте
мне нужна эта формула для 4 столбцов
Этот комментарий был сведен к минимуму модератором на сайте
Большое спасибо. То, что мне нужно :-)))
Этот комментарий был сведен к минимуму модератором на сайте
Большое спасибо за этот код. Я изменил код для количества столбцов, которые мне нужны (25). Спасибо,
Этот комментарий был сведен к минимуму модератором на сайте
В коде HiIn VBA я использовал четыре столбца, а диапазон столбцов: E2: E75, B2: B267, C2: C195 и D2: D267. Выходной диапазон J2. В этом случае выходной результат превышал лимит строк. Пожалуйста, помогите решить ошибку
Этот комментарий был сведен к минимуму модератором на сайте
Привет Итак, это код для 9 столбцов :')
ПодсписокВсеКомбинации()
'Обновить Extendoffice
Размер xDRg1, xDRg2, xDRg3, xDRg4, xDRg5, xDRg6, xDRg7, xDRg8, xDRg9 Как диапазон
Dim xRg как диапазон
Dim xStr как строка
Размер xFN1, xFN2, xFN3, xFN4, xFN5, xFN6, xFN7, xFN8, xFN9 как целое число
Dim xSV1, xSV2, xSV3, xSV4, xSV5, xSV6, xSV7, xSV8, xSV9 как строка
Set xDRg1 = Range("A2:A3") 'Данные первого столбца
Установить xDRg2 = Range("B2:B3") 'Данные второго столбца
Установите xDRg3 = Range("C2:C10") 'Данные третьего столбца
Set xDRg4 = Range("D2:D2") 'Данные третьего столбца
Set xDRg5 = Range("E2:E3") 'Данные третьего столбца
Set xDRg6 = Range("F2:F3") 'Данные третьего столбца
Установите xDRg7 = Range("G2:G4") 'Данные третьего столбца
Установите xDRg8 = Range("H2:H3") 'Данные третьего столбца
Set xDRg9 = Range("I2:I3") 'Данные третьего столбца
xStr = "-" 'Разделитель
Установить xRg = Range("K2") 'Выходная ячейка
Для xFN1 = 1 To xDRg1.Count
xSV1 = xDRg1.Элемент(xFN1).Текст
Для xFN2 = 1 To xDRg2.Count
xSV2 = xDRg2.Элемент(xFN2).Текст
Для xFN3 = 1 To xDRg3.Count
xSV3 = xDRg3.Элемент(xFN3).Текст
Для xFN4 = 1 To xDRg4.Count
xSV4 = xDRg4.Элемент(xFN4).Текст
Для xFN5 = 1 To xDRg5.Count
xSV5 = xDRg5.Элемент(xFN5).Текст
Для xFN6 = 1 To xDRg6.Count
xSV6 = xDRg6.Элемент(xFN6).Текст
Для xFN7 = 1 To xDRg7.Count
xSV7 = xDRg7.Элемент(xFN7).Текст
Для xFN8 = 1 To xDRg8.Count
xSV8 = xDRg8.Элемент(xFN8).Текст
Для xFN9 = 1 To xDRg9.Count
xSV9 = xDRg9.Элемент(xFN9).Текст
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9
Установите xRg = xRg.Offset (1, 0)
Далее
Далее
Далее
Далее
Далее
Далее
Далее
Далее
Далее
End Sub
Этот комментарий был сведен к минимуму модератором на сайте
조합의 나열을 "단어-단어-단어" 순으로 나열하는 법은 이해했습니다.
그런데 "단어"가 아니라 숫자일 경우,
즉 숫자의 조합을 단순 나열이 아닌 덧셈이나 곱셈으로 적용하려면 어떻게 앴야하는지 알 ꔘ 알 숝

'VBA 코드: 3 개 또는 여러 열의 모든 조합 생성' 에서 말이죠.

"1-1-1" 엑셀에 결과 값이 표기되는 것이 아니고

-1 로 엑셀에 표기될 수 있게 말입니다.
Этот комментарий был сведен к минимуму модератором на сайте
Добрый день, прокомментируйте Faire pour Que chaque valeurs soient placées dans une Colone Differente en non séparées par un tiret ?
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места