Formule générique
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Sommaire
Pour faire la moyenne des 5 derniers points de données, vous pouvez utiliser la fonction MOYENNE avec les fonctions COUNT et OFFSET. Vous pouvez utiliser cette approche pour faire la moyenne des N derniers points de données: 3 derniers jours, 6 dernières mesures, etc. Dans l'exemple illustré, la formule en F6 est:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Remarque: une valeur négative pour la hauteur ne fonctionnera pas dans les feuilles Google. Voir ci-dessous pour plus d'informations.
Explication
La fonction OFFSET peut être utilisée pour construire des plages rectangulaires dynamiques basées sur une référence de départ et des 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 incluses dans la plage finale. Pour cet exemple, OFFSET est configuré comme ceci:
- référence = C3
- lignes = COUNT (A: A)
- cols = 0
- hauteur = -5
- width = (non fourni)
La référence de départ est fournie comme C3 la cellule au-dessus des données réelles. Puisque nous voulons que OFFSET renvoie une plage provenant de la dernière entrée de la colonne C, nous utilisons la fonction COUNT pour compter toutes les valeurs de la colonne C afin d'obtenir le décalage de ligne requis. COUNT ne compte que les valeurs numériques, donc l'en-tête de la ligne 3 est automatiquement ignoré.
Avec 8 valeurs numériques dans la colonne C, la formule OFFSET se résout en:
OFFSET(C3,8,0,-5)
Avec ces valeurs, OFFSET commence à C3, décale 8 lignes à C11, puis utilise -5 pour étendre la plage rectangulaire «vers l'arrière» de 5 lignes pour créer la plage C7: C11.
Enfin, OFFSET renvoie la plage C7: C11 à la fonction MOYENNE, qui calcule la moyenne des valeurs de cette plage.
Excel contre Sheets
Une bizarrerie étrange avec cette formule est qu'elle ne fonctionnera pas avec Google Sheets, car la fonction OFFSET dans Sheets ne permettra pas une valeur négative pour les arguments de hauteur ou de largeur. La documentation Excel indique également que la hauteur ou la largeur ne peuvent pas être négatives, mais il semble que les valeurs négatives fonctionnent correctement dans Excel depuis les années 1990.
Pour éviter les valeurs de hauteur ou de largeur négatives, vous pouvez utiliser une formule comme celle-ci:
=OFFSET(C4,COUNT(C:C)-5,0,5)
L'avis C4 est la référence de départ dans ce cas. La forme générale est:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
où A1 est la première cellule des nombres dont vous voulez faire la moyenne.
