Den XVERWEIS ausreizen 15

Der XVERWEIS an sich ist schon eine Bereicherung. Aber da geht noch deutlich mehr!
 

Wer Excel aus Microsoft 365/Office 365 oder das brandneue Excel 2021 einsetzt, hat vermutlich schon Bekanntschaft mit der XVERWEIS-Funktion gemacht oder zumindest davon gehört. Es lohnt sich unbedingt, sich mit dem Nachfolger des berühmt-berüchtigten SVERWEIS zu beschäftigen, bringt er doch wesentliche Verbesserungen mit sich.

Darüber hatte ich auch vor längerer Zeit schon im Artikel Goodbye SVERWEIS, willkommen XVERWEIS! geschrieben.

Wenn du also schon mit XVERWEIS gearbeitet hast, dann zeige ich dir heute mit ein paar Spezialfällen, wie man noch viel mehr herausholen kann. Oder du kommst auf den Geschmack, dich endlich mit dieser tollen Funktion zu beschäftigen und den SVERWEIS endgültig zu begraben.

Und so geht’s:

Wichtiger Hinweis zu Beginn:
Falls du noch Excel 2019 oder älter einsetzt, steht dir diese Funktion leider nicht zur Verfügung. Weiterlesen darfst du natürlich trotzdem 😉

Spezialfall 1: XVERWEIS in zwei Richtungen

Ich habe eine klein Umsatztabelle vorbereitet, die für sechs Verkäufe die Umsätze für die Monate Januar bis Juni enthält. Nun möchte ich über zwei Eingabefelder einen Monat und einen Verkäufer festlegen und mir den dazu passenden Umsatz ausgeben lassen (die Beispieldatei kannst du dir hier herunterladen):

Die Ausgangslage: Umsatztabelle nach Verkäufer und Monat

Die Ausgangslage: Umsatztabelle nach Verkäufer und Monat


Die Herausforderung ist also, dass wir die Tabelle sowohl vertikal (nach dem Verkäufer) als auch horizontal (nach dem Monat) durchsuchen müssen und somit zwei Suchkriterien haben.

Der normale XVERWEIS mit nur einem Suchkriterium funktioniert so:
=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn nicht gefunden]; [Vergleichsmodus]; [Suchmodus])
Die letzten drei Parameter sind optional und für unsere Zwecke nicht erforderlich.

Würde ich nur nach einem Verkäuferumsatz für den, sagen wir mal, März suchen, wäre die Formel bezogen auf mein Beispiel:
=XVERWEIS(E13;A4:A9;D4:D9)

Zuerst ein Standardfall für XVWEIS

Zuerst ein Standardfall für XVWEIS

Wie schafft man es nun, dass die Suchmatrix variabel ist?

Ganz einfach: Über einen zweiten XVERWEIS im XVERWEIS!

Spezialfall 1: Suche mit 2 Kriterien

Spezialfall 1: Suche mit 2 Kriterien


=XVERWEIS(B12;A4:A9;XVERWEIS(B11;B3:G3;B4:G9))
Anstatt für den dritten Parameter (Suchmatrix) einen festen Zellenbereich anzugeben, setzten wir gleich einen XVERWEIS ein. Damit werden die Monatsüberschriften nach dem gewählten Monat durchsucht. Die Besonderheit hier ist, dass der Rückgabebereich nicht eine einzelne Zeile ist, sondern ein ganzer Zeilenbereich (B4:G9).

Spezialfall 2: Dynamische Summenspalten über alle Zeilen

Im Normalfall gibt XVERWEIS einen einzelnen Wert zurück, so wie auch im vorhergehenden Beispiel. In Kombination mit der SUMME-Funktion lässt sich damit aber auch ganze Zellenbereiche zurückliefern.

In diesem Beispiel möchte ich die Summe der Umsätze aller Verkäufer zwischen zwei frei wählbaren Monaten ausgeben:

Spezialfall 2: Dynamische Summe

Spezialfall 2: Dynamische Summe

Auch das ist kein Problem. Benötigt werden wieder zwei XVERWEIS-Funktionen, die innerhalb der SUMME-Funktion zum Einsatz kommen und dort den „von..bis“-Bereich bestimmen:

Spezialfall 2: Lösung

Spezialfall 2: Lösung


=SUMME(XVERWEIS(B16;B3:G3;B4:G9):XVERWEIS(B17;B3:G3;B4:G9))

Der erste XVERWEIS liefert sozusagen die Von-Spalte, der zweite XVERWEIS die Bis-Spalte, getrennt durch einen Doppelpunkt. Das Geniale ist, die Monate müssen in den beiden Eingabezellen nicht einmal in der richtigen Reihenfolge angegeben werden. Von Februar bis April liefert das gleiche Ergebnis wie von April bis Februar!

Spezialfall 3: Dynamische Summenspalten über eine Zeile

Setzen wir gleich noch eins drauf. Zu den frei wählbaren Anfangs- und Endmonaten möchte ich jetzt auch noch einen bestimmten Verkäufer angeben:

Spezialfall 3: Summe über einen Zeitraum, variabler Verkäufer

Spezialfall 3: Summe über einen Zeitraum, variabler Verkäufer


Wir haben also einen dynamischen Spaltenbereich kombiniert mit einer dynamischen Zeile.

Die Formel wird schon etwas umfangreicher und sieht jetzt so aus:

Spezialfall 3: Lösung

Spezialfall 3: Lösung


=SUMME(XVERWEIS(B21;B3:G3;XVERWEIS(B23;A4:A9;B4:G9)):XVERWEIS(B22;B3:G3;XVERWEIS(B23;A4:A9;B4:G9)))

Das heißt, die beiden Von..Bis-Bereiche innerhalb der SUMME-Funktion werden jeweils durch verschachtelte XVERWEIS-Funktionen dynamisiert. Jeder dieser verschachtelten XVERWEISE ist im Grunde identisch mit dem Beispiel aus Spezialfall 1. Nur dass hier eben zwei davon zum Einsatz kommen.

Spezialfall 4: Dynamische Summenspalten und dynamische Zeilen

Um dem ganzen die Krone aufzusetzen, soll jetzt zum Monatsbereich auch noch ein Verkäufer-Bereich angegeben werden.

Spezialfall 4: Dynamische Summen über zwei Richtungen

Spezialfall 4: Dynamische Summen über zwei Richtungen

Du ahnst es vermutlich schon und musst jetzt ganz tapfer sein. Die Formel wird nämlich ein ziemliches Monster:

Spezialfall 4: Lösung

Spezialfall 4: Lösung


=SUMME((XVERWEIS(B27;B3:G3;XVERWEIS(B29;A4:A9;B4:G9)):XVERWEIS(B28;B3:G3;XVERWEIS(B29;A4:A9;B4:G9))):(XVERWEIS(B27;B3:G3;XVERWEIS(B30;A4:A9;B4:G9)):XVERWEIS(B28;B3:G3;XVERWEIS(B30;A4:A9;B4:G9))))
Denn jetzt haben wir es mit zwei jeweils zweimal verschachtelten XVERWEIS-Funktionen innerhalb der SUMME-Funktion zu tun. Nur dadurch erreicht man eine Dynamisierung in beide Richtungen.

Ich gebe zu, dass diese Formel nicht ganz einfach und auch nicht für den täglichen Gebrauch geeignet ist. Aber wir besprechen hier schließlich Spezialfälle und da ist es doch irgendwie beruhigend zu wissen, dass es ginge, falls man es bräuchte 🙂

Welche interessanten Anwendungsmöglichkeiten für den XVERWEIS fallen dir noch ein?
Lass es uns in den Kommentaren wissen!

 

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.

15 Gedanken zu “Den XVERWEIS ausreizen

  • Marcel

    Und wo ist jetzt genau der Vorteil im Vergleich zu Index (in der Bezugsvariante) und Summenprodukt (für Angabe mehrerer Kriterien)?

    • Martin Weiß Autor des Beitrags

      Hallo Marcel,

      ich gebe dir recht, es gibt hier nicht zwingend einen Vorteil. Es hängt von den tatsächlichen Gegebenheiten und Vorlieben ab und ist lediglich eine alternative Möglichkeit.

      Schöne Grüße,
      Martin

  • John

    Hallo Martin,

    super Danke! Bei mir funktionieren auch die nachfolgenden Formeln. Diese sind jedoch etwas kürzer, deshalb schreibe ich diese mal hier rein. Gerne testen und bei Fehlfunktionen melden.

    Fall 3:
    =SUMME(XVERWEIS(B23;A4:A9;XVERWEIS(B21;$B$3:$G$3;B4:G9):XVERWEIS(B22;B3:G3;B4:G9)))

    Fall 4:
    =SUMME((XVERWEIS(B27;$B$3:$G$3;XVERWEIS(B29;A4:A9;B4:G9)):XVERWEIS(B28;B3:G3;XVERWEIS(B30;A4:A9;B4:G9))))

    VG
    John

    • Martin Weiß Autor des Beitrags

      Hallo John,

      vielen Dank, das sind sehr elegante Lösungen! Vor allem der Fall 4 ist deutlich kürzer und damit besser als meine Variante.

      Schöne Grüße,
      Martin

  • Martin Röllin-Kälin

    Hallo Martin
    Ich freue mich immer, wenn ich von dir das News-Mail erhalte!
    Mit X-VERWEIS habe ich folgendes Problem: in der Suchmatrix habe ich teilweise mehr als ein Kriterium, das dem Suchkriterium entspricht. Da wird dann nur das Resultat vom ersten zurückgeliefert. Gäbe es eine Kombination mit einer Array-Formel oder sonst eine Lösung, damit alle Übereinstimmungen zurückgeliefert würden?
    Danke für’s Studieren, und herzliche Grüsse aus der Schweiz
    Martin

  • Gerhard Duscha

    Lieber Martin,
    Du erweiterst mein Denken bei jedem Newletter. Bisher habe ich (aus Bequemlichkeit) Summewenn dem einfachen XVERWEIS vorgezogen.
    Aber jetzt…
    Danke für die Info

    • Martin Weiß Autor des Beitrags

      Hallo Gerhard,

      gerne, freut mich zu hören! Auch ich lerne immer wieder dazu 🙂

      Schöne Grüße,
      Martin

  • Rainer Linkenbach

    Ich habe eine Matrix mit Konten, und möchte herausfinden, ob ein bestimmtes Konto darin vorkommt.
    Also z. B. Spalten A-J; Zeilen 1-100
    Irgendwo in dieser Matrix kann das Konto 9999 vorhanden sein, oder auch nicht, das möchte ich wissen. Wie beim SVERWEIS würde mir reichen, wenn das Kriterium angezeigt wird oder #NV erscheint.
    Praktische Anwendung ist die Buchungsmatrix im Rahmen einer NAV Einführung. In der upload Datei will ich das eben prüfen.
    Viele Grüße
    Rainer

    • Martin Weiß Autor des Beitrags

      Hallo Rainer,

      das Problem wäre beispielsweise mit einer bedingten Formatierung zu lösen. Angenommen, die Kontenmatrix steht im Bereich A1:J100 und in Zelle L1 steht das gesuchte Konto.
      Dann markierst die diese Matrix und legst eine Formatierungsregel an:
      Menü „Start | Bedingte Formatierung | Neue Regel…“
      Als Regeltyp wählst Du „Formel zur Ermittlung der zu formatierenden Zellen verwenden“
      Und in das Eingeabefeld gibst du dann folgende Formel ein:
      =A1=$L$1
      (wenn deine Matrix eine andere Startzelle hat, dann musst du diese anstatt von A1 eingeben)
      Dann legst du noch eine Formatierung fest, z.B. gelber Hintergrund.

      Abhängig vom Wert, den du in Zelle L1 einträgst, sollte jetzt dieser Wert in der Matrix gelb hervorgehoben werden – oder eben nicht.

      Schöne Grüße,
      Martin

  • Julius

    Ich habe letztens versucht eine Lösung zu finden für eine Tabelle mit zwei Überschriftszeilen (erste Zeile mit Jahr, zweite Zeile mit Monat – super unpraktisch, aber in meinem Job bin ich leider häufig mit extrem schlecht verarbeitbaren Daten konfrontiert).

    Ich habe jetzt extra eine vereinfachte und übersichtlichere Tabelle erstellt, um sie hier zu posten, aber ich kann leider keine Screenshots anfügen. Ich umreiße es kurz:

    B1 bis E1: Kriterium 1, bei mir „A A B B“, entspricht im Ursprünglichen den Jahreszahlen (12 mal 2021, 12 mal 2022)
    B2 bis E2: Kriterium 2, bei mir „AA AB BA BB“, entspricht im Ursprünglichen den Monaten (2mal Januar bis Dezember)
    A3 bis A5: Kriterium 3, bei mir „1 2 3″, entspricht im Ursprünglichen verschiedenen Kunden
    B3 bis E5: Daten, bei mir spaßeshalber verschiedene Obstsorten, entspricht im Ursprünglichen den jeweiligen Kundenumsätzen

    B8: Auswahlfenster für Kriterium 1, bei mir A oder B, im Ursprünglichen die Jahreszahl 2021 oder 2022
    B9: Auswahlfenster für Kriterium 2, bei mir AA, AB, BA und BB, im Ursprünglichen die Monate Januar bis Dezember
    B10: Kriterium 3, bei mir 1, 2 oder 3, im Ursprünglichen die Kundenliste

    Die Auswahlfenster sind natürlich noch schön mit Dropdowns, damit lässt sich viel angenehmer und intuitiver arbeiten 😉

    Nun der interessante Teil, die Verformelung: =XVERWEIS(B10;A3:A5;XVERWEIS(B9;WENN(B1:E1=B8;B2:E2;““);B3:E5))

    Der Wenn-Bereich WENN(B1:E1=B8;B2:E2;““) gibt als Matrix den Bereich B2 bis E2 aus und lässt dabei die Zellen leer, die nicht unter dem entsprechenden Kriterium A (Zelle B8 in Bereich B1 bis E1) stehen.

    Dann habe ich einen Xverweis davor gesetzt: XVERWEIS(B9;WENN(B1:E1=B8;B2:E2;““);B3:E5). Das Array aus der Wenn-Funktion wird nun nach Kriterium 2 durchsucht. Sobald er gefunden ist, wird aus der entsprechenden Spalte der Bereich von Zeile 3 bis 5 als Array ausgegeben.

    Zuletzt kommt ein weiterer Xverweis davor: XVERWEIS(B9;WENN(B1:E1=B8;B2:E2;““);B3:E5). Dieser durchsucht den Bereich A3 bis A5 nach Kriterium 3. Die entsprechende Zeileninformation wendet er dann auf das im zweiten Step erzeugte Array an, das ja bereits nur noch die Ergebnisspalte für die gleichzeitig geltenden Kriterien 1 und 2 darstellt.

    Auf diese Weise können mehrere verschiedene Kriterien geltend gemacht werden. Je nach Wunsch und Möglichkeit stelle ich gerne meine Beispieldatei zur Verfügung!

    Ergänzung von Martin:
    Hier gibt es die Beispieldatei zum Download.

    • Martin Weiß Autor des Beitrags

      Hallo Julius,

      danke für deine Lösung und die ausführliche Beschreibung dazu. Wenn du möchtest, kannst du mir deine Beispieldatei an info@tabellenexperte.de schicken und ich stelle sie dann hier in deinem Kommentar zum Download für andere Leser bereit.

      Schöne Grüße
      Martin