Créer un produit cartésien (toutes les combinaisons) à partir de 2 colonnes dans Excel — Guide complet
Lorsque vous travaillez avec deux listes dans Excel — par exemple des noms de produits et des tailles, des régions et des commerciaux, ou des étudiants et des cours — vous pouvez avoir besoin de générer toutes les combinaisons possibles entre elles. Cette opération s’appelle une jointure croisée (ou produit cartésien). Ce tutoriel vous guide pas à pas, présente les avantages et inconvénients de chaque méthode, et propose des exemples concrets pour vous aider à choisir celle qui s’intègre le mieux à votre flux de travail.
Qu’est-ce qu’un produit cartésien ?
Un produit cartésien (également appelé jointure croisée) est une opération qui génère toutes les combinaisons possibles entre deux listes. Dans Excel, cela revient à associer chaque élément de la liste A à chacun des éléments de la liste B, produisant ainsi une matrice complète de combinaisons.
Les produits cartésiens sont extrêmement utiles dans de nombreux cas concrets, tels que :
Variantes de produits
Combinez couleurs, tailles et styles pour générer un catalogue produit complet.
Analyse commerciale
Créez des combinaisons entre régions, commerciaux et trimestres.
Planification et organisation
Générez toutes les combinaisons possibles entre employés × postes ou étudiants × cours.
Tests et simulations
Générez des combinaisons de scénarios pour la modélisation, la prévision ou la validation.
Exemple :
Si vous avez :
Le résultat de la jointure croisée sera :
Effectuer un produit cartésien dans Excel
Excel propose plusieurs méthodes pour créer un produit cartésien, et le choix de la meilleure dépend de votre version d’Excel, de votre maîtrise des formules ou des outils, ainsi que de la taille de vos données. Voici quatre méthodes pratiques et efficaces à votre disposition — allant des formules simples aux outils plus avancés comme Power Query et VBA. Chacune présente des avantages spécifiques, vous permettant de sélectionner celle qui s’adapte le mieux à votre flux de travail, à la volumétrie de vos données et à vos besoins en automatisation.
Méthode 1 : Produit cartésien à l’aide d’une formule (Excel 365)
1. Préparez vos données. Placez votre première liste dans une colonne (par exemple, A2:A5 pour les produits) et votre deuxième liste dans une autre colonne (par exemple, C2:C5 pour les couleurs).
2. Saisissez la formule suivante dans une cellule vide où vous souhaitez afficher le résultat, puis appuyez sur Entrée. La formule se propagera automatiquement et générera toutes les combinaisons d’un seul coup. Voir la capture d’écran :
=TEXTSPLIT(TEXTJOIN(",", TRUE, A2:A5 & "|" & TRANSPOSE(C2:C5)), "|", ",")
Explication de cette formule :
- A2:A5 & «|» & TRANSPOSE(C2:C5) : Associe chaque valeur de la colonne A à chaque valeur de la colonne C.
- TEXTJOIN(",", VRAI, …) : Combine tous les éléments en une seule chaîne de texte, séparés par des virgules.
- TEXTSPLIT(…, «|», ",") : Divise à nouveau le texte en un tableau structuré sur deux colonnes.
En résumé, la formule crée des paires A|C, les fusionne en une seule chaîne de texte, puis les divise à nouveau en un tableau structuré sur deux colonnes — générant ainsi toutes les combinaisons possibles.
Conseils :
Outre la formule présentée précédemment, vous pouvez également utiliser celle ci-dessous pour obtenir le même résultat.
=LET(a,A2:A5,b,C2:C5,
MAKEARRAY(ROWS(a)*ROWS(b),2,
LAMBDA(r,c,
IF(c=1, INDEX(a, 1+INT((r-1)/ROWS(b))), INDEX(b, 1+MOD(r-1, ROWS(b))))
)
))
Avantages
- Entièrement dynamique
- Aucune colonne auxiliaire requise
- Mise à jour automatique lorsque les listes sources changent
Inconvénients
- Nécessite Excel 365
- Formule complexe pour les débutants
✨ Lister toutes les combinaisons — Générez d’un seul clic toutes les combinaisons possibles !
Vous en avez assez de composer des formules complexes pour obtenir toutes les combinaisons possibles ? Avec Kutools pour Excel, listez instantanément toutes les combinaisons issues de plusieurs colonnes ou valeurs — sans formules, sans Power Query, juste quelques clics !
Méthode 2 : Produit cartésien à l’aide de Power Query
Power Query vous offre une approche propre et sans code, compatible avec toutes les versions modernes d’Excel. Il vous permet de générer un produit cartésien en suivant des étapes simples et intuitives, sans avoir à saisir la moindre formule. Suivez les instructions détaillées ci-dessous.
Étape 1 : Créez des tableaux pour chaque colonne de données
1. Sélectionnez la première liste de données, cliquez sur Insertion > Tableau. Dans la boîte de dialogue Créer un tableau, cliquez sur OK. Vous obtiendrez ainsi votre premier tableau.
2. Depuis l’onglet Conception de tableau, attribuez au tableau un nom significatif pour le référencer plus facilement par la suite.
3. Répétez les mêmes étapes pour transformer une autre colonne de données en tableau et lui attribuer un nom.
Étape 2 : Importez les tableaux et chargez-les en tant que connexions
1.Sélectionnez le premier tableau et cliquez sur Données>À partir d’un tableau/plage, voir capture d’écran :
2. Dans la fenêtre Éditeur Power Query qui s’ouvre, cliquez sur Fermer et charger > Fermer et charger depuis l’onglet Accueil.
3. Une boîte de dialogue Importer des données apparaît ; sélectionnez l’option Créer uniquement une connexion, puis cliquez sur OK.
4.Le volet Requêtes et connexions apparaît à droite, indiquant qu’une requête a été ajoutée en tant que connexion uniquement.
5. Répétez les mêmes étapes pour charger le deuxième tableau en tant que requête en mode connexion uniquement ; il apparaîtra alors à côté du premier dans le volet Requêtes et connexions.
Étape 3 : Créez une requête de référence et une colonne personnalisée
1. Cliquez avec le bouton droit sur la requête que vous souhaitez utiliser comme premier tableau dans la jointure croisée, puis sélectionnez Référence.
2.Dans la fenêtre Éditeur Power Query, accédez à l’onglet Ajouter une colonne, puis cliquez sur Colonne personnalisée, voir capture d’écran :
3. Dans la boîte de dialogue Colonne personnalisée, saisissez dans le champ Formule de la colonne personnalisée le nom de l’autre tableau que vous souhaitez utiliser pour la jointure croisée, puis cliquez sur le bouton OK.
Remarque :
Si le nom de votre requête contient des espaces (par exemple, Couleur produit), vous devez l’encadrer à l’aide de la syntaxe #«Nom de la requête» lors de sa saisie dans le champ Formule de la colonne personnalisée. Ainsi, pour « Couleur produit », saisissez #«Couleur produit».
4.Une nouvelle colonne personnalisée apparaît ; cliquez sur le bouton Développer pour en afficher le contenu.
5. Dans le volet déroulé, assurez-vous que la case correspondant au nom de la colonne que vous souhaitez joindre est cochée, puis cliquez sur OK.
6.Vous voyez désormais toutes les combinaisons générées à partir des deux tableaux.
Étape 4 : Chargez les données dans la feuille de calcul
Accédez à l’onglet Accueil, cliquez sur Fermer et charger > Fermer et charger. Le tableau contenant toutes les combinaisons sera chargé dans une nouvelle feuille de calcul.
→
Avantages
- Gère de grandes quantités de données : performances exceptionnelles, même avec des milliers de lignes.
- Réutilisable et actualisable : si vous ajoutez davantage de données à la plage source, il suffit d’actualiser la requête pour que les résultats soient automatiquement mis à jour.
Inconvénients
- Légèrement plus d’étapes
- Nécessite des connaissances de base en Power Query
Méthode 3 : Produit cartésien à l’aide de Tableau croisé dynamique
Cette méthode est indirecte, mais elle peut s’avérer étonnamment efficace pour générer toutes les combinaisons sans avoir à écrire de formules. Elle convient particulièrement aux utilisateurs qui privilégient une approche visuelle et intuitive, fondée sur les clics, ainsi qu’à ceux qui utilisent des versions d’Excel ne prenant pas en charge les formules de tableau dynamique.
1. Créez deux tableaux distincts pour vos listes de données et nommez-les en suivant les mêmes étapes que celles décrites à l’étape 1 de la méthode 2.
2. Sélectionnez le tableau que vous souhaitez utiliser comme première colonne. Ensuite, rendez-vous dans l’onglet Insertion, puis cliquez sur Tableau croisé dynamique. Voir la capture d’écran :
3. Dans la boîte de dialogue Tableau croisé dynamique à partir d’un tableau ou d’une plage, sélectionnez Feuille de calcul existante, choisissez l’emplacement du tableau croisé dynamique, cochez l’option Ajouter ces données au modèle de données, puis cliquez sur OK.
4. Lorsque le volet Champs de tableau croisé dynamique apparaît à droite, cochez le nom de la colonne du tableau ; celle-ci sera automatiquement ajoutée à la zone Lignes.
5. Ensuite, basculez vers l’onglet Tout du volet Champs de tableau croisé dynamique, sélectionnez le tableau que vous souhaitez utiliser comme deuxième colonne de votre jointure croisée, puis cochez le nom de sa colonne pour l’ajouter à la zone Lignes. Vous obtiendrez le tableau croisé dynamique illustré dans la capture d’écran ci-dessous :
6. Cliquez sur une cellule du tableau croisé dynamique, accédez à l’onglet Conception, puis sélectionnez Disposition du rapport > Afficher sous forme tabulaire pour obtenir votre tableau croisé dynamique en vue tabulaire. Voir la capture d’écran :
7. Cliquez ensuite sur Disposition du rapport > Répéter toutes les étiquettes d’éléments pour afficher tous les éléments sur chaque ligne.
8. Enfin, cliquez sur Totaux généraux > Désactivé pour les lignes et les colonnes.
Le tableau croisé dynamique affiche désormais clairement toutes les combinaisons, sans aucune ligne ni colonne de synthèse.
Avantages
- Aucune formule ni Power Query nécessaire
- Très facile et visuel
- Idéal pour une analyse rapide
Inconvénients
- Non dynamique
- Nécessite des actions manuelles
- Le résultat n’est pas lié aux données d’origine
Méthode 4 : Produit cartésien à l’aide d’une fonction personnalisée (Excel 365 / Excel 2021 et versions ultérieures)
Si vous devez fréquemment générer toutes les combinaisons possibles entre deux listes, une fonction personnalisée (UDF) s’impose comme une solution propre, réutilisable et extrêmement flexible. Avec Excel 365 ou Excel 2021, la sortie sous forme de tableau dynamique rend les résultats entièrement dynamiques, vous offrant ainsi un moyen puissant et pratique d’automatiser vos opérations de produit cartésien.
1. Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
2. Ensuite, cliquez sur Insertion > Module, puis copiez-collez le code suivant dans le module vierge.
Function CrossJoin(list1 As Range, list2 As Range)
'Updateby Extendoffice
Dim arr1, arr2, result()
Dim i As Long, j As Long, r As Long
arr1 = list1.Value
arr2 = list2.Value
ReDim result(1 To UBound(arr1, 1) * UBound(arr2, 1), 1 To 2)
r = 1
For i = 1 To UBound(arr1, 1)
For j = 1 To UBound(arr2, 1)
result(r, 1) = arr1(i, 1)
result(r, 2) = arr2(j, 1)
r = r + 1
Next j
Next i
CrossJoin = result
End Function
3. Revenez à la feuille Excel, saisissez la formule ci-dessous, appuyez sur Entrée, et Excel générera automatiquement toutes les combinaisons.
=CrossJoin(A2:A5, C2:C5)
Conclusion
En résumé, effectuer un produit cartésien dans Excel offre plusieurs solutions flexibles et efficaces, vous permettant de choisir la méthode la plus adaptée à vos besoins spécifiques et à votre environnement de travail :
- La formule utilise les formules de tableau dynamique d’Excel 365 pour générer rapidement des résultats sans programmation, ce qui la rend idéale pour les utilisateurs occasionnels qui préfèrent les formules natives.
- Power Query propose un processus clair et réutilisable, parfait pour traiter de grands jeux de données — ce qui en fait le choix idéal pour nettoyer vos données et créer des rapports automatisés.
- La méthode Tableau croisé dynamique est peut-être moins directe, mais elle s’avère très efficace et intuitive pour un public familiarisé avec cet outil.
- La fonction personnalisée VBA offre le plus haut niveau de personnalisation et convient parfaitement aux scénarios nécessitant une intégration dans du code macro complexe.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez 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 vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels