Moving Average Smoothing Excel

Lissage exponentiel Cet exemple vous apprend à appliquer le lissage exponentiel à une série temporelle dans Excel. Le lissage exponentiel est utilisé pour lisser les irrégularités (pics et vallées) afin de reconnaître facilement les tendances. 1. Tout d'abord, jetez un oeil à notre série chronologique. 2. Sous l'onglet Données, cliquez sur Analyse des données. Remarque: ne trouve pas le bouton Analyse des données Cliquez ici pour charger le complément Analysis ToolPak. 3. Sélectionnez Lissage exponentiel et cliquez sur OK. 4. Cliquez dans la zone Plage d'entrée et sélectionnez la plage B2: M2. 5. Cliquez dans la zone Facteur d'amortissement et tapez 0.9. La littérature parle souvent de la constante de lissage (alpha). La valeur (1) est appelée facteur d'amortissement. 6. Cliquez dans la zone Plage de sortie et sélectionnez la cellule B3. 8. Tracez un graphique de ces valeurs. Explication: parce que nous mettons alpha à 0,1, le point de données précédent reçoit un poids relativement faible tandis que la valeur lissée précédente reçoit un poids important (c'est-à-dire 0,9). En conséquence, les crêtes et les vallées sont lissées. Le graphique montre une tendance à la hausse. Excel ne peut pas calculer la valeur lissée pour le premier point de données car il n'existe aucun point de données précédent. La valeur lissée pour le second point de données est égale au point de données précédent. 9. Répétez les étapes 2 à 8 pour alpha 0,3 et alpha 0,8. Conclusion: Plus l'alpha est faible (plus le facteur d'amortissement est élevé), plus les pics et les vallées sont lissés. Plus l'alpha est élevé (plus le facteur d'amortissement est faible), plus les valeurs lissées sont proches des points de données réels. Recipe6.4. Données lissantes à l'aide de moyennes pondérées Vous aimeriez lisser les données d'une série chronologique. Utilisez Excels fonctionnalité de lissage exponentiel dans l'outil d'analyse ou construisez une série de données lissées vous-même en utilisant les fonctions de tableur et VBA. Discussion Les données de lissage sont souvent souhaitables pour éliminer les bruits indésirables dans une série de données. Calculer les moyennes mobiles comme indiqué dans la recette 6.3 est en fait un processus de lissage. Outre les moyennes mobiles des méthodes discutées précédemment, il existe d'autres façons de lisser les données. Excel offre une fonctionnalité de lissage exponentiel dans le ToolPak d'analyse. De plus, vous pouvez construire toutes les opérations de lissage que vous désirez en utilisant des fonctions de tableur standard (ou VBA). Vous avez déjà vu cette approche dans la recette précédente cependant, cette fois Ill vous montrer comment utiliser une moyenne mobile pondérée qui fait usage d'un noyau cubique spline interpolation. Lissage exponentiel Le lissage exponentiel est également une technique de calcul de la moyenne pondérée. L'idée à l'origine de la moyenne pondérée est de donner des valeurs de données les plus proches de la valeur prévisionnelle ou estimée plus grande importance, ou l'influence, par opposition aux valeurs plus loin. Le lissage exponentiel utilise la formule suivante: F n 1 est la valeur estimée à l'intervalle de temps n 1. a est un facteur de pondération appelé facteur d'amortissement dans Excel. F n est la valeur estimée précédente, et Y n est la valeur réelle précédente dans la série de données originale. Pour utiliser le lissage exponentiel, sélectionnez Outils Figure 6-11. Boîte de dialogue Lissage exponentiel Dans le champ Zone d'entrée, saisissez (ou sélectionnez dans la feuille de calcul) la plage de cellules contenant les données d'entrée que vous souhaitez lisser. Entrez un facteur d'amortissement dans le champ Facteur d'amortissement ou laissez-le vide pour utiliser la valeur par défaut de 0,3. Entrez une référence à la cellule la plus haute dans la plage de cellules de sortie souhaitée dans le champ Plage de sortie. Appuyez sur OK lorsque vous avez terminé et vous devriez voir les résultats sur votre feuille de calcul. La figure 6-12 montre un exemple pour les mêmes données de température annuelles utilisées dans la recette précédente. La série de données obtenue après le lissage est contenue dans la colonne R. Figure 6-12. Résultats de lissage exponentiel Tout comme l'outil Moyenne de déplacement décrit précédemment, l'outil Lissage exponentiel place une formule de cellule plutôt qu'une valeur dans chaque cellule de la série de données résultante. De cette façon, si vos données d'origine changent, les données lissées seront mises à jour automatiquement. La cellule R10 est sélectionnée dans la figure 6-12 pour que vous puissiez voir à quoi ressemblent les formules. La formule est représentée dans la barre de formule et est de la forme 0.7O90.3R9. Comme vous pouvez le voir, cette formule correspond à l'équation exponentielle de lissage que je vous ai montrée plus tôt. Bien sûr, vous pouvez entrer ces formules vous-même, contournant la nécessité d'utiliser le ToolPak d'analyse, si vous le désirez. La figure 6-13 illustre un tracé de la série de données lissées superposée à la série d'origine afin que vous puissiez voir la différence entre les deux. Figure 6-13. Données de température lissées de façon expo - nentielle D'autres techniques de moyenne pondérée sont couramment utilisées dans l'analyse des séries temporelles. Dans la section suivante, Ill vous montre une base basée sur un noyau d'interpolation spline cubique. Lissage du noyau Dans cet exemple, Ill vous montre une technique de moyenne pondérée qui utilise un noyau d'interpolation spline cubique conçu pour approximer un noyau gaussien. Vous pouvez utiliser toutes sortes de noyaux différents pour le lissage avec seulement de légères modifications à la technique mal présente. L'utilisation du noyau III est: r représente la distance entre les points de données et h représente le rayon de lissage efficace divisé par 2. Ill utilisera cette fonction de lissage pour calculer les poids lors du calcul des moyennes pondérées de la série chronologique. Pour faciliter cette tâche, j'ai ajouté une fonction VBA appelée Wcs que vous appelez à partir de la feuille de calcul. L'exemple 6-1 montre l'implémentation de VBA pour ce noyau de lissage. Exemple 6-1. Cubic spline kernel Ceci est une implémentation assez simple de l'équation que j'ai montré plus tôt, donc je ne vais pas sur chaque ligne de code. Reportez-vous au chapitre 2 si vous n'êtes pas déjà à jour sur l'utilisation de VBA et l'ajout de fonctions personnalisées et sous-routines. Ill utiliser cette fonction pour générer des poids afin de lisser les mêmes données de température discuté plus tôt. La figure 6-14 montre une table de poids I calculée à l'aide de cette fonction. Figure 6-14. Table des poids La première colonne du tableau, étiquetée r. Contient des indices relatifs autour du quatrième indice, marqués 0. Nous pouvons le faire ici pour calculer r parce que les données de séries temporelles sont échantillonnées à des intervalles régulièrement espacés. La deuxième colonne contient des formules comme Wcs (U10,2). C'est la formule contenue dans la cellule V10. Toutes les autres cellules contiennent des formules similaires. La somme des poids (calculée à l'aide de la formule SUM présentée dans la cellule V14) est nécessaire pour normaliser le calcul moyen pondéré. Vous pouvez éviter cette étape si votre noyau s'intègre à l'unité sur sa gamme de support. Maintenant, pour calculer les séries de données lissées, j'ai créé une autre colonne dans ma feuille de calcul, comme le montre la Figure 6-15. Figure 6-15. Résultats de lissage du noyau Les données lissées sont contenues dans la colonne S sous le titre Lissage du noyau. J'ai sélectionné la première valeur de données lissées dans la cellule S10 de la figure afin que vous puissiez voir la formule de cellule. La formule est SUMPRODUCT (R7: R13, V7: V13) V14. Comme vous pouvez le voir, il utilise la formule SUMPRODUCT pour calculer la somme des produits de chaque terme correspondant dans les deux plages de cellules données (voir le chapitre 7 pour plus d'informations sur cette fonction et d'autres fonctions pratiques). Dans ce cas, la première plage de cellules correspond aux six éléments de données autour de l'élément de données donné, y compris l'élément de données donné. Cela signifie que la moyenne pondérée dans ce cas est centrée sur la ième donnée et inclut l'influence pondérée des trois éléments précédents et les trois éléments suivants. La deuxième plage de cellules contient les poids représentés sur la figure 6-14. En outre, la division par la somme des poids, contenue dans la cellule V14, normalise le résultat. Une fois la première formule créée, je l'ai simplement copiée et collée dans les cellules restantes de la série de données. Notez que nous perdons certaines données. Plus précisément, nous perdons les premier et dernier trois éléments de données dans la plage en raison de la plage d'influence de la fonction de pondération que j'ai utilisée. Vous pouvez augmenter la portée de l'influence en augmentant la valeur du paramètre h passé dans Wcs. Ou vous pouvez le diminuer en diminuant cette valeur. Plus la plage d'influence est grande (plus le nombre d'éléments de données de la série est élevé pour chaque résultat lissé), plus les séries de données résultantes sont lissées. La figure 6-16 montre un tracé de la série de données lissées par rapport aux séries de données originales, l'effet du lissage est clairement évident. Figure 6-16. Données de température lissées au noyau Voir la recette 6.3 pour apprendre à calculer des moyennes mobiles non pondérées dans Excel. Lissage des données en utilisant les techniques de cette recette et la précédente est en fait une forme de filtrage. Vous pouvez également filtrer des données dans le domaine fréquentiel dans Excel en utilisant des transformées de Fourier lues Recette 6.10 pour en savoir plus. Moyenne de la mobilité Cet exemple vous enseigne comment calculer la moyenne mobile d'une série temporelle dans Excel. Une moyenne mobile est utilisée pour lisser les irrégularités (pics et vallées) pour reconnaître facilement les tendances. 1. Tout d'abord, jetez un oeil à notre série chronologique. 2. Sous l'onglet Données, cliquez sur Analyse des données. Remarque: ne trouve pas le bouton Analyse des données Cliquez ici pour charger le complément Analysis ToolPak. 3. Sélectionnez Moyenne mobile et cliquez sur OK. 4. Cliquez dans la zone Plage d'entrée et sélectionnez la plage B2: M2. 5. Cliquez dans la zone Intervalle et tapez 6. 6. Cliquez dans la zone Plage de sortie et sélectionnez la cellule B3. 8. Tracez un graphique de ces valeurs. Explication: parce que nous définissons l'intervalle sur 6, la moyenne mobile est la moyenne des 5 points de données précédents et le point de données actuel. En conséquence, les crêtes et les vallées sont lissées. Le graphique montre une tendance à la hausse. Excel ne peut pas calculer la moyenne mobile pour les 5 premiers points de données car il n'y a pas assez de points de données antérieurs. 9. Répétez les étapes 2 à 8 pour l'intervalle 2 et l'intervalle 4. Conclusion: Plus l'intervalle est grand, plus les sommets et les vallées sont lissés. Plus l'intervalle est faible, plus les moyennes mobiles sont proches des points de données réels.