Wie ein SVERWEIS von hinten: Den letzten Wert finden 10

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 🙂

 

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.



Schreibe einen Kommentar

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

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

  • 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

    • Martin Weiß Autor des Beitrags

      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

  • 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

    • Martin Weiß Autor des Beitrags

      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

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

  • 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

    • Martin Weiß Autor des Beitrags

      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

    • 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

      • Martin Weiß Autor des Beitrags

        Hallo Robert,

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

        Schöne Grüße,
        Martin