Gleitenden Durchschnitt mit BEREICH.VERSCHIEBEN berechnen

Das Gute an der Excel-Funktion BEREICH.VERSCHIEBEN ist, dass sie, ausgehend von einer festen Zelle auf Deinem Arbeitsblatt, in alle Richtungen operieren kann, nach oben, nach unten, nach links oder nach rechts.

Diese Eigenschaft soll bei der Berechnung eines gleitenden Durchschnitts genutzt werden.

Eine Möglichkeit zeigt dieser Beitrag.

1. Die Wertetabelle

Angenommen, in einem Unternehmen wurden die Produktionsmengen von 6 Artikeln für den Zeitraum von Januar 2020 bis jetzt, Februar 2021, in einer Tabelle festgehalten, hier ein Auszug:

2. Die Einstellungen zur Auswahl

Für die Auswahl des Artikels, des aktuellen Monats und der Anzahl der Perioden, über die ein gleitender Durchschnitt berechnen werden kann, wurden über die Datenüberprüfung Listen angelegt.

Unter „akt. Monat“ ist hier der Monat zu verstehen, bis zu dem der gleitende Durchschnitt über die ausgewählte Anzahl der Perioden berechnet werden soll.

So bedeutet „6“ im Beispiel, dass der Durchschnitt für den Artikel „ZB 87 bn“ von Januar bis Juni 2020 zu rechnen ist.

Für die Auswahl der Perioden stehen 3, 6, 9 und 12 Monate zur Verfügung.

3. Die Berechnungsformel

Die Funktion BEREICH.VERSCHIEBEN hat folgende Syntax:

BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;[Höhe];[Breite])

Du willst zuerst aber wissen, ob es links vom aktiven Monat (im Beispiel Jun 20) weitere 5 (6-1) Monate (im Beispiel) für die Durchschnittsberechnung gibt. Das kannst Du mit dieser Formel prüfen:

            =VERGLEICH(EDATUM(C14;-C15+1);B4:O4;0))

Als Ergebnis erhältst Du die 1 (Jan 20). Nun prüfts Du noch, ob das Ergebnis eine Zahl ist, dazu setzt Du noch die Funktion ISTZAHL davor:

            =ISTZAHL(VERGLEICH(EDATUM(C14;-C15+1);B4:O4;0))

Ergebnis ist WAHR.

Mit einer WENN-Abfrage machst Du weiter. Wenn die vorherige Berechnung WAHR ergibt, kann der Mittelwert über die mit Perioden vorgegebene Anzahl der Monate ermittelt werden.

=MITTELWERT(BEREICH.VERSCHIEBEN(INDIREKT(ADRESSE(4;1+VERGLEICH($C$14;B$4:O$4;0);1));VERGLEICH($C$13;A5:A10;0);0;1;-C15))

Das Argument Bezug in BEREICH.VERSCHIEBEN erhältst Du mit der Formel

=INDIREKT(ADRESSE(4;1+VERGLEICH($C$14;B$4:O$4;0);1))

Darin enthalten ist die Funktion ADRESSE. Diese liefert Dir als Bezug $G$4. Das ist genau die Zelle, in der „Jun 20“ in Zeile 4 steht.

Das Argument Zeile in BEREICH.VERSCHIEBEN berechnest Du mit der Formel

            =VERGLEICH($C$13;A5:A10;0)

Der gesuchte Artikel aus C13 steht in Zeile 3 der Liste A5:A10.

Das Argument Spalte in BEREICH.VERSCHIEBEN besetzt Du mit 0, Du bleibst in der Spalte des Bezuges.

Für das Argument Höhe nimmst Du eine 1, Du bleibst in der Zeile.

Für das Argument Breite trägst Du

            -C15

ein. Inklusive Jun 20 sollen es 6 Zellen sein, die in die Mittelwertberechnung einbezogen werden.

Letztlich bleibt noch die Sonst-Anweisung in der WENN-Abfrage. Sollte als die ISTZAHL-Formel FALSCH ergeben, soll in Zelle C17 die Mitteilung “Monate nicht verfügbar” angezeigt werden.

4. Zusammenfassung

Die beschrieben Methode ermöglicht es Dir, einen Artikel und einen Ausgangsmonat auszuwählen und davon ausgehend über die gewählten Perioden einen Mittelwert zu berechnen.

Wählst Du in C14 den nächsten Monat nach rechts, gleitet der Mittelwert ebenfalls.

Die Tabelle sieht gesamt so aus:

Die Datei dazu kannst Du herunterladen: