Hoe alle mogelijke combinaties in Excel te lijsten of genereren?

Bij het werken met gegevens in Excel kun je situaties tegenkomen waarin je alle mogelijke combinaties moet genereren uit meerdere lijsten. Als je bijvoorbeeld twee of meer kolommen met waarden hebt, kan het handmatig opschrijven van elke combinatie tijdrovend en foutgevoelig zijn, vooral naarmate het aantal waarden toeneemt. In deze handleiding onderzoeken we verschillende technieken om snel alle mogelijke combinaties in Excel te lijsten of genereren.

Alle mogelijke combinaties uit twee lijsten met een formule lijsten of genereren

Alle mogelijke combinaties uit drie of meer lijsten met VBA-code lijsten of genereren

Alle mogelijke combinaties uit meerdere lijsten met Kutools voor Excel lijsten of genereren

Alle mogelijke combinaties uit twee lijsten met een formule lijsten of genereren

De volgende lange formule kan helpen om alle mogelijke combinaties van waarden uit twee lijsten snel te lijsten. Volg de volgende stappen:

1. Voer of kopieer de onderstaande formule in een lege cel in, in dit geval zal ik hem invoeren in cel D2, en druk vervolgens op de "Enter"-toets om het resultaat te krijgen, zie screenshot:

=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. Selecteer vervolgens cel D2 en sleep de vulgreep omlaag totdat lege cellen verschijnen. Alle mogelijke combinaties worden gebaseerd op de twee lijsten met waarden. Zie screenshot:

Alle mogelijke combinaties uit drie of meer lijsten met VBA-code lijsten of genereren

Als de bovenstaande formule moeilijk toe te passen voelt, vooral met meerdere kolommen met gegevens, kan het lastig zijn om deze aan te passen. In dergelijke gevallen biedt een VBA-code een snellere en flexibele oplossing.

1. Houd de toetsen "ALT + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module", en plak de volgende code in het "Module"-venster.

VBA-code: Genereer alle combinaties van 3 of meerdere kolommen

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. Druk vervolgens op de toets "F5" om deze code uit te voeren, en alle combinaties van de 3 kolommen worden tegelijk gegenereerd, zie screenshot:

Alle mogelijke combinaties uit meerdere lijsten met Kutools voor Excel lijsten of genereren

Als er meerdere lijsten met waarden zijn waarvan alle mogelijke combinaties moeten worden gelijst, kan het moeilijk zijn om de code aan te passen. Hier kan ik een krachtig hulpmiddel aanbevelen - "Kutools voor Excel", dat een handige functie "Lijst alle combinaties" bevat die snel alle mogelijke combinaties kan lijsten op basis van gegeven datalijsten.

1. Klik op "Kutools" > "Invoegen" > "Lijst alle combinaties", zie screenshot:

2. Voer in het dialoogvenster "Lijst alle combinaties" de bewerkingen uit zoals hieronder aangegeven:

3. Alle gespecificeerde waarden en scheidingstekens zijn nu in het dialoogvenster gelijst, zie screenshot:

4. Klik vervolgens op de knop "OK", en er verschijnt een pop-upvenster om je eraan te herinneren een cel te selecteren om het resultaat uit te voeren, zie screenshot:

5. Klik op "OK", alle mogelijke combinaties op basis van de gegeven lijsten zijn gegenereerd in het werkblad zoals in de volgende screenshot wordt getoond:

Meer gerelateerde artikelen: