Saturs
Microsoft Excel ir daudz rīku datu vākšanai no vairākām tabulām (no dažādām lapām vai no dažādiem failiem): tiešās saites, funkcija NETIEŠAIS (NETIEŠI), Power Query un Power Pivot pievienojumprogrammas utt. No šīs barikādes puses viss izskatās labi.
Bet, ja jūs saskaraties ar apgrieztu problēmu - izkliedēt datus no vienas tabulas uz dažādām lapām, tad viss būs daudz bēdīgāk. Šobrīd Excel arsenālā diemžēl nav civilizētu iebūvētu rīku šādai datu atdalīšanai. Tātad jums būs jāizmanto makro programmā Visual Basic vai jāizmanto makro ierakstītājs + Power Query kombinācija ar nelielu “faila precizēšanu”.
Apskatīsim tuvāk, kā to var īstenot.
Problēmas formulēšana
Kā sākotnējie dati mums ir šāda tabula ar vairāk nekā 5000 rindu lielumu pārdošanai:
Uzdevums: sadalīt datus no šīs tabulas pa pilsētām uz atsevišķām šīs grāmatas lapām. Tie. izejā katrā lapā jāiekļauj tikai tās tabulas rindas, kurās pārdošana notika attiecīgajā pilsētā:
Sagatavot
Lai nesarežģītu makro kodu un padarītu to pēc iespējas vieglāk saprotamu, veiksim pāris sagatavošanas darbības.
Pirmkārt, izveidot atsevišķu uzmeklēšanas tabulu, kur vienā kolonnā būs uzskaitītas visas pilsētas, kurām vēlaties izveidot atsevišķas lapas. Protams, šajā direktorijā var nebūt visas avota datos esošās pilsētas, bet tikai tās, par kurām mums ir nepieciešamas atskaites. Vienkāršākais veids, kā izveidot šādu tabulu, ir izmantot komandu Dati — noņemiet dublikātus (Dati — noņemt dublikātus) kolonnas kopijai Pilsēta vai funkcija UNIK (UNIKĀLS) – ja jums ir jaunākā Excel 365 versija.
Tā kā jaunas lapas programmā Excel pēc noklusējuma tiek izveidotas pirms (pa kreisi no) pašreizējās (iepriekšējās), šajā direktorijā ir arī jēga kārtot pilsētas dilstošā secībā (no Z līdz A) – tad pēc izveides pilsēta lapas tiks sakārtotas alfabēta secībā.
Otrkārt, пkonvertēt abas tabulas uz dinamiskām (“gudrs”), lai būtu vieglāk strādāt ar tiem. Mēs izmantojam komandu Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu) vai īsinājumtaustiņš Ctrl+T. Parādītajā cilnē Konstruktors (Dizains) sauksim viņus tablProdaji и TableCityattiecīgi:
1. metode. Makro sadalīšanai pa lapām
Cilnē Papildu attīstītājs (Izstrādātājs) noklikšķiniet uz pogas Visual Basic vai izmantojiet īsinājumtaustiņus cits+F11. Atvērtajā makro redaktora logā izvēlnē ievietojiet jaunu tukšu moduli Ievietot - modulis un nokopējiet tur šādu kodu:
Apakšdalītājs() katrai šūnai diapazonā("таблГорода") Diapazons("таблПродажи"). Automātiskā filtra lauks:=3, kritēriji1:=šūna.Vērtību diapazons("таблПродажи[#All]").SpecialCells(xlC). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Šeit ar cilpu Katram… Nākamais īstenoja pāreju caur direktorija šūnām TableCity, kur katrai pilsētai tas tiek filtrēts (metode Automātiskais filtrs) sākotnējā pārdošanas tabulā un pēc tam kopējot rezultātus jaunizveidotajā lapā. Pa ceļam izveidotā lapa tiek pārdēvēta par tādu pašu pilsētas nosaukumu un tajā tiek ieslēgta automātiska kolonnu platuma pielāgošana skaistumam.
Izveidoto makro var palaist programmā Excel cilnē attīstītājs poga Makro (Izstrādātājs — makro) vai īsinājumtaustiņš cits+F8.
2. metode. Izveidojiet vairākus vaicājumus programmā Power Query
Iepriekšējai metodei, neskatoties uz visu tās kompaktumu un vienkāršību, ir būtisks trūkums – makro izveidotās lapas netiek atjauninātas, veicot izmaiņas sākotnējā pārdošanas tabulā. Ja ir nepieciešama atjaunināšana lidojuma laikā, jums būs jāizmanto komplekts VBA + Power Query vai drīzāk, izmantojot makro, jāizveido ne tikai lapas ar statiskiem datiem, bet arī atjaunināti Power Query vaicājumi.
Makro šajā gadījumā ir daļēji līdzīgs iepriekšējam (tam ir arī cikls Katram… Nākamais lai atkārtotu direktorijā esošās pilsētas), taču cilpas ietvaros vairs netiks veikta filtrēšana un kopēšana, bet gan Power Query vaicājuma izveide un tā rezultātu augšupielāde jaunā lapā:
Sub Splitter2() Katrai šūnai diapazonā ("Pilsētas tabula") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Saturs]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Kategorija", ierakstiet tekstu}, {""Nosaukums", ierakstiet tekstu}, {""Pilsēta", ierakstiet tekstu}, {""Pārvaldnieks", ierakstiet tekstu}, {""Darījums" datums "", ierakstiet datetime}, {""Maksa", tipa numurs}})," & Chr(13) & "" & Chr(10) & " #""Rindas ar lietotu filtru"" = Table.Se " & _ "lectRows(#""Mainīts veids"", katrs ([Pilsēta] = """ & cell.Vērtība & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Rindas ar lietotu filtru""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Nodrošinātājs =Microsoft.Mashup.OleDb.1;Datu avots=$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 = Patiess .RefreshOnFileOpen = Nepatiess .BackgroundQuery = Patiess .RefreshStyle = xlInsertDeleteCellsword =FalsePasword. SaveData = patiess .AdjustColumnWidth = patiess .RefreshPeriod = 0 .PreserveColumnInfo = patiess .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False Beigas ar ActiveSheet.Name = cell.Value Nākamā šūna beigu apakšpunkts Pēc tās palaišanas mēs redzēsim tās pašas lapas pa pilsētām, taču tās veidos jau izveidotie Power Query vaicājumi:
Pie jebkādām izmaiņām avota datos pietiks ar peles labās pogas atjaunināšanu atbilstošo tabulu – komandu Atjaunināt un saglabāt (Atjaunot) vai atjaunināt visas pilsētas vienlaikus lielapjomā, izmantojot pogu Update All tab Datums (Dati — atsvaidzināt visu).
- Kas ir makro, kā tos izveidot un izmantot
- Darbgrāmatas lapu saglabāšana kā atsevišķi faili
- Datu apkopošana no visām grāmatas lapām vienā tabulā