Generische Formel
=MATCH(bignum,range)
Zusammenfassung
Um die letzte relative Position (dh letzte Zeile, letzte Spalte) für numerische Daten (mit oder ohne leere Zellen) zu erhalten, können Sie die MATCH-Funktion mit einer sogenannten "großen Zahl" verwenden.
In dem gezeigten Beispiel lautet die Formel in E5:
=MATCH(9.99E+307,B4:B9)
Letzte * relative * Position, keine Zeile im Arbeitsblatt
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:
Erläuterung
Diese Formel verwendet die MATCH-Funktion im ungefähren Übereinstimmungsmodus, um den letzten numerischen Wert 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 eine sogenannte "große Zahl" (manchmal abgekürzt "Bignum"), die absichtlich größer ist als jeder Wert, der im Bereich erscheint.
Das Ergebnis ist, dass MATCH zum letzten numerischen Wert im Bereich "zurücktritt" und diese Position zurückgibt.
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.
Über Bignum
Die größte Zahl, die Excel verarbeiten kann, ist 9.99999999999999E + 307.
Wenn Sie MATCH auf diese Weise verwenden, können Sie eine beliebige große Zahl verwenden, die garantiert größer als jeder Wert im Bereich ist, zum Beispiel:
=MATCH(1E+06,range) // 1 million =MATCH(1E+09,range) // 1 billion =MATCH(1E+12,range) // 1 trillion
Der Vorteil der Verwendung von 9.99E + 307 oder ähnlichem besteht darin, dass es (1) eine große Zahl ist und (2) als Platzhalter für eine "große Zahl" erkennbar ist. Sie werden sehen, dass es in verschiedenen erweiterten Excel-Formeln verwendet wird.
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.
