Mehrfach Konditionaler SVERWEIS: Zwei Spalten auf einmal Nachschauen

Vor ein paar Wochen habe ich bei Chandoo über einen Trick gelesen den ich noch nicht kannte aber ziemlich interessant fand.

Normalerweise wenn wir in einer Tabelle einen Wert suchen benutzen wir den SVERWEIS. Wenn jedoch der gesuchte Wert auf zwei Kriterien basiert, z.B. eine Kostenstelle und ein Konto, dann müssen wir eine Hilfspalte einfügen um beide Kriterien zu haben. Das ist zusätzliche Arbeit und oft stört es auch im Aufbau eines übersichtlichen Reportings.

Dieser Trick schafft Abhilfe!

Der Multi-Konditionale SVERWEIS

Hier ein Beispielhaftes Problem. Wir wollen die Ausgaben wissen auf einer gewissen Kostenstellen / Konto Kombination:

Wenn wir den SVERWEIS in Kombination mit WAHL und durch eingeben als Matrix Formel eingeben kommen wir auch ohne Hilfsspalte zum gewünschten Resultat.

Wir suchen folgende Kombination: Kostenstelle 1110-2003 und Konto 3510

Die Formel in Zelle C11 ist:

{=SVERWEIS(A11&B11;WAHL({1\2};A2:A7&B2:B7;C2:C7);2;0)}

Das Interessante an dieser Formel ist natürlich der Einsatz von WAHL.

Was passiert da genau?

  1. Der wohl schwierigste Teil zum Verstehen in dieser Formel. Das erste Argument in der WAHL Formel diktiert auf was verwiesen wird. Da wir WAHL aber als Matrix Formel eingeben, können wir eine mehrfache Auswahl treffen! Wir müssen die Geschweiften Klammern an dieser Stelle manuell eingeben. Erst wenn wir die ganze Formel haben drücken wir STRG+SHIFT+ENTER.
  2. Die beiden Kriterien Spalten werden zusammen genommen. Durch die Eingabe als Matrix Formel liest Excel diese als wären Sie eine Spalte in welcher die Zellen zusammengesetzt worden sind mit VERKETTEN. z.B die erste Zeile =A2 & B2 als “1110-20001001”
  3. Hier die Ergebnisspalte in der wir die Ausgaben Werte finden.

Der Rest Drumherum ist einfach die SVERWEIS Formel wie wir sie normalerweise schreiben würden, aber wir geben es als Matrix Formel ein, drück also zuletzt noch STRG+SHIFT+ENTER!

Diese Technik lässt interessante neue Möglichkeiten zu ohne die normalerweise mühsamen Zwischenschritte!

Hier noch die Datei zum selber anschauen: Multi Kontiondaler SVERWEIS

Excel Daten Automatisch Zusammenführen

Wo werdet Ihr das Einsetzen?

Comments

  • Ah, ein Schweizer. :-)
    Letztendlich ist es nur eine weitere Variante zu http://excelformeln.de/formeln.html?welcher=30
    Ich bevorzuge die VERWEIS()-Variante – ohne CSE.

    XLariumDecember 10, 2014
    • Yup. Da komme ich her!

      Der Einsatz dieser Formeln Variante müsste meiner Meinung nach nur auf Dashboards oder Berichten benutzt werden wie alle Matrix Formeln (da man sonst Performance Verluste hat).

      Schreibst Du auf excelformeln.de?

      Lukas RohrDecember 10, 2014
  • Schreiben tun auf excelformeln.de ja nur die Betreiber. Und ich bin keiner von denen.

    XLariumDecember 10, 2014
  • Coole Formel – wie sieht diese aus, wenn man nun gleich die Summe bilden möchte, von mehreren Beträgen mit gleicher Konto- und Kostenstellennummer?

    AnonymousDecember 11, 2014
    • Ich denke eine normale SUMMEWENNS Formel dürfte dafür am besten geeignet sein!

      Lukas RohrDecember 11, 2014
  • Man könnte sich aber auch das Arbeiten mit Access angewöhnen, Da kann man zig Bedingungen einfach verwenden. Ich mache fast alle Auswertungen nur in Access und in Excel dann nur die letzten manuellen Schritte, Anpassungen wenn mit der Auswertung weiter gearbeitet wird. Leider kennen viel zu wenig Access, denn sonst würden viele die vielen Vorteile bei der Erstellung von Auswertungen erkennen

    AnonymousNovember 4, 2016
  • Kann ich auch bei dem Ausschnitt der Formel WAHL({1/2}……ein Bezug auf ein anderes Tabellenblatt machen? Mein Problem, die Spalten 1/2 müssen von einem anderen Tabellenblatt geholt werden. Vielen Dank für Vorschläge!!

    gabrielaelsenerDecember 8, 2016
  • Danke für diesen Trick, hat mir sehr geholfen!

    Matthias BlaserDecember 19, 2016

Leave a Reply