Dynamische Bereiche in Excel: BEREICH.VERSCHIEBEN Funktion

Die Funktion gibt einen Zellbezug zurück, der von einer Startzelle (oder einem Startbereich) „verschoben“ ist. Sie verschiebt keine Zellen oder Daten, sondern erstellt nur einen neuen Bezugsrahmen, den Sie dann in anderen Funktionen (wie SUMME, MITTELWERT oder in Diagrammen) verwenden können.

Die Syntax aufgeschlüsselt

Die Syntax ist umfangreicher als bei INDIREKT, aber jede Komponente hat eine klare Aufgabe:

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

• Bezug (Erforderlich): Ihr „Anker“ oder Startpunkt. Von dieser Zelle aus beginnt die „Reise“. (z.B. A1)
• Zeilen (Erforderlich): Wie viele Zeilen Sie sich vom Bezug nach unten (positive Zahl) oder oben (negative Zahl) bewegen möchten.
• Spalten (Erforderlich): Wie viele Spalten Sie sich vom Bezug nach rechts (positive Zahl) oder links (negative Zahl) bewegen möchten.
• [Höhe] (Optional): Definiert, wie viele Zeilen der neue Bereich haben soll. Wenn weggelassen, hat der neue Bereich dieselbe Höhe wie der Bezug.
• [Breite] (Optional): Definiert, wie viele Spalten der neue Bereich haben soll. Wenn weggelassen, hat der neue Bereich dieselbe Breite wie der Bezug.

Sie könnten manuell =SUMME(B5:B7) eingeben. Wenn Sie aber „Juli“ hinzufügen, müssten Sie die Formel zu =SUMME(B6:B8) ändern. Das ist mühsam.

Die dynamische Lösung mit BEREICH.VERSCHIEBEN:

• Wir erstellen eine Formel, die den zu summierenden Bereich automatisch findet.
• In einer Zelle (z.B. D1) geben Sie ein, wie viele Monate Sie betrachten möchten: 3
• In einer anderen Zelle (z.B. D2) kommt die BEREICH.VERSCHIEBEN-Formel, verpackt in eine SUMME-Funktion:
• =SUMME(BEREICH.VERSCHIEBEN(B1; ANZAHL2(B:B)-D1; 0; D1; 1))

Was passiert hier? Lassen Sie es uns zerlegen:

• BEREICH.VERSCHIEBEN(…): Dies ist der Kern.
• B1: Unser Bezug. Wir starten bei der Überschrift „Umsatz“.
• ANZAHL2(B:B)-D1: Das Zeilen-Argument.
• ANZAHL2(B:B) zählt alle nicht-leeren Zellen in Spalte B. Im Juni sind das 7 (Überschrift + 6 Monate).
• D1 ist 3.

Die Berechnung ist also: 7 – 3 = 4.

• Excel wird angewiesen: „Gehe vom Bezug (B1) 4 Zeilen nach unten.“ (Es landet bei Zelle B5, dem „April“).
• 0: Das Spalten-Argument. „Gehe 0 Spalten nach links oder rechts.“ (Bleibt in Spalte B).
• D1: Das Höhe-Argument. „Erstelle von B5 aus einen Bereich, der so hoch ist wie der Wert in D1 (also 3 Zeilen).“
• 1: Das Breite-Argument. „Der Bereich soll 1 Spalte breit sein.“
• Ergebnis: Die Formel erstellt dynamisch den Bezug B5:B7. SUMME addiert diesen Bereich und das Ergebnis ist 4.200 € (1.300 + 1.400 + 1.500).

Der magische Moment: Nächsten Monat fügen Sie „Juli“ mit 1.600 € in Zeile 8 hinzu.

• ANZAHL2(B:B) ist jetzt 8.
• Das Zeilen-Argument wird zu 8 – 3 = 5.
• Die Formel startet bei B1, geht 5 Zeilen nach unten (zu B6, „Mai“), erstellt einen Bereich, der 3 Zeilen hoch ist (B6:B8).
• Die Summe berechnet nun automatisch B6:B8 (Mai, Juni, Juli). Sie mussten die Formel nicht anfassen!

Wie INDIREKT ist BEREICH.VERSCHIEBEN eine volatile (flüchtige) Funktion.

Das bedeutet:

• Leistung: Die Formel wird bei jeder einzelnen Änderung in Ihrer Arbeitsmappe neu berechnet – selbst wenn Sie in einem ganz anderen Tabellenblatt etwas tippen.
• Performance-Problem: Wenn Sie Tausende von BEREICH.VERSCHIEBEN-Formeln verwenden, kann Ihre Excel-Datei spürbar langsamer werden.

Fazit und moderne Alternativen

BEREICH.VERSCHIEBEN ist unglaublich mächtig für die Erstellung dynamischer Bereiche, insbesondere für Diagramme, die mit ihren Daten „mitwachsen“ sollen (indem man die Formel in einem benannten Bereich verwendet).

Allerdings gibt es heute oft bessere, nicht-volatile Alternativen:

• Excel-Tabellen (Strg + T): Die beste Alternative. Wenn Sie Ihre Daten als „Tabelle formatieren“, erweitern sich Formeln und Diagramme, die darauf verweisen, automatisch, wenn Sie neue Daten hinzufügen. Dies macht BEREICH.VERSCHIEBEN für diesen Zweck oft überflüssig.
• INDEX: Eine Kombination aus INDEX und ANZAHL2 kann oft dieselben Ergebnisse wie BEREICH.VERSCHIEBEN erzielen, ist aber nicht volatil und daher schneller.

Verwenden Sie BEREICH.VERSCHIEBEN, wenn Sie die ultimative Flexibilität beim „Verschieben“ und „Größenändern“ eines Bezugs benötigen, aber seien Sie sich der Performance-Kosten bewusst.