Začněte s víceletými daty.
Řekněme, že máte dva roky podrobné záznamy. Každý záznam má denní datum. Když z této sestavy vytvoříte kontingenční tabulku, budete mít v kontingenční tabulce stovky řádků denních dat. To není moc shrnutí.
Vyberte jednu z těchto datových buněk v kontingenční tabulce. Na kartě Analýza na pásu karet zvolte Pole skupiny.
Protože se nacházíte v poli data, získáte tuto verzi dialogu Seskupení. V něm zrušte výběr Měsíců a vyberte Roky.
Denní data jsou shrnuta až na roky. Přesuňte pole Datum z ŘÁDŮ do SLOUPCŮ.
Výsledek je téměř dokonalý. Ale místo celkového součtu ve sloupci D pravděpodobně budete chtít procentuální rozptyl.
Chcete-li se zbavit sloupce Celkový součet, na kartě Návrh zvolte Celkové součty, Zapnuto pouze pro sloupce. (Souhlasím, toto je jedna z nejpodivnějších formulací v Excelu.)
Chcete-li vytvořit sloupec odchylky, musíte napsat kontingenční tabulku, která směřuje dovnitř kontingenční tabulky. Během vytváření vzorce se nedotýkejte myší ani kláves se šipkami, jinak se objeví ošklivá funkce GETPIVOTDATA. Místo toho jednoduše zadejte =C5/B5-1a stiskněte klávesu Enter.
Děkujeme Александр Воробьев za návrh tohoto tipu.
Sledovat video
- Začněte s víceletými daty
- Vložit, kontingenční tabulka
- Přetáhněte pole s datem do oblasti řádků
- Excel 2016: Stisknutím kláves Ctrl + Z rozdělíte data
- Přetáhněte výnosy do oblasti hodnot
- Vyberte libovolné datum v oblasti řádku
- Použijte pole skupiny
- Vyberte měsíce a roky
- Jak přidat mezisoučty do pole let po seskupení kontingenční tabulky
- Pomocí tabulkového formuláře v kontingenční tabulce můžete každému poli řádku dát vlastní sloupec
- Opakováním všech štítků řádků vyplňte mezery v kontingenční tabulce
- Přetáhněte roky do oblasti sloupců
- Klepněte pravým tlačítkem na záhlaví sloupce Celkový součet a odeberte
- Jak se vyhnout funkci GetPivotData, když vzorec ukazuje na kontingenční tabulku
- Chcete-li vytvořit rozptyl, zadejte vzorec bez myši nebo šipek
- Děkujeme Александр Воробьев za návrh tohoto tipu
Přepis videa
Naučte se Excel z podcastu MrExcel, epizoda 1998. Kontingenční tabulka meziročně.
Budu podcastovat celou tuto knihu, přejít, kliknout na to „i“ v pravém horním rohu a zobrazit seznam skladeb MrExcel Excel.
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnes první téma v knize o kontingenčních tabulkách a o tom, jak vytvořit meziroční zprávu. Začínáme tedy s datovou sadou, která trvá dva roky, jak vidíme, přecházíme od roku 2018 do roku 2019. Chystám se vytvořit kontingenční tabulku, Vložit, kontingenční tabulku, OK, to je v pořádku, pojďme na nový list.
Na novém listu vezmeme pole Datum a přetáhneme ho do oblasti Řádky. A pak, jak vidíte, v aplikaci Excel 2016 se věci automaticky seskupují. Nyní vím, že většina lidí je stále v aplikaci Excel 2013, možná Excel 2010, takže stiskem Ctrl + Z to vrátíte zpět. To je zkušenost, kterou získáte v letech 2010 a 2013, vezmeme pole Příjmy a přetáhneme jej do oblasti Hodnoty. Začneme tedy ošklivou zprávou, budou to stovky a stovky řádků, protože každý den jsme měli tržby. Ale půjdu do úplně prvního pole data, úplně prvního pole data, přejdu do nástrojů kontingenční tabulky, analyzovat, v letech 2013 nebo 2016 a 2010 se tomu říkalo Možnosti a zvolím Skupinové pole. Ujistěte se, že jste si vybrali datum, než vyberete Skupinové pole a posuňte věci na měsíce a roky. Nebo čtvrtletí a roky,nebo Měsíce a čtvrtletí a roky klikněte na OK. A co dostaneme, je nové virtuální pole s názvem Years down here in the Rows area.
Nyní nějaké zlosti. Je nepříjemné, že nám nedávají součty za rok, je snadné to opravit, i když v mém případě to opravit nemusím. Klikneme na Nastavení pole a Automaticky, protože neplánuji nechat Roky tady. Samozřejmě pokaždé, když vytvořím kontingenční tabulku, přejdu do nabídky Design a řeknu Show in Tabular Form, abych vše dostal do svého vlastního sloupce. A potom opakujte všechny štítky položek a vyplňte tyto mezery podél levé strany. To by zde opět nebylo nutné, protože neplánuji nechat Roky tam, kde jsou, vezmu Roky a přetáhnu je na Sloupce, a to je zpráva, pro kterou jsem střílel.
Dobře, takže máme měsíce, které jdou po levé straně, 2018 až 2019. Ve sloupci D, co opravdu chci, nechci celkový součet, kliknu pravým tlačítkem a řeknu Odstranit velký Celkem a nyní jsme mimo kontingenční tabulku. Takže,% Change, je velmi lákavé sestavit tento vzorec pomocí myši nebo kláves se šipkami, ale nemůžete, protože získáte funkci… get Pivot Data, která se nekopíruje snadno. Takže = C5 / B5-1, naformátujeme to jako procento a dvojitým kliknutím jej zkopírujeme dolů, správně. Krásná zpráva ukazující, kde jsme byli minulý rok, kde jsme byli letos a poté procentuální změna z roku na rok.
Tento tip a dalších 40 je v této knize. Pokračujte a klikněte na to „i“ v pravém horním rohu u knihy. Dobře, takže můžete začít s datovou sadou s více lety, proveďte Vložit, Kontingenční tabulka, přetáhněte pole s datem do oblasti řádků. V aplikaci Excel 2016 můžete stisknutím Ctrl + Z vrátit zpět jejich automatické aktualizace seskupení. Proč to tady prostě nenecháme? Ve skutečnosti to záleží. Pokud byste měli data, řekněme od 2. ledna do 30. prosince, nebudou tato data správně seskupovat. Abyste získali pole pro rok, musí strávit celý rok. Je možné, že vám to vyjde, jsem tak trochu podivín nad kontrolou, raději budu mít jen kontrolu, proto se vrátím zpět. K dispozici je: přetáhněte výnos do oblasti hodnot, vyberte libovolnou buňku, libovolné datum v oblasti řádků a poté seskupte pole, zvolte měsíce a roky,přetáhněte roky přes oblast sloupce, klepněte pravým tlačítkem myši na Celkový součet k odebrání a poté vytvořte rozptyl, zadejte vzorec, nepoužívejte klávesy myši nebo šipky.
Netuším, jak jej vyslovit, ale díky tomuto čtenáři (Александр Воробьев) za navržení tohoto tipu a díky vám, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast1998.xlsx
