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
Der ultimative Guide für Kopf- und Fußzeilen in Excel
Tabellen im Griff: Dieser Guide zeigt Ihnen, wie Sie Kopf- und Fußzeilen in Excel richtig nutzen. Von dynamischen Seitenzahlen über das fehlerfreie Einfügen von Firmenlogos bis hin zu Layout-Tipps für den perfekten Druck. Schluss mit dem Excel-Druck-Chaos!
Seiten in Word verschieben und vertauschen: Die besten Methoden
Das Verschieben von Seiten in Word wirkt oft knifflig. Dieser Ratgeber zeigt Ihnen vier bewährte Methoden für ein perfektes Layout: Nutzen Sie den Navigationsbereich für strukturierte Texte oder arbeiten Sie mit Copy & Paste und leeren Seiten für unformatierte Dokumente.
Word-Formatierungen endlich im Griff – Tutorial
Verzweifeln Sie nicht an zerschossenen Word-Dokumenten! Dieser Leitfaden zeigt Ihnen, wie Sie hartnäckige Formatierungen löschen, Formatvorlagen richtig nutzen und Steuerzeichen lesen. So sparen Sie Zeit und Nerven bei der Textverarbeitung.
Passwort für Word- und Excel-Dateien vergessen: So retten Sie Ihre Daten
Ein vergessenes Passwort bei Word oder Excel ist ärgerlich, bedeutet aber nicht immer Datenverlust. Während sich Schreibschutz durch einen XML-Trick umgehen lässt, erfordern verschlüsselte Dateien spezielle Tools. Hier finden Sie alle Lösungen im Überblick.
Excel KI-Update: So nutzen Sie Python & Copilot
Vergessen Sie komplexe Formeln! Excel wird durch Python und den KI-Copilot zum intelligenten Analysten. Diese Anleitung zeigt konkrete Beispiele, wie Sie Heatmaps erstellen, Szenarien simulieren und Ihre Arbeit massiv beschleunigen. Machen Sie Ihre Tabellen fit für die Zukunft.
Der XVERWEIS: Der neue Standard für die Datensuche in Excel
Verabschieden Sie sich vom Spaltenzählen! Der XVERWEIS macht die Datensuche in Excel endlich intuitiv und sicher. Erfahren Sie in diesem Tutorial, wie die Funktion aufgebaut ist, wie Sie sie anwenden und warum sie dem SVERWEIS und WVERWEIS haushoch überlegen ist.
