Indhold
Microsoft Excel har mange værktøjer til at indsamle data fra flere tabeller (fra forskellige ark eller fra forskellige filer): direkte links, funktion INDIREKTE (INDIREKTE), Power Query og Power Pivot tilføjelser osv. Fra denne side af barrikaden ser alt godt ud.
Men hvis du løber ind i et omvendt problem – at sprede data fra en tabel til forskellige ark – så vil alt være meget mere trist. I øjeblikket er der desværre ingen civiliserede indbyggede værktøjer til sådan dataadskillelse i arsenalet af Excel. Så du bliver nødt til at bruge en makro i Visual Basic, eller bruge makrooptageren + Power Query-kombinationen med en lille "filforfining" efter.
Lad os se nærmere på, hvordan dette kan implementeres.
Formulering af problemet
Vi har som indledende data sådan en tabel med en størrelse på mere end 5000 rækker til salg:
Opgave: at fordele dataene fra denne tabel efter by på separate ark i denne bog. De der. ved udgangen skal du på hvert ark kun få de rækker fra tabellen, hvor salget var i den tilsvarende by:
Forbered
For ikke at komplicere makrokoden og gøre den så let at forstå som muligt, lad os udføre et par forberedende trin.
Første, oprette en separat opslagstabel, hvor en enkelt kolonne viser alle de byer, som du vil oprette separate ark for. Naturligvis indeholder denne mappe muligvis ikke alle de byer, der er til stede i kildedataene, men kun dem, som vi har brug for rapporter for. Den nemmeste måde at oprette sådan en tabel på er at bruge kommandoen Data – Fjern dubletter (Data – Fjern dubletter) til spaltekopi By eller funktion ENESTÅENDE (ENESTÅENDE) – hvis du har den seneste version af Excel 365.
Da nye ark i Excel oprettes som standard før (til venstre for) det nuværende (forrige), giver det også mening at sortere byerne i denne mappe i faldende rækkefølge (fra Z til A) – derefter efter oprettelsen, byen ark vil blive ordnet alfabetisk.
Sekund, пkonvertere begge tabeller til dynamiske ("smart") for at gøre det nemmere at arbejde med dem. Vi bruger kommandoen Hjem – Formater som en tabel (Hjem — Formater som tabel) eller tastaturgenvej Ctrl+T. På den fane, der vises Constructor (Design) lad os ringe til dem tabelProdaji и TableCity, henholdsvis:
Metode 1. Makro til opdeling efter ark
På fanen Avanceret developer (Udvikler) klik på knappen Visual Basic eller brug tastaturgenvej andre+F11. Indsæt et nyt tomt modul gennem menuen i makroredigeringsvinduet, der åbnes Indsæt – Modul og kopier følgende kode dertil:
Sub Splitter() For hver celle I Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCell). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Her med en løkke For hver … Næste implementerede passagen gennem cellerne i mappen TableCity, hvor den filtreres for hver by (metode Autofilter) i den originale salgstabel og kopier derefter resultaterne til det nyoprettede ark. Undervejs omdøbes det oprettede ark til det samme navn på byen, og automatisk tilpasning af søjlernes bredde til skønhed er slået til på det.
Du kan køre den oprettede makro i Excel på fanen developer . Makroer (Udvikler – Makroer) eller tastaturgenvej andre+F8.
Metode 2. Opret flere forespørgsler i Power Query
Den tidligere metode, for al dens kompakthed og enkelhed, har en betydelig ulempe - arkene, der er oprettet af makroen, opdateres ikke, når der foretages ændringer i den originale salgstabel. Hvis det er nødvendigt at opdatere med det samme, bliver du nødt til at bruge VBA + Power Query-pakken, eller rettere oprette ved hjælp af en makro, ikke kun ark med statiske data, men opdaterede Power Query-forespørgsler.
Makroen i dette tilfælde ligner delvist den forrige (den har også en cyklus For hver … Næste at iterere over byer i mappen), men inde i løkken vil der ikke længere være filtrering og kopiering, men oprettelse af en Power Query-forespørgsel og upload af resultaterne til et nyt ark:
Sub Splitter2() For hver celle i rækkevidde("Bytabel") ActiveWorkbook.Queries.Add Name:=cell.Value, Formel:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Category"", type text}, {""Name"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal dato "", skriv datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Rækker med filter anvendt"" = Table.Se " & _ "lectRows(#""Ændret type"", hver ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Rækker med filter anvendt""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Datakilde=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = Falsk .FillAdjacentFormulas = Falsk .PreserveFormatting = Sand .RefreshOnFileOpen = Falsk .BackgroundQuery = Sand .RefreshStyle = xlInsertDeleteCells = .FalsePass. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Næste celle End Sub Efter lanceringen vil vi se de samme ark efter by, men allerede oprettede Power Query-forespørgsler vil danne dem:
Med eventuelle ændringer i kildedataene vil det være nok at opdatere den tilsvarende tabel med højre museknap - kommandoen Opdater & Gem (Opdater) eller opdater alle byer på én gang ved at bruge knappen Opdatering Alle fanen data (Data – Opdater alle).
- Hvad er makroer, hvordan man opretter og bruger dem
- Gemmer projektmappeark som separate filer
- Indsamling af data fra alle ark i bogen i én tabel