Ihre Excel-Daten ändern sich häufig, daher ist es hilfreich, einen dynamisch definierten Bereich zu erstellen, der sich automatisch auf die Größe Ihres Datenbereichs erweitert und zusammenzieht. Mal sehen wie.
Wenn Sie einen dynamisch definierten Bereich verwenden, müssen Sie die Bereiche Ihrer Formeln, Diagramme und PivotTables nicht manuell bearbeiten, wenn sich Daten ändern. Dies geschieht automatisch.
Zwei Formeln werden verwendet, um dynamische Bereiche zu erstellen: OFFSET und INDEX. Dieser Artikel konzentriert sich auf die Verwendung der INDEX-Funktion, da dies ein effizienterer Ansatz ist. OFFSET ist eine flüchtige Funktion und kann große Tabellenkalkulationen verlangsamen.
Inhaltsverzeichnis
Erstellen Sie einen dynamisch definierten Bereich in Excel
Für unser erstes Beispiel haben wir die unten gezeigte einspaltige Datenliste.
Dies muss dynamisch sein, damit der Bereich automatisch aktualisiert wird, wenn weitere Länder hinzugefügt oder entfernt werden.
In diesem Beispiel möchten wir die Header-Zelle vermeiden. Als solches wollen wir den Bereich $A$2:$A$6, aber dynamisch. Klicken Sie dazu auf Formeln > Namen definieren.
Geben Sie „Länder“ in das Feld „Name“ ein und geben Sie dann die folgende Formel in das Feld „Bezieht sich auf“ ein.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Das Eingeben dieser Gleichung in eine Tabellenkalkulationszelle und das anschließende Kopieren in das Feld Neuer Name ist manchmal schneller und einfacher.
Wie funktioniert das?
Der erste Teil der Formel gibt die Startzelle des Bereichs (in unserem Fall A2) an und dann folgt der Bereichsoperator (:).
=$A$2:
Die Verwendung des Bereichsoperators zwingt die INDEX-Funktion, einen Bereich anstelle des Werts einer Zelle zurückzugeben. Die INDEX-Funktion wird dann mit der COUNTA-Funktion verwendet. ANZAHLA zählt die Anzahl der nicht leeren Zellen in Spalte A (in unserem Fall sechs).
INDEX($A:$A,COUNTA($A:$A))
Diese Formel fordert die INDEX-Funktion auf, den Bereich der letzten nicht leeren Zelle in Spalte A ($A$6) zurückzugeben.
Das Endergebnis ist $A$2:$A$6 und ist aufgrund der COUNTA-Funktion dynamisch, da es die letzte Zeile findet. Sie können diesen „Länder“-definierten Namen jetzt in einer Datenvalidierungsregel, Formel, einem Diagramm oder überall dort verwenden, wo wir auf die Namen aller Länder verweisen müssen.
Erstellen Sie einen Zwei-Wege-Dynamik-definierten Bereich
Das erste Beispiel war nur in der Höhe dynamisch. Mit einer geringfügigen Änderung und einer weiteren COUNTA-Funktion können Sie jedoch einen Bereich erstellen, der sowohl in der Höhe als auch in der Breite dynamisch ist.
In diesem Beispiel verwenden wir die unten gezeigten Daten.
Diesmal erstellen wir einen dynamisch definierten Bereich, der die Header enthält. Klicken Sie auf Formeln > Namen definieren.
Geben Sie „Umsatz“ in das Feld „Name“ ein und geben Sie 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 verwendet dann einen Bereich des gesamten Arbeitsblatts ($1:$1048576), um hineinzuschauen und aus ihm zurückzukehren.
Eine der COUNTA-Funktionen wird verwendet, um die nicht-leeren Zeilen zu zählen, und eine andere wird für die nicht-leeren Spalten verwendet, wodurch sie in beide Richtungen dynamisch wird. Obwohl diese Formel mit A1 begann, hätten Sie eine beliebige Startzelle angeben können.
Sie können diesen definierten Namen (Umsatz) jetzt in einer Formel oder als Diagrammdatenreihe verwenden, um sie dynamisch zu machen.