Moving Average Glättung Excel

Exponentielle Glättung Dieses Beispiel lehrt, wie Sie eine exponentielle Glättung auf eine Zeitreihe in Excel anwenden. Exponentielle Glättung wird verwendet, um Unregelmäßigkeiten (Spitzen und Täler) zu glätten, um Trends leicht zu erkennen. 1. Erstens, werfen wir einen Blick auf unsere Zeitreihe. 2. Klicken Sie auf der Registerkarte Daten auf Datenanalyse. Hinweis: Klicken Sie hier, um das Analyse-ToolPak-Add-In zu laden. 3. Wählen Sie Exponentialglättung aus, und klicken Sie auf OK. 4. Klicken Sie im Feld Eingabebereich auf den Bereich B2: M2. 5. Klicken Sie in das Feld Dämpfungsfaktor und geben Sie 0.9 ein. Literatur spricht oft über die Glättungskonstante (alpha). Der Wert (1-) wird als Dämpfungsfaktor bezeichnet. 6. Klicken Sie in das Feld Ausgabebereich und wählen Sie Zelle B3 aus. 8. Zeichnen Sie ein Diagramm dieser Werte. Erläuterung: Da wir Alpha auf 0,1 setzen, erhält der vorhergehende Datenpunkt ein relativ geringes Gewicht, während der vorhergehende geglättete Wert ein großes Gewicht erhält (d. H. 0,9). Als Ergebnis werden Spitzen und Täler geglättet. Die Grafik zeigt eine zunehmende Tendenz. Excel kann den geglätteten Wert für den ersten Datenpunkt nicht berechnen, da es keinen vorherigen Datenpunkt gibt. Der geglättete Wert für den zweiten Datenpunkt entspricht dem vorherigen Datenpunkt. 9. Wiederholen Sie die Schritte 2 bis 8 für Alpha 0,3 und Alpha 0,8. Fazit: Je kleiner alpha (größer der Dämpfungsfaktor), desto mehr werden die Spitzen und Täler geglättet. Je größer alpha (kleiner der Dämpfungsfaktor), desto näher sind die geglätteten Werte auf die tatsächlichen Datenpunkte. Recipe6.4.Smoothing Daten mit gewichteten Durchschnittswerte Youd wie glatt Daten in einer Zeitreihe. Verwenden Sie Excels Exponential-Glättungsfunktion im Analysis ToolPak oder konstruieren Sie eine geglättete Datenreihe selbst, indem Sie Spreadsheet-Funktionen und VBA verwenden. Diskussion Glättungsdaten sind oft wünschenswert, um unerwünschtes Rauschen in einer Datenreihe zu beseitigen. Berechnen von Bewegungsdurchschnitten, wie in Rezept 6.3 diskutiert, ist tatsächlich ein Glättungsprozeß. Neben den oben beschriebenen Methoden der gleitenden Mittelwerte gibt es weitere Möglichkeiten, Daten zu glätten. Excel bietet eine exponentielle Glättungsfunktion als Teil des Analysis ToolPak. Außerdem können Sie beliebige Glättungsoperationen erstellen, die Sie mit Standard-Tabellenkalkulationsfunktionen (andor VBA) wünschen. Sie haben diesen Ansatz bereits im vorigen Rezept gesehen, zeigen aber diesmal, wie Sie einen gewichteten gleitenden Durchschnitt verwenden, der von einem kubischen Spline-Interpolationskernel Gebrauch macht. Exponentielle Glättung Die exponentielle Glättung ist auch eine gewichtete Mittelungstechnik. Die Idee hinter der gewichteten Mittelung besteht darin, Datenwerte zu liefern, die dem Wert am nächsten liegen, der prognostiziert wird oder im Vergleich zu weiter entfernten Werten eine größere Bedeutung oder einen größeren Einfluss hat. Exponentielle Glättung verwendet die folgende Formel: F n 1 ist der Wert, der im Zeitintervall n 1 abgeschätzt wird. A ist ein Gewichtungsfaktor, der in Excel als Dämpfungsfaktor bezeichnet wird. F n der vorherige Schätzwert ist und Y n der vorherige Istwert in der ursprünglichen Datenreihe ist. Um eine exponentielle Glättung zu verwenden, wählen Sie Werkzeuge Abbildung 6-11. Dialogfeld "Exponentialglättung" Geben Sie im Feld Eingabebereich den Zellbereich ein, der die Eingabedaten enthält, die Sie glätten möchten (oder wählen Sie im Arbeitsblatt). Geben Sie im Feld Dämpfungsfaktor einen Dämpfungsfaktor ein oder lassen Sie ihn leer, um den Standardwert von 0,3 zu verwenden. Geben Sie im Feld Ausgabebereich einen Verweis auf die oberste Zelle im gewünschten Ausgabezellenbereich ein. Drücken Sie OK, wenn Sie fertig sind, und Sie sollten die Ergebnisse auf Ihrer Tabelle sehen. Abbildung 6-12 zeigt ein Beispiel für die gleichen Jahrestemperaturdaten, die in der vorherigen Rezeptur verwendet wurden. Die resultierende Datenreihe nach Glättung ist in Spalte R enthalten. Abbildung 6-12. Exponentielle Glättungsergebnisse Genau wie das Tool Moving Average, das früher beschrieben wurde, legt das Tool Exponential Smoothing eine Zellenformel anstelle eines Wertes in jeder Zelle der resultierenden Datenreihe an. Auf diese Weise werden die geglätteten Daten automatisch aktualisiert, wenn sich Ihre ursprünglichen Daten ändern. Die Zelle R10 ist in Abbildung 6-12 ausgewählt, so dass Sie sehen können, wie die Formeln aussehen. Die Formel ist in der Formelleiste dargestellt und hat die Form 0.7O90.3R9. Wie Sie sehen können, entspricht diese Formel der exponentiellen Glättungsgleichung, die ich Ihnen früher gezeigt habe. Selbstverständlich können Sie diese Formeln selbst eingeben und dabei die Notwendigkeit nutzen, das Analyse-ToolPak zu verwenden, wenn Sie es wünschen. Abbildung 6-13 zeigt eine grafische Darstellung der geglätteten Datenreihe, die der Originalreihe überlagert ist, so dass Sie den Unterschied zwischen den beiden sehen können. Abbildung 6-13. Exponentiell geglättete Temperaturdaten Andere gewichtete Mittelungsverfahren werden üblicherweise in der Zeitreihenanalyse verwendet. Im nächsten Abschnitt Ill zeigen Sie ein basierend auf einem kubischen Spline-Interpolationskernel. Kernel-Glättung In diesem Beispiel zeigt Ill eine gewichtete Mittelungstechnik, die einen kubischen Spline-Interpolationskern verwendet, der entworfen ist, um einen Gaußschen Kernel zu approximieren. Sie können alle Arten von verschiedenen Kerne für Glättung mit nur geringfügigen Änderungen an der Technik Ill present. Der Kernel, den ich verwende, ist: r stellt den Abstand zwischen Datenpunkten dar und h stellt den effektiven Glättungsradius geteilt durch 2 dar. Kranke verwenden diese Glättungsfunktion, um Gewichte zu berechnen, wenn gewichtete Durchschnittswerte der Zeitreihen berechnet werden. Um diese Aufgabe zu erleichtern, habe ich eine VBA-Funktion namens Wcs, die Sie aus der Tabelle aufrufen. Beispiel 6-1 zeigt meine VBA-Implementierung für diesen Glättungskernel. Beispiel 6-1. Kubische Spline-Kernel Dies ist eine ziemlich einfache Umsetzung der Gleichung Ich zeigte früher, so werde ich nicht über jede Zeile des Codes. Beziehen Sie sich auf Kapitel 2, wenn Sie nicht bereits beschleunigen, wenn Sie VBA verwenden und benutzerdefinierte Funktionen und Unterprogramme hinzufügen. Kranke benutzen diese Funktion, um Gewichte zu erzeugen, um die gleichen Temperaturdaten, die früher erörtert wurden, zu glätten. Abbildung 6-14 zeigt eine mit dieser Funktion berechnete Gewichtstabelle I. Abbildung 6-14. Gewichtstabelle Die erste Spalte in der Tabelle mit der Beschriftung r. Enthält relative Indizes um den vierten Index mit der Bezeichnung 0. Wir können dies hier tun, um r zu berechnen, da die Zeitreihendaten in gleichmäßig beabstandeten Intervallen abgetastet werden. Die zweite Spalte enthält Formeln wie Wcs (U10,2). Dies ist die in Zelle V10 enthaltene Formel. Alle anderen Zellen enthalten ähnliche Formeln. Die Summe der Gewichtungen (berechnet unter Verwendung der in Zelle V14 gezeigten SUM-Formel) ist erforderlich, um die gewichtete Durchschnittsberechnung zu normalisieren. Sie können diesen Schritt vermeiden, wenn Ihr Kernel einheitlich über seinen Supportbereich integriert. Jetzt, um die geglättete Datenreihe zu berechnen, richte ich eine weitere Spalte in meiner Kalkulationstabelle ein, wie in Abbildung 6-15 gezeigt. Abbildung 6-15. Ergebnisse der Kernel-Glättung Die geglätteten Daten sind in Spalte S unter der Überschrift Kernel-Glättung enthalten. Ich selektierte den ersten geglätteten Datenwert in Zelle S10 in der Figur, so dass Sie die Zellformel sehen können. Die Formel ist SUMPRODUCT (R7: R13, V7: V13) V14. Wie Sie sehen können, verwendet es die SUMPRODUCT-Formel, um die Summe der Produkte jedes entsprechenden Terms in den beiden angegebenen Zellbereichen zu berechnen (weitere Informationen zu dieser und anderen praktischen Funktionen finden Sie in Kapitel 7). In diesem Fall entspricht der erste Zellenbereich den sechs Datenelementen um das gegebene Datenelement, einschließlich des gegebenen Datenelements. Dies bedeutet, dass der gewichtete Mittelwert in diesem Fall über dem i-ten Datenposten zentriert ist und den gewichteten Einfluss der drei vorherigen Posten und die drei folgenden Posten umfasst. Der zweite Zellbereich enthält die in Abbildung 6-14 dargestellten Gewichte. Weiterhin normalisiert das Teilen durch die Summe der in der Zelle V14 enthaltenen Gewichtungen das Ergebnis. Sobald die erste Formel aufgebaut wurde, habe ich einfach kopiert und in die restlichen Zellen in der Datenreihe eingefügt. Beachten Sie, dass wir einige Daten verlieren. Insbesondere verlieren wir aufgrund des Einflussbereichs der gewichteten Gewichtungsfunktion I die ersten und letzten drei Datenposten im Bereich. Sie können den Einflussbereich erhöhen, indem Sie den Wert für den in Wcs übergebenen h-Parameter erhöhen. Oder Sie können es verringern, indem Sie diesen Wert verringern. Je größer der Einflussbereich ist (umso größer die Anzahl der Datenelemente in der Reihe, die für jedes geglättete Ergebnis gemittelt wird), desto glatter wird die resultierende Datenreihe. Abbildung 6-16 zeigt ein Diagramm der geglätteten Datenreihe im Vergleich zu den ursprünglichen Datenreihen. Der Effekt der Glättung ist deutlich erkennbar. Abbildung 6-16. Kernelgeglättete Temperaturdaten Siehe Rezept 6.3, um herauszufinden, wie man ungewogene gleitende Durchschnittswerte in Excel berechnet. Glättung von Daten mit den Techniken in diesem und dem vorherigen Rezept ist eigentlich eine Form der Filterung. Sie können auch Daten in der Frequenzdomäne in Excel mit Fourier-Transformationen lesen Rezept 6.10, um mehr zu erfahren. Moving Average Dieses Beispiel lehrt, wie Sie den gleitenden Durchschnitt einer Zeitreihe in Excel zu berechnen. Ein gleitender Durchschnitt wird verwendet, um Unregelmäßigkeiten (Spitzen und Täler) zu glätten, um Trends leicht zu erkennen. 1. Erstens, werfen wir einen Blick auf unsere Zeitreihe. 2. Klicken Sie auf der Registerkarte Daten auf Datenanalyse. Hinweis: Klicken Sie hier, um das Analyse-ToolPak-Add-In zu laden. 3. Wählen Sie Verschiebender Durchschnitt aus, und klicken Sie auf OK. 4. Klicken Sie im Feld Eingabebereich auf den Bereich B2: M2. 5. Klicken Sie in das Feld Intervall und geben Sie 6 ein. 6. Klicken Sie in das Feld Ausgabebereich und wählen Sie Zelle B3 aus. 8. Zeichnen Sie ein Diagramm dieser Werte. Erläuterung: Da wir das Intervall auf 6 setzen, ist der gleitende Durchschnitt der Durchschnitt der vorherigen 5 Datenpunkte und der aktuelle Datenpunkt. Als Ergebnis werden Spitzen und Täler geglättet. Die Grafik zeigt eine zunehmende Tendenz. Excel kann den gleitenden Durchschnitt für die ersten 5 Datenpunkte nicht berechnen, da nicht genügend frühere Datenpunkte vorhanden sind. 9. Wiederholen Sie die Schritte 2 bis 8 für Intervall 2 und Intervall 4. Fazit: Je größer das Intervall, desto mehr werden die Spitzen und Täler geglättet. Je kleiner das Intervall, desto näher sind die gleitenden Mittelwerte zu den tatsächlichen Datenpunkten.