Zusammenfassung
Um einen gleitenden oder gleitenden Durchschnitt zu berechnen, können Sie eine einfache Formel verwenden, die auf der AVERAGE-Funktion mit relativen Referenzen basiert. In dem gezeigten Beispiel lautet die Formel in E7:
=AVERAGE(C5:C7)
Beim Kopieren der Formel wird ein gleitender 3-Tage-Durchschnitt basierend auf dem Verkaufswert für den aktuellen Tag und die beiden vorherigen Tage berechnet.
Im Folgenden finden Sie eine flexiblere Option, die auf der OFFSET-Funktion basiert und variable Zeiträume verarbeitet.
Über gleitende Durchschnitte
Ein gleitender Durchschnitt (auch als gleitender Durchschnitt bezeichnet) ist ein Durchschnitt, der auf Teilmengen von Daten in bestimmten Intervallen basiert. Durch die Berechnung eines Durchschnitts in bestimmten Intervallen werden die Daten geglättet, indem die Auswirkungen zufälliger Schwankungen verringert werden. Dies erleichtert das Erkennen allgemeiner Trends, insbesondere in einem Diagramm. Je größer das Intervall ist, das zur Berechnung eines gleitenden Durchschnitts verwendet wird, desto mehr Glättung tritt auf, da in jedem berechneten Durchschnitt mehr Datenpunkte enthalten sind.
Erläuterung
Die im Beispiel gezeigten Formeln verwenden alle die Funktion AVERAGE mit einer relativen Referenz, die für jedes bestimmte Intervall eingerichtet wurde. Der gleitende 3-Tage-Durchschnitt in E7 wird berechnet, indem DURCHSCHNITTLICH ein Bereich eingegeben wird, der den aktuellen Tag und die beiden vorherigen Tage wie folgt umfasst:
=AVERAGE(C5:C7) // 3-day average
Der 5-Tage- und der 7-Tage-Durchschnitt werden auf die gleiche Weise berechnet. In jedem Fall wird der für AVERAGE bereitgestellte Bereich um die erforderliche Anzahl von Tagen erweitert:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Alle Formeln verwenden eine relative Referenz für den Bereich, der für die AVERAGE-Funktion bereitgestellt wird. Während die Formeln in die Spalte kopiert werden, ändert sich der Bereich in jeder Zeile, um die für jeden Durchschnitt erforderlichen Werte einzuschließen.
Wenn die Werte in einem Liniendiagramm dargestellt werden, ist der Glättungseffekt klar:
Unzureichende Daten
Wenn Sie die Formeln in der ersten Zeile der Tabelle starten, verfügen die ersten Formeln nicht über genügend Daten, um einen vollständigen Durchschnitt zu berechnen, da der Bereich über der ersten Datenzeile liegt:
Dies kann ein Problem sein oder auch nicht, abhängig von der Struktur des Arbeitsblatts und davon, ob es wichtig ist, dass alle Durchschnittswerte auf der gleichen Anzahl von Werten basieren. Die Funktion DURCHSCHNITTLICH ignoriert automatisch Textwerte und leere Zellen, sodass weiterhin ein Durchschnitt mit weniger Werten berechnet wird. Deshalb "funktioniert" es in E5 und E6.
Eine Möglichkeit, eindeutig auf unzureichende Daten hinzuweisen, besteht darin, die aktuelle Zeilennummer zu überprüfen und mit #NA abzubrechen, wenn weniger als n Werte vorhanden sind. Für den 3-Tage-Durchschnitt könnten Sie beispielsweise Folgendes verwenden:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Der erste Teil der Formel generiert einfach eine "normalisierte" Zeilennummer, beginnend mit 1:
ROW()-ROW($C$5)+1 // relative row number
In Zeile 5 ist das Ergebnis 1, in Zeile 6 ist das Ergebnis 2 und so weiter.
Wenn die aktuelle Zeilennummer kleiner als 3 ist, gibt die Formel # N / A zurück. Andernfalls gibt die Formel wie zuvor einen gleitenden Durchschnitt zurück. Dies ahmt das Verhalten der Analysis Toolpak-Version von Moving Average nach, die # N / A ausgibt, bis der erste vollständige Zeitraum erreicht ist.
Mit zunehmender Anzahl von Perioden gehen Ihnen jedoch möglicherweise die Zeilen über den Daten aus und Sie können den erforderlichen Bereich nicht in AVERAGE eingeben. Beispielsweise können Sie mit dem Arbeitsblatt keinen gleitenden 7-Tage-Durchschnitt wie gezeigt einrichten, da Sie keinen Bereich eingeben können, der 6 Zeilen über C5 liegt.
Variable Perioden mit OFFSET
Eine flexiblere Methode zur Berechnung eines gleitenden Durchschnitts ist die OFFSET-Funktion. OFFSET kann einen Dynamikbereich erstellen, dh wir können eine Formel einrichten, bei der die Anzahl der Perioden variabel ist. Die allgemeine Form ist:
=AVERAGE(OFFSET(A1,0,0,-n,1))
Dabei ist n die Anzahl der Perioden, die in jedem Durchschnitt enthalten sein sollen. Wie oben gibt OFFSET einen Bereich zurück, der an die AVERAGE-Funktion übergeben wird. Unten sehen Sie diese Formel in Aktion, wobei "n" der benannte Bereich E2 ist. OFFSET erstellt ab Zelle C5 einen Bereich, der bis zu den vorherigen Zeilen zurückreicht. Dies wird erreicht, indem eine Höhe gleich negativ n verwendet wird. Wenn E5 in eine andere Zahl geändert wird, wird der gleitende Durchschnitt für alle Zeilen neu berechnet:
Die kopierte Formel in E5 lautet:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Wie die obige Originalformel hat auch die Version mit OFFSET das Problem, dass in den ersten Zeilen nicht genügend Daten vorhanden sind, je nachdem, wie viele Punkte in E5 angegeben sind.
In dem gezeigten Beispiel werden die Durchschnittswerte erfolgreich berechnet, da die AVERAGE-Funktion Textwerte und leere Zellen automatisch ignoriert und keine anderen numerischen Werte über C5 vorhanden sind. Während der in E5 an AVERAGE übergebene Bereich C1: C5 ist, gibt es nur einen zu mittelnden Wert von 100. Mit zunehmenden Zeiträumen erstellt OFFSET jedoch weiterhin einen Bereich, der sich über den Datenanfang hinaus erstreckt und schließlich in diesen Bereich übergeht oben im Arbeitsblatt und Rückgabe eines # REF-Fehlers.
Eine Lösung besteht darin, die Größe des Bereichs auf die Anzahl der verfügbaren Datenpunkte zu "begrenzen". Dies kann mithilfe der MIN-Funktion erfolgen, um die für die Höhe verwendete Anzahl wie unten dargestellt einzuschränken:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Das sieht ziemlich beängstigend aus, ist aber eigentlich ganz einfach. Wir begrenzen die Höhe, die mit der MIN-Funktion an OFFSET übergeben wird:
MIN(ROW()-ROW($C$5)+1,n)
Innerhalb von MIN ist der erste Wert eine relative Zeilennummer, berechnet mit:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Der zweite Wert, der MIN gegeben wird, ist die Anzahl der Perioden n. Wenn die relative Zeilennummer kleiner als n ist, gibt MIN die aktuelle Zeilennummer für die Höhe an OFFSET zurück. Wenn die Zeilennummer größer als n ist, gibt MIN n zurück. Mit anderen Worten, MIN gibt einfach den kleineren der beiden Werte zurück.
Ein nettes Merkmal der OFFSET-Option ist, dass n leicht geändert werden kann. Wenn wir n in 7 ändern und die Ergebnisse zeichnen, erhalten wir ein Diagramm wie das folgende:
Hinweis: Eine Besonderheit bei den obigen OFFSET-Formeln ist, dass sie in Google Sheets nicht funktionieren, da die OFFSET-Funktion in Sheets keinen negativen Wert für Höhe oder Breite zulässt. Die angehängte Tabelle enthält Problemumgehungsformeln für Google-Tabellen.
