Excel-Formel: Dynamischer benannter Bereich mit INDEX -

Inhaltsverzeichnis

Generische Formel

=$A$1:INDEX($A:$A,lastrow)

Zusammenfassung

Eine Möglichkeit, einen dynamischen benannten Bereich in Excel zu erstellen, ist die Verwendung der INDEX-Funktion. In dem gezeigten Beispiel wird der benannte Bereich "Daten" durch die folgende Formel definiert:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

Dies wird in den Bereich $ A $ 2: $ A $ 10 aufgelöst.

Hinweis: Diese Formel soll einen benannten Bereich definieren, der in anderen Formeln verwendet werden kann.

Erläuterung

Diese Seite zeigt ein Beispiel für einen dynamischen benannten Bereich, der mit der INDEX-Funktion zusammen mit der COUNTA-Funktion erstellt wurde. Dynamische benannte Bereiche werden automatisch erweitert und verkleinert, wenn Daten hinzugefügt oder entfernt werden. Sie sind eine Alternative zur Verwendung einer Excel-Tabelle, deren Größe auch beim Hinzufügen oder Entfernen von Daten geändert wird.

Die INDEX-Funktion gibt den Wert an einer bestimmten Position in einem Bereich oder Array zurück. Mit INDEX können Sie einzelne Werte oder ganze Zeilen und Spalten in einem Bereich abrufen. Was INDEX für dynamische benannte Bereiche besonders nützlich macht, ist, dass es tatsächlich eine Referenz zurückgibt. Dies bedeutet, dass Sie INDEX verwenden können, um eine gemischte Referenz wie $ A $ 1: A100 zu erstellen.

In dem gezeigten Beispiel wird der benannte Bereich "Daten" durch die folgende Formel definiert:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

Dies wird in den Bereich $ A $ 2: $ A $ 10 aufgelöst.

Wie diese Formeln funktionieren

Beachten Sie zunächst, dass diese Formel aus zwei Teilen besteht, die sich auf beiden Seiten des Bereichsoperators befinden (:). Links haben wir die Startreferenz für den Bereich, fest codiert als:

$A$2

Auf der rechten Seite befindet sich die Endreferenz für den Bereich, die mit INDEX wie folgt erstellt wurde:

INDEX($A:$A,COUNTA($A:$A))

Hier geben wir INDEX die gesamte Spalte A für das Array ein und verwenden dann die COUNTA-Funktion, um die "letzte Zeile" im Bereich zu ermitteln. COUNTA funktioniert hier gut, da es in Spalte A 10 Werte gibt, einschließlich einer Kopfzeile. COUNTA gibt daher 10 zurück, die als Zeilennummer direkt in INDEX eingeht. INDEX gibt dann einen Verweis auf $ A $ 10 zurück, die zuletzt verwendete Zeile im Bereich:

INDEX($A:$A,10) // resolves to $A$10

Das Endergebnis der Formel ist also dieser Bereich:

$A$2:$A$10

Ein zweidimensionaler Bereich

Das obige Beispiel funktioniert für einen eindimensionalen Bereich. Um einen zweidimensionalen Dynamikbereich zu erstellen, in dem auch die Anzahl der Spalten dynamisch ist, können Sie denselben Ansatz verwenden, der folgendermaßen erweitert wird:

=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

Nach wie vor wird COUNTA verwendet, um die "letzte Spalte" herauszufinden, und wir verwenden COUNTA erneut, um die "letzte Spalte" zu erhalten. Diese werden als row_num bzw. column_num für den Index bereitgestellt.

Für das Array stellen wir jedoch das vollständige Arbeitsblatt bereit, das als alle 1048576 Zeilen eingegeben wurde, sodass INDEX eine Referenz in einem 2D-Raum zurückgeben kann.

Hinweis: Excel 2003 unterstützt nur 65535 Zeilen.

Bestimmen der letzten Zeile

Abhängig von der Struktur und dem Inhalt der Daten im Arbeitsblatt gibt es verschiedene Möglichkeiten, die letzte Zeile (letzte relative Position) in einem Datensatz zu bestimmen: