La moyenne mobile signifie que nous calculons la moyenne des moyennes de l'ensemble de données que nous avons, dans Excel, nous avons une fonction intégrée pour le calcul de la moyenne mobile qui est disponible dans l'onglet d'analyse des données dans la section d'analyse, il faut une plage d'entrée et une sortie gamme avec des intervalles en sortie, les calculs basés sur de simples formules dans Excel pour calculer la moyenne mobile sont difficiles, mais nous avons une fonction intégrée dans Excel pour le faire.
Qu'est-ce que la moyenne mobile dans Excel
La moyenne mobile est une technique largement utilisée dans l'analyse de séries chronologiques qui sert à prédire l'avenir. Les moyennes mobiles dans une série chronologique sont essentiellement construites en prenant des moyennes de diverses valeurs séquentielles d'autres données de série chronologique.
Il existe trois types de moyennes mobiles, à savoir la moyenne mobile simple, la moyenne mobile pondérée et la moyenne mobile exponentielle dans Excel.
# 1 - Moyenne mobile simple dans Excel
Une simple moyenne mobile permet de calculer la moyenne du dernier nombre de périodes d'une série de données. Supposons que les prix de n période soient donnés. Ensuite, la moyenne mobile simple est donnée comme
Moyenne mobile simple = [P1 + P2 + …………. + Pn] / n
# 2 - Moyenne mobile pondérée dans Excel
La moyenne mobile pondérée fournit la moyenne pondérée des n dernières périodes. La pondération diminue avec chaque point de données de la période précédente.
Moyenne mobile pondérée = (Prix * facteur de pondération) + (Prix de la période précédente * facteur de pondération-1)
# 3 - Moyenne mobile exponentielle dans Excel
Elle est similaire à une simple moyenne mobile qui mesure les tendances sur une période de temps. Alors que la moyenne mobile simple calcule une moyenne de données données, la moyenne mobile exponentielle attache plus de poids aux données actuelles.
Moyenne mobile exponentielle = (K x (C - P)) + P
Où,
- K = constante de lissage exponentiel
- C = prix actuel
- P = moyenne mobile exponentielle des périodes précédentes (moyenne mobile simple utilisée pour le calcul des premières périodes)
Comment calculer la moyenne mobile dans Excel?
Voici l'exemple de moyenne mobile dans Excel.
Vous pouvez télécharger ce modèle Excel de moyenne mobile ici - Modèle Excel de moyenne mobileExemple # 1 - Moyenne mobile simple dans Excel
Pour calculer la moyenne mobile simple, nous avons pris les données de ventes d'une entreprise de janvier à décembre pour l'année 2018. Notre objectif est de lisser les données et de connaître le chiffre des ventes en janvier 2019. Nous utiliserons ici une moyenne mobile de trois mois.
Étape 1: La moyenne mobile de janvier, février et mars est calculée en prenant la somme du chiffre d'affaires des mois, puis en la divisant par 3.
Étape 2: Sélectionner dans le coin de la cellule D5, puis simplement faire glisser et descendre donnera la moyenne mobile pour les périodes restantes. C'est la fonction d'outil de remplissage d'Excel.
La prévision des ventes pour janvier 2019 est 10456.66667.
Étape 3: Nous traçons maintenant le chiffre des ventes et la moyenne mobile dans le graphique linéaire pour comprendre la différence de tendance. Cela peut être fait à partir de l'onglet Insertion. Tout d'abord, nous avons sélectionné la série de données, puis dans la section Graphiques sous insérer, nous avons utilisé le graphique linéaire.
Après avoir créé les graphiques, on peut voir que le graphique avec la moyenne mobile est beaucoup plus lissé que la série de données d'origine.
Exemple # 2 - Moyenne mobile simple via l'onglet Analyse des données dans Excel
- Sous l'onglet Données sous le groupe Analyse, nous devons cliquer sur Analyse des données. Voici la capture d'écran.
- À partir de l'analyse des données, la moyenne mobile est accessible.
- Après avoir cliqué sur la moyenne mobile, nous avons sélectionné le chiffre des ventes comme plage d'entrée.
- Les étiquettes de la première ligne sont cliquées afin de faire comprendre à Excel que la première ligne porte le nom de l'étiquette.
- L'intervalle 3 est sélectionné car nous voulons une moyenne mobile de trois ans.
- Nous avons sélectionné la plage de sortie avec adjacence au chiffre de vente.
- Nous voulons également voir la sortie du graphique dans laquelle nous pourrons voir la différence entre le réel et le prévu.
Ce graphique montre la différence entre la moyenne mobile réelle et prévue.
Exemple # 3 - Moyenne mobile pondérée dans Excel
Nous utilisons la moyenne mobile pondérée sur trois ans et la formule est donnée dans la capture d'écran.
Après avoir utilisé la formule, nous avons obtenu la moyenne mobile pour une période.
Nous avons obtenu la moyenne mobile pour toutes les autres périodes en faisant glisser et en déposant des valeurs dans les cellules suivantes.
La prévision pour janvier 2019 soit 10718,33
Nous avons maintenant pris le graphique linéaire pour voir le lissage des données. Pour cela, nous avons sélectionné notre mois les données prévues puis inséré un graphique linéaire.
Nous allons maintenant comparer nos données prévues avec nos données réelles. Dans les captures d'écran ci-dessous, nous pouvons facilement voir la différence entre les données réelles et les données prévues. Le graphique en haut est les données réelles et le graphique ci-dessous est la moyenne mobile et les données prévues. Nous pouvons voir que le graphique de moyenne mobile s'est considérablement lissé par rapport au graphique qui contient les données réelles.
Exemple # 4 - Moyenne mobile exponentielle dans Excel
La formule de la moyenne mobile exponentielle est S t = α.Y t-1 + (1- α) S t-1 …… (1)
Où,
- Y t-1 = observation réelle dans la t-1e période
- S t-1 = moyenne mobile simple à la t-1e période
- α = facteur de lissage et il varie entre 0,1 et 0,3. Plus la valeur de α est plus proche du graphique des valeurs réelles et moins la valeur de α, plus le graphique sera lisse.
Tout d'abord, nous calculons la moyenne mobile simple comme indiqué précédemment. Après cela, nous appliquons la formule donnée dans l'équation (1). Pour fixer la valeur α pour toutes les valeurs suivantes, nous avons appuyé sur F4.
Nous obtenons les valeurs par glisser-déposer dans les cellules suivantes.
Maintenant, nous voulons voir la comparaison entre les valeurs réelles, la moyenne mobile simple et la moyenne mobile exponentielle dans Excel. Nous avons fait cela en créant un graphique linéaire.
À partir de la capture d'écran ci-dessus, nous pouvons voir la différence entre le chiffre des ventes réel, la moyenne mobile simple et la moyenne mobile exponentielle dans Excel.
Choses à retenir sur la moyenne mobile dans Excel
- La moyenne mobile simple peut être calculée à l'aide d'une fonction MOYENNE dans Excel
- La moyenne mobile aide à lisser les données
- Les moyennes saisonnières sont souvent qualifiées d'indices saisonniers
- La moyenne mobile exponentielle dans Excel donne plus de poids aux données récentes que la moyenne mobile simple. Par conséquent, le lissage en cas de moyenne mobile exponentielle dans Excel est supérieur à celui de la moyenne mobile simple.
- Dans des entreprises comme un marché boursier, la moyenne mobile aide le trader à identifier plus facilement la tendance.
