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:
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“.
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:
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.
