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 ?
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?
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 + EQUIV
La syntaxe à utiliser pour combiner correctement ces formules est la suivante :
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.
La recherche du même résultat avec une formule RechercheV
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.
Bonjour,
Personnellement, je combine RechercheV + Equiv… ce qui m’évite de compter le nombre de colonnes à la main ou de modifier ma formule suite à un ajout de colonne.
Ainsi, avec une formule avec pour un même critère, je peux récupérer plusieurs éléments d’une grande source de données dans plusieurs cellule.
Excel, des formules et des milliers de possibilités…
Olivier.
« plusieurs cellules »… désolé pour la faute…
Si je devais faire la formule avec l’exemple ci-dessus, ça donnerait :
=recherchev(B10;$B$3:$D$7;EQUIV(A10;$B$2:$D$2;0);0)
où A10 contient un des éléments recherchés : nom du produit ou pays. Il s’agit de l’intitulé du tableau, repris au caractère près.
Bonne réception,
Bonjour Olivier,
Oui la plupart des cas sont solvables avec RechercheV, l’utilisation d’Equiv permet de contourner certaines limitations. Tu n’auras cependant pas autant de flexibilité et consommera plus de mémoire
Bonjour,
Alors, on en arrive à une question qui m’obstine depuis quelques temps et à laquelle je n’ai pas la réponse : comment évaluer les ressources nécessaires à un fichier ?
Il y a des tests que j’aimerais faire, mais ça reste difficile.
Et puis il y a des options qu’on prend parfois, et on a le sentiment que le résultat est optimisé. Sans certitude…
Par exemple, depuis peu, je nomme les formules un peu longues et compliquées que je crée dans le cadre de mon travail, et j’ai le sentiment que mes fichiers sont nettement plus réactifs. Est-ce un simple ressenti, ou est-ce vraiment le cas, je n’arrive pas à le déterminer.
Et tout récemment, j’ai découvert la fonctionnalité « Tableau » dans le ruban Insérer (ainsi que les segments). C’est quelque chose de vraiment génial…
Le plus fabuleux avec cet outil, c’est qu’on peut en découvrir tous les jours…
Bonne réception,
Olivier.
Bonjour Olivier
Effectivement connaitre les ressources nécessaires à l’exécution d’un fichier est une question intéressante, il faudra que je traite la question dans un prochain article!
Bonjour,
Je ne me considère pas aussi « expert » que beaucoup sur les forum, cependant le problème de chiffre en dur dans la rechercheV ou H peut être aisément contourné, et je m’en sert tout le temps, même avec des tableaux de 150 colonnes.
Il suffit « d’aller chercher » le numéro de colonne. Comment ?
En mettant en case C1 de votre exemple 1, en D1…2…etc….et lorsque vus insérez de nouvelles colonnes, il suffit de dire que le numéro de colonne à renvoyer est C$1 ou D$1…et cela fonctionne même en tirant les formules.
Autre cas selon l’utilisation, ce numéro en dur, enfin plus trop du coup, peut être le résultat d’une autre recherchev ou h
Finalement il n’est plus trop « dur » dans la formule, mais cela demande une gymnastique différente.
Certes cela rajoute une ligne de chiffre en 1ère ligne, mais cela facilite drôlement le travail lorsque des personnes utilisent le même fichier avec moins de connaissance en formule Excel.
Quoiqu’il en soit, pour ce que je veux faire je dis abandonner la recherchev et passer à l’index equiv, ou index recherchev, je vais voir ce qui marche le mieux.
@+
Nurbo
Bonjour,
très bon article sur lequel je me suis appuyé pour ma formule ci-dessous
=INDEX(‘\\serveur\repertoire\[AAAA0101_TEXTE_texte AAAA.xls]FEUIL1’!E13:E500;EQUIV(1;(‘\\serveur\repertoire\[AAAA0101_TEXTE_texte AAAA.xls]FEUIL1’!B13:B500=A9)*(‘\\serveur\repertoire\[AAAA0101_TEXTE_texte AAAA.xls]FEUIL1’!C13:C500=H3);0))
J’ai un fichier excel, disons fichier A.xls, sur lequel je renseigne les infos à chercher en A9 et H3, et la formule affiche la valeur correspondante trouvée entre E13:E500 du fichier B.xls qui est sur le réseau
Cette formule fonctionne parfaitement même si le fichier (B.xls) sur lequel je recherche les infos est en réseau et reste fermé.
Toutefois le (vrai) nom du fichier B.xls est composé d’un paramètre variable (c’est l’année AAAA), et comme j’utilise la formule à plusieurs reprises pour différentes recherches, j’aimerais que l’année soit une variable lue depuis la cellule A5 du fichier A.xls
en gros ça donnerait ceci en utilisant en plus INDIRECT dans ma formule
=INDEX(INDIRECT(A5& »!E13:E500″);EQUIV(1;(INDIRECT(A5& »!B13:B500=A9″))*(INDIRECT(A5& »!C13:C500=H3″));0))
mais malgré ça et le fait d’ouvrir le fichier en réseau B.xls, toujours rien
l’un d’entre vous aurait une idée
Merci d’avance
Bonjour Christophe
Je t’encourage à poser ta question sur le forum:http://forum.formuleexcel.com/
Tu pourras entre autre y uploader tes fichiers, ce sera plus pratique pour en voir le fonctionnement!