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:
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
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:
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:
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.
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:
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:
3. Cliquez OK, maintenant vous ne voyez que la valeur de recherche et ses valeurs correspondantes sont filtrées.
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
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...
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!
