Formule générique
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Sommaire
Pour obtenir la position de la dernière correspondance (c'est-à-dire la dernière occurrence) d'une valeur de recherche, vous pouvez utiliser une formule matricielle basée sur les fonctions IF, ROW, INDEX, MATCH et MAX. Dans l'exemple illustré, la formule en H6 est:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Où «noms» est la plage nommée C4: C11.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
L'essentiel de cette formule est que nous construisons une liste de numéros de ligne pour une plage donnée, correspondant à une valeur, puis utilisons la fonction MAX pour obtenir le plus grand numéro de ligne, qui correspond à la dernière valeur correspondante. Nous utilisons la plage nommée "noms" pour des raisons de commodité uniquement.
En travaillant de l'intérieur vers l'extérieur, cette partie de la formule générera un ensemble relatif de numéros de ligne:
ROW(names)-MIN(ROW(names))+1
Le résultat de l'expression ci-dessus est un tableau de nombres comme celui-ci:
(1;2;3;4;5;6;7;8)
Notez que nous obtenons 8 nombres, correspondant aux 8 lignes du tableau. Consultez cette page pour plus de détails sur le fonctionnement de cette partie de la formule.
Pour les besoins de cette formule, nous ne voulons que des numéros de ligne pour les valeurs correspondantes, nous utilisons donc la fonction IF pour filtrer les valeurs comme suit:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Cela donne un tableau qui ressemble à ceci:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Notez que ce tableau contient toujours huit éléments. Cependant, seuls les numéros de ligne où la valeur de la plage nommée «names» est égale à «amy» ont survécu (ie 1, 4, 7). Tous les autres éléments du tableau sont FAUX, car ils ont échoué au test logique de la fonction IF.
Enfin, la fonction IF délivre ce tableau à la fonction MAX. MAX renvoie la valeur la plus élevée du tableau, le nombre 7, qui correspond au dernier numéro de ligne où le nom est "amy". Une fois que nous connaissons le dernier numéro de ligne correspondant, nous pouvons utiliser INDEX pour récupérer une valeur à cette position.
Avant-dernier, etc.
Pour obtenir l'avant-dernière position, la troisième à la dernière, etc., vous pouvez passer de la fonction MIN à la fonction LARGE comme ceci:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
où k représente le "nième plus grand". Par exemple, pour obtenir l'avant-dernière correspondance dans l'exemple ci-dessus, vous pouvez utiliser:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Comme précédemment, il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
