Den letzten Wert in einer Spalte oder Zeile ermitteln – Excel

In diesem Tutorial lernen Sie, wie Sie den letzten Wert in einer Spalte oder Zeile in Excel ermitteln können.

Letzter Wert in einer Spalte

Sie können die VERWEIS-Funktion verwenden, um die letzte nicht leere Zelle in einer Spalte zu finden.

=VERWEIS(2;1/(B:B<>"");B:B)

Gehen wir diese Formel einmal durch:

Der Teil der Formel B:B<>““ gibt ein Array mit den Werten Wahr und Falsch zurück: {FALSCH, WAHR, WAHR,…}, wobei getestet wird, ob jede Zelle in Spalte B leer ist (FALSCH).

=VERWEIS(2;1/({FALSCH,WAHR,WAHR,WAHR,WAHR,WAHR,WAHR,FALSCH,...);B:B)

Diese booleschen Werte wandeln sich in 0 oder 1 um und werden zur Division von 1 als Nenner verwendet.

=VERWEIS(2;{#DIV/0!,1,1,1,1,1,#DIV/0!,;B:B)

Dies ist der Parameter verweis_vektor für die VERWEIS-Funktion. In unserem Fall beträgt der Parameter verweis_wert 2, aber der größte Wert im verweis_vektor beträgt 1, so dass die VERWEIS-Funktion die letzte 1 im Array findet und den entsprechenden Wert im ergebnis_vektor zurückgibt.

Wenn Sie sicher sind, dass Sie nur numerische Werte in Ihrer Spalte haben, Ihre Daten in Zeile 1 beginnen und Ihr Datenbereich kontinuierlich ist, können Sie eine etwas einfachere Formel mit den Funktionen INDEX und ANZAHL verwenden.

=INDEX(B:B,ANZAHL(B:B))

Die Funktion ANZAHL liefert die Anzahl der Zellen, die mit Daten im fortlaufenden Bereich gefüllt sind (4), und die Funktion INDEX gibt den Wert der Zelle in der entsprechenden Zeile (4) an.

Um mögliche Fehler zu vermeiden, falls Ihr Datenbereich eine Mischung aus numerischen und nicht-numerischen Werten ist oder sogar einige leere Zellen enthält, können Sie die VERWEIS-Funktion zusammen mit den Funktionen ISTLEER und NICHT verwenden.

=VERWEIS(2;1/(NICHT(ISTLEER(B:B)));B:B)

Die Funktion ISTLEER gibt ein Array zurück, das die Werte Wahr und Falsch enthält, die Einsen und Nullen entsprechen. Die Funktion NICHT ändert True (d.h. 1) in False und False (d.h. 0) in True. Wenn wir das resultierende Array invertieren (wenn wir 1 durch dieses Array dividieren), erhalten wir ein Ergebnis-Array, das wiederum #DIV/0!-Fehler und Einsen enthält und das als Verweis-Array (verweis_vektor) in unserer VERWEIS-Funktion verwendet werden kann. Die Funktionalität der VERWEIS-Funktion ist dann dieselbe wie in unserem ersten Beispiel: Sie gibt den Wert des Ergebnisvektors an der Position der letzten 1 im Verweis-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 Funktion ZEILE in Ihrem Ergebnis_Vektor ändern.

=VERWEIS(2;1/(B:B<>"");ZEILE(B:B))

Letzter Wert in der Zeile

Um den Wert der letzten nicht leeren Zelle in einer mit numerischen Daten gefüllten Zeile zu ermitteln, können Sie einen ähnlichen Ansatz verwenden, allerdings mit anderen Funktionen: die BEREICH.VERSCHIEBEN-Funktion zusammen mit den VERGLEICH- und MAX-Funktionen.

=BEREICH.VERSCHIEBEN(Referenz, Zeilen, Spalten)

=BEREICH.VERSCHIEBEN(B2;0;VERGLEICH(MAX(B2:XFD2)+1;B2:XFD2;1)-1)

Schauen wir uns an, wie diese Formel funktioniert.

Die Funktion Vergleich

Wir verwenden die VERGLEICH-Funktion, um zu „zählen“, wie viele Zellenwerte unter 1 + das Maximum aller Werte in Zeile2 ab B2 liegen.

=VERGLEICH(verweis_wert, verweis_array, [vergleichstyp])

=VERGLEICH(MAX(B2:XFD2)+1,B2:XFD2,1)

Der Parameter verweis_wert der VERGLEICH-Funktion ist das Maximum aller Werte in der Zeile2 + 1. Da dieser Wert in der Zeile2 offensichtlich nicht existiert und der Parameter vergleichstyp auf 1 (kleiner oder gleich verweis_wert) gesetzt ist, gibt die VERGLEICH-Funktion die Position der letzten „geprüften“ Zelle im Array zurück, d.h. die Anzahl der Zellen, die mit Daten im Bereich B2:XFD2 gefüllt sind (XFD ist die allerletzte Spalte in den neueren Versionen von Excel).

Die Funktion BEREICH.VERSCHIEBEN

Dann verwenden wir die BEREICH.VERSCHIEBEN-Funktion, um den Wert dieser Zelle zu erhalten, deren Position von der VERGLEICH-Funktion zurückgegeben wurde.

=BEREICH.VERSCHIEBEN(B2;0;C4-1)