Tutorial

Tutorial: Die letzten 6 Monatsumsätze mit der Excel-Funktion BEREICH.VERSCHIEBEN summieren

Melden

Hier ist ein ausführliches Tutorial, wie du die Excel-Funktion BEREICH.VERSCHIEBEN (englisch: OFFSET) nutzt, um dynamisch die Umsätze der letzten 6 Monate zu summieren.

In vielen Berichten ist es wichtig, rollierende Summen anzuzeigen (z. B. „Umsatz der letzten 6 Monate“). Anstatt die Formel jeden Monat manuell anzupassen, kannst du die Funktion BEREICH.VERSCHIEBEN nutzen, um diesen Bereich automatisch zu berechnen.

1. Die Syntax verstehen

Bevor wir starten, schauen wir uns die deutsche Funktion und ihre Argumente an:

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

  • Bezug: Der Startpunkt (z. B. die Überschrift deiner Umsatzspalte).
  • Zeilen: Wie viele Zeilen du vom Startpunkt nach unten (positiv) oder oben (negativ) gehen willst.
  • Spalten: Wie viele Spalten du nach rechts (positiv) oder links (negativ) gehen willst.
  • Höhe: Wie viele Zeilen der Zielbereich umfassen soll (hier: 6).
  • Breite: Wie viele Spalten der Zielbereich umfassen soll (hier: 1).

2. Das Szenario

Stell dir vor, du hast eine Tabelle mit folgenden Daten:

  • Spalte A: Monate (Januar bis Dezember)
  • Spalte B: Umsätze (B2 bis B13)

Wir möchten eine Formel schreiben, die immer die letzten 6 eingetragenen Werte in Spalte B summiert.

3. Schritt-für-Schritt-Anleitung

Schritt A: Den letzten Eintrag finden

Zuerst müssen wir wissen, wie viele Monate bereits Daten enthalten. Das machen wir mit der Funktion ANZAHL2. ANZAHL2(B:B) zählt alle Zellen in Spalte B, die nicht leer sind. Wenn deine Tabelle eine Überschrift hat, subtrahieren wir 1: ANZAHL2(B:B)-1

Schritt B: Den Bereich definieren

Jetzt nutzen wir BEREICH.VERSCHIEBEN. Unser Startpunkt ist die Zelle B1 (die Überschrift „Umsatz“).

  1. Bezug: B1
  2. Zeilen: Wir gehen so viele Zeilen nach unten, wie Daten vorhanden sind: ANZAHL2(B:B)-1.
  3. Spalten: Wir bleiben in der gleichen Spalte: 0.
  4. Höhe: Jetzt kommt der Trick. Da wir die letzten 6 Monate wollen, gehen wir vom aktuellen Punkt 6 Zeilen nach oben. Das definieren wir mit der Höhe: -6.
  5. Breite: Die Breite bleibt 1 Spalte.

Schritt C: Die SUMME-Funktion hinzufügen

Um das Ergebnis zu erhalten, umschließen wir alles mit der SUMME-Funktion.

Die fertige Formel lautet:

=SUMME(BEREICH.VERSCHIEBEN(B1; ANZAHL2(B:B)-1; 0; -6; 1))

4. Funktionsweise der Formel erklärt

  1. ANZAHL2(B:B)-1: Wenn du Daten bis Zeile 10 hast (inkl. Überschrift), ergibt dies 9.
  2. BEREICH.VERSCHIEBEN(B1; 9; 0; ...): Excel springt von B1 neun Zeilen nach unten zu B10 (dem aktuellsten Monat).
  3. ; -6; 1): Von B10 aus wird ein Bereich markiert, der 6 Zeilen hoch ist (also B5 bis B10) und 1 Spalte breit.
  4. SUMME(...): Diese 6 Zellen werden addiert.

5. Profi-Tipp: Fehler vermeiden

  • Leere Zellen: Die Formel mit ANZAHL2 funktioniert nur reibungslos, wenn keine Leerzeilen zwischen deinen Daten sind.
  • Volatilität: Die Funktion BEREICH.VERSCHIEBEN ist „volatil“. Das bedeutet, Excel berechnet sie bei jeder kleinsten Änderung im Arbeitsblatt neu. Bei sehr großen Tabellen (zehntausende Zeilen) kann dies die Performance verlangsamen.
  • Alternative (INDEX): Wenn du eine performantere Lösung suchst, kannst du die INDEX-Funktion nutzen:
    =SUMME(INDEX(B:B; ANZAHL2(B:B)):INDEX(B:B; ANZAHL2(B:B)-5))

Zusammenfassung

Mit BEREICH.VERSCHIEBEN erstellst du ein dynamisches Fenster über deine Daten. Sobald du in Zeile 11 einen neuen Umsatz einträgst, rutscht das „Fenster“ automatisch ein Stück weiter und summiert immer die aktuellsten 6 Monate.

0