Google Sheets-Kochbuch: Dynamische benannte Bereiche

Was ist ein benannter Bereich?

Ein benannter Bereich ist ein Datenbereich, den du anstatt einem Zellbezug verwenden kannst. Anstatt also in einer Tabelle immer einen bestimmten Zellbereich anzugeben (z.B. A3:C13), kannst du diesen Zellbereich benennen (z.B. "BIP") und dann diesen Namen verwenden. Dabei sind die benannten Bereiche absolute Zellbezüge, die innerhalb einer Tabellendatei gültig sind. Daher ist der Name des Tabellenblattes Teil des benannten Bereiches.

Auf einem Tabellenblatt mit "Länderdaten" stehen im Bereich A2:C13 Länder und ihre Bruttoinlandsprodukte. Auf einer anderen Seite sollen die Bruttoinlandsprodukte ausgeben werden.

• =FILTER('Länderdaten'!A2:C13;INDEX('Länderdaten'!A2:C13;0;1)="DEU")
  • Hier wird der Bereich jedesmal vollständig angegeben.

Es wird nun ein benannter Bereich mit dem Namen "BIP" definiert (Daten->Benannte Bereiche).

• =FILTER(BIP;INDEX(BIP;0;1)="DEU")
  • Statt also den Bereich direkt anzugeben, gibt man nun den benannten Bereich an.

Definition eines benannten Bereiches

Das ist natürlich im wesentlichen sinnvoll, wenn man diesen Bereich mehrfach benötigt. Außerdem hift es in komplizierteren Auswertungen dabei zu verstehen, welche Daten man abfragt.

Solange sich die Größe des benannten Bereiches nicht ändert, funktioniert das auch prima. Aber was ist, wenn weitere Daten zum benannten Bereich hinzukommen, in unserem Beispiel also mehr Länder? Ein anderes Beispiel wäre ein Tabelle die durch eine Umfrage gefüllt wird, und mit jeder abgegebenen Umfrage wird eine weitere Ergebniszeile eingefügt.

Idealerweise wird dann der benannte Bereich automatisch vergrößert, so dass wir in allen Formeln, in denen wir den benannten Bereich verwenden, uns immer auf den gesamten Bereich beziehen. Dies nennt man einen dynamischen benannten Bereich.

Dynamische benannte Bereiche

Um einen dynamischen benannten Bereich zu erstellen und zu verwenden, braucht es drei Schritte:

1. Man erstellt einen String (eine Zeichenkette), die den benannten Bereich beschreibt. Dabei sollte dieser String automatisch erstellt werden, z.B. über das Zählen der Einträge in der Liste.
   • In unserem Beispiel zählen wir die Einträge. Da wir in Zeile 2 mit der Liste anfangen, müssen wir eine weitere Zeile hinzuaddieren.
   • ="'Länderdaten'!A2:C"&COUNTA(A2:A)+1
   • Dabei muss man in unserem Fall aufpassen, dass keine leeren Einträge vorkommen.
2. Die Zelle, in der der String erstellt wird, wird als benannter Bereich definiert.
   • Hier habe ich die Zelle C1 verwendet.
3. In den Formeln in denen man den benannten Bereich verwendet, wird über die INDIRECT-Funktion auf den String zugegriffen und dieser als Beschreibung des benannten Bereiches verwendet.
   • =FILTER(INDIRECT(BIP);INDEX(INDIRECT(BIP);0;1)="DEU")

Definition eines dynamischen benannten Bereiches

Werden jetzt weitere Einträge in der Liste mit den Länderdaten hinzugefügt, erweitert sich der benannte Bereich automatisch. Die Methode hat zwei Nachteile:

1. Man muss wissen, welche Bereiche statisch und welche dynamisch definiert sind.
2. Ändert man den Namen des Tabellenblattes, muss man die Änderung händisch nachführen. Das macht Google Sheets sonst automatisch.

Die Beispiele auf dieser Seite wurden getestet: 07/2022