Passer au contenu principal

Comment rechercher une valeur renvoyant plusieurs valeurs correspondantes dans Excel?

Ce didacticiel parle de rechercher une valeur renvoyant plusieurs valeurs correspondantes dans Excel, comme illustré ci-dessous les captures d'écran:

doc-lookup-value-1

La valeur de recherche renvoie plusieurs valeurs correspondantes avec une formule matricielle

La valeur de recherche renvoie plusieurs valeurs correspondantes avec le filtre

La valeur de recherche renvoie plusieurs valeurs correspondantes avec la fonction définie


flèche bleue bulle droite La valeur de recherche renvoie plusieurs valeurs correspondantes avec une formule matricielle

Voici une formule de tableau long qui peut aider à rechercher une valeur et renvoyer plusieurs valeurs correspondantes.

1. Entrez la valeur que vous souhaitez rechercher dans une cellule vide. Voir la capture d'écran:

doc-lookup-value-2

2. Dans la cellule adjacente, saisissez cette formule =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)),"",
INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2))
dedans, et appuyez sur Maj + Ctrl + Entrée touches ensemble, puis faites glisser Remplissage automatique pour remplir les cellules jusqu'à ce que la première cellule vide sorte. Voir la capture d'écran:

doc-lookup-value-3

Remarque: dans la formule ci-dessus, $ A $ 1: $ B $ 7 indique les données de plage, $ A $ 1: $ A $ 7 représente la plage de colonnes dans laquelle votre recherche de la valeur donnée, $ D $ 4 indique la cellule dans laquelle vous tapez la recherche valeur à l'étape 1, 2 indique la recherche des valeurs correspondantes dans la deuxième colonne.


flèche bleue bulle droite La valeur de recherche renvoie plusieurs valeurs correspondantes avec le filtre

Dans Excel, vous pouvez également utiliser la fonction Filtre pour résoudre ce problème.

1. Sélectionnez la plage de colonnes dont vous souhaitez rechercher la valeur, puis cliquez sur Données > Filtre. Voir la capture d'écran:

doc-lookup-value-4

2. Cliquez ensuite sur le bouton fléché dans la première cellule de la plage sélectionnée et cochez la valeur que vous souhaitez rechercher uniquement dans la liste déroulante. Voir la capture d'écran:

doc-lookup-value-5

3. Cliquez OK, maintenant vous ne voyez que la valeur de recherche et ses valeurs correspondantes sont filtrées.

doc-lookup-value-6


flèche bleue bulle droite La valeur de recherche renvoie plusieurs valeurs correspondantes avec la fonction définie

Si vous êtes intéressé par la fonction définie, vous pouvez également résoudre le problème avec la fonction définie.

1. presse Alt + F11 clés pour ouvrir le Microsoft Visual Basic pour les applications fenêtre.

2. Cliquez Module > insérer pour insérer un Module et copiez le VBA ci-dessous dans la fenêtre.

VBA: la valeur de recherche renvoie plusieurs valeurs correspondantes.

Function MyVlookup(pWorkRng As Range, pRng As Range, pColumnIndex As Integer, Optional pType As String = "v")
'Updateby20140827
Dim xRow As Single
Dim xCol As Single
Dim arr() As Variant
ReDim arr(0)
For i = 1 To pRng.Rows.Count
    If pWorkRng = pRng.Cells(i, 1) Then
        arr(UBound(arr)) = pRng.Cells(i, pColumnIndex)
        ReDim Preserve arr(UBound(arr) + 1)
    End If
Next
If pType = "h" Then
    xCol = Range(Application.Caller.Address).Columns.Count
    For i = UBound(arr) To xCol
        arr(UBound(arr)) = ""
        ReDim Preserve arr(UBound(arr) + 1)
    Next
    ReDim Preserve arr(UBound(arr) - 1)
    MyVlookup = arr
Else
xRow = Range(Application.Caller.Address).Rows.Count
For i = UBound(arr) To xRow
    arr(UBound(arr)) = ""
    ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = Application.WorksheetFunction.Transpose(arr)
End If
End Function

3. Fermez la fenêtre et tapez cette formule dans une cellule = MyVlookup (A10, $ A $ 2: $ B $ 7,2) (A10 indique la valeur de recherche, $ A $ 2: $ B $ 7 indique la plage de données, 2 indique le numéro d'index de la colonne). Et appuyez sur Maj + Ctrl + Entrée clés. Faites ensuite glisser la poignée de recopie vers le bas des cellules, placez le curseur dans le Barre de formuleet appuyez sur Maj + Ctrl + Entrée nouveau.

Conseil : Si vous souhaitez renvoyer les valeurs dans des cellules horizontales, vous pouvez taper cette formule = MyVlookup (A10, $ A $ 2: $ B $ 7, 2, "h").

Meilleurs outils de productivité bureautique

Caractéristiques populaires: Rechercher, mettre en évidence ou identifier les doublons   |  Supprimer les lignes vides   |  Combinez des colonnes ou des cellules sans perdre de données   |   Tour sans formule 
Super recherche: VSearchup à critères multiples    VSearch à valeurs multiples  |   Recherche virtuelle sur plusieurs feuilles   |   Recherche floue ....
Liste déroulante avancée: Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple ....
Gestionnaire de colonnes: Ajouter un nombre spécifique de colonnes  |  Déplacer les colonnes  |  Basculer l'état de visibilité des colonnes masquées  |  Comparer les plages et les colonnes 
Caractéristiques en vedette: Mise au point de la grille   |  Voir Design   |   Grande barre de formule    Gestionnaire de classeurs et de feuilles   |  Centre de ressources (Texte automatique)   |  Sélecteur de date   |  Combiner des feuilles de travail   |  Crypter/déchiffrer les cellules    Envoyer des e-mails par liste   |  Super filtre   |   Filtre spécial (filtre gras/italique/barré...) ...
Les 15 meilleurs ensembles d'outils12 Texte Outils (Ajouter du texte, Supprimer les caractères, ...)   |   50+ Graphique Types (Diagramme de Gantt, ...)   |   40+ Pratique Formules (Calculer l'âge en fonction de l'anniversaire, ...)   |   19 Insertion Outils (Insérer le code QR, Insérer une image à partir du chemin, ...)   |   12 Conversion Outils (Nombres en mots, Conversion des devises, ...)   |   7 Fusionner et fractionner Outils (Lignes de combinaison avancées, Cellules divisés, ...)   |   ... et plus

Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps.  Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

onglet kte 201905


Office Tab apporte une interface à onglets à Office et facilite grandement 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é de 50% et réduit des centaines de clics de souris chaque jour!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
IF($A$1:$A$7=$D$4 This logical formula is always outputting "FALSE" or 0, so why is it like that, or did I miss something?

Pls explain

Steve
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations