Note: The other languages of the website are Google-translated. Back to English
English English
  • Documents
  • Excel
  • Comment vlookup et concaténer plusieurs valeurs correspondantes dans Excel?

Comment vlookup et concaténer plusieurs valeurs correspondantes dans Excel?

Comme nous le savons tous, le Vlookup La fonction dans Excel peut nous aider à rechercher une valeur et à renvoyer les données correspondantes dans une autre colonne, mais en général, elle ne peut obtenir la première valeur relative que s'il existe plusieurs données correspondantes. Dans cet article, je vais expliquer comment vlookup et concaténer plusieurs valeurs correspondantes dans une seule cellule ou une liste verticale.

Vlookup et renvoyer plusieurs valeurs correspondantes verticalement avec la formule

Vlookup et concaténer plusieurs valeurs correspondantes dans une cellule avec la fonction définie par l'utilisateur

Vlookup et concaténer plusieurs valeurs correspondantes dans une cellule avec Kutools for Excel


En supposant que je dispose de la plage de données suivante, pour obtenir toutes les valeurs correspondantes en fonction d'une valeur spécifique verticalement, comme illustré ci-dessous, vous pouvez appliquer une formule matricielle.

doc vlookup concaténer 1

1. Entrez cette formule: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") dans une cellule vide où vous souhaitez placer le résultat, par exemple, E2, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir la base de valeur relative sur un critère spécifique, voir capture d'écran:

doc vlookup concaténer 2

Notes: Dans la formule ci-dessus:

A1: A16 est la plage de colonnes qui contient la valeur spécifique que vous souhaitez rechercher;

D2 indique la valeur spécifique que vous souhaitez vlookup;

B1: B16 est la plage de colonnes à partir de laquelle vous souhaitez renvoyer les données correspondantes;

$ 1: $ 16 indique la référence des lignes dans la plage.

2. Sélectionnez ensuite la cellule E2 et faites glisser la poignée de remplissage vers les cellules jusqu'à ce que vous obteniez des cellules vides, et toutes les valeurs correspondantes sont répertoriées dans la colonne comme illustré ci-dessous:

doc vlookup concaténer 3


Au lieu d'obtenir les valeurs relatives verticalement, parfois, vous voulez les valeurs correspondantes dans une cellule et les concaténer avec un séparateur spécifique. Dans ce cas, la fonction définie par l'utilisateur suivante peut vous rendre service.

1. Maintenez la touche 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: Vlookup et concaténer plusieurs valeurs correspondantes dans une cellule

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Ensuite, enregistrez et fermez ce code, revenez à la feuille de calcul et entrez cette formule: = cusvlookup (D2, A1: B16,2) dans une cellule vide où vous voulez mettre le résultat, et appuyez sur Entrer clé, toutes les valeurs correspondantes basées sur des données spécifiques ont été renvoyées dans une cellule avec un séparateur d'espace, voir capture d'écran:

doc vlookup concaténer 4

Notes: Dans la formule ci-dessus: D2 indique les valeurs de cellule que vous souhaitez rechercher, A1 : B16 est la plage de données dans laquelle vous souhaitez récupérer les données, le nombre 2 est le numéro de colonne à partir duquel la valeur correspondante doit être renvoyée, vous pouvez modifier ces références selon vos besoins.


Si vous avez Kutools pour Excel, Avec son Lignes de combinaison avancées fonction, vous pouvez rapidement terminer ce travail avec facilité. Cette fonctionnalité peut vous aider à combiner toutes les valeurs correspondantes avec des délimiteurs spécifiques basés sur les mêmes données dans une autre colonne.

Kutools pour Excel : avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation en 30 jours.

Après l'installation de Kutools pour Excel, procédez comme suit:

1. Sélectionnez la plage de données pour laquelle vous souhaitez obtenir les valeurs correspondantes en fonction des données spécifiques.

2. Puis clique Kutools > Fusionner et fractionner > Lignes de combinaison avancées, voir capture d'écran:

3. Dans le Lignes de combinaison avancées boîte de dialogue, cliquez sur le nom de la colonne sur laquelle vous souhaitez combiner, puis cliquez sur Clé primaire bouton, voir capture d'écran:

doc vlookup concaténer 6

4. Cliquez ensuite sur un autre nom de colonne pour lequel vous souhaitez renvoyer les valeurs correspondantes, puis cliquez sur Combiner pour choisir un séparateur pour séparer les valeurs combinées, voir capture d'écran:

doc vlookup concaténer 7

5. Et puis cliquez Ok bouton, toutes les valeurs correspondantes basées sur les mêmes valeurs ont été combinées avec un séparateur spécifique, voir les captures d'écran:

doc vlookup concaténer 8 2 doc vlookup concaténer 9

 Téléchargez et essayez gratuitement Kutools for Excel Now!


Kutools pour Excel: avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation dans 30 jours. Téléchargez et essayez gratuitement maintenant!

Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité en
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 que
    300
    Caractéristiques puissantes
    . Prend en charge Office/Excel
    2007-2019 et 365
    . Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes
    30
    -jour d'essai gratuit. 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é en
    50 %
    , et réduit chaque jour des centaines de clics de souris !
bas de cabine
Commentaires (16)
Pas encore de notes. Soyez le premier à évaluer!
Ce commentaire a été minimisé par le modérateur sur le site
Comment obtenir le résultat. S'il vous plaît aider. données données1 résultat a 1 a1 b 2 a2 c b1 b2 c1 c2
Ce commentaire a été minimisé par le modérateur sur le site
Lors de l'utilisation de cusvlookup, existe-t-il un moyen d'ajouter également le nom de famille avec une virgule entre les deux qui pourrait apparaître dans la colonne C
Ce commentaire a été minimisé par le modérateur sur le site
J'ai adoré la fonction pour Excel 2013 mais je l'ai légèrement modifiée pour changer le caractère de séparation en ";" au lieu de " " puis supprimez le préfixe ";" à partir des valeurs concaténées Les résultats correspondant aux valeurs dans mon exemple auraient ;result01 ou ;result01;result02 . Ajout du supplément If Left(xResult, 1) = ";" pour supprimer tout ";" supplémentaire au début de la chaîne s'il s'agit du 1er caractère. Je suis sûr qu'il y a une façon plus propre de le faire, mais cela a fonctionné pour moi. :) Fonction CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" For Each rng In pWorkRng If rng = pValue Then xResult = xResult & ";" & rng.Offset(0, pIndex - 1) Si Gauche(xRésultat, 1) = ";" Alors xResult = MID(xResult,2,255) End If End If Next CusVlookup = xResult End Function
Ce commentaire a été minimisé par le modérateur sur le site
Faire si la condition pour le résultat si vide.

Fonction CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'mise à jour par Extendoffice 20151118
Plage Dim x As
Dim result As String
résultat = ""
Pour chaque x dans la plage de recherche
Si x = lookupval Alors
Si non résultat = "" alors
résultat = résultat & " " & x.Offset(0, indexcol - 1)
autre
result = x.Offset(0, indexcol - 1)
Si fin
Suivant x
CusVlookup = résultat
Fonction de fin
Ce commentaire a été minimisé par le modérateur sur le site
C'est incroyable mais je cherche autre chose, j'ai une table avec RollNo StudentName sub1, sub2, sub3 ... Résultat total, lorsque j'entre Rollnumber, cela devrait donner un résultat comme "SName Sub1 64, sub2 78, ... Total 389, Résultat pass", est-ce possible
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen de supprimer les valeurs en double dans la concaténation ?
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour Jacob,
Peut-être que l'article suivant peut vous aider à résoudre votre problème.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

S'il vous plaît essayez, j'espère que cela peut vous aider!
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen de répertorier les valeurs en double une seule fois, en utilisant le code vba et la formule ci-dessus ? Je ne sais pas où mettre l'instruction countif> 1 dans la barre de formule ou dans le vba lui-même. S'il vous plaît aider
Ce commentaire a été minimisé par le modérateur sur le site
vous pouvez ajouter deux conditions supplémentaires pour ignorer les cellules vides et pour ignorer les doublons :For i = 1 To CriteriaRange.Count
Si CriteriaRange.Cells(i).Value = Condition Alors
Si ConcatenateRange.Cells(i).Value <> "" Alors 'SAUTER LES BANQUES
Si InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Alors 'IGNORER SI TROUVÉ DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
Si fin
Si fin
Si fin
Suivant i
Ce commentaire a été minimisé par le modérateur sur le site
Je dois dire que j'essaie d'obtenir une formule pour combiner plusieurs valeurs et les renvoyer dans une seule cellule depuis 2 jours maintenant. Ce "How To" m'a sauvé !! Merci beaucoup! Je ne l'aurais jamais eu sans votre module !
J'ai quand même 2 questions. J'ai le délimiteur comme une virgule au lieu d'un espace et à cause de cela, il commence par une virgule. Existe-t-il un moyen d'empêcher la virgule de début mais de conserver le reste?
Ma deuxième question est; Lorsque j'utilise la poignée de recopie, cela modifie les valeurs de plage ainsi que la valeur de cellule que je souhaite rechercher. Je veux qu'il continue à changer le numéro de cellule que je veux rechercher mais garde les mêmes valeurs de plage. Comment puis-je faire en sorte que cela se produise ?

Je vous remercie beaucoup pour votre aide!!
Ce commentaire a été minimisé par le modérateur sur le site
Le cusVlookup a très bien fonctionné pour moi. Une autre façon d'avoir un séparateur différent consiste à envelopper dans deux fonctions de substitution. Le premier (de l'intérieur vers l'extérieur) remplace le premier espace sans espace, le second remplace tous les autres espaces par un "/" dans le mien. Pourrait utiliser "," si vous voulez des virgules.
=SUBSTITUER(SUBSTITUER(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

De plus, si votre valeur de recherche n'est pas la première colonne, vous pouvez utiliser 0 ou des nombres négatifs pour accéder à la colonne de gauche.
=SUBSTITUER(SUBSTITUER(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
Ce commentaire a été minimisé par le modérateur sur le site
Salut Jeff,
Merci pour votre partage, vous devez être un homme chaleureux.
Ce commentaire a été minimisé par le modérateur sur le site
Cela fonctionne très bien pour moi - y a-t-il un moyen de le changer pour qu'il vérifie si la cellule contient plutôt qu'une correspondance complète? Fondamentalement, j'ai une liste de tâches où:
Colonne A : Dépendances (ex. 10003 10004 10008)
Colonne B : Référence de la tâche (par exemple, 10001)
Colonne C : Tâches dépendantes (la colonne du résultat de la formule) - où il rechercherait la référence de tâche pour voir quelles lignes la contiennent dans la colonne A, puis listerait la référence de tâche de ces tâches.

Par exemple:

Ligne | Colonne A | Colonne B | Colonne C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
Ce commentaire a été minimisé par le modérateur sur le site
vous voudriez utiliser la fonction Instr() qui vérifiera quelque chose dans une chaîne de texte dans une cellule. Vous pouvez également utiliser Left() et Right() si vous recherchez les détails de début ou de fin.
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen d'obtenir le "nom" unique pour "class1"
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, sym-john,
Peut-être que l'article ci-dessous peut résoudre votre problème, veuillez le consulter :
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
Il n'y a pas encore de commentaires postés ici
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0   Personnages
Emplacements suggérés

Nous suivre

Copyright © 2009 - www.extendoffice.com. | Tous les droits sont réservés. Alimenté par ExtendOffice. | | Plan du site
Microsoft et le logo Office sont des marques commerciales ou des marques déposées de Microsoft Corporation aux États-Unis et / ou dans d'autres pays.
Protégé par Sectigo SSL