Jeder Excel-Anwender, der mit der SVERWEIS-Funktion vertraut ist, weiß, dass man damit immer das erste Ergebnis zu einem gesuchten Wert finden kann. Wenn jedoch das letzte Vorkommen gesucht wird, hilft der SVERWEIS nicht weiter. Hier muss man zu ein paar Tricks greifen.
Aber auch das ist kein großes Hexenwerk. In meiner hier vorgestellten Lösung werden wir ein paar andere Funktionen miteinander kombinieren und so das gewünschte Ergebnis erzielen.
Und so geht’s:
Die Ausgangslage
Für mein Beispiel habe ich eine fiktive Umsatzliste vorbereitet (du kannst die Datei bei Bedarf hier herunterladen). Sie besteht aus den Spalten Verkäufer, Datum und Umsatz. Da jeder Verkäufer an verschiedenen Tagen Umsätze getätigt hat, taucht er entsprechend oft in der Liste auf:
Die Beispielliste: Umsätze nach Verkäufer
Möchte man nun beispielsweise den ersten Umsatz zu einem Verkäufer bestimmen, würde ein einfacher SVERWEIS das gewünschte Ergebnis liefern. Denn bekanntermaßen liefert der SVERWEIS immer den ersten Treffer zu einem gesuchten Wert:
SVERWEIS liefert den ersten Eintrag
Was mache ich aber, wenn mich stattdessen der jeweils letzte Verkauf interessiert? Hier wird es schon etwas kniffeliger.
Vorbereitende Maßnahmen
Um den letzten Umsatz eines Verkäufers zu finden, muss ich erst einmal wissen, wie viele Einträge es überhaupt zu diesem Verkäufer gibt. Dabei hilft die ZÄHLENWENN-Funktion:
=ZÄHLENWENN($A$4:$A$63;A4)
Mit ZÄHLENWENN die Anzahl der Einträge je Verkäufer ermitteln
Damit werden in der Spalte mit den Verkäufern (A4:A63) alle Einträge gezählt, die dem Namen in Zelle A4 entsprechen. In meiner Tabelle sind das für den ersten Verkäufer 5 Stück. Wenn wir die Formel jetzt nach unten kopieren, wird für jeden Verkäufer das entsprechende Ergebnis geliefert.
Das Ergebnis ist noch nicht optimal
Das ist schon mal hilfreich, aber noch nicht ideal. Denn jetzt steht beispielsweise bei jedem Müller in Spalte E der Wert 5. Besser wäre es jedoch, wenn beim ersten Müller die 1, beim zweiten die 2 und so weiter stehen würde. Denn dann könnte man einfach nach dem Wert 5 suchen und hätte dann den letzten Eintrag für Verkäufer Müller.
Dazu müssen wir nur die Bezüge in der Formel ein wenig anpassen. Anstatt immer die ganze Tabelle zu durchsuchen, wird nur der Bereich vom Anfang bis zur jeweils aktuellen Zeile verwendet. Für die erste Zeile lautet die Formel so (achte dabei unbedingt darauf, dass beim zweiten A4 vor der 4 kein $-Zeichen gesetzt ist!):
=ZÄHLENWENN($A$4:$A4;A4)
Durch diese gemischten Bezüge bleibt beim Kopieren der Startbezug immer fix auf $A$4, der zweite Bezug ändert sich jedoch auf $A5, $A6, $A7 usw. Auf diese Weise werden die Namen immer nur vom Anfang bis zur jeweiligen aktuellen Zeile gezählt.
Nach dem Kopieren dieser Formel in alle anderen Zeilen sieht das Ergebnis so aus:
Gemischte Bezüge liefern die Position
Schon deutlich besser, denn nun steht in jeder Zeile eben die Position des jeweiligen Verkäufers. Wenn wir jetzt noch hinter die Zahl den Namen des Verkäufers setzen, haben wir in Spalte E alles, was wir brauchen. Dazu wird die Formel nochmal minimal erweitert:
=ZÄHLENWENN($A$4:$A4;A4)&A4
Position + Verkäufername kombinieren
Damit sind die Vorbereitungen abgeschlossen und wir können uns an die eigentliche Lösung machen, nämlich den jeweils letzten Eintrag zu finden.
Die Lösung
Trotz Hilfsspalte E können wir den SVERWEIS nicht zur Lösung verwenden, da sich die gewünschten Ergebnisse (Umsatz bzw. Datum) links von dieser Hilfsspalte befinden. Daher greifen wir einfach auf eine Kombination aus INDEX und VERGLEICH zurück.
(Wenn du noch nicht so sattelfest mit diesen beiden Funktionen bist“, empfehle ich dir diesen Artikel: Ein echtes Dream-Team: INDEX und VERGLEICH.
Und wenn du die Verweisfunktionen SVERWEIS, INDEX, VERGLEICH usw. noch genauer kennenlernen möchtest, wäre vielleicht mein E-Book SVERWEIS & Co. Verweis-Funktionen in Excel etwas für dich)
Und hier kommt die Lösung:
=INDEX($C$4:$C$63;VERGLEICH(ZÄHLENWENN($A$4:$A$63;G4)&G4;$E$4:$E$63;0))
Die Lösung: INDEX + VERGLEICH
Der erste Bezug in INDEX, $C$4:$C$63, sollte leicht nachvollziehbar sein. Wir wollen ja die Spalte mit den Umsätzen durchsuchen. Die Position wird über die VERGLEICH-Funktion bestimmt. Und hier verwenden wir für das erste Argument praktisch die gleiche ZÄHLENWENN-Funktion, die wir schon in unserer Hilfsspalte zum Einsatz gebracht haben:
VERGLEICH kombiniert mit ZÄHLENWENN
Im Beispiel im Screenshot wird gezählt, wie oft der Verkäufer Schneider in Spalte A vorkommt und hinter die Zahl wieder der Name gesetzt. Herr Schneider kommt in meiner Liste 5 mal vor, daher wird der Wert „5Schneider“ in der Hilfsspalte E gesucht und diese Position an die INDEX-Funktion übergeben. Und damit haben wir den letzten Umsatz für diesen Verkäufer.
Jetzt ist es eine Kleinigkeit, das dazu passende Datum zu bestimmen. Es muss in der Formel lediglich der erste Bezug in der INDEX-Funktion von Spalte C auf Spalte B geändert werden, alles andere bleibt gleich:
Eine kleine Anpassung liefert das passende Datum
Zugegeben:
Wenn du bisher noch nicht viel mit INDEX und VERGLEICH gearbeitet hast, sieht die Lösung auf den ersten Blick etwas verwirrend aus. Lass es einfach in Ruhe sacken, dann kommt die Erleuchtung bestimmt 🙂
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, Tabellen-Experte.
Vorweg: deine Tipps sind super – man lernt einfach nie aus.
Zu dieser Lösung: wäre es nicht viel einfacher, die Tabelle nach Datum/absteigend zu sortieren und dann mit dem guten, alten SVERWEIS zu suchen?
LG
Rainer
Hallo Rainer,
danke für das Lob!
Und du hast natürlich Recht, eine Sortierung wäre in jedem Fall die einfachere Variante. In manchen Szenarien ist eine Sortierung unter Umständen nicht möglich (geschützte Arbeitsblätter etc) oder gewünscht. Dann kann man auf die Formellösung zurückgreifen. Das Schöne: In Excel gibt es selten DIE eine richtig Lösung, viele Wege führen nach Rom 🙂
Schöne Grüße,
Martin
Vorweg: Ich bin kein Excel-Spezialist, wie ich sie immer in dieser Sparlte oder sonst im Intenet finde. Ich fummel mich so in Excel hinein. Wenn ich die Tipps lese, versuche ich sie nachzuvollziehen. Mancmal klappts, manchmal nicht.
Insgesamt sind die Tipps aber Klasse. Sie sind auch so augebaut, dass man sich hineinversetzten kann. Zu meiner Schane muss ich gestehen, dass ich diese Tipps heute ncihtz mehr so verwenden kann, wie ich es früher gerne gemacht hätte. In den Anfangszeiten gab es nch nicht diese theoretische Unterstützung. Und heute nutze ich diese Tipps aus Freude am Kennenlernen. (Ich kenne noch die Zeit, als man mit Starwriter gerechnet (!) hat).
LG Norbert
Hallo Norbert,
vielen Dank für das schöne Feedback.
Und auch ich stamme noch aus der Zeit von Starwriter & Co. 🙂
Schöne Grüße,
Martin
Klasse Sache, gerade das die Problematik mal andersrum betrachtet wird. Als Vorschlag ohne Hilfsspalte in einer Matrix-Formel (bezieht sich auf deine Beispieldatei):
{=SUMMEWENNS(C4:C63;A4:A63;G4;B4:B63;MAX(WENN(A4:A63=G4;B4:B63;““)))}
Viele Grüße und weiter so 🙂
Hallo Max,
vielen Dank für diese tolle Lösung, ich bin begeistert!
Schöne Grüße,
Martin
Hallo Tabellen-Experte!
Ich bin zwar etwas spät in dieser Diskussion, aber ich bin erst heute auf diesen Blog gestoßen. Meinen Lösungsansatz zu diesem Problem möchte ich trotzdem noch gerne teilen.
Statt Hilfsspalten und Matrixformeln (die bei größeren Tabellen gerne mal Performance rauben) kann auch die „Verweis-Methode“ genommen werden. Die zugehörige Formel dazu (für Zelle H4 im Beispiel):
=VERWEIS(2;1/($A$4:$A$63=$G$4);$C$4:$C$63)
oder allgemein ausgedrück
=VERWEIS(2;1/(Schlüssel_Suchbereich=Suchwert);Wertbereich)
Sieht kryptisch aus? Mag sein, funktioniert aber wunderbar. Was passiert hier?
Die Division 1/(Schlüssel-Suchbereich=Suchwert) wird gegen Wahrheitswerte Wahr = 1 und Falsh = 0 durchgeführt und ergibt somit nur bei passenden Schlüsseln für die entsprechende Zeile im Wertbereich ein valides numerisches Ergebnis. Die 2 am Anfang des Verweises ist der Suchwert, gegen den die Ergebnisse abgeglichen werden sollen. Hier ist wichtig zu wissen, dass die Verweisfunktion immer den nächstkleineren Wert zurückgibt, wenn der entsprechende Suchwert nicht gefunden wird. Da wir in unserer Suchmatrix 1/(Schlüssel-Suchbereich=Suchwert) entweder nur 1 oder #DIV/0! zeigen, wird somit immer auf die 1 referenziert, hierbei geht die Verweis-Formel alle Ergebnisse von unten nach oben durch – et voilá: der letzte passende Schlüssel wird zurückgegeben.
Viel Spaß mit der Lösung (ich hoffe sie ist verständlich geworden) und danke für diesen Blog, ich werde wieder vorbeischauen.
Beste Grüße,
Timo
Hallo Timo,
ich bin begeistert! Die VERWEIS-Funktion habe ich schon ewig nicht mehr benutzt und die Lösung mit der Division und der Suche nach 2 ist schon wirklich sehr clever!
Vielen Dank, dass du uns daran teilhaben lässt.
Schöne Grüße,
Martin
Hallo Timo,
vielen Dank, Dein Tipp ist Gold wert. Jetzt kann ich nicht nur den letzten Wert, sondern auch das letzte Datum mit Hilfe einer Textkette und den entsprechenden Formeln in meine Charts schreiben.
Alles gute und lieben Dank
Robert
Hallo Robert,
danke für das Feedback, freut mich, wenn der Artikel weitergeholfen hat.
Schöne Grüße,
Martin
Hallo,
bin gerade über diesen Beitrag gestolpert und frage mich, ob da noch mehr geht! 😉
Kann der Verweis, wie im Beispiel auch bei zwei oder mehr Suchkriterien eingesetzt werden?
in meiner Liste wird aktuell nach Datum (Spalte A) sortiert und der letzte Eintrag des Materials (Spalte B) gesucht, und so der letzte Einkaufspreis gefunden.
Was aber, wenn ich den letzten Wert eines bestimmten Datums oder Datumsbereiches (Kalenderjahr) finden möchte?
Gibt’s da Möglichkeiten, diese Formel zu erweitern?
Hallo Michael,
ja, grundsätzlich kann man so etwas auch für mehrere Kriterien umsetzen. Statt ZÄHLENWENN musst du dann ZÄHLENWENNS verwenden, hier kann man mehrere Kriterien angeben.
Schöne Grüße,
Martin
Hallo Experte,
was mache ich denn, wenn ich in der Ausgangstabelle in Spalte D statt der Anzahl der Einträge pro Verkäufer
einen festen Begriff einfügen möchte.
Also Beispielsweise soll bei Verkäufer „Mueller“ in der Zeile D die zugehöriger Jobbezeichnung „Manager“ erscheinen
und das soll für jede Zeile in der der Verkäufer Mueller steht geschehen. Usw mit den weiteren Verkäufern und ihren
Bezeichnungen.
Ist das möglich?
Hallo Felix,
wenn ich dich jetzt nicht völlig missverstanden habe, wäre das mit einer einfache SVERWEIS-Funktion zu lösen. Du brauchst irgendwo eine Referenztabelle mit der Zuordnung von Jobbezeichnung zu Verkäufer. Und in Spalte D kommt der SVERWEIS zum Einsatz und sucht nach dem Verkäufer in der Referenztabelle die dazu passende Jobbezeichnung.
Schöne Grüße,
Martin
Hallo,
Die Grundlegende Systematik des S- wie auch des W- und X-Verweises sind mir bekannt. Mein „Problem“ dreht sich eher um die Dynamisierung des Bereich-Parameters:
Eine Tabelle soll nach einem Wert durchsucht werden. Dies immer vom Anfang der Tabelle bis zur über der aktuell liegenden Zeile. Da die Zeile auch durchaus händisch an andere Stelle kopiert wird, bin ich scharf auf eine komplett dynamische Formel.
Leider scheint es nicht möglich, den Bereich dynamisch via =$A$1:Adresse(Zeile(Zeile()-1);Spalte(Spalte()-1)) zu dynamisieren.
Ich bin für jeglichen Tipp (und sei es nur der richtige Begriff zum danach suchen) dankbar.
Freundliche Grüße und allseits gute Gesundheit
Hallo Lars,
das sollte eigentlich schon über ganz normale gemischte Bezüge funktionieren. Du musst nur die Dollarzeichen richtig setzen. Bezogen auf dein Beispiel: Angenommen, die aktive Zelle steht in C20, dann lautet der dynamische Bereich:
=$A$1:B19
Wenn diese Formel kopiert wird, passt sich der zweite Teil des Bezugs immer an.
Oder habe ich etwas missverstanden?
Schöne Grüße,
Martin
Hallo,
erstmal danke für den tollen Lösungsweg 🙂
Mir stellt sich nun die Frage, ob es auch möglich ist, die letzten 2 oder 3 Umsätze einer Person anzeigen zu lassen?
Hallo Scholly,
ja, das geht. Wenn der letzte Umsatz so gefunden wird…
=INDEX($C$4:$C$63;VERGLEICH(ZÄHLENWENN($A$4:$A$63;G4)&G4;$E$4:$E$63;0))
…dann wäre der vorletzte…
=INDEX($C$4:$C$63;VERGLEICH(ZÄHLENWENN($A$4:$A$63;G4)-1&G4;$E$4:$E$63;0))
…und der drittletzte…
=INDEX($C$4:$C$63;VERGLEICH(ZÄHLENWENN($A$4:$A$63;G4)-2&G4;$E$4:$E$63;0))
Vorausgesetzt natürlich, es gibt überhaupt drei Umsätze.
Schöne Grüße,
Martin
Hallo Martin, ich bin über mein „Problem auf Deinen Blog gekommen. Vielen Dank für die vielen hilfreichen Tipps incl. verständlicher Erklärung. Hat mir sehr geholfen Weiter so! Leider habe ich noch keine Lösung für mein eigentliches Problem
eine Knifflige Frage meinerseits:
Tabelle D1:G…. Wenn in der Spalte G der Wert „JA“ steht, soll im Bezug zur gleichen Zeile „X“ aus Spalte D der zugehörige Wert ermittelt werden (im Beispiel Koordinate „X“/D). Der Wert „X“/D ist in der Spalte D mehrfach vorhanden. Die dem zugehörigen Wert „X“/D basierenden Spalten sollen ausgeblendet werden
Ausgang Schlüssel daten1 daten2 Bezug
1000 Vorname1 Nachname1 nein
1000 Vorname1 Nachname1 nein
2000 Vorname2 Nachname2 nein
1000 Vorname1 Nachname1 nein
2000 Vorname2 Nachname2 nein
3000 Vorname3 Nachname3 nein
1000 Vorname1 Nachname1 ja
Alle Zeilen zu Schlüssel 1000 solle ausgeblendet werden (Weil G8 auf „ja“steht)
Ziel Schlüssel daten1 daten2 Bezug
2000 Vorname2 Nachname2 nein
2000 Vorname2 Nachname2 nein
3000 Vorname3 Nachname3 nein
Hallo Jürgen,
um Zeilen automatisch auszublenden, reichen Formeln allein nicht aus, da musst du mit VBA rangehen.
Schöne Grüße,
Martin
Hallo Martin,
Eine sehr gut gemachte Seite!
Ich habe ein VERGLEICH Problem! In mehreren verbundenen Zellen fasse ich dann wieder mehrere Spalten zusammen. Ich will also erst die jeweilige übergeordnete verbundenen Zelle suchen und dann in diesem Bereich noch die entsprechende Spalte.
Mit der jeweiligen Zeile gibt es kein Problem. Jedoch die verschachtelten Spalten …
Leider ist da mein Latein am Ende.
Kannst Du helfen?
Vielen Dank!
Hallo Christian,
tut mir leid, ich fürchte, da habe ich jetzt auch keine konkrete Lösung für dich. Wenn du mehrere Spalten zu einem Wert zusammenfasst, dann verwendest du vermutlich irgendwelche Trennzeichen. Vielleicht lässt sich über die Position dieser Trennzeichen die betreffende Spalte ermitteln, aber das ist auch nur so ein Gedanke.
Schöne Grüße,
Martin
Hallo Martin,
danke für Deinen Vorschlag.
Geht das nicht einfacher mit der Funktion „xVergleich“ und „Index“. Bei „xVergeleich“ kann man beim vierten Argument „-1“ angeben und bekommt den letzten Eintrag.
Am schönsten wäre es natürlich, wenn es diese Möglichkeit auch direkt bei „sVerweis“ geben würde.
Viele Grüße,
Hendrik
Hallo Hendrik,
das stimmt, mittlerweile gibt es natürlich andere Optionen. Zu dem Zeitpunkt, als der Artikel veröffentlicht wurde, gab es XVERGLEICH & Co. jedoch noch nicht.
Schöne Grüße,
Martin