Table des matières
Récemment, un de mes amis m'a approché avec une demande d'aide à la génération de toutes les phrases possibles composées d'un ensemble de mots donnés. Des problèmes de ce type peuvent survenir lors de la compilation de listes de mots clés et d'expressions pour la publicité en ligne et la promotion SEO, lorsque vous devez parcourir toutes les permutations possibles de mots dans une requête de recherche :
En mathématiques, cette opération s'appelle produit cartésien. La définition officielle est la suivante : le produit cartésien des ensembles A et B est l'ensemble de toutes les paires dont la première composante appartient à l'ensemble A et la seconde composante appartient à l'ensemble B. De plus, les éléments des ensembles peuvent être à la fois chiffres et texte.
Traduit en langage humain, cela signifie que si dans l'ensemble A nous avons, par exemple, les mots "blanc" et "rouge", et dans l'ensemble B "BMW" et "Mercedes", alors après le produit cartésien de ces deux ensembles nous get sur la sortie est l'ensemble de toutes les variantes possibles de phrases, composé des mots des deux listes :
- Bmw blanche
- BMW rouge
- Mercedes blanche
- Mercedes rouge
… c'est-à-dire juste ce dont nous avons besoin. Regardons quelques façons de résoudre cette tâche dans Excel.
Méthode 1. Formules
Commençons par les formules. Supposons qu'en tant que données initiales, nous ayons trois listes de mots originaux dans les colonnes A, B et C, respectivement, et que le nombre d'éléments dans chaque liste puisse varier :
D'abord, faisons trois colonnes avec des indices, c'est-à-dire des nombres ordinaux de mots de chaque liste dans toutes les combinaisons possibles. La première ligne d'unités (E2:G2) sera saisie manuellement, et pour le reste nous utiliserons la formule suivante :
La logique ici est simple : si l'indice dans la cellule précédente supérieure a déjà atteint la fin de la liste, c'est-à-dire est égal au nombre d'éléments de la liste calculé par la fonction COUNT (COMPTERA), puis on recommence la numérotation. Sinon, nous augmentons l'indice de 1. Portez une attention particulière à la fixation intelligente des plages avec des signes dollar ($) afin que vous puissiez copier la formule vers le bas et vers la droite.
Maintenant que nous avons les nombres ordinaux des mots dont nous avons besoin dans chaque liste, nous pouvons extraire les mots eux-mêmes en utilisant la fonction INDEX (INDICE) en trois colonnes distinctes :
Si vous n'avez jamais rencontré cette fonction dans votre travail auparavant, je vous conseille fortement de l'étudier au moins en diagonale - elle aide dans de nombreuses situations et n'est pas moins utile (et même plus !) VPR (VLOOKUP).
Eh bien, après cela, il ne reste plus qu'à coller les fragments résultants ligne par ligne en utilisant le symbole de concaténation (&):
… ou (si vous avez la dernière version d'Excel) avec la fonction pratique COMBINER (JOINTURE TEXTE), qui peut coller tout le contenu des cellules spécifiées à travers un caractère de séparation donné (espace) :
Méthode 2. Via Power Query
Power Query est un complément puissant pour Microsoft Excel qui effectue deux tâches principales : 1. charger des données dans Excel à partir de presque toutes les sources externes, et 2. toutes sortes de transformations de tables chargées. Power Query est déjà intégré à Excel 2016-2019, et pour Excel 2010-2013, il est installé en tant que complément séparé (vous pouvez le télécharger gratuitement sur le site Web officiel de Microsoft). Si vous n'avez pas encore commencé à utiliser Power Query dans votre travail, il est temps d'y penser, car des transformations comme celles décrites ci-dessus s'y font facilement et naturellement, en quelques mouvements seulement.
Commençons par charger les listes sources en tant que requêtes distinctes dans Power Query. Pour cela, pour chaque table, effectuez les étapes suivantes :
- Transformons les tables en tables "intelligentes" avec un bouton Formater sous forme de tableau languette Accueil (Accueil — Format sous forme de tableau) ou raccourci clavier Ctrl+T. Chaque table recevra automatiquement un nom Tableau1,2,3…, qui peut toutefois être modifié si vous le souhaitez dans l'onglet Constructeur (Conception).
- Après avoir défini la cellule active dans le tableau, appuyez sur le bouton De la table (D'après le tableau) languette Données (Date) ou sur l'onglet Requête d'alimentation (si vous l'avez installé en tant que complément séparé pour Excel 2010-2013).
- Dans la fenêtre de l'éditeur de requêtes qui s'ouvre, sélectionnez la commande Accueil — Fermer et charger — Fermer et charger dans… (Accueil — Fermer&Charger — Fermer&Charger dans..) puis l'option Créez simplement une connexion (Créer uniquement une connexion). Cela laissera la table chargée en mémoire et permettra d'y accéder à l'avenir.
Si vous faites tout correctement, la sortie dans le panneau de droite devrait être de trois requêtes en mode Connexion uniquement avec nos noms de tables :
Maintenant, faites un clic droit sur la première requête et sélectionnez la commande Lien (Référence)pour en faire une copie pouvant être mise à jour, puis ajouter une colonne supplémentaire aux données via la commande Ajout d'une colonne ž – Colonne personnalisée (Ajouter une colonne -¾ colonne personnalisée). Dans la fenêtre de saisie de la formule, saisissez le nom de la nouvelle colonne (par exemple, Fragment2) et une expression extrêmement simple en guise de formule :
=Tableau2
… c'est-à-dire, en d'autres termes, le nom de la seconde requête :
Après avoir cliqué sur OK nous verrons une nouvelle colonne, dans chaque cellule dont il y aura un tableau imbriqué avec des phrases du deuxième tableau (vous pouvez voir le contenu de ces tableaux si vous cliquez dans le fond de la cellule à côté du mot lampe de table):
Il reste à dérouler tout le contenu de ces tableaux imbriqués en utilisant le bouton à doubles flèches dans l'en-tête de la colonne résultante et en décochant Utiliser le nom de la colonne d'origine comme préfixe (Utilisez le nom de la colonne d'origine comme préfixe):
… et nous obtenons toutes les combinaisons possibles d'éléments des deux premiers ensembles :
De plus, tout est similaire. Ajoutez une autre colonne calculée avec la formule :
=Tableau3
…, puis développez à nouveau les tableaux imbriqués - et maintenant nous avons déjà toutes les options possibles pour permuter les mots des trois ensembles, respectivement :
Il reste à sélectionner les trois colonnes de gauche à droite, en maintenant Ctrl, et concaténez leur contenu séparé par des espaces à l'aide de la commande Fusionner les colonnes (Fusionner les colonnes) de l'onglet De La Carrosserie (Transformer):
Les résultats obtenus peuvent être déchargés sur la feuille avec la commande déjà familière Accueil — Fermer et charger — Fermer et charger dans… (Accueil — Fermer&Charger — Fermer&Charger dans..):
Si à l'avenir quelque chose change dans nos tables source avec des fragments, il suffira de mettre à jour la requête générée en cliquant avec le bouton droit sur la table résultante et en choisissant la commande Mettre à jour et enregistrer (Rafraîchir) ou en appuyant sur le raccourci clavier Ctrl+autre+F5.
- Qu'est-ce que Power Query, Power Pivot, Power Map et Power BI et pourquoi ont-ils besoin d'un utilisateur Excel
- Création d'un diagramme de Gantt dans Power Query
- 5 façons d'utiliser la fonction INDEX