Dynamische Bereiche in Excel: BEREICH.VERSCHIEBEN Funktion
Wenn Sie INDIREKT als einen Übersetzer (Text zu Bezug) verstehen, dann stellen Sie sich BEREICH.VERSCHIEBEN (engl. OFFSET) als ein GPS-System für Ihre Zellen vor.
Diese Funktion ist eines der flexibelsten Werkzeuge in Excel, um dynamische Bereiche zu erstellen – also Bereiche, die ihre Größe oder Position ändern können, basierend auf anderen Berechnungen. Anstatt auf einen festen Bereich wie A1:A10 zu verweisen, sagen Sie Excel: „Starte bei A1, gehe 3 Zeilen nach unten, 2 Spalten nach rechts und gib mir von dort einen Bereich, der 5 Zeilen hoch und 1 Spalte breit ist.“
Was macht BEREICH.VERSCHIEBEN?
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.
Praktisches Beispiel: Die Summe der „letzten N Monate“ berechnen
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!
Wichtige Hinweise
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.
Beliebte Beiträge
So funktioniert die bedingte Formatierung in Excel
So funktioniert die bedingte Formatierung in Microsoft Excel und bietet vielfältige Möglichkeiten für die Darstellung von Daten.
Briefvorlagen mit Formularfeldern in Word erstellen
Erstellen Sie einfach Ihre eigenen Formulare in Word durch den Einsatz von Steuerelementen die sich frei konfigurieren lassen.
Die besten Tipps für Excel – Leitfaden
Diese 10 Tipps zu Microsoft Excel 2016/2019 mit Fragen die viele Anwender täglich stellen werden Ihnen garantiert weiterhelfen, und eine Menge Zeit ersparen.
Schriftarten in Word durch Google Fonts erweitern
Erweitern sie mit wenigen Schritten die Auswahl an Schriftarten in Word ganz einfach indem Sie die Vielfalt von Google Fonts verwenden.
Sverweis und Wverweis in Excel anwenden – Tutorial
So holen Sie per S-Verweis und W-Verweis zielgerichtet Daten aus einer definierten Quelle in Microsoft Excel raus.
Word Dokumente vor Bearbeitung schützen
Schützen Sie Ihre Dokumente in Microsoft Word vor unberechtigter Bearbeitung oder sperren den Zugriff komplett.
Angebote 2025/2026 in: Vorlagen
