Obsah
Formulace problému
Jako vstupní data máme soubor Excel, kde jeden z listů obsahuje několik tabulek s prodejními daty v následujícím tvaru:
Všimněte si, že:
- Tabulky různých velikostí a s různými sadami produktů a regionů v řádcích a sloupcích bez jakéhokoli řazení.
- Mezi tabulky lze vkládat prázdné řádky.
- Počet stolů může být libovolný.
Dva důležité předpoklady. Předpokládá se, že:
- Nad každou tabulkou je v prvním sloupci uvedeno jméno manažera, jehož tržby tabulka znázorňuje (Ivanov, Petrov, Sidorov atd.)
- Názvy zboží a regionů ve všech tabulkách jsou psány stejně – s přesností na malá a velká písmena.
Konečným cílem je shromáždit data ze všech tabulek do jedné ploché normalizované tabulky, vhodné pro následnou analýzu a sestavení souhrnu, tedy v této:
Krok 1. Připojte se k souboru
Vytvoříme nový prázdný soubor Excel a vybereme jej na kartě Data Příkaz Získat data – ze souboru – z knihy (Data — Ze souboru — Ze sešitu). Určete umístění zdrojového souboru s prodejními daty a poté v okně navigátoru vyberte list, který potřebujeme, a klikněte na tlačítko Převést data (Transformace dat):
V důsledku toho by se všechna data z něj měla načíst do editoru Power Query:
Krok 2. Vyčistěte odpadky
Odstraňte automaticky generované kroky upravený typ (Změněný typ) и Vyvýšené hlavičky (Propagovaná záhlaví) a pomocí filtru se zbavte prázdných řádků a řádků se součty null и DOHROMADY podle prvního sloupce. V důsledku toho dostaneme následující obrázek:
Krok 3. Přidání manažerů
Abychom později pochopili, kde jsou čí tržby, je nutné do naší tabulky přidat sloupec, kde v každém řádku bude odpovídající příjmení. Pro tohle:
1. Pomocí příkazu přidáme pomocný sloupec s čísly řádků Přidat sloupec – Indexový sloupec – od 0 (Přidat sloupec — Indexový sloupec — Od 0).
2. Pomocí příkazu přidejte sloupec se vzorcem Přidání sloupce – Vlastní sloupec (Přidat sloupec – Vlastní sloupec) a zavést tam následující konstrukci:
Logika tohoto vzorce je jednoduchá – je-li hodnota další buňky v prvním sloupci „Produkt“, znamená to, že jsme narazili na začátek nové tabulky, takže hodnotu předchozí buňky zobrazíme jméno manažera. Jinak nezobrazujeme nic, tedy null.
Chcete-li získat nadřazenou buňku s příjmením, nejprve se odkážeme na tabulku z předchozího kroku #„Přidán index“a poté zadejte název sloupce, který potřebujeme [Sloupec1] v hranatých závorkách a číslo buňky v tomto sloupci ve složených závorkách. Číslo buňky bude o jednu menší než aktuální, které převezmeme ze sloupce index, V uvedeném pořadí.
3. Zbývá vyplnit prázdné buňky null jména z vyšších buněk příkazem Transformovat – vyplnit – dolů (Transformace — Výplň — Dolů) a smažte již nepotřebný sloupec s indexy a řádky s příjmením v prvním sloupci. V důsledku toho získáme:
Krok 4. Seskupení do samostatných tabulek podle manažerů
Dalším krokem je seskupit řádky pro každého manažera do samostatných tabulek. Chcete-li to provést, použijte na kartě Transformace příkaz Seskupit podle (Transformovat – Seskupit podle) a v okně, které se otevře, vyberte sloupec Správce a operaci Všechny řádky (Všechny řádky), abyste jednoduše shromáždili data bez použití jakékoli agregační funkce na je (součet, průměr atd.). P.):
Výsledkem je, že pro každého manažera získáme samostatné tabulky:
Krok 5: Transformujte vnořené tabulky
Nyní dáme tabulky, které leží v každé buňce výsledného sloupce Všechny data ve slušném stavu.
Nejprve v každé tabulce odstraňte sloupec, který již není potřeba Manažer. Znovu používáme Vlastní sloupec Karta Proměna (Transformace – vlastní sloupec) a následující vzorec: \ t
Poté pomocí dalšího vypočítaného sloupce zvedneme první řádek v každé tabulce na nadpisy:
A nakonec provedeme hlavní transformaci – rozložení každého stolu pomocí M-funkce Table.UnpivotOtherColumns:
Názvy regionů z hlavičky přejdou do nového sloupce a získáme užší, ale zároveň delší normalizovanou tabulku. Prázdné buňky s null jsou ignorovány.
Abychom se zbavili zbytečných mezilehlých sloupců, máme:
Krok 6 Rozbalte vnořené tabulky
Zbývá rozbalit všechny normalizované vnořené tabulky do jednoho seznamu pomocí tlačítka s dvojitými šipkami v záhlaví sloupce:
…a konečně máme to, co jsme chtěli:
Výslednou tabulku můžete exportovat zpět do Excelu pomocí příkazu Domů — Zavřít a načíst — Zavřít a načíst v… (Domů — Zavřít&Načíst — Zavřít&Načíst do…).
- Sestavte tabulky s různými záhlavími z více knih
- Sběr dat ze všech souborů v dané složce
- Sběr dat ze všech listů knihy do jedné tabulky