Sledujte video - najrýchlejší spôsob, ako zrušiť rozpustenie údajov v programe Excel
Kontingenčné tabuľky sú skvelé, keď chcete analyzovať obrovské množstvo údajov za niekoľko sekúnd. Umožňuje vám tiež rýchlo vytvárať rôzne zobrazenia údajov jednoduchým pretiahnutím.
A aby ste mohli vytvoriť kontingenčnú tabuľku, musíte mať pripravené údaje v konkrétnom formáte.
V mnohých prípadoch pravdepodobne získate údaje vo formátoch, ktoré nie sú pripravené pre kontingenčnú tabuľku.
To je často prípad, keď niekto manuálne zhromažďuje údaje a vytvára formát, ktorý je pre ľudí lepšie čitateľný (nie kontingenčné tabuľky).
Niečo, ako je uvedené nižšie:
Vyššie uvedený formát údajov je niečo, čo očakávate ako výstup analýzy kontingenčnej tabuľky.
Čo teraz, keď chcete analyzovať tie isté údaje a zistiť, aké boli celkové tržby podľa jednotlivých regiónov alebo podľa jednotlivých mesiacov.
Aj keď to možno ľahko vykonať pomocou kontingenčných tabuliek, vyššie uvedené údaje bohužiaľ nemôžete vložiť do kontingenčnej tabuľky.
Údaje teda musíte zrušiť a prispôsobiť ich tak, aby boli vhodné pre kontingenčné tabuľky.
Aj keď existuje niekoľko spôsobov, ako to dosiahnuť pomocou vzorca programu Excel alebo VBA, Power Query (Získať a transformovať v programe Excel 2016) je najlepším nástrojom na odpojenie údajov.
Zrušte otočenie údajov pomocou Power Query
Tu sú kroky na zrušenie otočenia údajov pomocou Power Query:
(Ak sú vaše údaje už v tabuľke programu Excel, začnite od kroku 6 a ďalej)
- Vyberte ľubovoľnú bunku v množine údajov.
- Prejdite na kartu Vložiť.
- Kliknite na ikonu Tabuľka.
- V dialógovom okne „Vytvoriť tabuľku“ skontrolujte, či je rozsah správny. V prípade potreby môžete rozsah zmeniť.
- Kliknite na tlačidlo OK. Tým sa vaše tabuľkové údaje skonvertujú na tabuľku programu Excel.
- Po vybratí ľubovoľnej bunky v tabuľke programu Excel kliknite na kartu Údaje.
- V skupine údajov Získať a transformovať kliknite na ikonu „Z tabuľky/rozsahu“.
- V otvorenom dialógovom okne Vytvoriť tabuľku (ak sa otvorí) kliknite na tlačidlo OK. Tým sa otvorí editor dotazov pomocou údajov tabuľky programu Excel.
- V editore dotazu kliknite pravým tlačidlom myši na stĺpec Region.
- Kliknite na možnosť „Zrušiť otočenie ostatných stĺpcov“. Vaše údaje sa tým okamžite zrušia.
- Zmeňte názov stĺpca „Atribút“ na zmysluplnejší názov, napríklad „Mesiace“.
- Keď už máte nezaradené údaje, je vhodné zaistiť, aby boli všetky typy údajov správne. V tomto prípade kliknite na jednu bunku pre každý stĺpec a zobrazte typ údajov na karte Transformácia. V prípade potreby môžete tiež zmeniť typ údajov.
- (Voliteľné) Zmeňte názov svojho dopytu na „Predaj“.
- Prejdite na kartu Domov (v editore dotazov).
- Kliknite na Zavrieť a načítať.
Vyššie uvedené kroky by vyvrátili vašu množinu údajov pomocou Power Query a vložili by ju späť do Excelu ako tabuľku v novom pracovnom hárku.
Teraz môžete tieto údaje použiť na vytváranie rôznych zobrazení pomocou kontingenčnej tabuľky. Môžete napríklad skontrolovať celkovú hodnotu predaja podľa mesiaca alebo podľa regiónu.
Obnovenie dopytu po pridaní nových údajov
Toto všetko funguje dobre.
Čo sa však stane, keď sa do nášho pôvodného súboru údajov pridajú nové údaje.
Povedzme, že dostanete údaje za júl, ktoré sú v rovnakom formáte ako ten, s ktorým sme začali.
Potrebujem znova zopakovať všetky kroky, aby som mohol tieto údaje zahrnúť do svojej nekonvertovanej množiny údajov?
Odpoveď je NIE.
A to je na Power Query také úžasné. Môžete pokračovať v pridávaní nových údajov (alebo upravovať existujúce údaje) a Power Query ich okamžite aktualizuje, hneď ako ich obnovíte.
Ukážem vám, ako na to.
Predpokladajme, že nižšie uvádzam nový súbor údajov (ktorý má ďalšie údaje za júl):
Nasledujú kroky na obnovenie už vytvoreného dopytu a zrušenie otočenia týchto údajov:
- Pridajte tieto nové údaje k pôvodným údajom, ktoré ste použili na vytvorenie dotazu.
- Pretože pridávate údaje do susedného stĺpca tabuľky programu Excel, tabuľka programu Excel sa rozbalí a zahrnie do nej tieto údaje. Ak to náhodou nie je, urobte to manuálne potiahnutím malej obrátenej ikony „L“ v pravom dolnom rohu tabuľky programu Excel.
- Prejdite na kartu Údaje a kliknite na položku Dotazy a pripojenia. Zobrazí sa tabla so všetkými existujúcimi dopytmi.
- Pravým tlačidlom myši kliknite na predajný dotaz na table Dotazy.
- Kliknite na Obnoviť.
To je všetko! Vaše nové údaje sa okamžite zrušia a pridajú sa k existujúcim údajom.
Všimli ste si, že počet riadkov zobrazených v dopyte sa aktualizuje, aby vám ukázal nové čísla. V tomto prípade to bolo 24 pred obnovením a 28 po aktualizácii.
To tiež znamená, že ak ste vytvorili akékoľvek kontingenčné tabuľky pomocou údajov, ktoré ste získali z Power Query, tieto kontingenčné tabuľky sa tiež obnovia, aby vám ukázali aktualizované výsledky.
