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

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

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

Перечислите или сгенерируйте все возможные комбинации из двух списков с формулой

Перечислите или сгенерируйте все возможные комбинации из трех или более списков с кодом VBA

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


Перечислите или сгенерируйте все возможные комбинации из двух списков с формулой

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

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))

Внимание: В приведенной выше формуле $ A $ 2: $ A $ 5 - это диапазон значений первого столбца, а 2 млрд долларов: 4 млрд долларов - это диапазон значений второго списка, в котором вы хотите перечислить все их возможные комбинации, $ D $ 2 это ячейка, в которую вы поместили формулу, вы можете изменить ссылки на ячейки по своему усмотрению.

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


Перечислите или сгенерируйте все возможные комбинации из трех или более списков с кодом 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: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
Внимание: В приведенном выше коде A2: A5, B2: B4, C2: C4 диапазон данных, который вы хотите использовать, E2 - это выходная ячейка, в которой вы хотите найти результаты. Если вы хотите получить все комбинации из большего количества столбцов, измените и добавьте другие параметры в код по мере необходимости.

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


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

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

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

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

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

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

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

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

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

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


Больше относительных статей:

  • Создать все комбинации из 3 или нескольких столбцов
  • Предположим, у меня есть 3 столбца данных, теперь я хочу сгенерировать или перечислить все комбинации данных в этих 3 столбцах, как показано ниже. Есть ли у вас какие-нибудь хорошие методы решения этой задачи в Excel?
  • Найдите все комбинации, равные заданной сумме
  • Например, у меня есть следующий список чисел, и теперь я хочу знать, какая комбинация чисел в списке дает в сумме 480, на следующем скриншоте вы можете увидеть, что есть пять групп возможных комбинаций, которые в сумме равны до 480, например, 300 + 60 + 120, 300 + 60 + 40 + 80 и т. д. В этой статье я расскажу о некоторых методах определения суммирования ячеек до определенного значения в Excel.
  • Сгенерировать или перечислить все возможные перестановки
  • Например, у меня есть три символа XYZ, теперь я хочу перечислить все возможные перестановки на основе этих трех символов, чтобы получить шесть разных результатов, например: XYZ, XZY, YXZ, YZX, ZXY и ZYX. Как в Excel быстро сгенерировать или составить список всех перестановок на основе разного количества символов?
  • Составьте список всех возможных комбинаций из 4 цифр
  • В некоторых случаях нам может потребоваться сгенерировать список всех возможных 4-значных комбинаций чисел от 0 до 9, что означает создание списка 0000, 0001, 0002… 9999. Чтобы быстро решить задачу списка в Excel, я предлагаю вам несколько уловок.

 


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно

 

Сортировать комментарии по
Комментарии (42)
Номинальный 5 из 5 · рейтинги 1
Этот комментарий был сведен к минимуму модератором на сайте
Это гениальная формула! Я понятия не имею, как это работает, но я просто меняю столбцы A и B, чтобы они соответствовали длине моих списков, и получаю вывод в D1.
Этот комментарий был сведен к минимуму модератором на сайте
пример: мой список значений 1,2,3,4,5,6......80, я хочу, чтобы kutool отображал все комбинации набора из 2 чисел, пример: 1-2, 1-3, 1-4, 1-5, ...................79-80. возможно ли это с KUTOL?
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ заставить эту формулу работать, когда она создает различные комбинации, все еще разделенные на 2 отдельных столбца, но смежные друг с другом?
Этот комментарий был сведен к минимуму модератором на сайте
Удалось ли вам выяснить, как это сделать? Я пытаюсь сделать то же самое и разместить все свои вероятности в двух отдельных столбцах в Excel.
Этот комментарий был сведен к минимуму модератором на сайте
Вам еще не повезло с этим? Я пытаюсь сделать то же самое и разместить все возможные комбинации рядом друг с другом в двух отдельных столбцах.
Этот комментарий был сведен к минимуму модератором на сайте
Могу ли я получить формулу для всех комбинаций матрицы 5 * 5 (5 строк и 5 столбцов), которые я пытался, но не смог получить, пожалуйста, помогите мне .......
Этот комментарий был сведен к минимуму модератором на сайте
ОЧЕНЬ ПОЛЕЗНО. Теперь я могу очень легко создавать комбинации.
Этот комментарий был сведен к минимуму модератором на сайте
Как вы могли использовать KuTools или даже формулу, если вам нужны все перестановки заголовка месяца. Январь, январь и февраль, январь и март, январь и март, январь и февраль и март и т. д. и т. д.
Этот комментарий был сведен к минимуму модератором на сайте
кто-нибудь знает, как изменить это, чтобы отразить 6 столбцов данных, а не только два?
Этот комментарий был сведен к минимуму модератором на сайте
ищет то же самое. Кто-нибудь знает?
Этот комментарий был сведен к минимуму модератором на сайте
кто-нибудь знает, как изменить это, чтобы отразить 6 столбцов данных?
Этот комментарий был сведен к минимуму модератором на сайте
Для большего количества столбцов:
Первую часть формулы необходимо изменить, чтобы умножить все возможности, например, это будет для 6 столбцов.
COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9)
так ты добавляешь
*COUNTA(ВашДиапазонЗдесь)
для каждого столбца
Вторую часть формулы также необходимо изменить для каждого столбца следующим образом:
INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1)
так ты добавляешь
&INDEX(YourRangeHere,MOD(ROW()-ROW($I$2),COUNTA(YourRangeHere))+1)

Соедините их все вместе, и вы получите этот пример для 6 столбцов:

=IF(ROW()-ROW($I$2)+1>COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9),"",INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1))

Где бы вы вставили эту формулу в $I$2, и она будет смотреть на $A$2:$A$9 $B$2:$B$9 $C$2:$C$9 ... до $F$2:$F$9
Этот комментарий был сведен к минимуму модератором на сайте
это не работает :( сложнее, чем это
Этот комментарий был сведен к минимуму модератором на сайте
Для большего количества столбцов:
Первую часть формулы необходимо изменить, чтобы умножить все возможности, например, для 6 столбцов:

COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9)
так ты добавляешь
*COUNTA(ВашДиапазонЗдесь)
для каждого столбца

Вторую часть формулы также необходимо изменить для каждого столбца следующим образом:

INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1)

так ты добавляешь

&INDEX(YourRangeHere,MOD(ROW()-ROW($I$2),COUNTA(YourRangeHere))+1)

Соедините их все вместе, и вы получите этот пример для 6 столбцов:

=IF(ROW()-ROW($I$2)+1>COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9),"",INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1))

Где бы вы вставили эту формулу в $I$2, и она будет смотреть на $A$2:$A$9 $B$2:$B$9 $C$2:$C$9 ... до $F$2:$F$9
Этот комментарий был сведен к минимуму модератором на сайте
это не работает :( сложнее, чем это
Этот комментарий был сведен к минимуму модератором на сайте
Любая идея, как добавить пробел между ними?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Эоин,
формула в этой статье не может помочь вам добавить пробел между значениями ячеек, но вы можете применить Kutools for Excel, с ним вы можете ввести любой разделитель, если хотите разделить объединенный результат, см. снимок экрана:
Этот комментарий был сведен к минимуму модератором на сайте
Вы можете добавить пробел, добавив " "& перед индексированием столбца B, сразу после существующего &, например...

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


....или любой другой разделитель, который вам нравится!
Этот комментарий был сведен к минимуму модератором на сайте
Может ли KuTools сортировать ячейки подряд «простой комбинацией»?
Я имею в виду, если у меня есть эти данные:
_________________________
Джон Джек Пол Мэйси

Марк Ларри

Джерри Пол Мэри

Сэм Джефф Питер Лукас
_________________________



И я хочу вывести их так:
___________
Джон Джек

Джон Пол

Джон Мэйси

Джек Пол

Джек Мэйси

Пол Мэйси

Марк Ларри

Джерри Пол

Джерри жениться

Пол Мэри

Сэм Джефф

Сэм Питер

Сэм Лукас

Джефф Питер

Джефф Лукас

Питер Лукас
____________


Как я могу это сделать? Может ли KuTools сделать что-то подобное?

Благодаря!
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Артур,
Извините, Kutools не может помочь вам решить эту задачу, как вы сказали.
Спасибо за ваш комментарий.
Этот комментарий был сведен к минимуму модератором на сайте
Кто-нибудь придумал формулу, чтобы сделать это для 5 наборов данных? Я был в тупике на этом до сих пор.
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Марко,
Kutools может помочь вам быстро решить вашу проблему, пожалуйста, проверьте следующий снимок экрана:
Пожалуйста, попробуйте, надеюсь, это поможет вам, спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
Я хочу знать, что делать, если я хочу сгенерировать более 1,4 миллиона комбинаций и превысить лимит строк Excel?
Есть ли способ поставить Kudos, чтобы продолжить итерацию в следующем столбце?
Этот комментарий был сведен к минимуму модератором на сайте
Почему моя формула не сработала для создания 25 комбинаций в столбце E (5 элементов из столбца A * 5 элементов из столбца B)? Тогда как формула в этой статье работала в столбце D, когда я перетащил ее на 25 ячеек? Снимок экрана - https://prnt.sc/ihwr18
Этот комментарий был сведен к минимуму модератором на сайте
Hola me arroja una referencia circula la формула
=SI(FILA()-FILA($D$1)+1>(CONTARA($A$1:$A$4)*CONTARA($B$1:$B$3));"";INDICE($A$1:$A$4;RESIDUO((FILA()-FILA($D$1));COUNTA($B$1:$B$3)+1))
&INDICE($B$1:$B$3;RESIDUO(FILA()-FILA($D$1);CONTARA($B$1:$B$3))+1))
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, а если эти комбинации вместо текста являются числами, как я могу рассчитать результаты???? Я пытаюсь добавить «+», но Excel не знает, что это символ .. как вы это решаете?
Этот комментарий был сведен к минимуму модератором на сайте
Я использовал extions и работает фантастически, но теперь у меня проблема, мне нужно вычислить сумму, но результатом является текст, я ввожу разделитель, который является символом +, но, наконец, у меня есть текст и невозможно преобразовать в операцию.
Этот комментарий был сведен к минимуму модератором на сайте
Почти очень удобно. Было бы хорошо, если бы он действительно использовал все, что находится в ячейке ($ A $ 1), а не преобразовывал ячейку в текст. Затем я мог бы просто вставить то, что мне нужно, в A1 и запустить его снова, ничего не меняя.
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ вывести это в виде txt файла? Когда у вас есть миллионы результатов в столбце D, не совсем практично перетаскивать маркер заполнения.
Здесь еще нет комментариев
Загрузить ещё
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL