Een tabel in bladen splitsen

Microsoft Excel heeft veel tools voor het verzamelen van gegevens uit verschillende tabellen (van verschillende werkbladen of uit verschillende bestanden): directe links, functie INDIRECTE (INDIRECT), Power Query en Power Pivot add-ons, etc. Vanaf deze kant van de barricade ziet alles er goed uit.

Maar als je een omgekeerd probleem tegenkomt - gegevens van de ene tabel naar verschillende bladen verspreiden - dan wordt alles veel droeviger. Op dit moment zijn er helaas geen beschaafde ingebouwde tools voor dergelijke gegevensscheiding in het arsenaal van Excel. Je zult dus een macro in Visual Basic moeten gebruiken, of de combinatie macrorecorder + Power Query met een beetje "bestandsverfijning" erna.

Laten we eens nader bekijken hoe dit kan worden geïmplementeerd.

Formulering van het probleem

We hebben als initiële gegevens zo'n tabel met een grootte van meer dan 5000 rijen voor verkoop:

Taak: de gegevens uit deze tabel per stad verspreiden op aparte bladen van dit boek. Die. bij de uitvoer moet u op elk blad alleen die rijen uit de tabel krijgen waar de verkoop in de overeenkomstige stad was:

Voorbereiden

Laten we een paar voorbereidende stappen uitvoeren om de macrocode niet ingewikkeld te maken en het zo gemakkelijk mogelijk te maken om te begrijpen.

Ten eerste, de maak een aparte opzoektabel, waar een enkele kolom alle steden vermeldt waarvoor u aparte bladen wilt maken. Natuurlijk bevat deze map mogelijk niet alle steden die in de brongegevens aanwezig zijn, maar alleen die waarvoor we rapporten nodig hebben. De eenvoudigste manier om zo'n tabel te maken, is door het commando Gegevens – Duplicaten verwijderen (Gegevens — Verwijder duplicaten) voor kolomkopie Plaats of functie UNIEK (UNIEK) – als je de laatste versie van Excel 365 hebt.

Aangezien nieuwe bladen in Excel standaard vóór (links van) de huidige (vorige) worden gemaakt, is het ook logisch om de steden in deze map in aflopende volgorde (van Z naar A) te sorteren - en na het maken, de stad bladen worden alfabetisch geordend.

Ten tweede, de пconverteer beide tabellen naar dynamisch (“slim”) om het gemakkelijker te maken om ermee te werken. We gebruiken het commando Home – Opmaak als tabel (Home - Opmaken als tabel) of sneltoets Ctrl+T. Op het tabblad dat verschijnt Constructeur (Ontwerp) laten we ze bellen tablProdaji и TabelStadrespectievelijk:

Methode 1. Macro voor delen door bladen

Op het tabblad Geavanceerd ontwikkelaar (Ontwikkelaar) klik op de knop Visual Basic of gebruik de sneltoets anders+F11. In het macro-editorvenster dat wordt geopend, voegt u een nieuwe lege module in via het menu Invoegen – Module en kopieer daar de volgende 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 Volgende cel Worksheets("Данные").ShowAllData End Sub	  

Hier met een lus Voor elke ... Volgende de passage door de cellen van de directory geïmplementeerd TabelStad, waar voor elke stad wordt gefilterd (methode AutoFilter) in de originele verkooptabel en kopieer de resultaten vervolgens naar het nieuw gemaakte blad. Onderweg wordt het gemaakte blad hernoemd naar dezelfde naam van de stad en wordt de breedte van de kolommen voor schoonheid automatisch aangepast.

U kunt de gemaakte macro in Excel uitvoeren op het tabblad ontwikkelaar Macro's (Ontwikkelaar — Macro's) of sneltoets anders+F8.

Methode 2. Maak meerdere query's in Power Query

De vorige methode heeft, ondanks al zijn compactheid en eenvoud, een belangrijk nadeel: de bladen die door de macro zijn gemaakt, worden niet bijgewerkt wanneer er wijzigingen worden aangebracht in de oorspronkelijke verkooptabel. Als bijwerken on-the-fly nodig is, moet u de VBA + Power Query-bundel gebruiken, of liever een macro maken met niet alleen werkbladen met statische gegevens, maar bijgewerkte Power Query-query's.

De macro lijkt in dit geval gedeeltelijk op de vorige (hij heeft ook een cyclus Voor elke ... Volgende om steden in de map te herhalen), maar binnen de lus wordt niet langer gefilterd en gekopieerd, maar wordt een Power Query-query gemaakt en worden de resultaten naar een nieuw blad geüpload:

Sub Splitter2() For Each cell In Range("City table") 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(Bron , {{""Categorie"", typ text}, {""Naam"", typ text}, {""Plaats"", typ text}, {""Manager"", typ text}, {""Deal date "", typ datetime}, {""Kosten"", typ number}})," & Chr(13) & "" & Chr(10) & " #""Rijen waarop filter is toegepast"" = Table.Se " & _ "lectRows(#""Type gewijzigd"", each ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Rijen waarop filter is toegepast""" 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 Volgende cel End Sub  

Na de lancering zullen we dezelfde bladen per stad zien, maar al gemaakte Power Query-query's zullen ze vormen:

Bij eventuele wijzigingen in de brongegevens is het voldoende om de bijbehorende tabel bij te werken met de rechtermuisknop - het commando Bijwerken en opslaan (Vernieuwen) of update alle steden tegelijk in bulk met de knop Alles bijwerken tab Data (Gegevens — Alles vernieuwen).

• Wat zijn macro's, hoe maak je ze aan en gebruik je ze?
• Werkmapbladen opslaan als afzonderlijke bestanden
• Gegevens van alle bladen van het boek in één tabel verzamelen