Excel: Letzte Zeile und letzten Wert einer Liste ermitteln

Foto des Autors

Für manche Anwendungen ist es praktisch, wenn man die letzte mit einem Wert gefüllte Spalte oder Zeile in einer Excel-Tabelle ermitteln kann.

Stellen Sie sich zur Veranschaulichung eine Tabelle vor, die die Benutzung eines Leihgeräts festhält.

In der minimalen Version dieser Liste steht in der ersten Spalte der Name des Entleihers. In der zweiten Spalte speichern Sie das Datum, an dem das Gerät ausgegeben wurde und in der letzten Spalte wird der Rückgabetermin festgehalten.

Das sieht dann etwa so aus:

Beispieltabelle für Ermittlung des letzten Wertes
Beispiel für die Ermittlung des letzten Wertes einer Liste

Weil die Liste recht lang werden kann, möchten Sie nun den letzten Vorgang zusätzlich immer ganz oben angezeigt bekommen. Das spart das Herunterscrollen in der Liste.

Es soll also rechts vom Kopf der Liste eine Art Kopie der untersten Werte der Spalten A, B und C stehen.

So ermitteln Sie die letzte Zeile einer Liste

Schreiben Sie in Zelle D1 folgende Formel hinein

=VERGLEICH(0;B:B;-1)

Die Funktion VERGLEICH() sucht hier die letzte Zeile der Spalte B, die einen Wert größer oder gleich 0 hat. Der Bezug B:B bezeichnet dabei die gesamte Spalte B.

Weil der Vergleich auf Zahlen basiert, eignet sich dazu die Spalte A nicht, die ja die Namen und damit Textdaten enhält.

Der letzte Parameter, also die „-1“, wählt die Art der Suche aus. Erlaubt sind auch 0 und 1, was anderen Suchmethoden bedeutet, die bei dem Problem hier nicht weiterhelfen. So würde eine 0 bedeuten, dass die erste Zeile ausgegeben wird, die exakt dem Suchwert entspricht.

Das Ergebnis ist allerdings noch nicht richtig hilfreich. Denn es kommt lediglich ein Zahlenwert heraus – die Zeilenzahl der gesuchten Zelle, hier also „5“.

Werbung

So zeigt Excel den Wert der ermittelten Zeile an

Um stattdessen die enthalten Daten zu ermitteln, also etwa den Namen des Entleihers, benötigen Sie noch eine Konstruktion mit der Funktion INDEX(). Die gibt den Wert einer Zelle aus einem bestimmten Bereich zurück und wird so verwendet:  INDEX(Bereich; Zeilennummer; Spaltennummer).

Wenn in D1 die Nummer der letzten verwendeten Zeile steht, ermittelt diese Formel den Namen dazu. (Die Spaltennummer können Sie also weglassen).

=INDEX(A:A;D1)

Diese Formel schreiben Sie zum Beispiel in Zelle E2 und in die zwei Zellen rechts daneben, also in F2 und G2 die Entsprechungen für Ausleih- und Rückgabedatum:

=INDEX(B:B;D1)
=INDEX(C:C;D1)

Die beiden Datumsfelder werden zuerst allerdings als Zahlenwert dargestellt. Sie müssen Sie noch in ein Datumsformat umwandeln. Also markieren Sie sie und wählen im Reiter Start aus der Liste der Formatierungen statt dem vorgegebenen Wert Standard eines der Datumsformate aus.

So verhindern Sie, dass ein fehlendes Datum als 00.01.1900 angezeigt wird

Ein Schönheitsfehler ist noch der Inhalt von G2, also dem Rückgabedatum, für den Fall, dass das Gerät noch entliehen ist. Denn dann steht darin das Datum „00.01.1900“.

Um das zu ändern, erweitern Sie die Formel darin um eine Bedingung. Nur wenn der Datumswert größer als dieses „Nulldatum“ ist, soll es erscheinen, ansonsten eine passende Textmeldung. Ändern Sie also G2 so ab:

=WENN(INDEX(C:C;D1)>0;INDEX(C:C;D1);"Noch entliehen!")

Das Ergebnis sieht dann so aus:

Beispiel für die Ermittlung des letzten Wertes einer Liste.
Excel zeigt den Inhalt der letzten Tabellenzeile an.

Tragen Sie eine neue Zeile für einen Leihvorgang ein, ändern sich die Werte entsprechend in den Feldern E2 bis G2.

Ach, die ermittelte Zeilenzahl in D1 stört Sie noch? Dann eliminieren Sie einfach diese Hilfszelle und ersetzen den Bezug auf D1 in allen Formeln in den Zellen E2, D2 und G2 den Bezug auf E1 durch VERGLEICH(0;B:B;-1). Alternativ wählen Sie einfach für E1 weiße Schriftfarbe und machen die Zelle so unsichtbar. Dritte Variante: Die Spalte ausblenden.

Foto des Autors

Markus Schraudolph

Markus ist IT-Fachjournalist der ersten Stunde. Seine ersten Texte veröffentlichte er 1987 beim legendären Markt&Technik-Verlag. Seine Spezialität sind Datenbanken und Microsoft Excel. Als PHP-Experte programmierte Markus maßgeblich die erste Version des Tippscout und ist anerkannter Experte für PHP, MySQL und Wordpress.

6 Gedanken zu „Excel: Letzte Zeile und letzten Wert einer Liste ermitteln“

  1. Hallo, ich bin gerade auf Ihre Formel gestoßen =VERGLEICH(0;Tabelle1!B:B;-1). Das hat mir auch fast geholfen, allerdings wird nun die Zeilennummer de zuletzt beschrieben Zeile der Spalte angezeigt. Ich würde allerdings gerne den Inhalt der zuletzt beschriebenen Zelle der Zeile in der Spalte anzeigen. Können Sie mir dahingehend weiterhelfen?

    mfg Jacky

    Antworten
    • Generell adressieren Sie bei einem Zell- oder Bereichsbezug ein anderes Tabellenblatt durch Voranstellung des Blattnamens gefolgt von einem Ausrufezeichen. Konkret: Sie möchten im 2. Tabellenblatt die genannte Formel nutzen, um auf das erste Blatt zuzugreifen, dann schreiben Sie: =VERGLEICH(0;Tabelle1!B:B;-1)

      Antworten

Schreibe einen Kommentar