Erstellen Sie Tabellen mit unterschiedlichen Überschriften aus mehreren Büchern

Formulierung des Problems

Wir haben mehrere Dateien (in unserem Beispiel – 4 Stück, im allgemeinen Fall – beliebig viele) in einem Ordner Meldungen:

Im Inneren sehen diese Dateien so aus:

Worin:

• Das von uns benötigte Datenblatt wird immer aufgerufen Fotos, kann sich aber an einer beliebigen Stelle in der Arbeitsmappe befinden.
  
• Jenseits des Blattes Fotos Jedes Buch kann andere Blätter haben.
• Tabellen mit Daten haben eine andere Anzahl von Zeilen und können mit einer anderen Zeile auf dem Arbeitsblatt beginnen.
• Die Namen derselben Spalten in verschiedenen Tabellen können unterschiedlich sein (z. B. Menge = Menge = Menge).
• Spalten in Tabellen können in einer anderen Reihenfolge angeordnet werden.

Aufgabe: Sammeln Sie Verkaufsdaten aus allen Dateien des Blattes Fotos in eine gemeinsame Tabelle, um anschließend eine Zusammenfassung oder andere Analysen darauf aufzubauen.

Schritt 1. Vorbereiten eines Verzeichnisses mit Spaltennamen

Als erstes muss ein Nachschlagewerk mit allen möglichen Optionen für Spaltennamen und deren richtige Interpretation erstellt werden:

Diese Liste wandeln wir über die Schaltfläche Als Tabelle formatieren auf der Registerkarte in eine dynamische „intelligente“ Tabelle um Startseite (Home — Als Tabelle formatieren) oder Tastenkürzel Ctrl+T und laden Sie es mit dem Befehl in Power Query Daten – Aus Tabelle/Bereich (Daten – Aus Tabelle/Bereich). In neueren Versionen von Excel wurde es umbenannt in Mit Blättern (Vom Blatt).

Im Fenster des Power Query-Abfrage-Editors löschen wir traditionell den Schritt Geänderter Typ und fügen Sie stattdessen einen neuen Schritt hinzu, indem Sie auf die Schaltfläche klicken f_xin der Formelleiste (wenn es nicht sichtbar ist, können Sie es auf der Registerkarte aktivieren Bewertung) und geben dort die Formel in der eingebauten Power Query Sprache M ein:

=Table.ToRows(Quelle)

Dieser Befehl konvertiert die im vorherigen Schritt geladene Quelle Referenztabelle in eine Liste, die aus verschachtelten Listen (List) besteht, von denen jede wiederum ein Wertepaar ist Es wurde aus einer Zeile:

Wir werden diese Art von Daten etwas später benötigen, wenn wir Header aus allen geladenen Tabellen massenhaft umbenennen.

Wählen Sie nach Abschluss der Konvertierung die Befehle aus Home — Schließen und laden — Schließen und laden in… und Art des Imports Stellen Sie einfach eine Verbindung her (Home — Schließen&Laden — Schließen&Laden nach… — Nur Verbindung herstellen) und gehen Sie zurück zu Excel.

Schritt 2. Wir laden alles aus allen Dateien so wie es ist

Lassen Sie uns nun den Inhalt aller unserer Dateien aus dem Ordner laden – vorerst so wie er ist. Mannschaften auswählen Daten – Daten abrufen – Aus Datei – Aus Ordner (Daten – Daten abrufen – Aus Datei – Aus Ordner) und dann den Ordner, in dem sich unsere Quellenbücher befinden.

Klicken Sie im Vorschaufenster auf Konvertieren (Verwandeln) or Change (Bearbeiten):

Und erweitern Sie dann den Inhalt aller heruntergeladenen Dateien (Binär) Schaltfläche mit Doppelpfeilen in der Spaltenüberschrift Inhalt:

Power Query am Beispiel der ersten Datei (Wostok.xlsx) fragt uns nach dem Namen des Blattes, das wir aus jeder Arbeitsmappe nehmen möchten – wählen Sie Fotos und drücken Sie OK:

Danach treten (tatsächlich) mehrere Ereignisse auf, die für den Benutzer nicht offensichtlich sind, deren Folgen im linken Bereich deutlich sichtbar sind:

1. Power Query nimmt die erste Datei aus dem Ordner (wir werden sie haben Wostok.xlsx — sehen Dateibeispiel) als Beispiel und importiert seinen Inhalt durch Erstellen einer Abfrage Beispieldatei konvertieren. Diese Abfrage hat einige einfache Schritte wie Quelle (Dateizugriff) Navigation (Blattauswahl) und evtl. Erhöhung der Titel. Diese Anfrage kann nur Daten aus einer bestimmten Datei laden Wostok.xlsx.
   
2. Basierend auf dieser Anfrage wird die damit verbundene Funktion erstellt Datei konvertieren (angezeigt durch ein charakteristisches Symbol f_x), wobei die Quelldatei keine Konstante mehr ist, sondern ein variabler Wert – ein Parameter. Somit kann diese Funktion Daten aus jedem Buch extrahieren, das wir ihr als Argument hinzufügen.
   
3. Die Funktion wird der Reihe nach auf jede Datei (Binär) aus der Spalte angewendet Inhalt – step ist dafür verantwortlich Benutzerdefinierte Funktion aufrufen in unserer Abfrage, die der Dateiliste eine Spalte hinzufügt Datei konvertieren mit Importergebnissen aus jeder Arbeitsmappe:

   

4. Zusätzliche Spalten werden entfernt.
5. Der Inhalt verschachtelter Tabellen wird expandiert (Schritt Erweiterte Tabellenspalte) – und wir sehen die endgültigen Ergebnisse der Datenerhebung aus allen Büchern:

   
   

Schritt 3. Schleifen

Der vorherige Screenshot zeigt deutlich, dass sich die direkte Montage „wie besehen“ als von schlechter Qualität herausstellte:

• Die Spalten sind vertauscht.
• Viele zusätzliche Zeilen (leer und nicht nur).
• Tabellenüberschriften werden nicht als Überschriften wahrgenommen und mit Daten vermischt.
  

Sie können all diese Probleme ganz einfach beheben – optimieren Sie einfach die Abfrage zum Konvertieren von Beispieldateien. Alle Anpassungen, die wir daran vornehmen, fallen automatisch in die zugehörige Funktion Datei konvertieren, was bedeutet, dass sie später beim Importieren von Daten aus jeder Datei verwendet werden.

Durch Öffnen einer Anfrage Beispieldatei konvertieren, fügen Sie Schritte hinzu, um unnötige Zeilen zu filtern (z. B. nach Spalte Column2) und Anheben der Überschriften mit der Schaltfläche Verwenden Sie die erste Zeile als Überschrift (Verwenden Sie die erste Zeile als Überschrift). Der Tisch wird viel besser aussehen.

Damit Spalten aus unterschiedlichen Dateien später automatisch untereinander passen, müssen sie gleich benannt werden. Sie können eine solche Massenumbenennung nach einem zuvor erstellten Verzeichnis mit einer Zeile M-Code durchführen. Drücken wir noch einmal auf den Knopf f_x in der Bearbeitungsleiste und fügen Sie eine Funktion hinzu, um Folgendes zu ändern:

= Table.RenameColumns(#“Elevated Headers“, Headers, MissingField.Ignore)

Diese Funktion übernimmt die Tabelle aus dem vorherigen Schritt Erhöhte Kopfzeilen und benennt alle darin enthaltenen Spalten gemäß der verschachtelten Nachschlageliste um Schlagzeilen. Drittes Argument MissingField.Ignore wird benötigt, damit bei den Überschriften, die im Verzeichnis, aber nicht in der Tabelle stehen, kein Fehler auftritt.

Eigentlich ist das alles.

Zurück zur Anfrage Meldungen wir werden ein ganz anderes Bild sehen – viel schöner als das vorherige:

• Was ist Power Query, Power Pivot, Power BI und warum ein Excel-Benutzer sie braucht
  
• Sammeln von Daten aus allen Dateien in einem bestimmten Ordner
• Sammeln von Daten aus allen Blättern des Buches in einer Tabelle