Wenn du regelmäßiger Leser dieses Blogs bist, dann weißt du, dass ich ein großer Fan von Power Query bin. Damit lassen sich sehr viele Dinge erledigen, die mit „reinem“ Excel gar nicht oder nur sehr umständlich und mit viel Aufwand möglich sind.
Dennoch gibt es Gründe, manche Dinge direkt in Excel umzusetzen. Also ohne Power Query. In meinem Fall ist aus meiner persönlichen Urlaubsplanung die Idee entstanden, die Joins aus Power Query mit Excel-Funktionen nachzubauen.
Was genau dahinter steckt und wie alles funktioniert, erzähle ich dir in diesem Artikel.
Beispieldatei herunterladen
Meine Frau und ich haben einen kleinen Road-Trip durch Deutschland geplant. Zu diesem Zweck hatte jeder von uns in einer Liste die Städte aufgeführt, die er/sie auf jeden Fall besuchen möchte. Danach musste man nur noch die beiden Listen abgleichen und auf möglichst viele Übereinstimmungen hoffen, damit dann der Haussegen nicht allzu schief hängt…
Klar, zwei Listen mit jeweils zwei Handvoll Städtenamen lassen sich einfach nebeneinander legen und manuell abgleichen. Ginge natürlich auch mit einem inneren Join in Power Query. Aber wie würde man das mit Formeln in Excel lösen?
Die Artikel-Idee war geboren und meine Experimente begannen!
Die Join-Arten von Power Query
Damit überhaupt für jeden klar ist, worum es überhaupt geht, hier die Übersicht der 6 verschiedenen Joins, die es in Power Query gibt:
- Linker äußerer Join
- Rechter äußerer Join
- Vollständiger äußerer Join
- Innerer Join
- Linker Anti-Join
- Rechter Anti-Join
Die 6 Join-Arten in Power Query
Im diesem Blogbeitrag schauen wir uns die ersten Drei davon näher an, die anderen Drei kommen dann im nächsten Artikel dran.
Noch ein wichtiger Hinweis gleich zu Beginn:
Du brauchst Excel 2024 oder Microsoft 365, um die von mir verwendeten Funktionen nutzen zu können. Ansonsten bleibt dir natürlich weiterhin die Möglichkeit, die Joins direkt in Power Query zu nutzen.
Oder hast du den Einstieg schon hinter dir und möchtest jetzt das nächste Level erklimmen? Dann schau dir meinen Fortgeschrittenen-Kurs „Power Query-Praxisfälle“ an!
Linker äußerer Join
Dieser Join funktioniert im Grunde wie der allseits bekannte SVERWEIS oder auch der XVERWEIS. Man hat eine Tabelle (die „linke“ Tabelle), die man um Informationen aus einer anderen Tabelle (der „rechten“ Tabelle) ergänzen möchte. Sofern in der rechten Tabelle passende Daten vorhanden sind, werden diese in der linken Tabelle angezeigt, ansonsten eben nicht.
Folgendes kleine Beispiel soll das verdeutlichen.
Ich habe zwei Tabellen mit Kunden, die ein bestimmte Produkte gekauft haben. In Tabelle t_KundenProdukt1 sind alle Kunden enthalten, die das Produkt 1 gekauft haben, in t_KundenProdukt2 diejenigen, die das Produkt 2 gekauft haben:
Diese beiden Tabellen sollen zusammengeführt werden
Bei näherer Betrachtung erkennt man, dass manche Kunden nur in der ersten Tabelle, andere nur in der zweiten und manche sogar in beiden Tabellen enthalten sind.
Der linke äußere Join liefert nun sämtliche Kunden aus t_KundenProdukt1 und aus der t_KundenProdukt2 nur die übereinstimmenden Kandidaten.
Das gewünschte Ergebnis für den linken äußeren Join
Das Symbol mit den beiden Kreisen veranschaulicht ein wenig die Logik (vielleicht erinnerst du dich ja noch an die Mengenlehre in grauer Vorzeit im Grundschulunterricht…)
Das geübte Auge bemerkt vielleicht den blauen Rahmen um die Ausgabetabelle, welche auf eine dynamische Array-Funktion schließen lässt.
Stimmt! Für alle Formeln, mit denen ich die Joins nachbilde, kommen dynamische Array-Funktionen zum Einsatz.
Online-Kurs „Dynamische Array-Funktionen“
Wer es ganz eilig hat: Hier ist die fertige Formel dazu:
=HSTAPELN(
t_KundenProdukt1;
WENNFEHLER(INDEX(t_KundenProdukt2;XVERGLEICH(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr]);SEQUENZ(1;SPALTEN(t_KundenProdukt2)));""))
Die Formel für den linken äußeren Join
Was genau passiert hier?
Mit HSTAPELN lassen sich mehrere Tabellen oder Listen nebeneinander („H“ wie horizontal) stapeln.
HSTAPELN(Matrix1;Matrix2;...)
Da auf jeden Fall die komplette linke Tabelle ausgegeben werden soll, wird als erstes Argument einfach t_KundenProdukt1 übergeben.
Von der rechten Tabelle hingegen werden nur die übereinstimmenden Daten benötigt. Diese werden mit einer Kombination aus INDEX und XVERGLEICH abgefragt.
Die Kombination aus INDEX, XVERGLEICH und SEQUENZ
Für das Zeilen-Argument wird mit XVERGLEICH die Kundennummer aus der t_KundenProdukt1 in der t_KundenProdukt2 gesucht und bei einem Treffer die relative Position ausgegeben. Als die Trefferzeile.
Hinweis:
Statt XVERGLEICH hätte man genauso gut die alte VERGLEICH-Funktion verwenden können. XVERGLEICH hat aber den Vorteil, dass standardmäßig immer nach einer exakten Übereinstimmung gesucht wird. Bei VERGLEICH bräuchte man dafür zwingend das dritte Argument „0“. Die XVERGLEICH-Variante ist daher also etwas kürzer.
Nun zum Spalten-Argument.
SEQUENZ(1;SPALTEN(t_KundenProdukt2))
Es sollen in meinem Beispiel immer sämtliche Spalten aus der t_KundenProdukt2 ausgegeben werden. Da die Beispieltabelle aus drei Spalten besteht, hätte man anstelle der SEQUENZ-Funktion auch einfach schreiben können:
{1.2.3}
Damit übergibt man ein Array mit genau 3 Spalten.
Für mehr Flexibilität kommt bei mir jedoch die SEQUENZ-Funktion zum Einsatz. Darin wird mit der Funktion SPALTEN die Anzahl der Spalten in der t_KundenProdukt2 ermittelt und dann einfach eine fortlaufende Liste von 1 bis (Anzahl der Spalten) geliefert.
Ach ja, die umschließende WENNFEHLER-Funktion sorgt nur noch dafür, dass bei den Kunden ohne Übereinstimmung ein #NV-Fehler angezeigt wird.
Und das war’s auch schon mit dem linken äußeren Join!
Vielleicht fragst du dich, warum ich statt der etwas komplizierteren INDEX/XVERGLEICH-Variante nicht einfach mit einem XVERWEIS gearbeitet habe?
Eine sehr gute Frage!
Das war auch mein erster Ansatz. Man kann mit dem XVERWEIS nämlich grundsätzlich auch mehrere Spalten auf einmal ausgeben. Leider läuft der XVERWEIS aber dann nicht nach unten in mehrere Zeilen über. Jedenfalls nicht so, wie man es gerne hätte. Es wird dann nur noch die erste Spalte mit der Kundennummer ausgegeben, selbst wenn man die ganze Tabelle angegeben hat:
XVERWEIS liefert leider nicht das gewünschte Ergebnis
Schade. Darum also INDEX + XVERGLEICH.
Rechter äußerer Join
Nachdem wir den linken Join jetzt abgefrühstückt haben, ist der rechte ein Kinderspiel. Dieser Join verhält sich einfach nur spiegelverkehrt. Es werden also sämtliche Datensätze aus der rechten Tabelle ausgegeben, und nur die übereinstimmenden aus der linken.
=HSTAPELN(
WENNFEHLER(INDEX(t_KundenProdukt1;XVERGLEICH(t_KundenProdukt2[Kdnr];t_KundenProdukt1[Kdnr]);SEQUENZ(1;SPALTEN(t_KundenProdukt1)));"");
t_KundenProdukt2)
Der rechte äußere Join ist nur seitenverkehrt
Man muss also nur mit der Reihenfolge bei den Tabellennamen aufpassen, ansonsten sind die Formeln identisch mit Variante für den linken äußeren Join.
Vollständiger äußerer Join
Diese Join-Art soll sämtliche Datensätze aus beiden Tabellen ausgeben. In der Sprache der Mengenlehre wäre das die Vereinigungsmenge.
So soll der vollständige äußere Join aussehen
Bei Kunden, die in beiden Tabellen enthalten sind, werden in einer Zeile auch beiden Datensätze ausgegeben. Bei anderen Kunden sind die betreffenden Spalte der jeweils anderen Tabelle leer.
Die Formel dazu sieht noch ein bisschen wilder als die bisherigen aus:
=HSTAPELN(
WENNFEHLER(INDEX(t_KundenProdukt1;XVERGLEICH(EINDEUTIG(VSTAPELN(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr]));t_KundenProdukt1[Kdnr]);SEQUENZ(1;SPALTEN(t_KundenProdukt1)));"");
WENNFEHLER(INDEX(t_KundenProdukt2;XVERGLEICH(EINDEUTIG(VSTAPELN(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr]));t_KundenProdukt2[Kdnr]);SEQUENZ(1;SPALTEN(t_KundenProdukt2)));""))
Aber du wirst gleich sehen, dass es nicht so schlimm ist.
Um die oben gezeigte Darstellung zu erreichen, müssen erst einmal sämtliche Kundennummern ermittelt werden, die es zusammengenommen überhaupt gibt.
Zu diesem Zweck verwende ich eine Kombination aus EINDEUTIG und VSTAPELN innerhalb der schon bekannten XVERGLEICH-Funktion:
XVERGLEICH(EINDEUTIG(VSTAPELN(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr]));t_KundenProdukt1[Kdnr])
Damit erhalte ich eine duplikatfreie zeilenweise („V“ wie vertikal) Liste aller Kundennummern.
Ansonsten ist es wieder die exakt gleiche Kombination aus INDEX und XVERGLEICH, die wir schon beim linken bzw. rechten äußeren Join verwendet haben. Nur diesmal eben für beide Tabellen.
Die Formel für den vollständigen äußeren Join (ohne LET)
Eine Variante mit der LET-Funktion
Wenn in solchen komplexen und verschachtelten Formeln Teile davon mehrfach vorkommen, dann sollte man an die LET-Funktion denken. In diesem Artikel habe ich eine kleine Einführung zu LET gegeben: LET it be? Ganz im Gegenteil!
Vereinfacht gesagt lassen sich in LET eigene Variablen definieren, die man dann innerhalb der Funktion weiterverwenden kann. Die Formel wird dadurch besser lesbar und vor allem leichter änderbar.
In unserem Beispiel ist der Teil mit EINDEUTIG/VSTAPELN mehrfach vorhanden und könnte daher mit LET vereinfacht werden. Die alternative Formel würde dann so aussehen:
=LET(
Kundennummern;EINDEUTIG(VSTAPELN(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr]));
HSTAPELN(
WENNFEHLER(INDEX(t_KundenProdukt1;XVERGLEICH(Kundennummern;t_KundenProdukt1[Kdnr]);SEQUENZ(1;SPALTEN(t_KundenProdukt1)));"");
WENNFEHLER(INDEX(t_KundenProdukt2;XVERGLEICH(Kundennummern;t_KundenProdukt2[Kdnr]);SEQUENZ(1;SPALTEN(t_KundenProdukt2)));"")))
Die alternative Formel für den vollständigen äußeren Join (mit LET)
Zuerst wird eine Variable „Kundennummern“ definiert und dieser die EINDEUTIG/VSTAPELN-Funktion zugewiesen.
In den beiden XVERGLEICH-Funktionen wird dann nur noch der Variablenname verwendet.
Welche Variante du nun bevorzugst – mit oder ohne LET – ist letztendlich eine Geschmacksfrage. Das Ergebnis ist das gleiche.
Damit sind wir am Ende des ersten Teils angekommen. Die anderen drei Join-Arten stelle ich dir im nächsten Artikel vor.
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.