Finde den letzten Eintrag einer Excel-Liste 2

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.

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.

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

  • 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

    • Martin Weiß Beitragsautor

      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