Ihre Excel-Daten sind oft Veränderungen unterworfen. Daher ist es sehr nützlich, einen dynamisch definierten Bereich zu schaffen, der sich bei Änderungen der Daten automatisch anpasst. Sehen wir uns an, wie das funktioniert.
Mit einem dynamisch definierten Bereich müssen Sie bei sich ändernden Daten die Bereiche in Ihren Formeln, Diagrammen und PivotTables nicht mehr manuell ändern. Das passiert vollkommen automatisch.
Zur Erstellung dynamischer Bereiche gibt es zwei Formeln: OFFSET und INDEX. Dieser Artikel konzentriert sich auf die INDEX-Funktion, da sie effizienter ist. OFFSET ist eine volatile Funktion und kann umfangreiche Tabellenkalkulationen verlangsamen.
Einen dynamisch definierten Bereich in Excel erstellen
In unserem ersten Beispiel haben wir eine einspaltige Datenliste, wie unten dargestellt.
Dieser Bereich soll dynamisch sein, damit er sich beim Hinzufügen oder Entfernen von Ländern automatisch aktualisiert.
In diesem Beispiel möchten wir die Kopfzeile auslassen. Das heißt, wir wollen den Bereich $A$2:$A$6, aber dynamisch. Klicken Sie dazu auf „Formeln“ > „Namen definieren“.
Geben Sie im Feld „Name“ „Länder“ ein und geben Sie dann die folgende Formel in das Feld „Bezieht sich auf“ ein:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Manchmal geht es schneller und einfacher, diese Formel zuerst in eine Tabellenzelle einzugeben und sie dann in das Feld „Neuer Name“ zu kopieren.
Wie funktioniert das genau?
Der erste Teil der Formel gibt die Startzelle des Bereichs an (in unserem Fall A2) und wird gefolgt vom Bereichsoperator (:).
=$A$2:
Durch die Verwendung des Bereichsoperators zwingen wir die INDEX-Funktion, einen Bereich anstelle eines Zellwertes zurückzugeben. Die INDEX-Funktion wird in Kombination mit der COUNTA-Funktion verwendet. COUNTA zählt die Anzahl der nicht leeren Zellen in Spalte A (in diesem Fall sind es sechs).
INDEX($A:$A,COUNTA($A:$A))
Diese Formel weist die INDEX-Funktion an, den Bereich bis zur letzten nicht leeren Zelle in Spalte A ($A$6) zurückzugeben.
Das Endergebnis ist $A$2:$A$6 und ist durch die COUNTA-Funktion dynamisch, weil sie die letzte Zeile ermittelt. Sie können diesen definierten Namen „Länder“ jetzt in einer Datenvalidierungsregel, Formel, einem Diagramm oder überall dort verwenden, wo Sie auf die Namen aller Länder Bezug nehmen müssen.
Einen zweiseitig dynamisch definierten Bereich erstellen
Im ersten Beispiel war die Dynamik nur in der Höhe gegeben. Mit einer kleinen Änderung und einer weiteren COUNTA-Funktion lässt sich jedoch ein Bereich erstellen, der sowohl in der Höhe als auch in der Breite dynamisch ist.
In diesem Beispiel verwenden wir die untenstehenden Daten.
Diesmal erstellen wir einen dynamisch definierten Bereich, der auch die Kopfzeilen einschließt. Klicken Sie auf „Formeln“ > „Namen definieren“.
Geben Sie im Feld „Name“ „Umsatz“ ein und geben Sie dann die folgende Formel in das Feld „Bezieht sich auf“ ein:
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Diese Formel verwendet $A$1 als Startzelle. Die INDEX-Funktion durchsucht dann den gesamten Arbeitsblattbereich ($1:$1048576), um daraus einen Bereich zurückzugeben.
Eine der COUNTA-Funktionen zählt die nicht leeren Zeilen, die andere die nicht leeren Spalten, wodurch der Bereich in beiden Dimensionen dynamisch wird. Obwohl diese Formel mit A1 beginnt, können Sie jede beliebige Startzelle verwenden.
Sie können diesen definierten Namen „Umsatz“ jetzt in einer Formel oder als Datenreihe für ein Diagramm verwenden, um diese dynamisch zu gestalten.