Vielen Excel-Anwendern dürfte bekannt sein, wie man mit Hilfe der SVERWEIS-Funktion einen bestimmten Wert in einer Liste findet.
Wenn der gesuchte Eintrag mehrmals in der Liste vorhanden ist, dann gibt die SVERWEIS-Funktion üblicherweise den ersten Wert wieder.
Was macht man aber, wenn man eigentlich den letzten Wert bräuchte? Wann also z.B. ein Produkt zum letzten Mal verkauft wurde? Hier hilft der SVERWEIS leider nicht weiter.
Ein etwas kniffeliges Problem, das wir in diesem Artikel ganz elegant lösen werden. Und so geht’s:
Wenn der SVERWEIS versagt…
Ich habe dazu wieder eine Beispieltabelle vorbereitet, die du dir hier herunterladen kannst. Es handelt sich dabei um eine Umsatzübersicht für verschiedene Verkäufer:
Die Beispiel-Tabelle
Wie man unschwer erkennen kann, taucht jeder Verkäufer mehrfach in der Liste auf. Zur Verdeutlichung habe ich die Liste mit einer bedingten Formatierung versehen, so dass der in E2 eingetragene Verkäufer hervorgehoben wird:
Bedingte Formatierung
Nun möchte ich in Zelle F2 den letzten Umsatz des Verkäufers aus Zelle E2 berechnen. Versucht man jetzt, mit dem SVERWEIS den letzten Umsatz für Verkäufer Müller zu finden, stößt man ganz offensichtlich an die Grenzen dieser Funktion:
Wenn der SVERWEIS versagt
Was also tun?
…hilft SUMMENPRODUKT
Wir müssen also irgendwie an die letzte Zeile herankommen, in der es einen Umsatz bei Herrn Müller gegeben hat. Das erreichen wir mit der vielseitigen SUMMENPRODUKT-Funktion.
Geben wir also in Zelle F2 folgende Formel ein:
=SUMMENPRODUKT(MAX((A2:A31=E2)*ZEILE(A2:A31)))
Und auf wundersame Weise wird die korrekte Zeilennummer ermittelt:
SUMMENPRODUKT findet die Zeile
Was passiert in dieser Formel?
Zerlegen wir die SUMMENPRODUKT-Formel einfach in ihre Bestandteile. Dazu habe ich im Bereich H2:H31 ein Hilfstabelle angelegt, die die Wirkungsweise der Funktion verdeutlichen soll:
SUMMENPRODUKT in die Bestandteile zerlegt
In H2 prüfen wir, ob der Verkäufer in Spalte A unserer Umsatztabelle dem ausgewählten Verkäufer in Zelle E2 entspricht. So wird für jede Zeile der Wahrheitswert WAHR bzw. FALSCH berechnet.
In der Spalte I lassen wir uns mit Hilfe der ZEILE-Funktion einfach die jeweilige Zeilennummer ausgeben. Und in Spalte J berechnen wir das Produkt aus Spalte H und I. Dazu muss man nur wissen, dass der Wahrheitswert WAHR der Zahl 1 und der Wert FALSCH der Zahl 0 entspricht. Zum Schluss berechnen wir mit der MAX-Funktion in Zelle J32 noch den maximalen Wert und kommen somit auf das gleiche Ergebnis wie die SUMMENPRODUKT-Funktion.
Den letzten Umsatz finden
Da wir nun die Zeile des letzten Umsatzes in Zelle F2 stehen haben, brauchen wir diesen Wert nur noch der INDEX-Funktion übergeben. Zuvor müssen wir jedoch noch eine Zeile abziehen, da uns ja die Kopfzeile mit den Spaltenüberschriften nicht interessiert. Und somit erhalten wir den passenden Umsatz:
=INDEX(C2:C31;F2-1)
INDEX liefert das Ergebnis
Und weil wir ohne die Hilfsspalte für die Zeilenberechnung auskommen wollen, packen wir alles ein eine einzige Formel:
=INDEX(C2:C31;SUMMENPRODUKT(MAX((A2:A31=E2)*ZEILE(A2:A31)))-1)
Die fertige Formel
Fertig ist unsere tolle Berechnung. Und wieder einmal zeigt sich, wie mächtig und vielseitig die SUMMENPRODUKT-Funktion ist.
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.
„Den letzten Wert einer Liste finden“
Eine interessante Fragestellung und eine prima Lösung.
Hallo Gerhard,
danke für deinen Kommentar und es freut mich, wenn die Lösung gefällt.
Schöne Grüße,
Martin
Klasse, sehr elegante Lösung – bestens dargestellt!
Man kann sie auch etwas abwandeln u. in vielen Problemstellungen verwenden.
Z.B. mit „KGRÖSSTE“ statt „MAX“. So lassen sich viele Übereinstimmungen per „VERKETTEN“ in einer Zelle anzeigen.
So könnte man eine Geburtstagsliste in einen Excelkalender einlesen auch wenn Geburtstage am selben Tag mehrfach vorkommen.
Gruß aus Bürgstadt
Hallo Martin,
danke für das Lob! Die SUMMENPRODUKT-Funktion ist wirklich ein Tausendsasa und die Möglichkeiten sind unendlich, meistens denkt man einfach nur nicht dran. Übrigens gibt es gerade einen aktuellen und sehr ausführlichen Artikel zu SUMMENPRODUKT auf dem Blog von Gerhard Pundt.
Die Idee mit der Geburtstagsliste klingt übrigens sehr interessant.
Schöne Grüße,
Martin
Hallo Martin,
eigentlich macht bei dieser Formel die MAX()-Funktion die Hauptarbeit. Nur weil sie von Hause aus das Ergebnis nicht ohne Sondertasten-Kombination darstellen kann, wird sie zusätzlich in die Summenprodukt()-Funktion eingebettet, denn so geht es auch:
{=INDEX(C2:C31;MAX((A2:A31=E2)*ZEILE(A2:A31))-1;1)}.
Mit der folgenden Einbettungsfunktion geht es ohne Strg+Shift+Enter auch:
=INDEX(C2:C31;AGGREGAT(15;6;MAX((A2:A31=E2)*ZEILE(A2:A31))-1;1))
Gruß von Luschi
aus klein-Paris
Hallo Luschi,
vielen Dank für Deine Ergänzungen. Besonders die Variante mit der AGGREGAT-Funktion finde ich sehr gelungen. In Excel führen eben immer viele Wege nach Rom.
Schöne Grüße,
Martin
Kann man diese Funktion auch so abwandeln, dass bspw. auch die Spalte Daten mit einbezogen werden? Dass Quasi die komplette letzte Zeile von Herrn Müller ausgespuckt wird?
Hallo Duong,
klar, du musst dazu nur nochmal die gleiche Formel wie für den Umsatz eingeben und lediglich die Spaltenangabe im INDEX-Teil von Spalte C auf Spalte B ändern.
Schöne Grüße,
Martin
Guten Tag,
geht das auch mit zwei Werten?
Z.B.: zusätzlich zum Nachnamen kommt noch ein Vorname dazu und das aber unter Berücksichtigung des letzten (größten) Datums.
Würde mich freuen, wenn jemand eine Lösung dazu hätte.
Danke
Hallo Markus,
klar geht das, man muss nur den Teil in der MAX-Funktion um das zusätzliche Kriterium erweitern. Angenommen, die Vornamen sind in der Liste in Spalte B und der gesuchte Vorname steht in Zelle G2, dann würde die Formel lauten:
=INDEX(D2:D31;SUMMENPRODUKT(MAX((A2:A31=F2)*(B2:B31=G2)*ZEILE(A2:A31)))-1)
Schöne Grüße,
Martin
Hallo Martin,
danke für deinen Tipp (entschuldige bitte die Verspätung). Ich komme aber auf kein gültiges Ergebnis. Ich schildere dir hier die Situation:
Das ist die Tabelle „Info“ mit den Daten:
A B C D
Schule Fach Art Datum
London Deutsch Deu.Lon1 26.07.2019
Barcelona Deutsch Deu.Bar1 26.07.2019
Rom Deutsch Deu.Rom1 26.07.2019
Paris Deutsch Deu.Par1 26.07.2019
Wien Deutsch Deu.Wie1 26.07.2019
London Mathematik Mat.Lon1 26.07.2019
Barcelona Mathematik Mat.Bar1 26.07.2019
Rom Mathematik Mat.Rom1 26.07.2019
Paris Mathematik Mat.Par1 26.07.2019
Wien Mathematik Mat.Wie1 26.07.2019
London Englisch Eng.Lon1 26.07.2019
Barcelona Englisch Eng.Bar1 26.07.2019
Rom Englisch Eng.Rom1 26.07.2019
Paris Englisch Eng.Par1 30.07.2019
Wien Englisch Eng.Wie1 31.07.2019
London Geografie Geo.Lon1 31.07.2019
Barcelona Geografie Geo.Bar1 02.08.2019
Rom Geografie Geo.Rom1 02.08.2019
Paris Geografie Geo.Par1 02.08.2019
Wien Geografie Geo.Wie1 03.08.2019
London Deutsch Deu.Lon2 19.08.2019
Wien Geografie Geo.Wie2 19.08.2019
Hier die Tabelle „Ergebnis“ mit den Ergebnissen (es stimmt nur die 1. Zeile, der Rest nicht).
A B C D
Schule Deutsch Mathematik Englisch
London Deu.Lon2 Mat.Lon1 Eng.Lon1
Barcelona Deu.Rom1 Mat.Rom1 Eng.Rom1
Rom Deu.Wie1 Mat.Wie1 Eng.Wie1
Paris Mat.Bar1 Eng.Bar1 Geo.Bar1
Wien Mat.Par1 Eng.Par1 Geo.Par1
Das ist die Formel für Zelle B2 der Tabelle „Ergebnis“:
=INDEX(Info!$C2:$C100;SUMMENPRODUKT(MAX((Info!$A2:$A100=Ergebnis!$A2)*(Info!$B2:$B100=Ergebnis!B$1)*ZEILE(Info!$D2:$D100)))-1)
Für die anderen Zellen sind die Formeln entsprechend abgewandelt, ausgehend von Spalte A und Zeile 1, in welchen die entsprechenden Informationen vorhanden sind – z.B. Zelle D6:
=INDEX(Info!$C6:$C104;SUMMENPRODUKT(MAX((Info!$A6:$A104=Ergebnis!$A6)*(Info!$B6:$B104=Ergebnis!D$1)*ZEILE(Info!$D6:$D104)))-1)
Was mache ich falsch?
Es würde mich freuen, wenn du eine Lösung dafür hättest.
Schöne Grüße
Markus
Hallo Martin, habe den Fehler entdeckt. Es lag an meiner Zeilennummerierung.
Jetzt klappt es.
Danke nochmals
Markus
Hallo Martin,
danke erst mal für die hilfreiche Formel und gute Aufschlüsselung. Eine Frage, die ich noch hätte, wäre, ob sie wissen wie man die Formel umgestalten kann, dass man nicht nur Werte in Spalte A abgleichen kann, sondern in z.B. Spalte M. Mein erster Gedanke war den Bereich auf „A1:M1000“ zu setzen. Da gibt mir die Formel aber immer „#NV“ aus, da ich zu viele befüllte Zellen in dem Bereich habe (das wäre mal zumindest meine Vermutung). Innerhalb des Summenproduktes die Spalte von A auf eine andere zu wechseln gibt immer den gleichen Fehler, dass die am Ende angehängte „-1“ außerhalb des erlaubten Bereiches 0-999 liegen würde. Über eine Antwort wie man nicht nur in Spalte A, sondern einer anderen Spalte (nur einer anderen Spalte) suchen kann, wäre ich äußerst dankbar. 🙂
Meine Wunschformel wäre: =INDEX(importrange(„xxx“;“xxx!O2:O1000″);SUMMENPRODUKT(MAX((importrange(„xxx“;“xxx!M2:M1000″)=A2)*ZEILE(M2:M1000)))-1)
An Stelle von „M2:M1000“ scheint auch ohne importrange nur „A2:A1000“ oder „A2:M1000“ möglich zu sein.
Beste Grüße
Daniel 🙂
Hallo Daniel,
wenn ich es also richtig verstehe, können in deinem Beispiel die Suchwerte in Spalte A oder in Spalte M enthalten sein. Ein mögliche Variante wäre, über die WENNFEHLER-Funktion beide Bereiche getrennt abzufragen. Bezogen auf meine Beispiel aus dem Artikel:
=WENNFEHLER(INDEX(C2:C31;SUMMENPRODUKT(MAX((A2:A31=E2)*ZEILE(A2:A31)))-1);INDEX(C2:C31;SUMMENPRODUKT(MAX((M2:M31=E2)*ZEILE(A2:A31)))-1))
Wenn der Wert nicht in Spalte A enthalten ist (also einen Fehler liefert), dann wird die Spalte M durchsucht. Vielleicht hilft das weiter.
Schöne Grüße,
Martin