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?
- 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.
- 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”
- 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:
Wo werdet Ihr das Einsetzen?
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.
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?
Schreiben tun auf excelformeln.de ja nur die Betreiber. Und ich bin keiner von denen.
Coole Formel – wie sieht diese aus, wenn man nun gleich die Summe bilden möchte, von mehreren Beträgen mit gleicher Konto- und Kostenstellennummer?
Ich denke eine normale SUMMEWENNS Formel dürfte dafür am besten geeignet sein!
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
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!!
Danke für diesen Trick, hat mir sehr geholfen!
mit {1.2} statt {1\2} funktionierts auch in excel 2010
Hallo
ich hoffe dieses Forum ist noch aktiv und ich bekomme eine Antwort?
Ich suche einen Formel bei dem ich die Kriterien über zwei Spalten auf einem Blatt in einem anderen Blatt suche und den Wert aus der 6. Spalte aus dem 2. Blatt im 1. Blatt in der Ergebniszelle angezeigt bekomme.
also:
Spalte1;Spalte2
101 10100
findet aus dem zweiten Blatt den hinterlegten Text aus der Spalte 6 welcher durch 101 10100 in Spalte A&B referenziert ist.
Ich habe es mit SVERWEIS und WAHL probiert aber kriege es nicht hin…
bin für jeden Tip froh. es hadelt sich übrigens um Excel für MAC…
Danke für den Hinweis zu Excel 2010!
Hallo,
in der angegeben Formel steht {1\2}. In der Download-Datei wird jedoch {1.2} verwendetm womit es auch letztlich bei mir geklappt hat. Anderenfalls habe ich eine Fehlermeldung erhalten. Ich verwende Excel 365, sollte dies von Relevanz sein.
Danke für die klasse Seite und den Tipps.
Hi Anon,
relevant für das beschriebene Verhalten ist letztendlich nicht die Excel Version, sondern die Betriebsumgebung (Sprich OS) Landeseinstellungen. Die Trennzeichen die Vergeben werden sind somit abhängig von den Einstellungen welche die Regionalen Sprach- und Zahlensdarstellungen definieren. Hoffe die Antwort hilft Dir als Erklärung weshalb es passiert.
Hallo zusammen
Funktioniert dies auch mit Texteingaben? Wenn ja, wie kann dies implementiert werden?
Danke und Gruss