Sisu
Microsoft Excelil on palju tööriistu andmete kogumiseks mitmest tabelist (erinevatest lehtedest või erinevatest failidest): otselingid, funktsioon KAUDSEID (Kaudne), Power Query ja Power Pivoti lisandmoodulid jne. Sellelt barrikaadi poolelt paistab kõik hea.
Kui aga tekib pöördprobleem – andmete ühest tabelist erinevatele lehtedele levitamine –, siis on kõik palju kurvem. Hetkel pole Exceli arsenalis kahjuks tsiviliseeritud sisseehitatud tööriistu selliseks andmete eraldamiseks. Nii et peate Visual Basicus kasutama makrot või kasutama makrosalvesti + Power Query kombinatsiooni koos väikese failitäpsustusega.
Vaatame lähemalt, kuidas seda saab rakendada.
Probleemi sõnastamine
Meil on algandmetena müügiks selline tabel, mille suurus on üle 5000 rea:
Ülesanne: jagada selle tabeli andmed linnade kaupa selle raamatu eraldi lehtedele. Need. väljundis peate igale lehele saama tabelist ainult need read, kus müük toimus vastavas linnas:
Valmistama
Selleks, et makrokood ei muutuks keeruliseks ja oleks võimalikult lihtne mõista, teeme paar ettevalmistavat sammu.
Esiteks luua eraldi otsingutabel, kus ühes veerus on loetletud kõik linnad, mille jaoks soovite eraldi lehti luua. Muidugi ei pruugi see kataloog sisaldada kõiki lähteandmetes olevaid linnu, vaid ainult neid, mille kohta vajame aruandeid. Lihtsaim viis sellise tabeli loomiseks on kasutada käsku Andmed – eemaldage duplikaadid (Andmed – eemaldage duplikaadid) veeru koopia jaoks Linn või funktsioon UNIK (UNIKALNE) – kui teil on Excel 365 uusim versioon.
Kuna Excelis luuakse uued lehed vaikimisi enne (vasakul) praegust (eelmist), siis on mõttekas ka selles kataloogis olevad linnad sortida kahanevas järjekorras (Z-st A-ni) – siis pärast loomist linn lehed järjestatakse tähestikulises järjekorras.
Teiseks, пteisendada mõlemad tabelid dünaamiliseks (“nutikas”), et nendega oleks lihtsam töötada. Kasutame käsku Avaleht – vorminda tabelina (Avaleht — vorminda tabelina) või klaviatuuri otsetee Ctrl+T. Ilmuval vahekaardil Ehitaja (Kujundus) helistame neile tablProdaji и TableCityvastavalt:
Meetod 1. Makro lehtede kaupa jagamiseks
Vahekaardil Täpsemalt arendaja (Arendaja) klõpsa nupul Visual Basic või kasutage kiirklahvi muu+F11. Avanevas makroredaktori aknas sisestage menüü kaudu uus tühi moodul Sisesta – moodul ja kopeerige sinna järgmine kood:
Alamjaotur() iga lahtri jaoks vahemikus ("таблГорода") Range("таблПродажи"). Automaatfiltri väli:=3, Kriteerium1:=lahter.Väärtusvahemik("таблПродажи[#All]").SpecialCells(xlC). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Siin silmusega Iga jaoks … Järgmine rakendas läbipääsu läbi kataloogi lahtrite TableCity, kus iga linna jaoks see filtreeritakse (meetod Autofilter) algsesse müügitabelisse ja seejärel kopeerides tulemused vastloodud lehele. Teel nimetatakse loodud leht ümber linna samaks nimeks ja sellel lülitatakse sisse veergude laiuse automaatne kohandamine ilu huvides.
Saate käivitada loodud makro vahekaardil Excelis arendaja nupp Makrod (Arendaja – makrod) või klaviatuuri otsetee muu+F8.
2. meetod. Looge Power Querys mitu päringut
Eelmisel meetodil on kogu oma kompaktsuse ja lihtsuse juures märkimisväärne puudus – algses müügitabelis muudatuste tegemisel makro abil loodud lehti ei uuendata. Kui käigult värskendamine on vajalik, peate kasutama VBA + Power Query kimpu või pigem looma makro abil mitte ainult staatiliste andmetega lehti, vaid värskendatud Power Query päringuid.
Makro on sel juhul osaliselt sarnane eelmisega (sellel on ka tsükkel Iga jaoks … Järgmine kataloogis olevate linnade itereerimiseks), kuid tsükli sees ei toimu enam filtreerimist ja kopeerimist, vaid Power Query päringu loomine ja selle tulemuste uuele lehele üleslaadimine:
Sub Splitter2() Iga lahtri jaoks vahemikus ("Linnatabel") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Allikas = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Sisu]," & Chr(13) & "" & Chr(10) & " #""Muudetud tüüp"" = Tabel.TransformColumnTypes(Allikas , {{""Kategooria"", tippige tekst}, {""Nimi", tippige tekst}, {""Linn", tüüp text}, {""Manager", type text}, {""Tehing" date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Rakendatud filtriga read"" = Table.Se " & _ "lectRows(#""Muudetud tüüp"", iga ([Linn] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Rakendatud filtriga read""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Pakkuja =Microsoft.Mashup.OleDb.1;Andmeallikas=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = Väär .FillAdjacentFormulas = Väär .PreserveFormatting = Tõene .RefreshOnFileOpen = Vale .BackgroundQuery = Tõene .RefreshStyle = xlInsertDeleteCells .SavePass .SavePass. SaveData = Tõene .AdjustColumnWidth = õige Pärast selle käivitamist näeme linnade kaupa samu lehti, kuid juba loodud Power Query päringud moodustavad need:
Lähteandmete muudatuste korral piisab vastava tabeli värskendamisest hiire parema nupuga – käsuga Värskenda ja salvesta (Värskenda) või värskendage kõiki linnu korraga hulgi, kasutades nuppu Värskenda kõik tab kuupäev (Andmed – värskenda kõiki).
- Mis on makrod, kuidas neid luua ja kasutada
- Töövihiku lehtede salvestamine eraldi failidena
- Andmete kogumine raamatu kõigilt lehtedelt ühte tabelisse