Praktischer Austausch von Excel-Kenntnissen: Verwenden Sie Power Query, um Arbeitsmappen in Ordnern zusammenzuführen

Ich habe Ihnen zuvor die Verwendung der neuen Funktion Power Query von EXCEL zum Zusammenfassen der Arbeitsblätter in der Arbeitsmappe vorgestellt, aber die Funktion von Power Query ist weit mehr als das. Heute werde ich Ihnen eine fortgeschrittenere Zusammenführungstechnik vorstellen: die Verwendung Power Query führt Arbeitsmappen in Ordnern zusammen.

Wie unten gezeigt, befinden sich im Ordner „Verkäufe“ auf dem Desktop Verkaufsdaten für vier Regionen. Die Titelnamen in jeder Arbeitsmappe sind konsistent und die Reihenfolge kann unterschiedlich sein. Der Wert der Stadtspalte in jeder Arbeitsmappe ist der Name der Arbeitsmappe, was für die spätere Anzeige des Zusammenführungseffekts praktisch ist.

Der Vorgang ist wie folgt:

Schließen Sie die Datei im Ordner, erstellen Sie eine Erstellen Sie eine neue Arbeitsmappe und klicken Sie auf die Datenoption „Unter“. die Karte, [Abrufen und konvertieren] Gruppe „Neue Abfrage“ --- „Aus Datei“ --- „Aus Ordner“.

Geben Sie den Ordnerpfad ein, oder suchen Sie nach dem Speicherort des Ordners und klicken Sie auf „OK“.

Diese Oberfläche listet alle Arbeitsmappen im Ordner auf. Klicken Sie auf Bearbeiten.

Betreten Sie die Benutzeroberfläche des Power Query-Editors. Oben befindet sich die Menüleiste, in der Mitte der Tabellenbereich und am Ende die Daten, die an das Arbeitsblatt zurückgegeben werden. Im Abfrageeinstellungsfenster auf der rechten Seite wird der Power Query-Vorgangsdatensatz angezeigt.

Wie bereits erwähnt bedeutet die grüne Schriftart in der Spalte „Inhalt“, dass diese Zelle eine Datei enthält. Klicken Sie auf eine Zelle, um eine Vorschau ihres Inhalts anzuzeigen. (Hinweis: Bei der Vorschau des Inhalts einer Zelle sollten Sie die Maus auf die leere Stelle in der Zelle und nicht auf den Text platzieren. Durch Klicken auf den Text wird die Datei direkt in der Zelle geöffnet) Da die Datei direkt extrahiert wird aus dem Ordner im Binärformat, sodass die Arbeitsmappe im Binärformat im Vorschaufenster unten angezeigt wird.

Binär als Binärdatei kann nicht in einer Tabelle angezeigt werden. Wir müssen es also in ein Tabellenformat ändern und die Daten in die Tabelle erweitern. Löschen Sie zuerst andere unnötige Spalten.

Wählen Sie die Spalte „Inhalt“ aus und klicken Sie in der Gruppe [Spalten verwalten] auf der Registerkarte „Startseite“ auf „Spalte löschen“ – „Andere Spalten löschen“.

Auf diese Weise werden alle anderen Spalten außer der Spalte „Inhalt“ gelöscht.

Das Konvertieren von Binärdateien in normale Tabellen erfordert also die Verwendung der speziellen Programmiersprache von Power Query – der M-Sprache. Hier stelle ich Ihnen eine häufig verwendete Funktion vor.

Klicken Sie in der Gruppe [Allgemein] unter der Option „Spalte hinzufügen“ auf „Benutzerdefinierte Spalte“.

Geben Sie im Fenster „Benutzerdefinierte Spalten“ =Excel.Workbook([Content],true) in die „Benutzerdefinierte Spaltenformel“ ein, wobei „[Inhalt]“ in den verfügbaren Spalten auf „Inhalt“ klicken kann rechts, dann klicken Sie zum Einfügen auf die untere rechte Ecke (Hinweis: Die Groß- und Kleinschreibung der Formel darf nicht falsch sein).

Formelanalyse:

Excel.Workbook

Funktion: Die Datensätze des Arbeitsblatts aus der Excel-Arbeitsmappe zurückgeben.

Parameter: Excel.Workbook (Arbeitsmappe als Binärdatei, optionale useHeaders als nullbare logische Datei, optionale DelayTypes als nullbare Datei logisch) als Tabelle

Diese Funktion gibt eine Tabelle zurück. Der erste Parameter der Arbeitsmappe ist im Binärformat und der zweite Parameter ist der logische Wert des optionalen Parameters true, um den Titel der Originaltabelle als Titel zu verwenden Die neue Tabelle wird standardmäßig mit „false“ verwendet. Die neuen Spaltennamen ersetzen die ursprünglichen Arbeitsblatttitel. Machen Sie sich keine Sorgen über den dritten Parameter.

Hier verwenden wir immer noch den Originaltitel des Formulars, also tragen Sie „wahr“ ein. Dies erspart den nachträglichen Schritt der Verbesserung der ersten Zeile des Titels.

Die neue Spalte wurde erfolgreich hinzugefügt. Eine der Zellen wird in der Vorschau angezeigt. Dies kann direkt auf die Tabelle erweitert werden.

Die Spalte „Daten“ zeigt eine Tabelle im Tabellenformat an, einschließlich der Daten in der Tabelle. Hier müssen wir nur diese Spalte extrahieren. Klicken Sie auf die Schaltfläche „Erweitern“ in der oberen rechten Ecke der benutzerdefinierten Spalte, wählen Sie die erweiterte Spalte „Daten“ aus und deaktivieren Sie „Originalspaltennamen als Präfix verwenden“.

Der Spaltenname wird zu „Daten“. Zu diesem Zeitpunkt sehen wir eine Vorschau der Daten unter „Daten“ und unten werden die Originaldaten in der Tabelle angezeigt. Extrahieren Sie dann alle folgenden Daten.

Klicken Sie auf ähnliche Weise auf die Schaltfläche „Erweitern“ oben rechts in der benutzerdefinierten Spalte, wählen Sie die Option „Alle Spalten erweitern“ und aktivieren Sie nicht „Originalspaltennamen als Präfix verwenden“.

Auf diese Weise erhalten wir die Daten im Arbeitsblatt, indem wir Schicht für Schicht bohren.

Löschen Sie abschließend die Spalte „Inhalt“. Wählen Sie die Spalte „Inhalt“ aus und klicken Sie mit der rechten Maustaste, um sie zu löschen.

Zum Abschluss laden Sie einfach diese Tabelle in das Formular hoch.

Klicken Sie auf „Schließen und hochladen“ in der Gruppe [Schließen] auf der Registerkarte „Startseite“.

Auf diese Weise werden die Daten im Arbeitsblatt zusammengefasst.

Wenn Sie in der Spalte „Stadt“ auf die Filterschaltfläche klicken, sehen Sie, dass die Daten in den vier Arbeitsmappen alle in der Tabelle enthalten sind.

Was passiert, wenn sich noch eine Arbeitsmappe im Ordner befindet? Versuchen Sie, eine neue Arbeitsmappe „Xi'an“ in diesem Ordner abzulegen.

Gehen Sie zurück zu der Tabelle, in der Sie gerade Statistiken erstellt haben, und klicken Sie in der Gruppe [Verbindung] unter der Registerkarte „Daten“ auf „Alle aktualisieren“.

Die Stadtspalte enthält jetzt „Xi'an“ und die Daten, die diese neue Arbeitsmappe darstellen, wurden hinzugefügt.

Zusammenfassung: Power Query führt Ordner zusammen, solange die Titel in jedem Arbeitsblatt gleich sind. Diese Methode kann unabhängig von der Anzahl der Arbeitsmappen im Ordner zusammengeführt werden. Und alle Datenänderungen können mit einem Klick während der gesamten Aktualisierung aktualisiert werden.

Power Query ist ein leistungsstarkes Tool für die Excel-Datenanalyse, kombiniert mit seiner eigenen M-Sprache und dem Operationsrekorder, hilft es uns, mehr Daten auf einheitliche Weise zu verwalten und die Datenverarbeitung und -optimierung schnell abzuschließen. Darüber hinaus ist es schneller zu starten und einfacher zu bedienen als VBA, und die grafische Bedienung kann die meisten unserer Anforderungen erfüllen. Beeilt euch alle und lernt!

Verwandte Lernempfehlungen: Excel-Tutorial

Das obige ist der detaillierte Inhalt vonPraktischer Austausch von Excel-Kenntnissen: Verwenden Sie Power Query, um Arbeitsmappen in Ordnern zusammenzuführen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!