Sledujte video - nejrychlejší způsob, jak zrušit otočení dat v aplikaci Excel
Kontingenční tabulky jsou skvělé, když chcete analyzovat obrovské množství dat během několika sekund. Umožňuje také rychle vytvářet různá zobrazení dat pouhým přetažením.
A k vytvoření kontingenční tabulky potřebujete mít data v konkrétním formátu připraveném pro kontingenční tabulku.
V mnoha případech pravděpodobně získáte data ve formátech, které nejsou připraveny pro kontingenční tabulku.
To je často případ, kdy někdo ručně shromažďuje data a vytváří formát, který je pro lidi čitelnější (nikoli kontingenční tabulky).
Něco, jak je uvedeno níže:
Výše uvedený formát dat je něco, co očekáváte jako výstup analýzy kontingenční tabulky.
Co teď, když chcete analyzovat stejná data a zjistit, jaké byly celkové tržby podle jednotlivých regionů nebo podle každého měsíce.
I když to lze snadno provést pomocí kontingenčních tabulek, výše uvedená data bohužel nelze přenést do kontingenční tabulky.
Potřebujete tedy zrušit otočení dat a učinit je přátelskými pro kontingenční tabulku.
I když existuje několik způsobů, jak to provést pomocí vzorce aplikace Excel nebo VBA, Power Query (Získat a transformovat v aplikaci Excel 2016) je nejlepším nástrojem pro odpojení dat.
Zrušení otočení dat pomocí Power Query
Zde jsou kroky k odpojení dat pomocí Power Query:
(Pokud jsou vaše data již v tabulce aplikace Excel, začněte od kroku 6)
- Vyberte libovolnou buňku v datové sadě.
- Přejděte na kartu Vložit.
- Klikněte na ikonu Tabulka.
- V dialogovém okně „Vytvořit tabulku“ zkontrolujte, zda je rozsah správný. V případě potřeby můžete rozsah upravit.
- Klikněte na OK. Tím se vaše tabulková data převedou do tabulky aplikace Excel.
- S libovolnou buňkou vybranou v tabulce aplikace Excel klikněte na kartu Data.
- Ve skupině dat Získat a transformovat klikněte na ikonu „Z tabulky/rozsahu“.
- V dialogovém okně Vytvořit tabulku, které se otevře (pokud se otevře), klikněte na OK. Tím se otevře editor dotazů pomocí dat tabulky Excel.
- V editoru dotazů klikněte pravým tlačítkem na sloupec Region.
- Klikněte na možnost „Zrušit otočení ostatních sloupců“. Vaše data budou okamžitě zrušena.
- Změňte název sloupce „Atribut“ na smysluplnější název, například „Měsíce“.
- Jakmile máte data bez kontingence, je vhodné zajistit, aby všechny typy dat byly správné. V tomto případě klikněte na jednu buňku pro každý sloupec a zobrazte datový typ na kartě Transformace. V případě potřeby můžete také změnit datový typ.
- (Volitelné) Změňte název dotazu na „Prodej“.
- Přejděte na kartu Domů (v editoru dotazů).
- Klikněte na Zavřít a načíst.
Výše uvedené kroky by zrušily otočení sady dat pomocí Power Query a vložily by se zpět do Excelu jako tabulka v novém listu.
Nyní můžete tato data použít k vytváření různých zobrazení pomocí kontingenční tabulky. Můžete například zkontrolovat celkovou hodnotu prodeje podle měsíce nebo podle oblasti.
Obnovení dotazu při přidání nových dat
Tohle všechno funguje dobře.
Co se ale stane, když jsou do naší původní sady dat přidána nová data.
Řekněme, že získáte data za červenec, která jsou ve stejném formátu jako ta, se kterou jsme začali.
Musím znovu zahrnout všechny tyto kroky, abych zahrnul tato data do své neotočené datové sady?
Odpověď je ne.
A právě to je na Power Query tak úžasné. Můžete pokračovat v přidávání nových dat (nebo upravovat stávající data) a Power Query by je okamžitě aktualizovalo, jakmile je obnovíte.
Ukážu vám, jak na to.
Předpokládejme, že níže je nová datová sada, kterou dostanu (která má další data za červenec):
Zde jsou kroky k aktualizaci již vytvořeného dotazu a zrušení otočení těchto dat:
- Přidejte tato nová data k původním datům, která jste použili k vytvoření dotazu.
- Protože přidáváte data do sousedního sloupce tabulky aplikace Excel, tabulka aplikace Excel se rozbalí a zahrne do ní tato data. Pokud to náhodou není, udělejte to ručně přetažením malé převrácené ikony „L“ v pravé dolní části tabulky aplikace Excel.
- Přejděte na kartu Data a klikněte na Dotazy a připojení. Zobrazí se podokno se všemi existujícími dotazy.
- Klikněte pravým tlačítkem na prodejní dotaz v podokně Dotazy.
- Klikněte na Obnovit.
A je to! Vaše nová data se okamžitě zruší a přidají se ke stávajícím datům.
Všimli byste si, že počet řádků zobrazených v dotazu se aktualizuje, aby vám ukázal nová čísla. V tomto případě to bylo 24 před obnovou a 28 po aktualizaci.
To také znamená, že pokud jste vytvořili jakékoli kontingenční tabulky pomocí dat, která jste získali z Power Query, tyto kontingenční tabulky se také obnoví, aby vám zobrazily aktualizované výsledky.
