La fonction Equiv()

Cet article complète la série des articles publiés récemment sur les fonctions de recherche d’Excel :
 

22 mars 2020 : La fonction Index()

29 mars 2020 : Index() avec des trous

06 avril 2020 : La vie de la fonction Recherche()

13 avril 2020 : Les fonctions RechercheV et …H

Syntaxe de la fonction Equiv()

La syntaxe de la fonction est : =equiv(valeur_cherchée ;vecteur_recherche[;type])

Une fois de plus, ne vous fiez pas à la syntaxe proposée par Excel (cf ci-dessous) quand vous avez saisi =equiv(. En effet, cette syntaxe comporte deux erreurs. 

Premièrement, ce n’est pas un tableau de recherche, ce qui ferait a priori penser à une matrice, mais en fait un vecteur de recherche.

Ensuite, comme dans pratiquement toutes les syntaxes proposées par Excel pour les fonctions avec au moins un argument facultatif, le [ est mal placé. Si vous mettez le dernier « ; » sans le faire suivre par un type, vous obtenez une erreur. C’est donc la syntaxe que je vous propose ci-dessus qui est la bonne !

Utilisation courante de Equiv()

Dans l’utilisation courante de la fonction, equiv(), on recherche la valeur_cherchée dans le vecteur indiqué. Si l’argument est 1 (ou s’il est omis), on ramène la position dans le vecteur de la dernière valeur inférieure ou égale à valeur_cherchée . Dans notre exemple, quand on cherche le 25 de la cellule E2 dans le vecteur G1:G91 avec le paramètre 1, on trouve bien 4 qui est la position dans la colonne G de la dernière valeur inférieure ou égale à 25.

Si l'argument est 0, on cherche la position de la première valeur égale à celle que l'on cherche.

Si l'argument est -1, on cherche la position de la dernière valeur supérieur ou égale à celle que l'on cherche.

Une utilisation originale de Equiv()

Quand on a bien compris le fonctionnement d’Equiv(), on peut utiliser cette fonction pour atteindre des objectifs originaux. Supposons par exemple que nous souhaitions obtenir la position de la dernière valeur renseignée dans la colonne C, sachant que cette colonne ne peut recevoir que des valeurs positives.

La solution de la cellule E9 s’impose alors avec évidence : la dernière ligne renseignée est tout simplement la dernière ligne avec une valeur supérieure à -1 !

Remarque – Si la colonne C pouvait aussi recevoir des valeurs négatives, il suffirait alors d’utiliser la formule =equiv(10^-20;C:C;-1) à condition que toutes les valeurs de la colonne C soient supérieures ou égales à 10^-20…

Tirez profit du Covid-19 !

Nous sommes tous contraints par le Covid-19. Le seul côté positif, si l’on peut dire, est que l’on a – souvent bien malgré soi ! – retrouvé du temps libre. Vous pouvez profiter de ce temps pour essayer de progresser sur Excel, ce qui finalement vous fera gagner du temps dans l’avenir.

J’anime depuis plus de 10 ans au MBA HEC un électif baptisé « Advanced Excel Modelling ». C’est à ma connaissance le seul électif dont l’effectif est toujours plein dès le soir du premier jour où il est proposé aux étudiants. C’est un électif de 6 séances de 3 heures chacune qui correspond à la formation en 2 jours « Modélisation avec Excel » que je propose depuis des années via Logma, la société de conseil que j’ai créée il y a 40 ans et qui compte aujourd’hui une dizaine de consultants, dont les deux seuls consultants en France qui soient MVP Excel de Microsoft (Frédéric le Guen et votre serviteur).

Un premier tableau de bord

Je débute cette formation par la création par les élèves d’un petit tableau de bord assez simple puisque la seule fonction Excel qu’il utilise est la fonction Somme(). Un bon tableau de bord doit être en effet assez  simple pour qu’un patron puisse le comprendre :)

Je donne 10 minutes à mes élèves pour réaliser ce qu’ils peuvent. S’ils terminent avant, ils me font signe et je vais vérifier leur travail.  Ensuite, je le réalise devant eux, sans rien leur expliquer. D’expérience, je le fais 3 fois plus rapidement que le temps moyen des 4 élèves les plus rapides. Enfin, je passe un certain temps à le refaire pas à pas en expliquant toutes les astuces.

Un jour, un participant m’a dit : « Vous avez économisé quelques minutes, cela vous fait une belle jambe ! » Je lui ai répondu que, de la même façon, je pouvais développer en un jour ce qui aurait pris 3 jours aux 4 meilleurs d’entre eux…

Mes formations habituelles

Si vous cliquez dans le bouton « Prochaines formations » dans le bandeau droit de ce blog, vous pourrez voir la liste complète des formations que je propose. Les trois formations qui ont le plus de succès sont les formations « Modélisation avec Excel » et « Création de tableau de bord sous Excel » et « La modélisation du risque (Crystal Ball) ».

A titre indicatif, la formation « Modélisation avec Excel », outre qu’elle marche très fort au MBA d’HEC, a déjà été animée en séminaire intra, pour ne citer que quelques exemples, chez Aéroports de Paris, Arianespace, Aventis, Banques Populaires, Bouygues, Cegelec, CNES, Dassault Systèmes, EdF, EADS, Elf, Ernst & Young, Finacor, France Telecom, HSBC, MBDA, Sacilor, Sanofi, SKF et Total.

Mon offre exceptionnelle « Covid-19 »

Quand un participant s’inscrit à une formation collective (effectif maximum de 8 personnes), il est facturé 800 € HT pour une journée et 1.500 € HT pour deux jours.

Ma proposition exceptionnelle, valable durant le temps du confinement, est la suivante. Je vous propose un cours particulier – à distance bien sûr ! – d’une journée au tarif de 800 € HT, c’est-à-dire le même prix que vous payeriez pour participer à un cours collectif.

Vous pouvez choisir vous-même le sujet de cette formation, à prendre dans mon catalogue, par exemple la moitié de la formation « Modélisation avec Excel ».

La seule condition est que cette formation soit payée d’avance. En effet, pour une telle proposition, je ne peux pas envisager de perdre du temps à récupérer le règlement de la journée.

Si cela vous intéresse, contactez-moi à : thiriez@hec.fr.

Les fonctions RechercheV et RechercheH

Les fonctions RechercheV() et RechercheH() ont été créées pour ajouter de la flexibilité à la fonction Recherche() en permettant à l’utilisateur de choisir la position de la colonne (ou ligne) fournissant le résultat. En plus, elles ne dépendaient plus de la forme de la matrice (verticale ou horizontale), ce qui troublait certains utilisateurs.

La première syntaxe de RechercheV ou …H

La syntaxe de RechercheV était initialement =recherchev(valeur;matrice;n°colonne). En fait, elle fonctionnait comme la fonction recherche() sur une matrice carrée ou verticale, mais en ramenant la valeur de la colonne sélectionnée plutôt que celle de la dernière colonne.

Cela correspondait à la demande de nombreux utilisateurs. Mais une autre demande de nombreux utilisateurs restait insatisfaite, à savoir comment faire pour exiger que la valeur cherchée soit égale à une valeur de la première colonne.

Nous voyons dans l’image suivante, dans la cellule B14, ce qui se passe quand on fait un simple recherchev() pour trouver le nom de la personne à partir de son prénom. Comme il n’y a pas de Jules, Excel renvoie le nom de la personne ayant le dernier prénom antérieur à Jules.

Certes, on pouvait résoudre le problème comme cela a été fait en B15. Mais plus de 90% des utilisateurs d’Excel en étaient totalement incapables.

La solution utilisée en B16, grâce à la seconde syntaxe de RechercheV (avec faux), résout le problème plus aisément !

La seconde syntaxe de RechercheV ou …H

En effet, la seconde syntaxe de RechercheV (ou …H) comporte un dernier argument. Quand ce dernier argument est faux, un message d’erreur s’affiche si le prénom demandé n’appartient pas à la liste des prénoms.

Il y a une différence fondamentale quand on utilise ce dernier argument : Microsoft utilise alors un autre algorithme que l’algorithme traditionnel de Recherche().

L’ancien algorithme compare la valeur cherchée à la valeur courante dans la colonne de recherche et, dès que la valeur cherchée est inférieure à la valeur courante, s’arrêt et remonte d’une ligne pour fournir le résultat. Cela suppose que la première colonne soit rangée en ordre croissant.

Quand on utilise un recherchev() avec faux en dernier argument, c’est un autre algorithme qui est utilisé. On descend alors dans la première colonne jusqu’à ce que l’on trouve la valeur cherchée. Dès qu’on la trouve, on s’arrête ! Ce qui signifie qu’il n’y a plus du tout la nécessité que la première colonne soit rangée en ordre croissant.

En conséquence, dans notre exemple, si l’on cherche Gilles, on trouve Lebleu avec le recherche() tout court ou le recherchev() simple, et Lenoir avec le recherchev() avec faux à la fin !

Tout cela est subtil et, à mon humble avis, l’utilisateur lambda d’Excel n’en est pas conscient…

La vie de la fonction Recherche()

La fonction Recherche() est vraiment fondamentale. Elle présente la particularité d’être l’une des 31 fonctions de Visicalc, le premier tableur au monde, sous le nom de Lookup. Vous pourrez le revoir dans l’article Visicalc, le grand-père d’Excel du 28 février.

La première syntaxe de Recherche()

La fonction Recherche() originale d’Excel utilise la syntaxe =recherche(valeur;matrice).

Si la matrice est carrée ou verticale, les valeurs de sa première colonne doivent être en ordre croissant. Excel compare la valeur cherchée à la première colonne de la matrice, identifie la dernière ligne où la valeur reste inférieure ou égale à la valeur cherchée et renvoie alors la valeur de la matrice placée dans la même ligne et la dernière colonne.

Si la matrice est horizontale, le fonctionnement est le même, en inversant ligne et colonne.

Vous trouvez dans la première partie de la copie d’écran ci-dessous ce qui apparaît quand on entre =recherche( dans une cellule. 

Comme c’est souvent le cas avec des arguments facultatifs, Microsoft se trompe sur la syntaxe : il faut lire :

=recherche(valeur_cherchée ;vecteur_recherche[;vecteur_résultat]) 

En effet, le crochet doit être avant le « ; » sinon vous obtenez une erreur quand il n’y a pas de vecteur_résultat !

Je suis en désaccord avec la déclaration de Microsoft dans la copie d’écran ci-dessous : la fonction Recherche() est bien plus simple et plus rapide que les fonctions RechercheH ou RechercheV ou a fortiori que les solutions de type Index(Equi(…)).

La seconde syntaxe de Recherche()

Un certain nombre d’utilisateurs se plaignaient auprès de Microsoft que le résultat de la recherche soit obligatoirement à droite ou en dessous.

En réponse à cette demande, des années plus tard, Microsoft a créé une seconde syntaxe :

=recherche(valeur_cherchée;vecteur_recherche;vecteur_résultat)

Dans ce cas, Excel note la position de la valeur de référence dans le vecteur de recherche et ramène, dans le vecteur de résultat, la valeur qui occupe la même position.

Remarque – Si vous avez l’esprit vicieux, rien ne vous empêche alors d’utiliser un vecteur de recherche vertical et un vecteur de résultat horizontal. C’est en effet idéal pour créer la confusion dans l’esprit de l’utilisateur !