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

Comment lister ou générer toutes les combinaisons possibles dans Excel?

Disons que j'ai les deux colonnes de données suivantes, et maintenant, je veux générer une liste de toutes les combinaisons possibles en fonction des deux listes de valeurs comme illustré à gauche. Peut-être que vous pouvez lister toutes les combinaisons une par une s'il y a peu de valeurs, mais, s'il y a plusieurs colonnes avec plusieurs valeurs nécessaires pour répertorier les combinaisons possibles, voici quelques astuces rapides qui peuvent vous aider à résoudre ce problème dans Excel .

Listez ou générez toutes les combinaisons possibles à partir de deux listes avec formule

Répertoriez ou générez toutes les combinaisons possibles à partir de trois listes ou plus avec le code VBA

Répertoriez ou générez toutes les combinaisons possibles à partir de plusieurs listes avec une fonctionnalité puissante


Listez ou générez toutes les combinaisons possibles à partir de deux listes avec formule

La formule longue suivante peut vous aider à lister rapidement toutes les combinaisons possibles de deux valeurs de listes, procédez comme suit:

1. Entrez ou copiez la formule ci-dessous dans une cellule vide, dans ce cas, je l'entrerai dans la cellule D2, puis appuyez sur Entrer clé pour obtenir le résultat, voir capture d'écran:

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

Notes: Dans la formule ci-dessus, $ A $ 2: $ A $ 5 est la plage des valeurs de la première colonne, et 2 B $: 4 B $ est la plage des deuxièmes valeurs de liste dont vous souhaitez lister toutes leurs combinaisons possibles, le $ D $ 2 est la cellule dans laquelle vous mettez la formule, vous pouvez modifier les références de cellule selon vos besoins.

2. Sélectionnez ensuite la cellule D2 et faites glisser la poignée de recopie vers les cellules jusqu'à obtenir les cellules vides, et toutes les combinaisons possibles ont été répertoriées en fonction des valeurs des deux listes. Voir la capture d'écran:


Répertoriez ou générez toutes les combinaisons possibles à partir de trois listes ou plus avec le code VBA

Peut-être que la formule ci-dessus est quelque peu difficile à appliquer pour vous, s'il y a plusieurs colonnes de données, 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 le Module Fenêtre.

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: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
Notes: Dans le code ci-dessus, A2: A5, B2: B4, C2: C4 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 colonnes seront générées en même temps, voir capture d'écran:


Répertoriez ou générez toutes les combinaisons possibles à partir de plusieurs listes avec une fonctionnalité puissante

S'il y a plusieurs listes, les valeurs doivent être répertoriées les combinaisons possibles, il est peut-être difficile pour vous de modifier le code. Ici, je peux recommander un outil puissant - Kutools pour Excel, il contient une fonction pratique Lister toutes les combinaisons qui peut rapidement lister toutes les combinaisons possibles basées sur des listes de données données.

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, effectuez les opérations comme ci-dessous la démonstration montrée:

3. Ensuite, toutes les valeurs et séparateurs spécifiés ont été répertoriés dans la boîte de dialogue, voir capture d'écran:

4.Et puis cliquez sur Ok bouton, et une boîte de dialogue apparaîtra pour vous rappeler de sélectionner une cellule pour afficher le résultat, voir capture d'écran:

5Cliquez sur OK, toutes les combinaisons possibles basées sur les listes données ont été générées dans la feuille de calcul comme illustré ci-dessous:

Cliquez pour télécharger Kutools for Excel maintenant!


Articles plus relatifs:

  • Générer toutes les combinaisons de 3 ou plusieurs colonnes
  • 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?
  • Rechercher toutes les combinaisons égales à une somme donnée
  • Par exemple, j'ai la liste de nombres suivante, et maintenant, je veux savoir quelle combinaison de nombres dans la liste totalise jusqu'à 480, dans la capture d'écran suivante, vous pouvez voir qu'il y a cinq groupes de combinaisons possibles qui s'additionnent égaux à 480, comme 300 + 60 + 120, 300 + 60 + 40 + 80, etc. Cet article, je vais parler de certaines méthodes pour trouver quelles cellules se résument à une valeur spécifique dans Excel.
  • Générer ou répertorier toutes les permutations possibles
  • Par exemple, j'ai trois caractères XYZ, maintenant, je veux lister toutes les permutations possibles basées sur ces trois caractères pour obtenir six résultats différents comme ceci: XYZ, XZY, YXZ, YZX, ZXY et ZYX. Dans Excel, comment pouvez-vous générer ou répertorier rapidement toutes les permutations en fonction d'un nombre différent de caractères?
  • Générer une liste de toutes les combinaisons possibles à 4 chiffres
  • Dans certains cas, nous pouvons avoir besoin de générer une liste de toutes les combinaisons possibles de 4 chiffres de 0 à 9, ce qui signifie générer une liste de 0000, 0001, 0002… 9999. Pour résoudre rapidement la tâche de liste dans Excel, je vous présente quelques astuces.

 


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • 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 sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... 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 ...
  • 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
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • 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 (42)
Noté 5 hors 5 · évaluations 1
Ce commentaire a été minimisé par le modérateur sur le site
C'est une formule géniale ! Je n'ai aucune idée de comment cela fonctionne, mais je change juste les collum A et B pour correspondre à la longueur de mes listes et j'ai ma sortie en D1.
Ce commentaire a été minimisé par le modérateur sur le site
exemple : ma liste de valeurs est 1,2,3,4,5,6......80 , je veux que kutool affiche toutes les combinaisons d'un ensemble de 2 nombres, exemple : 1-2 , 1-3 , 1-4 , 1-5 , ..................79-80 . est-ce possible avec KUTOOL ?
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen de faire fonctionner cette formule là où elle produit les différentes combinaisons toujours séparées dans 2 colonnes distinctes, mais adjacentes les unes aux autres?
Ce commentaire a été minimisé par le modérateur sur le site
Avez-vous eu de la chance de trouver comment faire cela? J'essaie de faire exactement la même chose et d'avoir toutes mes probabilités dans 2 colonnes distinctes dans Excel.
Ce commentaire a été minimisé par le modérateur sur le site
Avez-vous déjà eu de la chance avec cela? J'essaie de faire la même chose et d'avoir toutes les combinaisons possibles les unes à côté des autres dans 2 colonnes distinctes.
Ce commentaire a été minimisé par le modérateur sur le site
Puis-je obtenir la formule pour toutes les combinaisons d'une matrice 5 * 5 (5 lignes et 5 colonnes) que j'ai essayé mais que je n'ai pas pu obtenir, aidez-moi s'il vous plaît .......
Ce commentaire a été minimisé par le modérateur sur le site
TRÈS UTILE. Je suis maintenant capable de générer des combinaisons très facilement.
Ce commentaire a été minimisé par le modérateur sur le site
Comment pourriez-vous utiliser KuTools, ou même une formule, si vous vouliez toutes les permutations de l'en-tête Mois. Jan, Jan et Fév, Jan et Mar, Jan et Mar, Jan et Fév et Mar, etc etc
Ce commentaire a été minimisé par le modérateur sur le site
quelqu'un sait comment modifier cela pour refléter 6 colonnes de données, plutôt que seulement deux ?
Ce commentaire a été minimisé par le modérateur sur le site
cherche la même chose. Quelqu'un sait?
Ce commentaire a été minimisé par le modérateur sur le site
quelqu'un sait comment modifier cela pour refléter 6 colonnes de données ?
Ce commentaire a été minimisé par le modérateur sur le site
Pour plus de colonnes :
La première partie de la formule doit être modifiée pour multiplier toutes les possibilités, par exemple ce serait pour 6 colonnes
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)
donc tu ajoutes
*COUNTA(VotrePlageIci)
pour chaque colonne
La deuxième partie de la formule doit également être modifiée pour chaque colonne comme ceci :
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)
donc tu ajoutes
&INDEX(Votreplageici,MOD(LIGNE()-LIGNE($I$2),COUNTA(Votreplageici))+1)

Alors mettez-les tous ensemble et vous obtenez cet exemple pour 6 colonnes :

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

Où vous colleriez cette formule dans $I$2 et elle regarderait $A$2:$A$9 $B$2:$B$9 $C$2:$C$9 ... jusqu'à $F$2:$F$9
Ce commentaire a été minimisé par le modérateur sur le site
ça ne marche pas :( plus compliqué que ça
Ce commentaire a été minimisé par le modérateur sur le site
Pour plus de colonnes :
La première partie de la formule doit être modifiée pour multiplier toutes les possibilités, par exemple ce serait pour 6 colonnes :

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)
donc tu ajoutes
*COUNTA(VotrePlageIci)
pour chaque colonne

La deuxième partie de la formule doit également être modifiée pour chaque colonne comme ceci :

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)

donc tu ajoutes

&INDEX(Votreplageici,MOD(LIGNE()-LIGNE($I$2),COUNTA(Votreplageici))+1)

Alors mettez-les tous ensemble et vous obtenez cet exemple pour 6 colonnes :

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

Où vous colleriez cette formule dans $I$2 et elle regarderait $A$2:$A$9 $B$2:$B$9 $C$2:$C$9 ... jusqu'à $F$2:$F$9
Ce commentaire a été minimisé par le modérateur sur le site
ça ne marche pas :( plus compliqué que ça
Ce commentaire a été minimisé par le modérateur sur le site
Une idée pour ajouter un espace entre les deux ?
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, Eoin,
la formule de cet article ne peut pas vous aider à ajouter un espace entre les valeurs de cellule, mais, vous pouvez appliquer le Kutools for Excel, avec lui, vous pouvez taper n'importe quel délimiteur car vous souhaitez séparer le résultat combiné, voir capture d'écran :
Ce commentaire a été minimisé par le modérateur sur le site
Vous pouvez ajouter un espace en ajoutant " "& avant d'indexer la colonne B, juste après le & existant, comme ceci...

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


....ou tout autre délimiteur que vous aimez !
Ce commentaire a été minimisé par le modérateur sur le site
KuTools est-il capable de trier les cellules d'affilée d'une manière "simple combinaison" ?
Je veux dire, si j'ai ces données:
_________________________
John Jack Paul Macy

Marc Larry

Jerry Paul Marie

Sam Jeff Peter Lucas
_________________________



Et je veux les sortir comme ceci:
___________
Jean-Jacques

John Paul

Jean Macy

Jack-Paul

Jack Macy

Paul Macy

Marc Larry

Jerry-Paul

jerry marier

Paul Marie

Sam Jeff

Sam Pierre

Sam Lucas

Jeff Pierre

Jeff Lucas

Pierre Lucas
____________


Comment puis-je faire ceci? KuTools peut-il faire quelque chose comme ça ?

Merci !
Ce commentaire a été minimisé par le modérateur sur le site
Salut Arthur,
Désolé, les Kutools ne peuvent pas vous aider à résoudre ce travail comme vous l'avez dit.
Merci pour votre commentaire.
Ce commentaire a été minimisé par le modérateur sur le site
Quelqu'un a-t-il déjà trouvé une formule pour le faire pour 5 ensembles de données? J'ai été perplexe là-dessus jusqu'à présent.
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour Marco,
Les Kutools peuvent vous aider à résoudre votre problème rapidement, veuillez vérifier la capture d'écran suivante :
S'il vous plaît essayez-le, j'espère que cela peut vous aider, merci!
Ce commentaire a été minimisé par le modérateur sur le site
Hi,
Je veux savoir quoi faire si je veux générer plus de 1,4 million de combinaisons et que je dépasse la limite de lignes Excel ?
Existe-t-il un moyen de mettre Kudos pour continuer son itération sur la colonne suivante ?
Ce commentaire a été minimisé par le modérateur sur le site
Pourquoi ma formule n'a pas fonctionné pour créer 25 combinaisons dans la colonne E (5 éléments de Col A * 5 éléments de Col B) ? Alors que la formule de cet article fonctionnait dans la colonne D lorsque je la faisais glisser sur 25 cellules ? Capture d'écran - https://prnt.sc/ihwr18
Ce commentaire a été minimisé par le modérateur sur le site
Hola me arroja una referencia circula la formule
=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))
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, et si ces combinaisons sont des nombres, comment puis-je faire le calcul pour les résultats ???? J'essaie d'ajouter le "+" mais Excel ne sait pas que c'est le symbole. Comment résolvez-vous cela ?
Ce commentaire a été minimisé par le modérateur sur le site
J'ai utilisé les extions et ça marche fantastique, mais maintenant j'ai un problème, j'ai besoin de calculer la somme, mais le résultat est un texte, j'entre un séparateur qui est le simbol +, mais finalement j'ai un texte et c'est impossible à convertir en une opération.
Ce commentaire a été minimisé par le modérateur sur le site
Presque très pratique. Ce serait bien s'il utilisait réellement tout ce qui se trouve dans la cellule ($A$1) et ne convertissait pas la cellule en texte. Ensuite, je pourrais simplement coller ce dont j'ai besoin dans A1 et le relancer sans rien changer.
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen de le sortir sous forme de fichier txt? Lorsque vous avez des millions de résultats dans la colonne D, il n'est pas vraiment pratique de faire glisser la poignée de recopie.
Il n'y a pas encore de commentaires postés ici
TÉLÉCHARGER PLUS
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0   Personnages
Emplacements suggérés