Angenommen, wir sind in einem Betrieb, der mehrere Filialen hat. Diese schicken regelmäßig Berichte, und die wiederum sollten identisch aufgebaut sein (also identische Spaltenanzahl und -beschriftungen) und dieselben Inhalte benutzen (also z.B. Koblenz (Stadt) und nicht Koblenz Stadt oder Koblenz, Stadt). Um sie auszuwerten reicht es nicht, diese Tabellen in z.B. Power Pivot einzulesen und zu verknüpfen (siehe auch den entsprechenden Artikel auf dieser Seite), sondern diese Tabellen muss man tatsächlich zu einer "Datentapete" zusammenführen. Wenn man diese Tabellen nun in einem speziellen Ordner abgelegt hat, in dem idealerweise nur diese Tabellen liegen, dann kann man das mit Hilfe von Excel recht einfach erledigen. Ich stelle hier einige mögliche Techniken vor. Alternativen, wie z.B. mit Hilfe einer VBA-Routine, behandle ich im Artikel "Exel Tabellen aneinanderfügen".
Power Query - Anfügen
Die anzufügenden Tabellen liegen in jeweils eigenen Dateien vor. In die Zieltabelle lädt man sie ein über Daten – Daten abrufen und transformieren – Aus Datei – Aus Arbeitsmappe. Man wählt zuerst die Datei und danach die interessierende Tabelle aus. Im Prüffeld wählen wir „Transformieren“ und prüfen, ob die Daten alle korrekt formatiert sind, das gilt besonders für Datumsangaben. Ist das nicht der Fall, kann man prüfen, ob ein im rechten Fenster (in Abb. 1 blau eingerahmt) dargestellter früherer Verlaufsschritt zu besseren Ergebnissen führt, indem man einfach daraufklickt. Dann werden die nachfolgenden Schritte einfach ignoriert. Wenn das der Fall ist, löschen wir die späteren, untenstehenden, Schritte. Wenn nicht, ändern wir den Datentyp. Im Menüband finden wir dazu unter Start – Transformieren das Drop-Down-Feld „Datentyp“ (in der Abbildung rot eingerahmt). Wenn man allerdings ein Eingabeformular verwendet, in welchem bestimmte Spalteninhalte errechnet werden, dann legt man vorsichtshalber mehr Zeilen an, als man erwartet. In diesen überschüssigen Zeilen stehen dann in den berechneten Zellen Formeln die sozusagen „ins Leere“ gehen (erkennbar am #NV). Diese Zeilen eliminiert man einfach durch Filtern. Wenn wir mit der Darstellung der einzelnen Felder zufrieden sind, gehen wir ganz links auf das kleine Dreieck unter Start – Schließen – Schließen & Laden (Abb.1., schwarzer Rahmen ). Dort wählen wir Schließen & Laden in … – Nur Verbindung erstellen.
Auf diese Weise binden wir alle relevanten Tabellen ein, ohne sie darzustellen. Wenn wir das getan haben, sind im Menüband 2 weitere Optionen verfügbar: Tabellen- und Abfragetools. Wir wählen Abfragetools – Anfügen (in Abb. 2 rot und schwarz umrandet). Dort wählen wir das Zutreffende aus und bestätigen wieder mit „Schließen & Laden“. Die Ergebnistabelle wird „Anfügen1“ genannt, das können wir bei Tabellentools – Entwurf – Eigenschaften – Tabellenname ändern. Jetzt sollte auch rechts das Abfragen- und Verbindungsfenster geöffnet sein, in dem alle vorhandenen Tabellen und Abfragen aufgelistet sind (in der Abbildung Bereich Bereich an der unteren rechten Seite).
Power Query – Ganzen Ordner einlesen
Wenn die benötigten Dateien alle in einem Ordner speziell für sie abgelegt sind, dann kann man ein anderes Vorgehen nutzen, genau genommen sogar drei. Alle diese Methoden nutzen, wie die vorherige, dasselbe Grundwerkzeug, nämlich Power Query.
Wir wählen in Excel Daten – Daten abrufen – Aus Datei – Aus Ordner, Ordner auswählen und bestätigen.
Verfahren einfach: ein spezielles Arbeitsblatt
Hier betrachten wir den Fall, dass wir immer ein spezielles Arbeitsblatt (oder eine Tabelle) importieren möchten. Das ist der Fall, wenn ein spezielles „Auswertungsblatt“ in einer Formulardatei enthalten ist, welches auch zwingend gleich benannt sein muss.
Dann wählen wir bei „Kombinieren“ (nach Klick auf das Auswahldreieck) „Kombinieren und bearbeiten“. Im Dialogfeld müssen wir auswählen, welche Tabelle innerhalb der Dateien wir öffnen und kombinieren möchten. In Abb. 3 ist es so, dass in allen Dateien eine Gesamt-Tabelle im Arbeitsblatt "Gesamt" enthalten ist, welche wir auch aneinanderfügen möchten. Wenn wir das ausgewählt haben, bestätigen wir mit „ok“ und es öffnet sich der Power Query Editor. Dort prüfen wir zuerst, ob durch die Automatik nicht z.B. die Datumsspalte umformatiert wurde. Wir können jetzt die korrekten Typen den Spalten zuweisen. Der wichtigste Schritt ist allerdings, darauf zu achten, dass eventuelle Leer- oder Fehlerfelder eliminiert werden. Die tauchen vor allem dann auf, wenn wir Formulare haben, in denen wir viele Zeilen für halbautomatische Eingaben vorgesehen haben, die aber nicht alle genutzt wurden. Das machen wir über die Schaltfläche Start – Zeilen entfernen – Fehler entfernen bzw. – Leere Zeilen entfernen. Das sollte reichen, um die Tabellen einzulesen.
Verfahren einfach: Alle Arbeitsblätter
Die Voraussetzungen, dass dies Verfahren klappt, sind,
- dass alle Dateien nicht nur gleich aufgebaut sind, sondern auch einen gleichartigen Namen haben. Im Beispiel beginnen alle Dateinamen mit „Umsatzdaten“;
- dass alle Arbeitsblätter denselben Aufbau haben, also dieselben Spaltennamen, - anzahl und -inhalte;
- dass idealerweise die Daten in "echten" Tabellen mit Namen enthalten sind.
-
<!-- [if gte mso 10]>
Das ist im Beispiel der Fall. Da sind pro Monat identisch aufgebaute Tabellen erstellt worden. Das ist aber nicht nötig, es würden auch alle anderen Tabellen oder Arbeitsblätter ausgelesen.
Wir starten wie vorher, wählen aber die Option „Daten transformieren“ statt „Kombinieren“. Die Auswahl der Dateien filtern wir mit dem Textfilter – ‚Beginnt mit“ und geben „Umsatz“ ein. Jetzt können wir alle Spalten außer den ersten beiden (Content und Name) löschen. In der Spalte „Content“ klicken wir oben auf das Verbinden-Symbol (nach unten weisende zwei Pfeile) und wählen im erscheinenden Dialogfeld „Parameter“ aus. Uns geht es ja um Tabellen, also filtern wir in der Spalte „Kind“ nach „Table“. Sonst könnten wir nach „Sheet“ filtern und nach Namen. Wir brauchen jetzt nur noch die Spalten Name und Data. Die Data-Spalte erweitern wir durch Klick auf das entsprechende Symbol in der Namensleiste. Den Spalten weisen wir den korrekten Datentyp zu. Dann rufen wir sicherheitshalber den Filter für die neuen Spalten auf und eliminieren Zeilen mit „null“ oder leere.
Etwas komplexeres Verfahren
Dies Verfahren ist zwar etwas schwieriger, als die vorigen, aber man lernt einen zusätzlichen Weg, der man nutzen kann, wenn die anderen Ansätze versagen.
Statt auf „Kombinieren“ gehen wir auf „Daten transformieren“. Dann bekommen wir lediglich eine Liste mit allen Dateien im Ordner angezeigt, so, wie sie auch im vorigen Verfahren in einem Dialogfeld aufgetaucht war.
Dort können wir alle Spalten, die wir nicht brauchen, über Start – Spalten löschen entfernen (z.B. die mit dem Datum, wann erzeugt oder gespeichert wurde und die Erweiterung .xlsx). Bei Bedarf filtern wir die Spalte „Extension“ nach „.xlsx“ und bekommen nur die Excel Workbooks angezeigt. Wir fügen nun eine neue benutzerdefiniert Spalte hinzu und nennen Sie „Inhalt“. Dann schreiben wir in die benutzerdefinierte Spaltenformel „= Excel.Workbook(“, mit einem Doppelklick auf den Eintrag „Content“ in den verfügbaren Spalten rechts fügen wir „[Content]“ hinzu und schließen die Klammer. Die fertige Formel lautet also „= Excel.Workbook([Content])“. Wichtig ist hier die korrekte Groß- Kleinschreibung! Die nun angefügte Spalte können wir durch Klick auf das Symbol oben rechts am Tabellenkopf aufklappen und erhalten eine Liste mit Spalten, aus der wir diejenigen wählen, die wir uns anzeigen lassen wollen. Normalerweise brauchen wir alle außer „Hidden“.
Wenn wir im Vorfeld die interessierenden Daten als Tabelle formatiert und abgespeichert haben, dann können wir jetzt in der Spalte Inhalt.Kind nach „Table“ filtern. Sonst nehmen wir in dieser Tabelle „Sheet“ (also Arbeits- oder Tabellenblatt), filtern evtl. noch in Inhalt.Item nach den Namen dieser Blätter und haben dann die Tabellen mit den für uns relevanten Daten aus allen in diesem Ordner gespeicherten Dateien. Weil wir nach der Prämisse oben ja in diesem Verzeichnis gleich aufgebaute Tabellen haben, ist es sinnvoll, zusätzlich die Spalte Inhalt.Data über das Symbol rechts am Tabellenkopf aufzuklappen, um die vorhandenen Spalten und deren Inhalte anzeigen zu lassen. Wie im vorigen Absatz beschrieben filtern wir eventuell vorhandene Fehler- und Leerzeilen aus. Wenn wir jetzt einfach auf das Symbol unter Start – Schließen & Laden klicken, bekommen wir eine Tabelle angezeigt mit den Inhalten aller Tabellen eines Verzeichnisses.
Alle drei Ansätze haben bei mir dieselben Ergebnisse bei einem Ordner mit unterschiedlichen Dateitypen gebracht.
Über Power Query erstellte Tabellen aktualisieren
Die nach den oben genannten Methoden erstellten Tabellen kann man einfach aktualisieren. Über den Rechtsklick auf eine beliebige Zelle der Tabelle kommt man zum Kontextmenü. Dort wählt man „Aktualisieren“ – fertig.
Kommentar schreiben