Introduction : Index & Equiv

Lorsque l’on commence à utiliser et à apprendre Excel, où bien que l’on en discute rapidement dans le cadre professionnel ou privée, le terme RECHERCHEV revient inlassablement, présenté comme une fonction centrale à la bonne maîtrise d’Excel. Or, RECHERCHEV n’a rien d’impressionnant et relève par ailleurs d’une pratique suboptimale. La combinaison des fonctions INDEX EQUIV relève d’une bien meilleure pratique puisqu’elle nous permettra d’aboutir au même résultat sans avoir à supporter les nombreux défauts et limites de la RECHERCHEV.

RECHERCHEV

Rappel de la syntaxe de la RECHERCHEV

Afin de clarifier la terminologie employée et en guise de rappel, ci-dessous la syntaxe de la fonction RECHERCHEV() accompagnée de quelques définitions :

=RECHERCHEV( valeur_cherchée ; table_matrice ; no_index_col ; [valeur proche])

• valeur_cherchée : valeur qui, en se comparant aux valeurs contenues dans la première colonne de table_matrice, va nous permettre d’identifier notre entrée (ligne) où se trouve notre Valeur de Retour.

  Exemple : une référence client, le nom d’une personne

• table_matrice : tableau de donnée comportant d’une part notre valeur cherchée (si elle existe)  et d’autre part notre valeur visée (Valeur de Retour)
• no_index_col : le numéro de la colonne au sein de la table_matrice contenant la valeur visée. La première colonne a pour valeur 1 et le numérotage se fait de la gauche vers la droite.
• [valeur proche] : valeur facultative prenant la valeur VRAI (1) ou FAUX (0). Si VRAI, cela indique à Excel que la fonction peut renvoyer un résultat approximatif. Par défaut, la valeur est définie à VRAI. Dans  la majorité des cas, un retour est souhaité uniquement si la correspondance est exacte et ce sera donc le paramètre FAUX (ou 0) qui devra être renseigné.

Avec :

• Valeur visée (Valeur de Retour) : valeur retournée par une fonction de recherche (tel RECHERCHEV ou bien INDEX/EQUIV)

L’article n’ayant pas pour objectif l’enseignement de la fonction RECHERCHEV, nous vous invitons à consulter notre article sur la RECHERCHEV si vous désirez en savoir plus.

Limites de la RECHERCHEV

Dans sa façon de fonctionner et d’être rédigée, la fonction RECHERCHEV entraîne deux contraintes limitantes : d’une part, l’obligation de réaliser la recherche dans la première colonne de la matrice renseignée, et d’autre part, la nécessité de renseigner le numéro de colonne, qui est fréquemment “coder en dur” – directement ou indirectement.

La première contrainte – rechercher obligatoirement dans la première colonne de la table matrice – entraîne une limite très dérangeante puisqu’il devient alors impossible que la Valeur de Retour soit contenue dans une colonne située à la gauche de la colonne de recherche. La seule solution est alors de modifier la structure de votre tableau de donnée (en positionnant la colonne de recherche à gauche), ce que vous voudrez absolument éviter pour des raisons de risques de dysfonctionnement futures mais aussi de perte de temps.

La seconde contrainte – coder en dur le numéro la colonne comportant la valeur de retour – peut rapidement rendre votre expérience de la RECHERCHEV fastidieuse. Puisque votre valeur est “figée”, elle ne sera pas modifiée si une colonne venait à être ajoutée à votre table de donnée. Si vous renseignez le nombre 4 pour la caractéristique “age”, Excel ne retiendra que le nombre 4. Si la colonne âge, suite à l’ajout d’une colonne, se retrouve en 5ème ou 6ème position, votre RECHERCHEV vous retournera certes un résultat, mais plus du tout celui attendu.

Ces deux raisons sont pour nous suffisantes pour vous pousser à vous pencher sur une techniques relevant d’une bien meilleure pratique : l’association des fonctions INDEX() et EQUIV(). Outre les deux limites mentionnées ci-dessus, INDEX-EQUIV propose de nombreux avantages supplémentaires. Pour autant, la fonction RECHERCHEV() n’est pas à effacer de votre palette technique : elle doit devenir l’exception plutôt que la règle.

Le fonctionnement de INDEX EQUIV

Présentation formule EQUIV()

La fonction EQUIV d’Excel permet de rechercher une valeur dans une matrice et de renvoyer sa position. C’est une fonction de recherche qui s’applique tout aussi bien aux matrices verticales que horizontales.

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

• valeur_cherchée : valeur recherchée dans la matrice, pour laquelle nous voulons identifier le positionnement
• tableau_recherche : matrice colonne dans laquelle la recherche est effectuée
• [type]: (entre crochet donc facultatif) peut prendre la valeur -1, 0 ou 1 permettant d’indiquer le type de recherche que doit effectuer la fonction EQUIV(). Les valeurs -1 et 1 permettent d’effectuer des recherches acceptant une approximation tandis que la valeur 0 oblige une correspondance exacte entre la valeur cherchée et la valeur trouvée. Cet argument est similaire à celui de RECHERCHEV.

Exemple

Nous voulons déterminer la position de “Lapin” dans la liste d’animaux.

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

=EQUIV( “Lapin” ; B2:B9 ; 0 ) 
= 5

La valeur “Lapin” étant en 5^ème position dans la matrice B2:B9, la fonction EQUIV() retourne la valeur 5. Ici, nous voulons une recherche sans approximation et nous donnons donc une valeur de 0 à l’argument [type].

Premier bras de la combinaison INDEX() / EQUIV(), la fonction EQUIV() permet de rechercher une valeur dans une matrice, c’est-à-dire déterminer le positionnement de la ligne où la valeur est située.

Présentation INDEX()

En quelques mots, en renseignant à INDEX() une matrice, un numéro de ligne et un numéro de colonne, la fonction vous retourne la valeur située au croisement de la ligne et de la colonne renseignées.

=INDEX( matrice ; no_lig ; [no_col] )

• matrice : tableau de donnée comportant la valeur que l’on veut retourner. Peut être composé d’une seule ou de plusieurs colonnes
• no_lig : le numéro de la ligne contenant la valeur visée
• [no_col]: le numéro de la colonne contenant la valeur visée

Vous remarquerez que [no_col] est un argument facultatif. En effet, il est nécessaire de le renseigner uniquement si la matrice fournit comporte plus d’une colonne.

Exemples :

Cas 1 : matrice comportant plusieurs colonnes

Nous voulons retourner le prénom “Marc”

=INDEX( matrice ; no_lig ; [no_col] )

=INDEX( C5:G7 ; 1 ; 4 )

Cas 2 : matrice comportant une seule colonne

Nous voulons retourner le prénom “Elisabeth”.

=INDEX( matrice ; no_lig ; [no_col] )

=INDEX( D5:D19 ; 5 )

Nous renseignons la valeur 5 pour no_lig puisque le prénom Elisabeth est en 5^ème position dans la matrice D5:D19. Ici, puisqu’il s’agit d’un tableau à une seule colonne, il n’est pas nécessaire de renseigner l’argument [no_col].

Imbrication de EQUIV() dans INDEX()

Nous venons de voir séparément les deux fonctions que sont INDEX() et EQUIV(). Afin d’aboutir à une fonction de recherche il va falloir maintenant les imbriquer.

=INDEX( matrice_de_retour ; EQUIV( valeur_cherchée ; tableau_recherche ; [type]) )

Exemple

Dans le tableau ci-dessous, nous voulons déterminer à l’aide d’une fonction de type INDEX/EQUIV le prix du produit dont le code produit est 7.

Notre fonction :
= INDEX( matrice_de_retour ; EQUIV( valeur_cherchée ; tableau_recherche ; [type]) )

= INDEX( E3:E13 ; EQUIV( 7; C3:C13 ; 0 ) )

= INDEX( E3:E13 ; 4 )

= 11

Décomposons les différentes étapes.

Partie 1 : EQUIV()

EQUIV( 7 ; C3:C13 ;  0 )

La fonction nous permet d’identifier la position de la valeur 7 dans la matrice C3:C13. Dit autrement, la fonction retourne le numéro de la ligne du code produit 7. Si cette fonction était exécutée de manière isolée, la valeur renvoyée par cette fonction serait donc le nombre 4 puisque le code produit 7 est situé en 4^ème ligne de la matrice colonne C3:C13.

Pour simplifier, C3:C13 constitue la matrice colonne où est effectuée la recherche. Nous voulons le prix pour un certain code produit, nous avons donc cherché (et trouvé) la ligne correspondant à ce code produit.

Partie 2 : INDEX()

Si l’on remplace l’ensemble de la fonction EQUIV() par la valeur qu’elle retourne, nous avons les étapes de calculs suivantes :

Avant l’exécution de EQUIV() :

= INDEX( E3:E13 ; EQUIV( 7; $C$3:$C$13;  0 ) )

Après l’exécution de EQUIV() :

= INDEX( E3:E13 ; 4 )

Ce que fait INDEX(), c’est de retourner la valeur situé en 4^ème ligne de la matrice colonne comportant les prix E3:E13. La valeur retournée est égale à 11 qui est le prix associé au code produit 7.

Les avantages supplémentaire à l’utilisation de la fonction INDEX/EQUIV.

Une exécution plus rapide

Lorsque votre base de donnée ne comporte que quelques lignes, la différence de vitesse n’est pas notable. En revanche, avec plusieurs centaines voir des milliers de lignes, la fonction INDEX/EQUIV s’exécute beaucoup plus rapidement que RECHERCHEV. Et on ne dis jamais non à un gain de temps.

Décorréler la recherche et la valeur de retour

La combinaison INDEX/EQUIV permet de décorréler la partie de la fonction effectuant la recherche (EQUIV) et la partie renvoyant la valeur de retour (INDEX). Le premier avantage est la possibilité de diminuer les calculs réalisés par Excel dans les situations où nous voulons extraire plusieurs données d’une même ligne. En effet, il suffira d’effectuer la fonction de recherche (EQUIV) dans une seule colonne puis d’exécuter indépendamment plusieurs fonctions de retour (INDEX) dans plusieurs colonnes. Ainsi, au lieu d’effectuer la recherche à chaque donnée extraite d’une même ligne, Excel n’effectue la recherche qu’une seule fois. La feuille xxx vous permettra de pleinement comprendre le procédé.

Une combinaison parfaite avec les références structurées

Lorsque vous utilisez des références structurées, les tables et les colonnes sont nommées et peuvent, lorsque vous êtes en train de rédiger une fonction sous Excel, être appelées avec un langage intelligible (contrairement à des plages de cellules de type L1C1). Et cela s’agence particulièrement bien dans le cadre de la fonction INDEX/EQUIV où tout ce que nous avons à fournir peut se faire via ces références structurées.

Exemple : voir feuille IE – Avantages du fichier Excel en support de cet article.