Tutorial: Dynamische Bereiche für Pivot-Tabellen mit BEREICH.VERSCHIEBEN erstellen

Hier ist ein ausführliches Tutorial, wie du einen dynamischen Bereich für eine Pivot-Tabelle mit der Funktion BEREICH.VERSCHIEBEN (englisch: OFFSET) in Excel erstellst.

Warum ein dynamischer Bereich?

Normalerweise musst du den Datenbereich einer Pivot-Tabelle manuell anpassen, wenn neue Zeilen oder Spalten hinzugefügt werden. Ein dynamischer Bereich erkennt automatisch, wenn neue Daten dazukommen, sodass du die Pivot-Tabelle nur noch aktualisieren musst, ohne die Datenquelle neu zu definieren.

Schritt 1: Die Logik der Funktion verstehen

Die Funktion BEREICH.VERSCHIEBEN gibt einen Bezug auf einen Bereich zurück. In Kombination mit ANZAHL2 (englisch: COUNTA) wird dieser Bereich flexibel.

Die Syntax lautet: =BEREICH.VERSCHIEBEN(Startzelle; Zeilen_Offset; Spalten_Offset; Höhe; Breite)

• Startzelle: Die obere linke Ecke deiner Daten (meist die Überschrift, z.B. $A$1).
• Zeilen/Spalten_Offset: Wie viele Zellen man von der Startzelle wegspringt (hier meist 0).
• Höhe: Wie viele Zeilen der Bereich hat (ermittelt durch ANZAHL2).
• Breite: Wie viele Spalten der Bereich hat (ermittelt durch ANZAHL2).

Schritt 2: Den dynamischen Namen definieren

Damit die Pivot-Tabelle die Formel versteht, müssen wir sie im Namens-Manager hinterlegen.

1. Klicke im Menüband auf den Reiter Formeln.
2. Wähle Namens-Manager und klicke auf Neu.
3. Gib im Feld Name einen Namen ohne Leerzeichen ein, z.B. MeinDynamischerBereich.
4. Gib im Feld Bezieht sich auf die folgende Formel ein (angepasst an deine Tabelle):

=BEREICH.VERSCHIEBEN(Tabelle1!$A$1;0;0;ANZAHL2(Tabelle1!$A:$A);ANZAHL2(Tabelle1!$1:$1))

Erklärung der Formel:

• Tabelle1!$A$1: Wir starten bei Zelle A1.
• 0;0: Wir verschieben den Startpunkt nicht.
• ANZAHL2(Tabelle1!$A:$A): Excel zählt alle ausgefüllten Zellen in Spalte A. Das bestimmt die Höhe (Zeilenanzahl).
• ANZAHL2(Tabelle1!$1:$1): Excel zählt alle ausgefüllten Zellen in Zeile 1. Das bestimmt die Breite (Spaltenanzahl).

5. Bestätige mit OK und schließe den Namens-Manager.

Schritt 3: Die Pivot-Tabelle erstellen

Nun verknüpfen wir die Pivot-Tabelle mit diesem Namen statt mit festen Zellbezügen.

1. Gehe auf den Reiter Einfügen und klicke auf PivotTable.
2. Im Feld Tabelle/Bereich tippst du nun einfach den Namen ein, den du vorhin vergeben hast: MeinDynamischerBereich.
3. Wähle aus, ob die Pivot-Tabelle in einem neuen oder bestehenden Arbeitsblatt erstellt werden soll, und klicke auf OK.

Schritt 4: Den dynamischen Bereich testen

1. Füge unter deinen vorhandenen Daten eine neue Zeile hinzu.
2. Klicke mit der rechten Maustaste in deine Pivot-Tabelle und wähle Aktualisieren.
3. Du wirst sehen, dass die neuen Daten sofort berücksichtigt werden, ohne dass du den Datenbereich ("Datenquelle ändern") anpassen musst.

Wichtige Tipps & Fehlervermeidung

• Keine Leerzellen: Die Funktion ANZAHL2 zählt alle Zellen mit Inhalt. Wenn du leere Zeilen innerhalb deines Datenbestands hast, wird die berechnete Höhe zu kurz sein. Achte auf einen lückenlosen Datenblock.
• Absolute Bezüge: Achte darauf, dass in der Formel im Namens-Manager immer Dollarzeichen ($) stehen (z.B. $A$1), damit der Bezug fixiert bleibt.
• Alternative "Tabelle": Seit neueren Excel-Versionen kannst du Daten auch mit Strg + T als offizielle Tabelle formatieren. Diese sind automatisch dynamisch. Die Methode mit BEREICH.VERSCHIEBEN ist jedoch die "Profi-Lösung", wenn du mehr Kontrolle brauchst oder mit älteren Excel-Dateien arbeitest.

Zusammenfassung der deutschen vs. englischen Funktionen: