Ce tutoriel montre comment utiliser les fonctions d'index et de correspondance dans Excel et pourquoi il est meilleur que VLookup.

Dans les articles récents, nous nous effor?ons d'expliquer les bases des fonctions Vlookup aux débutants et de fournir des exemples plus complexes de formules VLookup pour les utilisateurs avancés. Maintenant, je vais essayer non seulement de vous persuader de ne pas utiliser VLookup, mais aussi de montrer au moins une alternative à la recherche verticale dans Excel.

"Pourquoi ai-je besoin de ?a?" Vous pourriez demander. étant donné que VLookup a de nombreuses limites, cela peut vous empêcher d'obtenir le résultat souhaité dans de nombreux cas. D'un autre c?té, la combinaison d'index Match est plus flexible et a de nombreuses fonctionnalités excellentes qui le rendent supérieur à Vlookup à bien des égards.

étant donné que le but de ce tutoriel est de montrer comment effectuer des recherches verticales alternatives dans Excel en combinant les fonctions d'index et de correspondance, nous ne discuterons pas de leur syntaxe et de leur utilisation en détail. Nous ne couvrirons que le minimum requis pour comprendre le concept global, puis plongerons dans des exemples de formule qui révèlent tous les avantages de l'utilisation de l'index Match au lieu de Vlookup.

Fonctions d'index - Syntaxe et utilisation

La fonction d'index d'Excel renvoie une valeur du tableau en fonction des numéros de ligne et de colonne que vous avez spécifiées. La syntaxe de la fonction d'index est très simple:

Index (array, row_num, [colonnel_num]) Ce qui suit est une explication simple de chaque paramètre:

• Array - Plage de cellules à partir desquelles vous souhaitez renvoyer la valeur.
• ROW_NUM - Le numéro de ligne dans le tableau à partir duquel vous souhaitez renvoyer la valeur. En cas d'omission, Column_num est requis.
• Column_num - le numéro de colonne dans le tableau à partir duquel vous souhaitez renvoyer la valeur. S'il est omis, ROW_NUM est requis.

Pour plus d'informations, voir les fonctions Index Excel.

Voici l'exemple le plus simple de la formule d'index:

=INDEX(A1:C10,2,3)

Cette formule recherche les cellules A1 à C10 et renvoie la valeur cellulaire de la ligne 2 et de la colonne 3, c'est-à-dire la cellule C2.

Très simple, non? Cependant, lorsque vous traitez des données réelles, vous ne connaissez presque jamais les numéros de ligne et de colonne que vous souhaitez, c'est là que la fonction de correspondance est utile.

Fonction de correspondance - syntaxe et utilisation

La fonction de correspondance d'Excel recherche une valeur dans une plage de cellules et renvoie la position relative de la valeur dans la plage.

La syntaxe de la fonction de correspondance est la suivante:

Match (lookup_value, lookup_array, [match_type]) - lookup_value - le nombre ou la valeur de texte que vous recherchez.

• Lookup_Array - Plage de cellules recherchées.
• match_type - spécifie de renvoyer une correspondance exacte ou la correspondance la plus proche:
  • 1 ou omettre (par défaut) - correspondance approximative (suivant plus petit).
  • 0 - correspondance exacte. Dans la combinaison d'index / correspondance, vous avez presque toujours besoin d'une correspondance exacte, vous définissez donc le troisième paramètre de la fonction de match sur 0.
  • -1 - correspondance approximative (prochain plus grand).

Par exemple, si la plage B1: B3 contient les valeurs "New-York", "Paris", "Londres", la formule suivante renvoie le numéro 3, car "Londres" est la troisième entrée de la gamme:

=MATCH("London",B1:B3,0)

Pour plus d'informations, voir les fonctions de correspondance Excel.

à première vue, l'utilisation de la fonction de match semble être discutable. Qui se soucie de la position de la valeur dans la gamme? Ce que nous voulons vraiment savoir, c'est la valeur elle-même.

Permettez-moi de vous rappeler que la position relative de la valeur de recherche (c'est-à-dire le numéro de ligne et de colonne) est exactement ce que vous devez fournir aux paramètres ROW_NUM et COLUMN_NUM à la fonction d'index. Vous vous souvenez qu'Excel Index peut trouver la valeur à l'intersection d'une ligne et d'une colonne données, mais elle ne peut pas déterminer la ligne et la colonne spécifiques souhaitées.

Comment utiliser la fonction de correspondance d'index dans Excel

Maintenant que vous avez appris les bases, je crois que vous avez commencé à comprendre comment le match et l'index fonctionnent ensemble. En bref, l'index trouve des valeurs via des recherches sur la colonne et le numéro de ligne, tandis que Match fournit ces nombres. C'est ?a!

Pour une recherche verticale, vous n'utilisez la fonction de correspondance que pour déterminer le numéro de ligne et fournir directement la plage de colonne à index:

Index ( colonne à partir de laquelle la valeur doit être renvoyée , correspondant ( colonne à trouver , 0)) est toujours difficile à comprendre? Il peut être plus facile de comprendre au moyen d'un exemple. Supposons que vous ayez une liste de capitales nationales et de leurs populations:

Pour trouver la population d'une capitale, comme la capitale du Japon, utilisez la formule d'indice suivante:

=INDEX(C2:C10, MATCH("Japan", A2:A10, 0))

Maintenant, analysons ce que fait chaque composant de cette formule:

• La fonction de correspondance recherche la valeur de recherche "Japon" dans la plage A2: A10 et renvoie le nombre 3 car "Japon" est le troisième du tableau de recherche.
• Le numéro de ligne est transmis directement au paramètre ROW_NUM de l'index, le guidant pour renvoyer une valeur de la ligne.

Par conséquent, la formule ci-dessus devient un index simple (C2: C, 3), ce qui signifie rechercher dans les cellules C2 à C10 et extraire la valeur de la troisième cellule de cette plage, c'est-à-dire C4, car nous commen?ons à compter à partir de la deuxième ligne.

Vous ne voulez pas de code hardcode dans les formules? Entrez-le dans une certaine cellule, comme F1, alimentez la référence de la cellule pour correspondre, et vous obtiendrez une formule de recherche dynamique:

=INDEX(C2:C10, MATCH(F1,A2:A10,0))

Astuce importante! Le nombre de lignes dans le paramètre du tableau d'index doit correspondre au nombre de lignes dans le paramètre Lookup_Array de la correspondance, sinon la formule produira un résultat d'erreur.

Attendez ... pourquoi n'utilisons-nous pas simplement la formule Vlookup suivante? Quel est l'intérêt de perdre du temps à essayer de comprendre l'index Mystery of Excel Match?

=VLOOKUP(F1, A2:C10, 3, FALSE)

Dans ce cas, cela n'a aucun sens :) Cet exemple simple est à des fins de démonstration uniquement afin que vous compreniez comment les fonctions d'index et de correspondance fonctionnent ensemble. L'exemple suivant vous montrera la véritable puissance de cette combinaison, qui peut facilement gérer de nombreux scénarios complexes, et Vlookup aura des difficultés.

indice:

• Dans Excel 365 et Excel 2021, vous pouvez utiliser la formule XMatch index plus moderne.
• Pour Google Sheets, consultez l'exemple de formule de correspondance d'index dans cet article.

Comparaison entre l'index Match et Vlookup

Lors de la décision de la fonction à utiliser pour la recherche verticale, la plupart des experts Excel estiment que la correspondance d'index est de loin supérieure à Vlookup. Cependant, de nombreuses personnes s'en tiennent toujours à Vlookup, d'abord parce qu'elle est plus simple, deuxièmement parce qu'elles ne comprennent pas complètement tous les avantages de l'utilisation de la formule d'index dans Excel. Sans cette compréhension, personne ne veut passer du temps à apprendre des grammaires plus complexes.

Ci-dessous, je vais souligner les principaux avantages de l'index de correspondance sur VLookup, vous pouvez décider s'il vaut la peine de rejoindre votre bibliothèque d'outils Excel.

4 raisons principales d'utiliser la correspondance d'index au lieu de Vlookup

1. Regardez de droite à gauche. Tout utilisateur expérimenté sait que Vlookup ne peut pas sembler à gauche, ce qui signifie que votre valeur de recherche doit toujours être dans la colonne la plus à gauche du tableau. La correspondance d'index peut facilement rechercher à gauche! L'exemple suivant montre comment il fonctionne réellement: comment trouver la valeur à gauche dans Excel.
2. Insérer ou supprimer en toute sécurité les colonnes. Lorsqu'une nouvelle colonne est supprimée ou ajoutée à partir d'une table de recherche, la formule VLookup peut corrompre ou fournir des résultats incorrects car la syntaxe de VLookup nécessite de spécifier le numéro d'index de la colonne à partir de laquelle vous souhaitez extraire des données. Naturellement, lorsque vous ajoutez ou supprimez les colonnes, le numéro d'index change. Avec Index Match, vous spécifiez la plage de colonne de retour, pas le numéro d'index. Par conséquent, vous êtes libre d'insérer et de supprimer n'importe quel nombre de colonnes sans vous soucier de mettre à jour chaque formule connexe.
3. Il n'y a pas de limite sur la taille de la valeur de recherche. Lorsque vous utilisez la fonction Vlookup, la longueur totale de votre condition de recherche ne peut pas dépasser 255 caractères, sinon vous obtiendrez un #Value! erreur. Donc, si votre ensemble de données contient de longues cha?nes, la correspondance d'index est la seule solution viable.
4. Vitesse de traitement plus élevée. Si votre table est relativement faible, il y aura peu de différences significatives dans les performances Excel. Cependant, si votre feuille contient des centaines ou des milliers de lignes et correspondant des centaines ou des milliers de formules, Match Index fonctionnera plus rapidement que VLookup, car Excel gère uniquement les colonnes de recherche et de retour, pas la table de tableaux entière. Si votre classeur contient des formules de tableau complexes comme VLookup et SUM, l'impact de VLookup sur les performances Excel peut être particulièrement visible. La clé consiste à vérifier chaque valeur dans le tableau nécessite d'appeler la fonction VLookup séparément. Donc, plus votre tableau contient de valeurs et plus il y a de formules de tableau dans votre classeur, le plus lent fonctionnera.

Pour comprendre les nuances entre l'index correspond à l'index et xlookup, explorez l'analyse approfondie de ce guide: Excel xlookup et index correspond à.

Excellent Match de l'index - Exemple de formule

Comprendre les raisons de l'apprentissage de la fonction d'index de correspondance, passons à la partie la plus intéressante et voyons comment les connaissances théoriques peuvent être appliquées à la pratique.

Formules de correspondance d'index trouvés de droite à gauche

Comme mentionné précédemment, Vlookup ne peut pas rechercher les yeux vers les yeux. Donc, à moins que votre valeur de recherche ne soit dans la colonne la plus à gauche, il est presque impossible que la formule VLookup vous apporte le résultat souhaité. La fonction de correspondance d'index dans Excel est plus polyvalente et ne se soucie pas vraiment de trouver et de renvoyer les emplacements des colonnes.

Pour cet exemple, nous ajouterons une colonne de classement à gauche du tableau d'exemples et essayerons de découvrir le classement de la population de Moscou, la capitale de la Russie.

Entrez la valeur de recherche en G1, recherchez en C2: C10 en utilisant la formule suivante et renvoyez la valeur correspondante à partir d'A2: A10:

=INDEX(A2:A10,MATCH(G1,C2:C10,0))

indice. Si vous prévoyez d'utiliser la formule de correspondance d'index pour plusieurs cellules, assurez-vous de verrouiller les deux plages avec des références de cellules absolues telles que $ a 2: $ a 10 $ et $ c 2 $: $ C 10 $ pour éviter la déformation lors de la copie de la formule.

Trouver dans les lignes et les colonnes en utilisant l'index Match

Dans l'exemple ci-dessus, nous utilisons l'index Match en remplacement du VLookup classique, renvoyant les valeurs à partir de plages de colonnes uniques prédéfinies. Mais que se passe-t-il si vous devez rechercher plusieurs lignes et colonnes? En d'autres termes, que se passe-t-il si vous voulez effectuer ce qu'on appelle une matrice ou une recherche bidirectionnelle ?

Cela peut sembler délicat, mais la formule est très similaire à la fonction de correspondance de base d'index Excel avec une seule différence. Devinez ce que c'est?

En termes simples, utilisez deux fonctions de correspondance - l'une pour obtenir le numéro de ligne et l'autre pour obtenir le numéro de colonne. Je félicite ceux qui l'ont deviné bien :)

Index (tableau, correspondance ( valeur vlookup , colonne pour consulter , 0), match ( valeur hlookup , ligne pour consulter , 0)) maintenant, créons une formule de correspondance d'index pour trouver la population d'un pays donné au cours d'une année donnée (en millions).

Entrez le pays cible (valeur VLookup) en G1 et entrez l'année cible (valeur HLookup) en G2. La formule est la suivante:

=INDEX(B2:D11, MATCH(G1,A2:A11,0), MATCH(G2,B1:D1,0))

Comment fonctionne cette formule

Chaque fois que vous avez besoin de comprendre une formule Excel complexe, décomposez-la en parties plus petites et voyez ce que fait chaque fonction individuelle:

MATCH(G1,A2:A11,0) - Recherchez la valeur ("Chine") dans Cell G1 dans A2: A11 et renvoyez sa position, c'est-à-dire 2.

MATCH(G2,B1:D1,0)) - Recherche dans B1: D1 pour obtenir la position de la valeur médiane de la cellule G2 ("2015"), c'est-à-dire 3.

Les numéros de ligne et de colonne ci-dessus sont transmis aux paramètres correspondants de la fonction d'index:

INDEX(B2:D11, 2, 3)

En conséquence, vous obtenez une valeur à l'intersection de la ligne 2 et de la colonne 3 dans la plage B2: D11, c'est-à-dire la valeur dans la cellule D3. Est-ce simple? Oui!

Excel Index Match Trouver plusieurs conditions

Si vous avez la possibilité de lire notre tutoriel Excel Vlookup, vous avez peut-être testé une formule VLookup avec plusieurs conditions. Cependant, une limitation significative de cette méthode est la nécessité d'ajouter une colonne d'assistance. La bonne nouvelle est que la fonction d'index d'Excel peut également rechercher deux conditions ou plus sans modifier ou réorganiser les données source!

Voici une formule générale d'index avec plusieurs conditions:

{= Index ( return_range , correspond (1, ( critères1 = range1 ) ( critères2 = range2 *), 0))} Remarque. Il s'agit d'une formule de tableau qui doit être effectuée à l'aide du raccourci Entrée Ctrl Shift.

Dans l'exemple du tableau ci-dessous, supposons que vous souhaitiez trouver le montant basé sur deux conditions, le client et le produit .

La formule de correspondance d'index suivante fonctionne très bien:

=INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10), 0))

Lorsque C2: C10 est la plage à partir de laquelle la valeur doit être retournée, F1 est les critères1, A2: A10 est la plage par rapport aux critères1, F2 est des critères2 et B2: B10 est la plage par rapport aux critères2.

N'oubliez pas d'entrer correctement la formule en appuyant sur Ctrl Shift Entrée, Excel enfermera automatiquement la formule en accolades bouclées, comme indiqué dans la capture d'écran:

Si vous préférez ne pas utiliser une formule de tableau dans votre feuille de calcul, ajoutez une autre fonction d'index à la formule et faites-la avec la clé Entrée habituelle:

Comment ces formules fonctionnent

Ces formules utilisent la même méthode que la fonction de correspondance d'index de base, en regardant une seule colonne. Pour évaluer plusieurs conditions, vous créez deux tableaux ou plus de valeurs vraies et non appariées et non appariées représentant chaque condition individuelle, puis multipliez les éléments correspondants de ces tableaux. L'opération de multiplication convertit True et False en 1 et 0 et génère un tableau où 1 correspond aux lignes qui satisfont à toutes les conditions. La fonction de correspondance recherche le premier "1" dans le tableau pour trouver la valeur 1 et transmet sa position à l'index, qui renvoie la valeur de cette ligne à partir de la colonne spécifiée.

Les formules sans réseau reposent sur la capacité native de la fonction d'index à traiter les tableaux. Le deuxième index est configuré avec 0 ROW_NUM , il passe donc tout le tableau des colonnes à correspondre.

Il s'agit d'une explication de haut niveau de la logique de formule. Pour plus de détails, voir Excel Index correspond à plusieurs conditions.

Excel Index Match and Average, Max, Min

Microsoft Excel a des fonctions spéciales pour trouver les valeurs minimales, maximales et moyennes dans la plage. Mais que se passe-t-il si vous avez besoin d'obtenir la valeur d'une autre cellule associée à ces valeurs? Dans ce cas, utilisez la fonction max, min ou moyenne en conjonction avec la correspondance d'index.

Match d'index combiné avec max

Pour trouver la valeur maximale dans la colonne D et renvoyer une valeur de la colonne C dans la même ligne, utilisez cette formule:

=INDEX(C2:C10, MATCH(MAX(D2:D10), D2:D10, 0))

Correspond à index combiné avec min

Pour localiser la valeur minimale dans la colonne D et extraire la valeur associée de la colonne C, utilisez la formule suivante:

=INDEX(C2:C10, MATCH(MIN(D2:D10), D2:D10, 0))

Correspondance d'index combinée avec la moyenne

Pour calculer la valeur la plus proche de la moyenne dans D2: D10 et obtenir la valeur correspondante de la colonne C, utilisez la formule suivante:

=INDEX(C2:C10, MATCH(AVERAGE(D2:D10), D2:D10, -1 ))

Selon la fa?on dont vos données sont organisées, fournissez 1 ou -1 au troisième paramètre de la fonction de correspondance (match_type):

• Si votre colonne de recherche (dans notre colonne D) est triée par ordre croissant , entrez 1. La formule calculera la valeur maximale inférieure ou égale à la valeur moyenne.
• Si vos colonnes de recherche sont triées en ordre décroissant , entrez -1. La formule calculera la valeur minimale supérieure ou égale à la moyenne.
• Si votre tableau de recherche contient une valeur qui équivaut à la moyenne, vous pouvez entrer 0 pour une correspondance exacte. Aucun tri n'est requis.

Dans notre exemple, la population de la colonne D est triée par ordre décroissant, nous utilisons donc -1 comme type de correspondance. En conséquence, nous obtenons "Tokyo" parce que sa population (13 189 000) est la plus proche de la plus grande que la moyenne (12 269 006).

Vous pouvez être curieux que VLookup puisse également effectuer de tels calculs, mais en tant que formules de tableau: VLOOKUP Vs. Moyenne, Max, Min.

Utilisation de l'index Match en combinaison avec IFNA / IFERROR

Comme vous l'avez peut-être remarqué, si la formule de correspondance d'index dans Excel ne trouve pas la valeur de recherche, elle produit une erreur # n / a. Si vous souhaitez remplacer le symbole d'erreur standard par quelque chose de plus significatif, enveloppez votre formule de correspondance d'index dans une fonction IFNA. Par exemple:

=IFNA(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "No match is found")

Maintenant, si quelqu'un entre une valeur qui n'existe pas dans la table de recherche, la formule informe explicitement l'utilisateur qu'aucune correspondance n'a été trouvée:

Si vous souhaitez attraper toutes les erreurs, pas seulement # n / a, utilisez la fonction IFERROR au lieu d'IFNA:

=IFERROR(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "Oops, something went wrong!")

N'oubliez pas que dans de nombreux cas, il peut être imprudent de masquer toutes les erreurs, car ils vous alertent des défauts possibles dans la formule.

C'est comment utiliser l'index et correspondre dans Excel. J'espère que nos exemples de formule vous aideront et auront hate de vous voir sur notre blog la semaine prochaine!

Téléchargement du classeur d'exercice

Exemple de correspondance index (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!