Monsieur Excel
Pour tout savoir faire sur Excel !

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 !