Stalo padalijimas į lapus

Microsoft Excel turi daug įrankių rinkti duomenis iš kelių lentelių (iš skirtingų lapų arba iš skirtingų failų): tiesioginės nuorodos, funkcija NETIESIOGINĖS (NETIESIOGINIS), Power Query ir Power Pivot priedai ir kt. Iš šios užtvaros pusės viskas atrodo gerai.

Bet jei susidursite su atvirkštine problema – skleisti duomenis iš vienos lentelės į skirtingus lapus – tada viskas bus daug liūdniau. Šiuo metu „Excel“ arsenale, deja, nėra civilizuotų integruotų įrankių tokiam duomenų atskyrimui. Taigi turėsite naudoti „Visual Basic“ makrokomandą arba naudoti makrokomandos įrašymo įrenginį + „Power Query“ derinį su šiek tiek „patobulinti failą“.

Pažiūrėkime atidžiau, kaip tai galima įgyvendinti.

Problemos formulavimas

Kaip pradinius duomenis turime tokią lentelę, kurios dydis viršija 5000 eilučių pardavimui:

Stalo padalijimas į lapus

Užduotis: paskirstyti šios lentelės duomenis pagal miestus atskiruose šios knygos lapuose. Tie. išvestyje kiekviename lape turite gauti tik tas lentelės eilutes, kuriose buvo parduota atitinkamame mieste:

Stalo padalijimas į lapus

Parengti

Kad neapsunkintume makrokodo ir kad jį būtų kuo lengviau suprasti, atlikime keletą parengiamųjų veiksmų.

Pirma, sukurti atskirą paieškos lentelę, kur viename stulpelyje bus išvardyti visi miestai, kuriems norite sukurti atskirus lapus. Žinoma, šiame kataloge gali būti ne visi šaltinio duomenyse esantys miestai, o tik tie, apie kuriuos mums reikia ataskaitų. Lengviausias būdas sukurti tokią lentelę yra naudoti komandą Duomenys – pašalinkite dublikatus (Duomenys – pašalinti dublikatus) stulpelio kopijai Miestas arba funkcija UNIKALUS (UNIKALUS) – jei turite naujausią „Excel 365“ versiją.

Kadangi nauji lapai programoje „Excel“ pagal numatytuosius nustatymus sukuriami prieš (į kairę nuo) dabartinio (ankstesnio), taip pat prasminga miestus šiame kataloge rūšiuoti mažėjančia tvarka (nuo Z iki A) – tada sukūrus miestas lapai bus išdėstyti abėcėlės tvarka.

Antra, пabi lenteles konvertuoti į dinamines („protingas“), kad būtų lengviau su jais dirbti. Mes naudojame komandą Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę) arba spartusis klavišas "Ctrl"+T. Pasirodžiusiame skirtuke Konstruktorius (Dizainas) paskambinkime jiems tablProdaji и TableCityatitinkamai:

Stalo padalijimas į lapus

1 metodas. Padalijimo lapais makro

Skirtuke Išplėstinė ryškalas (Programuotojas) spustelėkite mygtuką "Visual Basic" arba naudokite sparčiuosius klavišus Kitas+F11. Atsidariusiame makrokomandų rengyklės lange įterpkite naują tuščią modulį per meniu Įterpimas – modulis ir nukopijuokite ten šį kodą:

Sub Splitter() Kiekvienam diapazono langeliui ("таблГорода") Diapazonas ("таблПродажи"). Automatinio filtro laukas:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlC) Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub

Čia su kilpa Kiekvienam… Kitas įgyvendino praėjimą per katalogo ląsteles TableCity, kur kiekvienam miestui jis filtruojamas (metodas „AutoFilter“) originalioje pardavimo lentelėje ir nukopijuoti rezultatus į naujai sukurtą lapą. Pakeliui sukurtas lapas pervadinamas į tą patį miesto pavadinimą ir jame įjungiamas automatinis stulpelių pločio pritaikymas grožiui.

Sukurtą makrokomandą galite paleisti skirtuke „Excel“. ryškalas mygtukas Makrokomandos (Kūrėjas – makrokomandos) arba spartusis klavišas Kitas+F8.

2 būdas. Sukurkite kelias užklausas Power Query

Ankstesnis metodas, nepaisant viso savo kompaktiškumo ir paprastumo, turi reikšmingą trūkumą – makrokomandos sukurti lapai neatnaujinami, kai atliekami pakeitimai pradinėje pardavimo lentelėje. Jei reikia atnaujinti skrydžio metu, turėsite naudoti VBA + Power Query paketą arba, tiksliau, sukurti naudodami makrokomandą ne tik lapus su statiniais duomenimis, bet ir atnaujintas Power Query užklausas.

Šiuo atveju makrokomanda yra iš dalies panaši į ankstesnę (ji taip pat turi ciklą Kiekvienam… Kitas kartoti kataloge esančius miestus), tačiau ciklo viduje nebebus filtruojamas ir kopijuojamas, o sukuriama Power Query užklausa ir jos rezultatai įkeliami į naują lapą:

Sub Splitter2() Kiekvienam diapazono langeliui ("Miesto lentelė") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Turinys]," & Chr(13) & "" & Chr(10) & " #""Pakeistas tipas"" = Lentelė.TransformColumnTypes(šaltinis) , {{""Kategorija", įveskite tekstą}, {""Pavadinimas", įveskite tekstą}, {""Miestas", įveskite tekstą}, {""Vadovas", įveskite tekstą}, {""Siūlymas" data "", tipas datetime}, {""Mokestis", tipo numeris}})," & Chr(13) & "" & Chr(10) & " #""Eilutės su pritaikytu filtru"" = Table.Se " & _ "lectRows(#""Pakeistas tipas"", kiekvienas ([Miestas] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Eilutės su pritaikytu filtru""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Teikėjas =Microsoft.Mashup.OleDb.1;Duomenų šaltinis=$Workbook$;Location=" & cell.Value & ";Išplėstinės ypatybės=""""" _ , Paskirtis:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Masyvas("SELECT * FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = Klaidinga .PreserveFormatting = Tiesa Ištrinti langelius .SavePassword = False . SaveData = Tiesa .AdjustColumnWidth = Tiesa

Po paleidimo matysime tuos pačius lapus pagal miestą, tačiau juos sudarys jau sukurtos Power Query užklausos:

Stalo padalijimas į lapus

Pasikeitus šaltinio duomenims, pakaks atnaujinti atitinkamą lentelę dešiniuoju pelės mygtuku – komanda Atnaujinkite ir išsaugokite (Atnaujinti) arba iš karto atnaujinkite visus miestus naudodami mygtuką Atnaujinti Visi kortelė Duomenys (Duomenys – Atnaujinti viską).

  • Kas yra makrokomandos, kaip jas kurti ir naudoti
  • Darbaknygės lapų išsaugojimas kaip atskiri failai
  • Duomenų surinkimas iš visų knygos lapų į vieną lentelę

Palikti atsakymą