Das Beste kommt zum Schluß: Die letzte Zeile einer Excel-Tabelle 7

Mit der simplen Kombination von ein paar Excel-Funktionen lässt sich der letzte Eintrag einer Tabelle finden - ganz ohne VBA.
 

Manchmal möchte man den jeweils letzten Eintrag seiner Excel-Tabelle zusätzlich noch an einer anderen Stelle ausgeben. Zum Beispiel ganz am Anfang einer sehr langen Tabelle, um den Eintrag jederzeit im Blick zu haben. Oder um ihn vielleicht in einem Formular ausgeben zu können.

Da die Tabelle aber ständig länger wird, sich das Ende also dynamisch nach unten verschiebt, kann man nicht einfach eine Verknüpfung zu einer festen Zeile erstellen, denn man kennt ja das Ende der Tabelle noch nicht.

Mit der geschickten Kombination einiger Excel-Standardfunktionen gibt es auch für dieses Problem ein Happy End. Und zwar ganz ohne VBA-Programmierung bemühen zu müssen.

Und so geht’s:

Durchgängige Tabelle

Am leichtesten ist es, wenn man eine fortlaufende Tabelle hat, in der in jeder Zeile ein Eintrag steht, so wie in meinem kleinen Beispiel. Hier habe ich eine Adressenliste und ich möchte den jeweils letzten Eintrag oben rechts in dem hervorgehobenen Bereich ausgeben lassen:

Beispiel: Adressenliste

Beispiel: Adressenliste

Wie gelingt uns das nun?

Dazu muss ich eigentlich nur wissen, in welcher Zeile der letzte Eintrag steht, sprich: wieviele Zeilen meine Tabelle hat. Da meine Adressenliste keine Zahlen, sondern alphanumerische Werte enthält, verwende ich dazu die Excel-Funktion ANZAHL2. Und das Ergebnis davon benutze ich in der INDEX-Funktion, um den Inhalt auszugeben:

ANZAHL2 und INDEX in Kombination

ANZAHL2 und INDEX in Kombination

Zur Erläuterung:
Die ANZAHL2-Funktion gibt die Anzahl der nichtleeren Zellen in dem angegebenen Bereich wieder. Da ich ja nicht weiß, wie lange meine Tabelle irgendwann sein wird, habe ich in meinem Beispiel die komplette Spalte A angegeben (“A:A)” und erhalte damit den Wert 17 zurück (die Überschrift wird mitgezählt).

Und an die INDEX-Funktion übergibt man in der einfachsten Version zwei Parameter. Nämlich einen Tabellenbereich und eine Zeilennummer. Zurückgeliefert wird dann der Inhalt der betreffenden Zelle.

Auch hier gebe ich die komplette Spalte A an (“A:A”), der zweite Parameter wird durch die ANZAHL2-Funktion geliefert (= 17). Damit gibt die INDEX-Funktion den Inhalt der 17. Zeile in Spalte A aus.

Tipp:
Sollte meine Tabelle nicht wie hier in der ersten Zeile beginnen, sondern z.B. erst in der vierten, dann muss ich einfach noch die Anzahl der Leerzeilen addieren. Und meine Funktion würde dann eben so aussehen:
=INDEX(A:A;ANZAHL2(A:A)+3)

Zusätzliche Leerzeilen am Anfang

Zusätzliche Leerzeilen am Anfang

Was aber, wenn es innerhalb meiner Tabelle leere Zeilen gibt?

Tabelle mit Leerzeilen

Sollten sich innerhalb meiner Tabelle Leerzeilen befinden, funktioniert die oben vorgestellte Lösung leider nicht. Die ANZAHL2-Funktion ermittelt wieder die Zahl 17, denn es gibt ja einschließlich der Überschrift 17 gefüllte Zeilen. Diese Zahl entspricht aber nicht mehr unserer letzten Zeilennummer, denn durch die Leerzeilen ist meine letzte Zeile in der Tabelle nach unten gerutscht:

Problem: Leere Zeilen

Problem: Leere Zeilen

Aber natürlich gibt es auch hierfür eine Lösung!

Da die ANZAHL2-Funktion nicht zum gewünschten Ergebnis führt, verwende ich stattdessen die VERGLEICH-Funktion, um nach der letzten Zeile in meiner Tabelle zu suchen.

Zur Erinnerung:
Die VERGLEICH-Funktion sucht in einem Zellbereich (Parameter 2) nach dem angegebenen Suchkriterium (Parameter 1) und gibt dessen Position zurück. Dabei kann man über einen optionalen dritten Parameter noch die Art und Weise bestimmten, wie gesucht werden soll:
=VERGLEICH(Suchkriterium; Bereich; Vergleichstyp)

Der Vergleichstyp kann entweder 1, 0 oder -1 sein. Wenn er weggelassen wird, unterstellt Excel den Wert 1. Die Excel-Hilfe sagt dazu folgendes:

Excel-Hilfe zum Vergleichstyp

Excel-Hilfe zum Vergleichstyp

Und genau diese dritte Option -1 machen wir uns hier auf eine zugegebenermaßen etwas ungewöhnliche Weise zunutze:
=VERGLEICH("";A:A;-1)

Als Suchkriterium werden zwei doppelte Anführungszeichen übergeben, was bedeutet, ich suche in Spalte A (= Paramter 2) nach einer leeren Zelle. Und jetzt kommt der Trick mit dem Vergleichstyp -1 (= Parameter 3). Excel geht laut Hilfefunktion bei der Suche davon aus, dass es sich um eine absteigend sortierte Liste handelt.

Das ist für uns völlig irrelevant, führt aber dazu, dass die Suche nicht beim ersten gefundenen Treffer aufhört, sondern bis zum Ende der Tabelle weitergeht. Denn Excel nimmt ja an, dass die Liste absteigend sortiert ist und immer “kleinere” Werte kommen, je weiter man in der Tabelle nach unten geht. Und da laut Hilfefunktion der kleinste Wert gesucht wird, der mit dem Suchkriterium (“”) übereinstimmt oder größer ist, trifft dies immer auf den letzten Wert in der Tabelle zu:

Liefert auch bei leeren Zeilen das gewünschte Ergebnis

Liefert auch bei leeren Zeilen das gewünschte Ergebnis

Die so ermittelte Position wird wieder an die INDEX-Funktion übergeben und liefert damit den gewünschten Zelleninhalt, wie man im Bild oben sieht.

Ich gebe zu, dass diese Lösung wahrscheinlich nicht auf auf Anhieb für jedermann nachvollziehbar ist. Manchmal muss man in Excel etwas um die Ecke denken und kommt dafür auf Lösungen, die vielleicht auch von den Excel-Schöpfern nicht unbedingt vorgesehen waren.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

7 Gedanken zu “Das Beste kommt zum Schluß: Die letzte Zeile einer Excel-Tabelle

  • Carola Lück

    Das mit den Leerzeilen und Vergleich funktioniert leider nicht, wenn in Spalte A Zahlen stehen z.B. Kundennummern. Dann findet er nur die Überschrift.
    zwei Anführungszeichen sind ja ein leerer Text und keine leere Zelle. Einfache Probe mit istleer(zelle).

    • Martin Weiß Beitragsautor

      Hallo Carola,

      danke für den Hinweis. Du hast Recht, wenn die Suchspalte Zahlen enthält, dann funktioniert die VERGLEICH-Funktion so nicht. Ein Lösung dafür könnte so aussehen:
      Anstelle des leeren Textes suchen wir nun nach der Ziffer 0. Die Formel heißt somit:
      =INDEX(A:A;VERGLEICH(0;A:A;-1))

      Für den Fall, dass die Null ebenfalls in der Liste vorkommt, muss ein Wert gewählt werden, der kleiner als die kleinste vorkommende Ziffer ist. Das kann dann auch eine negative Zahl sein:
      =INDEX(A:A;VERGLEICH(-999;A:A;-1))

      Schöne Grüße,
      Martin

      • Weis

        Super Artikel. Hab lange nach einer derartigen Lösung gesucht.
        Leider ist bei mir der Fall, dass sowohl Zahlen, als auch Buchstaben in einer Zeile Stehen können.
        Gibt es dafür auch eine Lösung? Am besten auch ohne Makro…?

        • Martin Weiß Beitragsautor

          Hallo Weis,

          ja, auch dafür gibt es eine Lösung. Man muss nur zwei VERGLEICH-Funktionen einsetzen. Einmal für Zahlen und einmal für Buchstaben und den jeweils höchsten gefundenen Wert übergibt man dann an die INDEX-Funktion:
          =INDEX(A:A;MAX(VERGLEICH(-999;A:A;-1);VERGLEICH(“”;A:A;-1)))

          Grüße,
          Martin

          • Alex

            Gibt es auch irgendwie die Möglichkeit, den letzten Datensatz aus einer Zelle auszulesen?
            Beispielsweise: In der Zelle A2 steht: Dr. med. Dieter Willhelm

            da ich mich für den Nachnamen in diesem Fall Willhelm interessiere, frage ich mich, ob ich das über irgendeine Funktion auch auslesen kann. Also einfach den letzten Datensatz?

          • Martin Weiß Beitragsautor

            Hallo Alex,

            mit Datensatz meinst Du hier einfach das letzte Wort, richtig? Da gibt’s verschiedene Ansätze. Entweder die Zelle in ihre Bestandteile zerlegen über das Menü “Daten – Text in Spalten” und dabei das Leerzeichen als Spaltentrenner angeben.

            Oder mit einer etwas komplizierteren Formellösung. Annahme: Der Text “Dr. med. Dieter Willhelm” steht in Zelle A1:
            =RECHTS(A1;LÄNGE(A1)-FINDEN(“~”;WECHSELN(A1;” “;”~”;LÄNGE(A1)-LÄNGE(WECHSELN(A1;” “;)))))

            Was mich gerade auf die Idee bringt, darüber mal einen kleinen Artikel zu schreiben…

            Schöne Grüße,
            Martin