So führen Sie Daten aus mehreren Excel-Arbeitsmappen zusammen. Angenommen, Sie haben einen Ordner voller Excel-Dateien. Jedes hat ein Arbeitsblatt mit einer unterschiedlichen Anzahl von Zeilen. Sie können alle Dateien im Ordner in eine einzelne Excel-Arbeitsmappe importieren.
Schau Video
- Überprüfen der bereinigten Daten mit Power Query-Podcast Nr. 2037
- Power Query kann jetzt alle Excel-Dateien in einem Ordner kombinieren.
- Verbessert: Sie löschen automatisch die Header aus allen außer der ersten Datei.
- Sie wählen aus, welche Datei als Beispieldatei verwendet werden soll.
- Wählen Sie aus, welches Arbeitsblatt, welche Tabelle oder welcher benannte Bereich importiert werden soll
- Verwenden Sie den Abfrageeditor, um Transformationen durchzuführen
- Schließen und laden, um alle Dateien zu kombinieren
- Aktualisieren Sie die Abfrage später, damit sie aktualisiert wird
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2077: Arbeitsmappen zusammenführen
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Noch vor 40 Folgen ging es darum, Daten mit Power Query zu bereinigen und eine ganze Reihe von CSV-Dateien in eine einzige Excel-Datei zu bringen. Nun, diese Funktion ist gerade dramatisch besser geworden.
Ich habe heute hier einen Ordner und in diesem Ordner befinden sich eine ganze Reihe monatlicher Excel-Dateien, 22 davon seit fast zwei Jahren. Ich muss alle diese Dateien kombinieren. Nun, diese haben alle das gleiche Spaltenlayout, okay? Sie haben also alle 9 Spalten, alle die gleichen Überschriften und so weiter, in Ordnung. Also werden wir all dies ohne ein Makro kombinieren.
Wenn Sie sich in Excel 2016 befinden, rufen Sie die Registerkarte Daten auf, um Daten abzurufen und zu transformieren. Wenn Sie sich in Excel 2010 oder 2013 befinden, werden Sie ein großartiges Produkt von Microsoft namens Power Query herunterladen. Sie können das Ganze herunterladen und in weniger als einer Minute installieren.
Wir werden also hierher kommen, um Daten aus einer Datei abzurufen, aber nicht aus einer einzelnen Arbeitsmappe. Wir möchten es aus einem solchen Ordner erhalten. Okay, hier sind sie. Sie werden gebeten, zum Ordner zu navigieren, aber ich werde dort nur klicken und Strg + V einfügen, da ich das Verzeichnis eingefügt habe. Klicken Sie auf OK. Also zeigten sie mir eine Vorschau der Daten, die ich habe. Ich öffne dieses kleine Dropdown-Menü Kombinieren hier und wähle Kombinieren & Bearbeiten. Okay, und dann ist der Schritt hier, dass wir eine Datei auswählen, die eine gute repräsentative Datei ist, okay, und sie wählen nur die erste aus, die zufällig alphabetisch ist. Es spielt keine Rolle, welche Sie wählen. Und dann diese Beispielabfrage, die wir aus der ersten Datei erstellen, die wir auswählen. In diesem Fall möchte ich Sheet1.Wenn Sie mehrere Blätter haben, können Sie Blatt 2 oder das Blatt Gewinn- und Verlustrechnung oder den Namensbereich oder eine Tabelle sagen. Und sie haben mir gezeigt, wie es aussieht und ich klicke auf OK.
In diesem Bildschirm ist jetzt viel los. Was sie hier tatsächlich getan haben, ist, dass sie eine Beispielabfrage erstellt haben, ahh Beispielabfrage, und diese dann als Funktion erstellt haben und diese Funktion für alles im Ordner ausführen. Sie fügen eine neue Spalte hinzu, die mir sagt, woher die Datei stammt, und in diesem speziellen Fall weiß ich bereits, woher sie stammt, da hier ein Datum ausgefüllt ist. Also brauche ich das nicht, ich werde mit der rechten Maustaste klicken und das entfernen. Und vielleicht sind dort einige Daten, die ich nicht brauche. Vielleicht gehen diese Daten an einen Kunden, also möchte ich den Gewinn nicht anzeigen, damit ich diese Spalte und sogar die Kostenspalte entfernen kann, und vielleicht gibt es einige nervige Dinge wie, wissen Sie, sie haben schlechte Header verwendet und ich möchte diese Header umbenennen. Klicken Sie mit der rechten Maustaste und benennen Sie es um. Wir nennen es MENGE für Menge.Vielleicht sind die Dinge in der falschen Reihenfolge, vielleicht möchte ich diesen Gegenstand nehmen und an einen anderen Ort bringen, okay? Nur all diese kleinen nervigen Dinge, die Sie möglicherweise haben, und natürlich führen sie hier alle angewendeten Schritte aus, und dann können Sie weiterhin alle Power Query-Dinge ausführen, die Sie tun müssen. Aber in diesem Fall ist es einfach. Ich möchte nur die Daten einspielen. Also gehe ich zu Close & Load, ein paar Sekunden vergehen und da ist es. Ich habe mein Finale. Lassen Sie uns dies hier einfach nach Datum sortieren. Sie sehen, wir gehen von Januar 2018 bis Oktober 2019. Wie cool ist das?Führen Sie alle angewendeten Schritte aus, und fahren Sie dann mit allen erforderlichen Power Query-Aufgaben fort. Aber in diesem Fall ist es einfach. Ich möchte nur die Daten einspielen. Also gehe ich zu Close & Load, ein paar Sekunden vergehen und da ist es. Ich habe mein Finale. Lassen Sie uns dies hier einfach nach Datum sortieren. Sie sehen, wir gehen von Januar 2018 bis Oktober 2019. Wie cool ist das?Führen Sie alle angewendeten Schritte aus, und fahren Sie dann mit allen erforderlichen Power Query-Aufgaben fort. Aber in diesem Fall ist es einfach. Ich möchte nur die Daten einspielen. Also gehe ich zu Close & Load, ein paar Sekunden vergehen und da ist es. Ich habe mein Finale. Lassen Sie uns dies hier einfach nach Datum sortieren. Sie sehen, wir gehen von Januar 2018 bis Oktober 2019. Wie cool ist das?
Hier ist das Erstaunliche, Erstaunliche daran. Angenommen, in unserem Ordner ändert sich etwas. Zurück hier in den Monatsdateien erhalte ich zwei neue Dateien, Einfügen, und jetzt gibt es hier zwei weitere Datensätze, zwei weitere Excel-Dateien, die ich kombinieren muss. Kehren Sie einfach zu meiner Excel-Datei zurück, die derzeit 516 Zeilen enthält, suchen Sie die MonthFiles und klicken Sie auf die Schaltfläche Aktualisieren. Und so schnell habe ich jetzt die neuen Rekorde von November und Dezember. Power Query ist ein erstaunliches neues Tool, das für Excel 2010 und Excel 2013 kostenlos ist. Es war schön, wenn sie CSV-Dateien kombinieren konnten, aber jetzt, da sie auch Excel-Dateien kombinieren können, einfach phänomenal.
Daher habe ich mein Buch Power Excel mit der Ausgabe 2017 aktualisiert, das Power Query, Power BI und alle neuen Tools enthält. Hör zu. Klicken Sie auf das "i" in der oberen rechten Ecke.
Okay, Abschluss dieser Episode: Wir überprüfen die Clean Data erneut mit dem Power Query-Podcast Nr. 2037, in dem wir mehrere CSV-Dateien kombinieren. Wir können jetzt dasselbe Tool, eine verbesserte Version dieses Tools, verwenden, um alle Excel-Dateien in einem Ordner zu kombinieren. Eine weitere Verbesserung, die Änderung von 2037, sie löschen automatisch die Header aus allen außer der ersten Datei und fördern diese Header in der ersten Datei. Sie wählen aus, welche Datei als Beispieldatei verwendet werden soll. In meinem Fall habe ich Januar verwendet. Arbeit - Wählen Sie aus, welches Arbeitsblatt, welche Tabelle oder welcher benannte Bereich importiert werden soll, und führen Sie dann alle Transformationen im Abfrageeditor durch. Wir klicken auf Schließen & Laden, es werden alle Dateien kombiniert. Wenn Sie dann mehr Dateien in den Ordner ablegen oder einige Dateien entfernen oder einige Dateien ändern, aktualisieren Sie einfach die Abfrage, und alle diese Schritte werden erneut ausgeführt. Power Query,ein absolut erstaunliches Werkzeug.
Ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2077.xlsm
