Letzten Wert einer Zeile oder Spalte in Excel ermitteln

Sie erfassen jede Woche einen Bestand, einen Zählerstand oder einen Kontostand, und an anderer Stelle soll immer der aktuellste Wert stehen. Mit einer einzigen Formel liest Excel den letzten Eintrag einer Zeile oder Spalte aus, auch wenn dazwischen Zellen leer sind.

Die Kurzlösung

Stehen die Werte in der Zeile C9:Q9, liefert diese Formel den letzten Eintrag:

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

Für eine Spalte tauschen Sie nur den Bereich, zum Beispiel =VERWEIS(2;1/(C24:C38<>"");C24:C38). Die Formel funktioniert in allen Excel-Versionen, ohne Matrixeingabe mit Strg+Umschalt+Eingabe.

Beispieldatei mit dem letzten Wert je Zeile und je Spalte

So arbeitet die Formel

Die Formel nutzt eine Eigenschaft von VERWEIS geschickt aus:

  1. C9:Q9<>"" prüft jede Zelle und liefert WAHR für gefüllte und FALSCH für leere Zellen.
  2. 1/(...) rechnet daraus 1 für gefüllte Zellen und den Fehler #DIV/0! für leere Zellen.
  3. VERWEIS sucht den Wert 2. Den gibt es nicht, alle Treffer sind 1. In diesem Fall nimmt VERWEIS den letzten Wert, der kleiner ist, und überspringt dabei Fehlerwerte. Das ist die letzte 1, also die letzte gefüllte Zelle.
  4. Das dritte Argument bestimmt, was zurückgegeben wird. Hier ist es derselbe Bereich, also der Wert selbst.

Weil das dritte Argument frei wählbar ist, erhalten Sie mit derselben Logik auch die Überschrift oder die Position des letzten Eintrags:

GesuchtFormel
letzter Eintrag=VERWEIS(2;1/(C9:Q9<>"");C9:Q9)
Überschrift dazu, zum Beispiel die Kalenderwoche=VERWEIS(2;1/(C9:Q9<>"");C$8:Q$8)
letzte Zahl, Text wird übersprungen=VERWEIS(2;1/ISTZAHL(C9:Q9);C9:Q9)
Zeilennummer des letzten Eintrags einer Spalte=VERWEIS(2;1/(C24:C38<>"");ZEILE(C24:C38))

Beispiel mit Lücken und Text

Die Beispieldatei enthält einen Lagerbestand je Kalenderwoche. Bei Artikel B fehlt der Wert für KW 3, in KW 6 steht der Text „k. A.“:

ArtikelKW 1KW 2KW 3KW 4KW 5KW 6KW 7Letzter Eintragaus KWLetzte Zahl
Artikel A12011510810096908585KW 785
Artikel B40383531k. A.k. A.KW 631

Die Lücke in KW 3 stört keine der Formeln. Beim Text kommt es darauf an, was Sie brauchen: Die Variante mit <>"" liefert den letzten Eintrag, also „k. A.“. Die Variante mit ISTZAHL liefert die letzte Zahl, also 31.

Varianten für Excel für Microsoft 365

In Excel für Microsoft 365 gibt es Funktionen, die dasselbe lesbarer ausdrücken:

FormelWann geeignet
=VERWEIS(2;1/(C9:Q9<>"");C9:Q9)alle Versionen, Dateien für unterschiedliche Empfänger
=XVERWEIS(WAHR;C9:Q9<>"";C9:Q9;;0;-1)Excel 2021 und Microsoft 365, Suchmodus -1 sucht von hinten
=ÜBERNEHMEN(FILTER(C9:Q9;C9:Q9<>"");;-1)Microsoft 365, für eine Spalte =ÜBERNEHMEN(FILTER(C24:C38;C24:C38<>"");-1)
=INDEX(C9:Q9;MAX(ISTZAHL(C9:Q9)*SPALTE(C9:Q9))-SPALTE(C9)+1)ältere Lösung, nur für Zahlen, vor Excel 365 als Matrixformel

Alle Varianten liefern mit denselben Daten dasselbe Ergebnis.

Wachsende Spalte ohne festen Bereich (Microsoft 365)

Bei einer Liste, die jede Woche länger wird, müssen Sie den Bereich in aktuellen Versionen von Microsoft 365 nicht mehr groß genug wählen. Ein gekürzter Bezug passt sich selbst an:

=ÜBERNEHMEN(C:.C;-1)

C:.C (Doppelpunkt und Punkt) steht für Spalte C bis zur letzten belegten Zelle. ÜBERNEHMEN mit -1 gibt davon die letzte Zelle zurück. Excel rechnet dabei nur die belegten Zellen, nicht eine Million Zeilen, die Formel bleibt also auch in großen Dateien schnell.

Ein Haken: Als belegt gilt auch eine Zelle mit Formel, die einen leeren Text "" liefert. Steht unter Ihren Werten eine solche Formel, ist das Ergebnis leer. Dann hilft die Variante mit XVERWEIS, die leere Texte überspringt:

=XVERWEIS(WAHR;C:.C<>"";C:.C;;0;-1)

Letzter Wert für jede Zeile auf einmal

Statt die Formel für jeden Artikel nach unten zu kopieren, berechnet NACHZEILE in Microsoft 365 den letzten Eintrag aller Zeilen in einem Schritt. Stehen die Wochenwerte der beiden Artikel aus dem Beispiel in C9:I10:

=NACHZEILE(C9:I10;LAMBDA(z;XVERWEIS(WAHR;z<>"";z;;0;-1)))

Das Ergebnis sind 85 für Artikel A und „k. A.“ für Artikel B. NACHZEILE übergibt der LAMBDA-Funktion jede Zeile einzeln als z. Mit ISTZAHL(z) statt z<>"" erhalten Sie die letzte Zahl, also 85 und 31. Kommen weitere Artikel hinzu, vergrößern Sie nur den Bereich.

Letzter Wert mit Bedingung

Oft wird nicht der letzte Wert einer Spalte gesucht, sondern der letzte Wert zu einem bestimmten Merkmal: der zuletzt bezahlte Preis eines Artikels, der letzte Zählerstand eines Geräts, die letzte Buchung eines Kunden. Ein Beispiel mit Preisen, die in zeitlicher Reihenfolge erfasst sind:

ArtikelPreis
X10
Y20
X11
Z30
Y21
X12

Den letzten Preis für Artikel X liefert in Excel 2021 und Microsoft 365:

=XVERWEIS("X";A2:A7;B2:B7;;0;-1)

Ergebnis: 12. Der Suchmodus -1 lässt XVERWEIS von unten nach oben suchen, der erste Treffer ist also der letzte Eintrag. In allen Versionen funktioniert die bekannte VERWEIS-Logik mit einer Bedingung: =VERWEIS(2;1/(A2:A7="X");B2:B7).

Eine Übersicht mit dem letzten Preis aller Artikel erzeugt in Microsoft 365 eine einzige Formel:

=LET(a;EINDEUTIG(A2:.A1000);
     HSTAPELN(a;XVERWEIS(a;A2:.A1000;B2:.B1000;;0;-1)))

EINDEUTIG listet jeden Artikel einmal, XVERWEIS sucht für alle gleichzeitig den letzten Preis, HSTAPELN setzt beides nebeneinander. Ergebnis: X 12, Y 21, Z 30. Neue Artikel erscheinen automatisch. Voraussetzung ist, dass die Liste zeitlich sortiert ist. Ist sie das nicht, suchen Sie über ein Datum, etwa mit =SORTIEREN(…) vorab oder mit MAXWENNS für das jüngste Datum.

Typische Fehler

  • Ganze Spalten als Bereich: A:A funktioniert, rechnet aber über eine Million Zellen. In großen Dateien wird das spürbar langsam. Begrenzen Sie den Bereich, verwenden Sie eine Tabelle (Strg+T), deren Bereich mitwächst, oder in Microsoft 365 den gekürzten Bezug A:.A.
  • INDEX mit falscher Spalte: Die alte Lösung mit MAX und SPALTE liefert die Spaltennummer des Blatts. Beginnt der Bereich nicht in Spalte A, muss die Startspalte abgezogen werden. Sonst greift INDEX daneben: Im Beispiel liefert die Formel ohne Abzug 0 statt 85, bei längeren Zeilen auch #BEZUG!.
  • Text in den Daten: Wer die letzte Zahl braucht, aber <>"" verwendet, erhält bei einem Eintrag wie „k. A.“ den Text. Nehmen Sie dann die Variante mit ISTZAHL.
  • Fehlerwerte in den Daten: Steht vor dem letzten Wert ein Fehler wie #DIV/0!, liefern VERWEIS und XVERWEIS trotzdem den letzten Wert. Die Variante mit FILTER gibt dagegen den Fehler zurück. Beheben Sie Fehler in den Daten oder fangen Sie sie dort mit WENNFEHLER ab.
  • Formeln, die „“ liefern: Zellen mit einer Formel wie =WENN(B5="";"";B5) gelten für <>"" als leer. Das ist meist gewünscht. ANZAHL2 zählt solche Zellen dagegen mit, deshalb taugt ANZAHL2 nicht zum Ermitteln der letzten Zeile.

Beispieldatei

Die Datei enthält beide Beispiele mit allen Formeln. Gelbe Zellen können Sie ändern, die Ergebnisse passen sich sofort an. Sie enthält keine Makros.

Mit KI schneller zum Ziel

Fragen Sie ChatGPT oder Copilot nach dem letzten Wert einer Spalte, kommt als Antwort gern =INDEX(C:C;ANZAHL2(C:C)). Die Formel zählt die gefüllten Zellen und nimmt an, dass die Liste keine Lücken hat. Stehen in C2 bis C6 die Werte 120, 115, eine leere Zelle, 100 und 96 unter einer Überschrift, liefert sie 100 statt 96, ohne jede Fehlermeldung. Testen Sie KI-Vorschläge deshalb immer mit einer Lücke und einem Text in den Daten, so wie in der Beispieldatei.

Mehr zu Stärken und Grenzen der Werkzeuge im Beitrag ChatGPT und Copilot in Excel: Möglichkeiten und Grenzen. Wie wir KI in der Entwicklung nutzen, damit Lösungen schneller und günstiger entstehen, steht unter KI und Automatisierung.

Aus der Praxis: aktuelle Werte im Kennzahlenbericht

Der letzte Wert ist der Kern vieler Berichte: aktueller Lagerbestand, letzter Monatsumsatz, Stand der offenen Posten. In einem Kennzahlenbericht stehen davon schnell zwanzig oder dreißig nebeneinander, mit Vergleich zum Vormonat und Ampel. Damit jede Zahl stimmt, wenn neue Daten dazukommen, bauen wir solche Berichte auf einer sauberen Datenbasis auf, die sich automatisch aktualisiert. Beispiele dafür zeigt die Seite Datenanalyse und Controlling.

Passende Beiträge

Lernen oder machen lassen?

Excel-Formeln, die im Alltag sitzen.

In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.