Перейти до основного матеріалу

Як перерахувати або створити всі можливі комбінації в Excel?

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

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

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

Список або генерувати всі можливі комбінації з декількох списків з потужною функцією


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

Наступна довга формула може допомогти вам швидко перерахувати всі можливі комбінації значень двох списків, будь-ласка, зробіть наступне:

1. Введіть або скопіюйте наведену нижче формулу в порожню клітинку, у цьому випадку я введу її в клітинку D2, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат, див. знімок екрана:

=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 - діапазон значень першого стовпця, і $ B $ 2: $ B $ 4 - діапазон значень другого списку, в якому ви хочете перерахувати всі їх можливі комбінації, $ D $ 2 це клітинка, в яку ви вкладаєте формулу, ви можете змінити посилання на клітинку відповідно до ваших потреб.

2. Потім виділіть комірку D2 і перетягніть маркер заповнення вниз до комірок, поки не отримаєте порожні комірки, і всі можливі комбінації будуть перераховані на основі двох значень списків. Дивіться знімок екрана:


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

Можливо, вищезазначену формулу вам дещо важко застосувати, якщо є кілька даних стовпців, це складно змінити. Тут я представив код VBA, щоб швидко з ним впоратися.

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у Модулі Вікно

Код 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 для Excel, він містить зручну функцію Перелічіть усі комбінації який може швидко перерахувати всі можливі комбінації на основі заданих списків даних.

Tips :Щоб застосувати це Перелічіть усі комбінації По-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Натисніть Кутулс > Insert > Перелічіть усі комбінації, див. скріншот:

2, в Перелічіть усі комбінації у діалоговому вікні виконайте операції, як показано нижче:

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

4А потім натисніть Ok , і з'явиться підказка, щоб нагадати вам вибрати клітинку для виведення результату, див. знімок екрана:

5. Клацання OK, всі можливі комбінації на основі поданих списків були сформовані на робочому аркуші, як показано на наступному знімку екрана:

Натисніть, щоб завантажити Kutools для 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 AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!

 

Comments (42)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
como que poderia gerar uma lista de combinações de nomes no LibreOffice?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hello, Johansson

Sorry, the methods in this article are only applied for Microsoft Excel.
Thank you!
This comment was minimized by the moderator on the site
Hej alle sammen

Jeg håber at i kan hjælpe med dette.

Jeg har en af de gamle Bastalåse kodelåse med 6 knapper, hvor man kan trykke knapperne ind, lade dem være i neutral eller trække dem ud.

Låsen var åben da jeg købte den, så jeg låste den i håb om at jeg kunne gå kombinationerne igennem.

Men jeg har lidt problemer med at få nedskrevet samtlige kombinationer (729 så vidt jeg kan regne ud).

Er der en der vil hjælpe mig med med dette? Evt. kan man kalde de 3 indstillingsmuligheder pr. knap for: 1-2-3, 1-0-2, eller I-N-U (ind-neutral-ud).


Krydser finger og siger på forhånd mange tak hvis jeg kan få låsen op, det er sådan lidt nostalgi.

Mvh

Nicolaj
This comment was minimized by the moderator on the site
No 1º Exemplo de captura de tela , está faltando em All combinations Jan - KTE
This comment was minimized by the moderator on the site
Hello friend,
How are you. Thank you for your notice. We changed the first ficture. Please check now. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Hi,
I have a project with excel (including Kutools) that I am stuck on.
Can anyone tell me how to get a list of all combinations of 6 different numbers, without repeating any of the numbers?
I know there should be 6! = 720 combinations but I need the list.
Thanks
This comment was minimized by the moderator on the site
Hi,
I have a project with excel (including Kutools) that I am stuck on.
Can anyone tell me how to get a list of all combinations of 6 different numbers, without repeating any digit?
I know there should be 6! = 720 combinations but I need the list.
Thanks
This comment was minimized by the moderator on the site
Hello Extend Office,
I want to extend the VBA code in "List Or Generate All Possible Combinations From Three Or More Lists With VBA Code article"
I want to show only the one's whose total of the combinations is equal to 9. Can you please show the additional extension/lines in the code? sorry, I'm barely new in VBA.
For example, I only want to show the combinations like the following:
1-2-2-2-2 (this is equal to 1+2+2+2+2 = 9) so, it will show the ones whose total is 9, the rest won't show.
1-2-3-1-3 (=9)
1-3-2-1-2 (=9) and so on...

Thanks very much for your help
Dalesimplest.becky15.08.21

This comment was minimized by the moderator on the site
edited formula to randomize 5 columns instead of 3 but still wont work,why?
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, xF5 As Integer
Dim xSV1, xSV2, xSV3, xSV4, xSV5 As String
Set xDRg1 = Range("A2:A10") 'First column data
Set xDRg2 = Range("B2:B10") 'Second column data
Set xDRg3 = Range("C2:C10") 'Third column data
Set xDRg2 = Range("D2:D10") 'Fourth column data
Set xDRg3 = Range("E2:E10") 'Fifth column data
xStr = "," 'Separator
Set xRg = Range("G2") '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
End Sub
This comment was minimized by the moderator on the site
Hello, jericho,You should modify your code as below code, please try it, thank you!<div data-tag="code">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:A10") 'First column data
Set xDRg2 = Range("B2:B10") 'Second column data
Set xDRg3 = Range("C2:C10") 'Third column data
Set xDRg4 = Range("D2:D10") 'Fourth column data
Set xDRg5 = Range("E2:E10") '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
This comment was minimized by the moderator on the site
How to remove the repetition if I have color 1, color 2, color 3 in different orders(i.e. color2, color 3, color 1) but they are the same for my need? tks!
This comment was minimized by the moderator on the site
Excelentes soluciones, muchas gracias, lo logré con Kutools
This comment was minimized by the moderator on the site
NON FUNZIONA. HO SEGUITO GLI STESSI PASSAGGI MA MI DICE CHE C'E' UN ERRORE
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations