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

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.



Schreibe einen Kommentar

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

12 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ß Autor des Beitrags

      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ß Autor des Beitrags

          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ß Autor des Beitrags

            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

          • Jake

            Wenn man jetzt noch die -999 durch (KKLEINSTE(A:A;-1)-1) ersetzt, hat man auch immer garantiert den kleinsten Wert. Nicht dass aus Versehen in der Tabelle mal eine Zahl auftaucht, die kleiner als die gesetzte Zahl ist.

          • Martin Weiß Autor des Beitrags

            Hallo Jake,

            sehr gute Idee, vielen Dank dafür!

            Schöne Grüße,
            Martin

  • Sven

    Hallo Martin,

    Deine Lösung bringt mich auf ein Problem was ich schon länger lösen möchte, jedoch bis jetzt noch keine passende einfache Lösung dazu gefunden habe:

    1. Teil: Kann man die Formel so umstellen/erweitern, dass man den Zeilenbereich nach der letzten befüllten Zeile ermitteln kann, die Tabelle ist natürlich begrenzt als Beispiel von Zeile 1 -50, Beispiel für die Spalte A: =INDEX($A$1:$A$50;MAX(VERGLEICH(-999;$A$1:$A$50;-1);VERGLEICH(“”;$A$1:$A$50;-1)))
    Das Ergebnis für den leeren Zeilenbereich nach der letzten befüllten Zeile, müsste also in Deinem Beispiel lauten: Tabelle1!$21:$50

    2. Teil: Das ermittelte Ergebnis des leeren Zeilenbereiches möchte ich in einen „definierten Namen“ dynamisch einbauen. Ist das möglich, wenn ja wie?

    3. Teil: Den dynamischen „definierten Namen“, nennen wir Ihn mal „leere_Zeilen_ausblenden“
    Möchte ich in einen Hyperlink einbauen der zum Beispiel so aussieht: =WENN($A21=””;HYPERLINK(“#leere_Zeilen_ausblenden”;”Links-Klick > Rechts-Klick > Ausblenden”);““). Der Hyperlink wird also nur dann angezeigt wenn die abgefragte Zelle leer ist.

    Da man ja leere Zeilen einer Tabelle, automatisch ermitteln und ausblenden nur mit VBA lösen kann, ich jedoch kein VBA nutzen möchte, soll zumindest das ausblenden der leeren Zeilen vereinfacht werden, halt mit einem Hyperlink auf einen „definierten Namen“ der einen Zeilenbereich beinhaltet.

    Wäre super wenn Du mir hier helfen könntest. Zurzeit habe ich es so gelöst, dass ich für eine Tabelle mit 50 Zeilen, 50 „definierte Namen“ angelegt habe, nach dem Schema:
    Name: leere_Zeilen_ausblenden_ab_2
    Bezieht sich auf: =Tabelle1!$2:$50
    Name: leere_Zeilen_ausblenden_ab_3
    Bezieht sich auf: =Tabelle1!$3:$50
    usw.
    und dann noch 50 Hyperlinks nach dem Schema:
    =WENN($A2=””;HYPERLINK(“#leere_Zeilen_ausblenden_ab_2″;”Links-Klick > Rechts-Klick > Ausblenden”);““).
    usw.
    Runterkopieren funktioniert hier leider nicht, da der Zähler im Hyperlink sich nicht hochzählt.

    Viele Grüße
    Sven

    • Sven

      Hallo Martin,

      ich habe mittlerweile eine bzw. mehrere Lösung(en) für mein Problem gefunden und beantworte meine Fragen mal selbst, für den Fall dass noch jemand so ein Thema hat.

      Zum 1. Teil:
      Die benötigte Formel lautet:
      =MAX(INDEX(($A$1:$A$50″”)*ZEILE($A$1:$A$50);0))+1&”:50″
      oder
      =SUMMENPRODUKT(MAX(($A$1:$A$50″”)*ZEILE($A$1:$A$50))+1)&”:50″
      Das Ergebnis der Formeln lautet jeweils: 21:50 Wenn die letzte gefüllte Zelle der auszuwertenden Spalte (also in dem Beispiel die Spalte A), die Zelle A20 ist. Es ist bei beiden Formeln egal ob Buchstaben oder Zahlen in der letzten befüllten Zelle stehen. Es ist auch egal, ob die Tabelle in Zeile 1 beginnt oder erst in Zeile 25, dann heißt es eben $A$25:$A$50.
      Die Formel:
      =VERGLEICH(“”;$A$1:$A$50;-1))+1&”:50″
      funktioniert auch, jedoch muss hier beachtet werden, wenn die Tabelle nicht in Zeile 1 beginnt, dann muss die +1 gegen die letzte Zeilennummer vor dem Tabellenbeginn ersetzt werden. Im obigen Beispiel muss es also +24 lauten.
      Die Formel trägt man in eine leere Zelle außerhalb der Tabelle ein, z.B. in K1

      Zum 2. Teil
      Um das Ergebnis dynamisch in einen „definierten Namen“, nennen wir Ihn mal „leere_Zeilen_ausblenden“, einzubauen, ist beim Anlegen des Namens im Feld „Bezieht sich auf:“ die folgende Formel einzugeben (für obiges Beispiel): =INDIREKT(Tabelle1!$K$1)

      Zum 3. Teil:
      Um beim obigen Beispiel zu bleiben, wird ein Hyperlink in Zelle L1 angelegt, der wie folgt lautet:
      =WENN($A1=””;HYPERLINK(“#leere_Zeilen_ausblenden”;”Links-Klick > Rechts-Klick > Ausblenden”);““). Dieser wird dann runterkopiert bis Zeile L50.
      In alle leeren Zeilen der Tabelle wird jetzt der Hyperlink angezeigt und wenn man diesen anklickt, werden alle leeren Zeilen markiert. Über einen Rechtsklick kann man diese dann relativ einfach ausblenden.

      Die Lösungsansätze habe ich auf den folgenden Seiten gefunden:
      http://www.tabellenexperte.de/finde-den-letzten-eintrag-einer-excel-liste/
      http://www.excelformeln.de/formeln.html?welcher=390
      http://www.excelformeln.de/formeln.html?welcher=48

      Viel Freude beim Nachmachen.
      Gruß
      Sven

      • Martin Weiß Autor des Beitrags

        Hallo Sven,

        tut mir sehr leid, dass Deine Frage vom Juni bei mir untergegangen ist. Aber ist freut mich umso mehr, dass Du selbst noch eine Lösung gefunden hast und diese hier mit den anderen Lesern teilst.

        Schöne Grüße,
        Martin