Excel, Formeln, VBA Programmierung, UDF, Tipps & Tricks

Datenbereich dynamisch gestalten

Hier nun 2 Möglichkeiten, den Daten/Quellbereich einer Pivottabelle dynamisch zu gestalten. Damit ist sichergestellt, dass hinzugefügte Daten auch in der Auswertung berücksichtigt werden. Das Wissen um das Erstellen einer Pivottabelle wird hier vorausgesetzt. Ansonsten empfehle ich zuvor den Beitrag: Pivottabelle -> Erster Einstieg - Nur 10 Mausklicks zu lesen.

Möglichkeit 1.)

Die Pivottabelle wird wie gewohnt durch setzen des Cursors in den Datenbereich erstellt. Excel erkennt selbstständig den aktuellen Bereich, erkennbar durch die umlaufende Markierung. Nach Fertigstellung der Pivottabelle setzen wir den Cursor erneut in den Datenbereich und wählen das Menü: Daten - Liste - Liste erstellen. Alternativ rechte Maustaste und aus dem Kontextmenü "Liste erstellen" wählen, oder mit der Tastenkombination Strg + L.

Werden jetzt neue Daten zu den bestehenden Daten hinzugefügt, verlängert sich automatisch die Liste und somit fließen diese Daten ebenfalls in die Berechnung der Pivottabelle ein.

Werden hingegen Daten entfernt, so nicht einfach diese markieren und Enfernen drücken. Nach Aktualisierung der Pivottabelle zeigt diese eine Zeile "Leer" an, was im Prinzip nicht weiter schlimm ist. Sollte die Pivottabelle jedoch nach Datum gruppiert sein ( Siehe Pivottabelle -> Gruppierung - Gliederung ) hebt Excel selbständig die Gruppierung auf. Mit einer Leerzeile im Quellbereich kann die Pivottabelle nicht gruppiert werden. Wird die Leerzeile entfernt, oder mit Daten gefüllt, so läßt sich die Pivottabelle trotzdem nicht wieder nach Datum gruppieren.

Deshalb die gewünschte Zeile markieren und aus dem Kontextmenü Zeile/Spalte löschen -> Zeile wählen.

Möglichkeit 2:)

Eine weitere Möglichkeit ist eine Formellösung in Verbindung mit einem definierten Namen. Sollte das Vergeben eines Namens für einen Datenbereich nicht bekannt sein, dann siehe bitte Tipps & Tricks -> Namen definieren.

Wir markieren den Datenbereich, welcher später als Datenquelle für unsere Pivottabelle dienen soll. Hier z.B den Bereich A1 bis C5 auf dem Tabellenblatt "Tabelle2". Excel zeigt den gewählten Bereich unten im Fenster bei "Bezieht sich auf:" an und schlägt hier den Namen "Datum" vor.

Wir ändern den Namen in einen aussagekräftigeren z.B. "Datenquelle". Um den Bereich nun dynamisch zu gestalten ersetzen wir bei "Bezieht sich auf" den festen Bereich durch eine Formel, welche automatisch den benutzten Bereich erkennt.

In diesem Fall lautet sie: =BEREICH.VERSCHIEBEN(Tabelle2!$A$1;;;ANZAHL2(Tabelle2!$A:$A);ANZAHL2(Tabelle2!$1:$1))

Es wird also geprüft, wie viele Zeilen und Spalten der Bereich umfasst. Der Bereich beginnt hier auf Tabelle2 in Zelle A1. Das erste ANZAHL2() prüft die Anzahl Zeilen in Spalte A, das zweite ANZAHL2() prüft die Anzahl der Spalten in Zeile 1.

Steht vorher fest, dass der Datenbereich beispielsweise nur Spalte A bis C umfasst, würde auch diese Formel reichen:

=BEREICH.VERSCHIEBEN(Tabelle2!$A$1;;;ANZAHL2(Tabelle2!$A:$A);3)

Wichtig ist hier das Setzen der Dollarzeichen, um den Bezug absolut zu halten sowie den Bezug zum Tabellenblatt.

Aufgrund der Formellänge wird diese leider nicht komplett angezeigt.

Anmerkung: Persönlich erstelle ich die Formel in einer Zelle und kopiere sie dann ins Fenster.

Beim Erstellen der Pivottabelle geben wir nun bei Bereich: Datenquelle ein und erstellen die Pivottabelle wie gewohnt. Werden nun Datensätze zu den bestehenden hinzugefügt oder entfernt, so stellt der durch die Formel dynamisch gehaltene definierte Bereich sicher, das die Pivottabelle immer mit den aktuellen Rohdaten arbeitet.

Anmerkung: Natürlich kann der Datenbereich auch von vorneherein auf Tabelle2!A:C, also die kompletten Spalten umfassend, festgelegt werden. Aber nur solange nicht nach Datum gruppiert werden soll.