Voir le flux RSS

Pierre Fauconnier

[Actualité] CINQ bonnes raisons de préférer INDEX EQUIV à RECHERCHEV

Noter ce billet
par , 26/12/2017 à 16h41 (1063 Affichages)
Dans les illustrations qui suivent, j'ai rédigé la formule avec INDEX EQUIV et avec RECHERCHEV. Regardons ce qui se passe avec chaque formule lorsque l'organisation du tableau change...

Nom : 2017-12-26_063900.png
Affichages : 2842
Taille : 49,0 Ko



1. RECHERCHEV impose que la colonne de recherche, c'est-à-dire celle qui contient l'identifiant, soit la première colonne du tableau.

C'est d'ailleurs précisé dans l'aide sur la fonction, l'explicatif de l'assistant fonction ou l'infobulle de la bibliothèque du ruban. C'est une contrainte car si je crée le tableau moi-même, je peux encore m'arranger, mais si mon tableau est le résultat d'une importation ou a été créé par une autre personne, j'ai un problème. Le déplacement d'une colonne pour la mettre en première position n'est pas toujours sans conséquences désastreuses pour le classeur.

De même, si par la suite, quelqu'un modifie mon tableau et déplace une colonne en première position, Le couple INDEX EQUIV est passé au travers de la catastrophe qui secoue RECHERCHEV...

Nom : 2017-12-26_064450.png
Affichages : 2100
Taille : 52,0 Ko



2. Souvent avec RECHERCHEV, le numéro de la colonne contenant la valeur de retour est exprimé par une constante.


Si l'on veut "sécuriser" RECHERCHEV, il faut insérer deux fonctions pour le calcul de la position de la colonne, ce qui complexifie la formule et fait tomber l'argument que j'entends souvent lorsque je parle d'INDEX EQUIV: "Oui Pierre, mais INDEX EQUIV, c'est une fonction imbriquée dans une autre et ça c'est compliqué"...

Voyons ce qui arrive si j'insère une colonne avant la colonne du prix unitaire...

Nom : 2017-12-26_064854.png
Affichages : 2087
Taille : 49,9 Ko

RechercheV ne renvoie plus la bonne valeur, et si d'autres RechercheV utilisent les colonnes à droite de P.U, c'est la même catastrophe partout. Le Coupe INDEX EQUIV, lui, continue bien sagement à nous donner le bon résultat... On a évidemment le même problème si on permute P.U. et Taille, uniquement pour RechercheV, bien sûr.

Nom : 2017-12-26_065138.png
Affichages : 2083
Taille : 48,2 Ko

Pour éviter cela, il faudrait transformer la constante en variable, par exemple avec la fonction COLONNE()...

Nom : 2017-12-26_070100.png
Affichages : 2083
Taille : 53,8 Ko

Alors, l'insertion ou la permutation de colonnes ne pose plus de problèmes...

Nom : 2017-12-26_070203.png
Affichages : 2072
Taille : 52,1 Ko

Problème résolu? Oui, dans ce cas-ci. Remarquez au passage que l'argument d'une seule fonction face à INDEX EQUIV tombe ...

Tiens, et si je déplace mon tableau pour qu'il ne commence pas en A?

Nom : 2017-12-26_070345.png
Affichages : 2066
Taille : 49,2 Ko

Gloups... Quand RECHERCHEV pas content, lui toujours faire ainsi... INDEX EQUIV continue inlassablement à me donner le bon résultat. Il y a une solution, mais elle passe par l'insertion d'une deuxième fonction COLONNE() dans la formule?

Nom : 2017-12-26_070559.png
Affichages : 2061
Taille : 45,3 Ko

Ah, on arrive ENFIN à la même stabilité entre RECHERCHEV et le couple INDEX EQUIV, avec toujours la contrainte de la première colonne pour RECHERCHEV qui est inexistante pour INDEX EQUIV... Qui a dit que INDEX EQUIV était "compliqué"?



3. En cas de recherche d'une tranche de valeurs, RECHERCHEV ne permet de rechercher que le plancher d'une tranche.

RECHERCHEV et INDEX EQUIV permettent de chercher le plancher d'une tranche de valeurs, comme par exemple dans le cas d'une remise accordée en fonction d'une quantité vendue... Il suffit, pour RECHERCHEV, d'utiliser VRAI (ou de ne rien mettre) en quatrième argument, et pour EQUIV, d'utiliser 1 ou de rien mettre en troisième argument. Notez que dans les deux cas, les données doivent être triées par ordre croissant sur la colonne de recherche.

Nom : 2017-12-26_161314.png
Affichages : 2077
Taille : 57,9 Ko


Imaginons maintenant que nous devions trouver le coût supposé d'un risque. Le principe de précaution veut que l'on surestime le risque plutôt que le sous estimer... Comment faire avec RECHERCHEV()? Ce n'est tout simplement pas possible, alors qu'ici aussi, INDEX EQUIV va s'en sortir sans problèmes...

Nom : 2017-12-26_162946.png
Affichages : 2066
Taille : 40,1 Ko


4. Avec RECHERCHEV, on ne peut rechercher que la valeur (entière ou partielle) dans la colonne de recherche. Impossible de composer une matrice, soit pour la recherche, soit pour la récupération d'une valeur, soit pour les deux.

Et si vous avez le libellé et la taille comme valeur de recherche? Avec RECHERCHEV, ce n'est tout simplement pas possible, alors qu'INDEX EQUIV travaille en matricielle pour nous apporter la solution sans autre construction que la formule elle-même...

Nom : 2017-12-26_160537.png
Affichages : 2075
Taille : 45,4 Ko


Pour que cela puisse être possible avec RECHERCHEV, il va falloir construire une colonne calculée A GAUCHE du tableau. Pratique lorsque les données sont importées dans un ordre de colonnes bien précis et immuable...

Nom : 2017-12-26_160849.png
Affichages : 2067
Taille : 48,5 Ko


5. RECHERCHEV est plus lent que INDEX EQUIV.

Sur un tableau de 30.000 lignes de ventes recherchant le P.U d'un article dans une liste de 8.640 articles, une modification de prix entraîne une durée de recalcul de +/- 25 secondes pour RECHERCHEV contre +/- 5 secondes pour INDEX EQUIV.


Conclusions

Que ce soit lors de la construction ou la modification du classeur, INDEX EQUIV est fiable à 100% là où RECHERCHEV vous réserve quelques surprises de taille, pas toujours décelables de suite (les résultats sont rarement sur la même feuille que le tableau de données).

Alors, convaincu(e)? Qu'utilisez-vous comme formules pour l'instant? Connaissiez-vous INDEX EQUIV? Voyez-vous les avantages à utiliser ces deux fonctions combinées qui offrent une réelle sécurité sur la vie de votre classeur?

Belle fin d'année et à l'année prochaine pour de nouveaux billets

Envoyer le billet « CINQ bonnes raisons de préférer INDEX EQUIV à RECHERCHEV » dans le blog Viadeo Envoyer le billet « CINQ bonnes raisons de préférer INDEX EQUIV à RECHERCHEV » dans le blog Twitter Envoyer le billet « CINQ bonnes raisons de préférer INDEX EQUIV à RECHERCHEV » dans le blog Google Envoyer le billet « CINQ bonnes raisons de préférer INDEX EQUIV à RECHERCHEV » dans le blog Facebook Envoyer le billet « CINQ bonnes raisons de préférer INDEX EQUIV à RECHERCHEV » dans le blog Digg Envoyer le billet « CINQ bonnes raisons de préférer INDEX EQUIV à RECHERCHEV » dans le blog Delicious Envoyer le billet « CINQ bonnes raisons de préférer INDEX EQUIV à RECHERCHEV » dans le blog MySpace Envoyer le billet « CINQ bonnes raisons de préférer INDEX EQUIV à RECHERCHEV » dans le blog Yahoo

Commentaires

  1. Avatar de curt
    • |
    • permalink
    Bonjour Pierre,

    décidemment, tu nous gâte pour noël.
    La démonstration est remarque et géniale de facilité à comprendre.
    Excellentes fêtes de fin d'année.
    Curt
  2. Avatar de Pierre Fauconnier
    • |
    • permalink
    Salut Curt,

    C'est un plaisir, et je pense qu'avec ce billet, il y a de quoi convaincre les irréductibles utilisateurs de RECHERCHEV...

    Merci de me suivre, en tout cas, et je te souhaite plein de bonnes choses pour l'année 2018 et, bien sûr, de finir aussi 2017 en beauté...

    A bientôt...
  3. Avatar de patmicro
    • |
    • permalink
    Merveilleuse démonstration : Il est vraiment trop fort ce Mr Fauconnier. Il est vrai que la recherche V est utile pour de petits tableau et deviens tout de suite compliqué avec de grandes tables comme j'ai connu avec des tableaux de 40000 lignes voir plus. on voit tout de suite que la fonction INDEX EQUIV est la solution idéale. Je connaît cette fonction mais pas toujours le temps de la mettre en pratique qui pourtant fait gagner du temps et exclut le risque d'erreur. Pour avoir utilisé très souvent la fonction Recherche V le temps de calcul est considérable. Convaincu de cette démonstration J'abandonne Recherche V au profit de INDEX EQUIV.

    Continuez à nous poster des sujets de ce type

    Très bonnes fêtes à vous et à tous les lecteurs

    et
  4. Avatar de Malick
    • |
    • permalink
    Salut

    Merci Pierre pour ces billets de très bonne qualité.

    Bonne et heureuse année 2018 avec pleins d'autres billets du genre.

    Malick
  5. Avatar de Pierre Fauconnier
    • |
    • permalink
    Merci Pat pour l'appréciation. Effectivement, INDEX EQUIV est pour moi une bien meilleure solution que RECHERCHEV, qui permet notamment de rechercher une valeur dans une matrice rectangulaire. J'en parlerai dans un prochain billet.

    2018 prolongera donc ce qui a été amorcé en 2017, avec des billets que j'espère pertinents. N'hésitez pas à commenter si vous souhaitez que je détaille sur le blog une technique ou fonction que vous voudriez utiliser.

    Meilleurs voeux, et que 2018 soit EXCEL...lente
  6. Avatar de Pierre Fauconnier
    • |
    • permalink
    Merci Malick,

    Et merci aussi pour ta disponibilité et ta collaboration.

    Au plaisir, pourquoi pas, ne nous rencontrer "IRL" ...
  7. Avatar de Pierre Dumas
    • |
    • permalink
    Merci Pierre pour cet article.

    Sache que tu n'es pas le seul à prêcher la bonne nouvelle d'INDEX-EQUIV par rapport à RECHERCHEV. Encore ce matin, cela m'est arrivé.

    Je continue juste d'informer les gens avec RECHERCHEV afin qu'ils restent "compatibles" avec leurs collègues (en attendant de les convertir).

    Bonne année à tous

    Pierre
  8. Avatar de Pierre Fauconnier
    • |
    • permalink
    Bonjour Pierre,


    Citation Envoyé par Pierre Dumas
    Merci Pierre pour cet article.[...]
    Pas de quoi. Il faudra du temps pour que RECHERCHEV disparaisse des pratiques, et il est dès lors important d'en comprendre le fonctionnement pour l'éventualité où l'on tombe dessus dans des classeurs tiers, mais petit à petit, en appuyant sur les avantages INDEX-EQUIV, on y arrivera...

    Bonne année à toi aussi
  9. Avatar de laurent_ott
    • |
    • permalink
    Bonjour Pierre.
    Mois aussi j'ai aimé cette documentation, car comme beaucoup d'entre nous j'utilise encore RechercheV au lieu de INDEX EQUIV qui est pourtant bien plus performant comme tu le démontres ici.
    Les habitudes sont tenaces. Justement, j'ai une remarque à formuler : les anciens qui n'ont pas intégré dans leurs habitudes les "nouveautés" d'EXCEL 2007, comme moi, n'utilisent pas les "tableaux de données" (il m'a fallu ton intervention pour me mettre sur la bonne voie), et se contentent des "plages ordinaires". Or dans ta documentation tu utilises toujours des tableaux de données, mais tu n'expliques pas comment transformer (simplement) une plage ordinaire (importée d'une base externe par exemple) en tableau de données : je pense que cela risque de déstabiliser certains utilisateurs qui ne vont pas forcément comprendre la syntaxe des formules utilisées. A mon avis, tu pourrais faire un exemple avec l'usage des plages ordinaires.
    De même, j'ai vu "{" dans un exemple, sans que tu expliques comment cela est produit (voir ta documentation précédente pour l'explication).

    Merci pour cette documentation qui va me faire changer mes mauvaises habitudes.
  10. Avatar de Pierre Fauconnier
    • |
    • permalink
    Salut Laurent,

    Merci pour ton appréciation, ça fait plaisir

    Pour les tableaux, je vais rédiger un petit billet reprenant rapidement les avantages d'utiliser les tables de données (tableaux structurés) et comment créer rapidement cela avec Excel, en partant de rien ou en partant d'une plage existante.

    Pour les accolades, je suppose que tu parles de ce billet. J'y explique rapidement la validation matricielle qui fait apparaître ces accolades de part et d'autre de la formule, mais cela mériterait un billet spécifique, voire un tuto.

    Je vais y penser et m'y mettre une de ces nuits...

    A +, et merci pour tes tutos sur le VBA
  11. Avatar de aldorhyn
    • |
    • permalink
    Un seul mot :

    MERCI, MERCI, MERCI !!

    bon,... ça en fait 3, mais c pas grave, ça les vaut bien.

    grâce à ce billet, je viens ENFIN de comprendre l'utilisation d'INDEX, d'EQUIV et leur application imbriquée pour la recherche.
    Utilisateur invétéré depuis de nombreuses années de rechercheV dans des kyrielles de classeurs, c'était à chaque fois la galère dès que je voulais modifier quelque chose dans mes tables....
    Je n'arrivais pas à comprendre la logique.
    l'utilisation de tableaux plutôt que de plages facilite aussi beaucoup les choses (c'est une autre mauvaise habitude qu'il me faut changer...)

    Ca semble tellement évident après coup, que j'en ai presque honte... .

    bon, bé, c'est bon, je suis revenu du côté obscur...
  12. Avatar de Pierre Fauconnier
    • |
    • permalink
    Salut,


    Citation Envoyé par aldorhyn
    Un seul mot :

    MERCI, MERCI, MERCI !!

    bon,... ça en fait 3, mais c pas grave, ça les vaut bien.

    grâce à ce billet, je viens ENFIN de comprendre l'utilisation d'INDEX, d'EQUIV et leur application imbriquée pour la recherche.[...]
    Pas de quoi! Ca me fait plaisir de savoir que mes billets sont intéressants, font gagner du temps et permettent une meilleure utilisation d'Excel.

    Bonne continuation