Innhold
Microsoft Excel har mange verktøy for å samle inn data fra flere tabeller (fra forskjellige ark eller fra forskjellige filer): direkte lenker, funksjon INDIREKTE (INDIREKTE), Power Query og Power Pivot-tillegg osv. Fra denne siden av barrikaden ser alt bra ut.
Men hvis du støter på et omvendt problem – å spre data fra én tabell til forskjellige ark – vil alt være mye tristere. For øyeblikket er det ingen siviliserte innebygde verktøy for slik dataseparasjon i arsenalet til Excel, dessverre. Så du må bruke en makro i Visual Basic, eller bruke makroopptakeren + Power Query-kombinasjonen med litt "filforfining" etterpå.
La oss se nærmere på hvordan dette kan implementeres.
Formulering av problemet
Vi har som startdata en slik tabell med en størrelse på mer enn 5000 rader for salg:
Oppgave: å distribuere dataene fra denne tabellen etter by på separate ark i denne boken. De. ved utgangen må du bare få på hvert ark de radene fra tabellen der salget var i den tilsvarende byen:
Forbered
For ikke å komplisere makrokoden og gjøre den så lett å forstå som mulig, la oss utføre et par forberedende trinn.
Først lage en egen oppslagstabell, hvor en enkelt kolonne vil vise alle byene du vil opprette separate ark for. Selvfølgelig kan denne katalogen ikke inneholde alle byene som finnes i kildedataene, men bare de som vi trenger rapporter for. Den enkleste måten å lage en slik tabell på er å bruke kommandoen Data – Fjern duplikater (Data – Fjern duplikater) for spaltekopi City eller funksjon UNIK (UNIK) – hvis du har den nyeste versjonen av Excel 365.
Siden nye ark i Excel opprettes som standard før (til venstre for) det nåværende (forrige), er det også fornuftig å sortere byene i denne katalogen i synkende rekkefølge (fra Å til A) – deretter etter opprettelsen, byen arkene vil bli ordnet alfabetisk.
Det andre, пkonvertere begge tabellene til dynamiske ("smart") for å gjøre det lettere å jobbe med dem. Vi bruker kommandoen Hjem – Formater som en tabell (Hjem — Formater som tabell) eller hurtigtast Ctrl+T. På fanen som vises Constructor (Design) la oss ringe dem tablProdaji и TableCity, henholdsvis:
Metode 1. Makro for deling etter ark
I kategorien Avansert utvikler (Utvikler) klikk på knappen Visual Basic eller bruk hurtigtaster andre+F11. I makroredigeringsvinduet som åpnes, sett inn en ny tom modul gjennom menyen Sett inn – modul og kopier følgende kode dit:
Sub Splitter() For hver celle I 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 Her med en løkke For hver … Neste implementerte passasjen gjennom cellene i katalogen TableCity, hvor det filtreres for hver by (metode Autofilter) i den originale salgstabellen og kopier deretter resultatene til det nyopprettede arket. Underveis blir det opprettede arket omdøpt til samme navn på byen og automatisk tilpasning av bredden på kolonnene for skjønnhet er slått på.
Du kan kjøre den opprettede makroen i Excel på fanen utvikler knapp Makroer (Utvikler – Makroer) eller hurtigtast andre+F8.
Metode 2. Opprett flere spørringer i Power Query
Den forrige metoden, for all sin kompakthet og enkelhet, har en betydelig ulempe - arkene som er opprettet av makroen, oppdateres ikke når det gjøres endringer i den originale salgstabellen. Hvis oppdatering umiddelbart er nødvendig, må du bruke VBA + Power Query-pakken, eller rettere sagt, lage ved å bruke en makro, ikke bare ark med statiske data, men oppdaterte Power Query-spørringer.
Makroen i dette tilfellet er delvis lik den forrige (den har også en syklus For hver … Neste å iterere over byer i katalogen), men inne i løkken vil det ikke lenger være filtrering og kopiering, men å lage en Power Query-spørring og laste opp resultatene til et nytt ark:
Sub Splitter2() For hver celle i rekkevidde("Bytabell") 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 "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Rader med filter brukt"" = Tabell.Se " & _ "lectRows(#""Endret type"", hver ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Rader med filter brukt""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Leverandør =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 = 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 Neste celle End Sub Etter lanseringen vil vi se de samme arkene etter by, men allerede opprettede Power Query-spørringer vil danne dem:
Med eventuelle endringer i kildedataene, vil det være nok å oppdatere den tilsvarende tabellen med høyre museknapp - kommandoen Oppdater og lagre (Forfriske) eller oppdater alle byer samtidig ved å bruke knappen Oppdater alle tab Data (Data – Oppdater alle).
- Hva er makroer, hvordan lage og bruke dem
- Lagre arbeidsbokark som separate filer
- Samle data fra alle arkene i boken i én tabell