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
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
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
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
Und wieder einmal hat uns die extrem vielseitige SUMMENPRODUKT-Funktion bei der Problemlösung geholfen.
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.
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
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
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.
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
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
Hallo Florian,
ja, du musst nur in der Formel die MAX-Funktion durch die MIN-Funktion ersetzen.
Schöne Grüße,
Martin
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
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