Das Dreamteam aufgebohrt: INDEX + VERGLEICH mit mehreren Kriterien 15

SVERWEIS war gestern: INDEX + VERGLEICH bieten maximale Flexibilität
 

Dass die Kombination der beiden Funktionen INDEX und VERGLEICH einem SVERWEIS um Längen voraus ist, habe ich vor langer Zeit schon in diesem Artikel beschrieben.

Wenn du bisher um diese zwei Funktionen einen großen Bogen gemacht oder sie vielleicht noch gar nicht gekannt hast, empfehle ich dir, dich ein wenig mit ihnen zu beschäftigen. Es lohnt sich wirklich, denn zusammen sind sie fast unschlagbar!

Einen kniffeligen Anwendungsfall dafür zeige ich dir in den nächsten beiden Artikeln. Es geht um die folgende Aufgabe:

Für einen kleinen Report sollen die benötigten Daten aus einer großen Rohdatentabelle per Formel ausgelesen werden. Die Schwierigkeit dabei ist, dass die jeder auszulesende Wert vier verschiedene Kriterien erfüllen muss.

Das war ist dir noch zu abstrakt? Die folgenden Bilder machen es deutlicher.

Die Ausgangslage

Mein kleiner Report soll immer einen Auszug aus einer Rohdatentabelle liefern. Die Beispieldatei zu diesem Artikel kannst du dir hier herunterladen.

Jeder einzelne Wert hängt somit von folgenden Kriterien ab:

  • Produkt
  • Merkmal (Umsatz oder Gewinn)
  • Monat
  • Ist-Wert / Vorjahreswert / Plan-Wert
Die noch leere Reporting-Tabelle

Die noch leere Reporting-Tabelle

Die Rohdatentabelle hat dabei folgenden Aufbau: Jedes Produkt wird in einer Zeile dargestellt. Von links nach rechts kommen dann 12-Monatsblöcke mit den Ist-Werten, den Vorjahreswerten und den Plan-Werten. Im Bereich der Spalten B bis AK werden die Umsätze dargestellt, in den Spalten AL bis BU schließen sich nach dem gleichen Schema die Gewinne an.

Die Rohdatentabelle, Teil 1

Die Rohdatentabelle, Teil 1

Die Rohdatentabelle, Teil 2

Die Rohdatentabelle, Teil 2

Insgesamt also eine seeehr breite Tabelle, aus der wir die Daten auslesen wollen.

Du könntest vielleicht auf die Idee kommen, das Problem mit einer Pivot-Tabelle zu lösen (kleine Werbeunterbrechung für mein Buch: Pivot-Tabellen für Dummies). Aufgrund des Aufbaus meiner Beispieltabelle ist das aber keine Option, denn dabei handelt es sich ja bereits um eine Kreuztabelle.

Darum möchte ich dir eine Formel-Lösung mit den eingangs erwähnten Funktionen INDEX und VERGLEICH zeigen.

Funktionsweise von INDEX und VERGLEICH

Zunächst schauen wir uns die “normale” Funktionsweise dieser beiden Tabellenfunktionen an, bevor es dann ans Eingemachte geht.

=INDEX(Matrix;Zeile;[Spalte])

Damit wird ein Wert aus einer Tabelle (= erstes Argument) zurückgeliefert, dessen Position durch die beiden folgenden Argumente Zeile und/oder Spalte bestimmt wird.

So liefert beispielsweise

=INDEX(A1:A20;5)

den Wert in der fünften Zeile im Bereich A1:A20 zurück. Also den Wert aus Zelle A5.

=INDEX(A1:G20);3;5)

liest den Wert am Schnittpunkt in der dritten Zeile und der fünften Spalte in A1:G20, also den Inhalt von Zelle E3.

=VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp])

Mit dieser Funktion wird in einer Tabelle (“Suchmatrix”) nach dem im ersten Argument “Suchkriterium” angegebenen Wert durchsucht. Über den Vergleichstyp wird festgelegt, auf welche Weise dieses Suchkriterium mit der Suchmatrix abgeglichen wird. Dafür können die Werte 1, 0 oder -1 angegeben werden. In den allermeisten Fällen wird man hier 0 (den Wert Null) verwenden, der nach einer exakten Übereinstimmung sucht.

Als Ergebnis wird jedoch nicht der Wert zurückgeliefert (denn der steht ja mit dem Suchkriterium bereits fest), sondern die Position dieses Wertes in der angegebenen Suchmatrix:

Die VERGLEICH-Funktion

Die VERGLEICH-Funktion

Die Mächtigkeit dieser auf den ersten Blick unscheinbaren Funktionen ergibt sich aus ihrer Kombination. Die INDEX-Funktion benötigt für die Zeilen- und Spaltenangabe immer einen numerischen Wert. Dieser Wert ist im Normalfall jedoch nicht bekannt, sondern wir kennen ja lediglich ein Suchkriterium. Und damit kommt die VERGLEICH-Funktion ins Spiel, die zu diesem Kriterium den benötigten Positionswert zurückliefert.

Nach dieser kleinen Theorie geht’s jetzt an die Praxis.

Das Dreamteam INDEX und VERGLEICH

Beginnen wir ganz einfach. In unserer Reporting-Tabelle wollen wir zunächst nur die Ist-Umsätze für die beiden Kriterien “Produkt” und “Monat” berücksichtigen und damit die Rohdatentabelle durchsuchen. Alles andere interessiert uns vorerst noch nicht.

Bezogen auf meine Beispieltabelle lautet die Formel hierfür:

=INDEX($B$23:$BU$37;VERGLEICH($B$1;$A$23:$A$37;0);VERGLEICH($A5;$B$20:$BU$20;0))

INDEX und VERGLEICH kombiniert

INDEX und VERGLEICH kombiniert

Das erste Argument in der INDEX-Funktion ist der Wertebereich in meiner Rohdatentabelle, also B23:BU37, denn hier befindet sich ja irgendwo mein gesuchtes Ergebnis (ich habe gleich den kompletten Bereich bis BU verwendet, auch wenn uns zunächst die Spalte M gereicht hätte). Wie man im Bild erkennen kann, wird zur Bestimmung des Zeilen- und Spaltenindex jeweils eine VERGLEICH-Funktion verwendet.

Die erste VERGLEICH-Funktion sucht nach dem Produkt, das ich in Zelle B1 ausgewählt habe. Dabei wird die Produktliste A23:A37 durchsucht und die Position der Trefferzelle innerhalb dieser Liste angegeben. Das dritte Argument 0 (Null) sorgt dafür, dass nur ein exakter Treffer geliefert wird. Da sich das Produkt 1 an der ersten Stelle von A23:A37 befindet, wird hier der Wert 1 zurückgegeben.

Die zweite VERGLEICH-Funktion durchsucht die Monatsspalten B20:BU20 nach dem Monat, der sich in Zelle A5 befindet. Wie du hier siehst, kann man mit dieser Funktion sowohl Zeilen als auch Spalten durchsuchen. Da sich der Monat “Jan” im angegebenen Spaltenbereich ebenfalls an erster Stelle befindet, liefert auch diese Funktion als Ergebnis 1 zurück.

Damit hat INDEX alles was es braucht, um den gewünschten Umsatz zu finden:

=INDEX($B$23:$BU$37;1;1)

Wenn du jetzt auch die Dollar-Zeichen genau wie von mir angegeben gesetzt hast, kannst Du die Formel nach unten kopieren und bekommst für jeden Monat den entsprechenden Wert. Nun kannst du die Formel testen, indem du in B1 ein anderes Produkt auswählst.

Das Dreamteam im Einsatz

Das Dreamteam im Einsatz

Wie bekommt man es jetzt aber hin, die entsprechenden Vorjahres- und Planwerte zu bestimmen? Eine Variante wäre, jeweils den Bereich einzupassen, der als erstes Argument in der INDEX-Funktion verwendet wird. Also für die Ist-Werte beispielsweise:

=INDEX($N$23:$Y$37;VERGLEICH($B$1;$A$23:$A$37;0);VERGLEICH($A5;$B$20:$BU$20;0))

Die beiden VERGLEICH-Funktionen könnten bei unserem Tabellenaufbau unverändert bleiben und wir würden damit das gewünschte Ergebnis erreichen:

Variante mit angepasstem Wertebereich

Variante mit angepasstem Wertebereich

Aber das wäre etwas lästig, denn dann muss für die Planwerte erneut der Suchbereich angepasst werden. Und wenn wir im nächsten Schritt auch noch über das Feld B2 zwischen Umsatz und Gewinn unterscheiden wollen, würden wir spätestens dann an die Grenzen stoßen.

Wie man dieses Problem löst, zeige ich Dir im nächsten Artikel. Dort werden wir die Verschachtelung der beiden Funktionen eine Stufe weitertreiben. Habe also noch etwas Geduld!

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

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.



Schreibe einen Kommentar

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

15 Gedanken zu “Das Dreamteam aufgebohrt: INDEX + VERGLEICH mit mehreren Kriterien

  • Hans-Georg Müller

    Hallo Martin,
    wie immer Klasse, sehr schön nachvollziehbar und verständlich erklärt. Ich freue mich auf die Folgeartikel.

    • Martin Weiß Autor des Beitrags

      Hallo Hans-Georg,

      vielen Dank! Auf den Folgeartikel kannst Du durchaus gespannt sein, der wird nochmal eins oben drauf setzen 🙂

      Schöne Grüße,
      Martin

  • Tim

    Ganz fieser Cliffhanger! 😀
    Danke für den Artikel. So langsam wird INDEX und VERGLEICH mein geliebtes S- und WVERWEIS ablösen.

    Sehe ich das richtig, dass VERGLEICH nur mit einer einspaltigen oder einzeiligen Matrix arbeiten kann? Es wundert mich, dass es keine direkte Funktion gibt, welche gleich Spalte als auch Zeile ausspuckt.

    Schöne Grüße

    • Martin Weiß Autor des Beitrags

      Hallo Tim,

      Cliffhanger klingt gut, fast wie ein Excel-Thriller 🙂

      Ja, die VERGLEICH-Funktion kann man nur auf einspaltige oder einzeilige Bereiche anwenden. Eine kombinierte Funktion wäre in der Tat eine interessante Idee. Vielleicht kommt ja so etwas mal mit Excel 2025…

      Schöne Grüße,
      Martin

      • Robert

        Hallo Martin,

        ich arbeite derzeit auch an einer Auswertung einer sehr großen Rohdaten-Tabelle mittels einer INDEX-VERGLEICH Kombination. Diese hat in den Kopfzeilen 2 Kriterien und mindestens 5 Kriterien in den Anfangsspalten. Zu der Frage, ob VERGLEICH nur einspaltig/-zeilig funktioniert (in der Hoffnung sie richtig verstanden zu haben), glaube ich , dass es auch mit mehreren geht. Ich habe, um die eindeutige Position in meinen Kopfzeilen bei 2 Kriterien zu bestimmen, im hinteren Teil der INDEX-Funktion (SPALTE, wofür ich VERGLEICH verwende) einfach das Kaufmanns-“UND” verwendet (&). Z.B. so: VERGLEICH(A1&A2;Rohdaten!$1:$1&Rohdaten!$2:$2;0).

        Viele Grüße
        Robert

        • Martin Weiß Autor des Beitrags

          Hallo Robert,

          vielen Dank für den Hinweis. In der beschriebenen Form hast Du natürlich Recht, zwei oder mehr Zellen lassen sich über das Und-Zeichen verknüpfen und damit mehrere Kriterien kombinieren. Da habe ich mich tatsächlich etwas unklar ausgedrückt. Ich wollte in meinem vorhergehenden Kommentar nur sagen, dass man nicht einen echten mehrspaltigen Tabellenbereich durchsuchen kann. Denn durch die den Einsatz des Und-Zeichens die betreffenden Zeilen/Spalten ja faktisch immer zu einer einzigen zusammengefasst.

          Schöne Grüße,
          Martin

  • Daniel Rasper

    Hallo Martin,
    verfasst du auch Artikel über Power Pivot? Das ist für mich noch ein neues aber sehr spannendes Thema.

    Viele Grüße Daniel

    • Martin Weiß Autor des Beitrags

      Hallo Daniel,

      zu Power Pivot kann ich leider nichts schreiben, da es in meiner Excel-Version nicht enthalten ist und ich daher keine Erfahrung damit habe. Leider ist die Lizenzpolitik von Microsoft an dieser Stelle etwas schwer nachvollziehbar…

      Schöne Grüße,
      Martin

  • Matthias M.

    …als mehr oder minder regelmäßiger Leser des Blogs konnte ich den Cliffhanger emotional nicht aushalten, und habe mich an einer Lösung versucht. Und siehe da, es ist mir gelungen, eine kopierbare Formel zu basteln, die aus der exorbitant breiten Tapete, die gewünschten Zahlen in der vorgegebenen Matrix darstellt. Dem geschätzten Betreiber dieses Blogs werde ich meinen Lösungsansatz per Mail zukommen lassen, um an dieser Stelle nicht zu spoilern. Trotzdem bin ich auf den Ausgang des Films höchst gespannt…

    • Martin Weiß Autor des Beitrags

      Hallo Matthias,

      hahaha, der Artikel entwickelt sich offensichtlich zum echten Krimi 🙂
      Meine Auflösung (natürlich wird der Mörder überführt) gibt es morgen.

      Schöne Grüße,
      Martin

  • Jakob

    Moin,
    super Anleitung und gute Sache, macht es sehr einfach.
    Ich habe die Formel auf einen ähnlichen Fall übertragen.
    Hier sieht es aber so aus.
    1. Zeile: MonatJahr, MonatJahr, MonatJahr, MonatJahr
    2. Zeile: act,act,ytd,ytd
    3. Zeile: Plan, Ist, Plan, Ist

    Die Formel klappt in meiner ersten Eingabe. Wenn ich sie aber in die nächste Zeile kopiere, schmeisst die Formel leider einen Bezugsfehler?

    VG
    Jakob

    • Martin Weiß Autor des Beitrags

      Hallo Jakob,

      das ist aus der Ferne natürlich schwer zu sagen. Aber wenn’s in der ersten Zeile geklappt hat, vermute ich mal, dass nur irgendwo die Bezüge nicht richtig gesetzt sind ($-Zeichen).

      Schöne Grüße,
      Martin

  • PHM

    Hi,
    alles super verständlich erklärt.

    Gibt es schon den nächstfolgenden Artikel.
    Habe nämlich ein Problem mit Tabellendaten auslesen.
    Aus Tabelle 1 (Datenerfassungstabelle) gibt es Merkmal1 mehrmals, Merkmal 2 nur einmal. Nun soll alles in einer Zeile zusammengefasst werden.

    Hoffe der nächste Artikel kann mir helfen.

    Gruß PHM