Verdeel 'n tafel in velle

Microsoft Excel het baie gereedskap om data van verskeie tabelle te versamel (van verskillende velle of van verskillende lêers): direkte skakels, funksie INDIREKTE (INDIREK), Power Query en Power Pivot-byvoegings, ens. Van hierdie kant van die sperstreep lyk alles goed.

Maar as jy 'n omgekeerde probleem ondervind - die verspreiding van data van een tabel na verskillende velle - dan sal alles baie hartseerder wees. Op die oomblik is daar ongelukkig geen beskaafde ingeboude gereedskap vir sulke dataskeiding in die arsenaal van Excel nie. So jy sal 'n makro in Visual Basic moet gebruik, of die makro-opnemer + Power Query-kombinasie gebruik met 'n bietjie "lêerverfyning" daarna.

Kom ons kyk van naderby hoe dit geïmplementeer kan word.

Formulering van die probleem

Ons het as aanvanklike data so 'n tabel met 'n grootte van meer as 5000 rye vir verkope:

Taak: om die data uit hierdie tabel volgens stad op aparte velle van hierdie boek te versprei. Dié. by die uitset, moet jy op elke vel net daardie rye van die tabel kry waar die verkoop in die ooreenstemmende stad was:

Berei

Om nie die makro-kode te bemoeilik nie en dit so maklik as moontlik te maak om te verstaan, laat ons 'n paar voorbereidende stappe uitvoer.

Eerstens, die skep 'n aparte opsoektabel, waar 'n enkele kolom al die stede sal lys waarvoor jy aparte velle wil skep. Natuurlik bevat hierdie gids dalk nie al die stede wat in die brondata teenwoordig is nie, maar slegs dié waarvoor ons verslae benodig. Die maklikste manier om so 'n tabel te skep, is om die opdrag te gebruik Data – Verwyder duplikate (Data – Verwyder duplikate) vir kolomkopie Stad of funksie UNIK (UNIEK) – as jy die nuutste weergawe van Excel 365 het.

Aangesien nuwe velle in Excel by verstek voor (links van) die huidige (vorige) een geskep word, is dit ook sinvol om die stede in hierdie gids in dalende volgorde (van Z na A) te sorteer – dan na die skepping, die stad blaaie sal alfabeties gerangskik word.

Tweedens, die пskakel beide tabelle om na dinamies (“slim”) om dit makliker te maak om met hulle te werk. Ons gebruik die opdrag Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel) of sleutelbordkortpad Ctrl+T. Op die blad wat verskyn konstruktor (Ontwerp) kom ons bel hulle tabelProdaji и TableCity, onderskeidelik:

Metode 1. Makro vir verdeling volgens velle

Op die oortjie Gevorderd ontwikkelaar (Ontwikkelaar) klik op die knoppie Visual Basic of gebruik sleutelbordkortpaaie alt+F11. In die makroredigeerdervenster wat oopmaak, voeg 'n nuwe leë module deur die kieslys in Voeg in – Module en kopieer die volgende kode daarheen:

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

Hier met 'n lus Vir elke … Volgende het die deurgang deur die selle van die gids geïmplementeer TableCity, waar dit vir elke stad gefiltreer word (metode Outofilter) in die oorspronklike verkoopstabel en kopieer dan die resultate na die nuutgeskepte blad. Langs die pad word die geskepte blad hernoem na dieselfde naam van die stad en die outomatiese aanpassing van die breedte van die kolomme vir skoonheid word daarop aangeskakel.

U kan die geskepde makro in Excel op die oortjie laat loop ontwikkelaar knoppie Macros (Ontwikkelaar - Makro's) of sleutelbordkortpad alt+F8.

Metode 2. Skep veelvuldige navrae in Power Query

Die vorige metode, vir al sy kompaktheid en eenvoud, het 'n beduidende nadeel - die velle wat deur die makro geskep word, word nie opgedateer wanneer veranderinge in die oorspronklike verkoopstabel aangebring word nie. As dit nodig is om dadelik by te werk, sal jy die VBA + Power Query-bundel moet gebruik, of eerder, skep deur 'n makro te gebruik, nie net blaaie met statiese data nie, maar opgedateerde Power Query-navrae.

Die makro in hierdie geval is gedeeltelik soortgelyk aan die vorige een (dit het ook 'n siklus Vir elke … Volgende om oor stede in die gids te herhaal), maar binne die lus sal daar nie meer gefiltreer en gekopieer word nie, maar 'n Power Query-navraag skep en die resultate daarvan na 'n nuwe blad oplaai:

Sub Splitter2() Vir Elke sel In Range("Stadtabel") ActiveWorkbook.Queries.Add Naam:=sel.Value, Formule:= _ "laat" & Chr(13) & "" & Chr(10) & " Bron = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Kategorie"", tik teks}, {""Naam"", tik teks}, {""Stad"", tik teks}, {""Bestuurder"", tik teks}, {""Deal datum "", tik datetime}, {""Koste"", tipe nommer}})," & Chr(13) & "" & Chr(10) & " #""Rye met filter toegepas"" = Table.Se " & _ "lectRows(#""Veranderde tipe"", elk ([Stad] = """ & sel.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Rye met filter toegepas""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Bron:= _ "OLEDB; Verskaffer =Microsoft.Mashup.OleDb.1;Databron=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Skikking("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells = FalsePass. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Volgende sel End Sub  

Na die bekendstelling daarvan sal ons dieselfde velle per stad sien, maar reeds geskepde Power Query-navrae sal dit vorm:

Met enige veranderinge in die brondata, sal dit genoeg wees om die ooreenstemmende tabel op te dateer met die regter muisknoppie - die opdrag Dateer op en stoor (Verfris) of werk alle stede gelyktydig op met behulp van die knoppie Dateer almal op tab data (Data – Herlaai alles).

• Wat is makro's, hoe om dit te skep en te gebruik
• Stoor werkboekvelle as aparte lêers
• Versamel data van alle velle van die boek in een tabel