Alle Excel-Dateien eines Ordners mit gleichem Aufbau in ein Blatt einer neuen Mappe einlesen
Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
Wie solch eine Aufgabe per VBA zu lösen ist, können Sie beispielsweise hier in unserem Blog nachlesen. „Zu Fuß” ist gewiss auch keine Option für Sie, sonst würden Sie hier nicht nachschauen. Es geht mit Power Query vollkommen ohne eine Zeile VBA-Code. Und Sie haben nach wie vor eine *.xlsx-Datei, was bei manchen restriktiven Einstellungen in Firmennetzwerken nicht zu unterschätzen ist. Dazu kommt, dass bei Änderungen keine Programmierkenntnisse erforderlich sind. – Um dieses Training nachvollziehen zu können, laden Sie bitte diese als *.zip gepackte Datei (enthält das komplette Verzeichnis mit diversen Files) herunter und entpacken sie in ein Laufwerk bzw. Verzeichnis Ihrer Wahl oder auf dem Desktop. Merken Sie sich diese Position.
Vorbereitung
Idealerweise öffnen oder erstellen Sie nun eine neue, leere Mappe. Hier hinein sollen die eben entpackten Dateien importiert werden. Bevor das geschieht, sehen Sie sich aber einmal das eben entpackte Verzeichnis an. Ihnen wird gewiss auffallen, dass da schon durch den Namen auffallend ein „Ausreißer” drin ist. Die Monate Januar bis Dezember sind die Umsätze der Bäckerei Kleinbrot und jeweils exakt gleich aufgebaut. Die Excel-Datei Bundestagsmitglieder hat sich irgendwie dorthin „verirrt” 😉 und passt in keiner Weise zu den Umsätzen. – Prinzipiell sollten Sie nun auch ein, zwei Files öffnen, um den Aufbau kennen zu lernen. Selbst wenn Sie diese eigenhändig erstellt haben, kann es in Firmen-Netzwerken schon einmal passieren, dass ein Kollege diese oder jene „Verbesserung” vorgenommen hat. Und zugegeben, wenn ich längere Zeit mit einer Excel-Mappe nicht gearbeitet habe, dann trügt die Erinnerung mitunter.
Der Import
Hinweis: Die Bilder und Einzelschritte dieses Trainings beziehen sich auf die Version Excel 2016. Der Haupt-Unterschied zu den früheren Versionen besteht darin, dass jetzt die einzelnen Punkte im Menü Daten (2016) zu finden sind, früherdirekt im Menüpunkt Power Query (2010/13).
Wählen Sie Neue Abfrage | Aus Datei | Aus Ordner:
Auswahl des zu importierenden Ordners
Sofort öffnet sich ein Dialog, wo Sie den Pfad des zu öffnenden Ordners per Hand eingeben oder per Schaltfläche auf Ihrem Rechner oder im Netzwerk aussuchen können:
Über die Schaltfläche ist der sicherere Weg …
Wenn Sie den Ordner bzw. das Verzeichnis nicht umbenannt haben, dann hat er den von mir vergebenen Namen Kleinbrot 12_Monate. Sie können übrigens ausschließlich den Ordner auswählen und sehen, nicht die darin enthaltenen Files. Bestätigen Sie mit OK:
Die Windows-typische Auswahl eines Verzeichnisses
Nach einem OK sind alle Excel-Dateien, die vom Aufbau her identisch sind, also nur die 12 Monate mit den Umsätzen der Bäckerei Kleinbrot in einer Tabelle erfasst. Korrektur: Es sind die Dateinamen und weitere Informationen. Sie werden aber die Datei mit den Bundestags-Abgeordneten in dieser Aufstellung vergeblich suchen. Die Liste eine Reihe von Filenamen mit den unterschiedlichsten Argumenten. Hinnehmen und abnicken, Sie brauchen für diese Aufgabe nicht das Wissen um die Details. 😎
Alle importierten Files in einer Tabelle
Klicken Sie auf Bearbeiten (ältere Versionen) oder auf Daten transformieren in neueren Versionen (hier liegt noch keine Abbildung vor) und alle hier angezeigten Dateien werden so importiert, dass Sie diese später bearbeiten oder ergänzen können. Und das geschieht in einem speziellen Editor, dem Abfrage- oder Query-Editor. Hinweis: Wenn Sie etwas geübter sind werden Sie vermutlich (wie auch ich) die Schaltfläche Laden erweitern und dann Laden in wählen, um dann die Option Nur Verbindung anzuklicken. Das ist flexibler.
Wenn Sie bereits einmal mit Power Query gearbeitet haben, kommt Ihnen das alles weitgehend bekannt vor. Nur dass Sie hier nicht so viel entscheiden konnten. Es kann aber auch nicht schaden, wenn Sie sich für andere Zwecke merken, welche Informationen in dieser Liste preisgegeben werden:
Diese Aufstellung umfasst alle zu importierenden Dateien
Im nächsten Schritt werden Sie in die Abfrage eine neue Spalte einfügen. Dazu wählen Sie den Menüpunkt Spalte hinzufügen und im Menüband gleich den ersten Menüpunkt Benutzerdefinierte Spalte hinzufügen. Wenn Sie dieses Element der Menüleiste anklicken, erscheint ein Dialog. Dort geben Sie im Feld Neuer Spaltenname beispielsweise Datei-Inhalt ein, es kann aber auch ein beliebiger anderer Wert sein. In das Feld Benutzerdefinierte Spaltenformel geben Sie eine Formel ein:
Ganz ohne Formeln geht es auch bei Power Query nicht immer
Das Gleichheitszeichen wird bereits vorgegeben, danach tragen Sie ein:
Excel.Workbook(
dann im Bereich Verfügbare Spalten entweder auf Content einen Doppelklick oder markieren und « Einfügen. Anschließend die schließende Klammer setzen und mit OK bestätigen. Hinweis: Die Groß- Kleinschreibung der Formel ist wichtig! Achten Sie im Dialogfenster unten links auf den Hinweis bezüglich eventueller Syntaxfehler. Augenblicklich wird die neue Spalte eingefügt und markiert:
Eine neue Spalte wurde zugefügt
Nun ja, so wirklich aussagekräftig ist der Inhalt der Spalte nun wirklich nicht. Table, Table, Table … Die Spaltenüberschrift ist aber jene, die Sie vorher so eingegeben haben. Rechts im Überschrift-Bereich ist das Symbol für ein Erweitern 
zu sehen. Klicken Sie ein Mal darauf. Es öffnet sich ein neues Dialogfenster:
Die Default-Auswahl ist genau richtig
Idealerweise belassen Sie es bei den gegebenen Einstellungen und Sie Klicken auf OK. Umgehend wird für jeden der angehakten Möglichkeiten in der Abfrage eine neue Spalte erzeugt, jede der neuen Spalten ist auch automatisch markiert:
Diverse neue Spalten sind nach dem Einfügen noch markiert
Auch hier ist es für den zu erzielenden Erfolg nicht relevant, die Bedeutung der einzelnen neuen Spaltenüberschriften zu kennen. Vielleicht ist es merkenswert, dass das Präfix (Datei-Inhalt) genau dem Namen der Ursprungs-Spalte entspricht. Das verhindert auch Verwechselungen, wenn beispielsweise eine Spalte der Importdaten auch Data als Überschrift hat. Diese Werte sind aber für den folgenden, mitentscheidenden Schritt wichtig.
Ich möchte mit Ihnen ein kleines Gedankenexperiment machen. Angenommen, Sie würden jetzt jede der in der Tabelle aufgelisteten Dateien direkt von Ihrer Festplatte oder aus dem Netzwerk nacheinander in eine Liste importieren. Was würde da ungemein stören, was würde nicht in die Aufstellung gehören? Wenn Sie sich die Ursprungsdaten noch einmal ins Gedächtnis rufen oder direkt nachsehen, dann gibt es eine einzige Zeile, die in jeder Tabelle nach dem Import überflüssig ist: Die Summe-Zeile.
Und die Überschrift, wie sieht es damit aus? Die ist nur in elf von zwölf Fällen überflüssig, denn ein Mal, in der ersten importierten Tabelle brauchen wir sie ja. Um die Überschrift nur ein einziges Mal zu importieren erstellen Sie wiederum eine neue Spalte. Den Weg kennen Sie: Menü Spalte hinzufügen | Benutzerdefinierte Spalte hinzufügen. Und auch das Dialogfenster kennen Sie schon. Geben Sie dort als Spaltenname Überschrift und bei Benutzerdefinierte Spaltenformel: erst einmal diese Anweisung ein:
Table.PromoteHeaders(
Wählen Sie im rechten Fensterbereich bei Verfügbare Spalten per Doppelklick oder über die Schaltfläche den Eintrag Datei-Inhalt.Data und der obligatorisch Kontrollblick nach unten …
In der Formel fehlt noch etwas …
… zeigt Ihnen, dass die Formel noch nicht vollständig ist. Ergänzen Sie die schließende runde Klammer, noch einmal nachschauen, ob nun alles in Ordnung ist und dann OK. Die benutzerdefinierte Spalte wird erstellt und das sieht nun so aus:
Die neue Spalte mit der gewünschten Überschrift
Dass der Inhalt der Zeilen solch einer Spalte nicht besonders aussagekräftig ist, das kennen Sie schon. Und Sie werden sich denken können, dass Sie als nächsten Schritt wiederum auf das Erweitern-Symbol in der Überschrift Klicken werden. Und natürlich auch ein Dialog, der sich nun aber situationsbedingt etwas anders darstellt:
Welche Spalten sollen übernommen werden?
Sie erkennen die einzelnen Spaltennamen, wie sie in den Monatsberichten vorhanden sind. Die letzte Spalte Summe brauchen Sie nicht in der Auswertung, also löschen Sie die Markierung. Und falls das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden gesetzt ist, entfernen Sie auch dieses, denn die Spaltennamen sollen unverändert übernommen werden. Das war’s, also OK.
Sofort werden alle gewählten Spalten und nun (endlich) die richtigen Daten in die Abfrage eingefügt. Die Spaltenüberschriften sind auch korrekt, also könnte der Import damit beendet sein:
Diese (markierten) Spalten werden für die Auswertung gebraucht
Ja, die Daten sind korrekt vorhanden. Aber da ist ja immer noch ein Wust von Spalten, die in der endgültigen Zusammenfassung nichts zu suchen haben. Sie brauchen ja nur die eben erstellten Spalten, mehr nicht. Wenn die Markierung (wie im Screenshot hierüber sichtbar) noch existiert belassen Sie es dabei, sonst Klicken Sie in die Überschrift Datum, Shift und ein Klick in die Überschrift Sonstiges; Die Überschrift Datum muss danach markiert sein, sonst kann die Reihenfolge der Spalten einfach vertauscht werden. Jetzt wie hier gezeigt vorgehen:
So löschen Sie die nicht markierten Spalten aus der Abfrage
Im Menü Start | Spalten entfernen | Andere Spalten entfernen anklicken, und es bleiben nur noch die gewünschten Spalten in der Abfrage. Natürlich können Sie auch die ersten Spalten bis vor Datum markieren und diese dann löschen, das bleibt Ihnen überlassen. Das Ergebnis ist identisch.
Wenn Sie nun in dem Fenster etwas nach unten scrollen werden Sie daran erinnert, dass Sie zwar die Spalte mit den Summen aber nicht die entsprechenden Zeilen entfernt haben. Zeile 31 ist solch ein Beispiel. Und sehen Sie sich doch einmal die Zeile 32 an. Komplett leer, nur null.
Leere Zeilen gehören ja nun einmal nicht in eine ordentliche Tabelle/Liste. Punkt (oder wie der Ex-Bundeskanzler Schröder wahrscheinlich von sich geben würde: „basta”). Darum werden Sie erst einmal über das Menü Start alle Leerzeilen löschen:
Alle komplett leeren Zeilen einfach gelöscht
Das klappt schon einmal hervorragend. Und selbstredend bleiben alle Sonntage erhalten, auch wenn in allen Produkten null vermerkt ist. Die Zeile ist nicht leer, da ein Datum in der ersten Spalte steht. – Bleibt noch, die Summe-Zeilen nach dem jeweiligen Ultimo zu entfernen. Und das geht ähnlich unproblematisch wie eben, aber über einen anderen Weg:
Das Häkchen bei „Summe” entfernen und OK
Folgendes Vorgehen ist eine gute Möglichkeit (von mehreren):
- Markieren Sie die Überschrift Datum oder Klicken Sie gleich auf das DropDown-Symbol
in der Überschrift. - Ziehen Sie in der Auswahl den Rollbalken (Scrollbar) ganz nach unten, damit der Eintrag Summe zu sehen ist.
- Entfernen Sie das Häkchen beim letzten Eintrag Summe
- Bestätigen Sie mit OK.
Jetzt bietet es sich natürlich von alleine an, in dieser Spalte im gleichen DropDown das Datum aufsteigend zu sortieren. Dieser Vorgang bedarf gewiss keiner Erklärung, Sie kennen das wahrscheinlich aus den Intelligenten Tabellen / Listen in Excel.
Fertig. (Endlich? 😎 ) Zum Abschluss also zur Übernahme der Daten aus der Abfrage Klicken Sie nun entweder auf den Text im Symbol Schliessen & laden (nicht auf das Bild) und dann Schliessen & laden in…:
Möglichkeit 1 zum Schließen und laden in…
Oder Sie Klicken auf den Menüpunkt Datei und wählen dann den zweiten Punkt in der Auswahl:
Der Weg zum gleichen Ziel über das Menü Datei
Da Sie ja im letzten Schritt Schliessen & laden in… gewählt haben, wird nicht automatisch ein neues Tabellenblatt erstellt sondern Ihnen wird angeboten, wo Sie denn die Daten speichern wollen:
Wo soll das Ergebnis der Abfrage gespeichert werden?
Im aktuellen Tabellenblatt gleich in A1, das passt schon. Laden und die Ergebnisse stehen an der definierten Stelle. Was allerdings vielleicht etwas irritiert: In Spalte A steht zwar der korrekte Wert des Datums, die Formatierung ist aber als serielle Zahl:
Die Erwartung war gewiss anders …
Natürlich wäre es „ein Klacks”, die Darstellung hier in der endgültigen Tabelle anzupassen. Aber das hätte einen entscheidenden Nachteil: Wenn Sie in der Abfrage im Nachhinein etwas ändern, dann werden Sie das Zahlenformat erneut anpassen müssen. Ein kleiner Trost: Bei einer Aktualisierung bleibt das aktuelle Format erhalten.
Sie sollten aber auch den „vernünftigen” Weg kennen lernen. Neben dem „gleich richtig machen” 😳 bleibt ja nur noch der Weg der Änderung in der Abfrage. Entweder ein Doppelklick auf den grün hinterlegten Bereich im rechten Seitenfenster:
Ein Doppelklick hier öffnet wieder die Abfrage
Oder Menü Abfragetools | Abfrage | Bearbeiten auswählen. So oder so öffnet sich wieder der Abfrage-Editor und Sie können dort weiter arbeiten, wo Sie ‑wann auch immer- aufgehört hatten. Das klappt auch dann, wenn Sie vor einem Jahr zuletzt mir der Datei gearbeitet haben. Dennoch stellt sich die Frage, warum in der eben erzeugten Tabelle die kalendarischen Daten nicht in der gewünschten Form dargestellt wurden. Hier in der Abfrage ist es doch klar ersichtlich ein Datum. Die rechtsbündige Ausrichtung spricht auch dafür. Und überhaupt, beim Sortieren hat Power Query (PQ) ja auch erkannt, dass es sich um kalendarische Daten handelt!
Ach ja, wenn doch immer alles so einfach wäre, wie es zu sein scheint. Die Wege des Bill Gates sind manchmal unergründlich. 🙄 Ich räume ein, dass ich beim ersten Versuch auch leicht irritiert war. Die Lösung ist auf den ersten Blick nicht unbedingt logisch, aber sie ist zielführend. Markieren Sie erst einmal die Spalte Datum. Im Menü Start, Gruppe Transformieren erkennen Sie, dass als Datentyp Beliebig vermerkt ist. Das trifft übrigens auch auf die restlichen Spalten zu. Und da habe ich mich auch gefragt, warum PQ bei anderen Importen den Datentyp meist korrekt erkennt. Gut, bei kalendarischen Daten wird fast immer Datum/Zeit erkannt, aber der Grundtyp ist ja gleich. Als Ausgleich für die Irritation biete ich Ihnen eine logische Erklärung: Beim erstmaligen Einlesen der Daten waren ja gemischte Datentypen in den Zeilen. Die Leerzeilen zählen dabei nicht, aber die „Summe” ist ja eindeutig Text. Darum der Datentyp Beliebig, der dann nicht mehr angepasst wurde.
Okay, ändern Sie den Datentyp der ersten Spalte auf Datum. Und wenn Sie genau hingesehen haben werden Sie eine ganz kleine Änderung gesehen haben: In der Überschrift links ist nicht mehr das Symbol für Beliebig sondern ein kleiner symbolischer Kalender, also Datum. Und wenn Sie schon einmal dabei sind, markieren Sie doch die Überschriften Brot, Shift (bis) Sonstiges und korrigieren Sie auch diesen Datentyp, allerdings auf Währung. Dann hat alles seine Ordnung. 😉
Die durch den Query-Editor erzeugte Tabelle existiert ja schon an der gewünschten Stelle, Sie haben die zugehörige Abfrage ja nur verändert. Darum reicht ein einfacher Klick auf Schliessen & laden um die Auswertungstabelle zu aktualisieren. Jetzt steht dort das Datum auch im korrekten Format.
Last but not least eine Anmerkung: Dass am 3. Oktober, einem Feiertag in Deutschland für jedes Produkt genau 100,00 € Umsatz gemacht worden sind, ist natürlich mehr als unwahrscheinlich. Diese Zahlen sind für eine andere Schulung „eingeschleust” worden, wo nach derartigen Unregelmäßigkeiten gesucht werden soll. 💡
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag (1,00€ bis 2,50€) Ihrerseits freuen …
Bundesweite ✉ Schulungen ✉ durch unseren Sponsor GMG Computer-Consulting