Generische Formel
=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))
Zusammenfassung
Eine Möglichkeit, einen dynamischen benannten Bereich mit einer Formel zu erstellen, besteht darin, die OFFSET-Funktion zusammen mit der COUNTA-Funktion zu verwenden. Dynamische Bereiche werden auch als Erweiterungsbereiche bezeichnet. Sie werden automatisch erweitert und verkleinert, um neue oder gelöschte Daten aufzunehmen.
Hinweis: OFFSET ist eine flüchtige Funktion, dh sie wird bei jeder Änderung an einem Arbeitsblatt neu berechnet. Bei einem modernen Computer und einem kleineren Datensatz sollte dies kein Problem verursachen, bei großen Datenmengen kann jedoch eine langsamere Leistung auftreten. In diesem Fall sollten Sie stattdessen einen dynamischen benannten Bereich mit der INDEX-Funktion erstellen.
Im gezeigten Beispiel lautet die für den Dynamikbereich verwendete Formel:
=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))
Erläuterung
Diese Formel verwendet die OFFSET-Funktion, um einen Bereich zu generieren, der sich durch Anpassen von Höhe und Breite basierend auf der Anzahl nicht leerer Zellen erweitert und verkleinert.
Das erste Argument in OFFSET repräsentiert die erste Zelle in den Daten (den Ursprung), in diesem Fall die Zelle B5. Die nächsten beiden Argumente sind Offsets für Zeilen und Spalten und werden als Null angegeben.
Die letzten beiden Argumente stehen für Höhe und Breite. Höhe und Breite werden im laufenden Betrieb mithilfe von COUNTA generiert, wodurch die resultierende Referenz dynamisch wird.
Für die Höhe verwenden wir die COUNTA-Funktion, um nicht leere Werte im Bereich B5: B100 zu zählen. Dies setzt keine leeren Werte in den Daten und keine Werte über B100 hinaus voraus. COUNTA gibt 6 zurück.
Für die Breite verwenden wir die COUNTA-Funktion, um nicht leere Werte im Bereich B5: Z5 zu zählen. Dies setzt keine Header-Zellen und keine Header über Z5 hinaus voraus. COUNTA gibt 6 zurück.
Zu diesem Zeitpunkt sieht die Formel folgendermaßen aus:
=OFFSET(B5,0,0,6,6)
Mit diesen Informationen gibt OFFSET einen Verweis auf B5: G10 zurück, der einem Bereich von 6 Zeilen Höhe und 6 Spalten Durchmesser entspricht.
Hinweis: Die für Höhe und Breite verwendeten Bereiche sollten an das Arbeitsblattlayout angepasst werden.
Variation mit vollständigen Spalten- / Zeilenreferenzen
Sie können auch vollständige Spalten- und Zeilenreferenzen für Höhe und Breite wie folgt verwenden:
=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))
Beachten Sie, dass die Höhe mit -2 angepasst wird, um die Kopf- und Titelwerte in den Zellen B4 und B2 zu berücksichtigen. Der Vorteil dieses Ansatzes ist die Einfachheit der Bereiche innerhalb von COUNTA. Der Nachteil liegt in der großen Größe der vollen Spalten und Zeilen. Es muss darauf geachtet werden, dass fehlerhafte Werte außerhalb des Bereichs liegen, da sie die Anzahl leicht beeinträchtigen können.
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:
- Letzte Zeile in gemischten Daten mit Leerzeichen
- Letzte Zeile in gemischten Daten ohne Leerzeichen
- Letzte Zeile in Textdaten
- Letzte Zeile in numerischen Daten
