Aufteilen einer Tabelle in Blätter

Microsoft Excel verfügt über viele Tools zum Sammeln von Daten aus mehreren Tabellen (aus verschiedenen Blättern oder aus verschiedenen Dateien): direkte Links, Funktion INDIREKT (INDIREKT), Power Query- und Power Pivot-Add-Ons usw. Von dieser Seite der Barrikade sieht alles gut aus.

Aber wenn Sie auf ein umgekehrtes Problem stoßen – das Verteilen von Daten von einer Tabelle auf verschiedene Blätter – dann wird alles viel trauriger. Im Moment gibt es leider keine zivilisierten eingebauten Tools für eine solche Datentrennung im Arsenal von Excel. Sie müssen also ein Makro in Visual Basic verwenden oder die Kombination Makrorekorder + Power Query mit einer kleinen „Dateiverfeinerung“ danach verwenden.

Schauen wir uns genauer an, wie dies implementiert werden kann.

Formulierung des Problems

Wir haben als Ausgangsdaten eine solche Tabelle mit einer Größe von mehr als 5000 Zeilen für den Verkauf:

Aufgabe: Die Daten aus dieser Tabelle nach Stadt auf separate Blätter dieses Buches zu verteilen. Diese. Bei der Ausgabe müssen Sie auf jedes Blatt nur die Zeilen aus der Tabelle bringen, in denen der Verkauf in der entsprechenden Stadt stattgefunden hat:

Danach

Um den Makrocode nicht zu verkomplizieren und ihn so leicht verständlich wie möglich zu machen, führen wir ein paar vorbereitende Schritte durch.

Zuerst wird der Erstellen Sie eine separate Nachschlagetabelle, wo eine einzelne Spalte alle Städte auflistet, für die Sie separate Blätter erstellen möchten. Natürlich enthält dieses Verzeichnis möglicherweise nicht alle Städte, die in den Quelldaten vorhanden sind, sondern nur diejenigen, für die wir Berichte benötigen. Der einfachste Weg, eine solche Tabelle zu erstellen, ist die Verwendung des Befehls Daten – Duplikate entfernen (Daten — Duplikate entfernen) für Spaltenkopie Stadt oder Funktion UNIK (EINZIGARTIG) – wenn Sie die neueste Version von Excel 365 haben.

Da neue Blätter in Excel standardmäßig vor (links neben) dem aktuellen (vorherigen) erstellt werden, ist es auch sinnvoll, die Städte in diesem Verzeichnis absteigend (von Z bis A) zu sortieren – nach dem Erstellen dann die Stadt Blätter werden alphabetisch geordnet.

Zweitens, die пKonvertieren Sie beide Tabellen in dynamisch („smart“), um die Arbeit mit ihnen zu erleichtern. Wir verwenden den Befehl Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren) oder Tastenkürzel Ctrl+T. Auf der angezeigten Registerkarte Bauherr (Design) nennen wir sie tablProdaji и Tischstadt, beziehungsweise:

Methode 1. Makro für die Aufteilung nach Blättern

Auf der Registerkarte Erweitert Entwickler (Entwickler) Klicken Sie auf die Schaltfläche Visual Basic oder verwenden Sie die Tastenkombination Andere+F11. Fügen Sie im sich öffnenden Fenster des Makro-Editors über das Menü ein neues leeres Modul ein Einfügen – Modul und kopiere dort folgenden Code:

Sub Splitter() For Each cell In Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub	  

Hier mit Schleife Für jeden … Weiter implementiert den Durchgang durch die Zellen des Verzeichnisses Tischstadt, wo für jede Stadt gefiltert wird (method Automatischer Filter) in der ursprünglichen Verkaufstabelle und kopieren Sie dann die Ergebnisse in das neu erstellte Blatt. Unterwegs wird das erstellte Blatt in den gleichen Namen der Stadt umbenannt und die automatische Anpassung der Breite der Spalten für die Schönheit wird darauf aktiviert.

Auf der Registerkarte können Sie das erstellte Makro in Excel ausführen Entwickler Taste im nun erscheinenden Bestätigungsfenster nun wieder los. Makros (Entwickler – Makros) oder Tastenkürzel Andere+F8.

Methode 2. Erstellen Sie mehrere Abfragen in Power Query

Die vorherige Methode hat trotz ihrer Kompaktheit und Einfachheit einen erheblichen Nachteil – die vom Makro erstellten Blätter werden nicht aktualisiert, wenn Änderungen in der ursprünglichen Verkaufstabelle vorgenommen werden. Wenn eine Aktualisierung im laufenden Betrieb erforderlich ist, müssen Sie das VBA + Power Query-Bundle verwenden, oder besser gesagt, mithilfe eines Makros nicht nur Blätter mit statischen Daten erstellen, sondern aktualisierte Power Query-Abfragen.

Das Makro ähnelt in diesem Fall teilweise dem vorherigen (es hat auch einen Zyklus Für jeden … Weiter um Städte im Verzeichnis zu durchlaufen), aber innerhalb der Schleife wird nicht mehr gefiltert und kopiert, sondern eine Power Query-Abfrage erstellt und ihre Ergebnisse in ein neues Blatt hochgeladen:

Sub Splitter2() für jede Zelle im Bereich ("Stadttabelle") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Kategorie"", Text eingeben}, {""Name"", Text eingeben}, {""Stadt"", Text eingeben}, {""Manager"", Text eingeben}, {""Deal date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Zeilen mit angewendetem Filter"" = Table.Se " & _ "lectRows(#""Changed type"", each ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Zeilen mit angewendetem Filter""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub  

Nach dem Start werden wir die gleichen Blätter nach Stadt sehen, aber bereits erstellte Power Query-Abfragen werden sie bilden:

Bei Änderungen in den Quelldaten genügt es, die entsprechende Tabelle mit der rechten Maustaste zu aktualisieren – dem Befehl Aktualisieren & Speichern (Aktualisierung) oder aktualisieren Sie alle Städte auf einmal in großen Mengen über die Schaltfläche Alle aktualisieren Tab Datum (Daten — Alle aktualisieren).

• Was sind Makros, wie werden sie erstellt und verwendet?
• Speichern von Arbeitsmappenblättern als separate Dateien
• Sammeln von Daten aus allen Blättern des Buches in einer Tabelle