Obtenez plusieurs valeurs de recherche dans une seule cellule (avec et sans répétition)

Pouvons-nous rechercher et renvoyer plusieurs valeurs dans une cellule dans Excel (séparées par une virgule ou un espace) ?

Plusieurs de mes collègues et lecteurs m'ont posé cette question à plusieurs reprises.

Excel propose des formules de recherche étonnantes, telles que RECHERCHEV, INDEX/MATCH (et maintenant RECHERCHEXX), mais aucune d'entre elles n'offre un moyen de renvoyer plusieurs valeurs correspondantes. Tout cela fonctionne en identifiant la première correspondance et en la retournant.

J'ai donc fait un peu de codage VBA pour créer une fonction personnalisée (également appelée fonction définie par l'utilisateur) dans Excel.

Mettre à jour: Après qu'Excel a publié des tableaux dynamiques et des fonctions impressionnantes telles que UNIQUE et TEXTJOIN, il est désormais possible d'utiliser une formule simple et renvoie toutes les valeurs correspondantes dans une cellule (couvert dans ce tutoriel).

Dans ce tutoriel, je vais vous montrer comment procéder (si vous utilisez la dernière version d'Excel - Microsoft 365 avec toutes les nouvelles fonctions), ainsi qu'un moyen de le faire si vous utilisez des versions plus anciennes ( en utilisant VBA).

Alors, commençons!

Rechercher et renvoyer plusieurs valeurs dans une cellule (à l'aide d'une formule)

Si vous utilisez Excel 2016 ou des versions antérieures, passez à la section suivante où je montre comment procéder à l'aide de VBA.

Avec l'abonnement Microsoft 365, votre Excel dispose désormais de fonctions et de fonctionnalités beaucoup plus puissantes qui n'étaient pas présentes dans les versions précédentes (telles que XLOOKUP, Dynamic Arrays, fonctions UNIQUE/FILTER, etc.)

Ainsi, si vous utilisez Microsoft 365 (anciennement Office 365), vous pouvez utiliser les méthodes décrites dans cette section pour rechercher et renvoyer plusieurs valeurs dans une seule cellule dans Excel.

Et comme vous le verrez, c'est une formule très simple.

Ci-dessous, j'ai un ensemble de données où j'ai les noms des personnes dans la colonne A et la formation qu'elles ont suivie dans la colonne B.

Cliquez ici pour télécharger le fichier d'exemple et suivez

Pour chaque personne, je veux savoir quelle formation elle a suivie. Dans la colonne D, j'ai la liste des noms uniques (de la colonne A), et je souhaite rechercher et extraire rapidement toute la formation que chaque personne a effectuée et les obtenir dans un seul ensemble (séparé par une virgule).

Vous trouverez ci-dessous la formule qui le fera :

=TEXTJOIN(", ",VRAI,SI(D2=$A$2:$A$20,$B$2:$B$20,""))

Après avoir entré la formule dans la cellule E2, copiez-la pour toutes les cellules où vous voulez les résultats.

Comment fonctionne cette formule ?

Permettez-moi de déconstruire cette formule et d'expliquer chaque partie de la façon dont elle s'assemble pour nous donner le résultat.

Le test logique de la formule SI (D2=$A$2:$A$20) vérifie si la cellule de nom D2 est la même que celle de la plage A2:A20.

Il parcourt chaque cellule de la plage A2:A20 et vérifie si le nom est le même dans la cellule D2 ou non. si c'est le même nom, il renvoie VRAI, sinon il renvoie FAUX.

Donc, cette partie de la formule vous donnera un tableau comme indiqué ci-dessous :

{VRAI;FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX}

Étant donné que nous voulons uniquement obtenir la formation pour Bob (la valeur dans la cellule D2), nous devons obtenir toute la formation correspondante pour les cellules qui renvoient VRAI dans le tableau ci-dessus.

Cela se fait facilement en spécifiant la partie [value_if_true] de la formule SI comme plage qui a la formation. Cela garantit que si le nom de la cellule D2 correspond au nom de la plage A2:A20, la formule SI renverrait toute la formation que cette personne a suivie.

Et partout où le tableau renvoie un FALSE, nous avons spécifié la valeur [value_if_false] comme "" (vide), donc il renvoie un blanc.

La partie SI de la formule renvoie le tableau comme indiqué ci-dessous :

{“Excel”;””;””;”PowerPoint”;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””}

Là où il y a les noms de la formation que Bob a suivie et des blancs là où le nom n'était pas Bob.

Maintenant, tout ce que nous avons à faire est de combiner ces noms de formation (séparés par une virgule) et de les renvoyer dans une seule cellule.

Et cela peut être facilement fait en utilisant la nouvelle formule TEXTJOIN (disponible dans Excel2021-2022 et Excel dans Microsoft 365)

La formule TEXTJOIN prend trois arguments :

• le délimiteur - qui est ", " dans notre exemple, car je veux que la formation soit séparée par une virgule et un espace
• TRUE - qui indique à la formule TEXTJOIN d'ignorer les cellules vides et de ne combiner que celles qui ne sont pas vides
• La formule Si qui renvoie le texte qui doit être combiné

Si vous utilisez Excel dans Microsoft 365 qui possède déjà des tableaux dynamiques, vous pouvez simplement entrer la formule ci-dessus et appuyer sur Entrée. Et si vous utilisez Excel2021-2022, vous devez entrer la formule, maintenez les touches Contrôle et Maj enfoncées, puis appuyez sur Entrée.

Cliquez ici pour télécharger le fichier d'exemple et suivez

Obtenir plusieurs valeurs de recherche dans une seule cellule (sans répétition)

Étant donné que la formule UNIQUE n'est disponible que pour Excel dans Microsoft 365, vous ne pourrez pas utiliser cette méthode dans Excel2021-2022

S'il y a des répétitions dans votre ensemble de données, comme indiqué ci-dessous, vous devez modifier un peu la formule afin que vous n'obteniez qu'une liste de valeurs uniques dans une seule cellule.

Dans l'ensemble de données ci-dessus, certaines personnes ont suivi une formation plusieurs fois. Par exemple, Bob et Stan ont suivi deux fois la formation Excel et Betty deux fois la formation MS Word. Mais dans notre résultat, nous ne voulons pas que le nom d'une formation se répète.

Vous pouvez utiliser la formule ci-dessous pour ce faire :

=TEXTJOIN(", ",TRUE,UNIQUE(SI(D2=$A$2:$A$20,$B$2:$B$20,"")))

La formule ci-dessus fonctionne de la même manière, avec un changement mineur. nous avons utilisé la formule SI dans la fonction UNIQUE afin qu'en cas de répétitions dans le résultat de la formule if, la fonction UNIQUE le supprime.

Cliquez ici pour télécharger le fichier exemple

Rechercher et renvoyer plusieurs valeurs dans une cellule (à l'aide de VBA)

Si vous utilisez Excel 2016 ou des versions antérieures, vous n'aurez pas accès à la formule TEXTJOIN. Ainsi, le meilleur moyen de rechercher et d'obtenir plusieurs valeurs correspondantes dans une seule cellule consiste à utiliser une formule personnalisée que vous pouvez créer à l'aide de VBA.

Pour obtenir plusieurs valeurs de recherche dans une seule cellule, nous devons créer une fonction dans VBA (similaire à la fonction VLOOKUP) qui vérifie chaque cellule d'une colonne et si la valeur de recherche est trouvée, l'ajoute au résultat.

Voici le code VBA qui permet de faire cela :

'Code par Sumit Bansal (https://trumpexcel.com) Fonction SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells .Count If LookupRange.Cells(i, 1) = Lookupvalue Then Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & "," End If Next i SingleCellExtract = Left(Result, Len(Result) - 1) Fonction de fin

Où mettre ce code ?

1. Ouvrez un classeur et cliquez sur Alt + F11 (cela ouvre la fenêtre de l'éditeur VBA).
2. Dans cette fenêtre de l'éditeur VBA, à gauche, il y a un explorateur de projet (où tous les classeurs et feuilles de calcul sont répertoriés). Cliquez avec le bouton droit sur n'importe quel objet du classeur dans lequel vous souhaitez que ce code fonctionne et accédez à Insertion -> Module.
3. Dans la fenêtre du module (qui apparaîtra à droite), copiez et collez le code ci-dessus.
4. Maintenant, vous êtes prêt. Accédez à n'importe quelle cellule du classeur et tapez =ExtraitCellule Unique et branchez les arguments d'entrée requis (c'est-à-dire LookupValue, LookupRange, ColumnNumber).

Comment fonctionne cette formule ?

Cette fonction fonctionne de manière similaire à la fonction RECHERCHEV.

Il prend 3 arguments en entrée :

1. Valeur de recherche - Une chaîne que nous devons rechercher dans une plage de cellules.
2. Plage de recherche - Un tableau de cellules à partir duquel nous devons récupérer les données ($B3:$C18 dans ce cas).
3. Numéro de colonne - Il s'agit du numéro de colonne de la table/tableau à partir de laquelle la valeur correspondante doit être renvoyée (2 dans ce cas).

Lorsque vous utilisez cette formule, elle vérifie chaque cellule de la colonne la plus à gauche de la plage de recherche et lorsqu'il trouve une correspondance, il ajoute au résultat dans la cellule dans laquelle vous avez utilisé la formule.

Rappelles toi: Enregistrez le classeur en tant que classeur prenant en charge les macros (.xlsm ou .xls) pour réutiliser cette formule. En outre, cette fonction serait disponible uniquement dans ce classeur et pas dans tous les classeurs.

Cliquez ici pour télécharger le fichier exemple

Apprenez à automatiser les tâches répétitives ennuyeuses avec VBA dans Excel. Rejoins Cours Excel VBA

Obtenir plusieurs valeurs de recherche dans une seule cellule (sans répétition)

Il est possible que vous ayez des répétitions dans les données.

Si vous utilisez le code utilisé ci-dessus, il vous donnera également des répétitions dans le résultat.

Si vous voulez obtenir le résultat où il n'y a pas de répétitions, vous devez modifier un peu le code.

Voici le code VBA qui vous donnera plusieurs valeurs de recherche dans une seule cellule sans aucune répétition.

'Code par Sumit Bansal (https://trumpexcel.com) Fonction MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells .Count If LookupRange.Cells(i, 1) = Lookupvalue Then For J = 1 To i - 1 If LookupRange.Cells(J, 1) = Lookupvalue Then If LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Then GoTo Skip End If End If Next J Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left(Result, Len(Result) - 1) End Une fonction

Une fois que vous aurez placé ce code dans l'éditeur VB (comme indiqué ci-dessus dans le tutoriel), vous pourrez utiliser le Recherche MultipleAucunRept une fonction.

Voici un aperçu du résultat que vous obtiendrez avec cela Recherche MultipleAucunRept une fonction.

Cliquez ici pour télécharger le fichier exemple

Dans ce didacticiel, j'ai expliqué comment utiliser des formules et VBA dans Excel pour rechercher et renvoyer plusieurs valeurs de recherche dans une cellule dans Excel.

Bien que cela puisse être facilement fait avec une formule simple si vous utilisez Excel dans un abonnement Microsoft 365, si vous utilisez des versions antérieures et n'avez pas accès à des fonctions telles que TEXTJOIN, vous pouvez toujours le faire en utilisant VBA en créant votre propre fonction personnalisée.