Gleitender Durchschnitt bedeutet, dass wir den Durchschnitt der Durchschnittswerte des Datensatzes berechnen. In Excel verfügen wir über eine integrierte Funktion zur Berechnung des gleitenden Durchschnitts, die auf der Registerkarte Datenanalyse im Analyseabschnitt verfügbar ist. Sie benötigt einen Eingabebereich und eine Ausgabe Bereich mit Intervallen als Ausgabe, Berechnungen, die auf reinen Formeln in Excel basieren, um den gleitenden Durchschnitt zu berechnen, sind schwierig, aber wir haben eine eingebaute Funktion in Excel, um dies zu tun.
Was ist der gleitende Durchschnitt in Excel?
Der gleitende Durchschnitt ist eine weit verbreitete Technik in der Zeitreihenanalyse, mit der die Zukunft vorhergesagt wird. Die gleitenden Durchschnitte in einer Zeitreihe werden im Wesentlichen konstruiert, indem Mittelwerte verschiedener aufeinanderfolgender Werte anderer Zeitreihendaten genommen werden.
Es gibt drei Arten von gleitenden Durchschnitten, nämlich einfachen gleitenden Durchschnitt, gewichteten gleitenden Durchschnitt und exponentiellen gleitenden Durchschnitt in Excel.
# 1 - Einfacher gleitender Durchschnitt in Excel
Ein einfacher gleitender Durchschnitt hilft bei der Berechnung des Durchschnitts der letzten Anzahl von Perioden einer Datenreihe. Angenommen, die Preise für n Zeitraum sind angegeben. Dann wird der einfache gleitende Durchschnitt als angegeben
Einfacher gleitender Durchschnitt = [P1 + P2 + …………. + Pn] / n
# 2 - Gewichteter gleitender Durchschnitt in Excel
Der gewichtete gleitende Durchschnitt liefert den gewichteten Durchschnitt der letzten n Perioden. Die Gewichtung nimmt mit jedem Datenpunkt des vorherigen Zeitraums ab.
Gewichteter gleitender Durchschnitt = (Preis * Gewichtungsfaktor) + (Preis der Vorperiode * Gewichtungsfaktor-1)
# 3 - Exponentieller gleitender Durchschnitt in Excel
Es ähnelt dem einfachen gleitenden Durchschnitt, der Trends über einen bestimmten Zeitraum misst. Während der einfache gleitende Durchschnitt einen Durchschnitt gegebener Daten berechnet, misst der exponentielle gleitende Durchschnitt den aktuellen Daten mehr Gewicht bei.
Exponentieller gleitender Durchschnitt = (K x (C - P)) + P.
Wo,
- K = exponentielle Glättungskonstante
- C = aktueller Preis
- P = exponentieller gleitender Durchschnitt früherer Perioden (einfacher gleitender Durchschnitt für die Berechnung der ersten Perioden)
Wie berechnet man den gleitenden Durchschnitt in Excel?
Unten finden Sie ein Beispiel für einen gleitenden Durchschnitt in Excel.
Sie können diese gleitende durchschnittliche Excel-Vorlage hier herunterladen - gleitende durchschnittliche Excel-VorlageBeispiel 1 - Einfacher gleitender Durchschnitt in Excel
Für die Berechnung des einfachen gleitenden Durchschnitts haben wir die Verkaufsdaten eines Unternehmens von Januar bis Dezember für das Jahr 2018 herangezogen. Unser Ziel ist es, die Daten zu glätten und die Verkaufszahlen im Januar 2019 zu kennen. Wir werden hier den gleitenden Durchschnitt von drei Monaten verwenden.
Schritt 1: Der gleitende Durchschnitt von Januar, Februar und März wird berechnet, indem die Summe der Verkaufszahlen der Monate genommen und dann durch 3 geteilt wird.
Schritt 2: Wenn Sie an der Ecke der D5-Zelle auswählen und dann nur ziehen und nach unten ziehen, erhalten Sie den gleitenden Durchschnitt für die verbleibenden Zeiträume. Dies ist die Füllwerkzeugfunktion von Excel.
Die Umsatzprognose für Januar 2019 lautet 10456.66667.
Schritt 3: Jetzt zeichnen wir die Verkaufszahl und den gleitenden Durchschnitt im Liniendiagramm auf, um den Unterschied im Trend zu verstehen. Dies kann über die Registerkarte Einfügen erfolgen. Zuerst haben wir die Datenreihe ausgewählt und dann im Abschnitt Diagramme unter Einfügen das Liniendiagramm verwendet.
Nach dem Erstellen der Diagramme ist ersichtlich, dass das Diagramm mit dem gleitenden Durchschnitt viel geglätteter ist als die ursprünglichen Datenreihen.
Beispiel 2 - Einfacher gleitender Durchschnitt über die Registerkarte Datenanalyse in Excel
- Auf der Registerkarte Daten unter der Gruppe Analyse müssen wir auf Datenanalyse klicken. Es folgt der Screenshot.
- Über die Datenanalyse kann auf den gleitenden Durchschnitt zugegriffen werden.
- Nachdem wir auf den gleitenden Durchschnitt geklickt haben, haben wir die Verkaufszahl als Eingabebereich ausgewählt.
- Die Beschriftungen in der ersten Zeile werden angeklickt, um Excel verständlich zu machen, dass die erste Zeile den Beschriftungsnamen hat.
- Intervall 3 wird ausgewählt, weil wir einen gleitenden Durchschnitt von drei Jahren wollen.
- Wir haben den Ausgabebereich neben der Verkaufszahl ausgewählt.
- Wir möchten auch die Diagrammausgabe sehen, in der wir den Unterschied zwischen der tatsächlichen und der prognostizierten sehen können.
Dieses Diagramm zeigt den Unterschied zwischen dem tatsächlichen und dem prognostizierten gleitenden Durchschnitt.
Beispiel 3 - Gewichteter gleitender Durchschnitt in Excel
Wir verwenden den dreijährigen gewichteten gleitenden Durchschnitt und die Formel ist im Screenshot angegeben.
Nachdem wir die Formel verwendet haben, haben wir den gleitenden Durchschnitt für einen Zeitraum erhalten.
Wir haben den gleitenden Durchschnitt für alle anderen Perioden durch Ziehen und Ablegen von Werten in den folgenden Zellen erhalten.
Die Prognose für Januar 2019 dh 10718,33
Jetzt haben wir das Liniendiagramm genommen, um die Glättung der Daten zu sehen. Dazu haben wir in unserem Monat die prognostizierten Daten ausgewählt und anschließend ein Liniendiagramm eingefügt.
Jetzt werden wir unsere prognostizierten Daten mit unseren tatsächlichen Daten vergleichen. In den folgenden Screenshots können wir den Unterschied zwischen den tatsächlichen Daten und den prognostizierten Daten leicht erkennen. Das Diagramm oben zeigt die tatsächlichen Daten und das Diagramm unten zeigt den gleitenden Durchschnitt und die prognostizierten Daten. Wir können sehen, dass sich das Diagramm des gleitenden Durchschnitts im Vergleich zu dem Diagramm, das die tatsächlichen Daten enthält, erheblich geglättet hat.
Beispiel 4 - Exponentieller gleitender Durchschnitt in Excel
Die Formel für den exponentiellen gleitenden Durchschnitt lautet S t = α.Y t-1 + (1- α) S t-1 …… (1)
Wo,
- Y t-1 = tatsächliche Beobachtung in der t-1. Periode
- S t-1 = einfacher gleitender Durchschnitt in der t-1. Periode
- α = Glättungsfaktor und variiert zwischen 0,1 und 0,3. Je größer der Wert von α ist, desto näher ist das Diagramm an den tatsächlichen Werten und je geringer der Wert von α ist, desto glatter wird das Diagramm.
Zunächst berechnen wir den einfachen gleitenden Durchschnitt wie oben gezeigt. Danach wenden wir die in Gleichung (1) angegebene Formel an. Um den α-Wert für alle folgenden Werte festzulegen, haben wir F4 gedrückt.
Wir erhalten die Werte durch Ziehen und Ablegen in die folgenden Zellen.
Nun wollen wir den Vergleich zwischen den tatsächlichen Werten, dem einfachen gleitenden Durchschnitt und dem exponentiellen gleitenden Durchschnitt in Excel sehen. Wir haben dies mit einem Liniendiagramm getan.
Aus dem obigen Screenshot können wir den Unterschied zwischen der tatsächlichen Verkaufszahl, dem einfachen gleitenden Durchschnitt und dem exponentiellen gleitenden Durchschnitt in Excel erkennen.
Dinge, die Sie beim Verschieben des Durchschnitts in Excel beachten sollten
- Der einfache gleitende Durchschnitt kann mit einer AVERAGE-Funktion in Excel berechnet werden
- Der gleitende Durchschnitt hilft bei der Glättung der Daten
- Saisonale Durchschnittswerte werden häufig als saisonaler Index bezeichnet
- Der exponentielle gleitende Durchschnitt in Excel gibt den aktuellen Daten mehr Gewicht als der einfache gleitende Durchschnitt. Daher ist die Glättung im Fall des exponentiellen gleitenden Durchschnitts in Excel größer als die des einfachen gleitenden Durchschnitts.
- In Unternehmen wie einer Börse hilft der gleitende Durchschnitt dem Händler, den Trend leichter zu erkennen.
