Trouver n’importe quelle donnée, n’importe ou: Les fonctions INDEX et EQUIV

Où sont passées mes données?
Où sont passées mes données?

 

Je vous avais parlé dans un article précedent de la fonction RechercheV (Vlookup en anglais) qui facilite énormément la recherche de données sur Excel. Dans cet article, on passe à la vitesse supérieure et je vous montre une technique de recherche de données bien plus puissante qui vous permet de tout faire : la combinaison des fonctions INDEX et EQUIV. RechercheV c’est faire du vélo avec les roulettes. Index + Equiv , c’est conduire votre première voiture. Je vous explique tout de suite comment ca marche:

Fonctionnement de la formule INDEX

La formule INDEX vous permet de retourner la valeur qui se trouve à la place que vous souhaitez dans une plage.

La syntaxe est :

=INDEX(Plage de valeurs; numéro de la ligne ou de la colonne souhaité)

On peut par exemple utiliser cette formule pour demander : quel est  le 3ème élément de la colonne D ?

Utilisation de la fonction Excel INDEX pour retrouver des valeurs dans un tableau
Exemple de l’utilisation de la fonction INDEX

En soit rien de magique, l’intérêt principal est que le  numéro de l’élément peut etre variable et être lui même le résultat d’une formule… C’est le rôle de la fonction EQUIV

Fonctionnement de la formule EQUIV

La formule EQUIV permet de retourner la place d’une valeur cherchée dans une plage de données.

La syntaxe à utiliser pour cette fonction est :

=EQUIV(Valeur cherchée; Plage de recherche; 0)

Le zéro est très important, il indique que vous cherchez une valeur exacte. Si vous spécifiez 1 ou rien du tout, Excel considère que la plage de recherche est triée par ordre croissant et s’arrête dès qu’il trouve une valeur plus grande que la cible. C’est problématique si la liste n’est pas triée (ce qui est souvent le cas).

On peut utiliser dans le tableau précédent la fonction EQUIV pour se demander: Quel est le numéro de l’Allemagne?

REtourver des données dans un tableau grace à la formule EQUIV
Utilisation de la formule EQUIV

Combinaison INDEX + EQUIV

EQUIV vous permet de calculer la position d’une valeur dans un plage, INDEX vous permet de retrouver la valeur qui se trouve à la même position, mais dans une autre plage, n’importe laquelle. Vous pouvez donc chercher la même position dans un tableau , ou meme la position d’une ligne dans une colonne si vous le souhaitez! C’est extrêmement puissant!

Utilisation des fonctions INDEX et EQUIV pour rechercher des données dans un tableau
Utilisation des fonctions INDEX + EQUIV

La syntaxe à utiliser pour combiner correctement ces formules est la suivante :

=INDEX(Plage de recherche; EQUIV(Valeur Cible; Plage d’origine ;0))

Pourquoi INDEX + EQUIV est la meilleure formule de recherche (comparée aux RechercheV et RechercheH)?

Il ne s’agit pas juste de savoir si c’est l’éléphant ou l’hippopotame le plus fort. Le choix des formules que vous utilisez à un véritable impact en terme d’utilisation de mémoire d’une part, et d’autre part dans le comportement de votre fichier en cas de modifications.

  • Contrairement aux RechercheV et RechercheH la plage de la valeur cible n’a pas besoin d’être la première du tableau. RechercheV vous permet de décaler vers la droite, INDEX+EQUIV vous permet aussi d’aller vers la gauche.
  • Dans RechercheV , le décalage entre les colonnes est un chiffre en dur ( 2 dans l’exemple ci dessous) . La formule ne marchera pas si vous rajoutez une colonne au milieu du tableau.
Recherche d'une valeur dans une plage de données avec la fonction Excel RechercheV
La recherche du même résultat avec une formule RechercheV
2 méthodes pour chercher des données dans un tableau
Effet du rajout de colonne sur une REchercheV et un INDEX EQUIV
  • Autre problème venant de ce chiffre en dur dans le RechercheV, c’est qu’il faut compter le décalage à la main. 2 cellules ca va, mais vous allez vous amuser avec un tableau de 140 colonnes .
  • En termes de mémoire, RechercheV prend comme argument toute la table de données,  INDEX EQUIV seulement 2 colonnes. C’est de moins en moins un problème avec la puissance des machines, mais ca peut vous sauver la vie sur une grosse table de bourrin.

 

Comments are closed.