Obsah
Microsoft Excel má mnoho nástrojů pro sběr dat z několika tabulek (z různých listů nebo z různých souborů): přímé odkazy, funkce NEPŘÍMÝ (NEPŘÍMÝ), doplňky Power Query a Power Pivot atd. Z této strany barikády vše vypadá dobře.
Pokud ale narazíte na inverzní problém – šíření dat z jedné tabulky do různých listů – pak bude všechno mnohem smutnější. V současné době v arzenálu Excelu bohužel neexistují žádné civilizované vestavěné nástroje pro takové oddělení dat. Takže budete muset použít makro ve Visual Basicu nebo použít kombinaci záznamníku maker + Power Query s malým „upřesněním souboru“.
Podívejme se blíže na to, jak to lze implementovat.
Formulace problému
Jako výchozí data máme takovou tabulku o velikosti více než 5000 řádků pro prodej:
Úkol: rozdělit údaje z této tabulky podle měst na samostatné listy této knihy. Tito. na výstupu musíte na každý list dostat pouze ty řádky z tabulky, kde byl prodej v odpovídajícím městě:
Připravit
Abychom kód makra nekomplikovali a usnadnili mu pochopení, proveďte několik přípravných kroků.
Za prvé, vytvořit samostatnou vyhledávací tabulku, kde v jednom sloupci budou uvedena všechna města, pro která chcete vytvořit samostatné listy. Tento adresář samozřejmě nemusí obsahovat všechna města přítomná ve zdrojových datech, ale pouze ta, pro která potřebujeme sestavy. Nejjednodušší způsob, jak vytvořit takovou tabulku, je použít příkaz Data – Odstranit duplikáty (Data – odstranit duplikáty) pro kopii sloupce Město nebo funkci UNIK (UNIKÁTNÍ) – pokud máte nejnovější verzi Excelu 365.
Vzhledem k tomu, že se nové listy v Excelu standardně vytvářejí před (vlevo od) aktuálním (předchozím), má smysl také řadit města v tomto adresáři sestupně (od Z do A) – po vytvoření pak město listy budou seřazeny podle abecedy.
Za druhé, пpřevést obě tabulky na dynamické („chytré“), aby se s nimi lépe pracovalo. Použijeme příkaz Domů – Formát jako tabulka (Domů — Formátovat jako tabulku) nebo klávesovou zkratkou Ctrl+T. Na kartě, která se zobrazí Stavitel (Design) zavolejme jim tablProdaji и TableCity, respektive:
Metoda 1. Makro pro dělení podle listů
Na kartě Upřesnit vývojka (Vývojář) klikněte na tlačítko Visual Basic nebo použijte klávesovou zkratku Další+F11. V okně editoru maker, které se otevře, vložte přes nabídku nový prázdný modul Vložit – Modul a zkopírujte tam následující kód:
Sub Splitter() pro každou buňku v rozsahu("таблГорода") Rozsah("таблПродажи").Pole automatického filtru:=3, Criteria1:=cell.Value Range("таблПродажи[#VisibleCells]"). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Další buňka Worksheets("Данные").ShowAllData End Sub Tady se smyčkou Pro každý… Další implementoval průchod buňkami adresáře TableCity, kde se pro každé město filtruje (metoda Automatický filtr) v původní prodejní tabulce a následné zkopírování výsledků do nově vytvořeného listu. Po cestě se vytvořený list přejmenuje na stejný název města a zapne se na něm automatické přizpůsobení šířky sloupců pro krásu.
Vytvořené makro můžete spustit v Excelu na záložce vývojka tlačítko makra (Vývojář — Makra) nebo klávesovou zkratkou Další+F8.
Metoda 2. Vytvořte více dotazů v Power Query
Předchozí metoda má při vší své kompaktnosti a jednoduchosti podstatnou nevýhodu – listy vytvořené makrem se při změnách v původní prodejní tabulce neaktualizují. Pokud je nutná aktualizace za běhu, budete muset použít balíček VBA + Power Query, nebo spíše vytvořit pomocí makra nejen listy se statickými daty, ale aktualizované dotazy Power Query.
Makro je v tomto případě částečně podobné předchozímu (má také cyklus Pro každý… Další iterovat přes města v adresáři), ale uvnitř smyčky již nebude filtrování a kopírování, ale vytváření dotazu Power Query a nahrávání jeho výsledků na nový list:
Sub Splitter2() Pro každou buňku V rozsahu("Tabulka města") ActiveWorkbook.Queries.Add Název:=cell.Value, Vzorec:= _ "let" & Chr(13) & "" & Chr(10) & " Zdroj = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Kategorie"", zadejte text}, {""Jméno"", napište text}, {""Město"", napište text}, {""Manažer"", napište text}, {""Dohoda datum "", zadejte datetime}, {""Cost"", zadejte číslo}}), & Chr(13) & "" & Chr(10) & " #""Řádky s aplikovaným filtrem"" = Tabulka.Se " & _ "lectRows(#""Změněný typ"", každý ([City] = """ & cell.Value & """)" & Chr(13) & "" & Chr(10) & "v " & Chr(13) & "" & Chr(10) & " #""Řádky s aplikovaným filtrem""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Poskytovatel =Microsoft.Mashup.OleDb.1;Zdroj dat=$Sešit$;Umístění=" & 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 = Falešné slovo .Sa SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Další buňka End Sub Po jeho spuštění uvidíme stejné listy podle města, ale budou je tvořit již vytvořené dotazy Power Query:
Při jakýchkoli změnách ve zdrojových datech bude stačit aktualizovat odpovídající tabulku pravým tlačítkem myši – příkazem Aktualizovat a uložit (Obnovit) nebo hromadně aktualizovat všechna města pomocí tlačítka Aktualizovat vše Karta Data (Data – Obnovit vše).
- Co jsou makra, jak je vytvářet a používat
- Ukládání listů sešitu jako samostatných souborů
- Sběr dat ze všech listů knihy do jedné tabulky