$ PQ: (Alle) Arbeitsblätter einer Arbeitsmappe in einem Arbeitsgang importieren

Xtract: Alle oder aus­gewählte Arbeits­blät­ter ein­er Mappe (Work­book) mit weni­gen Arbeitss­chrit­ten importieren. Die Basis-Ver­sion stelle ich Ihnen hier kosten­frei zur Ver­fü­gung, die erweit­erte Kom­fort-Ver­sion erhal­ten Sie gegen eine Spende von € 4,00.

  Wis­sens­stand: Lev­el 2 ⇒ Solides Basiswis­sen 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” deklar­i­ert, wenn logisch zusam­men­hän­gende Dat­en in ver­schiedene Arbeits­blät­ter aufgeteilt wer­den. Das ist dann die „reine Lehre”, die Prax­is sieht aber vielfach anders aus. Ich habe ein­mal auf der Basis mein­er Bäck­erei Klein­brot die Monats­dat­en „auseinan­der gepflückt” und in sep­a­rate Arbeits­blät­ter verteilt. Diese Aufteilung entspricht dur­chaus gängiger Prax­is. Sie kön­nen diese Muster-Datei hier herun­ter­laden.

Ich räume ein, dass es aus Excel-Sicht gewiss nicht den Regeln ein­er geord­neten Daten­hal­tung entspricht. Es ist nun aber ein­mal so, dass ‑aus welchen Grün­den auch immer- Dat­en bei der Eingabe in Monats- oder Kalen­der­wochen-Blät­ter einge­tra­gen wer­den. Das sind dann typ­is­cher­weise die Blät­ter für Jan­u­ar bis Dezem­ber oder für die Kalen­der­wochen des Jahres. Ich selb­st vertrete eine nicht so strin­gente Mei­n­ung; wenn der Chef es so will, soll er es so haben, denn er bezahlt mich und meine Arbeit. Und wenn ver­schiedene Außen­lager ihren Bestand als Excel Datei zur Zen­trale schick­en, ist das schon fast ein notwendi­ges Erforder­nis.

Vor eini­gen Jahren hat­te ich bere­its für Schu­lun­gen und Sem­i­nare diese Excel-Datei angelegt. Falls noch nicht geschehen, laden Sie das File herunter und spe­ich­ern Sie es auf Ihrer Fest­plat­te. Merken Sie sich den Spe­icherort, denn Sie brauchen ihn gle­ich. 😉

Falls noch nicht geschehen, öff­nen bzw. erstellen Sie eine neue, leere Arbeitsmappe. Hier hinein sollen dann die Dat­en aller 12 Monate in der eben gespe­icherten Datei importiert wer­den. Hin­weis: Alter­na­tiv kön­nen Sie natür­lich auch die Datei direkt öff­nen und die Zusam­men­fas­sung, den Import der einzel­nen Monate in ein weit­eres, neues Arbeits­blatt platzieren. Aber das würde dem tief­er­en Sinn dieses Beitrags wider­sprechen.

Um es gle­ich im Vor­wege zu sagen: Wenn Sie die herun­terge­ladene Datei öff­nen und glauben, dass Sie alle Monats-Blät­ter markieren (grup­pieren) und dann per Dat­enAus Tabelle/Bereich (oder ähn­lich) importieren kön­nen, dann wer­den Sie rasch erken­nen, dass jegliche Schalt­fläche aus­ge­graut und somit nicht wählbar ist. 😥

▲ nach oben …

Import in eine leere Datei

Nor­maler­weise wer­den Sie sich auch tat­säch­lich in ein­er kom­plett leeren Arbeitsmappe befind­en. Und wenn schon Blät­ter mit Dat­en existieren, soll­ten es nicht die später zu ver­ar­bei­t­en­den Dat­en sein. Wählen Sie im Menü Dat­en das Sym­bol Dat­en abrufen | Aus Datei | Aus Arbeitsmappe und im Datei-Dia­log navigieren Sie zu jen­em File, welch­es Sie eben nach dem Down­load gespe­ichert haben. Wenn Sie dieses File per Markierung und Importieren oder Dop­pelk­lick auswählen, erken­nen Sie nach dem laden diesen Dia­log:

Der Import-Dia­log für mehrere Arbeits­blät­ter

Wenn Sie die Ein­träge unter­halb des Namens für den Ord­ner durchzählen wer­den Sie rasch erken­nen, dass es 14 Zeilen sind. Das erk­lärt auch die Zahl in den eck­i­gen Klam­mern hin­ter dem Fold­er-Namen in der ersten Zeile. Aber wieso 14? Zwölf Monate plus ein Arbeits­blatt für die Feiertage sind in der Mappe. Das macht nach Adam Riese 13. Nach kurz­er Analyse wird Ihnen auf­fall­en, dass der Ein­trag Feiertage zwei Mal vorhan­den ist; an erster Posi­tion in der Liste mit dem Anhängsel 1 und ein weit­eres Mal an alpha­betisch kor­rek­ter Posi­tion nach dem Feb­ru­ar. Einen weit­eren entschei­den­den Unter­schied wer­den Sie fest­stellen: Die bei­den Sym­bole links des Namens unter­schei­den sich; das Sym­bol des ersten Ein­trags hat ober­halb des Rasters einen blauen Streifen und ist dadurch als Intel­li­gente Tabelle gekennze­ich­net. Der zweite Ein­trag hat unter­halb des Git­ters zwei sym­bol­is­che Reg­is­terkarten, wodurch erkennbar wird, dass Pow­er Query keine Tabelle und keinen definierten Bere­ich, son­dern nur ein Tabel­len­blatt erken­nt.

Klick­en Sie ober­halb des Drop­Downs Anzeigeop­tio­nen ▼ in das Kästchen Mehrere Ele­mente auswählen, markieren Sie dann zuerst April, Shift und anschließend Sep­tem­ber. Bei mir war von Anfang an die „Macke”, dass der zum Schluss angek­lick­te Ein­trag so gut wie immer nicht mit markiert ist. Also ein weit­er­er Klick um auch diesen Monat zu markieren und anschließend kön­nen Sie noch ein­mal auf den immer noch vorhan­de­nen und markierten Ein­trag Feiertage Klick­en, um diesen zu demarkieren, also das Häkchen zu ent­fer­nen; die an ober­ster Posi­tion ste­hende Tabelle Feiertage1 hat­ten Sie ja nicht markiert. Nun ein Klick auf Dat­en trans­formieren und die Dat­en aller Monate wer­den nacheinan­der in den Abfrage-Edi­tor geladen.

Na ja, jet­zt sind zwar alle Dat­en in Pow­er Query erfasst aber es existiert für jeden Monat eine einzelne Abfrage. Falls Sie das linke Seit­en­fen­ster einge­blendet haben,¿ ist das auch dort erkennbar. Das Ziel ist ja, dass die Dat­en des ganzen Jahres in ein­er einzi­gen Abfrage erfasst sein sollen. Darum markieren Sie im linken Seit­en­fen­ster erst ein­mal die Abfrage April, wählen dann im Menü Start das Sym­bol Kom­binieren bzw. erweit­ern Sie in der Gruppe Kom­binieren den Punkt Abfra­gen anfü­gen und wählen dort Abfra­gen als neu anfü­gen. (Und ja, es geht auch anders…)

Es sollen ja Drei oder mehr Tabellen ange­führt wer­den, aktivieren Sie darum genau diesen Option-But­ton. Erste Tabelle in Kas­ten Anzufü­gende Tabellen kann April bleiben und bei Ver­füg­bare Tabellen markieren Sie alle restlichen Monate. Ein Klick auf OK und die Dat­en wer­den in den recht­en Kas­ten des Dialogs einge­tra­gen. In neueren Ver­sio­nen wer­den Sie wahrschein­lich eine größere Auswahl an Import­möglichkeit­en haben; schauen Sie, was für Sie passend ist.

Die Rei­hen­folge der Dat­en in der Query ist naturgemäß nach Monat­en in alpha­betis­ch­er Rei­hen­folge und es ste­ht auch immer noch die Zeile Summe unter­halb jedes Monats in der Spalte Datum drin. Zu diesem Zeit­punkt ist es aber nicht ganz so rel­e­vant, diese „Bere­ini­gung” wer­den Sie durch­führen, wenn Sie die näch­sten Abschnitte durchgear­beit­et haben. Die hier fol­gen­den Schritte sind näm­lich für bei­de Vorge­hensweisen weit­ge­hend iden­tisch.

Um einen weit­eren, zweit­en Weg zum ein­le­sen, zum importieren der Dat­en ken­nen­zuler­nen gehen Sie über Schließen & laden in… und wählen ide­al­er­weise Nur Verbindung erstellen. Danach spe­ich­ern Sie die Datei und schließen danach erst ein­mal diese Arbeitsmappe. Und merken Sie sich den Namen, denn Sie wer­den ihn vielle­icht später noch ein­mal brauchen. Ich schlage Ihnen als Namen Kleinbrot_Alle_(1) vor.

▲ nach oben …

Zusätzliches Arbeitsblatt

Wech­seln Sie in Excel zum Menü Dat­en | Dat­en abrufen | Aus anderen Quellen | Leere Abfrage und es öffnet sich ein unge­wohnt leer­er Pow­er Query-Edi­tor. Tra­gen Sie in die (leere) Edi­ti­er-Zeile diesen Code (mit dem führen­den Gle­ich­heit­sze­ichen) ein: 
= Excel.CurrentWorkbook()
und Klick­en Sie dann in den freien Bere­ich unter­halb der Zeile oder ein­fach nur Enter  (Return, Zeilen­schal­tung) oder auf das Häkchen (links der Edi­tierzeile, neben fx). Das Ergeb­nis stellt sich so dar:

Nur 1 Zeile mit 1 Tabelle ‘Feiertage’? …

Aha… Erweit­ern Sie gerne ein­mal die Über­schrift Con­tent und Sie wer­den rasch erken­nen, dass dort nur die Tabelle mit den Feierta­gen extrahiert wird. Und mit etwas Forschergeist (beispiel­sweise auch der Analyse der ersten Abbil­dung) wer­den Sie fest­stellen, dass in dieser Mappe nur 1 „echte” Tabelle existiert, die Monats-Blät­ter sind durch­weg wed­er Benan­nte Bere­iche noch Intel­li­gente Tabellen. Hier kön­nte Microsoft aus mein­er Sicht etwas nachbessern und die Nur-Arbeits­blät­ter mit ein­beziehen.

Und wie kom­men Sie nun an die anderen Dat­en aus den Arbeits­blät­tern der einzel­nen Monate? Sie ken­nen das gewiss von Excel, fast immer gibt es mehrere Möglichkeit­en das Ziel nach ein­er Meth­ode zu erre­ichen, die Ihnen (dem Anwen­der) logisch vorkommt und die auch gut nachvol­lziehbar ist. Grund­sät­zlich biete ich Ihnen noch zwei Möglichkeit­en an. Die erste ist so nahe­liegend, dass Sie (eigentlich) sel­ber darauf kom­men kön­nten und prinzip­iell auch soll­ten:

Über einen kleinen Umweg …

So würde sich die Abfrage nach dem Import darstellen, wenn Sie prinzip­iell auf dem bish­er gegan­genen Weg bleiben wollen.

Die zweite von mir vorgestellte Möglichkeit hat den Charme, mit weni­gen Aktio­nen, sprich einzel­nen Angewen­de­ten Schrit­ten im Pow­er Query-Edi­tor auszukom­men; mit auss­chließlich den in der Abbil­dung hierunter gezeigten Aktio­nen sind Sie am Ziel und auch die Rei­hen­folge der Monate stimmt sog­ar:

Auch kom­plett andere Wege kön­nen zum Ziel führen

Nun möcht­en Sie vielle­icht auch diese bei­den Wege ken­nen ler­nen. Gerne, aber

Ein wichtiger Hin­weis
Der Link auf den Folge-Beitrag begin­nt mit „$$”. Das bedeutet für alle der­ar­tig aus­geze­ich­neten Beiträge, dass diese kostenpflichtig sind (eine geringe Spende).
Näheres dazu in der ange­sproch­enen Mail. Auch Wis­sen hat einen Wert!

 

Wie eben schon dargelegt habe ich auch eine aus­führliche Beschrei­bung meines Lösungsweges in bekan­nter, aus­führlich­er Form erstellt. Eine E‑Mail an mich mit dem Stichwort/Betreff $$ (IV) PQ: (Alle) Arbeits­blät­ter ein­er Arbeitsmappe in einem Arbeits­gang importieren und Sie bekom­men von mir den entsprechen­den Link sowie das erforder­liche Pass­wort zum öff­nen des Beitrages, sofern Sie mir eine kleine Spende von 4,00€ (Über­weisung, Donate-But­ton oder Pay­Pal Fre­und­schaft) haben zukom­men lassen.

▲ nach oben …

Dieser Beitrag wurde unter Daten zusammenführen, Datum & Zeit, Filtern & Sortieren, Power Query, PQ-Formeln (Sprache M), Spende/Donation abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.