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

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. 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, 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, 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 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. 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

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

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 de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top