Zwei Tabellen in Excel mit Power Query zusammenführen

Für Auswertungen werden häufiger Daten benötigt, die sich in zwei oder mehreren Tabellen befinden. Falls die Tabellen jeweils eine gemeinsame Spalte haben, werden diese Tabellen in der Regel mit Hilfe der Funktion SVERWEIS miteinander verknüpft. Mit dem Add-In Power Query lassen sich die Daten aus mehreren Tabellen einfach und flexibel in eine neue Tabelle zusammenführen.

In unserem Beispiel sollen zwei Tabellen in eine neue Tabelle überführt werden. In der einen Tabelle mit dem Namen Kunden befinden sich die Stammdaten der Kunden mit Nummer, Name und Ort. In der zweiten Tabelle mit dem Namen Umsätze stehen die Kundennummern, die Umsatzbeträge und die Anzahl der Aufträge, wobei nicht mit allen Kunden Umsätze erzielt wurden. Die beiden Tabellen sehen folgendermaßen aus:

2 zusammenzuführende Tabellen in Excel

Um die die beiden Tabellen mit Power Query zusammenführen, muss dieses Add-In installiert und aktiviert sein. Sie finden dann in dem Menüband die Registerkarte Power Query.

Gehen Sie bitte folgendermaßen vor:

 

    1. Aktivieren Sie eine beliebige Zelle in der linken Tabelle mit den Kundenstammdaten.
    2. Klicken Sie im Menüband auf der Registerkarte Power Query in der Gruppe Excel-Daten auf die Schaltfläche Von Tabelle. Power Query wird gestartet und zeigt Ihnen im Abfrage-Editor die Daten der Tabelle an. Falls Sie vorher keine Tabelle definiert haben, erscheint zunächst ein Dialogbild, indem Sie die Größe des Zellbereichs und die Frage, ob in der ersten Zeile der Tabelle Überschriften stehen, bestätigen müssen.
    3. Schließen Sie den Abfrage-Editor wieder, um die die zweite Tabelle zu übernehmen. Es erscheint eine Sicherheitsabfrage mit dem Hinweis, ob Sie die Änderungen beibehalten möchten. Klicken Sie auf die Schaltfläche Beibehalten. Die Kundendaten werden danach in ein neues Tabellenblatt der aktuellen Arbeitsmappe übertragen.
    4. Wechseln Sie in das Tabellenblatt mit den Umsatzdaten und aktivieren Sie eine beliebige Zelle in der rechten Tabelle mit den Umsatzbeträgen.
    5. Klicken Sie im Menüband auf der Registerkarte Power Query in der Gruppe Excel-Daten erneut auf die Schaltfläche Von Tabelle. Power Query wird wieder gestartet und zeigt Ihnen jetzt die Umsatzdaten im Abfrage-Editor an.
    6. Erweitern Sie den Navigationsbereich Abfragen am linken Rand des Editors, indem Sie auf dem Pfeil, der nach rechts zeigt, klicken. Es werden die beiden Abfragen Kunden und Umsätze angezeigt. Es sind die Namen, die den beiden Tabellen in der Arbeitsmappe gegeben wurden.

      Navigationsbereich in Power Query

    7. Markieren Sie die Abfrage Kunden und klicken Sie im Menüband von Power Query auf der Registerkarte Start in der Gruppe Kombinieren auf die Schaltfläche Abfragen zusammenführen. Es erscheint das folgende Dialogbild:

      Abfragen in Power Query zusammenführen

       

    8. Klicken Sie auf den Pfeil im Kombinationsfeld, das sich unter der Tabelle mit den Kundendaten befindet. Wählen Sie den Eintrag Umsätze. Die ersten Zeilen der Tabelle mit den Umsatzdaten werden angezeigt.
    9. Um die beiden Tabellen über die Kundennummer miteinander zu verknüpfen, markieren Sie zunächst die Spalte Nummer in der Tabelle mit den Kundendaten und danach die in der Tabelle mit den Umsatzdaten.
    10. Wählen Sie im Kombinationsfeld Join-Art den ersten Eintrag Linkerer äußerer Join (alle aus erster, übereinstimmende aus zweiter). Damit legen Sie fest, dass auch die Kunden ohne Umsätze in die neue Tabelle übertragen werden sollen. Sollen nur die Kunden berücksichtigt werden, mit denen Umsätze erzielt wurden, wählen Sie stattdessen den Eintrag Innerer Join (nur übereinstimmende Zeilen). Das Dialogbild sieht folgendermaßen aus:

      Spalten zuordnen in Power Query

      Das Kombinationsfeld Join-Art erscheint erst in der Version, die im August 2015 erscheinen ist.

       

    11. Klicken Sie auf die Schaltfläche OK. Die Abfrage-Editor sieht danach folgendermaßen aus:

      neue Spalte in Power Query

       

    12. Es wurde ein neue Spalte NewColumn angelegt. In allen Zeilen dieser Spalte steht der Eintrag Table. Diese Tabelle soll jetzt aufgelöst werden. Klicken Sie im Spaltenkopf der Spalte NewColumn auf die Schaltfläche mit den beiden Pfeilen. Es erscheint das folgende Dialogbild:

      Spalte erweitern in Power Query

       

    13. Sie sehen alle Spaltenüberschriften aus der Tabelle mit den Umsatzdaten. In die neue Tabelle sollen nur die Spalten Umsatz und Aufträge übernommen werden. Deaktivieren Sie die Kontrollkästchen (Alle Spalten auswählen) und Ursprünglichen Spaltennamen als Präfix verwenden und aktivieren Sie danach die Kontrollkästchen Umsatz und Aufträge. Klicken Sie abschließend auf OK. Die folgende Tabelle wird angezeigt

      Spalte erweitern in Power Query

       

    14. Sicherheitshalber sollten Sie die Spalte Umsatz noch den Datentyp Währung zuweisen. Da in der Tabelle alle Umsatzwerte ohne Nachkommastellen erscheinen, hat die Spalte den Datentyp Ganzzahl. Klicken Sie auf der Registerkarte Start in der Gruppe Transformieren auf den Pfeil im Kombinationsfeld Datentyp und wählen Sie den Eintrag Währung.
    15. Klicken Sie danach auf die Schaltfläche Schließen und Laden in der Gruppe Schließen. Die Daten werden in das Tabellenblatt übernommen. Die neue Tabelle hat folgendes Aussehen:

      Mit Power Query zusammengeführte Tabelle

       

Hinweise

 

    • Die im Abfrage-Editor mit null gekennzeichneten Zellen erscheinen im Excel-Tabellenblatt als leere Zellen.
    • Power Query hat für die Abfrage Kunden und für die Abfrage Umsätze jeweils ein neues Tabellenblatt in der Arbeitsmappe angelegt. Da das Tabellenblatt Umsätze nicht benötigt wird, können Sie die Abfrage Umsätze so einstellen, dass nur eine Verbindung erstellt wird und keine Tabelle erzeugt werden soll. Aktivieren Sie dazu eine Zelle in der neuen Tabelle mit den Umsätzen. Am rechten Bildschirmrand erscheint der Aufgabenbereich Arbeitsmappenabfragen.

      Arbeitsmappenabfragen in Power Query

      Klicken Sie mit der rechten Maustaste auf den Eintrag Umsätze und danach im Kontextmenü auf den Menüpunkt Laden in. Es erscheint das folgende Dialogbild.

      Optionen beim Laden in Power Query

      Wählen Sie den Eintrag Nur Verbindung erstellen und klicken Sie danach auf OK. Wenn Sie Power Query mit Excel 2010 einsetzen, haben Sie im obigen Dialogbild nicht die Möglichkeit, die Daten direkt in das Datenmodell von PowerPivot zu laden.

       

    • Wenn sich die Daten in den beiden Quelltabellen Kunden und Umsätze ändern, können Sie die Zusammenführung der beiden Tabellen aktualisieren, indem Sie im Aufgabenbereich Arbeitsmappenabfragen den Eintrag Kunden markieren und auf die Schaltfläche Aktualisieren am rechten oberen Rand des Eintrags klicken. Den Befehl Aktualisieren finden Sie auch, wenn Sie mit der rechten Maustaste das Kontextmenü öffnen.
    • Auf diese Weise können Sie nicht nur 2 Excel-Tabellen zusammenführen, sondern auch Tabellen aus anderen Datenquellen wie z.B. SQL- oder Access-Datenbanken.

Seminar

Weitere interessante Einsatzmöglichkleiten für Power Query erlernen Sie in unserem Seminar.

Unsere Dienstleistungen für Sie

Wenn Sie Unterstützung beim Erstellen von Power Query-Abfragen benötigen, sprechen Sie uns an, Tel. 0 23 24 / 70 89 4 – 0 oder füllen Sie unser Kontaktformular aus.

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

4 Kommentare

  1. Anonymous

    Interessanter ausführlicher Artikel. Durch die Grafiken verständlicher. Er hat mich zum Nachdenken angeregt. Ich werde das mal in meiner Fa probieren, ob ich auch Tourenpläne damit erstellen bzw auswerten u optimieren kann. Die bestehen jetzt aus zig Einzeldateien.

    Antworten
  2. Estrichbeton

    Das ist ja mal ein informativer, sorgfältig mit Liebe zum Detail geschriebener Artikel. Vielen Dank! 🙂

    Antworten
  3. Peter

    Was aus dem Beitrag nicht hervorgeht, diese Variante hat einen sehr entscheidenten Nachteil: Die Tabelle wird nur auf manuellem Wege aktualisiert, wo hingegen SVerweis automatisch nachgerechnet wird. Für weitere Berechnungsschritte ist diese Variante entsprechend nutzlos.

    Antworten
    • Michael Richter

      Hallo Peter,

      das beschriebene Beispiel soll zeigen, dass es oft einfacher ist, zwei Tabellen, bei der allen Datensätze miteinander verknüpft werden sollen, vor dem Laden in eine Excel-Tabellenblatt bereits mit Power Query zu verbinden und nur die resultierende Tabelle zu importieren. Diese Vorgehensweise hat den Vorteil, dass die beiden Tabellen aus unterschiedlichen Datenquellen stammen können und bei hoher Anzahl von Datensätzen performanter ist, weil das Eingeben und Kopieren etlicher SVERWEIS-Formeln wegfällt. Für den SVERWEIS gibt es nach wie vor zahlreiche Anwendungsgebiete, für die Power Query keinen Ersatz darstellt. Die Aktualisierung von Abfragen Power Query kann so eingestellt werden, dass sie beim Öffnen der Arbeitsmappe oder nach Ablauf einer bestimmten Zeitspanne automatisch erfolgt.

      Antworten

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Pin It on Pinterest

Share This