BEREICH.VERSCHIEBEN gibt einen Bezug zurück, der eine bestimmte Anzahl Zeilen und Spalten von einer Startzelle entfernt liegt. Seine eigentliche Stärke sind dynamische Bereiche: Bezüge, die mitwachsen oder schrumpfen, wenn du Daten ergänzt. Deshalb steckt es hinter vielen selbst wachsenden Diagrammen und laufenden Summen.
Syntax
=BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite])
- Bezug - die Startzelle oder der Startbereich.
- Zeilen - wie viele Zeilen verschoben wird (positiv = nach unten, negativ = nach oben).
- Spalten - wie viele Spalten verschoben wird (positiv = nach rechts, negativ = nach links).
- Höhe - optional. Wie viele Zeilen der zurückgegebene Bereich umfassen soll.
- Breite - optional. Wie viele Spalten der zurückgegebene Bereich umfassen soll.
Eine einzelne Zelle zurückgeben
Starte bei A1 und verschiebe 3 Zeilen nach unten und 1 Spalte nach rechts, um B4 zu erreichen:
=BEREICH.VERSCHIEBEN(A1; 3; 1)
Einen Bereich zurückgeben und summieren
Mit Höhe und Breite gibt BEREICH.VERSCHIEBEN einen Block zurück, den du an eine andere Funktion übergeben kannst. Summiere einen Block aus 5 Zeilen und 1 Spalte, der eine Zeile unter A1 beginnt:
=SUMME(BEREICH.VERSCHIEBEN(A1; 1; 0; 5; 1))
Einen dynamischen Summenbereich bauen
Ein klassischer Einsatz ist das Summieren von einem festen Anfang bis zur letzten Zeile, wie viele es auch sind. Mit Werten in Spalte B:
=SUMME(BEREICH.VERSCHIEBEN(B2; 0; 0; ANZAHL(B:B); 1))
ANZAHL(B:B) liefert die Höhe, sodass sich der summierte Bereich an die Zahl der Zahlen in Spalte B anpasst. Ergänze eine Zeile, und die Summe bezieht sie ein, ganz ohne Formeländerung.
Laufende Summe bis zur aktuellen Zeile
Innerhalb einer Tabelle kannst du von der ersten Datenzeile bis zur aktuellen summieren:
=SUMME(BEREICH.VERSCHIEBEN($B$2; 0; 0; ZEILE()-1; 1))
Häufige Probleme
- #BEZUG! - die Verschiebung zeigt außerhalb des Tabellenblatts (zum Beispiel negative Zeilen über Zeile 1). Prüfe deine Zeilen- und Spaltenverschiebung.
- #WERT! - Höhe oder Breite ist 0 oder negativ. Beide müssen positive ganze Zahlen sein.
- Das Ergebnis berechnet sich ständig neu. BEREICH.VERSCHIEBEN ist eine volatile Funktion: Sie berechnet sich bei jeder Änderung irgendwo in der Arbeitsmappe neu, auch bei nicht verwandten Bearbeitungen. Bei großen Blättern bremst das Excel. Nutze für dynamische Bereiche lieber INDEX oder eine Tabelle, wenn Tempo zählt.
BEREICH.VERSCHIEBEN im Vergleich zu INDEX
INDEX kann ebenfalls dynamische Bereiche bauen und ist nicht volatil, also meist die schnellere Wahl. =SUMME(B2:INDEX(B:B; ANZAHL(B:B)+1)) summiert einen wachsenden Bereich ohne den Neuberechnungs-Aufwand von BEREICH.VERSCHIEBEN. Greif zu BEREICH.VERSCHIEBEN, wenn du gezielt einen Bezug um eine variable Anzahl Zeilen oder Spalten verschieben musst.
Häufig gestellte Fragen
Was macht die Funktion BEREICH.VERSCHIEBEN?
BEREICH.VERSCHIEBEN gibt einen Bezug zurück, der eine bestimmte Anzahl Zeilen und Spalten von einer Startzelle entfernt liegt, und optional einen Bereich mit einer bestimmten Höhe und Breite. Man nutzt es, um auf ein bewegliches Ziel zu zeigen oder einen Bereich zu definieren, der beim Ergänzen von Daten seine Größe ändert.
Warum bremst BEREICH.VERSCHIEBEN meine Tabelle?
BEREICH.VERSCHIEBEN ist volatil, das heißt, es berechnet sich jedes Mal neu, wenn sich irgendetwas in der Arbeitsmappe ändert, nicht nur seine eigenen Eingaben. Bei vielen solchen Formeln summiert sich das. Ersetze sie durch INDEX oder strukturierte Tabellenbezüge, die nicht volatil sind.
Was ist der Unterschied zwischen BEREICH.VERSCHIEBEN und INDEX?
Beide können eine Zelle oder einen dynamischen Bereich zurückgeben. BEREICH.VERSCHIEBEN verschiebt um eine Anzahl Zeilen und Spalten und ist volatil. INDEX gibt eine Position innerhalb eines Bereichs zurück und ist nicht volatil, deshalb wird INDEX für dynamische Bereiche bevorzugt, wenn die Leistung zählt.
Wie baue ich einen dynamischen Bereich, der mit meinen Daten wächst?
Nutze BEREICH.VERSCHIEBEN mit einer Anzahl als Höhe: =BEREICH.VERSCHIEBEN(B2; 0; 0; ANZAHL(B:B); 1) umfasst so viele Zeilen, wie es Zahlen in Spalte B gibt. Für eine schnellere, nicht volatile Fassung erledigt eine Tabelle oder ein Bereich auf Basis von INDEX dieselbe Aufgabe.