Tutoriel Excel : comment calculer la moyenne mobile.

Guillaume 7 août 2019 Excel Laisser un potin

Dans ce court tutoriel, vous apprendrez à calculer rapidement une moyenne mobile simple dans Excel, les fonctions à utiliser pour obtenir une moyenne mobile des N derniers jours, semaines, mois ou années. Si vous souhaitez plus de tutoriels Excel, il suffit de lancer une recherche sur les moteurs de recherche pour trouver de nombreuses ressources.

Calcul de la moyenne mobile simple dans Excel

Dans l’ensemble, il existe deux manières d’obtenir une moyenne mobile simple dans Excel. En utilisant des formules et des options de courbe de tendance. Les exemples suivants illustrent les deux techniques.

Exemple 1. Calculer la moyenne mobile pour une période donnée

Une simple moyenne mobile peut être calculée en un rien de temps avec la fonction MOYENNE. Supposons que vous ayez une liste des températures mensuelles moyennes dans la colonne B et que vous souhaitiez obtenir une moyenne mobile sur 3 mois.

Ecrivez une formule MOYENNE habituelle pour les 3 premières valeurs et entrez-la dans la ligne correspondant à la 3e valeur en partant du haut (cellule C4 dans cet exemple), puis copiez la formule dans les autres cellules de la colonne: =MOYENNE(B2:B4) (Voir à gauche de la capture ci-dessous).

Vous pouvez corriger la colonne avec une référence absolue (telle que $B2) si vous le souhaitez, mais veillez à utiliser des références de ligne relatives (sans le signe $) afin que la formule s’adapte correctement aux autres cellules.

En vous rappelant qu’une moyenne est calculée en additionnant les valeurs, puis en divisant la somme par le nombre de valeurs à moyenner, vous pouvez vérifier le résultat à l’aide de la formule SOMME: =SOMME(B2:B4) / 3

Exemple 2. Obtenir la moyenne mobile des derniers N jours / semaines / mois / années dans une colonne

Supposons que vous ayez une liste de données, par exemple chiffres de vente, et vous voulez connaître la moyenne des 3 derniers mois à tout moment. Pour cela, vous avez besoin d’une formule qui recalcule la moyenne dès que vous entrez une valeur pour le mois suivant. Quelle fonction Excel est capable de faire cela? La bonne vieille MOYENNE en combinaison avec DECALER et NBVAL.

=MOYENNE(DECALER(première cellule ; NBVAL(plage entière) -N ;0 ; N; 1))

Où N est le nombre de derniers jours / semaines / mois / années à inclure dans la moyenne.

Vous ne savez pas comment utiliser cette formule de moyenne mobile dans vos feuilles de calcul Excel? L’exemple suivant clarifiera les choses.

En supposant que les valeurs à moyenner figurent dans la colonne B commençant à la ligne 2, la formule serait la suivante :

=MOYENNE(DECALER(B2 ; NBVAL(B2: B100) -3 ;0 ;3 ;1)) (Voir à droite de la capture ci-dessous)

Et maintenant, essayons de comprendre ce que cette formule de moyenne mobile Excel est en train de faire.

La fonction NBVAL(B2: B100) compte le nombre de valeurs déjà entrées dans la colonne B. Nous commençons à compter en B2 car la ligne 1 correspond à l’en-tête de colonne.
La fonction DECALER prend la cellule B2 (le premier argument) comme point de départ et décale la valeur renvoyée par la fonction NBVAL en déplaçant 3 lignes vers le haut (-3 dans le deuxième argument). En tant que résultat, il renvoie la somme des valeurs dans une plage composée de 3 lignes (3 dans le 4ème argument) et 1 colonne (1 dans le dernier argument), ce qui correspond aux 3 derniers mois souhaités.
Enfin, la somme renvoyée est transmise à la fonction MOYENNE pour calculer la moyenne mobile.

Comme vous l’avez probablement remarqué, le tableau de cet exemple contient des données pour 9 mois seulement, mais la plage B2: B100 est fournie à NBVAL, juste pour être du côté de la sauvegarde.

Création d’un graphique de moyenne mobile

Si vous avez déjà créé un graphique pour vos données, l’ajout d’une ligne de tendance moyenne mobile pour ce graphique ne prend que quelques secondes. Pour cela, nous allons utiliser la fonctionnalité Excel Courbe de tendance et les étapes détaillées sont décrites ci-dessous.

Pour cet exemple, j’ai créé un graphique à colonnes 2D (onglet Insertion > groupe Graphiques) pour nos données de vente. Et maintenant, nous voulons « visualiser » la moyenne mobile pendant 3 mois.

1. Sélectionnez le graphique, accédez à l’onglet Création de graphique > groupe disposition du graphique, puis cliquez sur Ajouter un élément graphique > courbe de tendance> Autres options de la courbe de tendance… Le volet Format de la courbe de tendance s’ouvrira à droite de votre feuille de calcul dans Excel 2013 et nouvelles versions et la boîte de dialogue correspondante s’ouvrira dans Excel 2010 et 2007.
2. Dans le volet Format de la courbe de tendance, vous cliquez sur l’icône Options de la courbe de tendance, sélectionnez l’option Moyenne mobile et spécifiez l’intervalle de la moyenne mobile dans la zone Période.
3. Fermez le volet Ligne de tendance et vous trouverez la ligne de tendance moyenne mobile ajoutée à votre graphique