Den letzten Wert einer Liste finden 6

Wenn der SVERWEIS mal versagt, kann SUMMENPRODUKT vielleicht weiterhelfen
 

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

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

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

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

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

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

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

Die fertige Formel

Fertig ist unsere tolle Berechnung. Und wieder einmal zeigt sich, wie mächtig und vielseitig die SUMMENPRODUKT-Funktion ist.
 

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.



Kommentar erstellen

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

6 Gedanken zu “Den letzten Wert einer Liste finden

    • Martin Weiß Beitragsautor

      Hallo Gerhard,

      danke für deinen Kommentar und es freut mich, wenn die Lösung gefällt.

      Schöne Grüße,
      Martin

  • Martin Winkler

    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

    • Martin Weiß Beitragsautor

      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

  • Luschi

    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

    • Martin Weiß Beitragsautor

      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