Match d'index Match dans Excel pour la recherche bidimensionnelle

Le tutoriel présente quelques formules différentes pour effectuer une recherche bidimensionnelle dans Excel. Regardez simplement les alternatives et choisissez votre préféré :)

Lorsque vous recherchez quelque chose dans vos feuilles de calcul Excel, la plupart du temps, vous recherchez verticalement dans des colonnes ou horizontalement en rangées. Mais parfois, vous devez regarder à travers les lignes et les colonnes. En d'autres termes, vous visez à trouver une valeur à l'intersection d'une certaine ligne et colonne. C'est ce qu'on appelle la recherche matricielle (alias la recherche bidimensionnelle ou bidimensionnelle ), et ce tutoriel montre comment le faire de 4 manières différentes.

• Correspond à la correspondance de la correspondance à la recherche dans les lignes et les colonnes
• Recherche bidirectionnelle à l'aide de Vlookup
• Recherche à 2 voies avec xlookup
• Recherche bidimensionnelle avec sumproduct
• Recherche matricielle avec des plages nommées (intersection explicite)

Excel index Match Match Formula

La façon la plus populaire de faire une recherche bidirectionnelle dans Excel consiste à utiliser l'index Match Match. Il s'agit d'une variation de la formule Classic Index Match à laquelle vous ajoutez une fonction de correspondance de plus afin d'obtenir à la fois les numéros de ligne et de colonne:

Index ( data_array , match ( vlookup_value , lookup_column_range , 0), correspond ( valeur hlookup , lookup_row_range , 0))

Par exemple, faisons une formule pour tirer une population d'un certain animal au cours d'une année donnée du tableau ci-dessous. Pour commencer, nous définissons tous les arguments:

• Data_array - B2: E4 (cellules de données, sans compter les en-têtes de lignes et de colonne)
• Vlookup_value - H1 (animal cible)
• Lookup_Column_Range - A2: A4 (en-têtes de ligne: noms d'animaux) - A3: A4
• Hlookup_value - h2 (année cible)
• Lookup_Row_Range - B1: E1 (en-têtes de colonne: années)

Mettez tous les arguments ensemble et vous obtiendrez cette formule pour une recherche bidirectionnelle:

=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))

Si vous devez effectuer une recherche bidirectionnelle avec plus de deux critères, jetez un œil à cet article: Index Mortez avec plusieurs critères en lignes et colonnes.

Comment fonctionne cette formule

Bien qu'il puisse sembler un peu complexe à première vue, la logique de la formule est vraiment simple et facile à comprendre. La fonction d'index récupère une valeur à partir du tableau de données en fonction des numéros de ligne et de colonne, et deux fonctions de correspondance fournissent ces nombres:

INDEX(B2:E4, row_num, column_num)

Ici, nous tirons parti de la capacité de Match (Lookup_value, Lookup_Array, [Match_Type]) pour renvoyer une position relative de Lookup_Value dans Lookup_Array .

Donc, pour obtenir le numéro de ligne, nous recherchons l'animal d'intérêt (H1) sur les en-têtes de ligne (A2: A4):

MATCH(H1, A2:A4, 0)

Pour obtenir le numéro de colonne, nous recherchons l'année cible (H2) à travers les en-têtes de colonne (B1: E1):

MATCH(H2, B1:E1, 0)

Dans les deux cas, nous recherchons une correspondance exacte en définissant le 3ème argument sur 0.

Dans cet exemple, le premier match renvoie 2 parce que notre valeur Vlookup (ours polaire) est trouvée dans A3, qui est la 2 ^ème cellule dans A2: A4. Le deuxième match renvoie 3 car la valeur HLookup (2000) est trouvée dans D1, qui est la cellule 3 ^RD dans B1: E1.

Compte tenu de ce qui précède, la formule se réduit à:

INDEX(B2:E4, 2, 3)

Et renvoyez une valeur à l'intersection de la 2 ^ème ligne et de la colonne 3 ^RD dans le tableau de données B2: E4, qui est une valeur dans la cellule D3.

Formule Vlookup et Match pour la recherche bidirectionnelle

Une autre façon de faire une recherche bidimensionnelle dans Excel est d'utiliser une combinaison de fonctions VLookup et Match:

Vlookup ( vlookup_value , table_array , match ( hlookup_value , lookup_row_range , 0), false)

Pour notre tableau d'échantillons, la formule prend la forme suivante:

=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)

Où:

• Table_Array - A2: E4 (cellules de données, y compris les en-têtes de ligne)
• Vlookup_value - H1 (animal cible)
• Hlookup_value - h2 (année cible)
• Lookup_row_range - a1: e1 (en-têtes de colonne: années)

Comment fonctionne cette formule

Le noyau de la formule est la fonction VLookup configurée pour la correspondance exacte (le dernier argument défini sur false), qui recherche la valeur de recherche (H1) dans la première colonne du tableau de table (A2: E4) et renvoie une valeur d'une autre colonne dans la même ligne. Pour déterminer la colonne de renvoyer une valeur, vous utilisez la fonction de correspondance qui est également configurée pour la correspondance exacte (le dernier argument défini sur 0):

MATCH(H2, A1:E1, 0)

Faites correspondre la valeur de la valeur en H2 à travers les en-têtes de colonne (A1: E1) et renvoie la position relative de la cellule trouvée. Dans notre cas, l'année cible (2010) se trouve dans E1, qui est 5 ^e dans le tableau de recherche. Ainsi, le numéro 5 va à l'argument Col_index_num de VLOOKUP:

VLOOKUP(H1, A2:E4, 5, FALSE)

Vlookup le prend à partir de là, trouve une correspondance exacte pour sa valeur de recherche dans A2 et renvoie une valeur de la 5 ^e colonne de la même ligne, qui est la cellule E2.

Remarque importante! Pour que la formule fonctionne correctement, table_array (A2: E4) de VLOOKUP et Lookup_Array de Match (A1: E1) doit avoir le même nombre de colonnes, sinon le numéro transmis par correspondance à col_index_num sera incorrect (ne correspondra pas à la position de la colonne dans Table_Array ).

Fonction xlookup à regarder en lignes et colonnes

Récemment, Microsoft a introduit une autre fonction dans Excel qui vise à remplacer toutes les fonctions de recherche existantes telles que VLookup, Hlookup et Index Match. Entre autres choses, Xlookup peut examiner l'intersection d'une ligne et d'une colonne spécifiques:

XLookup ( vlookup_value , vlookup_column_range , xlookup ( hlookup_value , hlookup_row_range , data_array )))

Pour notre échantillon de données, la formule se déroule comme suit:

=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))

Note. La fonction XLookup est uniquement disponible dans Excel pour Microsoft 365, Excel 2021 et Excel pour le Web.

Comment fonctionne cette formule

La formule utilise la capacité de xlookup pour renvoyer une ligne ou une colonne entière. La fonction intérieure recherche l'année cible dans la ligne d'en-tête et renvoie toutes les valeurs de cette année (dans cet exemple, pour l'année 1980). Ces valeurs vont à l'argument return_array de l'extérieur xlookup:

XLOOKUP(H1, A2:A4, {22000;25000;700}))

La fonction extérieure xlookup recherche l'animal cible à travers les en-têtes de colonne et renvoie la valeur dans la même position à partir du retour_array.

Formule de sumproduct pour la recherche bidirectionnelle

La fonction de sumproduct est comme un couteau suisse dans Excel - il peut faire tant de choses au-delà de son objectif désigné, en particulier lorsqu'il s'agit d'évaluer plusieurs critères.

Pour rechercher deux critères, en lignes et colonnes, utilisez cette formule générique:

Sumproduct ( vlookup_column_range = vlookup_value ) * ( hlookup_row_range = hlookup_value ), data_array )

Pour effectuer une recherche bidirectionnelle dans notre ensemble de données, la formule se déroule comme suit:

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)

La syntaxe ci-dessous fonctionnera également:

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)

Comment fonctionne cette formule

Au cœur de la formule, nous comparons deux valeurs de recherche avec les en-têtes de ligne et de colonne (l'animal cible en H1 contre tous les noms d'animaux en A2: A4 et l'année cible dans H2 contre toutes les années en B1: E1):

(A2:A4=H1) * (B1:E1=H2)

Il en résulte 2 tableaux de valeurs vraies et fausses, où les TRUE représentent les correspondances:

{FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}

L'opération de multiplication contraint les valeurs vraies et fausses en 1 et 0 et produit un tableau bidimensionnel de 4 colonnes et 3 lignes (les lignes sont séparées par semi-colons et chaque colonne de données par une virgule):

{0,0,0,0;0,0,0,0;0,1,0,0}

Les fonctions de sumproduct multiplie les éléments du tableau ci-dessus par les éléments de B2: E4 dans les mêmes positions:

{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}

Et parce que la multiplication par zéro donne zéro, seul l'élément correspondant à 1 dans le premier tableau survit:

SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})

Enfin, le sumproduct additionne les éléments du tableau résultant et renvoie une valeur de 2000.

Note. Si votre table a plus d'une ligne ou / et des en-têtes de colonne avec le même nom, le tableau final contiendra plus d'un numéro autre que zéro, et tous ces chiffres seront additionnés. En conséquence, vous obtiendrez une somme de valeurs qui répondent aux deux critères. C'est ce qui rend la formule de sumproduct différente de l'index Match Match et Vlookup, qui renvoie le premier match trouvé.

Recherche matricielle avec des plages nommées (intersection explicite)

Un autre moyen incroyablement simple de faire une recherche matricielle dans Excel est d'utiliser des plages nommées. Voici comment:

Partie 1: Nom Colonnes et rangées

Le moyen le plus rapide de nommer chaque ligne et chaque colonne de votre table est la suivante:

1. Sélectionnez l'ensemble du tableau (A1: E4 dans notre cas).
2. Dans l'onglet Formules , dans le groupe de noms définis , cliquez sur Créer à partir de la sélection ou appuyez sur le raccourci Ctrl Shift F3 .
3. Dans la boîte de dialogue Créer des noms à partir de sélection , sélectionnez la ligne supérieure et la colonne de gauche, puis cliquez sur OK.

   

Cela crée automatiquement des noms basés sur les en-têtes de ligne et de colonne. Cependant, il y a quelques mises en garde:

• Si vos en-têtes de colonne et / ou de lignes sont des numéros ou contiennent des caractères spécifiques qui ne sont pas autorisés dans les noms Excel, les noms de ces colonnes et lignes ne seront pas créés. Pour voir une liste des noms créés, ouvrez le gestionnaire de noms ( CTRL F3 ). Si certains noms sont manquants, définissez-les manuellement comme expliqué dans la façon de nommer une plage d'Excel.
• Si certains de vos en-têtes de ligne ou de colonne contiennent des espaces, les espaces seront remplacés par des soulignements, par exemple, Polar_Bear .

Pour notre tableau d'échantillonnage, Excel n'a créé automatiquement que les noms de lignes. Les noms de colonne doivent être créés manuellement car les en-têtes de colonne sont des numéros. Pour surmonter cela, vous pouvez simplement préfacer les nombres avec des soulignements, comme _1990 .

En conséquence, nous avons les plages nommées suivantes:

Partie 2: Faire une formule de recherche de matrice

Pour tirer une valeur à l'intersection d'une ligne et d'une colonne données, tapez simplement l'une des formules génériques suivantes dans une cellule vide:

= ROW_NAME COLUMN_NAME

Ou vice versa:

= Column_name ROW_NAME

Par exemple, pour obtenir la population de baleines bleues en 1990, la formule est aussi simple que:

=Blue_whale _1990

Si quelqu'un a besoin d'instructions plus détaillées, les étapes suivantes vous guideront tout au long du processus:

1. Dans une cellule où vous voulez que le résultat apparaisse, tapez le signe d'égalité (=).
2. Commencez à taper le nom de la ligne cible, par exemple, Blue_Whale . Après avoir tapé quelques caractères, Excel affichera tous les noms existants qui correspondent à votre entrée. Double-cliquez sur le nom souhaité pour y entrer dans votre formule:

   

3. Après le nom de la ligne, tapez un espace , qui fonctionne comme l' opérateur d'intersection dans ce cas.
4. Entrez le nom de la colonne cible ( _1990 dans notre cas).

   

5. Dès que les noms de ligne et de colonne sont entrés, Excel mettra en surbrillance la ligne et la colonne correspondantes dans votre table, et vous appuyez sur Entrée pour terminer la formule:

   

Votre recherche de matrice est terminée, et la capture d'écran ci-dessous montre le résultat:

C'est comment rechercher dans les rangées et les colonnes dans Excel. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Téléchargements disponibles

Exemple de classeur de recherche bidimensionnelle

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!