Monsieur Excel
Pour tout savoir faire sur Excel !

27 février 2017

La fonction equiv() : aide en ligne

La fonction equiv() est une des trois fonctions de recherche d’Excel : elle est particulièrement utile. Rappelons-en le principe :
=equiv(valeur_cherchée;vecteur;code)

La valeur cherchée est un texte ou une valeur numérique.
Quand le code est 0, equiv() renvoie la position de la première occurrence de la valeur cherchée dans le vecteur.
Quand le code est 1, equiv() renvoie la position de la dernière occurrence inférieure ou égale à la valeur cherchée dans le vecteur. Cela suppose en général que le vecteur soit classé en ordre croissant.
Quand le code est -1, equiv() renvoie la position de la dernière occurrence supérieure ou égale à la valeur cherchée dans le vecteur. Cela suppose en général que le vecteur soit classé en ordre décroissant.
Quand le code est absent, Excel se comporte comme s’il était égal à 1.


Là où l’aide d’Excel a tout faux…

Comme on le voit dans la photo n°1, Excel appelle le second argument tableau_recherche, ce qui laisse entendre qu’il s’agit d’une matrice. Ceci est renforcé par la photo n°2, où Excel parle clairement d’une « matrice de valeurs ou la référence à une matrice ». Le problème, c’est que si vous entrez une matrice (c’est-à-dire au moins deux lignes et deux colonnes), le résultat de votre equiv() devient un déprimant #NA!

Là où l’aide d’Excel vous induit en erreur…

Dans le bloc D4:D8, nous avons entré les formules reproduites en E4:E8. Dans mon modèle, la formule de D1, ainsi que celles des colonnes A et B, est : =ent(1+10*alea()).

Chaque fois que je lance un recalcul avec [F9], je constate que D5 et D6 affichent toujours le même résultat, idem pour D7 et D8.

D5:D6 montrent bien que, quand le troisième argument est absent, le code absent prend la valeur 1.

Mais on voit en D7:D8 que, quand il y a le « ; » sans code, le code absent prend alors la valeur 0 !

21 février 2017

Factorielle : une illusion d’optique !

Le problème suivant a été soulevé par le MVP Liam Bastik.

La formule de C4 est : =B4*C3
La formule de D4 est : =droite(C4;15)*1
La formule de E4 est : =droite(C4;19)*1

La question qui se pose est la suivante. Pourquoi, à partir de la ligne 24, obtient-on des résultats qui ont l’air tout à fait incohérents ?


Pour vous mettre sur la voie, calculons donc la valeur de C23 (avec [F9] dans la barre de formule) : nous obtenons 51090942171709400000.

Calculons à présent celle de C24 : 1,12400072777761E+21.

Le problème vient donc du passage à la notation exponentielle.

Le MVP Jan Karel Pieterse a suggéré pour D4 la formule =droite(texte(C4;"0");15).

Mais on peut encore raccourcir sa formule en éliminant les guillemets…

La solution la plus courte est donc : =droite(texte(C4;0);15)

16 février 2017

Position de la dernière occurrence

Dans les deux articles précédents, nous avons vu comment trouver la première occurrence, puis la seconde, puis la troisième.. d’une valeur ou d’un texte dans une table.

Pour trouver la position de la dernière occurrence, c’est beaucoup plus simple. Nous utilisons en D12 la formule suivante : =max(ligne(A2:A21)*(A2:A21=D1)).

Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Notons que, si aucune occurrence n’a été trouvée, la cellule affichera « Ligne 0 », ce qui n’est pas particulièrement joli.

Ce serait plus joli d’afficher alors « Aucune ».


Beaucoup d’utilisateurs d’Excel vont alors construire une formule compliquée avec un si(), ce qui les amènera à écrire deux fois la formule.

Pour notre part, nous n’allons pas alourdir notre formule. Ce problème est résolu de façon bien plus esthétique avec le format personnalisé "Ligne "0;;"Aucune".

En effet, l’ordre des formats personnalisés est Positif;Négatif;Nul;Texte. Avec notre format, nous masquons les nombres négatifs et affichons « Aucune » quand la cellule vaut 0.

Remarque – De nombreuses formules de ce type peuvent être allégées de façon significative en ayant recours aux formats personnalisés !

10 février 2017

La énième occurrence, en direct

Dans l’article précédent, nous avons vu comment trouver la première occurrence, puis la seconde, puis la troisième.. à partir des positions des occurrences précédentes.

Parfois, on souhaite accéder directement à la troisième occurrence, ou à la septième, sans pour autant passer par les occurrences précédentes. Nous allons voir aujourd’hui comment atteindre cet objectif. Vous pouvez voir ce que cela donne dans le bloc D5:E10.

Nous entrons en colonne E le numéro de l’occurrence souhaitée et obtenons en colonne D le numéro de la ligne concernée. Mais ici chacune des formules de D5 à F10 est indépendante et autonome.


La formule de D5 est :
=sierreur(petite.valeur(si($A$1:$A$21=$D$1;ligne($A$1:$A$21));E7);"")
Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Pour comprendre cette formule, il suffit de voir que :
si($A$1:$A$21=$D$1;ligne($A$1:$A$21))
Donne le résultat suivant :
{FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;
FAUX;15;FAUX;FAUX;18;FAUX;FAUX;21}

On cherche alors la énième plus petite valeur et, comme les FAUX ne comptent pas dans cette recherche, on trouve bien la énième occurrence de la valeur recherchée.

05 février 2017

Trouver la énième occurrence

De nombreux utilisateurs d’Excel connaissent la fonction equiv() et savent qu’elle sert par exemple à trouver la première occurrence d’une valeur ou d’un texte dans un vecteur quand son troisième argument est 0, grâce à la syntaxe : =equiv(valeur;vecteur;0).

Dans le tableau ci-dessous, nous avons tiré en colonne A des valeurs aléatoires de 1 à 10, histoire de pouvoir bien tester le fonctionnement de nos formules. Il suffit donc d’utiliser la touche F9 pour obtenir un nouveau tirage des valeurs de la colonne A.


La formule de D2, visible dans le commentaire associé, sert à calculer la position du premier 7 dans la colonne A.

La formule de D3, reproduite ensuite vers le bas, sert à calculer la position du second 7, du troisième, … et ainsi de suite.

Remarque  Dans le temps (avant Excel 2007), la fonction sierreur() n'existait pas et l'on était alors obligé d'utiliser une formule plus complexe avec un si(), ce qui nous obligeait à écrire deux fois l'expression à analyser !