Generische Formel
=MATCH(bigtext,range)
Zusammenfassung
Um die letzte relative Position (dh letzte Zeile, letzte Spalte) für Textdaten (mit oder ohne leere Zellen) zu erhalten, können Sie die MATCH-Funktion verwenden. In dem gezeigten Beispiel lautet die Formel in D5:
=MATCH(REPT("z",255),B4:B11)
Erläuterung
Diese Formel verwendet die MATCH-Funktion im ungefähren Übereinstimmungsmodus, um den letzten Textwert in einem Bereich zu lokalisieren. Die ungefähre Übereinstimmung wird aktiviert, indem das dritte Argument in MATCH auf 1 gesetzt oder dieses Argument weggelassen wird, das standardmäßig 1 ist.
Der Suchwert ist ein sogenannter "großer Text" (manchmal abgekürzt "großer Text"), der absichtlich ein "größerer" Wert ist als jeder Wert, der im Bereich angezeigt wird. Wenn Sie mit Text arbeiten, der alphabetisch sortiert ist, bedeutet dies einen Textwert, der immer am Ende der alphabetischen Sortierreihenfolge angezeigt wird.
Da diese Formel mit Text übereinstimmt, besteht die Idee darin, einen Suchwert zu erstellen, der im tatsächlichen Text niemals vorkommt, aber immer der letzte ist. Dazu verwenden wir die REPT-Funktion, um den Buchstaben "z" 255 Mal zu wiederholen. Die Zahl 255 repräsentiert die größte Anzahl von Zeichen, die MATCH in einem Suchwert zulässt.
Wenn MATCH diesen Wert nicht finden kann, wird zum letzten Textwert im Bereich "zurückgesetzt" und die Position dieses Werts zurückgegeben.
Hinweis: Dieser Ansatz funktioniert gut mit leeren Zellen im Bereich, ist jedoch bei gemischten Daten, die sowohl Zahlen als auch Text enthalten, nicht zuverlässig.
Letzte relative Position gegen letzte Zeilennummer
Wenn Sie erweiterte Formeln erstellen, die Dynamikbereiche erstellen, müssen Sie häufig den letzten Speicherort der Daten in einer Liste ermitteln. Abhängig von den Daten kann dies die letzte Zeile mit Daten, die letzte Spalte mit Daten oder der Schnittpunkt beider sein. Hinweis: Wir möchten die letzte relative Position innerhalb eines bestimmten Bereichs, nicht die Zeilennummer im Arbeitsblatt:
Dynamikbereich
Mit dieser Formel können Sie einen Dynamikbereich mit anderen Funktionen wie INDEX und OFFSET erstellen. Beispiele und Erklärungen finden Sie unter den folgenden Links:
- Dynamikbereich mit INDEX und COUNTA
- Dynamikbereich mit OFFSET und COUNTA
Die Inspiration für diesen Artikel kam von Mike Girvins ausgezeichnetem Buch Control + Shift + Enter, in dem Mike das Konzept der "letzten relativen Position" erklärt.
