Sommaire
Pour calculer une moyenne mobile ou mobile, vous pouvez utiliser une formule simple basée sur la fonction MOYENNE avec des références relatives. Dans l'exemple illustré, la formule en E7 est:
=AVERAGE(C5:C7)
Au fur et à mesure que la formule est copiée, elle calcule une moyenne mobile sur 3 jours basée sur la valeur des ventes pour le jour en cours et les deux jours précédents.
Vous trouverez ci-dessous une option plus flexible basée sur la fonction OFFSET qui gère des périodes variables.
À propos des moyennes mobiles
Une moyenne mobile (également appelée moyenne mobile) est une moyenne basée sur des sous-ensembles de données à intervalles donnés. Le calcul d'une moyenne à des intervalles spécifiques lisse les données en réduisant l'impact des fluctuations aléatoires. Cela permet de voir plus facilement les tendances générales, en particulier dans un graphique. Plus l'intervalle utilisé pour calculer une moyenne mobile est grand, plus le lissage se produit, car plus de points de données sont inclus dans chaque moyenne calculée.
Explication
Les formules présentées dans l'exemple utilisent toutes la fonction MOYENNE avec une référence relative configurée pour chaque intervalle spécifique. La moyenne mobile sur 3 jours dans E7 est calculée en fournissant à MOYENNE une plage qui inclut le jour en cours et les deux jours précédents comme ceci:
=AVERAGE(C5:C7) // 3-day average
Les moyennes sur 5 jours et 7 jours sont calculées de la même manière. Dans chaque cas, la plage fournie à MOYENNE est élargie pour inclure le nombre de jours requis:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Toutes les formules utilisent une référence relative pour la plage fournie à la fonction MOYENNE. Au fur et à mesure que les formules sont copiées dans la colonne, la plage change à chaque ligne pour inclure les valeurs nécessaires pour chaque moyenne.
Lorsque les valeurs sont tracées dans un graphique en courbes, l'effet de lissage est clair:
Données insuffisantes
Si vous démarrez les formules dans la première ligne du tableau, les premières formules n'auront pas suffisamment de données pour calculer une moyenne complète, car la plage s'étendra au-dessus de la première ligne de données:
Cela peut ou non être un problème, selon la structure de la feuille de calcul et s'il est important que toutes les moyennes soient basées sur le même nombre de valeurs. La fonction MOYENNE ignorera automatiquement les valeurs de texte et les cellules vides, elle continuera donc à calculer une moyenne avec moins de valeurs. C'est pourquoi cela "fonctionne" en E5 et E6.
Une façon d'indiquer clairement que les données sont insuffisantes consiste à vérifier le numéro de ligne actuel et à abandonner avec #NA lorsqu'il y a moins de n valeurs. Par exemple, pour la moyenne sur 3 jours, vous pouvez utiliser:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
La première partie de la formule génère simplement un numéro de ligne "normalisé", commençant par 1:
ROW()-ROW($C$5)+1 // relative row number
Dans la ligne 5, le résultat est 1, dans la ligne 6, le résultat est 2, et ainsi de suite.
Lorsque le numéro de ligne actuel est inférieur à 3, la formule renvoie # N / A. Sinon, la formule renvoie une moyenne mobile comme auparavant. Cela imite le comportement de la version Analysis Toolpak de Moving Average, qui génère # N / A jusqu'à ce que la première période complète soit atteinte.
Cependant, à mesure que le nombre de périodes augmente, vous finirez par manquer de lignes au-dessus des données et ne pourrez pas entrer la plage requise dans MOYENNE. Par exemple, vous ne pouvez pas configurer une moyenne mobile sur 7 jours avec la feuille de calcul comme indiqué, car vous ne pouvez pas entrer une plage qui s'étend sur 6 lignes au-dessus de C5.
Périodes variables avec OFFSET
La fonction OFFSET est un moyen plus flexible de calculer une moyenne mobile. OFFSET peut créer une plage dynamique, ce qui signifie que nous pouvons mettre en place une formule où le nombre de périodes est variable. La forme générale est:
=AVERAGE(OFFSET(A1,0,0,-n,1))
où n est le nombre de périodes à inclure dans chaque moyenne. Comme ci-dessus, OFFSET renvoie une plage qui est passée dans la fonction MOYENNE. Ci-dessous, vous pouvez voir cette formule en action, où "n" est la plage nommée E2. À partir de la cellule C5, OFFSET construit une plage qui remonte aux lignes précédentes. Ceci est accompli en utilisant une hauteur égale à n négatif. Lorsque E5 est remplacé par un autre nombre, la moyenne mobile recalcule sur toutes les lignes:
La formule dans E5, copiée vers le bas, est:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Comme la formule originale ci-dessus, la version avec OFFSET aura également le problème de données insuffisantes dans les premières lignes, en fonction du nombre de périodes données dans E5.
Dans l'exemple illustré, les moyennes sont calculées avec succès car la fonction MOYENNE ignore automatiquement les valeurs de texte et les cellules vides et il n'y a pas d'autres valeurs numériques au-dessus de C5. Ainsi, alors que la plage passée à MOYENNE dans E5 est C1: C5, il n'y a qu'une seule valeur à moyenne, 100. Cependant, à mesure que les périodes augmentent, OFFSET continuera à créer une plage qui s'étend au-dessus du début des données, finissant par en haut de la feuille de calcul et renvoyant une erreur #REF.
Une solution consiste à «limiter» la taille de la plage au nombre de points de données disponibles. Cela peut être fait en utilisant la fonction MIN pour restreindre le nombre utilisé pour la hauteur comme indiqué ci-dessous:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Cela semble assez effrayant, mais c'est en fait assez simple. Nous limitons la hauteur passée en OFFSET avec la fonction MIN:
MIN(ROW()-ROW($C$5)+1,n)
À l'intérieur de MIN, la première valeur est un numéro de ligne relatif, calculé avec:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
La deuxième valeur donnée à MIN est le nombre de périodes, n. Lorsque le numéro de ligne relatif est inférieur à n, MIN renvoie le numéro de ligne actuel à OFFSET pour la hauteur. Lorsque le numéro de ligne est supérieur à n, MIN renvoie n. En d'autres termes, MIN renvoie simplement la plus petite des deux valeurs.
Une fonctionnalité intéressante de l'option OFFSET est que n peut être facilement modifié. Si nous changeons n en 7 et représentons les résultats, nous obtenons un graphique comme celui-ci:
Remarque: une bizarrerie avec les formules OFFSET ci-dessus est qu'elles ne fonctionneront pas dans Google Sheets, car la fonction OFFSET dans Sheets n'autorisera pas une valeur négative pour la hauteur ou la largeur. La feuille de calcul jointe contient des formules de contournement pour les feuilles Google.
