Kurs
Google Sheets: Fortgeschrittene Funktionen
4 Std.
57.7K
Excel-Grundlagen lernen
Erwerbe die Fähigkeit, Excel effektiv zu nutzen - keine Erfahrung erforderlich.
ACF und PCF
Wie der Name vermuten lässt, beschreibt Autokorrelation die lineare Abhängigkeit einer Variablen mit sich selbst zu zwei verschiedenen Zeitpunkten. Mit dieser Funktion lässt sich bestimmen, wie stark der aktuelle Wert einer Reihe mit ihren vergangenen Werten zusammenhängt. Die ACF berücksichtigt bei der Korrelationsbestimmung alle Komponenten der Zeitreihe – Trend, Saisonalität, Zyklen und Zufall (Residuum). Daher ist sie ein vollständiges Autokorrelationsdiagramm. Die ACF eignet sich gut, um einen positiven oder negativen Trend zu bestätigen; bei einem positiven Trend fällt die ACF-Kurve nicht ab.
Eine weitere Möglichkeit, die lineare Beziehung zwischen Variablen zu bestimmen, ist die partielle Autokorrelation. Um die Korrelation zwischen zwei Zufallsvariablen x und x zu messen, betrachtet man nur den Anteil der Korrelation, der durch x und x erklärt wird, entfernt also den linearen Einfluss der dazwischenliegenden Zufallsvariablen x, …, x und bestimmt anschließend die Korrelation der transformierten Variablen. Dieses Verfahren nennt man partielle Autokorrelationsfunktion.
Gleitende Durchschnitte
Gleitende Durchschnitte glätten Zeitreihendaten und zeigen deutlicher, wohin sich der Trend bewegt. Sie reduzieren Rauschen, indem sie Ausreißer abmildern. Zur Berechnung nimmst du das arithmetische Mittel einer Variablen über ein Fenster. Es gibt zwei gängige Varianten:
- Einfacher gleitender Durchschnitt (Simple Moving Average, SMA): Der SMA wird berechnet, indem das arithmetische Mittel eines festgelegten Wertekorbs gebildet wird. Um etwa einen 5‑Tage‑SMA der Schlusskurse zu berechnen, addierst du die Schlusskurse der letzten 5 Tage und teilst durch 5. Eine zentrale Funktion gleitender Durchschnitte ist die Trendbestimmung (Quelle).
Dabei gilt m = 2k+1. Das heißt: Die Schätzung des Trend-Zyklus zum Zeitpunkt t erhält man, indem man die Werte der Zeitreihe innerhalb von k Perioden um t herum mittelt (Quelle).
Tag 1 – 30
Tag 2 – 50
Tag 3 – 100
Tag 4 – 40
Tag 5 – 35
Einfacher gleitender Durchschnitt = 30 + 50 + 100 + 40 + 35 / 5
= 255/5
= 51
- Exponentieller gleitender Durchschnitt (Exponential Moving Average, EMA): Der EMA ist eine Form des gleitenden Durchschnitts, die neueren Datenpunkten ein höheres Gewicht gibt, da der einfache Durchschnitt durch Ausreißer mit Spitzen verfälscht werden kann. Daher spricht man auch vom exponentiell gewichteten gleitenden Durchschnitt. Für das obige Beispiel verwendest du zur Berechnung folgende Formel:
Für eine Reihe mit n, wobei N = 5,
EMA = [Letzter Wert in der Reihe – Vorletzter Wert] (2 / n+1) + Vorletzter Wert,
EMA = (35 – 40) (2 / 6) + 40
EMA = 35,33
Gleitender Durchschnitt in Tabellenkalkulationen
Anwendung des einfachen gleitenden Durchschnitts auf Stromabsatzdaten. Berechnen wir den 3MA, also den 3‑Punkte‑Durchschnitt:
1. Zuerst zeichnen wir die Daten auf und schauen uns den Trend an.
2. Füge eine Spalte mit dem Namen Three Moving Average (3MA) hinzu und wende die Formel an. Für 3MA sind die k‑Werte −1, 0, 1. Du bildest also den Durchschnitt aus vorherigem, aktuellem und folgendem Wert. Für den ersten und letzten Eintrag kann 3MA nicht berechnet werden, da der vorherige bzw. der folgende Wert fehlt.
3. Wir tragen die Funktion in C3 ein: =AVERAGE(B2:B4). Das liefert den 3MA‑Wert.
4. Kopiere diese Formel von Zelle C4 bis C20. Dadurch erhältst du die Werte.
5. Wähle nun die Daten- und 3MA‑Spalten aus und zeichne die Zeitreihen. Um beides im selben Diagramm darzustellen, wähle Daten auswählen und füge eine Datenreihe mit dem Namen 3MA hinzu. Deaktiviere den 1. und 20. Eintrag, da diese Werte im 3MA null sind, und klicke auf OK.
Analog kannst du 5MA, 7MA usw. berechnen.
Holt-Linear
Bis hierhin hast du verschiedene Prognosemethoden kennengelernt, doch bei Daten mit starken Schwankungen liefern diese Modelle oft keine guten Ergebnisse. Möchtest du zum Beispiel den künftig steigenden Bitcoin-Preis prognostizieren, sind die oben genannten Methoden nicht ideal.
Sie berücksichtigen den Trend nicht explizit. Der Trend ist, wie oben beschrieben, die langfristige Bewegung einer Zeitreihe. Du brauchst also eine Methode, die den Trend ohne zusätzliche Annahmen präzise erfasst. Genau dafür nutzt du die Holt’sche lineare Trendmethode.
Die Holt’sche lineare Methode ist auch als Double Exponential Smoothing bekannt. Sie besteht aus drei Gleichungen: einer Vorhersagegleichung und zwei Glättungsgleichungen,
In der ersten Gleichung steht l für die Schätzung des Niveaus der Zeitreihe zum Zeitpunkt t und b für die Schätzung der Steigung zum Zeitpunkt t.
Für die zweite Gleichung und zur Bestimmung des aktuellen Trends wird zunächst der Trend der Vorperiode ermittelt, indem b zum zuletzt geglätteten Wert l addiert wird. Das reduziert den Lag und bringt l näher an das aktuelle Niveau. Da die Daten zufällige Schwankungen oder Fehler enthalten können, wird der Trend durch Glättung mit β·(l − l) angepasst und zum vorherigen Trendwert addiert. Die letzte Gleichung dient der eigentlichen Vorhersage.
Holt-Linear in Tabellenkalkulationen
Lass uns das mit Tabellenkalkulationen durchspielen. Wir verwenden den Datensatz aus dem Buch „Spyros G. Makridakis, Steven C. Wheelwright, Rob J Hyndman – Forecasting: Methods and Applications – Wiley (1997)“ zur Holt’schen linearen Methode.
-
Zur Initialisierung brauchst du zwei Schätzungen – eine für den ersten geglätteten Wert L1 und eine für den Trend b1. Eine Möglichkeit ist L1 = Y1 und b1 = Y2 − Y1 bzw. (Y4 − Y1)/3.
-
Als Startwerte nimmst du α = 0,501 und β = 0,072. Diese Parameter musst du optimieren. Das geht zum Beispiel über das MSE (Mean Squared Error). Die α- und β-Werte mit dem kleinsten MSE setzt du als Hyperparameter.
-
Tragen wir die Formeln in Excel ein, um die Prognosewerte zu berechnen. Bei Lag 1 (m = 1) in der dritten Gleichung.
- Für den Start gilt L1 = Y1 und b1 = Y2 − Y1. Weisen wir diese Werte in Excel zu.
- Verwenden wir nun die Formeln zur Berechnung von L2, b2 und F2.
- Nun kopierst du alle drei Formeln bis zum Ende der verfügbaren Daten.
Jetzt liegen alle Werte vor. Wenn du den 25. Zeitraum prognostizieren möchtest, setzt du m = 1, für den 26. Zeitraum m = 2 und so weiter.
- Berechnen wir Vorhersagewerte für verschiedene Zeiträume.
Für m = 1,
Für m = 2,
Genauso kannst du für weitere Perioden vorgehen.
Fazit
Glückwunsch, du hast das Ende dieses Tutorials erreicht!
In diesem Tutorial hast du die wichtigsten Aspekte der Zeitreihenanalyse kennengelernt: ACF und PCF, gleitende Durchschnitte, deren Umsetzung in Tabellenkalkulationen, Holt‑Linear und Holt‑Linear in Tabellenkalkulationen.
Idealerweise kannst du die Konzepte der Zeitreihenanalyse jetzt nutzen, um Hypothesen zu prüfen. Danke fürs Lesen!
Wenn du mehr zu Tabellenkalkulationen lernen möchtest, schau dir den DataCamp‑Kurs Introduction to Statistics in Spreadsheets an.
Bringe deine Karriere mit Excel voran
Erwerbe die Fähigkeiten, um Excel optimal zu nutzen - keine Erfahrung erforderlich.
Themen