Excel Index correspond à plusieurs critères - Exemples de formule

Le tutoriel montre comment rechercher avec plusieurs critères dans Excel en utilisant l'index et la correspondance et quelques autres façons.

Bien que Microsoft Excel offre des fonctions spéciales pour la recherche verticale et horizontale, les utilisateurs experts les remplacent normalement par une correspondance d'index, ce qui est supérieur à Vlookup et Hlookup à bien des égards. Entre autres choses, il peut rechercher deux ou plusieurs critères dans les colonnes et les rangées. Ce tutoriel explique la syntaxe et la mécanique intérieure en détail afin que vous puissiez facilement ajuster la formule pour vos besoins particuliers. Pour rendre les exemples plus faciles à suivre, vous êtes invités à télécharger notre exemple de classeur.

• Index correspond à plusieurs critères
• Index sans réseau correspondant à deux ou plusieurs critères
• Index correspond à plusieurs critères en lignes et colonnes (recherche de matrice)

Excel Index correspond à plusieurs critères

Lorsque vous travaillez avec de grandes bases de données, vous pouvez parfois vous retrouver dans une situation où vous avez besoin de trouver quelque chose mais que vous n'avez pas d'identifiant unique pour la recherche. Dans ce cas, la recherche avec plusieurs conditions est la seule solution.

Pour rechercher une valeur basée sur plusieurs critères dans des colonnes distinctes, utilisez cette formule générique:

{= Index ( return_range , match (1, ( critères1 = range1 ) * ( critères2 = range2 ) * (…), 0))}

Où:

• Return_range est la plage à partir de laquelle renvoyer une valeur.
• Critères1 , critères2 ,… sont les conditions à remplir.
• Range1 , plage2 ,… sont les plages sur lesquelles les critères correspondants doivent être testés.

Remarque importante! Il s'agit d'une formule de tableau et elle doit être complétée avec Ctrl Shift Entrée . Cela enfermera votre formule en {crochets bouclés}, qui est un signe visuel d'une formule de tableau dans Excel. N'essayez pas de taper les accolades manuellement, cela ne fonctionnera pas!

La formule est une version avancée de la correspondance d'index emblématique qui renvoie une correspondance basée sur un seul critère. Pour évaluer plusieurs critères, nous utilisons l'opération de multiplication qui fonctionne comme opérateur et dans les formules de tableau. Ci-dessous, vous trouverez un exemple réel et l'explication détaillée de la logique.

Conseil. Dans Excel 365 et 2021, vous pouvez utiliser la formule XLookup avec plusieurs critères.

Index correspond à plusieurs critères - Exemple de formule

Pour cet exemple, nous utiliserons un tableau au format soi-disant "Flat-File" avec chaque combinaison de critères distincts (élément régional-mois dans notre cas) sur sa propre rangée. Notre objectif est de récupérer le chiffre des ventes d'un certain article dans une région et un mois spécifiques.

Avec les données et les critères source dans les cellules suivantes:

• Return_range (ventes) - d2: d13
• Critères1 (région cible) - G1
• Critères2 (mois cible) - G2
• Critères3 (élément cible) - G3
• Range1 (régions) - A2: A13
• Gamme 2 (mois) - b2: b13
• Range3 (articles) - C2: C13

La formule prend la forme suivante:

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Entrez la formule, par exemple en G4, complétez-la en appuyant sur Ctrl Shift Entrée et vous obtiendrez le résultat suivant:

Comment fonctionne cette formule

La partie la plus délicate est la fonction de match, alors découvrons d'abord:

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Comme vous vous en souvenez peut-être, Match (Lookup_Value, Lookup_Array, [Match_Type]) recherche la valeur de recherche dans le tableau de recherche et renvoie la position relative de cette valeur dans le tableau.

Dans notre formule, les arguments sont les suivants:

• Lookup_value : 1
• Lookup_Array : (G1 = A2: A13) * (G2 = B2: B13) * (G3 = C2: C13)
• Match_type : 0

L'argument 1 ^st est cristallin - la fonction recherche le numéro 1. L'argument 3 ^{e de} 3 eclats est défini sur 0 une "correspondance exacte", c'est-à-dire que la formule renvoie la première valeur trouvée qui est exactement égale à la valeur de recherche.

La question est - pourquoi recherchons-nous "1"? Pour obtenir la réponse, regardons de plus près le tableau de recherche où nous comparons chaque critère avec la plage correspondante: la région cible de G1 contre toutes les régions (A2: A13), le mois cible en G2 contre tous les mois (B2: B13) et l'élément cible dans G3 contre tous les éléments (C2: C13). Un résultat intermédiaire est 3 tableaux de vrai et false lorsque True représente des valeurs qui répondent à la condition testée. Pour visualiser cela, vous pouvez sélectionner les expressions individuelles dans la formule et appuyer sur la touche F9 pour voir ce que chaque expression évalue:

L'opération de multiplication transforme respectivement les valeurs vraies et fausses en 1 et 0:

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

Et parce que la multiplication par 0 donne toujours 0, le tableau résultant n'a 1 que dans les lignes qui répondent à tous les critères:

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

Le tableau ci-dessus revient à l'argument de match de recherche . Avec Lookup_value de 1, la fonction renvoie la position relative de la ligne pour laquelle tous les critères sont vrais (ligne 3 dans notre cas). S'il y a plusieurs 1 dans le tableau, la position du premier est retournée.

Le nombre renvoyé par correspondance va directement à l'argument ROW_NUM de la fonction index (array, row_num, [colonnel_num]):

=INDEX(D2:D13, 3)

Et il donne le résultat de 115 $, ce qui est la valeur de 3 ^ectes dans le tableau D2: D13.

Formule de correspondance d'index sans réseau avec plusieurs critères

La formule Array discutée dans l'exemple précédent fonctionne bien pour les utilisateurs expérimentés. Mais si vous construisez une formule pour quelqu'un d'autre et que quelqu'un ne connaît pas les fonctions de tableau, il peut le casser par inadvertance. Par exemple, un utilisateur peut cliquer sur votre formule pour l'examiner, puis appuyer sur Entrée au lieu de Ctrl Shift Entrée . Dans de tels cas, il serait sage d'éviter les tableaux et d'utiliser une formule régulière plus à l'épreuve des balles:

Index ( return_range , match (1, index (( critères1 = range1 ) * ( critères2 = range2 ) * (..), 0, 1), 0))

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

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

Comment fonctionne cette formule

Comme la fonction d'index peut traiter les tableaux nativement, nous ajoutons un autre index pour gérer le tableau de 1 et 0 qui est créé en multipliant deux tableaux vrais / faux / faux. Le deuxième index est configuré avec un argument 0 ROW_NUM pour que la formule renvoie l'intégralité du tableau de colonne plutôt qu'une seule valeur. Comme il s'agit d'un tableau à une colonne de toute façon, nous pouvons fournir en toute sécurité 1 pour Column_num :

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

Ce tableau est transmis à la fonction de match:

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

Match trouve le numéro de ligne pour lequel tous les critères sont vrais (plus précisément, la position relative de cette ligne dans le tableau spécifié) et transmet ce numéro à l'argument Row_num du premier index:

=INDEX(D2:D13, 3)

Index correspondant à plusieurs critères en lignes et colonnes

Cet exemple montre comment effectuer la recherche en testant deux ou plusieurs critères dans les lignes et les colonnes. En fait, c'est un cas plus complexe de la soi-disant «recherche de matrice» ou de la «recherche bidirectionnelle» avec plus d'une ligne d'en-tête.

Voici la formule générique de correspondance d'index avec plusieurs critères en lignes et colonnes:

?​​​​​​

Où:

Table_Array - La carte ou la zone à rechercher à l'intérieur, c'est-à-dire toutes les valeurs de données excluant les en-têtes de colonne et de lignes.

Vlookup_value - La valeur que vous recherchez verticalement dans une colonne.

Lookup_Column - La plage de colonne pour rechercher, généralement les en-têtes de ligne.

Hlookup_value1, hlookup_value2,… - les valeurs que vous recherchez horizontalement en lignes.

Lookup_row1, lookup_row2,… - la ligne varie à rechercher, généralement les en-têtes de colonne.

Remarque importante! Pour que la formule fonctionne correctement, elle doit être saisie sous forme de formule de tableau avec Shift Ctrl Entrée .

Il s'agit d'une variation de la formule de recherche bidirectionnelle classique qui recherche une valeur à l'intersection d'une certaine ligne et colonne. La différence est que vous concaténez plusieurs valeurs et gammes HLookup pour évaluer plusieurs en-têtes de colonne. Pour mieux comprendre la logique, veuillez considérer l'exemple suivant.

Recherche de matrice avec plusieurs critères - Exemple de formule

Dans le tableau d'échantillonnage ci-dessous, nous rechercherons une valeur basée sur les en-têtes de ligne (éléments) et 2 en-têtes de colonne (régions et vendeurs). Pour rendre la formule plus facile à construire, définissons d'abord tous les critères et gammes:

• Table_array - b3: e4
• Vlookup_value (élément cible) - H1
• Lookup_Column (en-têtes de ligne: éléments) - A3: A4
• Hlookup_value1 (région cible) - H2
• Hlookup_value2 (fournisseur cible) - H3
• Lookup_row1 (en-têtes de colonne 1: régions) - b1: e1
• Lookup_row2 (en-têtes de colonne 2: vendeurs) - b2: e2

Et maintenant, fournissez les arguments dans la formule générique expliquée ci-dessus, et vous obtiendrez ce résultat:

=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

N'oubliez pas de compléter la formule en appuyant sur le raccourci Entrée en appuyant sur Ctrl , et votre recherche de matrice avec plusieurs critères sera effectuée avec succès:

Comment fonctionne cette formule

Au fur et à mesure que nous recherchons verticalement et horizontalement, nous devons fournir à la fois les numéros de ligne et de colonne pour la fonction index (array, row_num, colonnel_num).

ROW_NUM est livré par Match (H1, A3: A5, 0) qui compare l'élément cible (pommes) dans H1 contre les en-têtes de ligne dans A3: A5. Cela donne le résultat de 1 car "pommes" est le 1er élément de la plage spécifiée.

Column_num est élaboré en concaténant 2 valeurs de recherche et 2 tableaux de recherche: Match (H2 & H3, B1: E1 & B2: E2, 0))

Le facteur clé du succès est que les valeurs de recherche doivent correspondre exactement aux en-têtes de colonne et être concaténées dans le même ordre. Pour visualiser cela, sélectionnez les deux premiers arguments dans la formule Match, appuyez sur F9 , et vous verrez ce que chaque argument évalue:

MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)

Comme "NorthVendor 2" est le deuxième élément du tableau, la fonction renvoie 2.

À ce stade, notre formule de correspondance d'index bidimensionnelle longue se transforme en ce simple:

=INDEX(B3:E5, 1, 2)

Et renvoie une valeur à l'intersection de la 1ère ligne et de la 2ème colonne dans la plage B3: E5, qui est la valeur de la cellule C3.

C'est comment rechercher plusieurs critères dans Excel. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Index Excel correspond à plusieurs critères (fichier .xlsx)

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!