Blätter einer Arbeitsmappe mit Power Query zusammenfassen (1)

Xtract: Mehrere oder auch alle Arbeitsblätter einer Mappe mittels Power Query zu einer einzigen Abfrage/Tabelle zusammenfassen. Der Aufbau der einzelnen Tabellen ist identisch, die Anzahl der Zeilen unterschiedlich.

  Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung   

(Fast) Alle Blätter einer Arbeitsmappe in einer Übersicht mittels Power Query zusammenfassen

Gegeben ist diese Excel-Mappe, wo für jeden Monat ein Arbeitsblatt existiert. Zusätzlich gibt es noch ein Sheet, wo die Feiertage des Jahres vermerkt sind. Alle Monats-Blätter sind identisch aufgebaut, die Anzahl der Tage variiert natürlich zwischen 28 und 31. Die eigentlichen Daten sind bis Mitte März bereits erfasst. Die Umsätze werden täglich eingetragen und natürlich auch weiterhin erfasst. Und ein weiterer wichtiger Punkt: Die Daten einschließlich der Überschriften sind bis zum Ultimo des jeweiligen Monats als Intelligente Tabelle formatiert, auch wenn noch keinerlei Umsätze eingetragen worden sind. Das ist eine Voraussetzung für die hier vorgestellte Lösung.

Es soll in einer getrennten Datei auf einem Blatt eine Zusammenfassung erstellt werden, die immer den tagesaktuellen Stand abbildet. Einerseits soll dieses eine fortlaufende Tabelle sein, andererseits ist es später das Ziel, die erfassten Daten in einer PivotTable zumindest minimalistisch auszuwerten.

Öffnen Sie zu Beginn den Bereich, wo Sie die Power Query Funktionalität nutzen können (⇒ mehr). Das ist der gleichnamige Menüpunkt in Excel 2010/13 und das Menü Daten in Excel 2016. Die Beschreibung hier bezieht sich auf Excel 2016. – Erstellen Sie eine neue Abfrage aus einer Datei:

Abfrage aus einer Excel-Datei erstellen (2016)

Abfrage aus einer Excel-Datei erstellen (2016)

Als Quelle biet ich Ihnen das oben genannte File an. Nach der Auswahl per Doppelklick oder dem Klick auf Importieren zeigt sich dieser Dialog:

Der Inhalt der gewählten Arbeitsmappe, Arbeitsblätter und Tabellen

Der Inhalt der gewählten Arbeitsmappe, Arbeitsblätter und Tabellen

Gelb gemarkert ist hier der Hinweis, dass 26 Elemente ausgewählt werden können. Für 12 Monate scheint das etwas viel … Aber schauen Sie sich gerne einmal die beiden (markierten) Monate April an. Einmal mit dem Präfix tbl_ und einmal ohne. Das betrifft übrigens alle weiteren Einträge gleichermaßen. Der Hintergrund: tbl_Januar (als Beispiel) ist eine Intelligente Tabelle mit genau diesem Namen, Januar ist der Name des Tabellenblatts. Das Symbol links der Bezeichnung ist entsprechend unterschiedlich, auch wenn der Name einmal identisch sein sollte. Da Power Query so oder so eine Liste/Tabelle erwartet, wählen Sie doch gleich die formatierten Tabellen als Quelle aus.

Da Sie ja mehrere Objekte markieren und auswählen wollen, setzen Sie zuerst das Häkchen ganz oben bei Mehrere Elemente auswählen. Ein Klick auf tbl_April, Shift und ein Klick auf tbl_September. Dann noch ein Klick etwa in der Mitte der Auswahl auf tbl_Feiertage um die Markierung wieder zu entfernen, denn diese Daten brauchen Sie in dieser Auswertung nicht. – Sie könnten jetzt auf Laden klicken, aber da Sie die Daten noch bearbeiten werden, klicken Sie auch auf die Schaltfläche Bearbeiten.

Die Daten werden eingelesen und Monat für Monat in je einer eigenen Abfrage im Query-Editor dargestellt. Im linken Seitenfenster genügt ein Klick auf einen Monat und die Daten werden im mittleren Bereich entsprechend angezeigt:

Darstellung der Daten im Editor, hier der Monat Januar

Darstellung der Daten im Editor, hier der Monat Januar

Sie erkennen, dass es 12 einzelne Abfragen sind. Prinzipiell der Spiegel der einzelnen Tabellen. Das Ziel ist ja, alle 12 Monate in 1 Tabelle/Liste zusammenzufassen, zu kombinieren. Und da bietet sich sehr weit rechts im Menüband, direkt neben der Gruppe Transformieren die Schaltfläche Kombinieren an. Ein Klick auf diese Schaltfläche und Sie sollen sich entscheiden, ob Sie Abfragen zusammenführen oder anfügen wollen. Sie werden an die derzeit aktive Abfrage die anderen Abfragen Anfügen. Im sich öffnenden Fenster …

Der Dialog, um an die aktuelle Abfrage weitere anzufügen

Der Dialog, um an die aktuelle Abfrage weitere anzufügen

… markieren Sie erst den Punkt Drei oder mehr Tabellen und markieren links den zweiten Monat in der Liste der verfügbaren Tabellen, dann Hinzufügen. Sie sehen den Erfolg sofort auf der rechten Seite. Das wiederholen Sie für den dritten bis zwölften Monat. Leider ist eine Mehrfachmarkierung nicht möglich, auch ein Doppelklick zeigt keine Wirkung. Ungewöhnlich für Microsoft, aber hier ist aus meiner Sicht Nachbesserungsbedarf gegeben. Wenn alle Monate in der rechten Liste aufgeführt sind, OK.

Die Abfrage tbl_April ist immer noch markiert, in der Statuszeile unten links steht, dass sie 7 Spalten und 365 Zeilen enthält. Prima, 2014 war kein Schaltjahr, also passt das mit den 365 Tagen auch. Im rechten Seitenfenster sehen Sie, dass zwar (mindestens) eine Abfrage angefügt ist und dass der Name immer noch tbl_April ist:

Ändern Sie den Namen der Abfrage

Ändern Sie den Namen der Abfrage

Der besseren Identifizierung wegen wegen ändern Sie den Namen der Abfrage bitte auf tbl_Zusammenfassung. Blättern Sie nun einmal durch die Datensätze. Sie erkennen, dass eine sehr große Menge an Zeilen mit Ausnahme des Datums keine Daten enthält. Und da stellt sich die Frage, was mit diesen Zeilen geschehen kann. Einerseits soll der Jahresüberblick aktuell sein, andererseits machen sich diese vielen umsatzlosen Tage nicht gut und erschweren auch die Transparenz der Liste.

Aber erst einmal soll nach dem Datum sortiert werden. Ein Klick in das Überschrift-Feld auf DropDownPfeil und Sie können Aufsteigend sortieren. In der zweiten Spalte (Brot) klicken Sie auch auf das DropDown-Symbol und entfernen jetzt das Häkchen bei (NULL), es steht ganz oben, direkt unter Alles auswählen. OK und ein Blick in die Statuszeile zeigt, dass nur noch 58 Zeilen vorhanden sind. Jetzt noch ein Klick auf Schließen & laden und prinzipiell ist die Aufgabe erledigt.

Aber Sie werden gewiss überrascht sein, denn es wurden 12 Tabellenblätter erzeugt und im rechten Seitenfenster sind noch alle Abfragen sichtbar. Klicken Sie auf die Abfrage tbl_Zusammenfassung und sofort wird genau dieses Arbeitsblatt zum aktiven Sheet. Löschen Sie alle restlichen Blätter (nicht die Abfragen), die Sheets werden nicht mehr gebraucht. Idealerweise benennen Sie das Blatt mit der Zusammenfassung, dem Ergebnis der Abfrage auch um, beispielsweise Jahr 2014 oder Zusammenfassung.Und falls Sie das rechte Nebenfenster irritiert, können Sie es auch mit dem großen X neben Arbeitsmappenabfragen schließen.

Der letzte Eintrag ist vom 10.03.2014, was ja auch der letzte Tag mit Umsatz ist. Den ganzen Aufwand werden Sie natürlich am 11.03. nicht noch einmal durchführen wollen, nur weil an dem Tag die Umsätze aktualisiert worden sind. Sie ahnen es, das brauchen Sie auch nicht. – Tragen Sie in der Tabelle mit den Roh-Daten am 11. März (oder auch weiteren Tagen) beliebige Umsatzzahlen ein und speichern Sie die Mappe, sie brauchen sie aber nicht zu schließen. Wechseln Sie wieder zur Zusammenfassung und im Menü Daten, Gruppe Verbindungen ein Klick auf Alle Aktualisieren. Schon haben Sie das neue Ergebnis. – Apropos Ergebnis: Hier finden Sie die fertige Auswertung.

Diese Liste können Sie nach Belieben bearbeiten, formatieren, auswerten. Im kleinen Rahmen auch als Pivot Tabelle. Wie Sie solch eine Kreuztabelle dann auch noch wesentlich besser mit PivotTable auswerten können, das lesen Sie beispielsweise hier oder auch in diesem Beitrag nach. Übrigens auch mit Hilfe von Power Query. 😎


Blätter einer Arbeitsmappe zusammenfassen Teil 1
Blätter einer Arbeitsmappe zusammenfassen Teil 2
Blätter einer Arbeitsmappe zusammenfassen Teil 3

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,00  freuen …

Dieser Beitrag wurde unter Daten zusammenführen, Musterlösungen, Ohne Makro/VBA, Power Query, Tabelle und Zelle abgelegt und mit , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.