Excel: 5 façons de travailler avec des moyennes mobiles

Publié le 25 juin 2014
par Sophie Marchand M.Sc., CPA, CGA, MVP
Moyennes mobiles

Excel: 5 façons de travailler avec des moyennes mobiles

Le principe des moyennes mobiles est intéressant en affaires, surtout lorsque vient le temps de faire des prévisions. En gros, il s’agit de calculer une moyenne, basée sur les résultats les plus récents, afin de créer des prévisions. Ainsi, on suppose que les données plus récentes ont plus de poids (voire plus de signification) que les données plus anciennes. Dans l’article suivant, je vous explique 5 façons de travailler avec les moyennes mobiles dans Excel.

 

1. Les moyennes mobiles de base dans Excel

Le calcul de moyennes mobiles de base est très simple dans Excel. Dans l’exemple ci-bas, à partir de données journalières de vente, j’ai calculé les moyennes mobiles sur 2 jours, 3 jours et 4 jours et je l’ai fait à l’aide de la fonction Average (ou Moyenne en français). Il s’agit simplement d’aller chercher les 2, 3 ou 4 dernières valeurs et de copier la formule sur le reste de la colonne.

Moyennes mobiles

 

2. Les moyennes mobiles dynamiques

Il est possible que vous deviez travailler avec des données, comme des données de vente, qui sont mises à jour régulièrement. Dans l’exemple ci-bas, chaque jour, une nouvelle donnée de vente vient s’ajouter à la liste des données déjà compilées. Afin d’obtenir une moyenne mobile, sur 2, 3, 4 jours ou plus, il faudra utiliser une formule un peu plus élaborée. Cette formule devra tenir compte du fait que la plage de données de vente est dynamique. Voici donc la formule à utiliser pour une moyenne mobile sur 2 jours:

En anglais: = Average(Offset($D$28,counta($D$28:$D$41)-2,0,2,1))

En français: = Moyenne(Decaler($D$28;nbval($D$28:$D$41)-2;0;2;1))

 

Grâce à la portion Counta (ou Nbval en français), imbriquée dans la fonction Offset (ou Decaler en français), Excel peut toujours considérer les nouvelles valeurs dans son calcul de moyenne mobile. Cela dit, si l’on souhaite calculer une moyenne mobile dynamique sur 3 jours, il faudra modifier la formule comme suit:

En anglais: = Average(Offset($D$28,counta($D$28:$D$41)-3,0,3,1))

En français: = Moyenne(Decaler($D$28;nbval($D$28:$D$41)-3;0;3;1))

Moyennes mobiles

 

Calculs démontrant l’exactitude de la formule:

Moyennes mobiles

 

3. Les moyennes mobiles des x plus grandes valeurs

Il est possible que vous souhaitiez calculer une moyenne mobile dans Excel, basée sur les x plus grandes ou plus petites valeurs observées dans votre échantillon. Pour ce faire, je vous suggère de procéder en deux étapes. Tout d’abord, vous pourriez insérer une colonne additionnelle afin de déterminer quelles données feront partie du calcul de moyenne mobile et quelles données en seront exclues, autrement dit, il s’agit de déterminer les x plus grandes ou plus petites valeurs. Dans l’exemple ci-bas, nous avons tenté l’expérience avec les 3 plus grandes valeurs. La formule pour identifier si une donnée fait partie du top x, est la suivante:

En anglais: =If(D48>=Large($D$48:$D$61,$F$49),1,0)

En français:  Si(D48>=Grande.valeur($D$48:$D$61;$F$49);1;0)

 

Ainsi, les données faisant partie du top x recevront un “1” et celles qui n’ent font pas partie recevront un “0”. Vous pourrez alors nettement distinguer quelles sont les données utilisées dans le calcul de moyennes mobiles. Si vous devez travailler avec les plus petites valeurs, vous devrez plutôt utiliser la fonction Small (ou petite.valeur en français).

Moyennes mobiles

 

Une fois que vous aurez identifié les données sur lesquelles vous souhaitez calculer une moyenne mobile dans Excel, il ne vous restera plus qu’à utiliser la fonction Averageif (ou Moyenne.si en français), tel qu’illustré dans l’exemple ci-bas.

Moyennes mobiles

 

4. Les moyennes mobiles via le menu Analyse de données

Dans le menu Data (ou Données en français), vous avez un sous-menu qui s’intitule Data Analysis (ou Analyse de données en français).

Analyse de données

 

Ce menu vous offre l’option “Moving Average” ou “Moyenne mobile” en français. Si vous cliquez sur cette option, vous verrez une boîte de dialogue s’ouvrir. Vous n’aurez qu’à entrer votre plage de données (dans notre exemple, les ventes), le nombre d’intervalles souhaité (moyenne mobile sur les x dernier résultats) et la cellule où vous souhaitez voir le résultat final. Vous pouvez également cocher l’option de graphique pour obtenir un graphique.

Moyennes mobiles

 

 

Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel.

 

Vous obtiendrez alors les résultats du calcul des moyennes mobiles et le tout sera également projeté sur un graphique. Notez que sur ce graphique, vous aurez vos données réelles (votre échantillon de départ) et vos données prévues (résultats du calcul des moyennes mobiles).

Moyennes mobiles

 

5. Moyennes mobiles via graphiques

Une autre option s’offre à vous dans Excel et c’est celle des graphiques avec moyennes mobiles. En effet, à partir de vos données, vous pourriez élaborer un graphique, en nuage de points. Par la suite, Excel vous proposera d’insérer une courbe de tendance (trendline) et vous pourrez alors choisir d’ajouter une courbe de tendance, basée sur le calcul d’une moyenne mobile. Vous pourrez alors, évidemment, spécifiez le nombre de périodes souhaité (moyenne mobile sur 2, 3, 4 jours ou plus).

Moyennes mobiles

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Développez vos compétences en analyse de données et exploitez les fonctions d’Excel qui permettent de manipuler, gérer et transformer des données qui proviennent de diverses sources et différents formats, sans faire intervenir Power Query ou le VBA, en suivant la formation Excel – Traitement, manipulation et analyse de données.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Le CFO masqué - Commentaires formation - Excel – Traitement, manipulation et analyse de données

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

3 réflexions sur “Excel: 5 façons de travailler avec des moyennes mobiles”

  1. Bonjour,
    Il me semblait que pour calculer une moyenne mobile de base 3, par exemple, on prenait les valeurs (n-1), n et (n+1). Dans votre premier exemple, vous utilisez (n-2), (n-1) et n. Que pensez-vous de cette différence de méthode ?

  2. Lugnier, votre préoccupation est normale et cette différence de méthode s’explique par la fait que les valeurs (n-1), n et (n+1) sont pour le calcul de la moyenne mobile centrée alors que les valeurs de (n-2) (n-1) et n sont pour la moyenne mobile non centrée pour obtenir la prévision de (n+1)

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top