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

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.

2Cliquez sur 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:

1Cliquez sur 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éeplus
  • 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 gammesplus
  • 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 cellulesplus
  • 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 PDFplus
  • 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 nomplus
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
Commentaires (17)
Pas encore de notes. Soyez le premier à évaluer!
Ce commentaire a été minimisé par le modérateur sur le site
Pour générer une combinaison aléatoire, nous pouvons utiliser
=INDEX($A$2:$A$4,RANDBETWEEN(1,3))&"-"&INDEX($B$2:$B$6,RANDBETWEEN(1,5))&"-"&INDEX($C$2:$C$5,RANDBETWEEN(1,4))
Ce commentaire a été minimisé par le modérateur sur le site
Merci beaucoup d'avoir partagé ce message. Merci beaucoup à @Balaji pour sa formule
Ce commentaire a été minimisé par le modérateur sur le site
JE NE VOUS REMERCIERAI JAMAIS ASSEZ! M'A FAIT GAGNER BEAUCOUP DE TEMPS !
Ce commentaire a été minimisé par le modérateur sur le site
Comment faire ci-dessous la formule pour 5 colonnes ? Essayer de comprendre mais ça donne une erreur
=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),"")
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, Nikhil, Pour obtenir toutes les combinaisons de 5 colonnes, le code VBA ci-dessous peut vous aider, veuillez modifier les références de cellule en vos données.
Sous-listetoutescombinaisons()
'Mettre à jour par Extendoffice
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 comme plage
Dim xRg As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3, xFN4, xFN5 comme nombre entier
Dim xSV1, xSV2, xSV3, xSV4, xSV5 en tant que chaîne
Set xDRg1 = Range("A2:A7") 'Données de la première colonne
Set xDRg2 = Range("B2:B7") 'Deuxième colonne de données
Set xDRg3 = Range("C2:C7") 'Données de la troisième colonne
Set xDRg4 = Range("D2:D7") 'Données de la quatrième colonne
Set xDRg5 = Range("E2:E7") 'Données de la cinquième colonne
xStr = "-" 'Séparateur
Set xRg = Range("H2") 'Cellule de sortie
Pour xFN1 = 1 à xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Texte
Pour xFN2 = 1 à xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Texte
Pour xFN3 = 1 à xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Texte
Pour xFN4 = 1 à xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Texte
Pour xFN5 = 1 à xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Texte
xRg.Valeur = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5
Définir xRg = xRg. Décalage(1, 0)
Suivant
Suivant
Suivant
Suivant
Suivant
End SubVeuillez essayer, j'espère que cela pourra vous aider !
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour skyyang, j'ai changé le code pour 7 rouleaux, mais vu qu'Excel n'a que 1,048,576 1 0 lignes, le code VBA ne peut pas donner toutes les combinaisons. Avez-vous une idée de comment je peux continuer sur d'autres colonnes ? Je pense que ce code - Set xRg = xRg.Offset(XNUMX, XNUMX) devait changer
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour skyyang, j'ai changé le code pour 7 rouleaux, mais vu qu'Excel n'a que 1,048,576 XNUMX XNUMX lignes, le code VBA ne peut pas donner toutes les combinaisons.
Avez-vous une idée de comment je peux continuer sur d'autres colonnes ?
Je pense que ce code - Set xRg = xRg.Offset(1, 0)
besoin de changer 
Ce commentaire a été minimisé par le modérateur sur le site
=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),"")
Ce commentaire a été minimisé par le modérateur sur le site
j'ai besoin de cette formule pour 4 colonnes
Ce commentaire a été minimisé par le modérateur sur le site
Merci beaucoup. Exactement ce dont j'ai besoin :-)))
Ce commentaire a été minimisé par le modérateur sur le site
Merci beaucoup pour ce code. J'ai modifié le code pour la quantité de colonne dont j'ai besoin (25). Merci,
Ce commentaire a été minimisé par le modérateur sur le site
Le code HiIn VBA que j'ai utilisé quatre colonnes et la plage de la colonne sont E2: E75, B2: B267, C2: C195 et D2: D267. La plage de sortie est J2. Dans ce cas, le résultat de sortie a dépassé la limite de ligne. S'il vous plaît aider à résoudre l'erreur
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour Alors ceci est un code pour 9 colonnes :')
Sous-listetoutescombinaisons()
'Mettre à jour par Extendoffice
Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5, xDRg6, xDRg7, xDRg8, xDRg9 comme plage
Dim xRg As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3, xFN4, xFN5, xFN6, xFN7, xFN8, xFN9 comme nombre entier
Dim xSV1, xSV2, xSV3, xSV4, xSV5, xSV6, xSV7, xSV8, xSV9 en tant que chaîne
Set xDRg1 = Range("A2:A3") 'Données de la première colonne
Set xDRg2 = Range("B2:B3") 'Deuxième colonne de données
Set xDRg3 = Range("C2:C10") 'Données de la troisième colonne
Set xDRg4 = Range("D2:D2") 'Données de la troisième colonne
Set xDRg5 = Range("E2:E3") 'Données de la troisième colonne
Set xDRg6 = Range("F2:F3") 'Données de la troisième colonne
Set xDRg7 = Range("G2:G4") 'Données de la troisième colonne
Set xDRg8 = Range("H2:H3") 'Données de la troisième colonne
Set xDRg9 = Range("I2:I3") 'Données de la troisième colonne
xStr = "-" 'Séparateur
Set xRg = Range("K2") 'Cellule de sortie
Pour xFN1 = 1 à xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Texte
Pour xFN2 = 1 à xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Texte
Pour xFN3 = 1 à xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Texte
Pour xFN4 = 1 à xDRg4.Count
xSV4 = xDRg4.Item(xFN4).Texte
Pour xFN5 = 1 à xDRg5.Count
xSV5 = xDRg5.Item(xFN5).Texte
Pour xFN6 = 1 à xDRg6.Count
xSV6 = xDRg6.Item(xFN6).Texte
Pour xFN7 = 1 à xDRg7.Count
xSV7 = xDRg7.Item(xFN7).Texte
Pour xFN8 = 1 à xDRg8.Count
xSV8 = xDRg8.Item(xFN8).Texte
Pour xFN9 = 1 à xDRg9.Count
xSV9 = xDRg9.Item(xFN9).Texte
xRg.Valeur = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9
Définir xRg = xRg. Décalage(1, 0)
Suivant
Suivant
Suivant
Suivant
Suivant
Suivant
Suivant
Suivant
Suivant
End Sub
Ce commentaire a été minimisé par le modérateur sur le site
조합의 나열을 "단어-단어-단어" 순으로 나열하는 법은 이해했습니다.
그런데 "단어" 가 아니라 숫자일 경우,
즉 숫자의 조합을 단순 나열이 아닌 덧셈이나 곱셈으로 적용하려면 어떻게 해야하는지 알 수 알 수 알 수 칞

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

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

-1 로 엑셀에 표기될 수 있게 말입니다.
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, comment faire pour que chaque valeur soit placée dans une colonne distincte en non séparées par un tiret ?
Ce commentaire a été minimisé par le modérateur sur le site
Esse código me ajudou bastante e combinou um item de cada coluna entre elas. Mas tambem preciso combinar dois itens de cada coluna, sem repetição. Alguém poderia me ajudar nisso?
Ce commentaire a été minimisé par le modérateur sur le site
Esse código me ajudou bastante e combinou um item de cada coluna entre elas. Mas tambem preciso combinar dois itens de cada coluna, sem repetição. Alguém poderia me ajudar nisso?
Il n'y a pas encore de commentaires postés ici
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0   Personnages
Emplacements suggérés