Den letzten Wert einer Liste finden 14

Artikelbild 144
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.
 

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

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

14 Gedanken zu “Den letzten Wert einer Liste finden

    • Avatar-Foto
      Martin Weiß

      Hallo Gerhard,

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

      Schöne Grüße,
      Martin

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Duong

    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?

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Markus

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Markus

        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

        • Avatar-Foto
          Markus

          Hallo Martin, habe den Fehler entdeckt. Es lag an meiner Zeilennummerierung.

          Jetzt klappt es.

          Danke nochmals

          Markus

  • Avatar-Foto
    Daniel

    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 🙂

    • Avatar-Foto
      Martin Weiß

      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