Warum wir SVERWEIS in Excel nicht verwenden sollten…

20 min readNov 25, 2019

Hinweis, eine englische Version dieses Artikels gibt es hier.

Beruflich nutze ich das gute alte Excel praktisch jeden Tag, ich erhalte jede Woche unzählige Excel-Dateien und erstelle welche — kurz um, ich bin wohl ein “Excel-Fanboy”. Doch was ich so alles in Excel zu sehen bekomme, erinnert mich mehr und mehr an das berühmte Hammer-Zitat.

If the only tool you have is a hammer, you tend to see every problem as a nail.
- von Abraham Maslow

SVERWEIS — Der Excel-Hammer

Einer dieser Excel-Hammer ist SVERWEIS (engl. VLOOKUP), denn der exzessive Gebrauch von SVERWEIS ist (fast) immer ein Zeichen dafür,

dass Excel für Dinge eingesetzt wird, für die es bessere Werkzeuge gibt,
dass Excel als Datenbank missbraucht wird oder,
dass der Nutzer immer noch in einer “alten Excel-Welt” gefangen ist.

Whenever I open a spreadsheet and see someone using vlookup() to look up a value, I feel sad for that person. It’s like I’m watching someone trying to light a fire by banging rocks together, and I have matches in my pocket.
— Charlie Benkendorf on Medium

Immer wenn ich eine Excel-Datei öffne und jemanden sehe, der SVERWEIS() nutzt, um nach einem Wert zu suchen, fühle ich mich für ihn traurig. Es ist als würde ich zusehen, wie jemand versucht ein Feuer zu entfachen, indem er Steine aneinander schlägt, während ich Streichhölzer in meiner Tasche habe.
— Charlie Benkendorf on Medium, Übersetzung d.A.

Das obige Zitat stammt aus einem lesenswerten Artikel auf Medium, der viele richtige Einschränkungen der SVERWEIS-Funktion bespricht. Leider überzeugen mich die dort genannten Lösungen nicht, denn auch dort fehlt der Hinweis auf die neuen Excel-Features, Power Query und Power Pivot, mit denen viele SVERWEIS-Szenarien wesentlich eleganter, robuster und mit größerem Benefit gelöst werden könnten.

SVERWEIS und seine Anwendungsfälle

SVERWEIS nutzen wir in der alten Excel-Welt immer dann, wenn wir einen Wert aus einer anderen Tabelle (“Matrix” oder “Bereich”) suchen müssen. Der englische Name ist insofern deutlich selbsterklärender, “VLOOKUP” => Value LOOK UP (also etwa “Wert nachschlagen”).

Nehmen wir die folgende Liste von Kunden und Rechnungsbeträgen. Jedem Kunden haben wir außerdem eine Rabatt-Gruppe zugeordnet.

Eine weitere Tabelle liefert uns den Rabatt, der auf die jeweilige Rabatt-Gruppe anzuwenden ist.

Ziel ist es, für jeden Kunden in der ersten Tabelle den richtigen Rabatt aus der zweiten Tabelle zu finden und den endgültigen rabattierten Rechnungspreis zu bestimmen.

Viele Benutzer würden an dieser Stelle (vor)schnell zu SVERWEIS greifen.

F2 = SVERWEIS(E2;'Tab2'!A2:B6;2;Falsch)

Die obige Funktion macht nichts anderes als,

in Zelle E2 die Rabatt-Gruppe herauszulesen (“4”),
in das Arbeitsblatt Tab2 und dort in den Bereich A2:B6 zu springen,
in der ersten Spalte dieses Bereichs (A)
nach unserer Rabatt-Gruppe (“4”) zu suchen,
dann in der ersten Zeile, in der die Rabatt-Gruppe vorkommt, anzuhalten,
von dort aus in die angegebene Spalte (“2” = B) zu springen, um
den dort gefundenen Wert (3%) zurückzuliefern.

Die Angabe “Falsch” bedeutet, dass wir eine exakte Suche wünschen.

Dies war nur ein high-level Überblick über SVERWEIS, für mehr Informationen sei auf die Microsoft Excel Dokumentation verwiesen.

Der Rest ist für den durchschnittlichen Excel-Anwender plain vanilla, Formel nach unten ziehen, in Spalte G Rabatt und Preis multiplizieren, durch hundert teilen und anschließend in Spalte H den Rabatt vom Preis abziehen…

Die Unzulänglichkeiten und Stolpersteine…

Photo by Ronaldo de Oliveira on Unsplash

Soweit, so gut! Die oben genannte Formel ist absolut richtig, sie liefert einen Rabatt von 3%. Alles ist so, wie es sein sollte. Klasse! Feierabend!

❗️ STOPP mein Freund, langsam❗️

Leider gibt es mit SVERWEIS nämlich einige Stolpersteine und Einschränkungen, die man kennen sollte.

Stolperstein 1 — ein alter Hut und (nur) eine Geschmacksfrage!?

Ich finde es höchst befremdlich, wenn in Formeln das Wort “FALSCH” steht. Dies mag im englischen weniger befremdlich wirken, da “wrong” und “false” nicht verwendungs- und bedeutungsgleich sind, in der deutschen Sprache fühlt sich “falsch” jedoch irgendwie “falsch” an und führt immer wieder zu Nachfragen von weniger erfahrenen Benutzern.

Um diesen Punkt abzuschließen: Wenn es unbedingt SVERWEIS sein soll, dann bitte FALSCH durch einen Wert ersetzen, der in jeder Excel-Version als FALSE ausgewertet wird. 0 — ist unsere Rettung, denn 0 ist seit dem IT-Urknall der natürliche Zustand für FALSCH! 0️⃣

SVERWEIS(E2;'Tab2'!A2:B6;2;0)SVERWEIS(E2;'Tab2'!A2:B6;2;1=2) //funktioniert auch, aber verwirrt!

Allerdings ist dieser Punkt durchaus mehr als eine reine Geschmackssache. Vielmehr verdeutlicht er welche Schwierigkeiten durch verschiedene Sprachversionen von Excel entstehen können.

Die Gefahr lag lange Zeit darin, dass Excel-Dateien mit “deutschen Formeln” in anderssprachigen Excel-Versionen Schwierigkeiten bereiten konnten. Gerade das Wort “Falsch” in SVERWEIS hat nach meinen Erfahrungen immer mal wieder Schwierigkeiten bereitet.

Wenn man sich in aktuellen Versionen von Excel die Dateien bzw. das XML-Markup ansieht, dürfte dieser Punkt nicht mehr kritisch sein. (Wie das geht und wie wir damit hacken können, erläutere ich in einem anderen Post.)

Excel ist so intelligent, unsere als SVERWEIS(E2;'Tab2'!A2:B6;2;FALSCH) eingegebene Formel “heimlich” als VLOOKUP(E2, 'Tab2'!A2_B6,2,FALSE) zu speichern. 👍

Dennoch können wir auch heute nicht einfach einem ausländischen Kollegen eine Formel außerhalb von Excel schicken oder in Schulungsunterlagen abdrucken. Die Eingabe von =VLOOKUP(...) in meiner deutschen Excel-Version geht definitiv schief. Und so dürfte es sich mit anderssprachigen Systemen und deutscher Formel-Syntax verhalten.

BTW, in anderssprachigen Versionen sind Funktionsargumente nicht wie bei uns mit “;” getrennt, sondern hier nutzt Excel das “,”… 😦

Stolperstein 2 — Formel nach unten ziehen

Machen wir also weiter und “ziehen” unsere Formel nach unten oder kopieren diese in weitere Zeilen. Das Ergebnis bringt leider jede Menge Fehler. 💥

Der geneigte Excel-Anwender wird hier abwinken, ihm ist sofort klar, was passiert ist. Spätestens ab Zeile 6 verweist unsere Formel nämlich auf einen Bereich (A7:B11), in dem nichts mehr zu finden ist.

Die Lösung ist klar, wir brauchen absolute Zell-Bezüge, die wir durch das Einfügen von $-Zeichen bekommen. Tipp: F4-Taste nutzen. 😉

=SVERWEIS(E2;'Tab2'!$A$2:$B$6;2;WAHR)

Tipp: Alternativ können wir auch unser Nachschlage-Tabelle (Lookup-Tabelle) einen Namen geben und diesen dann in der Formel verwenden. Dadurch können wir die Formel gefahrlos kopieren.😉

F2=SVERWEIS(E2;rabattCode;2;0) 
F3=SVERWEIS(E3;rabattCode;2;0)
F4=SVERWEIS(E4;rabattCode;2;0)
...

Das Thema “absolute Zellbezüge” in Formeln ist ebenfalls kein spezifisches SVERWEIS-Problem. In SVERWEIS besteht jedoch die Gefahr, dass ein solcher Fehler lange unentdeckt bleibt.

Diese Gefahr besteht, wenn die Lookup-Tabelle sehr lang ist und wir nicht mit “exakter Übereinstimmung”, sondern mit “ungefährer Übereinstimmung”, also mit SVERWEIS(...;...;...;WAHR) arbeiten.

Stolperstein 3 — Der Spaltenindex beginnt mit 1

In unser Formel haben wir bisher immer im Spaltenindex “2” gesucht (SVERWEIS(...;...;2;0) ), obwohl wir doch eigentlich das Ergebnis aus der “ersten” Spalte neben unserer Such-Spalte haben wollten, oder?

Warum die Such-Spalte hier mitzählt, weiß wohl nur Microsoft selbst. Denn wozu sollten wir jemals nach Rabatt-Gruppe “4” suchen, indem wir Rabatt-Gruppe “4” eingeben?

It’d be like going to the phone book and looking up “John Smith” in order to find “John Smith”. — Charlie Benkendorf

Abgesehen davon, ist der Spaltenindex ohnehin keine besonders glückliche Sache. Haben wir es mit einer Lookup-Tabelle zu tun, die sehr viele Spalten hat und brauchen wir dann ggf. noch mehrere Spalten aus der Lookup-Tabelle, dann befinden wir uns schnell in einer wahren “Zähl-Orgie” — Wie viel Spalten waren das noch gleich?

Die Möglichkeit einer benannten Ergebnis-Spalte gibt es in SVERWEIS leider nicht. ⚡️

Einschränkung 1 — SVERWEIS bedeutet von Links nach Rechts

Wie auch Charlie Benkendorf in seinem Artikel zeigt, ist eine der blödesten Einschränkungen von SVERWEIS, dass in unser Nachschlage-Tabelle der gesuchte Wert immer rechts vom Suchkriterium stehen muss.

Würde unsere Lookup-Tabelle wie hier abgebildet aussehen, hätten wir keine Chance mit SVERWEIS zu arbeiten.

Bei einer solch kleinen Tabelle drehen wir die Spalten einfach um, fertig! In der Praxis können beide Listen aber durchaus lang werden oder sehr dynamisch sein (häufigen Änderungen unterliegen). Wollen wir dann wirklich jedes Mal eine “Copy ‘n Paste”-Orgie veranstalten? ⚡️

Einschränkung 2 — Spaltensuche geht nicht

Was, wenn wir die folgende Lookup-Tabelle erhalten haben?

Natürlich ist so etwas einfach nur ein schlechtes Tabellen-Design.

Leider liegt es eben oftmals nicht in unseren Händen, die Struktur von Daten-Zulieferungen zu bestimmen.

SVERWEIS hilft hier jedenfalls nicht! Was hier hilft: WVERWEIS— allerdings mit den ansonsten gleichen Problemen und Einschränkungen wie SVERWEIS.

=WVERWEIS(H16;L1:P2;1;0)

Einschränkung 3— Suche nach mehreren Kriterien geht nicht

Was machen wir, wenn wir folgende Tabellen haben?

Es gibt meines Wissens keine Möglichkeit, SVERWEIS mit mehreren Kriterien arbeiten zu lassen. Die einzige Lösung die mit einfallen würde, wäre eine Verkettung, um ein individuelles Ordnungsmerkmal zu erhalten.

Auf diese Weise könnten wir wieder mit SVERWEIS arbeiten, müssten in der Lookup-Tabelle zunächst aber noch die Spalte “Key” links vor die Spalte “Rabatt in %” schieben (siehe oben Einschränkung 1).

❓ Index — Match als Lösung? ❓

Die oft gepriesene Alternative soll nun eine Kombination aus INDEX() und MATCH() bzw. in deutschen Versionen INDEX() und VERGLEICH() sein.

O.K. — es funktioniert! Aber was zur Hölle haben wir hier für eine Formel? 😱

=INDEX('Tab2'!B2:B6;  VERGLEICH('Tab1'!E2;  'Tab2'!A2:A6;  0);  0)

Was macht die INDEX()-Funktion

Was macht INDEX— Hier die Antwort von Microsoft:

Gibt den Wert eines Elements in einer Tabelle oder einem Array zurück, das durch die Zeilen-und Spaltennummern Indizes ausgewählt wird.

Alles klar soweit? Nein? 😕 Dann folgt hier die Erklärung.

Stellen wir uns die folgende Tabelle (besser Matrix/Array/Bereich) vor.

Die Formel =INDEX(A1:C9;5;2) würde

in dem Bereich A1:C9 gehen,
in Zeile 5 des Bereichs springen,
in Zeile 5 dann in Spalte 2 den Wert (“15”) auslesen und
uns diesen zurückgeben.

Haben wir nur eine Spalte, könnten wir die Formel sogar vereinfachen und den Spalten-Index weglassen =INDEX(A1:A9;5) was letztlich =INDEX(A1:A9;5;0) entspräche (was m.E. inkonsistent ist, aber das ist ein anderes Thema…).

INDEX hat den Vorteil, dass wir uns auf diese Weise und horizontal und vertikal in unserer Matrix bewegen können. So gibt INDEX(A1:C1;1;2) hier 11 (B1) zurück.

Was macht die VERGLEICH()-Funktion

Zur VERGLEICH-Funktion schreibt Microsoft:

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range..

Auch hier alles klar soweit? Nein? 😕 Dann folgt auch hier die Erklärung.

Nehmen wir wieder unser Beispiel von oben.

Mit VERGLEICH lassen wir hier im Bereich B1:B9 nach dem Wert “14” suchen, und zwar nach exakt “14”. Wir bekommen jetzt den Wert 4 zurückgeliefert, weil sich die “14” in dem ausgewählten Bereich sich an 4. Stelle befindet.

Auch dies geht in Zeilenrichtung, wie hier zu sehen.

Das Ergebnis hier ist “2”, denn in dem Bereich A4:C4 finden wir die “14” exakt an zweiter Stelle.

INDEX und MATCH zusammen

Kommen wir zurück auf unsere obige Formel.

=INDEX('Tab2'!B2:B6;  VERGLEICH('Tab1'!E2;  'Tab2'!A2:A6;  0);  0)

Beginnen wir unsere Analyse mit dem inneren VERGLEICH(...). Diese Funktion

sucht nach dem Wert aus E2 , also nach der Rabatt-Gruppe “4”,
und zwar in unser Lookup-Tabelle im Bereich A2:A6,
unter Beachtung genauer Übereinstimmung.

Diese Übereinstimmung findet die Funktion im vierten Element des Bereichs (konkret hier die vierte Zeile). Die Funktion liefert also “4” zurück.

Damit können wir nun auch unsere INDEX(...) Funktion erklären. Diese

sucht in der Lookup-Tabelle im Bereich B2:B6
in der vierten Zeile (sieh unsere VERGLEICH(...) Funktion)
Die Spalte brauchen wir nicht, diese ist daher “0”.

Der Rabatt der dort gefunden wird, wird nun zurück geliefert. 👏 💪

Was sind die Vorteile?

Ja, wir sind jetzt erheblich flexibler, als mit SVERWEIS. Wir können mit ein und derselben Funktions-Kombination:

in Spalten suchen
in Zeilen suchen
in nicht zusammenhängenden Bereichen suchen

Schön ist sicherlich auch, dass die VERGLEICH-Funktion nicht so eine hässliche Übereinstimmungs-Syntax (“Wahr”, “Falsch”) hat, sondern direkt “0”, “1” oder “-1” verlangt.

Wer bisher nur die SVERWEIS-Welt kannte, der dürfte gerade eine Offenbarung erlebt haben. 🌠

Warum bin ich unzufrieden?

1. Statt mit einer Funktion, haben wir es plötzlich mit der Kombination von zwei Funktionen zu tun.

=INDEX('Tab2'!B2:B6;VERGLEICH('Tab1'!E2;'Tab2'!A2:A6;0);0)

Egal ob in Excel oder in der Programmierung, dass Verschachteln von Funktionen macht die Nachvollziehbarkeit hart. Sollten wir jetzt noch längere Namen der Arbeitsblätter haben, wird es noch unübersichtlicher (siehe folgendes Beispiel).

=INDEX(Rabatt_Codes_for_2018_2019_rev!A1:A10;VERGLEICH(Rabatt_Codes_for_2018_2019_rev!B1:B10;data_syst_report_march_2019!E2;0);0)

2. Das Problem der relativen Bezüge haben wir hier ebenfalls. Wir brauchen wieder unsere $-Syntax. Die Formel sieht dann leider schon so aus.

=INDEX('Tab2'!$B$2:$B$6; VERGLEICH('Tab1'!E2;'Tab2'!$A$2:$A$6;0);0)

Oder eben so…

=INDEX(Rabatt_Codes_for_2018_2019_rev!$A$1:$A$10;VERGLEICH(Rabatt_Codes_for_2018_2019_rev!$B$1:$B$10;data_syst_report_march_2019!E2;0);0)

Und wehe, ein $-Zeichen steht an der falschen Stelle! 💥

3. Weiterhin nicht gelöst, haben wir das Thema mehrfacher Suchkriterien.

Seien wir ehrlich, vielleicht bin ich da auch einfach zu kleingeistig, für mich ist das alles ein gruseliges Durcheinander.

Power Query und Power Pivot als Rettung

Photo by Sebastian Grochowicz on Unsplash

Wenn der SVERWEIS-Mann unser Steinzeit-Mensch ist, Charlie immerhin schon Streichhölzer hat, dann werden wir jetzt in eine Rakete steigen, damit 3x um die Erde düsen, im vorbeifliegen unseren Photonen-Grill anwerfen und trotzdem unser Essen warm haben, noch bevor die anderen ihr Feuer überhaupt entzünden konnten… 😁

Auf in die blühenden Landschaften…

Ausgangslage unserer Challenge — 10–9–8…

O.K. nehmen wir also die Aufgabe an. Wir haben unsere Kundenliste jetzt wie folgt erhalten.

Insgesamt liegen uns 1.000 Datensätze vor, die Datei soll ein Export aus unser Firmendatenbank sein. Wir müssen daher immer damit rechnen, dass wir wieder solch einen Report erhalten und neue Auswertungen erstellen müssen… also möglichst wenig Handarbeit!

Außerdem hat die Geschäftsleitung uns folgende Tabelle geschickt, aus der die Rabatte je Rabatt-Gruppen hervorgehen.

Auch diese Datei kann sich, nach Lust und Laune der Geschäftsleitung in kommenden Jahren, Wochen, Tagen ändern. Wir haben hier also ebenfalls keine Lust manuell tätig zu werden und vielleicht die Reihenfolge der Spalten zu ändern.

Außerdem brauchen wir eine Suche nach multiplen Kriterien. Unsere Kundendaten enthalten die meisten Kunden doppelt, einmal für 2019 und einmal für 2019. Gleichzeitig sind die Rabatte je Rabatt-Gruppe ebenfalls vom Jahr abhängig. Wer hier mit der alten Excel-Welt einen Blumentopf gewinnen will möge sich jetzt melden…

Vorbereitung für Power Query — 7…6…

1. Da wir jederzeit damit rechnen müssen, dass wir geänderte Daten erhalten schlage ich vor, die Datei mit dem Kundenstamm-Report einfach in einen Ordner zu speichern.

2. Die Tabelle mit den Rabatt-Gruppen, würde ich ebenfalls in einer separaten Excel-Tabelle speichern.

3. Für die eigentliche Auswertung würde ich mir nun eine gesonderte leere Excel-Datei anlegen. Damit brauchen wir später nur einen Knopf drücken, um einen neuen Report einzulesen…

Daten mit Power Query einlesen — 5…4…

In unserer neu angelegten Datei gehen wir jetzt wie folgt vor.

Wir wählen unsere Report-Datei…

…und es öffnet sich ein Fenster…

Wir hätten hier auch gleich auf “Laden” klicken können, da wir aber wenigstens etwas lernen wollen gehen wir den etwas längeren weg.

An dieser Stelle öffnet sich jetzt der Power Query-Editor.

Viel gibt es hier im Moment nicht zu tun. Power Query (PQ) hat unsere Daten schon weitestgehend automatisiert vorbereitet. 🎉 🎊

Dort wo jetzt noch “data” steht, tragen wir “Stammdaten” ein, dadurch erhält unsere Abfrage einen aussagekräftigeren Namen.

In dem mit “2” markierten Bereich, können wir außerdem sehen welche Schritte PQ schon auf unsere Daten angewendet hat. Es lohnt sich hier mal herum zu klicken!

Jetzt gehen wir schnell auf “Schließen und laden in” und der erste Schritt ist fast getan.

Den vorstehenden Dialog passen wir wie hier gezeigt an.

Wir wählen “Nur Verbindung erstellen” aus, da wir zwar mit den Daten arbeiten wollen, sie aber (jetzt noch) nicht in unsere Excel-Datei kopiert bekommen möchten. Die Verbindung zu den Daten reicht uns völlig — “keine Kopier-Orgien” war schließlich eines unserer Ziele.

Zum Thema Datenmodell kommen wir später noch.

Was jetzt in unser Excel-Datei passiert (nicht viel), ignorieren wir einfach und lesen nun auch die zweite Datei ein.

Hier gehen wir abermals, nachdem wir einen sinnvollen Namen für die Abfrage vergeben haben, auf “Schließen und laden in”.

Das Vorgehen gleicht dem der ersten Datei...

Nachdem wir hier “OK” gedrückt haben, sollte uns Excel zwei Abfragen anzeigen, sonst aber keine Daten.

Immer noch ein leeres Blatt? — 3…2…

Bis jetzt haben wir mit wenigen Klicks scheinbar auch nur wenig bis nichts geschafft. Kein Grund zur Sorge, denn tatsächlich haben wir unsere beiden Dateien eingelesen und damit den Grundstein für etwas Großes, Neues und Schönes gelegt. 👼

Zeit die Abfragen zu verbinden. Dazu klicken wir eine der Abfragen mit rechts an und wählen “zusammenführen”.

Und jetzt wird es cool! 😮

Wir brauchen keine Formeln, kein SVERWEIS, kein INDEX, kein VERGLEICH, wir denken nicht über relative und absolute Bezüge nach, wir zählen keinen Spaltenindex, wir klicken einfach ein bisschen mit der Maus umher.

Dazu wählen wir die beiden zu verknüpfenden Abfragen aus…

Anschließend klicken wir oben und unten diejenigen Spalten-Köpfe an, die übereinstimmen müssen; die Rabatt-Gruppe und das Jahr.

Ganz nebenbei haben wir damit im vorbeifliegen das Problem mehrfacher Suchkriterien gelöst. 💪

Hinweis 1: Um mehrere Spalten auszuwählen bitte die shift -Taste gedrückt halten.

Hinweis 2: Bitte auf die kleinen Zahlen in den Spaltenköpfen achten. Wenn Rabatt-Gruppe oben die “1” hat, muss sie dies auch unten haben…

Die Verknüpfungsart (Join-Art) belassen wir bei “Linker äußerer Join”.

Die Vorschau verrät uns, dass wir damit für unsere tausend Kunden-Datensätze tatsächlich auch tausend Rabatt-Gruppen-Datensätze gefunden haben. 👍

Jetzt O.K. drücken und PQ erstellt automatisch eine neue Abfrage für uns.

Links (1) sehen wir die neue Abfrage “Merge1”, die wir auf der rechten Seite (3) gern umbenennen können. Außerdem hat PQ unseren Kunden-Datensätzen eine neue Spalte “Rabattgruppen” zugeordnet.

Diese brauchen wir nur noch wie folgt expandieren.

🎉 Der wesentlichste Teil der Aufgabe wäre damit erledigt...

Wir haben eine Spalte mit den jeweiligen Rabatten, außerdem habe ich hier noch den Abfrage-Namen geändert.

Wie schon angekündigt, machen wir jetzt gleich noch ein wenig Mathematik, dann brauchen wir später nicht mehr in Excel rumkaspern.

Hierzu legen wir eine Benutzerdefinierte Spalte an.

PQ fügt uns unsere neue Spalte hinzu. Hier sollten wir noch das richtige Zahlenformat wählen.

Anschließend ermitteln wir mit einer weiteren benutzerdefinierten Spalte den rabattierten Rechnungsbetrag.

Auch hier noch das Zahlenformat anpassen und fertig ist unsere Aufgabe!

In Excel importieren! — 1…0…Fire

Dies ist der Moment! Wir klicken einfach auf “Schließen & Laden” und alles ist erledigt!

In Excel ist alles genau so wie wir es uns vorgestellt haben, oder! 💪 💪

Rekapitulieren wir noch mal…

Was haben wir jetzt erreicht?

Wir haben keine Formeln schreiben müssen, kein SVERWEIS, kein INDEX, kein VERGLEICH.
Die Reihenfolge der Spalten und Zeilen ist völlig egal.
Relative und absolute Bezüge sind kein Thema für uns.
Mehrfache Verknüpfungskriterien haben wir mit wenigen Klicks in den Griff bekommen.
Im PQ-Editor ist jeder Schritt auch nachträglich noch nachvollziehbar und änderbar (hierauf bin ich in diesem Artikel nicht eingegangen).
Selbst wenn sich unsere Ausgangsdaten ändern, Spalten verschwinden oder hinzukommen, PQ wird dies nicht stören.

Bonus 1 — Wiederverwendbarkeit

Ich hatte eingangs erwähnt, dass unsere Lösung viel effizienter ist, als die alten Excel-Lösungen. Den Beweis will ich natürlich nicht schuldig bleiben.

Nehmen wir daher an, wir haben gerade unser obiges Werk abgeschlossen, wollen in die Pause gehen und… das Telefon klingelt.

“Sorry, die Stammdatenselektion in unser Datenbank war falsch…äh…ja…also, das tut mir jetzt leid, ich hoffe du hast noch nicht angefangen… Ich wollte dir nur die Kunden bis Juli 2019 schicken, tja…hihi…haha.. und jetzt…du weißt schon der neue Praktikant... irgendwie hast du auch die bis September bekommen…ich schick dir gleich noch eine neue Datei…”. 😡

Nicht ärgern mein Freund!

Früher im alten Excel, ja, da hätten wir jetzt wieder die neuen Tabellen zusammenkopiert, die Formeln kopiert, Spalten eingefügt, Fehler gesucht und und und…

Mit PQ schmeißen wir einfach die alte Stammdaten-Datei weg, speichern die neue Datei mit gleichem Namen an die Stelle der Alten und klicken auf aktualisieren. 🎉 🎊

Wollen wir das ausprobieren? — Wir öffnen hierzu unsere Stammdatendatei und löschen einfach mal die ersten 10 Zeilen und speichern die Datei.

Vorher:

Aktualisieren:

Nachher:

Selbiges könnten wir machen, falls die Rabatte sich ändern. Cool, oder? Hat gerade mal eine Minute gedauert. Aber, psssst, wir sind ja hier unter uns und sollten das Excel-Schamanentum aufrecht erhalten. 😈

Bonus II — In Spalten suchen

Ich hatte bei den Nachteilen von SVERWEIS erwähnt, dass wir mit SVERWEIS nicht in Spalten suchen können. Mit PQ geht dies im Grund auch nicht. Der Grund hierfür liegt darin, dass der Aufbau einer solchen Tabelle im Grunde schon ein fehlerhaftes Daten-Design ist und bestraft werden sollte, aber dies ist ein anderes Thema.

Hier noch mal das Problem:

Tja, was tun? — Lesen wir die Datei doch einfach mal ein!

In unserem Fall sieht das Ergebnis wie folgt aus.

Fragen wir uns ferner: Was würden wir in Excel machen?

Wir würden die Daten kopieren und dann auf “Werte einfügen” und “Transponieren” gehen. Und auch in PQ machen wir es ähnlich. PQ hat dafür sogar einen eigenen Button. Der heißt zwar nicht “Transponieren”, sondern “Vertauschen”, macht ansonsten aber nichts anderes...

Das Ergebnis kann sich sehen lassen, oder?

Jetzt müssen wir PQ nur noch erklären, dass die erste Zeile unser Tabellen-Kopf werden soll, und natürlich, auch hierfür gibt es einen Button. 😃

Und schon haben wir unser Ziel erreicht!

Das schöne ist, wie immer in PQ, dass wir die Quell-Daten jederzeit austauschen können und PQ wird immer die gleichen Schritte durchführen.

Power Pivot

Jetzt, da wir unsere Aufgabe(n) erfolgreich und effizient gelöst haben, und hoffentlich niemand mehr exzessiv SVERWEIS in Excel nutzt, könnten wir uns zurücklehnen und einen Kakao trinken. ☕️

Da ich eingangs allerdings noch ein weiteres Tool erwähnte, Power Pivot, bin ich an dieser Stelle doch noch einige Zeilen schuldig.

Ich werde in diesem Artikel aber keinen Einstieg in Power Pivot bieten, dafür ist dieses Tool mit seiner Programm-Spache DAX zu mächtig und umfangreich. Aus Gründen der Vollständigkeit will ich aber zumindest zeigen, wie wir SVERWEIS auch mit diesem Tool ersetzen können.

Blicken wir dazu noch einmal zurück, wie wir die Abfragen geladen haben.

Ich hatte hier immer darauf Wert gelegt, dass wir die Abfrage auch unserem Datenmodell hinzufügen. Warum? Dies blieb bisher offen.

Der Grund ist (technisch unsauber, hier aber ausreichend erklärt): Die Abfrage ist damit in Power Pivot vorhanden.

Zeit also, dass wir Power Pivot öffnen.

Wieder öffnet sich ein Fenster, dass wir vorher noch nie gesehen haben. Dieses Fenster ist Power Pivot für Excel.

Ganz unten können wir die Abfragen sehen, die unserem Datenmodell hinzugefügt wurden und mit denen wir in unserem Datenmodell arbeiten können.

In den Stammdaten legen wir nun, ohne langes Zögern, eine neue Spalte an.

Dazu markieren wir die erste freie Spalte (1) und beginnen in der Formel-Zeile “Look…” einzutippen. Aus der Vorschlagsliste wählen wir die Funktion LOOKUPVALUE aus.

LOOKUPVALUE — SVERWEIS in schön…

LOOKUPVALUE funktioniert ähnlich wie SVERWEIS, allerdings mit vielen Vorteilen.

Wir suchen nicht per Spaltenzahl-Angabe, sondern mit benannten Spalten.
Wir brauchen uns ebenfalls nicht um relative und absolute Bezüge kümmern, Power Pivot wendet Formeln immer auf die gesamte Spalte an.
Wir brauchen keine Reihenfolge der Spalten beachten.
Wir können nach mehreren Kriterien suchen.

Die Syntax ist letztlich Folgende.

LOOKUPVALUE(
             Tabelle[Spalte] aus der unser Ergebnis kommen soll;
             Tabelle[Spalte] in der wir suchen;
             [Spalte]         mit dem Wert, nach dem wir suchen
)

Hierbei können wir jedoch beliebig viele Suchpaare eingeben. Die Formel in unserem Fall sieht daher wie folgt aus.

=LOOKUPVALUE(
         Rabattgruppen[Rabatt in %];
         Rabattgruppen[Jahr];     [Jahr];
         Rabattgruppen[Rabatt-Gruppe];      [Rabatt-Gruppe]
  )

Wieder erreichen wir zügig eine Verknüpfung unserer Daten.

Ging doch schnell oder? — Die Frage, was wir mit diesen Daten anfangen können, muss ich aus Zeitgründen aber einem anderen Post vorbehalten.

Power Pivot — Beziehungen

Pover Pivot bietet noch eine weitere, sehr elegante Möglichkeit, Daten zu verknüpfen. Dies geht allerdings nicht mit multiplen Suchkriterien.

Hier ein kurzes Beispiel.

Nehmen wir an, wir haben aus unserem Buchführungssystem eine Bilanzliste exportiert und diese in Power Pivot eingelesen. Jedes Konto kann hier nur ein einziges Mal vorkommen.

Ferner haben wir für steuerliche Zwecke eine Liste mit Abweichungen zu den Bilanzwerten erstellt und ebenfalls in Power Pivot eingelesen.

Auch hier sei aus Gründen der Vereinfachung angenommen, dass für jedes Konto nur maximal eine Abweichung möglich ist.

Das Ziel ist hier natürlich ebenfalls, die beiden Listen zu kombinieren.

Auch hier würde LOOKUPVALUE wieder funktionieren. Doch Power Pivot bietet noch eine weitere Möglichkeit. Dazu gehen wir in die Diagrammsicht.

In dieser Ansicht werden uns sämtliche Tabellen mit ihren Spaltenüberschriften angezeigt.

Durch einfaches Ziehen (Drag ‘n Drop) können wir in dieser Ansicht Beziehungen anlegen.

Power Pivot zeigt uns nun durch eine Verbindungslinie dass zwischen den Tabellen Beziehungen bestehen. Alles weitere bleibt auch hier einem separaten Artikel vorbehalten.