Office-Blog

Excel-Listen abgleichen und verknüpfen, das geht ganz leicht: Die 6 magischen Joins von Power Query

03.07.2018   Hildegard Hügemann

Was Datenbanken als Standard können, was in Excel per SVERWEIS und mit umständlichem Zusammenkopieren funktioniert, lässt sich in Power Query mit ein paar Mausklicks und ohne jede Formel erledigen.

  • Welche Produkte für Kunde A werden auch an Kunde B verkauft?
  • Welche Artikel aus Lager A kann auch Lager B liefern?
  • Welche Artikel wurden im aktuellen Jahr nicht verkauft?
  • Welche Kunden haben im letzten Jahr keine Bestellung aufgeben?

Um solche Fragen zu beantworten, müssen Listen abgeglichen werden. Das erledigen in Power Query sogenannte Joins. Sie stellen die notwendigen Beziehungen zwischen den zu vergleichenden Tabellen her. So werden beispielsweise die Artikelnummern aus Lager A mit den Artikelnummern aus Lager B abgeglichen. Für diesen Abgleich stellt Power Query sechs verschiedene Join-Möglichkeiten zur Verfügung.

Im folgenden Beispiel geht es um den Verkauf von Artikeln in Europa und Asien. Die Artikel, die in beiden Kontinenten verkauft wurden, sind im Bild 1 fett dargestellt.

Umsatzdaten aus verschiedenen Kontinenten abgleichen

Bild 1: Welche Artikel wurden sowohl in Europa als auch in Asien verkauft? Sie sind fett hervorgehoben.

Mit diesen Schritten wird ein Join definiert

  1. In Excel über das Register Daten > Aus Tabelle/Bereich die beiden Umsatztabellen für Europa und Asien in Power Query laden
  2. Im Power Query-Editor über das Register Start > Kombinieren [1] > Abfragen als neue Abfrage zusammenführen [2]
  3. Im Dialogfenster Zusammenführen die Tabellen Europa [3] und Asien [4] auswählen
  4. In jeder dann angezeigten Tabelle die Spalte Artikel als übereinstimmende Spalte markieren [5] [6]
  5. Unter Join-Art je nach Fragestellung die passende Auswahl – wie weiter unten beschrieben – treffen [7]

In Power Query stehen 6 verschiedene Joins zur Auswahl

Bild 2: Über den Befehl Kombinieren > Abfragen zusammenführen stehen 6 verschiedene Joins zur Auswahl

Hinweis:

  • Die übereinstimmenden Spalten müssen nicht den gleichen Namen tragen, wohl aber vom gleichen Datentyp sein.
  • Durch Drücken der Strg-Taste beim Markieren können auch mehrere Spalten zum Abgleich gewählt werden. Diese werden dann mit Nummern für die eindeutige Zuordnung der Spalten versehen.

Nach Auswahl der Join-Art können über die neue Spalte Asien in Bild 3 [1] [2] die gewünschten Spalten aus der Asien-Tabelle gewählt werden.

Beim Zusammenführen die benötigten Spalten auswählenBild 3: Nach Auswahl des passenden Joins können die gewünschten Spalten ausgewählt werden

6 Fragen … 6 Joins … 6 Antworten

Nachfolgend wird der Nutzen der verschiedenen Joins dargestellt.

Frage 1: Welche in Europa verkauften Artikel wurden auch in Asien abgesetzt und mit welchem Umsatz? Dabei kommt der linke äußere Join zum Einsatz.

 

Frage 2: Welche in Asien verkauften Artikel wurden auch in Europa abgesetzt und mit welchem Umsatz? Dabei kommt der rechte äußere Join zum Einsatz.

Rechter äusserer Join

 

Frage 3: Welcher Umsatz wurde auf beiden Kontinenten mit allen Artikeln generiert?
Hier liefert der vollständige äußere Join die nachfolgende Gesamtliste.

Vollständiger äusserer Join

 

Frage 4: Welche Artikel wurden auf beiden Kontinenten, also sowohl in Europa als auch in Asien, verkauft und mit welchem Umsatz?
Der innere Join liefert die sog. Schnittmenge der auf beiden Kontinenten verkauften Artikel

Innerer Join

 

Frage 5: Welche Artikel wurden nur in Europa verkauft und mit welchem Umsatz?
Der linke Anti Join liefert das Ergebnis. Die Spalten für Asien wurden im Bild nur zur Verdeutlichung mit aufgeführt.

Linker Anti Join

 

Frage 6: Welche Artikel wurden nur in Asien verkauft und mit welchem Umsatz?
Entsprechend Frage 5 kommt hier der rechte Anti Join zum Einsatz. Auch hier wurden die Artikel und Umsatzspalte aus Europa nur zur Verdeutlichung belassen.

Rechter Anti Join

 

FAZIT: Die obigen Fragestellungen in Excel mit Formeln und Kopieren zu lösen, bedarf einigen Aufwands. In Power Query hingegen lässt sich das in Sekunden durch Klicken erledigen.

Lesen Sie im Folgebeitrag, wie mit einem Spezialjoin – dem sog. Cross Join (Kreuzprodukt) – alle Daten der einen Tabelle mit allen Daten der anderen Tabelle kombiniert werden können.

Und wer mehr wissen will, sollte sich schnell einen Platz bei den Power BI-Kompetenztagen im Oktober 2018 sichern.

 

Über den Autor

Hildegard Hügemann

- Dipl.-Informatikerin
- Zertifizierte Office-Trainerin mit den Schwerpunkten Excel, Access, Word
- Entwicklerin von Excel-Tools zur Projekt-, Produktions- und Verkaufssteuerung
- Coach bei der Datenaufbereitung mit Access, Power Query und Power Pivot
- Entwicklerin von Access-Datenbanken für Mittelständler und Behörden
- Bloggerin zu Excel und Access auf www.huegemann-informatik.de
- Autorin für Microsoft Press, dpunkt.verlag und das »Projekt Magazin«

4 Comments so far

MarcoPosted on  1:38 pm - Okt 24, 2020

geiler Beitrag ;) Power Query und die Joins endlich verstanden und in meine täglichen Arbeit anwenden können. Danke, Danke, Danke :)

Mehrere Excel-Listen kombinieren und berechnen: Der Cross Join macht’s möglich – office-kompetenz.dePosted on  8:18 am - Jul 23, 2018

[…] Beitrag vom 3. Juli 2018 habe ich beschrieben, wie Listen mit gleichen Spalten über Joins miteinander abgeglichen werden. […]

Excel-Listen abgleichen und verknüpfen geht ganz leicht: Die 6 magischen Joins von Power Query | huegemann-informatik.dePosted on  10:51 am - Jul 11, 2018

[…] Wie Sie das mit ein paar Klicks in Power Query mit sogenannten Joins erledigen, zeige ich in diesem Blogbeitrag auf […]

Frank Arendt-TheilenPosted on  7:24 pm - Jul 3, 2018

Hallo Hildegard,
Wieder einmal ein toller Beitrag, der mit den grafischen Darstellungen alles klar erklärt.
Frank Arendt-Theilen