Tutoriel Excel : la fonction INDEX

Ce tutoriel Excel explique comment utiliser la fonction Excel INDEX avec une syntaxe et des exemples.

La fonction Microsoft Excel INDEX renvoie une valeur dans une table en fonction de l'intersection d'une ligne et d'une position de colonne dans cette table. La première ligne du tableau est la ligne 1 et la première colonne du tableau est la colonne 1.

La fonction INDEX est une fonction intégrée à Excel, classée en tant que fonction de recherche / référence. Il peut être utilisé en tant que fonction de feuille de calcul (Excel). En tant que fonction de feuille de calcul, la fonction INDEX peut être entrée en tant que partie d’une formule dans une cellule d’une feuille de calcul.

La fonction INDEX renvoie la valeur / position de la cellule dans une table ou une plage donnée. Index dans Excel est utile lorsque nous avons plusieurs données et que nous connaissons la position à partir de laquelle le point de données doit être récupéré.

Table des matières

Quelle est la fonction INDEX ?

Comme il est déjà mentionné La fonction INDEX est classée dans les fonctions de recherche et de référence. La fonction renverra la valeur à une position donnée dans une plage ou un tableau. La fonction INDEX est souvent utilisée avec la fonction EQUIV. Nous pouvons dire que c'est une autre façon de faire RECHERCHEV.

Pour les analystes financiers, le fonction INDEX peut être utilisée dans d'autres formes d'analyse en plus de rechercher une valeur dans une liste ou un tableau. En analyse financière, nous pouvons l’utiliser avec d’autres fonctions pour rechercher et renvoyer la somme d’une colonne.

Il existe deux formats pour la fonction INDEX :

1-      Format de matrice

2-      Format de référence

Le format de matrice de la fonction INDEX

Le format de matrice est utilisé lorsque nous souhaitons renvoyer la valeur d’une cellule ou d’un tableau de cellules spécifié.

Formule

= INDEX (matrice ; numéro_ligne ; [numéro_col])

La fonction utilise les arguments suivants :

• Matrice (argument requis) - Il s'agit du tableau ou de la plage de cellules spécifiée.
• numéro_ligne  (argument requis) - Indique le numéro de ligne du tableau spécifié. Lorsque l'argument est défini sur zéro ou vide, toutes les lignes du tableau fourni sont définies par défaut.
• numéro_col (argument facultatif) - Indique le numéro de colonne du tableau spécifié. Lorsque cet argument est défini sur zéro ou vide, toutes les lignes du tableau fourni sont définies par défaut.

Le format de référence de la fonction INDEX

Le format de référence est utilisé lorsque nous souhaitons renvoyer la référence de la cellule à l'intersection « Numéro de ligne » et « Numéro de colonne ».

Formule

= INDEX (référence ; numéro de ligne ; [numéro de colonne] ; [numéro de zone])

La fonction utilise les arguments suivants :

• Référence (argument requis) - Il s'agit d'une référence à une ou plusieurs cellules. Si vous entrez plusieurs zones directement dans la fonction, les zones individuelles doivent être séparées par des points virgules et entourées de parenthèses. Tels que (A1: B2 ; C3: D4), etc.
• Numéro_ligne  (argument requis) - Indique le numéro de ligne d'une zone spécifiée. Lorsque l'argument est défini sur zéro ou vide, toutes les lignes du tableau fourni sont définies par défaut.
• Numéro_colonne (argument facultatif) - Indique le numéro de colonne du tableau spécifié. Lorsque l'argument est défini sur zéro ou vide, toutes les lignes du tableau fourni sont définies par défaut.
• Numéro_zone (argument facultatif) - Si la référence est fournie sous forme de plusieurs plages, Numéro_zone indique la plage à utiliser. Les zones sont numérotées selon l'ordre dans lequel elles sont spécifiées.

Si l'argument Numéro_zone est omis, sa valeur par défaut est 1 (c'est-à-dire que la référence provient de la première zone de la plage fournie).

Points à retenir sur la fonction INDEX

Avant de commencer à utiliser la fonction INDEX il y a des points que vous devez retenir, à savoir :

1-      L’erreur #VALEUR!

-          La fonction d'indexation sur Excel renvoie #VALEUR ! Erreur si la zone mentionnée dans la formule INDEX dans Excel se trouve dans une autre feuille. Les zones mentionnées dans la formule INDEX dans Excel doivent figurer sur une seule feuille.

-          L’erreur #VALEUR! - Se produit lorsque l'un des arguments donnés Numéro_ligne, Numéro_colonne ou Numéro_zone n'est pas numérique.

2-      L’erreur #REF! - se produit pour l'une des raisons suivantes :

-          L'argument Numéro_ligne donné est supérieur au nombre de lignes de la plage donnée.

-          L'argument [Numéro_colonne] donné est supérieur au nombre de colonnes dans la plage fournie. Ou

-          L'argument donné [Numéo_zone] est supérieur au nombre de zones dans la plage fournie.

3-      La fonction RECHERCHEV vs. INDEX

-          La fonction Excel RECHERCHEV ne peut pas regarder à sa gauche, ce qui signifie que notre valeur de recherche doit toujours figurer dans la colonne la plus à gauche de la plage de recherche. Ce n'est pas le cas avec les fonctions INDEX et EQUIV.

-          Les formules RECHERCHEV sont cassées ou renvoient des résultats incorrects lorsqu'une nouvelle colonne est supprimée ou ajoutée à une table de recherche. Avec INDEX et EQUIV, nous pouvons supprimer ou insérer de nouvelles colonnes dans votre table de recherche sans altérer les résultats.

Quand utiliser la fonction Excel INDEX

La fonction Excel INDEX peut être utilisée lorsque vous voulez extraire la valeur d'une donnée tabulaire et que vous avez le numéro de ligne et le numéro de colonne du point de données. Par exemple, dans l’exemple ci-dessous, vous pouvez utiliser la fonction INDEX pour obtenir les repères «Tom» dans Physics lorsque vous connaissez le numéro de ligne et le numéro de colonne dans le jeu de données.

Comment utiliser la fonction INDEX

Dans cette partie, vous apprendrez à utiliser les deux formats de la fonction INDEX : format matrice et format de référence.

Fonction d'indexation Excel (format de tableau)

Dans ce premier exemple nous allons rechercher le nombre d’années d’expériences de l’employé « Emp 4 » en utilisant le format de matrice de la fonction INDEX. Pour entrer la fonction INDEX et les arguments à l’aide de la boîte de dialogue :

1-      Cliquez sur la cellule D2, qui est l'emplacement où le résultat sera affiché.

2-      Cliquez sur l'onglet Formules du menu du ruban.

3-      Choisissez « Recherche et référence » de l’onglet « Formule » dans le ruban pour ouvrir la liste déroulante des fonctions.

4-      Cliquez sur INDEX dans la liste pour afficher la boîte de dialogue de la fonction.

5-      Dans la zone « Arguments », sélectionnez l'option qui fait référence au « matrice ; no_lig ;no_col » et cliquez sur « OK »

6-      Mettez en surbrillance les cellules A2 à B8 dans la feuille de calcul pour entrer la matrice dans la boîte de dialogue dans la zone « Matrice ».

7-      Cliquez sur la zone de numéro de ligne « no_lig » dans la boîte de dialogue.

8-      Insérez le nombre 4 où Excel va chercher la valeur.

9-      Cliquez sur la zone de numéro de colonne « no_col » dans la boîte de dialogue.

10-   Entrez le nombre 2 dans cette zone pour renvoyer la valeur de la colonne 2, intitulée expérience (Année).

11-   Cliquez sur OK pour terminer la fonction et fermer la boîte de dialogue.

12-   Le chiffre 7 apparaît dans la cellule D2 puisque l’employé de la quatrième rangée, Emp 4, compte 7 années d’expérience.

Lorsque vous cliquez sur la cellule D2, la fonction complète = INDEX (A2: B8 ;4 ;2) apparaît dans la barre de formule au-dessus de la feuille de calcul.

Fonction d'indexation Excel (format de référence)

Voyons maintenant la seconde forme de la fonction « INDEX ».

La forme de référence de la fonction « INDEX » nous permet de rechercher des valeurs dans plusieurs plages plutôt que dans une seule plage comme nous l’avons fait jusqu’à présent. Par exemple, considérons un scénario dans lequel vous aviez les plages suivantes qui sont exactement dans le même formulaire mais avec des données différentes.

Dans l'exemple suivant, nous cherchons à trouver les heures de production du mois juin. Nous avons trois trimestres, et chaque trimestre a son propre tableau des heures de productivité.

1-       La première chose à faire est de faire nos sélections pour le paramètre de référence. Il s’agira de trois plages différentes (nos trois tables distinctes des heures de productivité dans les trimestres) séparées par une virgule entre parenthèses. Donc nous allons sélectionner (A2 :B4 ;B8 :C10 ;D2 :E4).

2-      Ensuite, les deux paramètres suivants sont simplement la ligne et la colonne, comme nous l’avons déjà utilisé.

3-      Le dernier paramètre, « No_zone », est le numéro de position des quatre plages qui composent notre paramètre de référence. Par exemple, si nous choisissons le nombre 2 pour « no_zone », cela sélectionnerait la plage B8: C10 (notre deuxième plage dans le paramètre «référence»).

Si nous voulons trouver le nombre d’heures de production pour le mois juin nous devons mettre 2 dans le numéro de zone puisque « Juin » est inclus dans la zone B8 :C10, et nous choisirons la rangée 2, la colonne 2. Donc la formule devient : =INDEX((A2:B4;B8:C10;D2:E4);2;2;2)

Cela renvoie la valeur de 142 exactement comme nous l’attendions.

Création d'une référence à l'aide de la fonction INDEX (plages nommées dynamiques)

C'est une utilisation très importante de la fonction Excel INDEX. Prenons un exemple simple.

J'ai une liste de noms comme ci-dessous :

Maintenant, je peux utiliser une simple fonction INDEX pour obtenir le dernier nom de la liste. Voici la formule à utiliser :

=INDEX($A$2:$A$9;NBVAL($A$2:$A$9))

Cette fonction compte simplement le nombre de cellules qui ne sont pas vides et renvoie le dernier élément de cette liste (elle ne fonctionne que s'il n'y a pas de blancs dans la liste).

Maintenant, ce qui vient ici la magie.

Si vous placez la formule devant une référence de cellule, la formule renverrait une référence de cellule de la valeur correspondante (au lieu de la valeur elle-même).

=A2:INDEX($A$2:$A$9;NBVAL($A$2:$A$9))

Vous vous attendriez à ce que la formule ci-dessus renvoie = A2: "Naima" (où Naima est la dernière valeur de la liste). Cependant, il retourne = A2:A9 et vous obtenez donc un tableau de noms comme indiqué ci-dessous :

Pour voir le tableau retourné, faites une double clique sur la cellule pour afficher la formule et cliquez ensuite sur la touche clavier F9, cela affichera les éléments du tableau.

INDEX et EQUIV dans Excel - une meilleure alternative à RECHERCHEV

Cette partie présente les principaux avantages de la fonction INDEX / EQUIV d’Excel, qui la rend supérieure à RECHERCHEV. Vous trouverez un certain nombre d'exemples de formules qui vous aideront à gérer facilement de nombreuses tâches complexes en cas d'échec de RECHERCHEV.

Dans quelques articles récents, je me suis efforcé d'expliquer les bases de la fonction Excel RECHERCHEV et de fournir des exemples plus complexes de formules RECHERCHEV. Et maintenant, je vais essayer de ne pas vous dissuader d’utiliser RECHERCHEV, mais au moins de vous montrer une autre façon de faire une recherche verticale dans Excel.

« Pourquoi ai-je besoin de ça ? » vous pouvez me demander. Parce que RECHERCHEV n'est pas la seule formule de recherche disponible dans Excel, ses nombreuses limitations peuvent vous empêcher d'obtenir le résultat souhaité dans de nombreuses situations. D'autre part, INDEX EQUIV d'Excel est plus flexible et présente certaines fonctionnalités qui le rendent supérieur à RECHERCHEV à bien des égards.

La syntaxe et l'utilisation de la fonction EQUIV

La fonction Excel EQUIV recherche une valeur de recherche dans une plage de cellules et renvoie la position relative de cette valeur dans la plage.

Par exemple, si la plage B1:B3 contient les valeurs "Agadir", "Rabat", "Casablanca", la formule = EQUIV ("Casablanca" ;B1: B3 ;0) renvoie le nombre 3, car "Casablanca"est la troisième entrée de la plage.

La syntaxe de la fonction EQUIV est la suivante :

EQUIV (valeur_cherchée; tableau_recherche; [type])

valeur_cherchée - c'est le numéro ou le texte que vous recherchez. Cela peut être une valeur, une référence de cellule ou une valeur logique.

tableau_recherche - une plage de cellules en cours de recherche.

type - ce paramètre indique à la fonction EQUIV si vous souhaitez renvoyer une correspondance exacte ou la correspondance la plus proche:

• 1 ou omis : recherche la plus grande valeur inférieure ou égale à la valeur de référence. Les valeurs du tableau de recherche doivent être triées par ordre croissant, du plus petit au plus grand.
• 0 : trouve la première valeur qui est exactement égale à la valeur de recherche. Dans la combinaison INDEX / EQUIV, vous avez presque toujours besoin de la correspondance exacte. Le troisième argument de votre fonction EQUIV est donc « 0 ».
• -1 : recherche la plus petite valeur supérieure ou égale à valeur_cherchée. Les valeurs du tableau de recherche doivent être triées par ordre décroissant, du plus grand au plus petit.

Pour plus d'informations sur la fonction EQUIV, reportez-vous à la section Comment utiliser la fonction EQUIV dans Excel.

À première vue, l’utilité de la fonction EQUIV peut paraître discutable. Qui se soucie de la position d'une valeur dans une plage ? Ce que nous voulons 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 un numéro de ligne ou / et de colonne) correspond exactement à ce que vous devez fournir à l'argument Num_lg ou / et Num_colde la fonction INDEX. Comme vous vous en souvenez, la fonction INDEX peut renvoyer la valeur à la jonction d'une ligne et d'une colonne données, mais elle ne peut pas déterminer exactement la ligne et la colonne souhaitées.

Comment utiliser la fonction INDEX EQUIV dans Excel

Maintenant que vous connaissez les bases de ces deux fonctions, vous devez avoir compris pourquoi les fonctions EQUIV et INDEX d’Excel fonctionnent ensemble. La fonction EQUIV détermine la position relative de la valeur de recherche dans la plage de cellules spécifiée. À partir de là, la fonction INDEX prend ce ou ces chiffres et renvoie une valeur dans la cellule correspondante.

Vous avez toujours des difficultés à le comprendre ? Pensez à Excel INDEX / EQUIV de la manière suivante :

INDEX (colonne à partir de laquelle renvoyer une valeur ; EQUIV (valeur de recherche ; colonne à rechercher ; 0))

Je crois que c'est encore plus facile à comprendre à partir d'un exemple. Supposons que vous ayez une liste des capitales nationales comme celle-ci :

Trouvons la population de certains capitaux, par exemple la capitale du Japon, en utilisant la formule de correspondance d'index suivante :

=INDEX($D$2:$D$10;EQUIV("Japon";$B$2:$B$10;0))

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

-          La fonction EQUIV recherche la valeur de recherche "Japon" dans la plage B2: B10 et renvoie le nombre 3, car « Japon » est la troisième dans le tableau de recherche.

-          La position relative de la valeur de recherche va directement à l'argument « Num_lg » de la fonction INDEX lui demandant de renvoyer une valeur à partir de cette ligne.

Ainsi, la formule ci-dessus se transforme en un simple INDEX ($D$2: $D$10 ;3) qui dit de rechercher dans les cellules D2 à D10 et de renvoyer la valeur de la 3ème cellule dans cette plage, c'est-à-dire la cellule D4 car nous commençons à compter la deuxième rangée.

Et voici le résultat obtenu dans Excel :

Important ! Le nombre de lignes dans l'argument de tableau d'INDEX doit correspondre au nombre de lignes de l'argument tableau_recherche de la fonction EQUIV, sinon la formule renverra un résultat incorrect.

Attendez, attendez ... pourquoi n'utilisons-nous pas simplement la formule RECHERCHEV suivante ? À quoi sert-il de perdre du temps à essayer de comprendre les méandres obscurs de EQUIV / INDEX d'Excel ?

= RECHERCHEV("Japon";$B$2:$D$10;3;0)

Dans ce cas, ça ne sert à rien :) Cet exemple simple sert uniquement à des fins de démonstration, pour vous donner une idée de la manière dont les fonctions INDEX et EQUIV fonctionnent ensemble. Les autres exemples ci-dessous vous montreront le pouvoir réel de la liaison INDEX EQUIV, qui gère facilement de nombreux scénarios complexes lorsque RECHERCHEV tombe.

Pourquoi INDEX EQUIV d'Excel est meilleur que RECHERCHEV

Lors du choix de la formule à utiliser pour les recherches verticales, la majorité des gourous d'Excel s'accordent pour dire que INDEX / EQUIV est bien meilleur que RECHERCHEV. Cependant, de nombreux utilisateurs d'Excel ont encore recours à RECHERCHEV car il s'agit d'une fonction plus simple. Cela est dû au fait que très peu de personnes comprennent parfaitement tous les avantages du passage de RECHERCHEV à INDEX EQUIV et que, sans cette compréhension, personne ne veut investir son temps pour apprendre une formule plus complexe.

Ci-dessous, je vais essayer de souligner les principaux avantages de l'utilisation de la fonction INDEX avec EQUIV dans Excel, puis vous décidez si vous préférez rester avec RECHERCHEV ou basculer vers la correspondance d'index.

Quatre principaux avantages de l'utilisation de EQUIV INDEX dans Excel

1-      Recherche de droite à gauche. Comme tout utilisateur averti le sait, Excel RECHERCHEV ne peut pas regarder à sa gauche, ce qui signifie que votre valeur de recherche doit toujours figurer dans la colonne la plus à gauche de la plage de recherche. INDEX EQUIV peut facilement faire une recherche à gauche ! L'exemple suivant illustre cette fonctionnalité en action - Comment RECHERCHEV une valeur à gauche dans Excel à l’aide de la correspondance d’index.

2-      Insérer ou supprimer des colonnes en toute sécurité. Les formules RECHERCHEV sont cassées ou renvoient des résultats incorrects lorsqu'une nouvelle colonne est supprimée ou ajoutée à une table de recherche. Avec RECHERCHEV, toute colonne insérée ou supprimée modifie les résultats renvoyés par vos formules car la syntaxe de la fonction RECHERCHEV nécessite la spécification du tableau de table entier et d'un certain nombre indiquant la colonne à partir de laquelle vous souhaitez extraire les données.

Par exemple, si vous avez une table A1: C10 et souhaitez renvoyer une valeur de la colonne B, vous allez mettre « 2 » dans le troisième paramètre (no_index_col) d'une formule RECHERCHEV, par exemple = RECHERCHEV ("valeur de recherche" ; A1: C10 ; 2). Si, par la suite, vous insérez une nouvelle colonne entre A et B, vous devrez remplacer « 2 » par « 3 » dans votre formule. Dans le cas contraire, il renverrait une valeur à partir de la nouvelle colonne insérée.

Avec INDEX EQUIV, vous pouvez supprimer ou insérer de nouvelles colonnes dans votre table de recherche sans altérer les résultats puisque vous spécifiez directement la colonne contenant la valeur que vous souhaitez obtenir. Et c’est un avantage considérable, en particulier lorsque vous travaillez avec de grands ensembles de données, car vous pouvez insérer et supprimer des colonnes sans vous soucier de la mise à jour de chaque formule RECHERCHEV associée.

3-      Aucune limite pour la taille d'une valeur de recherche. Lorsque vous utilisez la fonction RECHERCHEV, rappelez-vous que la longueur totale de vos critères de recherche ne doit pas dépasser 255 caractères, sinon vous obtiendrez l’erreur « #VALEUR ! ». Ainsi, si votre jeu de données contient de longues chaînes, INDEX EQUIV est la seule solution qui fonctionne.

Supposons que vous utilisiez la formule RECHERCHEV suivante pour rechercher la valeur dans la cellule A2 dans les cellules B5 à D10 :

= RECHERCHEV (A2 ; B5: D10 ;3 ; FAUX)

La formule ne fonctionnera pas si la valeur de recherche dans la cellule A2 dépasse 255 symboles. A la place, vous utiliserez la fonction analogue INDEX / EQUIV :

=INDEX(D5:D10;EQUIV(VRAI;INDEX(B5:B10=A2;0);0))

4-      Plus grande vitesse de traitement. Si vos tables sont relativement petites, les performances d'Excel ne seront guère différentes. Toutefois, si vos feuilles de calcul contiennent des centaines ou des milliers de lignes et, par conséquent, des centaines ou des milliers de formules, EQUIV INDEX fonctionnera beaucoup plus rapidement que RECHERCHEV, car Excel devra traiter uniquement les colonnes de recherche et de retour plutôt que le tableau de tableau entier.

L'impact de RECHERCHEV sur les performances d'Excel peut être particulièrement visible si votre classeur contient des formules matricielles complexes telles que RECHERCHEV et SOMME. Le fait est que la vérification de chaque valeur du tableau nécessite un appel séparé de la fonction RECHERCHEV. Ainsi, plus un tableau contient de valeurs et plus vous avez de formules de tableau dans un classeur, plus Excel est lent.

D'autre part, avec INDEX EQUIV, Excel doit prendre en compte uniquement les colonnes de recherche et de retour, de sorte qu'il traite ces formules beaucoup plus rapidement.

La correspondance d’index Excel INDEX et EQUIV - exemples de formules

Maintenant que vous connaissez les raisons d'apprendre la fonction EQUIV INDEX, passons à la partie la plus intéressante et voyons comment appliquer concrètement les connaissances théoriques.

Comment regarder de droite à gauche avec INDEX et EQUIV

Comme indiqué dans n'importe quel didacticiel RECHERCHEV, cette fonction Excel ne peut pas regarder à gauche. Ainsi, sauf si votre colonne de recherche est la colonne la plus à gauche de la plage de recherche, il n'y a aucune chance qu'une formule RECHERCHEV renvoie le résultat souhaité.

La fonction INDEX EQUIV d'Excel est plus flexible et ne tient pas vraiment compte de l'emplacement de la colonne de retour. À titre d’exemple, nous utiliserons à nouveau le tableau répertoriant les capitales nationales par population. Cette fois-ci, écrivons une formule INDEX EQUIV qui indique comment la capitale Russie, Moscou, se classe en termes de population.

Comme vous pouvez le voir dans la capture d'écran ci-dessous, la formule suivante ne pose pas de problème pour effectuer une RECHERCHEV gauche :

= INDEX ($A$2: $A$10; EQUIV ("Russie"; $B$2: $B$10;0))

Naturellement, vous pouvez remplacer la valeur de recherche "codée en dur" par une référence de cellule :

=INDEX($A$2:$A$10;EQUIV(E5;$B$2:$B$10;0))

Pour le moment, vous ne devriez pas avoir de difficulté à comprendre comment fonctionne la formule :

-          Tout d'abord, vous écrivez une formule EQUIV simple qui trouve la position de la Russie : = EQUIV ("Russie" ; $ B $ 2: $ B $ 10 ;0))

-          Ensuite, vous déterminez le paramètre de tableau pour votre fonction Index, qui est la colonne A dans notre cas (A2: A10).

-          Enfin, vous assemblez les deux parties et obtenez la formule suivante : = INDEX ($A$2: $A$10; EQUIV ("Russie"; $B$2: $B$10;0))

Remarque : Il est judicieux de toujours utiliser des références de cellules absolues dans les formules INDEX et EQUIV afin que vos plages de recherche ne soient pas déformées lorsque vous copiez la formule dans d'autres cellules.

Calculs avec INDEX EQUIV dans Excel (MOYENNE, MAX, MIN)

Vous pouvez imbriquer d'autres fonctions Excel dans la formule INDEX EQUIV, par exemple, pour rechercher la valeur minimale ou maximale ou la valeur la plus proche de la moyenne dans la plage. Voici quelques exemples de formules pour la table utilisée dans l'exemple précédent :

1-      Rechercher la valeur minimale de population dans la colonne D et renvoyer la valeur de la capital correspondante de la colonne C dans la même ligne : =INDEX($C$2:$C$10;EQUIV(MIN($D$2:D$10);$D$2:D$10;0))

2-      Rechercher la valeur maximale de population dans la colonne D et renvoyer la valeur de la capital correspondante de la colonne C dans la même ligne : =INDEX($C$2:$C$10;EQUIV(MAX($D$2:D$10);$D$2:D$10;0))

3-      Calculer la moyenne dans l'intervalle D2: D10, et trouver la valeur la plus proche de cette moyenne en suite renvoyer une valeur correspondante dans la colonne C.

=INDEX($C$2:$C$10;EQUIV(MOYENNE($D$2:D$10);$D$2:D$10;1))

Points importants à prendre en compte lors de l'utilisation de MOYENNE avec INDEX / EQUIV

Lorsque vous utilisez la fonction MOYENNE en combinaison avec INDEX EQUIV, vous devez le plus souvent entrer "1" ou "-1" dans le troisième argument (type) de la fonction EQUIV, sauf si vous êtes certain que votre tableau de correspondance contient une valeur exactement égale à la moyenne. Dans ce dernier cas, vous pouvez entrer "0" pour une correspondance exacte.

Si vous définissez 1, les valeurs de la colonne de recherche doivent être triées par ordre croissant et la formule renverra la valeur la plus grande inférieure ou égale à la valeur moyenne.

Si vous définissez -1, les valeurs de la colonne de recherche doivent être triées par ordre décroissant et la plus petite valeur supérieure ou égale à la valeur moyenne sera renvoyée.

Dans notre exemple, les valeurs de la colonne D sont triées par ordre croissant. Nous utilisons donc "1" comme type de correspondance et notre formule Moyenne + Correspondance d'index renvoie « Moscou » puisque sa population (11,5410,00) est la plus proche « inférieur ou égale » et donc correspond au nombre moyen (12.269.006).

Comment utiliser INDEX EQUIV pour effectuer une recherche par valeur de ligne et de colonne

Cette formule est l'équivalent du RECHERCHEV en deux dimensions qui vous permet de trouver la valeur à l'intersection d'une ligne et d'une colonne données.

Dans cet exemple, votre fonction Excel INDEX EQUIV sera très similaire aux autres formules que nous avons déjà décrites dans ce tutoriel, à la seule différence. Devine quoi ?

Comme vous vous en souvenez, la syntaxe de la fonction INDEX autorise les deux méthodes : INDEX (matrice ; numéro_ligne ; [numéro_colonne]). Et je félicite ceux d’entre vous qui ont deviné juste:)

Pour commencer, créons la formule générale pour effectuer une recherche matricielle. Nous prenons simplement la formule INDEX / EQUIV que vous connaissez déjà et nous y ajoutons une fonction EQUIV supplémentaire, qui renverra le numéro de colonne :

= INDEX (table de consultation ; EQUIV (valeur de recherche verticale ; colonne à laquelle se référer ; 0) ; EQUIV (valeur de recherche horizontale ; ligne à laquelle rechercher ; 0))

S'il vous plaît faites attention que vous devez spécifier la table entière dans l'argument de tableau de la fonction INDEX en cas de recherche dans les deux sens.

Et maintenant, appliquons ce modèle de formule dans la pratique. Vous trouverez ci-dessous une liste des pays les plus peuplés du monde. Supposons que vous souhaitiez connaître le nombre d'habitants aux États-Unis en 2015 :

Bon, commençons par la formule. Chaque fois que j'ai besoin de créer une formule Excel complexe avec une ou plusieurs fonctions imbriquées, j'écris toujours chaque fonction en premier. Ainsi, vous commencez par écrire deux fonctions EQUIV qui renverront les numéros de ligne et de colonne de votre fonction INDEX.

-          Correspondance verticale - vous recherchez dans la colonne B, plus précisément dans les cellules B2 à B11, la valeur de ("USA") et la fonction EQUIV correspondante est la suivante :

= EQUIV ("USA"; $B$1: $B$11 ;0) Cette formule EQUIV renvoie 4 parce que "USA" est le 4ème élément de la colonne B (y compris l'en-tête de colonne).

-          Correspondance horizontale - vous recherchez la valeur ("2015") dans la ligne 1, c'est-à-dire dans les cellules A1 à E1 et la formule est : = EQUIV (2015; $A$1: $E$1 ;0) Cette formule EQUIV renvoie " 5 " parce que" 2015 "est la 5ème colonne.

Maintenant, mettez les formules ci-dessus dans la fonction INDEX, et le tour est joué :

=INDEX($A$1:$E$11;EQUIV("USA";$B$1:$B$11;0);EQUIV(2015;$A$1:$E$1;0))

Si vous remplacez les fonctions EQUIV par les nombres renvoyés, la formule est beaucoup plus facile à comprendre : =INDEX($A$1:$E$11;4;5)

En d'autres termes, elle renvoie une valeur à l'intersection de la 4ème ligne et de la 5ème colonne de la plage A1: E11, qui correspond à la valeur de la cellule E4.

Recherchez avec plusieurs critères en utilisant INDEX EQUIV

La fonction Excel RECHERCHEV nous permet aussi de créer des formules permettant de faire une recherche verticale avec plusieurs critères. Cependant, une des limitations importantes de cette approche c’est la nécessité d’ajouter une colonne auxiliaire. La bonne nouvelle est que la fonction INDEX EQUIV d'Excel peut également rechercher des valeurs dans 2 colonnes. Aucune colonne d'assistance n'est demandée !

Supposons que vous ayez une liste de commandes et que vous souhaitiez trouver la somme en fonction de 2 critères, « Nom du client » et « Produit ». Un facteur de complication est qu'un client peut acheter plusieurs produits et que les noms de clients sont listés dans un ordre aléatoire dans la table de recherche. Le tableau principal est dans une feuille de calcul que j’ai nommé « table principale » et le tableau de recherche est dans une autre feuille de calcul nommée « table de consultation ».

Et ci-dessous la table de consultation.

La formule de tableau INDEX EQUIV suivante fonctionne comme un régal :

=INDEX('Table de consultation'!$A$2:$C$13;EQUIV(1;(A2='Table de consultation'!$A$2:$A$13)*(B2='Table de consultation'!$B$2:$B$13);0);3)

Cette formule est plus complexe que toutes les autres dont nous avons discuté précédemment, mais reposant sur la connaissance d’Excel EQUIV INDEX que vous avez déjà, je suis sûr que vous pourrez y faire face.

La partie la plus délicate est la fonction EQUIV, alors allons-y d'abord :

EQUIV(1;(A2='Table de consultation'!$A$2:$A$13);0) * (B2='Table de consultation'!$B$2:$B$13); 0)

Nous avons ici les 3 arguments suivants :

Valeur_recherchée : 1

Tableau_recherche : (A2='Table de consultation'!$A$2:$A$13);0) * (B2='Table de consultation'!$B$2:$B$13)

Type : 0

Les paramètres 1 et 3 sont limpides - la fonction recherche "1" et renvoie la première valeur trouvée.

Maintenant, la question principale est : pourquoi cherchons-nous "1" ? Pour obtenir la réponse, examinons de plus près notre tableau de recherche.

Dans ce cas, prenons la première valeur (A2) de la colonne « Nom du client » de la table principale et la comparons à tous les noms de clients de la table de recherche (A2: A13). Si la correspondance est trouvée, l'équation renvoie VRAI, sinon FAUX. Ensuite, nous procédons de même pour les valeurs des colonnes B ("Produits").

Pour mieux comprendre ce dont je parle, vous pouvez sélectionner les tableaux dans la barre de formule et appuyer sur la touche clavier « F9 » pour voir en quoi chaque partie sélectionnée est évaluée :

Comme vous le savez probablement, dans les formules Excel, la valeur logique de VRAI est égale à 1 et FAUX est égale à 0. Et comme l'astérisque (*) joue le rôle d'opérateur ET dans les formules matricielles, nous n'obtenons « 1 » que si la correspondance est trouvée dans les deux colonnes, 0 sinon. Ainsi, le résultat de cette opération est un tableau de 1 et de 0, où « 1 » est la valeur qui remplit les deux conditions spécifiées. Si votre table de recherche ne contient aucune ligne dupliquée, il y aura juste un « 1 » dans le tableau. Et parce que « 1 » est notre valeur de recherche, la fonction EQUIV renvoie la position relative de cette ligne, comme illustré dans la capture d'écran suivante :

Veuillez également noter que vous devez utiliser le troisième paramètre facultatif (num_col) de la fonction INDEX. Cela est dû au fait que vous spécifiez la table entière dans le premier paramètre (tableau) et que vous devez indiquer à la fonction quelle colonne vous souhaitez renvoyer comme une valeur. Dans notre cas, c'est la colonne C (« Somme »), donc nous entrons 3 dans le troisième argument de la fonction Index.

Enfin, étant donné que nous devons vérifier chaque cellule du tableau, notre formule INDEX EQUIV doit être une formule matricielle. L’indication visuelle la plus évidente d’une formule matricielle est {accolade} dans laquelle elle est encapsulée dans la barre de formule. N'oubliez pas d'appuyer sur le raccourcie clavier « Ctrl + Maj + Entrée » pour compléter correctement la formule une fois que vous avez fini de la taper dans la cellule D2.

Si tout est fait correctement, vous obtiendrez un résultat similaire à ce que vous voyez dans la capture d'écran ci-dessous :

Utilisation de INDEX / EQUIV avec SIERREUR dans Excel

Comme vous l'avez probablement remarqué (plus d'une fois), si vous entrez une valeur non valide, c'est-à-dire une valeur inexistante dans le tableau de recherche, la fonction INDEX / EQUIV génère un message d'erreur « # N / A » ou « #VALEUR ». Si vous préférez le remplacer par quelque chose de plus significatif, vous pouvez envelopper votre formule INDEX / EQUIV dans la fonction SIERREUR.

La syntaxe de la fonction SIERREUR est très simple :

SIERREUR (valeur ; valeur_si_erreur)

Où l'argument de valeur est la valeur vérifiée pour une erreur (le résultat de la formule INDEX EQUIV dans notre cas); et Valeur_si_erreur est la valeur à renvoyer si la formule génère une erreur.

Par exemple, vous pouvez inclure la formule de l'exemple précédent dans la fonction SIERREUR de la manière suivante :

=SIERREUR(INDEX($A$1:$E$11; EQUIV("UK";$B$1:$B$11;0); EQUIV(2015;$A$1:$E$1;0));"Aucune correspondance n'est trouvée. Veuillez réessayer !")

Et maintenant, si quelqu'un entre une entrée invalide, la formule produira le résultat que vous voyez dans la capture d'écran ci-dessous :

Si vous préférez avoir une cellule vide lorsqu'une erreur est renvoyée, vous pouvez simplement utiliser des guillemets doubles ("") dans le deuxième paramètre de la fonction SIERREUR, comme ceci :

SIERREUR(INDEX (matrice; EQUIV (valeur_recherchée ; tableau_recherche ; 0) ; "")