Zostavte tabuľky s rôznymi hlavičkami z viacerých kníh

Formulácia problému

V jednom priečinku máme niekoľko súborov (v našom príklade – 4 kusy, vo všeobecnom prípade toľko, koľko chcete). Správy:

Vo vnútri tieto súbory vyzerajú takto:

Kde:

• Datasheet, ktorý potrebujeme, je vždy tzv fotografie, ale môže byť kdekoľvek v zošite.
  
• Za listom fotografie Každá kniha môže mať iné listy.
• Tabuľky s údajmi majú rôzny počet riadkov a môžu začínať iným riadkom v hárku.
• Názvy rovnakých stĺpcov v rôznych tabuľkách sa môžu líšiť (napr. Množstvo = množstvo = množstvo).
• Stĺpce v tabuľkách môžu byť usporiadané v inom poradí.

Úloha: zhromaždiť údaje o predaji zo všetkých súborov z hárku fotografie do jednej spoločnej tabuľky, aby sa na nej následne vytvoril súhrn alebo akékoľvek iné analýzy.

Krok 1. Príprava adresára názvov stĺpcov

Prvá vec, ktorú musíte urobiť, je pripraviť referenčnú knihu so všetkými možnými možnosťami pre názvy stĺpcov a ich správnu interpretáciu:

Tento zoznam prevedieme na dynamickú „inteligentnú“ tabuľku pomocou tlačidla Formátovať ako tabuľku na karte Domov (Domov – Formátovať ako tabuľku) alebo klávesová skratka ctrl+T a načítajte ho do Power Query pomocou príkazu Údaje – z tabuľky/rozsahu (Údaje – z tabuľky/rozsahu). V posledných verziách Excelu bol premenovaný na S listami (Z listu).

V okne editora dotazov Power Query tradične odstraňujeme krok Zmenený typ a pridajte namiesto neho nový krok kliknutím na tlačidlo f_xna paneli vzorcov (ak nie je viditeľný, môžete ho povoliť na karte preskúmanie) a zadajte tam vzorec v vstavanom jazyku Power Query M:

=Table.ToRows(zdroj)

Tento príkaz skonvertuje príkaz načítaný v predchádzajúcom kroku zdroj referenčnú tabuľku na zoznam pozostávajúci z vnorených zoznamov (List), z ktorých každý je zase dvojicou hodnôt Bolo-stalo z jedného riadku:

Tento typ údajov budeme potrebovať o niečo neskôr, pri hromadnom premenovávaní hlavičiek zo všetkých načítaných tabuliek.

Po dokončení prevodu vyberte príkazy Domov — Zavrieť a načítať — Zavrieť a načítať v… a typ dovozu Stačí vytvoriť spojenie (Domov — Zavrieť&Načítať — Zavrieť&Načítať do... — Vytvoriť iba pripojenie) a vráťte sa do Excelu.

Krok 2. Načítame všetko zo všetkých súborov tak, ako sú

Teraz načítajme obsah všetkých našich súborov z priečinka – zatiaľ tak, ako je. Výber tímov Údaje – Získať údaje – Zo súboru – Z priečinka (Údaje — Získať údaje — Zo súboru — Z priečinka) a potom priečinok, kde sú naše zdrojové knihy.

V okne ukážky kliknite na Konvertovať (Transformácia) or Zmena (Edit):

A potom rozbaľte obsah všetkých stiahnutých súborov (binárne) tlačidlo s dvojitými šípkami v záhlaví stĺpca obsah:

Power Query na príklade prvého súboru (Vostok.xlsx) sa nás opýta na názov hárku, ktorý si chceme zobrať z každého zošita – vyberte si fotografie a stlačte OK:

Potom (v skutočnosti) dôjde k niekoľkým udalostiam, ktoré nie sú pre používateľa zrejmé, ktorých dôsledky sú jasne viditeľné v ľavom paneli:

1. Power Query vezme prvý súbor z priečinka (budeme ho mať Vostok.xlsx — vidieť Príklad súboru) ako príklad a importuje jeho obsah vytvorením dotazu Previesť vzorový súbor. Tento dotaz bude mať niekoľko jednoduchých krokov, ako napr zdroj (prístup k súboru) Navigácia (výber listu) a prípadne zvýšenie titulkov. Táto požiadavka môže načítať údaje iba z jedného konkrétneho súboru Vostok.xlsx.
   
2. Na základe tejto požiadavky sa vytvorí funkcia, ktorá je s ňou spojená Konvertovať súbor (označené charakteristickou ikonou f_x), kde zdrojový súbor už nebude konštanta, ale premenná hodnota – parameter. Táto funkcia teda dokáže extrahovať dáta z akejkoľvek knihy, ktorú do nej vkĺzneme ako argument.
   
3. Funkcia sa postupne použije na každý súbor (binárny) zo stĺpca obsah – je za to zodpovedný krok Zavolajte vlastnú funkciu v našom dotaze, ktorý pridá stĺpec do zoznamu súborov Konvertovať súbor s výsledkami importu z každého zošita:

   

4. Extra stĺpce sú odstránené.
5. Obsah vnorených tabuliek sa rozbalí (krok Rozšírený stĺpec tabuľky) – a vidíme konečné výsledky zberu údajov zo všetkých kníh:

   
   

Krok 3. Brúsenie

Predchádzajúca snímka obrazovky jasne ukazuje, že priama montáž „ako je“ sa ukázala ako nekvalitná:

• Stĺpce sú obrátené.
• Veľa riadkov navyše (nielen prázdnych).
• Hlavičky tabuliek nie sú vnímané ako hlavičky a sú zmiešané s údajmi.
  

Všetky tieto problémy môžete vyriešiť veľmi jednoducho – stačí upraviť dopyt Convert Sample File. Všetky úpravy, ktoré v ňom vykonáme, budú automaticky spadať do pridruženej funkcie Konvertovať súbor, čo znamená, že sa neskôr použijú pri importe údajov z každého súboru.

Otvorením žiadosti Previesť vzorový súbor, pridajte kroky na filtrovanie nepotrebných riadkov (napríklad podľa stĺpca Column2) a zdvíhanie nadpisov pomocou tlačidla Použite prvý riadok ako hlavičky (Použite prvý riadok ako hlavičky). Stôl bude vyzerať oveľa lepšie.

Aby sa stĺpce z rôznych súborov neskôr automaticky zmestili pod seba, musia byť pomenované rovnako. Takéto hromadné premenovanie môžete vykonať podľa predtým vytvoreného adresára s jedným riadkom M-kódu. Opäť stlačíme tlačidlo f_x do riadka vzorcov a pridajte funkciu, ktorú chcete zmeniť:

= Table.RenameColumns(#”Elevated Headers”, Headers, MissingField.Ignore)

Táto funkcia preberá tabuľku z predchádzajúceho kroku Vyvýšené hlavičky a premenuje všetky stĺpce v ňom podľa vnoreného vyhľadávacieho zoznamu Aktuality. Tretí argument MissingField.Ignore je potrebné, aby na tých nadpisoch, ktoré sú v adresári, ale nie sú v tabuľke, nevznikla chyba.

Vlastne, to je všetko.

Návrat k žiadosti Správy uvidíme úplne iný obrázok – oveľa krajší ako ten predchádzajúci:

• Čo je Power Query, Power Pivot, Power BI a prečo ich potrebuje používateľ Excelu
  
• 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