Obsah
Microsoft Excel má veľa nástrojov na zhromažďovanie údajov z niekoľkých tabuliek (z rôznych hárkov alebo z rôznych súborov): priame odkazy, funkcie NEPRIAME (NEPRIAME), doplnky Power Query a Power Pivot atď. Z tejto strany barikády vyzerá všetko dobre.
Ak však narazíte na inverzný problém – šírenie údajov z jednej tabuľky do rôznych hárkov – všetko bude oveľa smutnejšie. V súčasnosti, žiaľ, v arzenáli Excelu neexistujú žiadne civilizované vstavané nástroje na takéto oddelenie údajov. Takže budete musieť použiť makro v jazyku Visual Basic alebo použiť kombináciu záznamníka makier + Power Query s malým „spresnením súboru“.
Pozrime sa bližšie na to, ako sa to dá implementovať.
Formulácia problému
Ako počiatočné údaje máme takúto tabuľku s veľkosťou viac ako 5000 riadkov pre predaj:
Úloha: rozdeliť údaje z tejto tabuľky podľa miest na samostatné listy tejto knihy. Tie. na výstupe musíte na každý hárok dostať iba tie riadky z tabuľky, kde bol predaj v príslušnom meste:
Pripraviť
Aby sme kód makra nekomplikovali a aby bol čo najjednoduchší, urobme si niekoľko prípravných krokov.
Po prvé, vytvorte samostatnú vyhľadávaciu tabuľku, kde v jedinom stĺpci budú uvedené všetky mestá, pre ktoré chcete vytvoriť samostatné hárky. Samozrejme, tento adresár nemusí obsahovať všetky mestá prítomné v zdrojových údajoch, ale iba tie, pre ktoré potrebujeme prehľady. Najjednoduchší spôsob vytvorenia takejto tabuľky je použiť príkaz Údaje – odstrániť duplikáty (Údaje – odstrániť duplikáty) pre kopírovanie stĺpca City alebo funkciu UNIK (JEDINEČNÉ) – ak máte najnovšiu verziu Excelu 365.
Keďže nové hárky v Exceli sa štandardne vytvárajú pred (naľavo od) aktuálneho (predchádzajúceho), má zmysel aj zoraďovať mestá v tomto adresári v zostupnom poradí (od Z po A) – po vytvorení potom mesto listy budú zoradené podľa abecedy.
Po druhé, пpreviesť obe tabuľky na dynamické („inteligentné“), aby sa s nimi ľahšie pracovalo. Používame príkaz Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku) alebo klávesová skratka ctrl+T. Na karte, ktorá sa zobrazí staviteľ (Dizajn) zavolajme im tablProdaji и TableCity, respektíve:
Metóda 1. Makro na delenie podľa listov
Na karte Rozšírené vývojka (vývojár) kliknite na tlačidlo Visual Basic alebo použite klávesovú skratku ostatné+F11. V okne editora makier, ktoré sa otvorí, vložte nový prázdny modul cez ponuku Vložiť – Modul a skopírujte tam nasledujúci kód:
Sub Splitter() pre každú bunku v rozsahu("таблГорода") Rozsah("таблПродажи").Pole automatického filtra:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCellTypeColls(All]). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Ďalšia bunka Worksheets("Данные").ShowAllData End Sub Tu so slučkou Pre každú… Ďalej implementoval prechod cez bunky adresára TableCity, kde sa pre každé mesto filtruje (metóda Automatický filter) v pôvodnej predajnej tabuľke a následným skopírovaním výsledkov do novovytvoreného hárku. Po ceste sa vytvorený list premenuje na rovnaký názov mesta a zapne sa na ňom automatické prispôsobenie šírky stĺpcov pre krásu.
Vytvorené makro môžete spustiť v Exceli na karte vývojka gombík Makrá (Vývojár – makrá) alebo klávesová skratka ostatné+F8.
Metóda 2. Vytvorte viacero dotazov v Power Query
Predchádzajúci spôsob má pri celej svojej kompaktnosti a jednoduchosti značnú nevýhodu – hárky vytvorené makrom sa pri zmenách v pôvodnej predajnej tabuľke neaktualizujú. Ak je potrebná priebežná aktualizácia, budete musieť použiť balík VBA + Power Query, alebo skôr vytvoriť pomocou makra nielen hárky so statickými údajmi, ale aj aktualizované dotazy Power Query.
Makro je v tomto prípade čiastočne podobné predchádzajúcemu (má tiež cyklus Pre každú… Ďalej iterovať cez mestá v adresári), ale v rámci cyklu už nebude filtrovanie a kopírovanie, ale vytváranie dotazu Power Query a nahrávanie jeho výsledkov do nového hárka:
Sub Splitter2() Pre každú bunku v rozsahu("Tabuľka mesta") ActiveWorkbook.Queries.Add Name:=cell.Value, Vzorec:= _ "let" & Chr(13) & "" & Chr(10) & " Zdroj = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Kategória"", zadajte text}, {""Názov"", zadajte text}, {""Mesto"", zadajte text}, {""Manažér"", zadajte text}, {""Dohoda dátum "", zadajte dátum a čas}, {""Cena"", zadajte číslo}}), & Chr(13) & "" & Chr(10) & " #""Riadky s aplikovaným filtrom"" = Tabuľka.Se " & _ "lectRows(#""Zmenený typ"", každý ([City] = """ & cell.Value & """)" & Chr(13) & "" & Chr(10) & "v " & Chr(13) & "" & Chr(10) & " #""Riadky s aplikovaným filtrom""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Poskytovateľ =Microsoft.Mashup.OleDb.1;Zdroj údajov=$Workbook$;Umiestnenie=" & 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 = xlInsertDeleteCellssword .Sa SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End with ActiveSheet.Name = cell.Value Ďalšia bunka End Sub Po jeho spustení uvidíme rovnaké hárky podľa mesta, ale vytvoria ich už vytvorené dotazy Power Query:
Pri akýchkoľvek zmenách v zdrojových údajoch bude stačiť aktualizovať príslušnú tabuľku pravým tlačidlom myši – príkazom Aktualizovať a uložiť (Obnoviť) alebo aktualizujte všetky mestá naraz pomocou tlačidla aktualizácie Všetko pútko dátum (Údaje – Obnoviť všetko).
- Čo sú makrá, ako ich vytvárať a používať
- Ukladanie hárkov zošita ako samostatných súborov
- Zhromažďovanie údajov zo všetkých listov knihy do jednej tabuľky