Xtract: Alle oder ausgewählte Arbeitsblätter einer Mappe (Workbook) mit wenigen Arbeitsschritten importieren. Die Basis-Version stelle ich Ihnen hier kostenfrei zur Verfügung, die erweiterte Komfort-Version erhalten Sie gegen eine Spende von € 4,00.
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Alle oder auswählbare Arbeitsblätter (Register) eines Workbooks automatisch mit Power Query importieren
In fast allen Excel-Foren wird es als fast schon „unanständig” deklariert, wenn logisch zusammenhängende Daten in verschiedene Arbeitsblätter aufgeteilt werden. Das ist dann die „reine Lehre”, die Praxis sieht aber vielfach anders aus. Ich habe einmal auf der Basis meiner Bäckerei Kleinbrot die Monatsdaten „auseinander gepflückt” und in separate Arbeitsblätter verteilt. Diese Aufteilung entspricht durchaus gängiger Praxis. Sie können diese Muster-Datei hier herunterladen.
Ich räume ein, dass es aus Excel-Sicht gewiss nicht den Regeln einer geordneten Datenhaltung entspricht. Es ist nun aber einmal so, dass ‑aus welchen Gründen auch immer- Daten bei der Eingabe in Monats- oder Kalenderwochen-Blätter eingetragen werden. Das sind dann typischerweise die Blätter für Januar bis Dezember oder für die Kalenderwochen des Jahres. Ich selbst vertrete eine nicht so stringente Meinung; wenn der Chef es so will, soll er es so haben, denn er bezahlt mich und meine Arbeit. Und wenn verschiedene Außenlager ihren Bestand als Excel Datei zur Zentrale schicken, ist das schon fast ein notwendiges Erfordernis.
Vor einigen Jahren hatte ich bereits für Schulungen und Seminare diese Excel-Datei angelegt. Falls noch nicht geschehen, laden Sie das File herunter und speichern Sie es auf Ihrer Festplatte. Merken Sie sich den Speicherort, denn Sie brauchen ihn gleich. 😉
Falls noch nicht geschehen, öffnen bzw. erstellen Sie eine neue, leere Arbeitsmappe. Hier hinein sollen dann die Daten aller 12 Monate in der eben gespeicherten Datei importiert werden. Hinweis: Alternativ können Sie natürlich auch die Datei direkt öffnen und die Zusammenfassung, den Import der einzelnen Monate in ein weiteres, neues Arbeitsblatt platzieren. Aber das würde dem tieferen Sinn dieses Beitrags widersprechen.
Um es gleich im Vorwege zu sagen: Wenn Sie die heruntergeladene Datei öffnen und glauben, dass Sie alle Monats-Blätter markieren (gruppieren) und dann per Daten | Aus Tabelle/Bereich (oder ähnlich) importieren können, dann werden Sie rasch erkennen, dass jegliche Schaltfläche ausgegraut und somit nicht wählbar ist. 😥
Import in eine leere Datei
Normalerweise werden Sie sich auch tatsächlich in einer komplett leeren Arbeitsmappe befinden. Und wenn schon Blätter mit Daten existieren, sollten es nicht die später zu verarbeitenden Daten sein. Wählen Sie im Menü Daten das Symbol Daten abrufen | Aus Datei | Aus Arbeitsmappe und im Datei-Dialog navigieren Sie zu jenem File, welches Sie eben nach dem Download gespeichert haben. Wenn Sie dieses File per Markierung und Importieren oder Doppelklick auswählen, erkennen Sie nach dem laden diesen Dialog:
Der Import-Dialog für mehrere Arbeitsblätter
Wenn Sie die Einträge unterhalb des Namens für den Ordner durchzählen werden Sie rasch erkennen, dass es 14 Zeilen sind. Das erklärt auch die Zahl in den eckigen Klammern hinter dem Folder-Namen in der ersten Zeile. Aber wieso 14? Zwölf Monate plus ein Arbeitsblatt für die Feiertage sind in der Mappe. Das macht nach Adam Riese 13. Nach kurzer Analyse wird Ihnen auffallen, dass der Eintrag Feiertage zwei Mal vorhanden ist; an erster Position in der Liste mit dem Anhängsel 1 und ein weiteres Mal an alphabetisch korrekter Position nach dem Februar. Einen weiteren entscheidenden Unterschied werden Sie feststellen: Die beiden Symbole links des Namens unterscheiden sich; das Symbol des ersten Eintrags hat oberhalb des Rasters einen blauen Streifen und ist dadurch als Intelligente Tabelle gekennzeichnet. Der zweite Eintrag hat unterhalb des Gitters zwei symbolische Registerkarten, wodurch erkennbar wird, dass Power Query keine Tabelle und keinen definierten Bereich, sondern nur ein Tabellenblatt erkennt.
Klicken Sie oberhalb des DropDowns Anzeigeoptionen ▼ in das Kästchen Mehrere Elemente auswählen, markieren Sie dann zuerst April, Shift und anschließend September. Bei mir war von Anfang an die „Macke”, dass der zum Schluss angeklickte Eintrag so gut wie immer nicht mit markiert ist. Also ein weiterer Klick um auch diesen Monat zu markieren und anschließend können Sie noch einmal auf den immer noch vorhandenen und markierten Eintrag Feiertage Klicken, um diesen zu demarkieren, also das Häkchen zu entfernen; die an oberster Position stehende Tabelle Feiertage1 hatten Sie ja nicht markiert. Nun ein Klick auf Daten transformieren und die Daten aller Monate werden nacheinander in den Abfrage-Editor geladen.
Na ja, jetzt sind zwar alle Daten in Power Query erfasst aber es existiert für jeden Monat eine einzelne Abfrage. Falls Sie das linke Seitenfenster eingeblendet haben,¿ ist das auch dort erkennbar. Das Ziel ist ja, dass die Daten des ganzen Jahres in einer einzigen Abfrage erfasst sein sollen. Darum markieren Sie im linken Seitenfenster erst einmal die Abfrage April, wählen dann im Menü Start das Symbol Kombinieren bzw. erweitern Sie in der Gruppe Kombinieren den Punkt Abfragen anfügen und wählen dort Abfragen als neu anfügen. (Und ja, es geht auch anders…)
Es sollen ja Drei oder mehr Tabellen angeführt werden, aktivieren Sie darum genau diesen Option-Button. Erste Tabelle in Kasten Anzufügende Tabellen kann April bleiben und bei Verfügbare Tabellen markieren Sie alle restlichen Monate. Ein Klick auf OK und die Daten werden in den rechten Kasten des Dialogs eingetragen. In neueren Versionen werden Sie wahrscheinlich eine größere Auswahl an Importmöglichkeiten haben; schauen Sie, was für Sie passend ist.
Die Reihenfolge der Daten in der Query ist naturgemäß nach Monaten in alphabetischer Reihenfolge und es steht auch immer noch die Zeile Summe unterhalb jedes Monats in der Spalte Datum drin. Zu diesem Zeitpunkt ist es aber nicht ganz so relevant, diese „Bereinigung” werden Sie durchführen, wenn Sie die nächsten Abschnitte durchgearbeitet haben. Die hier folgenden Schritte sind nämlich für beide Vorgehensweisen weitgehend identisch.
Um einen weiteren, zweiten Weg zum einlesen, zum importieren der Daten kennenzulernen gehen Sie über Schließen & laden in… und wählen idealerweise Nur Verbindung erstellen. Danach speichern Sie die Datei und schließen danach erst einmal diese Arbeitsmappe. Und merken Sie sich den Namen, denn Sie werden ihn vielleicht später noch einmal brauchen. Ich schlage Ihnen als Namen Kleinbrot_Alle_(1) vor.
Zusätzliches Arbeitsblatt
Wechseln Sie in Excel zum Menü Daten | Daten abrufen | Aus anderen Quellen | Leere Abfrage und es öffnet sich ein ungewohnt leerer Power Query-Editor. Tragen Sie in die (leere) Editier-Zeile diesen Code (mit dem führenden Gleichheitszeichen) ein:
= Excel.CurrentWorkbook()
und Klicken Sie dann in den freien Bereich unterhalb der Zeile oder einfach nur Enter (Return, Zeilenschaltung) oder auf das Häkchen (links der Editierzeile, neben fx). Das Ergebnis stellt sich so dar:
Nur 1 Zeile mit 1 Tabelle ‘Feiertage’? …
Aha… Erweitern Sie gerne einmal die Überschrift Content und Sie werden rasch erkennen, dass dort nur die Tabelle mit den Feiertagen extrahiert wird. Und mit etwas Forschergeist (beispielsweise auch der Analyse der ersten Abbildung) werden Sie feststellen, dass in dieser Mappe nur 1 „echte” Tabelle existiert, die Monats-Blätter sind durchweg weder Benannte Bereiche noch Intelligente Tabellen. Hier könnte Microsoft aus meiner Sicht etwas nachbessern und die Nur-Arbeitsblätter mit einbeziehen.
Und wie kommen Sie nun an die anderen Daten aus den Arbeitsblättern der einzelnen Monate? Sie kennen das gewiss von Excel, fast immer gibt es mehrere Möglichkeiten das Ziel nach einer Methode zu erreichen, die Ihnen (dem Anwender) logisch vorkommt und die auch gut nachvollziehbar ist. Grundsätzlich biete ich Ihnen noch zwei Möglichkeiten an. Die erste ist so naheliegend, dass Sie (eigentlich) selber darauf kommen könnten und prinzipiell auch sollten:
Über einen kleinen Umweg …
So würde sich die Abfrage nach dem Import darstellen, wenn Sie prinzipiell auf dem bisher gegangenen Weg bleiben wollen.
Die zweite von mir vorgestellte Möglichkeit hat den Charme, mit wenigen Aktionen, sprich einzelnen Angewendeten Schritten im Power Query-Editor auszukommen; mit ausschließlich den in der Abbildung hierunter gezeigten Aktionen sind Sie am Ziel und auch die Reihenfolge der Monate stimmt sogar:
Auch komplett andere Wege können zum Ziel führen
Nun möchten Sie vielleicht auch diese beiden Wege kennen lernen. Gerne, aber
Ein wichtiger Hinweis
Der Link auf den Folge-Beitrag beginnt mit „$$”. Das bedeutet für alle derartig ausgezeichneten Beiträge, dass diese kostenpflichtig sind (eine geringe Spende).
Näheres dazu in der angesprochenen Mail. Auch Wissen hat einen Wert!
Wie eben schon dargelegt habe ich auch eine ausführliche Beschreibung meines Lösungsweges in bekannter, ausführlicher Form erstellt. Eine E‑Mail an mich mit dem Stichwort/Betreff $$ (IV) PQ: (Alle) Arbeitsblätter einer Arbeitsmappe in einem Arbeitsgang importieren und Sie bekommen von mir den entsprechenden Link sowie das erforderliche Passwort zum öffnen des Beitrages, sofern Sie mir eine kleine Spende von 4,00€ (Überweisung, Donate-Button oder PayPal Freundschaft) haben zukommen lassen.