Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment générer toutes les combinaisons de 3 ou plusieurs colonnes dans Excel?

En supposant que j'ai 3 colonnes de données, maintenant, je souhaite générer ou répertorier toutes les combinaisons de données dans ces 3 colonnes, comme illustré ci-dessous. Avez-vous de bonnes méthodes pour résoudre cette tâche dans Excel?

Générez toutes les combinaisons basées sur 3 colonnes de données en utilisant une formule

Générer toutes les combinaisons basées sur 3 ou plusieurs colonnes de données à l'aide du code VBA

Générez toutes les combinaisons basées sur 3 ou plusieurs colonnes de données en utilisant une fonctionnalité géniale

Générez toutes les combinaisons basées sur 3 colonnes de données en utilisant une formule

La longue formule suivante peut aider à lister toutes les combinaisons de 3 colonnes, procédez comme suit:

1. Veuillez cliquer sur une cellule où afficher le résultat, puis copiez et collez la formule ci-dessous:

=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),"")

Notes: Dans cette formule: A2: A4, B2: B6, C2: C5 sont les plages de données que vous souhaitez utiliser.

2. Ensuite, faites glisser la poignée de remplissage vers les cellules jusqu'à ce que les cellules vides soient affichées, ce qui signifie que toutes les combinaisons des 3 colonnes ont été répertoriées, voir capture d'écran:

Générer toutes les combinaisons basées sur 3 ou plusieurs colonnes de données à l'aide du code VBA

La formule longue ci-dessus est quelque peu difficile à utiliser, s'il y a plusieurs colonnes que les données doivent utiliser, il sera difficile de la modifier. Ici, je vais introduire un code VBA pour le gérer rapidement.

1. Maintenez le ALT + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. Cliquez insérer > Moduleet collez le code suivant dans la fenêtre Module.

Code VBA: générez toutes les combinaisons de 3 ou plusieurs colonnes

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

Notes: Dans le code ci-dessus, A2: A4, B2: B6, C2: C5 sont la plage de données que vous souhaitez utiliser, E2 est la cellule de sortie dans laquelle vous souhaitez localiser les résultats. Si vous souhaitez obtenir toutes les combinaisons de plusieurs colonnes, veuillez modifier et ajouter d'autres paramètres au code selon vos besoins.

3. Puis appuyez F5 clé pour exécuter ce code, et toutes les combinaisons des 3 ou plusieurs colonnes seront générées en même temps, voir capture d'écran:

Générez toutes les combinaisons basées sur 3 ou plusieurs colonnes de données en utilisant une fonctionnalité géniale

Si vous avez Kutools pour Excel, avec son puissant Lister toutes les combinaisons fonctionnalité, vous pouvez lister toutes les combinaisons de plusieurs colonnes rapidement et facilement.

Conseils:Appliquer cette Lister toutes les combinaisons fonctionnalité, tout d'abord, vous devez télécharger le Kutools pour Excel, puis appliquez la fonction rapidement et facilement.

Après l'installation de Kutools pour Excel, veuillez faire comme ceci:

1. Cliquez Kutools > insérer > Lister toutes les combinaisons, voir capture d'écran:

2. Dans le Lister toutes les combinaisons boîte de dialogue, spécifiez les données des colonnes et les séparateurs pour lister les combinaisons comme illustré ci-dessous:

3. Après avoir défini les données et le séparateur, cliquez sur Ok bouton, dans la boîte d'invite suivante, sélectionnez une cellule pour localiser le résultat, voir capture d'écran:

4. Et puis, cliquez OK bouton, toutes les combinaisons ont été générées immédiatement comme illustré ci-dessous:

Cliquez pour télécharger Kutools pour Excel et un essai gratuit maintenant!

  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes et conservation des données; Contenu des cellules divisées; Combiner les lignes en double et la somme / moyenne... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Formules favorites et insérer rapidement, Plages, graphiques et images; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Regroupement du tableau croisé dynamique par numéro de semaine, jour de la semaine et plus encore ... Afficher les cellules déverrouillées et verrouillées par différentes couleurs; Mettre en évidence les cellules qui ont une formule / un nom...
onglet kte 201905
  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
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 · 7 days 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 · 3 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 · 8 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 · 9 months ago
    Thank you so much. Exactly what I need :-)))
  • To post as a guest, your comment is unpublished.
    Karthik · 10 months 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 · 9 months ago
      i need this formula for 4 columns
  • To post as a guest, your comment is unpublished.
    Nikhil · 11 months 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 · 11 months 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.
        Tikikus · 3 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))