Obsah
Formulácia problému
Ako vstupné údaje máme excelovský súbor, kde jeden z hárkov obsahuje niekoľko tabuliek s údajmi o predaji v nasledujúcom tvare:
Poznač si to:
- Tabuľky rôznych veľkostí a s rôznymi sadami produktov a oblastí v riadkoch a stĺpcoch bez akéhokoľvek triedenia.
- Medzi tabuľky je možné vkladať prázdne riadky.
- Počet stolov môže byť ľubovoľný.
Dva dôležité predpoklady. Predpokladá sa, že:
- Nad každou tabuľkou je v prvom stĺpci uvedené meno manažéra, ktorého tržby tabuľka zobrazuje (Ivanov, Petrov, Sidorov atď.)
- Názvy tovarov a regiónov vo všetkých tabuľkách sú písané rovnako – s presnosťou na malé a veľké písmená.
Konečným cieľom je zhromaždiť údaje zo všetkých tabuliek do jednej plochej normalizovanej tabuľky, vhodnej na následnú analýzu a zostavenie súhrnu, teda do tejto:
Krok 1. Pripojte sa k súboru
Vytvorme nový prázdny súbor programu Excel a vyberte ho na karte dátum povel Získať údaje – zo súboru – z knihy (Údaje — Zo súboru — Zo zošita). Zadajte umiestnenie zdrojového súboru s údajmi o predaji a potom v okne navigátora vyberte hárok, ktorý potrebujeme, a kliknite na tlačidlo Previesť údaje (Transformácia údajov):
V dôsledku toho by sa všetky údaje z neho mali načítať do editora Power Query:
Krok 2. Vyčistite odpadky
Odstrániť automaticky generované kroky upravený typ (Zmenený typ) и Vyvýšené hlavičky (Propagované hlavičky) a pomocou filtra sa zbavte prázdnych riadkov a riadkov so súčtom null и CELKOM podľa prvého stĺpca. V dôsledku toho dostaneme nasledujúci obrázok:
Krok 3. Pridanie manažérov
Aby sme neskôr pochopili, kde sú koho tržby, je potrebné do našej tabuľky pridať stĺpec, kde v každom riadku bude zodpovedajúce priezvisko. Pre to:
1. Pridajme pomocou príkazu pomocný stĺpec s číslami riadkov Pridať stĺpec – Indexový stĺpec – od 0 (Pridať stĺpec — Indexový stĺpec — Od 0).
2. Pomocou príkazu pridajte stĺpec so vzorcom Pridanie stĺpca – vlastný stĺpec (Pridať stĺpec – vlastný stĺpec) a zaviesť tam nasledujúcu konštrukciu:
Logika tohto vzorca je jednoduchá – ak je hodnota nasledujúcej bunky v prvom stĺpci „Produkt“, znamená to, že sme narazili na začiatok novej tabuľky, takže hodnotu predchádzajúcej bunky zobrazíme meno konateľa. V opačnom prípade nezobrazujeme nič, teda null.
Ak chcete získať nadradenú bunku s priezviskom, najprv sa odkážeme na tabuľku z predchádzajúceho kroku #„Pridaný index“a potom zadajte názov stĺpca, ktorý potrebujeme [Stĺpec 1] v hranatých zátvorkách a číslo bunky v tomto stĺpci v zložených zátvorkách. Počet buniek bude o jeden menší ako aktuálny, ktorý preberieme zo stĺpca index, Resp.
3. Zostáva vyplniť prázdne bunky null mená z vyšších buniek príkazom Transform – Fill – Down (Transformácia – vyplnenie – dole) a vymažte už nepotrebný stĺpec s indexmi a riadky s priezviskami v prvom stĺpci. V dôsledku toho dostaneme:
Krok 4. Zoskupenie do samostatných tabuliek podľa manažérov
Ďalším krokom je zoskupenie riadkov pre každého manažéra do samostatných tabuliek. Ak to chcete urobiť, na karte Transformácia použite príkaz Zoskupiť podľa (Transformovať – Zoskupiť podľa) a v okne, ktoré sa otvorí, vyberte stĺpec Správca a operáciu Všetky riadky (Všetky riadky), aby ste jednoducho zhromaždili údaje bez použitia akejkoľvek agregačnej funkcie na ich (súčet, priemer atď.). P.):
Výsledkom je, že pre každého manažéra dostaneme samostatné tabuľky:
Krok 5: Transformujte vnorené tabuľky
Teraz dáme tabuľky, ktoré ležia v každej bunke výsledného stĺpca Všetky údaje v slušnom stave.
Najprv odstráňte stĺpec, ktorý už nie je potrebný v každej tabuľke manažér. Používame znova Vlastný stĺpec pútko Premena (Transformácia – vlastný stĺpec) a nasledujúci vzorec:
Potom pomocou ďalšieho vypočítaného stĺpca zdvihneme prvý riadok v každej tabuľke na nadpisy:
A nakoniec vykonáme hlavnú transformáciu – rozloženie každého stola pomocou M-funkcie Table.UnpivotOtherColumns:
Názvy regiónov z hlavičky prejdú do nového stĺpca a získame užšiu, no zároveň dlhšiu normalizovanú tabuľku. Prázdne bunky s null sú ignorované.
Aby sme sa zbavili nepotrebných medziľahlých stĺpcov, máme:
Krok 6 Rozbaľte vnorené tabuľky
Zostáva rozbaliť všetky normalizované vnorené tabuľky do jedného zoznamu pomocou tlačidla s dvojitými šípkami v hlavičke stĺpca:
...a konečne sme dostali to, čo sme chceli:
Výslednú tabuľku môžete exportovať späť do Excelu pomocou príkazu Domov — Zavrieť a načítať — Zavrieť a načítať v… (Domov — Zavrieť&Načítať — Zavrieť&Načítať do...).
- Zostavte tabuľky s rôznymi hlavičkami z viacerých kníh
- Zhromažďovanie údajov zo všetkých súborov v danom priečinku
- Zhromažďovanie údajov zo všetkých listov knihy do jednej tabuľky