Comme beaucoup de mes tutoriels Excel, celui-ci est également inspiré par l'une des requêtes que j'ai reçues d'un ami. Elle voulait calculer la moyenne mobile dans Excel, et je lui ai demandé de la rechercher en ligne (ou de regarder une vidéo YouTube à ce sujet).
Mais ensuite, j'ai décidé d'en écrire un moi-même (le fait que j'étais un peu un nerd de statistiques à l'université a également joué un rôle mineur).
Maintenant, avant de vous expliquer comment calculer la moyenne mobile dans Excel, permettez-moi de vous donner rapidement un aperçu de ce que signifie la moyenne mobile et des types de moyennes mobiles disponibles.
Si vous souhaitez passer à la partie où je montre comment calculer la moyenne mobile dans Excel, cliquez ici.
Remarque : je ne suis pas un expert en statistiques et mon intention dans ce tutoriel n'est pas de tout couvrir sur les moyennes mobiles. Je vise uniquement à vous montrer comment calculer les moyennes mobiles dans Excel (avec une brève introduction de ce que signifient les moyennes mobiles).Qu'est-ce qu'une moyenne mobile ?
Je suis sûr que vous savez ce qu'est une valeur moyenne.
Si j'ai trois jours de données de température quotidiennes, vous pouvez facilement me dire la moyenne des trois derniers jours (indice : vous pouvez utiliser la fonction MOYENNE dans Excel pour ce faire).
Une moyenne mobile (également appelée moyenne mobile ou moyenne courante) consiste à conserver la même période de temps pour la moyenne, mais continue de se déplacer à mesure que de nouvelles données sont ajoutées.
Par exemple, au jour 3, si je vous demande la température moyenne mobile sur 3 jours, vous me donnerez la valeur de température moyenne des jours 1, 2 et 3. Et si au jour 4 je vous demande la température moyenne mobile sur 3 jours , vous me donnerez la moyenne des jours 2, 3 et 4.
Au fur et à mesure que de nouvelles données sont ajoutées, vous conservez la même période (3 jours) mais utilisez les dernières données pour calculer la moyenne mobile.
La moyenne mobile est largement utilisée pour l'analyse technique et de nombreuses banques et analystes boursiers l'utilisent quotidiennement (vous trouverez ci-dessous un exemple que j'ai obtenu du site Market Realist).
Types de moyennes mobiles
Il existe trois types de moyennes mobiles :
- Moyenne mobile simple (SMA)
- Moyenne mobile pondérée (WMA)
- Moyenne mobile exponentielle (EMA)
Moyenne mobile simple (SMA)
Il s'agit de la moyenne simple des points de données dans la durée donnée.
Dans notre exemple de température quotidienne, lorsque vous prenez simplement une moyenne des 10 derniers jours, cela donne la moyenne mobile simple sur 10 jours.
Ceci peut être réalisé en faisant la moyenne des points de données dans la durée donnée. Dans Excel, vous pouvez le faire facilement en utilisant la fonction MOYENNE (ceci est couvert plus loin dans ce didacticiel).
Moyenne mobile pondérée (WMA)
Disons que le temps se refroidit chaque jour qui passe et que vous utilisez une moyenne mobile sur 10 jours pour obtenir la tendance de la température.
La température du jour 10 est plus susceptible d'être un meilleur indicateur de la tendance par rapport au jour 1 (puisque la température baisse chaque jour qui passe).
Donc, nous sommes mieux si nous comptons davantage sur la valeur du jour 10.
Pour que cela se reflète dans notre moyenne mobile, vous pouvez donner plus de poids aux dernières données et moins aux données passées. De cette façon, vous obtenez toujours la tendance, mais avec plus d'influence des dernières données.
C'est ce qu'on appelle la moyenne mobile pondérée.
Moyenne mobile exponentielle (EMA)
La moyenne mobile exponentielle est un type de moyenne mobile pondérée où plus de poids est accordé aux dernières données et elle diminue de façon exponentielle pour les points de données plus anciens.
Elle est également appelée moyenne mobile pondérée exponentielle (EWMA)
La différence entre WMA et EMA est qu'avec WMA, vous pouvez attribuer des pondérations en fonction de n'importe quel critère. Par exemple, dans une moyenne mobile à 3 points, vous pouvez attribuer un âge pondéral de 60 % au dernier point de données, 30 % au point de données du milieu et 10 % au point de données le plus ancien.
Dans EMA, une pondération plus élevée est attribuée à la dernière valeur et la pondération continue de baisser de façon exponentielle pour les valeurs antérieures.
Assez de cours de statistiques.
Voyons maintenant comment calculer les moyennes mobiles dans Excel.
Calcul de la moyenne mobile simple (SMA) à l'aide de Data Analysis Toolpak dans Excel
Microsoft Excel dispose déjà d'un outil intégré pour calculer les moyennes mobiles simples.
C'est ce qu'on appelle le Boîte à outils d'analyse de données.
Avant de pouvoir utiliser l'utilitaire d'analyse de données, vous devez d'abord vérifier si vous l'avez dans le ruban Excel ou non. Il y a de fortes chances que vous deviez prendre quelques mesures pour l'activer d'abord.
Si vous disposez déjà de l'option Analyse des données dans l'onglet Données, ignorez les étapes ci-dessous et consultez les étapes de calcul des moyennes mobiles.Cliquez sur l'onglet Données et vérifiez si vous voyez l'option Analyse des données ou non. Si vous ne le voyez pas, suivez les étapes ci-dessous pour le rendre disponible dans le ruban.
- Cliquez sur l'onglet Fichier
- Cliquez sur Options
- Dans la boîte de dialogue Options Excel, cliquez sur Compléments
- Au bas de la boîte de dialogue, sélectionnez Compléments Excel dans la liste déroulante, puis cliquez sur OK.
- Dans la boîte de dialogue Compléments qui s'ouvre, cochez l'option Analysis Toolpak
- Cliquez sur OK.
Les étapes ci-dessus activeraient le pack d'outils d'analyse de données et vous verrez cette option dans l'onglet Données maintenant.
Supposons que vous ayez l'ensemble de données illustré ci-dessous et que vous souhaitiez calculer la moyenne mobile des trois derniers intervalles.
Vous trouverez ci-dessous les étapes à suivre pour utiliser l'analyse des données pour calculer une moyenne mobile simple :
- Cliquez sur l'onglet Données
- Cliquez sur l'option Analyse des données
- Dans la boîte de dialogue Analyse des données, cliquez sur l'option Moyenne mobile (vous devrez peut-être faire défiler un peu pour l'atteindre).
- Cliquez sur OK. Cela ouvrira la boîte de dialogue "Moyenne mobile".
- Dans la plage d'entrée, sélectionnez les données pour lesquelles vous souhaitez calculer la moyenne mobile (B2:B11 dans cet exemple)
- Dans l'option Intervalle, entrez 3 (car nous calculons une moyenne mobile à trois points)
- Dans la plage de sortie, entrez la cellule dans laquelle vous souhaitez obtenir les résultats. Dans cet exemple, j'utilise C2 comme plage de sortie
- Cliquez sur OK
Les étapes ci-dessus vous donneraient le résultat de la moyenne mobile comme indiqué ci-dessous.
Notez que les deux premières cellules de la colonne C ont pour résultat une erreur #N/A. C'est parce qu'il s'agit d'une moyenne mobile à trois points et qu'il faut au moins trois points de données pour donner le premier résultat. Ainsi, les valeurs réelles de la moyenne mobile commencent après le troisième point de données.
Vous remarquerez également que tout ce que cette boîte à outils d'analyse de données a fait est d'appliquer une formule MOYENNE aux cellules. Donc, si vous souhaitez le faire manuellement sans le pack d'outils d'analyse de données, vous pouvez certainement le faire.
Il y a, cependant, quelques choses qui sont plus faciles à faire avec l'outil d'analyse de données. Par exemple, si vous souhaitez obtenir la valeur d'erreur standard ainsi que le graphique de la moyenne mobile, il vous suffit de cocher une case et cela fera partie de la sortie.
Calcul des moyennes mobiles (SMA, WMA, EMA) à l'aide de formules dans Excel
Vous pouvez également calculer les moyennes mobiles en utilisant la formule MOYENNE.
En fait, si tout ce dont vous avez besoin est la valeur moyenne mobile (et non l'erreur standard ou le graphique), l'utilisation d'une formule peut être une meilleure option (et plus rapide) que l'utilisation de Data Analysis Toolpak.
De plus, Data Analysis Toolpak ne donne que la moyenne mobile simple (SMA), mais si vous souhaitez calculer WMA ou EMA, vous devez vous fier uniquement aux formules.
Calcul de la moyenne mobile simple à l'aide de formules
Supposons que vous ayez l'ensemble de données illustré ci-dessous et que vous souhaitiez calculer la SMA à 3 points :
Dans la cellule C4, entrez la formule suivante :
=MOYENNE(B2:B4)
Copiez cette formule pour toutes les cellules et cela vous donnera le SMA pour chaque jour.
N'oubliez pas : lorsque vous calculez la SMA à l'aide de formules, vous devez vous assurer que les références sur la formule sont relatives. Cela signifie que la formule peut être =AVERAGE(B2:B4) ou =AVERAGE($B2:$B4), mais elle ne peut pas être =AVERAGE($B$2:$B$4) ou =AVERAGE(B$2:B$4 ). La partie numéro de ligne de la référence doit être sans le signe dollar. Vous pouvez en savoir plus sur les références absolues et relatives ici.
Puisque nous calculons une moyenne mobile simple (SMA) à 3 points, les deux premières cellules (pour les deux premiers jours) sont vides et nous commençons à utiliser la formule à partir du troisième jour. Si vous le souhaitez, vous pouvez utiliser les deux premières valeurs telles quelles et utiliser la valeur SMA à partir de la troisième.
Calcul de la moyenne mobile pondérée à l'aide de formules
Pour WMA, vous devez connaître les poids qui seraient attribués aux valeurs.
Par exemple, supposons que vous deviez calculer le WMA à 3 points pour l'ensemble de données ci-dessous, où 60 % de pondération est attribuée à la dernière valeur, 30 % à celle qui la précède et 10 % à celle qui la précède.
Pour ce faire, entrez la formule suivante dans la cellule C4 et copiez pour toutes les cellules.
=0.6*B4+0.3*B3+0.1*B2
Puisque nous calculons une moyenne mobile pondérée (WMA) à 3 points, les deux premières cellules (pour les deux premiers jours) sont vides et nous commençons à utiliser la formule à partir du troisième jour. Si vous le souhaitez, vous pouvez utiliser les deux premières valeurs telles quelles et utiliser la valeur WMA à partir de la troisième.
Calcul de la moyenne mobile exponentielle à l'aide de formules
La moyenne mobile exponentielle (EMA) donne un poids plus élevé à la dernière valeur et les poids continuent de baisser de manière exponentielle pour les valeurs antérieures.
Vous trouverez ci-dessous la formule pour calculer l'EMA pour une moyenne mobile à trois points :
EMA = [Dernière valeur - Valeur EMA précédente] * (2 / N+1) + EMA précédente
… où N serait 3 dans cet exemple (car nous calculons une EMA à trois points)
Remarque : Pour la première valeur EMA (lorsque vous n'avez aucune valeur précédente pour calculer l'EMA), prenez simplement la valeur telle quelle et considérez-la comme la valeur EMA. Vous pouvez ensuite utiliser cette valeur à l'avenir.Supposons que vous ayez l'ensemble de données ci-dessous et que vous souhaitiez calculer l'EMA à trois périodes :
Dans la cellule C2, entrez la même valeur qu'en B2. C'est parce qu'il n'y a pas de valeur précédente pour calculer l'EMA.
Dans la cellule C3, entrez la formule ci-dessous et copiez pour toutes les cellules :
=(B3-C2)*(2/4)+C2
Dans cet exemple, j'ai gardé les choses simples et j'ai utilisé la dernière valeur et la valeur EMA précédente pour calculer l'EMA actuelle.
Une autre méthode courante consiste à calculer d'abord la moyenne mobile simple, puis à l'utiliser à la place de la dernière valeur réelle.
Ajout d'une ligne de tendance de moyenne mobile à un graphique à colonnes
Si vous disposez d'un ensemble de données et que vous créez un graphique à barres en l'utilisant, vous pouvez également ajouter la ligne de tendance de la moyenne mobile en quelques clics.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Vous trouverez ci-dessous les étapes pour créer un graphique à barres en utilisant ces données et en ajoutant une ligne de tendance moyenne mobile en trois parties à ce graphique :
- Sélectionnez l'ensemble de données (y compris les en-têtes)
- Cliquez sur l'onglet Insérer
- Dans le groupe Graphique, cliquez sur l'icône « Insérer une colonne ou un graphique à barres ».
- Cliquez sur l'option Graphique à colonnes groupées. Cela insérera le graphique dans la feuille de calcul.
- Avec le graphique sélectionné, cliquez sur l'onglet Conception (cet onglet n'apparaît que lorsque le graphique est sélectionné)
- Dans le groupe Dispositions de graphique, cliquez sur « Ajouter un élément de graphique ».
- Passez le curseur sur l'option « Ligne de tendance », puis cliquez sur « Plus d'options de la ligne de tendance »
- Dans le volet Format de la ligne de tendance, sélectionnez l'option « Moyenne mobile » et définissez le nombre de périodes.
C'est ça! Les étapes ci-dessus ajouteraient une ligne de tendance mobile à votre graphique à colonnes.
Si vous souhaitez insérer plus d'une ligne de tendance moyenne mobile (par exemple une pour 2 périodes et une pour 3 périodes), répétez les étapes de 5 à 8).
Vous pouvez également utiliser les mêmes étapes pour insérer une ligne de tendance de moyenne mobile dans un graphique en courbes.
Formatage de la ligne de tendance de la moyenne mobile
Contrairement à un graphique en courbes ordinaire, une ligne de tendance moyenne mobile ne permet pas beaucoup de formatage. Par exemple, si vous souhaitez mettre en évidence un point de données spécifique sur la ligne de tendance, vous ne pourrez pas le faire.
Voici quelques éléments que vous pouvez formater dans la ligne de tendance :
- Couleur de la ligne. Vous pouvez l'utiliser pour mettre en évidence l'une des lignes de tendance en rendant tout le graphique de couleur claire et en faisant apparaître la ligne de tendance avec une couleur vive.
- Le épaisseur de la ligne
- Le transparence de la ligne
Pour formater la ligne de tendance de la moyenne mobile, cliquez dessus avec le bouton droit de la souris, puis sélectionnez l'option Formater la ligne de tendance.
Cela ouvrira le volet Format Trendline sur la droite. Ce volet contient toutes les options de mise en forme (dans différentes sections - Options de remplissage et de ligne, Effets et Ligne de tendance).
