Gewichtete Gleitende Durchschnitt Excel Prognose

Berechnen des gleitenden Durchschnitts in Excel In diesem kurzen Tutorial erfahren Sie, wie Sie schnell einen einfachen gleitenden Durchschnitt in Excel berechnen können, welche Funktionen für die letzten N Tage, Wochen, Monate oder Jahre gleitend sind, und wie man einen Umzug hinzufügt Durchschnittliche Trendlinie zu einem Excel-Diagramm. In ein paar jüngsten Artikeln haben wir einen genaueren Blick auf die Berechnung des Durchschnitts in Excel. Wenn Sie unseren Blog verfolgt haben, wissen Sie bereits, wie Sie einen normalen Durchschnitt berechnen und welche Funktionen zu verwenden, um einen gewichteten Durchschnitt zu finden. In der heutigen Tutorial, werden wir diskutieren zwei grundlegende Techniken zu berechnen gleitenden Durchschnitt in Excel. Was ist gleitender Durchschnitt Im Allgemeinen kann der gleitende Durchschnitt (auch als rollender Durchschnitt bezeichnet werden, der durchschnittliche oder bewegliche Mittelwert ist) als eine Reihe von Durchschnittswerten für verschiedene Teilmengen desselben Datensatzes definiert werden. Es wird häufig in der Statistik verwendet, saisonbereinigte Wirtschafts - und Wettervorhersage, um zugrunde liegende Trends zu verstehen. Im Aktienhandel ist der gleitende Durchschnitt ein Indikator, der den Durchschnittswert einer Sicherheit über einen bestimmten Zeitraum anzeigt. Im Geschäft ist es eine gängige Praxis, einen gleitenden Durchschnitt des Umsatzes für die letzten 3 Monate zu berechnen, um den jüngsten Trend zu bestimmen. Zum Beispiel kann der gleitende Durchschnitt der dreimonatigen Temperaturen berechnet werden, indem man den Durchschnitt der Temperaturen von Januar bis März, dann den Durchschnitt der Temperaturen von Februar bis April, dann von März bis Mai und so weiter. Es gibt verschiedene Arten von gleitenden Durchschnitt wie einfach (auch bekannt als arithmetisch), exponentiell, variabel, dreieckig und gewichtet. In diesem Tutorial werden wir in den am häufigsten verwendeten einfachen gleitenden Durchschnitt suchen. Berechnen einfacher gleitender Durchschnitt in Excel Insgesamt gibt es zwei Möglichkeiten, um einen einfachen gleitenden Durchschnitt in Excel zu bekommen - mit Formeln und Trendline-Optionen. Die folgenden Beispiele zeigen beide Techniken. Beispiel 1. Berechnen Sie den gleitenden Durchschnitt für einen bestimmten Zeitraum Ein einfacher gleitender Durchschnitt kann in kürzester Zeit mit der AVERAGE-Funktion berechnet werden. Angenommen, Sie haben eine Liste der durchschnittlichen monatlichen Temperaturen in Spalte B, und Sie wollen einen gleitenden Durchschnitt für 3 Monate (wie in der Abbildung oben gezeigt) zu finden. Schreiben Sie eine übliche AVERAGE Formel für die ersten 3 Werte und geben Sie sie in die Zeile ein, die dem 3. Wert von oben entspricht (Zelle C4 in diesem Beispiel), und kopieren Sie dann die Formel auf andere Zellen in der Spalte: Sie können die Spalte mit einer absoluten Referenz (wie B2), wenn Sie wollen, aber seien Sie sicher, relative Zeilenreferenzen (ohne das Zeichen) zu verwenden, damit die Formel für andere Zellen richtig passt. Wenn man bedenkt, dass ein Durchschnitt durch Addition von Werten berechnet wird und dann die Summe durch die Anzahl der zu gemittelten Werte dividiert wird, kannst du das Ergebnis mit der SUM-Formel verifizieren: Beispiel 2. Gehender Durchschnitt für die letzten N Tage Wochen Monate Jahre In einer Spalte Angenommen, Sie haben eine Liste von Daten, zB Verkauf Zahlen oder Aktienkurse, und Sie wollen den Durchschnitt der letzten 3 Monate zu jedem Zeitpunkt wissen. Dazu benötigen Sie eine Formel, die den Durchschnitt neu berechnet, sobald Sie einen Wert für den nächsten Monat eingeben. Welche Excel-Funktion ist in der Lage, dies zu tun Die gute alte AVERAGE in Kombination mit OFFSET und COUNT. DURCHSCHNITT (OFFSET (erste Zelle COUNT (gesamte Bereich) - N, 0, N, 1)) Wo N die Anzahl der letzten Tage Wochen Monate Jahre im Durchschnitt ist. Nicht sicher, wie man diese gleitende durchschnittliche Formel in Ihren Excel-Arbeitsblättern verwendet Das folgende Beispiel macht die Dinge klarer. Angenommen, die Werte zum Durchschnitt sind in Spalte B beginnend in Zeile 2, die Formel wäre wie folgt: Und jetzt können wir versuchen zu verstehen, was diese Excel gleitenden durchschnittlichen Formel tatsächlich tut. Die COUNT-Funktion COUNT (B2: B100) zählt, wie viele Werte bereits in Spalte B eingegeben wurden. Wir beginnen in B2 zu zählen, da Zeile 1 die Spaltenüberschrift ist. Die Funktion OFFSET nimmt die Zelle B2 (das 1. Argument) als Startpunkt an und versetzt die Zählung (den von der COUNT-Funktion zurückgegebenen Wert) durch Bewegen von 3 Zeilen (-3 im 2. Argument). Als Ergebnis gibt es die Summe der Werte in einem Bereich aus 3 Zeilen (3 im 4. Argument) und 1 Spalte (1 im letzten Argument), die die letzten 3 Monate, die wir wollen. Schließlich wird die zurückgegebene Summe an die AVERAGE-Funktion übergeben, um den gleitenden Durchschnitt zu berechnen. Spitze. Wenn Sie mit ständig aktualisierbaren Arbeitsblättern arbeiten, in denen zukünftig neue Zeilen hinzugefügt werden sollen, stellen Sie sicher, dass Sie eine ausreichende Anzahl von Zeilen an die COUNT-Funktion liefern, um potenzielle neue Einträge zu berücksichtigen. Es ist kein Problem, wenn man mehr Zeilen als tatsächlich benötigt, solange man die erste Zelle rechts hat, wird die COUNT-Funktion alle leeren Zeilen sowieso verwerfen. Wie Sie wahrscheinlich bemerkt haben, enthält der Tisch in diesem Beispiel nur 12 Monate Daten, und doch wird der Bereich B2: B100 an COUNT geliefert, nur um auf der Rettungsseite zu sein :) Beispiel 3. Gehender Durchschnitt für die letzten N Werte in Eine Zeile Wenn Sie einen gleitenden Durchschnitt für die letzten N Tage, Monate, Jahre usw. in der gleichen Zeile berechnen möchten, können Sie die Offset-Formel auf diese Weise anpassen: Angenommen, B2 ist die erste Zahl in der Zeile und Sie wollen Um die letzten 3 Zahlen im Durchschnitt einzuschließen, nimmt die Formel die folgende Form an: Erstellen eines Excel-Gültigkeitsdiagramms Wenn Sie bereits ein Diagramm für Ihre Daten erstellt haben, ist das Hinzufügen einer gleitenden durchschnittlichen Trendlinie für dieses Diagramm eine Frage von Sekunden. Dazu werden wir die Excel Trendline nutzen und die ausführlichen Schritte folgen unten. Für dieses Beispiel hat Ive für unsere Verkaufsdaten ein 2-D-Säulendiagramm (Insert tab gt Charts group) erstellt: Und nun wollen wir den gleitenden Durchschnitt für 3 Monate visualisieren. In Excel 2010 und Excel 2007 gehen Sie zu Layout gt Trendline gt Weitere Trendline-Optionen. Spitze. Wenn Sie nicht brauchen, um die Details wie die gleitenden durchschnittlichen Intervall oder Namen angeben, können Sie klicken Design gt Add Chart Element gt Trendline gt Moving Average für das unmittelbare Ergebnis. Der Format Trendline-Bereich wird auf der rechten Seite des Arbeitsblattes in Excel 2013 geöffnet und das entsprechende Dialogfeld wird in Excel 2010 und 2007 angezeigt. Um den Chat zu verfeinern, können Sie auf die Registerkarte Fill amp Line oder Effects wechseln Das Format Trendline-Fenster und spielen mit verschiedenen Optionen wie Line-Typ, Farbe, Breite, etc. Für leistungsstarke Datenanalyse, können Sie ein paar gleitende durchschnittliche Trendlinien mit verschiedenen Zeitintervallen hinzufügen, um zu sehen, wie sich der Trend entwickelt. Der folgende Screenshot zeigt die 2-monatigen (grünen) und 3-monatigen (brickrot) bewegten durchschnittlichen Trendlinien: Nun, das ist alles über die Berechnung des gleitenden Durchschnitts in Excel. Das Beispiel Arbeitsblatt mit den gleitenden durchschnittlichen Formeln und Trendline steht zum Download zur Verfügung - Moving Average Kalkulationstabelle. Ich danke Ihnen für das Lesen und freue mich darauf, Sie nächste Woche zu sehen Sie können auch interessiert sein an: Ihr Beispiel 3 oben (Get gleitend Durchschnitt für die letzten N Werte in einer Reihe) funktionierte perfekt für mich, wenn die ganze Zeile Zahlen enthält. Ich mache das für meine Golf-Liga, wo wir einen 4-wöchigen durchschnittlichen Durchschnitt verwenden. Manchmal fehlen die Golfer anstatt einer Partitur, ich werde ABS (Text) in die Zelle setzen. Ich möchte immer noch die Formel nach den letzten 4 Scores suchen und das ABS weder im Zähler noch im Nenner zählen. Wie kann ich die Formel ändern, um dies zu erreichen Ja, ich habe bemerkt, ob die Zellen leer waren, die Berechnungen waren falsch. In meiner Situation verfolge ich über 52 Wochen. Auch wenn die letzten 52 Wochen Daten enthielten, war die Berechnung falsch, wenn eine Zelle vor den 52 Wochen leer war. Ich versuche, eine Formel zu schaffen, um den gleitenden Durchschnitt für 3 Periode zu erhalten, schätzen, wenn Sie pls helfen können. Datum Artikel Preis 1012016 A 1,00 1012016 B 5,00 1012016 C 10,00 1022016 A 1,50 1022016 B 6,00 1022016 C 11,00 1032016 A 2,00 1032016 B 15,00 1032016 C 20,00 1042016 A 4,00 1042016 B 20,00 1042016 C 40,00 1052016 A 0,50 1052016 B 3,00 1052016 C 5,00 1062016 A 1.00 1062016 B 5.00 1062016 C 10.00 1072016 A 0.50 1072016 B 4.00 1072016 C 20.00 Hallo, ich bin beeindruckt von dem umfangreichen Wissen und der prägnanten und effektiven Instruktion, die Sie zur Verfügung stellen. Ich habe auch eine Abfrage, die ich hoffe, dass Sie Ihr Talent auch mit einer Lösung verleihen können. Ich habe eine Spalte A von 50 (wöchentlichen) Intervalldaten. Ich habe eine Spalte B daneben mit geplanter Produktion durchschnittlich pro Woche, um Ziel von 700 Widgets (70050) zu vervollständigen. In der nächsten Spalte summiere ich meine wöchentlichen Schritten bis heute (100 zum Beispiel) und rezuliere meine verbleibende Anzahl prognose avg pro verbleibende wochen (ex 700-10030). Ich möchte wöchentlich einen Graphen replotieren, der mit der aktuellen Woche beginnt (nicht das Anfangs-x-Achsen-Datum des Diagramms), mit dem summierten Betrag (100), so dass mein Ausgangspunkt die aktuelle Woche plus die verbleibende Avgweek (20) ist und Beenden Sie den linearen Graphen am Ende der Woche 30 und y Punkt von 700. Die Variablen der Identifizierung der richtigen Zelle Datum in Spalte A und endet am Ziel 700 mit einem automatischen Update vom heutigen Datum, verwechselt mich. Könnten Sie bitte helfen, mit einer Formel (ich habe versucht, IF-Logik mit Heute und nur nicht zu lösen.) Vielen Dank Bitte helfen Sie mit der richtigen Formel, um die Summe der Stunden in einem bewegten 7 Tage Zeitraum zu berechnen. Beispielsweise. Ich muss wissen, wieviel Überstunden von einer Person über eine rollende 7-tägige Periode gearbeitet wird, die vom Anfang des Jahres bis zum Ende des Jahres berechnet wird. Der Gesamtbetrag der Stunden, die gearbeitet werden, muss für die 7 rollenden Tage aktualisieren, da ich die Überstundenzeiten auf einer täglichen Basis betreibe Danke Gibt es einen Weg, um eine Summe von einer Zahl für die letzten 6 Monate zu erhalten, die ich in der Lage sein möchte, das zu berechnen Summe für die letzten 6 Monate jeden Tag. So krank muss es jeden Tag aktualisieren. Ich habe ein Excel-Blatt mit Säulen von jedem Tag für das letzte Jahr und wird schließlich jedes Jahr mehr hinzufügen. Irgendeine Hilfe würde sehr geschätzt werden, da ich stumped Hallo, ich habe ein ähnliches Bedürfnis. Ich muss einen Bericht erstellen, der neue Kundenbesuche, Gesamtkundenbesuche und andere Daten zeigt. Alle diese Felder werden täglich auf einer Tabellenkalkulation aktualisiert, ich muss diese Daten für die letzten 3 Monate nach Monaten, 3 Wochen pro Woche und letzten 60 Tagen zerlegen. Gibt es eine VLOOKUP oder Formel, oder etwas, was ich tun könnte, wird das Link auf das Blatt aktualisiert werden täglich aktualisiert wird, dass auch mein Bericht zu aktualisieren täglichHow zu berechnen gewichtete Moving Averages in Excel mit exponentiellen Glättung Excel Data Analysis für Dummies, 2nd Edition The Das exponentielle Glättungswerkzeug in Excel berechnet den gleitenden Durchschnitt. Die exponentielle Glättung gewichtet jedoch die in den gleitenden Durchschnittsberechnungen enthaltenen Werte, so dass neuere Werte einen größeren Einfluss auf die Durchschnittsberechnung haben und alte Werte einen geringeren Effekt haben. Diese Gewichtung wird durch eine Glättungskonstante erreicht. Um zu veranschaulichen, wie das Exponential-Glättungswerkzeug funktioniert, nehmen wir an, dass Sie die durchschnittliche tägliche Temperaturinformation noch einmal betrachten. Um die gewichteten Bewegungsdurchschnitte mit einer exponentiellen Glättung zu berechnen, gehen Sie wie folgt vor: Um einen exponentiell geglätteten gleitenden Durchschnitt zu berechnen, klicken Sie zuerst auf die Schaltfläche Daten tab8217s Datenanalyse. Wenn Excel das Dialogfeld Datenanalyse anzeigt, wählen Sie aus der Liste die Option Exponentielle Glättung aus und klicken dann auf OK. Excel zeigt das Dialogfeld Exponentielle Glättung an. Identifizieren Sie die Daten. Um die Daten zu identifizieren, für die Sie einen exponentiell geglätteten gleitenden Durchschnitt berechnen möchten, klicken Sie in das Textfeld Eingabebereich. Dann identifizieren Sie den Eingabebereich, indem Sie entweder eine Arbeitsblattbereichsadresse eingeben oder den Arbeitsblattbereich auswählen. Wenn Ihr Eingabebereich eine Textbeschriftung enthält, um Ihre Daten zu identifizieren oder zu beschreiben, markieren Sie das Kontrollkästchen Etiketten. Geben Sie die Glättung konstant. Geben Sie den Glättungs-Konstantenwert im Textfeld Dämpfungsfaktor ein. Die Excel-Hilfedatei schlägt vor, dass Sie eine Glättungskonstante zwischen 0,2 und 0,3 verwenden. Vermutlich aber, wenn du dieses Tool benutzt hast, hast du deine eigenen Vorstellungen darüber, was die richtige Glättungskonstante ist. (Wenn Sie sich über die Glättungskonstante ahnungslos machen, dann sollten Sie dieses Tool nicht benutzen.) Sagen Sie Excel, wo die exponentiell geglätteten gleitenden Durchschnittsdaten platziert werden sollen. Verwenden Sie das Textfeld Ausgabebereich, um den Arbeitsbereich zu identifizieren, in den Sie die gleitenden Durchschnittsdaten platzieren möchten. Im Beispiel des Arbeitsblattes platzieren Sie beispielsweise die gleitenden Durchschnittsdaten in den Arbeitsblattbereich B2: B10. (Optional) Diagramm die exponentiell geglätteten Daten. Um die exponentiell geglätteten Daten darzustellen, markieren Sie das Kontrollkästchen Diagrammausgabe. (Optional) Geben Sie an, dass Standardfehlerinformationen berechnet werden sollen. Um Standardfehler zu berechnen, markieren Sie das Kontrollkästchen Standardfehler. Excel setzt Standardfehlerwerte neben den exponentiell geglätteten gleitenden Mittelwerten. Nachdem Sie festgelegt haben, welche gleitenden durchschnittlichen Informationen Sie berechnen möchten und wo Sie es platzieren möchten, klicken Sie auf OK. Excel berechnet gleitende durchschnittliche information. Weighted Moving Average In Beispiel 1 von Simple Moving Average Forecast. Die Gewichte der vorherigen drei Werte waren alle gleich. Wir betrachten jetzt den Fall, wo diese Gewichte unterschiedlich sein können. Diese Art der Prognose heißt gewichteter gleitender Durchschnitt. Hierzu weisen wir m Gewichte w 1 zu. , W m. Wo w 1 W m 1 und definieren die prognostizierten Werte wie folgt Beispiel 1. Wiederholen Sie Beispiel 1 der Simple Moving Average Prognose, wo wir davon ausgehen, dass neuere Beobachtungen mehr als ältere Beobachtungen gewichtet werden, wobei die Gewichte w 1,6, w 2 .3 und w 3 .1 (wie im Bereich G4: G6 von 1 gezeigt) ). Abbildung 1 Gewichtete Bewegungsdurchschnitte Die Formeln in Abbildung 1 sind die gleichen wie die in Abbildung 1 der Simple Moving Average Forecast. Mit Ausnahme der prognostizierten y-Werte in Spalte C. E. g. Die Formel in Zelle C7 ist jetzt SUMPRODUCT (B4: B6, G4: G6). Die Prognose für den nächsten Wert in der Zeitreihe beträgt nun 81,3 (Zelle C19), indem man die Formel SUMPRODUCT (B16: B18, G4: G6) verwendet. Reales Statistik-Datenanalyse-Tool. Excel bietet nicht eine gewichtete gleitende Durchschnitte Datenanalyse-Tool. Stattdessen können Sie das Datenanalyse-Tool "Real Statistics Weighted Moving Averages" verwenden. Um dieses Werkzeug für Beispiel 1 zu verwenden, drücken Sie Ctr-m. Wählen Sie aus dem Hauptmenü die Option Zeitreihenfolge und dann die Option Grundlegende Vorhersagemethoden aus dem angezeigten Dialogfeld. Füllen Sie das Dialogfeld aus, das wie in Abbildung 5 der Simple Moving Average Forecast angezeigt wird. Aber diesmal wählen Sie die Option Gewichtete Umschlagsmittelwerte aus und füllen den Gewichtsbereich mit G4: G6 aus (beachten Sie, dass keine Spaltenüberschrift für den Gewichtsbereich enthalten ist). Es werden keine Parameterwerte verwendet (im Wesentlichen von Lags wird die Anzahl der Zeilen im Gewichtsbereich und der Jahreszeiten und der Forecasts wird standardmäßig auf 1). Die Ausgabe sieht genauso aus wie die Ausgabe in Abbildung 2 der Simple Moving Average Forecast. Mit der Ausnahme, dass die Gewichte bei der Berechnung der Prognosewerte verwendet werden. Beispiel 2 Verwenden Sie Solver, um die Gewichte zu berechnen, die den kleinsten mittleren quadratischen Fehler MSE erzeugen. Verwenden Sie die Formeln in Abbildung 1, wählen Sie Data gt AnalysisSolver aus und füllen Sie das Dialogfeld aus, wie in Abbildung 2 dargestellt. Abbildung 2 Solver Dialogfeld Beachten Sie, dass wir die Summe der Gewichte auf 1 beschränken müssen, was wir tun, indem wir auf die Schaltfläche klicken Taste hinzufügen. Daraufhin erscheint das Dialogfeld "Constraint hinzufügen", das wir wie in Abbildung 3 ausfüllen und dann auf die Schaltfläche OK klicken. Abbildung 3 Add Constraint-Dialogfeld Als nächstes klicken wir auf die Solve-Schaltfläche (siehe Abbildung 2), die die Daten in Abbildung 1 wie in Abbildung 4 gezeigt modifiziert. Abbildung 4 Lösungsoptimierung Wie aus Abbildung 4 ersichtlich ist, ändert Solver die Gewichte auf 0 223757 und .776243, um den Wert von MSE zu minimieren. Wie Sie sehen können, ist der minimierte Wert von 184.688 (Zelle E21 von Abbildung 4) mindestens kleiner als der MSE-Wert von 191.366 in Zelle E21 von Abbildung 2). Um diese Gewichte zu sperren, musst du auf die Schaltfläche "OK" des Dialogfelds "Solver-Ergebnisse" klicken, die in Abbildung 4 gezeigt ist. Gewichtsbewegung Durchschnittliche Prognose und MAD in EXCEL Das Problem besagt, dass der Manager der Teppichstadt-Steckdose eine genaue Vorhersage machen muss Die Nachfrage nach Soft Shag Teppich (es größter Verkäufer). Wenn der Manager nicht genügend Teppich aus der Teppichmühle bestellt, werden die Kunden ihren Teppich aus einer der Teppichstadt viele Konkurrenten kaufen. Der Manager hat die folgenden Nachfragedaten für die letzten acht Monate gesammelt Monat Demand für Soft Shag Teppich 1.000 yd 1 8 2 12 3 7 4 9 5 15 6 11 7 10 8 12 Berechnen Sie eine 3 Monate gleitende durchschnittliche Prognose für Monat 4 bis 9 Berechnen Eine gewogene 3 Monate gleitende durchschnittliche Prognose für die Monate 4 bis 9. Weisen Sie Gewichte von .53. 33, und .12 bis zum Monat in Folge, beginnend mit dem letzten Monat. Vergleichen Sie die beiden Prognosen mit MAD, die Prognose scheint genauer zu sein. Lösungsvorschau Bitte beachten Sie den Anhang Solution. xlsx für die Arbeit und. Lösungszusammenfassung Eine 3 Monate bewegte durchschnittliche Prognose und eine weitere 3-monat gewichtete bewegliche durchschnittliche Prognose, die unterschiedliche Glättungswägungsfaktoren verwendet, wurde in Excel durchgeführt. Prognosefehler (MAD) wurde berechnet und die beiden Prognosen wurden mit diesen MAD-Werten verglichen. In den Warenkorb entfernen