Generische Formel
(=MATCH(2,1/(range"")))
Zusammenfassung
Um die letzte relative Position (dh letzte Zeile, letzte Spalte) für gemischte Daten zu erhalten, die möglicherweise leere Zellen enthalten, können Sie die MATCH-Funktion wie unten beschrieben verwenden.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabetaste eingegeben werden.
In dem gezeigten Beispiel lautet die Formel in E5:
(=MATCH(2,1/(B4:B10"")))
Letzte * relative * Position, keine Zeile im Arbeitsblatt
Bei der Erstellung erweiterter Formeln ist es häufig erforderlich, den letzten Speicherort der Daten in einer Liste zu ermitteln. Abhängig von den Daten kann dies die letzte Zeile mit Daten, die letzte Spalte mit Daten oder der Schnittpunkt beider sein. Wir wollen die letzte * relative Position * innerhalb eines bestimmten Bereichs, nicht die Zeilennummer im Arbeitsblatt:
Erläuterung
Diese Formel verwendet die konfigurierte MATCH-Funktion, um die Position der letzten nicht leeren Zelle in einem Bereich zu ermitteln.
Das Lookup-Array in MATCH funktioniert von innen nach außen wie folgt:
=1/(B4:B10"")) =1/(TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE) =(1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!)
Hinweis: Alle Werte im Array sind entweder 1 oder # DIV / 0! Error.
MATCH wird dann so eingestellt, dass es im "ungefähren Übereinstimmungsmodus" mit dem Wert 2 übereinstimmt, indem das dritte Argument weggelassen wird.
Da der Suchwert 2 niemals gefunden wird, findet MATCH immer die letzte 1 im Sucharray, die der letzten nicht leeren Zelle entspricht.
Dieser Ansatz funktioniert mit allen Arten von Daten, einschließlich Zahlen, Text, Datumsangaben usw. Er funktioniert auch mit Null-Textzeichenfolgen, die von folgenden Formeln zurückgegeben werden:
=IF(A1<100,"")
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" hervorragend erklärt.
