Innehåll
Microsoft Excel har många verktyg för att samla in data från flera tabeller (från olika ark eller från olika filer): direktlänkar, funktion INDIREKT (INDIREKT), Power Query och Power Pivot-tillägg, etc. Från den här sidan av barrikaden ser allt bra ut.
Men om du stöter på ett omvänt problem – att sprida data från en tabell till olika ark – så blir allt mycket tråkigare. För tillfället finns det tyvärr inga civiliserade inbyggda verktyg för sådan dataseparering i Excel-arsenalen. Så du måste använda ett makro i Visual Basic, eller använda kombinationen makroinspelare + Power Query med lite "filförfining" efter.
Låt oss titta närmare på hur detta kan genomföras.
Formulering av problemet
Vi har som initialdata en sådan tabell med en storlek på mer än 5000 rader för försäljning:
Uppgift: att distribuera data från denna tabell efter stad på separata blad i denna bok. De där. vid utgången behöver du bara få de rader från tabellen där försäljningen var i motsvarande stad på varje ark:
Förbered
För att inte komplicera makrokoden och göra den så lätt att förstå som möjligt, låt oss utföra ett par förberedande steg.
Först den skapa en separat uppslagstabell, där en enda kolumn listar alla städer som du vill skapa separata ark för. Naturligtvis kan den här katalogen inte innehålla alla städer som finns i källdata, utan bara de som vi behöver rapporter för. Det enklaste sättet att skapa en sådan tabell är att använda kommandot Data – Ta bort dubbletter (Data – Ta bort dubbletter) för kolumnkopia Stad eller funktion UNIK (UNIK) – om du har den senaste versionen av Excel 365.
Eftersom nya ark i Excel skapas som standard före (till vänster om) det nuvarande (föregående) är det också meningsfullt att sortera städerna i den här katalogen i fallande ordning (från Z till A) – sedan efter skapandet, staden ark kommer att ordnas i alfabetisk ordning.
Andra, пkonvertera båda tabellerna till dynamiska ("smart") för att göra det lättare att arbeta med dem. Vi använder kommandot Hem – Formatera som en tabell (Hem — Formatera som tabell) eller kortkommandon ctrl+T. På fliken som visas Konstruktör (Design) låt oss ringa dem tablProdaji и TableCityrespektive:
Metod 1. Makro för uppdelning efter ark
På fliken Avancerat utvecklare (Utvecklaren) klicka på knappen Visual Basic eller använd kortkommandon andra+F11. I makroredigeringsfönstret som öppnas, infoga en ny tom modul genom menyn Infoga – Modul och kopiera följande kod dit:
Sub Splitter() För varje cell inom Range("таблГорода") 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 Här med en slinga För varje … Nästa implementerade passagen genom cellerna i katalogen TableCity, där den filtreras för varje stad (metod Autofilter) i den ursprungliga försäljningstabellen och kopiera sedan resultaten till det nyskapade arket. Längs vägen döps det skapade arket om till samma namn på staden och automatisk anpassning av kolonnernas bredd för skönhet aktiveras på det.
Du kan köra det skapade makrot i Excel på fliken utvecklare Knappen Makron (Utvecklare – Makron) eller kortkommandon andra+F8.
Metod 2. Skapa flera frågor i Power Query
Den tidigare metoden, för all sin kompakthet och enkelhet, har en betydande nackdel - arken som skapas av makrot uppdateras inte när ändringar görs i den ursprungliga försäljningstabellen. Om uppdatering i farten är nödvändig, måste du använda VBA + Power Query-paketet, eller snarare skapa med hjälp av ett makro, inte bara ark med statiska data, utan uppdaterade Power Query-frågor.
Makrot i det här fallet liknar delvis det föregående (det har också en cykel För varje … Nästa att iterera över städer i katalogen), men i slingan kommer det inte längre att finnas filtrering och kopiering, utan att skapa en Power Query-fråga och ladda upp dess resultat till ett nytt ark:
Sub Splitter2() För varje cell Inom Range("Stadstabell") ActiveWorkbook.Queries.Add Namn:=cell.Value, Formel:= _ "låt" & Chr(13) & "" & Chr(10) & " Källa = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Category"", typ text}, {""Name"", typ text}, {""City"", typ text}, {""Manager"", typ text}, {""Deal datum "", typ datetime}, {""Kostnad"", typnummer}})," & Chr(13) & "" & Chr(10) & " #""Rader med filter tillämpat"" = Tabell.Se " & _ "lectRows(#""Ändrad typ"", var och en ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Rader med filter tillämpat""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Källa:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Datakälla=$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 = FalsePass. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Falskt slut med ActiveSheet.Name = cell.Value Nästa cell End Sub Efter lanseringen kommer vi att se samma ark per stad, men redan skapade Power Query-frågor kommer att bilda dem:
Med eventuella ändringar i källdata räcker det att uppdatera motsvarande tabell med höger musknapp - kommandot Uppdatera & spara (Uppdatera) eller uppdatera alla städer samtidigt i bulk med knappen Uppdatera alla fliken Data (Data – Uppdatera alla).
- Vad är makron, hur man skapar och använder dem
- Spara arbetsboksblad som separata filer
- Samla data från alla ark i boken i en tabell