Formule générique
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Sommaire
Pour faire la moyenne des 3 dernières valeurs numériques d'une plage, vous pouvez utiliser une formule matricielle basée sur une combinaison de fonctions pour introduire les n dernières valeurs numériques dans la fonction MOYENNE. Dans l'exemple illustré, la formule en D6 est:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
où "données" est la plage nommée B5: B13.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
La fonction MOYENNE calculera une moyenne des nombres présentés dans un tableau, donc presque tout le travail dans cette formule consiste à générer un tableau des 3 dernières valeurs numériques d'une plage. Travaillant de l'intérieur vers l'extérieur, la fonction IF est utilisée pour "filtrer" les valeurs numériques:
IF(ISNUMBER(data),ROW(data))
La fonction ISNUMBER renvoie TRUE pour les valeurs numériques et FALSE pour les autres valeurs (y compris les espaces), et la fonction ROW renvoie les numéros de ligne, de sorte que le résultat de cette opération est un tableau des numéros de ligne qui correspondent aux entrées numériques:
(5;6;FALSE;8;9;10;FALSE;12;13)
Ce tableau entre dans la fonction LARGE avec la constante de tableau (1,2,3) pour k. LARGE ignore automatiquement les valeurs FALSE et renvoie un tableau avec les 3 plus grands nombres, qui correspondent aux 3 dernières lignes avec des valeurs numériques:
(13,12,10)
Ce tableau entre dans la fonction LOOKUP comme valeur de recherche. Le tableau de recherche est fourni par la fonction ROW et le tableau de résultats est la plage nommée "data":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP renvoie alors un tableau contenant les valeurs correspondantes dans "data", qui est introduit dans MOYENNE:
=AVERAGE((100,92,90))
Gérer moins de valeurs
Si le nombre de valeurs numériques tombe en dessous de 3, cette formule renverra l'erreur #NUM car LARGE ne pourra pas renvoyer 3 valeurs comme demandé. Une façon de gérer cela consiste à remplacer la constante de tableau codée en dur (1, 2, 3) par un tableau dynamique créé en utilisant INDIRECT comme ceci:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Ici, MIN est utilisé pour définir la limite supérieure du tableau sur 3 ou le nombre réel de valeurs numériques, selon la valeur la plus petite.
Remarque: je suis tombé sur cette approche intelligente sur chandoo.org, dans une réponse de Sajan à une question similaire.
