Das ist Teil 2 meiner dreiteiligen Artikelserie, in der ich beschreibe, wie man die Joins aus Power Query direkt mit Excel-Funktionen umsetzen kann.
Im ersten Teil hatte ich ja schon drei der Joins vorgestellt, in diesem Artikel nehme ich mir die verbleibenden 3 vor.
Für den dritten und letzten Teil habe ich mir dann ein besonderes Schmankerl überlegt. Aber jetzt basteln wir uns erst einmal drei Joins mit Excel zusammen.
Und so geht’s:
Beispieldatei herunterladen
Auch heute gleich wieder ein wichtiger Hinweis am Anfang:
Da meine hier gezeigten Lösungen dynamische Array-Funktionen voraussetzen, brauchst du dafür zwingend Excel 2024 oder Microsoft 365. Ansonsten bleibt dir natürlich weiterhin die Möglichkeit, die Joins direkt in Power Query zu nutzen.
Der Vollständigkeit halber sind hier nochmal die 6 Joins aus Power Quer:
- 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
Falls du den ersten Artikel noch nicht gelesen hast, kannst du es gerne hier nachholen.
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!
Innerer Join
Mit diesem Join soll die Schnittmenge aus beiden Quelltabellen gebildet werden. Also nur die Datensätze, die in beiden Tabellen enthalten sind.
Das erreicht man im Wesentlichen durch zwei Filterfunktionen, deren Ergebnisse sortiert und mit HSTAPELN nebeneinander aufgelistet werden:
=HSTAPELN(
SORTIEREN(FILTER(t_KundenProdukt1;ZÄHLENWENN(t_KundenProdukt2[Kdnr];t_KundenProdukt1[Kdnr])>0;""));
SORTIEREN(FILTER(t_KundenProdukt2;ZÄHLENWENN(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr])>0;"")))
Der innere Join ist relativ einfach
In den beiden FILTER-Funktionen wird über ZÄHLENWENN geprüft, wie oft die Kundennummer der einen Tabelle auch in der anderen enthalten ist:
ZÄHLENWENN(t_KundenProdukt2[Kdnr];t_KundenProdukt1[Kdnr])
Wenn das Ergebnis größer als Null ist, liefert die Funktion ein logisches WAHR, ansonsten ein logisches FALSCH. Die Besonderheit dabei ist, dass ZÄHLENWENN hier nicht nur eine einzelne Kundennummer als Suchkriterium verwendet, sondern gleich die ganze Spalte (t_KundenProdukt1[Kdnr] bzw. t_KundenProdukt2[Kdnr]).
Dadurch erzeugt die Funktion nicht, wie eigentlich üblich, nur ein einzelnes Ergebnis, sondern gleich eine ganze Liste. Diese Liste mit WAHR und FALSCH wird dann als Filterkriterium verwendet und über die umschließende SORTIEREN-Funktion noch nach der Kundennummer sortiert. Letzteres ist wichtig, damit beim anschließenden Stapeln die jeweiligen Datensätze auch zusammenpassen.
Linker Anti-Join
Kommen wir nun zu den etwas merkwürdig klingenden „Anti-Joins“. Der linke Anti-Join liefert nur die Datensätze, die ausschließlich in der linken Tabelle vorkommen. Also nur diejenigen Kunden, die nur das Produkt 1 gekauft haben.
Mit dieser Information könnten wir dann beispielsweise eine kleine Marketing-Kampagne starten, um nur genau diesen Kunden auch das Produkt 2 anzubieten. Denn das haben sie ja offensichtlich noch nicht gekauft.
Für diesen Fall reicht eine einfache FILTER-Funktion:
=FILTER(t_KundenProdukt1;ZÄHLENWENN(t_KundenProdukt2[Kdnr];t_KundenProdukt1[Kdnr])=0;"")
Der linke Anti-Join ist noch einfacher
Es wird also wieder gezählt, wie oft eine Kundennummer aus Tabelle 1 in der Tabelle 2 vorkommt. Ist das Ergebnis gleich Null, wird ein WAHR geliefert, ansonsten ein FALSCH. Und die FILTER-Funktion filtert dann die Tabelle 1 nach den Zeilen, die ein WAHR ergeben.
Nur für den Fall, dass es keinen solchen Kunden gibt, habe ich als letztes Argument in der Filterfunktion zweimal die Anführungszeichen gesetzt. Dann wird einfach eine leere Zelle angezeigt, und nicht ein #CALC-Fehler.
Da es in diesem Fall zwangsläufig keine Datensätze aus der zweiten Tabelle geben darf, können wir diesen Teil getrost ignorieren und die Formel somit schön schlank halten.
Rechter Anti-Join
Du ahnst es bereits, was dieser Join macht: Er liefert alle Kunden, die nur in der rechten Tabellen enthalten sind.
Die Formel dafür lautet:
=FILTER(t_KundenProdukt2;ZÄHLENWENN(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr])=0;"")
Der rechte Anti-Join (Variante 1)
Ich habe die Formel aus optischen Gründen in den orangen Ausgabebereich geschrieben, da es ja in dieser Darstellung keine Datensätze im grünen Bereich geben kann.
Rechter Anti-Join (alternative Version)
Damit auch die Formel für den rechten Anti-Join wie alle anderen gleich in der ersten Spalte des Ausgabebereichs steht, habe ich mir noch eine zweite Variante überlegt.
Die Herausforderung bei dieser Variante ist, dass wir im grünen Bereich eine bestimmte Anzahl von leeren Zeilen und Spalten benötigen. Genau gesagt sollen es so viele leere Zeilen sein, wie unser äußerer Join für Tabelle 2 ausgibt. Und es sollen so viele Spalten sein, wie die Tabelle 1 hat.
Dafür verwende ich wieder die LET-Funktion:
=LET(
Kunden2;FILTER(t_KundenProdukt2;ZÄHLENWENN(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr])=0;"");
Kunden1;WENN(SEQUENZ(ZEILEN(Kunden2);SPALTEN(t_KundenProdukt1));"");
HSTAPELN(Kunden1;Kunden2))
Der rechte Anti-Join (Variante 2)
In der LET-Funktion definiere ich erst eine Variable „Kunden2“, welcher exakt der rechte äußere Join aus der oben gezeigten ersten Variante zugewiesen wird. So weit, so klar.
Die Variable „Kunden1“ erzeugt über die SEQUENZ-Funktion eine Matrix mit so vielen Zeilen wie in „Kunden2“ und so vielen Spalten wie t_KundenProdukt1 enthält:
SEQUENZ(ZEILEN(Kunden2);SPALTEN(t_KundenProdukt1))
Bis hierher sähe die Ausgabe damit noch so aus:
Noch unvollständig: die Ausgabe mit der reinen SEQUENZ-Funktion
Hinweis:
Die Spalte mit dem Kaufdatum habe ich wie auch in allen anderen Beispielen manuelle als Datum formatiert.
Wir wollen aber keine fortlaufenden Zahlen, sondern einfach nur leere Zellen. Und genau da kommt die WENN-Funktion ins Spiel:
WENN(SEQUENZ(ZEILEN(Kunden2);SPALTEN(t_KundenProdukt1));"")
Sie nimmt einfach die Matrix aus der SEQUENZ-Funktion, welche ja lauter fortlaufende Zahlen erzeugt. Wenn der jeweilige Wert ein logisches WAHR ergibt, dann gibt die WENN-Funktion ein leeres Element aus.
Dazu muss man folgendes wissen:
Bei einer Wahrheitsprüfung gilt in Excel jede Zahl (auch negative Zahlen oder Dezimalzahlen) als WAHR, nur die Ziffer 0 gilt als FALSCH.
Da unsere SEQUENZ-Funktion nur fortlaufende Zahlen ab 1 generiert, liefert jeder Wert ein WAHR und wird somit durch ein leeres Element ersetzt.
Der rechte Anti-Join (Variante 2)
Es gäbe sicherlich noch andere Lösungen, um die entsprechende Anzahl an leeren Zeilen und Spalten zu erzeugen, aber diese soll uns erst einmal reichen.
Und wozu das Ganze? Das erzähle ich dir im nächsten und letzten Beitrag in dieser kleinen Artikelserie…
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.