Formule générique
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Sommaire
Pour faire la moyenne des 5 dernières valeurs de données dans une plage de colonnes, vous pouvez utiliser la fonction MOYENNE avec les fonctions COUNT et OFFSET. Dans l'exemple illustré, la formule en F6 est:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Explication
La fonction OFFSET peut être utilisée pour construire des plages dynamiques à l'aide d'une cellule de départ et de lignes, colonnes, hauteur et largeur données.
Les arguments des lignes et des colonnes fonctionnent comme des "décalages" à partir de la référence de départ. Les arguments de hauteur et de largeur, tous deux facultatifs, déterminent le nombre de lignes et de colonnes que comprend la plage finale. Nous voulons que OFFSET renvoie une plage qui commence à la dernière entrée et se développe «en arrière», nous fournissons donc les arguments comme suit:
référence - la référence de départ est D5 - la cellule directement à droite de la formule et la première cellule de la plage de valeurs avec laquelle nous travaillons.
lignes - nous utilisons 0 pour l'argument lignes, car nous voulons rester dans la même ligne.
colonnes - pour l'argument colonnes, nous utilisons la fonction COUNT pour compter toutes les valeurs de la plage, puis soustrayons 5. Cela décale le début de la plage de 5 colonnes vers la gauche.
height - nous utilisons 1 car nous voulons une plage d'une ligne comme résultat final.
width - nous utilisons 5, car nous voulons une plage finale avec 5 colonnes.
Pour la formule en C5, OFFSET renvoie une plage finale de F5: J5. Ceci entre dans la fonction MOYENNE qui renvoie la moyenne des 5 valeurs de la plage
Moins de 5 valeurs
S'il y a moins de 5 valeurs, la formule renverra une erreur de référence circulaire, car la plage se prolongera dans la cellule qui contient la formule. Pour éviter cette erreur, vous pouvez adapter la formule comme suit:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Ici, nous utilisons la fonction MIN pour «attraper» les situations où il y a moins de 5 valeurs, et utiliser le nombre réel lorsqu'il y en a.
