Berechnen des gleitenden Durchschnitts in Excel (einfach, gewichtet und exponentiell)

Wie viele meiner Excel-Tutorials ist auch dieses von einer der Anfragen inspiriert, die ich von einem Freund erhalten habe. Sie wollte den gleitenden Durchschnitt in Excel berechnen, und ich bat sie, online danach zu suchen (oder sich ein YouTube-Video dazu anzusehen).

Aber dann beschloss ich, selbst einen zu schreiben (die Tatsache, dass ich im College so etwas wie ein Statistik-Nerd war, spielte auch eine untergeordnete Rolle).

Bevor ich Ihnen nun erzähle, wie Sie den gleitenden Durchschnitt in Excel berechnen, möchte ich Ihnen kurz einen Überblick darüber geben, was der gleitende Durchschnitt bedeutet und welche Arten von gleitenden Durchschnitten es gibt.

Falls Sie zu dem Teil springen möchten, in dem ich zeige, wie man den gleitenden Durchschnitt in Excel berechnet, klicken Sie hier.

Hinweis: Ich bin kein Experte für Statistik und meine Absicht in diesem Tutorial ist nicht, alles über gleitende Durchschnitte zu behandeln. Ich möchte Ihnen nur zeigen, wie Sie gleitende Durchschnitte in Excel berechnen (mit einer kurzen Einführung in die Bedeutung von gleitenden Durchschnitten).

Was ist ein gleitender Durchschnitt?

Sie wissen sicher, was ein Durchschnittswert ist.

Wenn ich drei Tage mit täglichen Temperaturdaten habe, können Sie mir leicht den Durchschnitt der letzten drei Tage mitteilen (Tipp: Sie können dazu die MITTELWERT-Funktion in Excel verwenden).

Ein gleitender Durchschnitt (auch gleitender Durchschnitt oder laufender Durchschnitt genannt) liegt vor, wenn Sie den Zeitraum des Durchschnitts gleich halten, sich aber weiter bewegen, wenn neue Daten hinzugefügt werden.

Wenn ich Sie zum Beispiel an Tag 3 nach der gleitenden 3-Tage-Durchschnittstemperatur frage, geben Sie mir den durchschnittlichen Temperaturwert von Tag 1, 2 und 3. Und wenn ich Sie an Tag 4 nach der 3-tägigen gleitenden Durchschnittstemperatur frage , geben Sie mir den Durchschnitt von Tag 2, 3 und 4 an.

Wenn neue Daten hinzugefügt werden, behalten Sie den Zeitraum (3 Tage) bei, verwenden jedoch die neuesten Daten, um den gleitenden Durchschnitt zu berechnen.

Der gleitende Durchschnitt wird stark für die technische Analyse verwendet und viele Banken und Börsenanalysten verwenden ihn täglich (unten ist ein Beispiel, das ich von der Market Realist-Site erhalten habe).

Einer der Vorteile der Verwendung der gleitenden Durchschnitte besteht darin, dass Sie den Trend erkennen und Schwankungen bis zu einem gewissen Grad glätten. Wenn es beispielsweise einen wirklich heißen Tag gibt, würde der gleitende Drei-Tage-Durchschnitt der Temperatur dennoch sicherstellen, dass der Durchschnittswert geglättet wurde (anstatt Ihnen einen wirklich hohen Wert anzuzeigen, der ein Ausreißer sein könnte - ein Eins- Instanz aus).

Arten von gleitenden Durchschnitten

Es gibt drei Arten von gleitenden Durchschnitten:

• Einfacher gleitender Durchschnitt (SMA)
• Gewichteter gleitender Durchschnitt (WMA)
• Exponentieller gleitender Durchschnitt (EMA)

Einfacher gleitender Durchschnitt (SMA)

Dies ist der einfache Durchschnitt der Datenpunkte in der angegebenen Dauer.

Wenn Sie in unserem Beispiel für die tägliche Temperatur einfach den Durchschnitt der letzten 10 Tage nehmen, ergibt dies den einfachen gleitenden 10-Tage-Durchschnitt.

Dies kann durch Mittelung der Datenpunkte in der gegebenen Dauer erreicht werden. In Excel können Sie dies einfach mit der AVERAGE-Funktion tun (dies wird später in diesem Tutorial behandelt).

Gewichteter gleitender Durchschnitt (WMA)

Nehmen wir an, das Wetter wird mit jedem Tag kühler und Sie verwenden einen gleitenden 10-Tage-Durchschnitt, um den Temperaturtrend zu erhalten.

Die Temperatur von Tag 10 ist wahrscheinlicher ein besserer Indikator für den Trend im Vergleich zu Tag 1 (da die Temperatur mit jedem Tag sinkt).

Wir sind also besser dran, wenn wir uns mehr auf den Wert von Tag 10 verlassen.

Damit sich dies in unserem gleitenden Durchschnitt widerspiegelt, können Sie den neuesten Daten mehr Gewicht beimessen und früheren Daten weniger. Auf diese Weise erhalten Sie immer noch den Trend, aber mit mehr Einfluss der neuesten Daten.

Dies wird als gewichteter gleitender Durchschnitt bezeichnet.

Exponentieller gleitender Durchschnitt (EMA)

Der exponentielle gleitende Durchschnitt ist eine Art gewichteter gleitender Durchschnitt, bei dem den neuesten Daten mehr Gewicht beigemessen wird und er für die älteren Datenpunkte exponentiell abnimmt.

Er wird auch als exponentiell gewichteter gleitender Durchschnitt (EWMA) bezeichnet.

Der Unterschied zwischen WMA und EMA besteht darin, dass Sie bei WMA Gewichtungen nach beliebigen Kriterien zuweisen können. In einem gleitenden 3-Punkte-Durchschnitt können Sie beispielsweise dem neuesten Datenpunkt ein Gewichtungsalter von 60 %, dem mittleren Datenpunkt 30 % und dem ältesten Datenpunkt 10 % zuweisen.

In EMA wird dem neuesten Wert eine höhere Gewichtung zugewiesen, und die Gewichtung wird für frühere Werte exponentiell niedriger.

Genug der Statistik-Vorlesung.

Lassen Sie uns nun eintauchen und sehen, wie man gleitende Durchschnitte in Excel berechnet.

Berechnung des Simple Moving Average (SMA) mit dem Data Analysis Toolpak in Excel

Microsoft Excel verfügt bereits über ein integriertes Tool zur Berechnung der einfachen gleitenden Durchschnitte.

Es heißt die Datenanalyse-Toolpak.

Bevor Sie das Datenanalyse-Toolpak verwenden können, müssen Sie zunächst überprüfen, ob Sie es in der Excel-Multifunktionsleiste haben oder nicht. Es besteht eine gute Chance, dass Sie einige Schritte unternehmen müssen, um es zuerst zu aktivieren.

Falls Sie bereits über die Option Datenanalyse auf der Registerkarte Daten verfügen, überspringen Sie die folgenden Schritte und sehen Sie sich die Schritte zur Berechnung von gleitenden Durchschnitten an.

Klicken Sie auf die Registerkarte Daten und prüfen Sie, ob die Option Datenanalyse angezeigt wird oder nicht. Wenn Sie es nicht sehen, führen Sie die folgenden Schritte aus, um es im Menüband verfügbar zu machen.

1. Klicken Sie auf die Registerkarte Datei
2. Klicken Sie auf Optionen
3. Klicken Sie im Dialogfeld Excel-Optionen auf Add-Ins
4. Wählen Sie unten im Dialogfeld in der Dropdown-Liste Excel-Add-Ins aus und klicken Sie dann auf Los.
5. Aktivieren Sie im sich öffnenden Dialogfeld Add-Ins die Option Analysis Toolpak
6. OK klicken.

Die obigen Schritte würden das Data Analysis Toolpack aktivieren und Sie sehen diese Option jetzt auf der Registerkarte Daten.

Angenommen, Sie haben den unten gezeigten Datensatz und möchten den gleitenden Durchschnitt der letzten drei Intervalle berechnen.

Nachfolgend finden Sie die Schritte zur Verwendung der Datenanalyse, um einen einfachen gleitenden Durchschnitt zu berechnen:

1. Klicken Sie auf die Registerkarte Daten
2. Klicken Sie auf die Option Datenanalyse
3. Klicken Sie im Dialogfeld „Datenanalyse“ auf die Option „Gleitender Durchschnitt“ (möglicherweise müssen Sie ein wenig scrollen, um sie zu erreichen).
4. OK klicken. Dadurch wird das Dialogfeld „Gleitender Durchschnitt“ geöffnet.
5. Wählen Sie im Eingabebereich die Daten aus, für die Sie den gleitenden Durchschnitt berechnen möchten (B2:B11 in diesem Beispiel).
6. Geben Sie in der Option Intervall 3 ein (da wir einen gleitenden Drei-Punkte-Durchschnitt berechnen)
7. Geben Sie im Ausgabebereich die Zelle ein, in der die Ergebnisse angezeigt werden sollen. In diesem Beispiel verwende ich C2 als Ausgabebereich
8. OK klicken

Die obigen Schritte würden Ihnen das Ergebnis des gleitenden Durchschnitts geben, wie unten gezeigt.

Beachten Sie, dass die ersten beiden Zellen in Spalte C das Ergebnis als #N/A-Fehler haben. Dies liegt daran, dass es sich um einen gleitenden Drei-Punkte-Durchschnitt handelt und mindestens drei Datenpunkte benötigt, um das erste Ergebnis zu erhalten. Die eigentlichen gleitenden Durchschnittswerte beginnen also ab dem dritten Datenpunkt.

Sie werden auch feststellen, dass dieses Datenanalyse-Toolpak lediglich eine MITTELWERT-Formel auf die Zellen angewendet hat. Wenn Sie dies also manuell ohne das Datenanalyse-Toolpack tun möchten, können Sie dies sicherlich tun.

Es gibt jedoch einige Dinge, die mit dem Datenanalyse-Toolpak einfacher zu machen sind. Wenn Sie beispielsweise den Standardfehlerwert sowie das Diagramm des gleitenden Durchschnitts erhalten möchten, müssen Sie nur ein Kontrollkästchen aktivieren, und es wird Teil der Ausgabe.

Berechnung von gleitenden Durchschnitten (SMA, WMA, EMA) mit Formeln in Excel

Sie können die gleitenden Durchschnitte auch mit der AVERAGE-Formel berechnen.

Wenn Sie lediglich den gleitenden Durchschnittswert (und nicht den Standardfehler oder das Diagramm) benötigen, kann die Verwendung einer Formel eine bessere (und schnellere) Option sein als die Verwendung des Data Analysis Toolpak.

Außerdem gibt das Datenanalyse-Toolpak nur den Simple Moving Average (SMA) an, aber wenn Sie WMA oder EMA berechnen möchten, müssen Sie sich nur auf Formeln verlassen.

Berechnung des einfachen gleitenden Durchschnitts mit Formeln

Angenommen, Sie haben den unten gezeigten Datensatz und möchten den 3-Punkt-SMA berechnen:

Geben Sie in Zelle C4 die folgende Formel ein:

=MITTEL(B2:B4)

Kopieren Sie diese Formel für alle Zellen und Sie erhalten den SMA für jeden Tag.

Denken Sie daran: Wenn Sie den SMA mithilfe von Formeln berechnen, müssen Sie sicherstellen, dass die Verweise auf die Formel relativ sind. Das bedeutet, dass die Formel =DURCHSCHNITT(B2:B4) oder =DURCHSCHNITT($B2:$B4) sein kann, aber nicht =DURCHSCHNITT($B$2:$B$4) oder =DURCHSCHNITT(B$2:B$4 ). Der Zeilennummernteil der Referenz muss ohne Dollarzeichen sein. Mehr über absolute und relative Referenzen erfahren Sie hier.

Da wir einen einfachen gleitenden 3-Punkte-Durchschnitt (SMA) berechnen, sind die ersten beiden Zellen (für die ersten zwei Tage) leer und wir beginnen ab dem dritten Tag mit der Verwendung der Formel. Wenn Sie möchten, können Sie die ersten beiden Werte unverändert verwenden und ab dem dritten den SMA-Wert verwenden.

Berechnung des gewichteten gleitenden Durchschnitts mithilfe von Formeln

Für WMA müssen Sie die Gewichte kennen, die den Werten zugewiesen werden.

Angenommen, Sie müssen den 3-Punkte-WMA für den folgenden Datensatz berechnen, wobei dem letzten Wert 60 %, dem vorherigen Wert 30 % und dem vorherigen Wert 10 % zugewiesen werden.

Geben Sie dazu die folgende Formel in Zelle C4 ein und kopieren Sie sie für alle Zellen.

=0,6*B4+0,3*B3+0,1*B2

Da wir einen 3-Punkte-gewichteten gleitenden Durchschnitt (WMA) berechnen, sind die ersten beiden Zellen (für die ersten beiden Tage) leer und wir beginnen ab dem dritten Tag mit der Verwendung der Formel. Wenn Sie möchten, können Sie die ersten beiden Werte unverändert verwenden und ab dem dritten den WMA-Wert verwenden.

Berechnung des exponentiellen gleitenden Durchschnitts mit Formeln

Exponential Moving Average (EMA) verleiht dem letzten Wert eine höhere Gewichtung und die Gewichtungen werden für frühere Werte exponentiell niedriger.

Nachfolgend finden Sie die Formel zur Berechnung des EMA für einen gleitenden Drei-Punkte-Durchschnitt:

EMA = [Letzter Wert - Vorheriger EMA-Wert] * (2 / N+1) + Vorheriger EMA

… wobei N in diesem Beispiel 3 wäre (da wir einen Drei-Punkte-EMA berechnen)

Hinweis: Für den ersten EMA-Wert (wenn Sie keinen vorherigen Wert haben, um den EMA zu berechnen), nehmen Sie einfach den Wert wie er ist und betrachten ihn als EMA-Wert. Sie können diesen Wert dann in Zukunft verwenden.

Angenommen, Sie haben den folgenden Datensatz und möchten den Drei-Perioden-EMA berechnen:

Geben Sie in Zelle C2 denselben Wert wie in B2 ein. Dies liegt daran, dass es keinen vorherigen Wert zur Berechnung des EMA gibt.

Geben Sie in Zelle C3 die folgende Formel ein und kopieren Sie sie für alle Zellen:

=(B3-C2)*(2/4)+C2

In diesem Beispiel habe ich es einfach gehalten und den letzten Wert und den vorherigen EMA-Wert verwendet, um den aktuellen EMA zu berechnen.

Eine andere beliebte Methode, dies zu tun, besteht darin, zuerst den Simple Moving Average zu berechnen und ihn dann anstelle des aktuellen aktuellen Wertes zu verwenden.

Hinzufügen einer gleitenden Durchschnittstrendlinie zu einem Säulendiagramm

Wenn Sie über einen Datensatz verfügen und damit ein Balkendiagramm erstellen, können Sie auch die Trendlinie des gleitenden Durchschnitts mit wenigen Klicks hinzufügen.

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

Nachfolgend finden Sie die Schritte zum Erstellen eines Balkendiagramms mit diesen Daten und zum Hinzufügen einer dreiteiligen Trendlinie des gleitenden Durchschnitts zu diesem Diagramm:

1. Wählen Sie den Datensatz (einschließlich der Kopfzeilen) aus.
2. Klicken Sie auf die Registerkarte Einfügen
3. Klicken Sie in der Gruppe Diagramm auf das Symbol „Säulen- oder Balkendiagramm einfügen“.
4. Klicken Sie auf die Option Gruppiertes Säulendiagramm. Dadurch wird das Diagramm in das Arbeitsblatt eingefügt.
5. Klicken Sie bei ausgewähltem Diagramm auf die Registerkarte Design (diese Registerkarte wird nur angezeigt, wenn das Diagramm ausgewählt ist).
6. Klicken Sie in der Gruppe Diagrammlayouts auf „Diagrammelement hinzufügen“.
7. Bewegen Sie den Cursor über die Option „Trendlinie“ und klicken Sie dann auf „Weitere Trendlinienoptionen“.
8. Wählen Sie im Bereich Trendlinie formatieren die Option „Gleitender Durchschnitt“ und legen Sie die Anzahl der Perioden fest.

Das ist es! Die obigen Schritte würden Ihrem Säulendiagramm eine bewegliche Trendlinie hinzufügen.

Falls Sie mehr als eine gleitende Durchschnittstrendlinie einfügen möchten (z. B. eine für 2 Perioden und eine für 3 Perioden), wiederholen Sie die Schritte 5 bis 8).

Sie können die gleichen Schritte auch verwenden, um eine gleitende Durchschnittstrendlinie in ein Liniendiagramm einzufügen.

Formatieren der Trendlinie des gleitenden Durchschnitts

Im Gegensatz zu einem regulären Liniendiagramm lässt eine Trendlinie mit gleitendem Durchschnitt nicht viel Formatierung zu. Wenn Sie beispielsweise einen bestimmten Datenpunkt auf der Trendlinie hervorheben möchten, können Sie dies nicht.

Einige Dinge, die Sie in der Trendlinie formatieren können, sind:

• Farbe der Linie. Sie können dies verwenden, um eine der Trendlinien hervorzuheben, indem Sie alles im Diagramm heller machen und die Trendlinie mit einer hellen Farbe hervortreten lassen
• Das Dicke der Linie
• Das Transparenz der Linie

Um die Trendlinie des gleitenden Durchschnitts zu formatieren, klicken Sie mit der rechten Maustaste darauf und wählen Sie dann die Option Trendlinie formatieren.

Dies öffnet den Bereich Trendlinie formatieren auf der rechten Seite. Dieser Bereich enthält alle Formatierungsoptionen (in verschiedenen Abschnitten - Füllung & Linie, Effekte und Trendlinienoptionen).