Arrêtez de perdre des heures à copier-coller dans Excel : générez instantanément toutes vos combinaisons de données
Dans ce tutoriel, je vais vous montrer comment générer en quelques secondes toutes les combinaisons possibles entre plusieurs listes dans Excel — et construire un catalogue produit complet de 480 références sans copier-coller une seule ligne.
Pour cela, nous allons découvrir deux méthodes que vous pourrez appliquer immédiatement dans votre propre fichier.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les données de base d'une boutique de prêt-à-porter en ligne.
Cette boutique propose des vêtements déclinés en trois dimensions :
- des catégories de vêtements,
- des couleurs disponibles,
- et des tailles proposées.
Notre objectif est de générer automatiquement toutes les références du catalogue — c'est-à-dire chaque combinaison possible entre ces trois listes.
Avec 10 catégories, 8 couleurs et 6 tailles, nous obtenons 10 × 8 × 6 = 480 combinaisons.
Réaliser cela à la main prendrait plusieurs heures, et si nous devions ajouter une nouvelle couleur demain, tout serait à recommencer.
Avant d'aller plus loin, nous allons transformer chacune de nos trois listes en tableau structuré.
Un tableau structuré est une plage que Excel reconnaît officiellement comme une base de données : il porte son propre nom, ses en-têtes sont identifiés, et surtout, il s'étend automatiquement quand nous ajoutons une ligne.
Cela rendra nos formules et nos requêtes Power Query totalement dynamiques.
Nous cliquons sur une cellule de la liste « Catégorie », puis nous appuyons sur [Ctrl]+[L].
Dans la fenêtre qui s'ouvre, nous vérifions que « Mon tableau comporte des en-têtes » est bien coché, et nous validons avec « OK ».
Nous nous rendons ensuite dans l'onglet « Création de tableau » du ruban et, dans la zone « Nom du tableau », nous saisissons « tblCategories ».
Nous répétons cette opération pour les deux autres listes, en les nommant « tblCouleurs » et « tblTailles ».
Ces noms courts sont importants : nous les retrouverons dans nos formules et dans Power Query.
Un nom trop long alourdit inutilement la lecture.
Il faut également les définir maintenant, avant de créer quoi que ce soit, car les modifier après coup peut casser toutes les requêtes existantes.
2. Méthode rapide : Power Query
Power Query est un outil intégré dans Excel depuis la version 2016, disponible dans l'onglet « Données ».
Il permet de combiner et de transformer des données sans écrire la moindre ligne de code.
C'est la méthode la plus rapide pour notre cas d'usage.
2.1. Charger les tableaux dans Power Query
Nous cliquons sur une cellule de « tblCategories », puis dans l'onglet « Données », nous cliquons sur « À partir d'un tableau/d'une plage ».
L'éditeur Power Query s'ouvre avec un aperçu de notre liste.
Nous n'avons aucune transformation à faire ici : nous cliquons simplement sur « Fermer et charger » → « Fermer et charger dans… ».
Dans la fenêtre qui s'ouvre, nous sélectionnons « Uniquement créer la connexion » et nous cliquons sur « OK ».
Notre tableau est maintenant enregistré dans Power Query comme connexion, sans générer de nouvelle feuille dans notre classeur.
Nous répétons cette opération pour « tblCouleurs » et « tblTailles ».
À l'issue de cette étape, le volet « Requêtes et connexions » — accessible depuis l'onglet « Données » — affiche nos trois connexions.
Si ce volet n'est pas visible, nous cliquons sur le bouton « Requêtes et connexions » dans le ruban pour l'afficher.
2.2. Créer la liste combinée
Dans le volet « Requêtes et connexions », nous faisons un clic droit sur la requête « tblCategories » et nous sélectionnons « Dupliquer ».
Une nouvelle requête s'ouvre dans l'éditeur Power Query.
Nous double-cliquons sur son nom dans le volet gauche et nous la renommons « CatalogueCombine ».
Nous nous rendons dans l'onglet « Ajouter une colonne » du ruban de l'éditeur, et nous cliquons sur « Colonne personnalisée ».
Dans la fenêtre qui s'ouvre, nous saisissons « Couleur » comme nom de colonne, et dans la zone de formule, nous tapons simplement : tblCouleurs.
Nous validons avec « OK ».
Une nouvelle colonne « Couleur » apparaît, avec la mention [Table] dans chaque cellule.
Cela signifie que Power Query a associé à chaque catégorie la liste entière des couleurs.
Nous cliquons sur l'icône en forme de double flèche dans l'en-tête de la colonne pour « développer » ces tables imbriquées, et nous validons.
Chaque catégorie est maintenant répétée pour chacune des 8 couleurs : nous obtenons 80 lignes.
Nous répétons exactement la même opération pour les tailles : « Ajouter une colonne » → « Colonne personnalisée », nom « Taille », formule tblTailles, et développement.
Nous obtenons nos 480 combinaisons.
2.3. Nettoyer les en-têtes et charger le résultat
Power Query a nommé les nouvelles colonnes « Couleur.Couleur » et « Taille.Taille » à cause du développement.
Pour corriger cela sans ajouter d'étapes inutiles dans la requête, nous cliquons dans le volet « Étapes appliquées » sur l'étape de développement concernée.
Dans la barre de formule, nous remplaçons « Couleur.Couleur » par « Couleur » directement dans le code, et nous validons avec [Entrée].
Nous faisons de même pour « Taille.Taille ».
Enfin, nous cliquons sur « Fermer et charger » → « Fermer et charger dans… », nous sélectionnons « Tableau » puis « Feuille de calcul existante », nous choisissons une cellule de départ (par exemple la cellule G6), et nous cliquons sur « OK ».
Les 480 références s'affichent en quelques secondes.
2.4. Mise à jour automatique et limites
Si nous ajoutons une nouvelle catégorie, par exemple « Kimono », dans « tblCategories », il suffit d'aller dans l'onglet « Données » et de cliquer sur « Actualiser tout » — ou d'utiliser le raccourci [Ctrl]+[Alt]+[F5] — pour que le catalogue passe instantanément de 480 à 528 références.
Néanmoins, Power Query présente une limite importante à connaître avant de partager le fichier, la plupart des utilisateur d’Excel ignorent complètement son existence !
Ils ne sauront donc pas qu'il faut actualiser pour voir les nouvelles données.
De plus, Power Query est très sensible aux noms : si quelqu'un renomme la colonne « Couleur » en « Teinte » dans le tableau source, la requête retournera une table vide au prochain rafraîchissement, sans afficher le moindre message d'erreur visible.
La règle d'or : fiez les noms de colonnes et de tableaux dès le départ, avant de créer la moindre requête.
3. Méthode formules : une seule formule pour tout générer
Si le fichier doit être utilisé ou modifié par des personnes qui ne connaissent pas Power Query, la méthode par formules sera alors plus adaptée.
Chaque calcul est visible, lisible et auditable directement dans les cellules.
L'idée ici est radicalement différente de ce que l'on pourrait imaginer. Au lieu de construire un tableau colonne par colonne avec des formules de comptage réparties sur des centaines de lignes, nous allons écrire une seule formule, dans une seule cellule, qui va déverser automatiquement les 480 combinaisons dans trois colonnes adjacentes.
Pour gagner du temps, nous utilisons la fonction « Créer une formule » de mon outil « IA Excelformation.fr », dans laquelle nous saisissons le prompt suivant :
« Génère une formule Excel 365 qui produit une matrice déversée de toutes les combinaisons possibles entre trois tableaux structurés nommés tblCategories (colonne « Catégorie »), tblCouleurs (colonne « Couleur ») et tblTailles (colonne « Taille »). »
L'IA nous retourne la formule suivante :
=LET(a;tblCategories[Catégorie];b;tblCouleurs[Couleur];c;tblTailles[Taille];ra;LIGNES(a);rb;LIGNES(b);rc;LIGNES(c);total;ra*rb*rc;catIdx;MOD(SEQUENCE(total;1;0);ra)+1;colIdx;MOD(ENT(SEQUENCE(total;1;0)/ra);rb)+1;sizeIdx;ENT(SEQUENCE(total;1;0)/(ra*rb))+1;ASSEMB.H(INDEX(a;catIdx);INDEX(b;colIdx);INDEX(c;sizeIdx)))
Il est possible que la formule retournée sur votre écran diffère légèrement de celle-ci — ce n'est pas important tant que le résultat affiché est correct.
N'hésitez pas à relancer l'opération si le résultat ne vous convient pas.
Si tout se passe bien, Excel devrait alors déverser instantanément un tableau de 480 lignes et 3 colonnes. Nous devons simplement nous assurer que la zone à droite et en dessous est entièrement vide avant de valider, sinon Excel affichera l'erreur #PROPAGATION! indiquant que des cellules occupées bloquent le déversement.
Décortiquons maintenant cette formule étape par étape, pour que vous puissiez la comprendre et l'adapter à vos propres listes.
Bien entendu, dans la mesure où le résultat est correct, il n’est pas nécessaire de comprendre la formule, d’autant que celle-ci est relativement complexe.
Mais cela vous permettra d’en comprendre le principe, même si vous ne disposez pas de l’outil incroyable qu’est IA Excelformation.fr.
3.1. LET : nommer les ingrédients avant de cuisiner
La formule commence par utiliser la fonction LET, qui est une sorte de bloc-notes intégré à la formule.
Elle nous permet de donner des noms courts à des valeurs ou des plages, afin de ne pas les réécrire à chaque fois.
Les trois premiers couples d’arguments définissent nos listes sources.
- a désigne la colonne « Catégorie » du tableau tblCategories,
- b désigne la colonne « Couleur » de tblCouleurs,
- et c désigne la colonne « Taille » de tblTailles.
Ces noms à une lettre sont volontairement courts pour alléger la formule.
Les trois suivantes comptent le nombre de lignes dans chacune de ces listes, grâce à la fonction LIGNES, qui est l'équivalent de « combien d'éléments contient cette liste ».
- ra vaut donc 10 (nos catégories),
- rb vaut 8 (nos couleurs)
- et rc vaut 6 (nos tailles)
Enfin, total multiplie ces trois compteurs entre eux : ra*rb*rc = 10 × 8 × 6 = 480.
C'est le nombre total de combinaisons que nous allons générer.
3.2. SEQUENCE : le compteur universel
La formule utilise ensuite SEQUENCE(total;1;0) à trois reprises dans la suite des calculs.
Cette fonction génère une liste de nombres consécutifs : ici, elle produit les nombres de 0 à 479 dans une seule colonne verticale.
C'est ce compteur qui va nous permettre de calculer, pour chaque ligne, quel élément de chaque liste afficher.
Pourquoi commencer à 0 et non à 1 ? Parce que les fonctions MOD et ENT que nous allons utiliser juste après fonctionnent plus naturellement avec une numérotation qui part de zéro.
Le +1 final dans chaque calcul d'indice se chargera de ramener le résultat dans la plage 1–10, 1–8 ou 1–6 attendue par INDEX.
3.3. catIdx, colIdx, sizeIdx : calculer l'indice de chaque liste
catIdx calcule pour chaque ligne quel numéro de catégorie afficher.
catIdx;MOD(SEQUENCE(total;1;0);ra)+1
MOD(n;ra) calcule le reste de la division de chaque numéro par 10. Pour les numéros 0 à 9, le reste est 0, 1, 2, …, 9.
Pour les numéros 10 à 19, le reste recommence à 0, 1, 2, …, 9.
On obtient donc une séquence cyclique.
Le +1 transforme cela en 1 à 10 pour qu'INDEX puisse pointer sur la bonne ligne de la liste.
colIdx calcule quel numéro de couleur afficher.
colIdx;MOD(ENT(SEQUENCE(total;1;0)/ra);rb)+1
ENT(n/ra) divise chaque numéro par 10 et arrondit à l'inférieur.
Cela donne 0 pour les lignes 0 à 9, 1 pour les lignes 10 à 19, 2 pour les lignes 20 à 29, etc.
En d'autres termes, la couleur ne change qu'après un cycle complet des 10 catégories.
MOD(...;rb) fait ensuite cycler cette valeur entre 0 et 7, et le +1 donne 1 à 8.
sizeIdx calcule quel numéro de taille afficher.
sizeIdx;ENT(SEQUENCE(total;1;0)/(ra*rb))+1
ENT(n/(ra*rb)) divise par 80.
La taille ne change donc que toutes les 80 lignes, ce qui correspond exactement à un cycle complet de catégories × couleurs.
Sur les 480 lignes, elle passe de 1 à 6, une seule fois, sans jamais cycler — d'où l'absence de MOD ici.
3.4. ASSEMB.H : assembler les trois colonnes
La dernière instruction est ASSEMB.H, qui prend nos trois colonnes de résultats et les place côte à côte.
ASSEMB.H(INDEX(a;catIdx);INDEX(b;colIdx);INDEX(c;sizeIdx))
INDEX(a;catIdx) retourne, pour chaque ligne, la catégorie dont le numéro est indiqué par catIdx.
INDEX(b;colIdx) fait de même pour les couleurs, et INDEX(c;sizeIdx) pour les tailles.
ASSEMB.H assemble ces trois colonnes de 480 valeurs en un tableau final de 480 lignes × 3 colonnes, qui se déverse automatiquement à partir de la cellule A2.
3.5. Mise à jour automatique
Tout comme la méthode Power Query, l’avantage de cette formule est qu’elle est entièrement pilotée par les tableaux sources.
Si nous ajoutons la couleur « Orange » dans tblCouleurs, la valeur rb passe de 8 à 9, total recalcule à 540, et Excel déverse automatiquement 60 lignes supplémentaires, sans aucune intervention de notre part.
La seule contrainte à garder en tête : la zone de déversement doit rester libre.
Si une cellule est occupée en dessous ou à droite de la cellule, la formule affichera #PROPAGATION! jusqu'à ce que l'espace soit libéré.
