On a souvent besoin de récupérer des valeurs dans un tableau à partir d'un des éléments. Par exemple, dans un tableau contenant des références et des prix, comment connaître le prix sachant la référence ? Les fonctions RechercheH et RechercheV peuvent être utilisées dans ce but mais elles présentent des limitations importantes, notamment sur l'ordre des colonnes ou des lignes. La combinaison des fonctions INDEX et EQUIV peut sembler d'abord plus complexe mais elle s'avère bien plus puissante.
INDEX(Tableau_de_référence;ligne;colonne)
Cette fonction permet de renvoyer la valeur d'une cellule se trouvant à l'intersection d'une ligne et d'une colonne dans un tableau.
Avec un tableau nommé Tablo (insertion/nom/définir ou avec xl2007 formules/définir un nom)
index(Tablo;4;2) renvoie la valeur située à l'intersection de la 4° ligne et de la 2° colonne par rapport à la cellule de référence du tableau. Par définition cette cellule est toujours celle qui est située le plus en haut et à gauche de votre tableau.
Si votre tableau est en C18:E26, =index(C18:E26;4;2) qu'il est préférable d'écrire =index(tablo;4;2) pour faciliter la lecture renvoie la valeur contenue en D21. Si votre tableau n'a qu'une colonne, le dernier paramètre peut être omis.
EQUIV(valeur_cherchée;tableau de recherche;type)
La fonction EQUIV recherche un élément spécifique dans une plage de cellules et renvoie sa position relative. Par exemple, si la plage B4:B8 nommée "prénoms" contient Pierre, Paul, Jacques, André, =EQUIV("Paul";B4:B8;0) qu'on préferera également écrire pour faciliter la lecture = EQUIV("Paul";prénoms;0) renvoie 2, Paul étant le 2° élément de ce tableau.
Le troisième argument de la fonction est important. Dans 99% des cas, on recherche une valeur exacte dans un tableau et ce paramètre doit être mis à 0. Malheureusement, le paramètre par défaut de cette fonction est 1 qui permet de rechercher la valeur la plus élevée qui est inférieure ou égale à la valeur_cherchée, pour autant que le tableau soit auparavant trié (si si relisez, c'est compliqué mais compréhensible !). L'aide en ligne donne des exemples d'utilisation des types 1 et -1 dont nous ne parlerons pas ici.
Retenez : Il est ESSENTIEL de TOUJOURS préciser le dernier paramètre de EQUIV et de le mettre à 0. Il n'est pas nécessaire de trier le tableau dans ce cas et c'est un des gros avantage de l'utilisation du couple INDEX et EQUIV pour extraire des données d'un tableau.
Si vous recherchez un texte, comme ici "Paul", vous pouvez utiliser les caractères génériques, à savoir l’astérisque et le point d’interrogation dans la valeur_cherchée. Le point d’interrogation correspond à un caractère et l’astérisque à une séquence de caractères. Si vous voulez rechercher un véritable point d’interrogation ou astérisque, tapez "~?" ou "~*"
=INDEX(tableau_contenant _la_valeur_à_renvoyer;EQUIV(valeur_recherchée;Tableau_contenant_la_valeur_recherchée;0))
Nous allons maintenant combiner ces deux fonctions pour
1) rechercher dans un tableau la position d'un élément (EQUIV)
2) utiliser cette position pour extraire la valeur de l'élément INDEX
Dans une feuille j'ai trois colonnes : en A des noms, en B les prénoms correspondants, en C les ages correspondants.
Pour que les formules soient plus faciles à comprendre, commencez par nommer ces trois plages (normalement elles ont le même nombre de lignes). Rappel, si ces données sont appellées à être modifiées, vous pouvez mettre à profit l'usage de la fonction DECALER pour rendre vos plage dynamiques.
Comment trouver l'age de Pierre ?
Dans ce tableau de trois colonnes, l'age de Pierre se trouve sur la même ligne que son prénom. On va donc extraire cette position dans le tableau avec EQUIV : =EQUIV("Pierre";prenoms;0) puisqu'on recherche Pierre dans la liste des prénoms.
On va ensuite injecter cette valeur pour renvoyer dans la liste des ages, la valeur de l'élément ayant la même position.
=INDEX(ages;equiv("Pierre";prenoms;0)).
Sur la même feuille (ou une autre, peu importe), par exemple en colonnes K etL vous avez les noms des concurrents d'une course, et à côté le classement qu'ils ont obtenu. Vous souhaitrez étudier si l'age influence le classement. Pour cela vous allez rechercher dans les trois premières colonnes l'age correspondant au nom du concurrent et le sur la même ligne en colonne M. Nommez Concurrents et Classement les plages correspondantes. Celles-ci n'ont pas nécessairement le même nombre de lignes que les 3 premières. Il faut bien entendu que les noms des concurrents soient présents dans la liste des noms mais tous les noms ne sont pas nécessairement dans la liste des concurrents et surtout, aucune des deux listes n'est triée et n'est dans le même ordre.
en M2, écrivez =INDEX(ages;EQUIV(K2;noms;0))
Avec cette combinaison de fonctions, sans oublier le 0 de EQUIV, il n'y a aucune contrainte sur l'ordre de recherche des colonnes (alors qu'avec RechercheV on ne peut pas rechercher à partir de la colonne K des éléments se trouvant dans une colonne située à gauche de cette colonne ce qui est très limitant.
La fonction INDEX
INDEX(Tableau_de_référence;ligne;colonne)Cette fonction permet de renvoyer la valeur d'une cellule se trouvant à l'intersection d'une ligne et d'une colonne dans un tableau.
Avec un tableau nommé Tablo (insertion/nom/définir ou avec xl2007 formules/définir un nom)
index(Tablo;4;2) renvoie la valeur située à l'intersection de la 4° ligne et de la 2° colonne par rapport à la cellule de référence du tableau. Par définition cette cellule est toujours celle qui est située le plus en haut et à gauche de votre tableau.
Si votre tableau est en C18:E26, =index(C18:E26;4;2) qu'il est préférable d'écrire =index(tablo;4;2) pour faciliter la lecture renvoie la valeur contenue en D21. Si votre tableau n'a qu'une colonne, le dernier paramètre peut être omis.
La fonction EQUIV
EQUIV(valeur_cherchée;tableau de recherche;type)La fonction EQUIV recherche un élément spécifique dans une plage de cellules et renvoie sa position relative. Par exemple, si la plage B4:B8 nommée "prénoms" contient Pierre, Paul, Jacques, André, =EQUIV("Paul";B4:B8;0) qu'on préferera également écrire pour faciliter la lecture = EQUIV("Paul";prénoms;0) renvoie 2, Paul étant le 2° élément de ce tableau.
Le troisième argument de la fonction est important. Dans 99% des cas, on recherche une valeur exacte dans un tableau et ce paramètre doit être mis à 0. Malheureusement, le paramètre par défaut de cette fonction est 1 qui permet de rechercher la valeur la plus élevée qui est inférieure ou égale à la valeur_cherchée, pour autant que le tableau soit auparavant trié (si si relisez, c'est compliqué mais compréhensible !). L'aide en ligne donne des exemples d'utilisation des types 1 et -1 dont nous ne parlerons pas ici.
Retenez : Il est ESSENTIEL de TOUJOURS préciser le dernier paramètre de EQUIV et de le mettre à 0. Il n'est pas nécessaire de trier le tableau dans ce cas et c'est un des gros avantage de l'utilisation du couple INDEX et EQUIV pour extraire des données d'un tableau.
Si vous recherchez un texte, comme ici "Paul", vous pouvez utiliser les caractères génériques, à savoir l’astérisque et le point d’interrogation dans la valeur_cherchée. Le point d’interrogation correspond à un caractère et l’astérisque à une séquence de caractères. Si vous voulez rechercher un véritable point d’interrogation ou astérisque, tapez "~?" ou "~*"
Combinaison de INDEX+EQUIV
=INDEX(tableau_contenant _la_valeur_à_renvoyer;EQUIV(valeur_recherchée;Tableau_contenant_la_valeur_recherchée;0))Nous allons maintenant combiner ces deux fonctions pour
1) rechercher dans un tableau la position d'un élément (EQUIV)
2) utiliser cette position pour extraire la valeur de l'élément INDEX
Dans une feuille j'ai trois colonnes : en A des noms, en B les prénoms correspondants, en C les ages correspondants.
Pour que les formules soient plus faciles à comprendre, commencez par nommer ces trois plages (normalement elles ont le même nombre de lignes). Rappel, si ces données sont appellées à être modifiées, vous pouvez mettre à profit l'usage de la fonction DECALER pour rendre vos plage dynamiques.
Comment trouver l'age de Pierre ?
Dans ce tableau de trois colonnes, l'age de Pierre se trouve sur la même ligne que son prénom. On va donc extraire cette position dans le tableau avec EQUIV : =EQUIV("Pierre";prenoms;0) puisqu'on recherche Pierre dans la liste des prénoms.
On va ensuite injecter cette valeur pour renvoyer dans la liste des ages, la valeur de l'élément ayant la même position.
=INDEX(ages;equiv("Pierre";prenoms;0)).
Utiliser INDEX+EQUIV pour combiner deux tableaux en un seul.
Sur la même feuille (ou une autre, peu importe), par exemple en colonnes K etL vous avez les noms des concurrents d'une course, et à côté le classement qu'ils ont obtenu. Vous souhaitrez étudier si l'age influence le classement. Pour cela vous allez rechercher dans les trois premières colonnes l'age correspondant au nom du concurrent et le sur la même ligne en colonne M. Nommez Concurrents et Classement les plages correspondantes. Celles-ci n'ont pas nécessairement le même nombre de lignes que les 3 premières. Il faut bien entendu que les noms des concurrents soient présents dans la liste des noms mais tous les noms ne sont pas nécessairement dans la liste des concurrents et surtout, aucune des deux listes n'est triée et n'est dans le même ordre.en M2, écrivez =INDEX(ages;EQUIV(K2;noms;0))
Avec cette combinaison de fonctions, sans oublier le 0 de EQUIV, il n'y a aucune contrainte sur l'ordre de recherche des colonnes (alors qu'avec RechercheV on ne peut pas rechercher à partir de la colonne K des éléments se trouvant dans une colonne située à gauche de cette colonne ce qui est très limitant.
Cette page a été vue 1726 fois.