Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial erfahren Sie, wie Sie den letzten Wert in einer Spalte oder Zeile in Excel nachschlagen.
Letzter Wert in Spalte
Sie können die LOOKUP-Funktion verwenden, um die letzte nicht leere Zelle in einer Spalte zu finden.
| 1 | =VERWEIS(2,1/(B:B""),B:B) |
Gehen wir diese Formel durch.
Der Teil der Formel B:B”” gibt ein Array zurück, das True- und False-Werte enthält: {FALSE, TRUE, TRUE,…}, testet jede Zelle in Spalte B ist leer (FALSE).
| 1 | =VERWEIS(2,1/({FALSCH;WAHR;WAHR;WAHR;WAHR;WAHR;FALSCH;…),B:B) |
Diese booleschen Werte werden in 0 oder 1 umgewandelt und werden verwendet, um 1 zu dividieren.
| 1 | =VERWEIS(2,{#DIV/0!;1;1;1;1;1;#DIV/0!;,B:B) |
Dies ist der lookup_vector für die LOOKUP-Funktion. In unserem Fall ist lookup_value 2, aber der größte Wert im lookup_vector ist 1, sodass die LOOKUP-Funktion mit der letzten 1 im Array übereinstimmt und den entsprechenden Wert im result_vector zurückgibt.
Wenn Sie sicher sind, dass Ihre Spalte nur numerische Werte enthält, Ihre Daten ab Zeile 1 beginnen und Ihr Datenbereich kontinuierlich ist, können Sie mit den Funktionen INDEX und ANZAHL eine etwas einfachere Formel verwenden.
| 1 | =INDEX(B:B,ANZAHL(B:B)) |
Die COUNT-Funktion gibt die Anzahl der mit Daten gefüllten Zellen im fortlaufenden Bereich (4) zurück und die INDEX-Funktion liefert somit den Wert der Zelle in dieser entsprechenden Zeile (4.).
Um mögliche Fehler zu vermeiden, wenn Ihr Datenbereich eine Mischung aus numerischen und nicht numerischen Werten oder sogar einige leere Zellen enthält, können Sie die LOOKUP-Funktion zusammen mit den Funktionen ISBLANK und NOT verwenden.
| 1 | =VERWEIS(2,1/(NICHT(ISBLANK(B:B))),B:B) |
Die ISBLANK-Funktion gibt ein Array zurück, das True- und False-Werte enthält, die den Werten 1 und 0 entsprechen. Die NOT-Funktion ändert True (d. h. 1) in False und False (d. h. 0) in True. Wenn wir dieses resultierende Array invertieren (wenn 1 durch dieses Array geteilt wird), erhalten wir ein Ergebnisarray, das wieder #DIV/0 enthält! Fehler und Einsen, die als Lookup-Array (lookup_vector) in unserer LOOKUP-Funktion verwendet werden können. Die Funktionalität der LOOKUP Function ist dann dieselbe wie in unserem ersten Beispiel: Sie gibt den Wert des Ergebnisvektors an der Position der letzten 1 im Lookup-Array zurück.
Wenn Sie die Zeilennummer mit dem letzten Eintrag zurückgeben möchten, können Sie die in unserem ersten Beispiel verwendete Formel zusammen mit der ROW-Funktion in Ihrem result_vector ändern.
| 1 | =VERWEIS(2,1/(B:B""),ZEILE(B:B)) |
Letzter Wert in Zeile
Um den Wert der letzten nicht leeren Zelle in einer mit numerischen Daten gefüllten Zeile zu erhalten, möchten Sie möglicherweise einen ähnlichen Ansatz verwenden, jedoch mit anderen Funktionen: die OFFSET-Funktion zusammen mit den Funktionen MATCH und MAX.
| 1 | =OFFSET(Referenz, Zeilen, Spalten) |
| 1 | =OFFSET(B2,0,MATCH(MAX(B2:XFD2)+1,B2:XFD2,1)-1) |
Sehen wir uns an, wie diese Formel funktioniert.
MATCH-Funktion
Wir verwenden die MATCH-Funktion, um zu „zählen“, wie viele Zellenwerte unter 1 + dem Maximum aller Werte in Zeile 2 ab B2 liegen.
| 1 | =MATCH(lookup_value, lookup_array, [match_type]) |
| 1 | =VERGLEICH(MAX(B2:XFD2)+1,B2:XFD2,1) |
Der lookup_value der MATCH-Funktion ist das Maximum aller Werte in der Zeile2 + 1. Da dieser Wert offensichtlich nicht in der Zeile2 vorhanden ist und der match_type auf 1 (kleiner oder gleich lookup_value) gesetzt ist, gibt die MATCH-Funktion den Position der zuletzt „geprüften“ Zelle im Array, d. h. die Anzahl der mit Daten gefüllten Zellen im Bereich B2:XFD2 (XFD ist die allerletzte Spalte in den neueren Excel-Versionen).
OFFSET-Funktion
Dann verwenden wir die OFFSET-Funktion, um den Wert dieser Zelle zu erhalten, deren Position von der MATCH-Funktion zurückgegeben wurde.
| 1 | =OFFSET(B2,0,C4-1) |
