Xtract: Mehrere oder auch alle Register (Arbeitsblätter) einer Arbeitsmappe in die gleiche Mappe gezielt importieren und auch die Blattnamen gezielt auswerten. (Teil 1)
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Alle oder auch nur ausgewählte Arbeitsblätter/Register der aktuellen Mappe als PQ-Abfrage importieren
Laden Sie von unserem Server erst einmal diese Datei herunter. Das Ziel ist, in der gleichen Mappe ein Arbeitsblatt mit den zusammengefassten Daten zu erstellen. Wie das in einem anderen (also nicht mit einem die Daten enthaltenen) Workbook machbar ist, konnten Sie hier nachlesen. Beide Wege unterscheiden sich jedoch deutlich …
Der Name des Files lässt Sie ja schon erahnen, dass nicht alle kalendarischen Daten der einzelnen Blätter auch Umsatz-Daten enthalten. Was Sie aber an dieser Stelle nicht erkennen können isteine wichtige Voraussetzung: Die zu importierenden Daten müssen als „Intelligente” Tabelle (ListObject) formatiert sein. Und schon im Arbeitsblatt März werden Sie fündig, dass der letzte Eintrag am 8. März des Jahres erfolgte. Da sich aber naturgemäß im Laufe der Zeit die Umsatzdaten füllen (ergänzt werden), sollten schon im ersten Arbeitsschritt alle Blätter importiert werden. Alle, wirklich alle? Na ja… Das Blatt Feiertage ist nicht relevant, das brauchen wir (Sie) für diese Auswertung wirklich nicht. Aber an dieser Stelle des Ablaufs ist das (noch) nicht relevant. Erst einmal werden Sie dennoch alle Arbeitsblätter in die Abfrage importieren und anschließend das WorkSheet mit den Feiertagen per Filter entfernen. Nur Übung macht den Meister… 😆 Und wie das recht gut geht, sehen Sie in den folgenden Zeilen.
Auch wenn der Gedanke naheliegt, dass Sie jetzt erst ein neues Arbeitsblatt erstellen müssen: Das brauchen Sie nicht und sollen Sie auch nicht zu tun. – Und auch wenn Sie in Power Query sonst sehr viele Dinge per Mausklick erledigen können, hier ist vorzugsweise etwas Funktions-Arbeit angesagt. Und die Funktion werden Sie nicht (wie vielleicht gewohnt) über Spalte hinzufügen | Benutzerdefinierte Spalte einfügen. Gehen Sie in einem beliebigen Excel-Arbeitsblatt dieser Arbeitsmappe über Daten | Daten abrufen | Aus anderen Quellen | Leere Abfrage:
Der Power Query-Editor öffnet sich automatisch. Und ohne Ihr Zutun wurde im linken (und auch rechten) Seitenfenster eine neue Query mit dem Titel Abfrage1 erstellt:
Im linken Seitenfenster: Die neue, leere Abfrage
Tragen sie in das Textfeld (Editierzeile) folgende Funktion mit dem führenden Gleichheitszeichen ein:
= Excel.CurrentWorkbook()
Eingeben der Funktion zum Einlesen der Daten
Beachten Sie dabei die exakte Groß- Kleinschreibung, in Power Query ist praktisch alles „case sensitive”. Nach Enter oder einem Klick in den Freiraum unterhalb der Eingabezeile sehen Sie das Ergebnis der Funktion:
Das Ergebnis nach dem Aufruf der Funktion
Filtern Sie die Spalte Name nun so, dass der Eintrag tbl_Feiertage nicht mehr enthalten ist. Entweder entfernen Sie das Häkchen im Filter-Modus oder Sie wählen den Weg über Textfilter | Enthält nicht… . Erweitern Sie nun die Spalte Content durch einen Klick auf den Doppelpfeil 
in der Überschrift. Entfernen Sie auch das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden, dann nur noch OK. Nach einer Korrektur des Datentyps der ersten Spalte zu Datum und eventuell aufsteigender Sortierung des Datums haben Sie ein perfektes Abbild der Wunsch-Zusammenfassung. Und ach ja, die Spalte Name kann natürlich gelöscht werden. Und beim Stichwort „Name” fällt mir ein, dass ich der Abfrage1 auch einen „sprechenden” Namen geben sollte: Zusammenfassung. Nun noch Schließen & laden in… und wählen Sie Nur Verbindung erstellen. Im Moment brauchen Sie ja (noch) nicht die zusammengefassten Daten, hie ging es ja um eine erste Möglichkeit des Imports.
Aus der Sicht einer ordentlichen Datenhaltung ist es vollkommen in Ordnung, wenn ab dem 9. März nur noch leere Inhalte in den Produkt-Spalten sind. Zumindest was Plain Excel betrifft. Typischerweise wird ja täglich von Montag bis Samstag (und in der heutigen Zeit vielleicht auch Sonntag) ein weiterer Wert eingetragen. Für PQ gilt: Wenn Sie dennoch nur die aktuellen Daten sehen wollen, wird der einfachste Weg gewiss sein, wenn Sie händisch alle Daten, wo in Datum ein Zukunfts-Wert steht, per Filter entfernen. Und natürlich geht das auch PQ-automatisch. Versuchen Sie es gerne selber, auch wenn Sie vielleicht zu Beginn den einen oder anderen Misserfolg haben (ging mir auch so 😎 ). Alternativ können Sie mir eine E‑Mail senden; gegen eine Spende von 2,00 € (z.B. Amazon-Gutschein) werde ich Ihnen dann eine minimal kommentierte Datei mit meiner Lösung per E‑Mail zukommen lassen.
So weit, so gut. In dieser xlsx hat das ja prima geklappt. Und das wird auch in (wirklich) gleichartig aufgebauten Excel-Files gleichermaßen klappen. – Inzwischen ist einige Zeit ins Land gegangen und es liegt eine Datei mit den kompletten Jahresumsätzen vor. Laden Sie diese einfach einmal hier herunter und versuchen Sie Ihr Glück. 👿
Das Ergebnis nach dem Aufruf der Funktion ist ernüchternd…
Mein erster Gedanke nach meinem anfänglichen Versuch war der Lieblings-Ausspruch von Mr. Spock (TV-Serie Star Trek): „Faszinierend”. Wieso wird nur das Blatt importiert, welches ich eigentlich gar nicht haben will? Nur die Feiertage… Ich denke, dass Ihnen rasch klar wird, woran das liegt und wie Sie das ändern können (den Tipp gibt’s auch gratis per Rück-Mail 😉 ).
Aber stellen Sie sich einmal vor, dass es in dem zu importierenden Workbook nicht nur 12+1 Blätter sondern 50, 100 oder noch mehr Worksheets sind. Und die sollen „natürlich” nicht von extern in einer neuen Datei ausgewertet werden sondern wie gehabt in der gleichen Mappe. Eines steht fest, die Blätter müssen irgendwie andes, über einen anderen Weg eingelesen werden; die weitere Verarbeitung kann dann gleichartig erfolgen. Ich sage Ihnen hier nur dieses: Es ist eine andere M-Funktion, mit welcher Sie zum Erfolg kommen oder aber auch „einfach” über die GUI (Grafische Benutzeroberfläche), die zu Recht so beliebten Schaltflächen.
Okay, also noch einmal ganz von vorn. Die Ausgangslage ist die geöffnete Arbeitsmappe mit den 12 + 1 Registern: Januar bis Dezember und den Feiertagen. Aus diesen Daten soll in der gleichen Datei auf möglichst komfortable Weise eine Zusammenfassung aller Blätter mit Ausnahme des Excel-Registers Feiertage erfolgen. Und wie schon angesprochen könnten es in der Realität natürlich viel, viel mehr Arbeitsblätter sein, die komplett oder nach bestimmten Kriterien importiert werden sollen.
Vielen der älteren Leser meines Blogs ist Wilhelm Busch und da vor allen Dingen die Geschichte von Max und Moritz noch in Erinnerung. Und einer der woh am meisten daraus zitieten Sätze ist: „Dieses war der erste Streich, doch der zweite folgt sogleich.” Ich trenne an dieser Stelle den Beitrag und weiter geht’s dann hier im zweiten Teil. 😉
Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen … (← Klick mich!)