Wie ein SVERWEIS von hinten: Den letzten Wert finden 24

Artikelbild-229
SVERWEIS findet immer den ersten Wert. Aber wie findet man den letzten?
 

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

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

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

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 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

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

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

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

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

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 🙂

 

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 Hendrik Antworten abbrechen

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

24 Gedanken zu “Wie ein SVERWEIS von hinten: Den letzten Wert finden

  • Avatar-Foto
    Rainer

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Norbert Hoyer

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Max

    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 🙂

  • Avatar-Foto
    Timo

    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

    • Avatar-Foto
      Martin Weiß

      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

    • Avatar-Foto
      Robert Rethfeld

      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

      • Avatar-Foto
        Martin Weiß

        Hallo Robert,

        danke für das Feedback, freut mich, wenn der Artikel weitergeholfen hat.

        Schöne Grüße,
        Martin

    • Avatar-Foto
      Michael Eckstein

      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?

      • Avatar-Foto
        Martin Weiß

        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

  • Avatar-Foto
    Felix

    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?

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Lars Backhaus

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Scholly

    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?

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Juergen Rauch

    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

    • Avatar-Foto
      Martin Weiß

      Hallo Jürgen,

      um Zeilen automatisch auszublenden, reichen Formeln allein nicht aus, da musst du mit VBA rangehen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Christian Kulze

    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!

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Hendrik

    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

    • Avatar-Foto
      Martin Weiß

      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