Das Glätten von Excel-Diagrammdaten mit einem festen gleitenden Durchschnitt funktioniert, wenn Sie Erfahrung mit den Daten haben und wissen, wie variabel sie sind. Wenn sich die Daten jedoch ändern oder neu sind, möchten Sie oder das Management vielleicht mit einer anderen Anzahl von Perioden im Glättungsdurchschnitt experimentieren.
In diesem Beispiel wird eine OFFSET-Formel verwendet, um einen Durchschnitt über einen dynamischen Bereich zu erstellen. Sie geben die Anzahl der Monate ein, die Sie für einen Glättungszeitraum verwenden möchten, und MITTELWERT und OFFSET berechnen, über wie viele Zellen der Durchschnitt gebildet werden soll.
Bevor Sie den MITTELWERT dynamisch machen, sollten Sie zunächst verstehen, wie OFFSET funktioniert. In der folgenden Abbildung enthält Zelle G26 die Formel für die Glättung der Daten in Zeile 20,
=MITTELWERT(OFFSET(G20,0,0,1,-3))
OFFSET gibt den Bereich an, über den AVERAGE die Daten glättet.
Diese Formel ist ein Zwischenschritt, der Ihnen hilft zu verstehen, wie die dynamische Formel funktioniert. Die Funktion OFFSET gibt einen Bereich zurück, auf den AVERAGE einwirken wird. Die Attribute von OFFSET sind:
OFFSET(Referenz,Zeilen,Spalten,,)
In diesem Beispiel beginnt der Bereich, den OFFSET berechnet, bei G20 und hat einen Zeilen- oder Spaltenversatz von Null. Mit anderen Worten, die obere linke Ecke des Bereichs, den OFFSET berechnet, ist einfach G20. Er wird nicht um Zeilen oder Spalten verschoben oder versetzt. Der berechnete Bereich liegt eine Zeile höher und umfasst die aktuelle Zelle G26 und zwei weitere links davon (das ist eine -3). Der berechnete Bereich ist also G20:E20.
Kopieren Sie die Formel in G26 nach rechts, und Sie werden sehen, dass sie den Durchschnitt der darüber liegenden Datenzelle in Zeile 20 und der beiden vorhergehenden Zellen auf der linken Seite berechnet.
So, hier ist der Trick, der dies zu einem dynamischen Durchschnitt über einen von Ihnen gewählten Bereich macht. Was passiert, wenn Sie die -3 in der Formel durch eine Zellreferenz ersetzen?
In der folgenden Abbildung wurde die Formel in Zelle G26 so geändert, dass sie durch die Zahl in Zelle E24 angegeben wird,
MITTELWERT(ABSTAND(G20,0,0,1,-$E$24))
$E$24 ist die grün schattierte Zelle. Die Zahl, die Sie in die Zelle E24 eingeben, bestimmt die Breite der Zellen, die für den Durchschnitt verwendet werden.
Die Eingabe eines Wertes in E24 ändert den Bereich, der für die Glättung der Daten verwendet wird.
An dieser Stelle können Sie ein Drehrad oder ein Dropdown-Menü verwenden, um dem Benutzer die Wahl zu geben, wie breit der geglättete Durchschnitt sein soll. Das Ergebnis der Auswahl sollte in Zelle E24 eingetragen werden.
Die glättenden Excel-Diagrammdaten mit fester oder dynamischer Glättung herunterladen
Es gibt ein Problem mit dieser Anordnung. Wenn der Benutzer eine größere Zahl in E24 eingibt, als Zellen links davon vorhanden sind, schließt OFFSET leere Zellen und die Beschriftung ein. Dies führt zu einem falschen Durchschnittswert. Wenn E24 so groß ist, dass OFFSET über das Blatt hinausgeht, erzeugt AVERAGE einen Fehler. Was ist zu tun? Wir brauchen eine Lösung, die nicht nur einen Fehler verhindert, wie ISERROR, sondern eine, die falsche Antworten verhindert.
In dieser nächsten Abbildung wurde die Formel in Zelle G26 angepasst. Zeile 25 enthält eine Reihe von Zahlen, die die Grenzen für gültige Bereiche auf der linken Seite darstellen. Die neue Formel in Zelle G26 sieht in Zeile 25 nach, und wenn die Zahl in Zelle E24 größer ist als der Grenzwert in Zeile 25, dann wird NA() ausgegeben. Diese neue Fehlerkorrekturformel in G26 lautet:
=IF($E$24>G25,NA(),MITTELWERT(OFFSET(G20,0,0,1,-$E$24)))
Wenn ein Excel-Diagramm auf NA() in einer Zelle verweist, wird nichts gezeichnet. Das verhindert, dass die Glättungslinie auf Null fällt.
Ändern Sie die Formel für die dynamische Glättung, um Fehler zu vermeiden.
Probleme mit der Glättung von Daten und wie man sie umgehen kann
Jetzt können Sie den Benutzer jede beliebige Glättungsperiode ausprobieren lassen. Es gibt jedoch ein Problem mit der Glättung von Daten (in diesem Fall gibt es immer ein „aber“ oder ein „aber“). Durch die Glättung wird zwar die Volatilität herausgenommen, aber es bedeutet auch, dass man Richtungsänderungen, Wendepunkte oder neue Trends erst nach der Hälfte des Glättungszeitraums sieht. Sie liegen hinter der Kurve.
Das Beste aus beiden Welten ist die Verwendung einer Glättungsfunktion mit gewichtetem Durchschnitt. Auf diese Weise können Sie den kurzfristigen Daten mehr Gewicht geben, so dass Sie Trends sehen, aber Sie können auf der Grundlage älterer Daten glätten, um einen Teil der Volatilität zu eliminieren. Klingt wie ein guter Fall für einen anderen Artikel, Glätten von Excel-Diagrammdaten mit einem gewichteten Durchschnitt.
Laden Sie die Beispieldatei für das Glätten von Excel-Diagrammdaten mit gleitender oder dynamischer Glättung herunter
Verwandte Artikel,
- Glätten von Excel-Diagrammdaten mit gleitendem Durchschnitt
- Glätten von Excel-Diagrammdaten mit einem gewichteten Durchschnitt