Generische Formel
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Zusammenfassung
Um die Position der letzten Übereinstimmung (dh des letzten Auftretens) eines Suchwerts zu ermitteln, können Sie eine Array-Formel verwenden, die auf den Funktionen IF, ROW, INDEX, MATCH und MAX basiert. In dem gezeigten Beispiel lautet die Formel in H6:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Wobei "Namen" der benannte Bereich C4: C11 ist.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
Der Kern dieser Formel besteht darin, dass wir eine Liste von Zeilennummern für einen bestimmten Bereich erstellen, die mit einem Wert übereinstimmen, und dann die MAX-Funktion verwenden, um die größte Zeilennummer zu erhalten, die dem letzten übereinstimmenden Wert entspricht. Wir verwenden den benannten Bereich "Namen" nur zur Vereinfachung.
Dieser Teil der Formel arbeitet von innen nach außen und generiert einen relativen Satz von Zeilennummern:
ROW(names)-MIN(ROW(names))+1
Das Ergebnis des obigen Ausdrucks ist ein Array von Zahlen wie folgt:
(1;2;3;4;5;6;7;8)
Beachten Sie, dass wir 8 Zahlen erhalten, die den 8 Zeilen in der Tabelle entsprechen. Auf dieser Seite finden Sie Details zur Funktionsweise dieses Teils der Formel.
Für die Zwecke dieser Formel möchten wir nur Zeilennummern für übereinstimmende Werte, daher verwenden wir die IF-Funktion, um die Werte wie folgt zu filtern:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Dies führt zu einem Array, das folgendermaßen aussieht:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Beachten Sie, dass dieses Array noch acht Elemente enthält. Es sind jedoch nur Zeilennummern erhalten, bei denen der Wert im benannten Bereich "Namen" gleich "Amy" ist (dh 1, 4, 7). Alle anderen Elemente im Array sind FALSE, da sie den logischen Test in der IF-Funktion nicht bestanden haben.
Schließlich liefert die IF-Funktion dieses Array an die MAX-Funktion. MAX gibt den höchsten Wert im Array zurück, die Nummer 7, die der letzten Zeilennummer entspricht, in der der Name "amy" lautet. Sobald wir die letzte übereinstimmende Zeilennummer kennen, können wir INDEX verwenden, um einen Wert an dieser Position abzurufen.
Vorletzter usw.
Um die vorletzte Position, die drittletzte Position usw. zu erreichen, können Sie wie folgt von der MIN-Funktion zur LARGE-Funktion wechseln:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
wobei k "n-te größte" darstellt. Um beispielsweise die vorletzte Übereinstimmung im obigen Beispiel zu erhalten, können Sie Folgendes verwenden:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Dies ist nach wie vor eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
