Xtract: Mehrere oder auch alle Register (Arbeitsblätter) einer Arbeitsmappe in die gleiche Mappe gezielt importieren und auch die Blattnamen gezielt auswerten. (Teil 2)
Wissensstand: Level 3 ⇒ Excel GUT!, mindestens Basis-Kenntnisse in Power Query
Alle oder auch nur ausgewählte Arbeitsblätter/Register der aktuellen Mappe als PQ-Abfrage importieren Teil 2
Der folgende Text basiert auf dem ersten Beitrag zu diesem Thema und schließt nahtlos daran an. Die Datenlage hat sich nur leicht verändert, die Vorgehensweise ist hier eine deutlich andere.
Okay, also noch einmal ganz von vorn. Die Ausgangslage ist die geöffnete Arbeitsmappe mit den 12 + 1 Registern: Januar bis Dezember und Feiertage. Diese hatten Sie hier heruntergeladen. 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 wohl 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. 
Im ersten Schritt erstellen Sie in Excel ein neues Arbeitsblatt und benennen Sie dieses zweckmäßigerweise Zusammenfassung. Im Folgenden gehe ich davon aus, dass die ursprüngliche Excel-Datei in folgendem Pfad liegt: C:\Data\ und Sie im aktuellen Workbook die Daten auch von dort geöffnet haben. Wie schon vorher beschrieben können Sie den Weg über eine PQ-Funktion gehen. Nach dem Erstellen einer neuen, leeren Abfrage (Daten abrufen | Aus anderen Quellen | Leere Abfrage) tragen Sie dazu in die Eingabezeile folgende Funktion ein:
= Excel.Workbook(File.Contents("C:\Data\Kleinbrot_12_Monate.xlsx"), null, true)
(den Pfad müssen Sie naturgemäß bei Bedarf anpassen) und es zeigt sich anschließend dieser Abfrage-Dialog:
Der Import-Dialog direkt nach dem Aufruf der Funktion
Was mich anfangs mächtig irritierte ist die Tatsache, dass das Blatt Zusammenfassung gar nicht mit aufgenommen wurde. Okay, es sind ja noch keine Daten enthalten, aber es machte mich doch nachdenkleich. Schnell wurde mir aber klar, dass das Blatt gar nicht importiert werden konnte. Denn mit der Befehlszeile habe ich ja auf die gespeicherte Version der Datei zugegriffen, und dort ist ja noch der Zustand ohne das hinzugefügte Arbeitsblatt! Und noch etwas fällt mir auf: In Zeile 13 und Zeile 14 gibt es die Namen Feiertage und Feiertage1, in der Spalte Item ist der Eintrag dieser beiden Zeilen sogar identisch. Allerdings finden Sie die Erklärung in der Spalte Kind, denn ein Mal ist es das Arbeitsblatt welches angesprochen wird und das andere Mal die „intelligente” Tabelle.
Einen gleichen Abfrage-Dialog können Sie mit einigen Schritten mehr aber ohne die M-Funktion direkt eingeben zu müssen erreichen, wenn sie sich in Excel (beispielsweise) im Arbeitsblatt Zusammenfassung befinden und über Daten | Daten abrufen | Aus Datei | Als Excel-Arbeitsmappe exakt die aktuelle, zurzeit hier geöffnete Datei im File-Dialog auswählen. Da zeigt sich zu Beginn der Navigator:
Alle Tabellen und Tabellenblätter der zu impotierenden Datei
Da Sie ja unter Umständen sehr, sehr viele Blätter importieren wollen bietet es sich an, erst einmal alle existierenden Sheets der Mappe in eine Abfrage zu importieren, statt Mehrere Elemente auswählen anzuhaken und dann die einzelnen zu importirenden Blätter/Tabellen mit einzelnen Klicks zu markieren. Dazu Klicken Sie ausschließlich auf dem ersten Eintrag mit dem gelben Ordner-Symbol:
Diese Zeile markieren, um alle Elemente der Liste zu importieren
…und anschließend Laden ▼ | Laden in… | Nur Verbindung erstellen, um die Daten aller Arbeitsblätter zu importieren, ohne sie gleich in da aktuelle oder ein neues Arbeitsblatt zu speichern. Zu diesem Zeitpunkt haben Sie wiederum den gleichen Dialog wie schon vorher beim Thema M-Funktion beschrieben. Eine Ausnahme zeigt sich aber: Der Name der Abfrage ist nicht Tabelle1 sondern entspricht dem Namen des importieren Files: Kleinbrot_12_Monate xlsx.
Okay, Sie wissen natürlich, dass die Feiertage nicht gebraucht werden. Aber in einer anderen Datei können das ja ganz anders benannte Blätter sein, beispielsweise „Arbeitsfrei” oder weitere Blätter mit beliebigen Bezeichnungen. In dieser vorliegenden Konstellation können Sie auch nach einem anderen Kriterium filtern: Die Spalte Kind werden Sie so filtern, dass ausschließlich Sheet erhalten bleibt. Beachten Sie, dass in der Spalte Table alle Einträge identisch sind.
Übrigens: Wenn Sie in der Spalte Data in die Freifläche neben dem Eintrag Table Klicken, wird unterhalb der Abfrage-Tabelle direkt über der Statuszeile eine Tabelle angezeigt, wo der Inhalt dieses Arbeitsblatts angezeigt wird. Für Kontrollzwecke kann dieses ganz hilfreich sein. – Filtern Sie nun noch die Spalte Name oder Item so, dass nur die Monatsnamen erhalten bleiben.
Löschen Sie nun alle Spalten außer Data. Erweitern Sie diese Spalte und nach Übernahme der Vorgabe Start | Erste Zeile als Überschrift verwenden. Einige Zeilen müssen nun noch gelöscht werden, damit nur die relevanten Daten übrig bleiben. Zu löschen sind leere Zeilen und jene Zeilen, wo in Spalte Datum die ursprünglichen Überschriften (Summe und Datum) stehen. Der einfachste Schritt wird sein, im Filter bei den entsprechenden Einträgen das Häkchen zu entfernen. Aber es gibt noch einen anderen Weg, der manchmal hilfreicher sein kann. Da ja in der ersten Spalte ausschließlich kalendarischen Daten stehen sollen und stehen dürfen, sind ja alle anderen Werte zu entfernen. Ich zeige Ihnen hier 2 pragmatische Möglichkeiten auf:
- Ändern Sie den Datentyp in Spalte Datum von Beliebig auf Datum. Anschließend per Filter die null-Einträge (das sind auch leere Zeilen) und die Fehler (Error) entfernen. Fertig!
- Etwas aufwändiger aber auch interessant: Spalte hinzufügen | Benutzerdefinierte Spalte. Die Überschrift kann bleiben oder auch beispielsweise Check oder Prüfung sein. Als Benutzerdefinierte Spaltenformel geben Sie ein:
Value.Is([Datum], type date)
Da weder Texte noch leere Zellen den Datentyp Date haben können, werden die entsprechenden Zeilen in der neuen Spalte mit False gekennzeichnet; bei allen echten Datumswerten steht entsprechend True. Jetzt nach True filtern und es bleiben nur noch jene Zeilen in der Abfrage erhalten, wo in der ersten Spalte wirklich ein Datum steht.
Und wenn in jeder Zeile Error steht, was dann und warum? Erst einmal das „warum”: Die Spalte Datum hat (gewiss immer noch) den Datentyp Beliebig; und das gilt für jeden Datensatz (Zeile), auch wenn dort schön rechtsbündig ein Datum steht. Die Lösung: Entweder (wie vorher schon angesprochen) den Datentyp auf Datum ändern oder die eingegebene Formel so ändern: = Value.Is(Date.From([Datum]), type date)
und anschließend zuerst über Start | Zeilen verringern die Fehler entfernen und anschließend nach TRUE filtern.
Zugegeben, ich setze bei solchen Gelegenheiten eigentlich immer die erste Methode ein. Sie ist leichter zu habdhaben. Der zweigenannte Weg hat aber auch seinen Charme; insbesondere dann, wenn die Formel noch einmal dahingehend verändert wird, dass in der neuen Spalte entewder das Datum aus der gleichnamigen Spalte oder einen Error-Wert enthält, wobei anschließend diese Zeilen bequen geöscht werden können. Wenn Sie an der Formel interessiert sind, senden Sie mir eine Mail mit dem Betreff Kleinbrot-Nur Datum filtern und gegen eine Spende von 2,00 € (Amazon-Gutschein oder PayPal Freundschaft für G.Mumme@Excel-ist-sexy.de oder Sie fragen nach der Bankverbindung) sende ich Ihnen eine Mail mit der entsprechenden Formel.
Zum Schluss können Sie nun noch alle Spalten rechts von Sonstiges löschen und (vorsichtshalber) die erste Spalte aufsteigend nach Datum sortieren. Damit ist das Ziel so gut wie erreicht. Bleiben eigentlich nur noch 2 Dinge: Die Spalte Datum eventuell noch als Datentyp Datum formatieren und die als Nur Verbindung gespeicherte Abfrage in das Blatt Zusammenfassung zu positionieren. Erforderlichenfalls finden Sie hier einen Hinweis zum Handling.
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. 3,00 € freuen … (← Klick mich!)