Finde den letzten Eintrag einer Excel-Liste 8

Artikelbild-101
Mit SUMMENPRODUKT die letzte benutzte Zeile in einer Excel-Tabelle ermitteln.
 

Ein immer mal wieder vorkommendes Problem ist, den letzten Eintrag in einer Excel-Tabelle zu finden und auszugeben.

Nein, ich spreche nicht davon, „zu Fuß“ bis zum Ende der Tabelle zu blättern, sondern ich möchte den jeweils letzten Eintrag automatisch per Formel finden und an anderer Stelle ausgeben.

Vor längerer Zeit hatte ich dazu schon mal eine Lösung vorgestellt.

Heute zeige ich dir einen anderen und deutlich flexibleren Weg dafür – natürlich ohne VBA.

Meine Funktion der Wahl lautet wieder einmal: SUMMENPRODUKT

Und so geht’s:

Die Aufgabe

Ich habe eine Adressenliste, aus der ich mir den jeweils letzten Eintrag in dem roten Bereich rechts oben anzeigen lassen möchte (die Beispieldatei kannst du dir hier herunterladen):

Adressenliste

Adressenliste

Wenn jetzt ein neuer Eintrag am Ende der Liste dazukommt, soll er automatisch wieder rechts oben angezeigt werden.

Wie macht man das?

Die Lösung

Eine geschickte Kombination aus INDEX und SUMMENPRODUKT bringt uns zum Ziel:

Den letzten Eintrag finden

Den letzten Eintrag finden

Aber jetzt mal langsam und der Reihe nach.

Die alles umschließende INDEX-Funktion soll also den letzten Eintrag in unserer Adressenliste liefern. Die eigentliche Arbeit macht aber die Funktion SUMMENPRODUKT:

=SUMMENPRODUKT(MAX(($A:$A<>"")*ZEILE($A:$A)))

Die SUMMENPRODUKT-Funktion ist eine Matrix-Funktion. Das heißt, die in ihr enthaltenen Elemente werden so behandelt, wie wenn man sie in einer Matrix eingibt und dann für jedes einzelne Element die angegebene Berechnung durchführt.

Beginnen wir mit dem ersten Teil
($A:$A<>"")
Hier prüft die Funktion für jedes Element in Spalte A, ob es nicht leer ist. Da ich nicht weiß, wie lange die Tabelle wird, habe ich daher zur Sicherheit die komplette Spalte A angegeben. Steht die maximale Länge von Anfang an fest, gibt man natürlich besser den tatsächlichen Bereich ein.

Das Ergebnis jeder Berechnung ist entweder WAHR (d.h. es ist kein leeres Element) oder FALSCH (wenn die Zelle leer ist).

Kommen wir zum zweiten Teil:
ZEILE($A:$A)
Hier wird einfach die Zeilennummer zu jeder geprüften Zelle zurückgeliefert.

Nun multipliziert man die beiden Listen:
($A:$A<>"")*ZEILE($A:$A)
Dabei entspricht ein WAHR dem Wert 1 und ein FALSCH dem Wert 0 (Null).

Da wir lediglich am letzten Wert interessiert sind, umschließen wir das Ganze noch mit der MAX-Funktion:
MAX(($A:$A<>"")*ZEILE($A:$A))
Diese ermittelt den größten Wert, was in unserem Beispiel die 17 wäre. Hier ist nochmal alles in der Übersicht dargestellt:

Die Funktionsweise von SUMMENPRODUKT

Die Funktionsweise von SUMMENPRODUKT

Damit haben wir für unsere INDEX-Funktion den gewünschten Wert – nämlich die Zeile 17. Für die Ausgabe der anderen Adresseninformationen müssen wir nur die Spaltenangabe anpassen. Für den Namen also Spalte B, für Straße Spalte C usw.:

Spaltenangabe anpassen

Spaltenangabe anpassen

Und wieder einmal hat uns die extrem vielseitige SUMMENPRODUKT-Funktion bei der Problemlösung geholfen.

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Martin Weiß Antworten abbrechen

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

8 Gedanken zu “Finde den letzten Eintrag einer Excel-Liste

  • Avatar-Foto
    Paul

    Hallo Martin,

    könntest Du von diesem schönen Ansatz ausgehend, für die letzten 5 Werte einer sich stetig verlängernden Spalte eine Ausgabe per Formel entwickeln oder schon besitzen? Evtl. nicht als Arrayformel.

    Meine Freude wäre dir gewiss.

    Schöne Grüße
    Paul

    • Avatar-Foto
      Martin Weiß

      Hallo Paul,

      eine Möglichkeit wäre, in der letzten Index-Formel im Artikel anstelle der MAX-Funktion die KGRÖSSTE-Funktion zu verwenden:
      =INDEX(A:A;SUMMENPRODUKT(KGRÖSSTE(($A:$A<>„“)*ZEILE($A:$A);5)))
      liefert den 5.-letzten Wert,
      =INDEX(A:A;SUMMENPRODUKT(KGRÖSSTE(($A:$A<>„“)*ZEILE($A:$A);4)))
      liefert den 4.-letzten Wert und so weiter.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Armin Hank

    Hallo Martin,
    bin erst heute auf Deine coole & nützliche Website gestoßen.
    Bei meiner aktuellen Aufgabe brauche ich nicht den Wert der in der äußersten rechten bzw. untersten Zelle steht, sondern die Zellenadresse.
    Hält Excel hierfür eine Funktion parat die es einem erspart über 17 Milliarden Zellen zu scannen?

    Es grüßt Dich Armin aus Bayern.

    • Avatar-Foto
      Martin Weiß

      Hallo Armin,

      bezogen auf meine Beispieldatei liefert die folgende Formel zumindest die Zeilennummer der letzten Zeile in der betreffenden Spalte:
      =MAX(($A:$A<>„“)*ZEILE($A:$A))

      Und damit hast du ja auch die Zelladresse.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Florian

    Hallo Martin,
    wäre es mit einer Adaption der Formel möglich, den ersten Wert einer Liste zu bestimmen bzw deren Zeilennummer?
    Sprich ein Bereich beginnt mit Leerzellen, gib die erste Zelle aus, die nicht leer ist.
    Liebe Grüße
    Florian

    • Avatar-Foto
      Martin Weiß

      Hallo Florian,

      ja, du musst nur in der Formel die MAX-Funktion durch die MIN-Funktion ersetzen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Eugen

    Hallo Martin,

    kann es sein, dass die Formel bei einem Bereich, der als Tabelle definiert ist, nicht funktioniert? Ich bekomme die Fehlermeldung #BEZUG!
    Das sowohl bei Auswahl einer ganzen Spalte, als auch bei bei einem Teilbereich.

    Mit einem nicht als Tabelle definiert Bereich funktioniert es allerdings wunderbar. Deine Erläuterung ist wirklich 1A!

    Grüße
    Eugen

    • Avatar-Foto
      Martin Weiß

      Hallo Eugen,

      doch, die Formel funktioniert grundsätzlich auch mit formatierten Tabellen. Allerdings ist es wichtig, dass in den Bezügen auch die Kopfzeilen mit enthalten sind.

      Also nicht so:
      =INDEX(Tabelle1[Vorname];SUMMENPRODUKT(MAX((Tabelle1[Vorname]<>„“)*ZEILE(Tabelle1[Vorname]))))

      Sondern so:
      =INDEX(Tabelle1[[#Alle];[Vorname]];SUMMENPRODUKT(MAX((Tabelle1[[#Alle];[Vorname]]<>„“)*ZEILE(Tabelle1[[#Alle];[Vorname]]))))

      Schöne Grüße,
      Martin