Začnite s viacročnými údajmi.
Povedzme, že máte dvojročné záznamy podrobností. Každý záznam má denný dátum. Keď z tohto prehľadu zostavíte kontingenčnú tabuľku, budete v nej mať stovky riadkov denných dátumov. Toto nie je veľké zhrnutie.
Vyberte jednu z týchto buniek s dátumom v kontingenčnej tabuľke. Na karte Analýza na páse s nástrojmi zvoľte Skupinové pole.
Pretože ste v poli s dátumom, získate túto verziu dialógového okna Zoskupenie. V ňom zrušte výber mesiacov a vyberte roky.
Denné dátumy sa zostavujú na roky. Presuňte pole Dátum z riadkov do stĺpcov COLUMNS.
Výsledok je takmer dokonalý. Ale namiesto celkového súčtu v stĺpci D pravdepodobne chcete percentuálnu odchýlku.
Ak sa chcete zbaviť stĺpca Celkový súčet, na karte Návrh zvoľte Celkové súčty, Zapnuté iba pre stĺpce. (Súhlasím, toto je jedno z nepríjemnejších textov v programe Excel.)
Ak chcete vytvoriť stĺpec variancie, musíte napísať vzorec mimo kontingenčnú tabuľku, ktorý ukazuje dovnútra kontingenčnej tabuľky. Počas vytvárania vzorca sa nedotýkajte myši ani klávesov so šípkami, inak sa objaví nepríjemná funkcia GETPIVOTDATA. Namiesto toho jednoducho zadajte =C5/B5-1a stlačte kláves Enter.
Ďakujeme Александр Воробьев za navrhnutie tohto tipu.
Pozeraj video
- Začnite s viacročnými údajmi
- Vložiť, kontingenčná tabuľka
- Presuňte pole s dátumom do oblasti riadka
- Excel 2016: Ak chcete dátumy oddeliť, stlačte Ctrl + Z
- Presuňte výnosy do oblasti hodnôt
- Vyberte ľubovoľný dátum v oblasti riadkov
- Použite pole skupiny
- Vyberte mesiace a roky
- Ako pridať medzisúčty do poľa rokov po zoskupení kontingenčnej tabuľky
- V tabuľkovej forme v kontingenčnej tabuľke môžete dať každému poľu riadku vlastný stĺpec
- Opakujte všetky štítky riadkov, aby ste vyplnili prázdne miesta v kontingenčnej tabuľke
- Presuňte roky do oblasti stĺpca
- Pravým tlačidlom myši kliknite na hlavičku stĺpca Celkový súčet a odstráňte ju
- Ako sa vyhnúť funkcii GetPivotData, keď vzorec ukazuje na kontingenčnú tabuľku
- Ak chcete vytvoriť odchýlku, zadajte vzorec bez myši alebo šípok
- Ďakujeme Александр Воробьев za navrhnutie tohto tipu
Prepis videa
Naučte sa Excel z podcastu MrExcel, epizóda 1998. Kontingenčná tabuľka medziročne.
Budem podcastovať celú túto knihu, choďte a kliknite na to „i“ v pravom hornom rohu pre zoznam skladieb MrExcel Excel.
Ahoj, vitaj späť na netcastu, som Bill Jelen. Dnes prvá téma v knihe o kontingenčných tabuľkách a o tom, ako vytvoriť medziročnú správu. Takže začíname s množinou údajov, ktorá trvá dva roky, vidíme, že prechádzame od roku 2018 do roku 2019. Vytvorím kontingenčnú tabuľku, prílohu, kontingenčnú tabuľku, OK, je to v poriadku, poďme k nový pracovný list.
Na novom pracovnom hárku vezmeme pole Dátum a presunieme ho do oblasti Riadky. A potom, ako vidíte, v programe Excel 2016 sa veci automaticky zoskupujú. Teraz viem, že väčšina ľudí je stále v programe Excel 2013, možno Excel 2010, takže akciu vrátim späť stlačením kombinácie klávesov Ctrl + Z. To je skúsenosť, ktorú získate v rokoch 2010 a 2013. Zoberieme pole Výnosy a presunieme ho do oblasti Hodnoty. Takže začneme škaredou správou, budú to stovky a stovky riadkov, pretože každý deň sme mali tržby. Ale pôjdem do úplne prvého poľa s dátumom, do úplne prvého poľa s dátumom, do Nástrojov pre kontingenčné tabuľky, Analyzovať, v rokoch 2013 alebo 2016 a 2010 sa volalo Možnosti a vyberiem Skupinové pole. Skôr ako vyberiete možnosť Skupinové pole, nezabudnite zvoliť dátum a zoradiť veci do mesiacov a rokov. Alebo štvrťroky a roky,alebo Mesiace a Štvrťroky a Roky kliknite na OK. A dostaneme nové virtuálne pole s názvom Years down here in the Rows area.
Teraz nejaké nepríjemnosti. Je nepríjemné, že nám nedajú súčty za rok, je ľahké to napraviť, aj keď v mojom prípade to opravovať nemusím. Klikneme na Nastavenia poľa a Automaticky, pretože neplánujem nechať Roky tu. Samozrejme vždy, keď vytvorím kontingenčnú tabuľku, prejdem do časti Návrh a poviem Zobraziť v tabuľkovej podobe, aby som dostal všetko do svojho vlastného stĺpca. A potom opakujte všetky štítky položiek, aby ste vyplnili tieto prázdne miesta na ľavej strane. To by tu opäť nebolo potrebné, pretože neplánujem nechať Roky tam, kde sú, vezmem Roky a pretiahnem to na Stĺpy, a to je správa, pre ktorú som točil.
Dobre, takže máme mesiace, ktoré idú dole po ľavej strane, 2018 až 2019. V stĺpci D, čo vlastne chcem, nechcem celkový súčet, kliknem pravým tlačidlom myši a poviem Remove the Grand Celkovo a teraz sme mimo kontingenčnej tabuľky. Takže,% Change, je veľmi lákavé zostaviť tento vzorec pomocou myši alebo klávesov so šípkami, ale nemôžete, pretože získate… funkciu Pivot Data, ktorá sa nedá ľahko skopírovať. Takže = C5 / B5-1, naformátujeme to ako percento a dvojitým kliknutím ho skopírujeme dole, vpravo. Krásna správa ukazujúca, kde sme boli minulý rok, kde sme boli tento rok a potom percentuálna zmena z roka na rok.
Tento tip a ďalších 40 je v tejto knihe. Pokračujte kliknutím na to „i“ v pravom hornom rohu pri knihe. Dobre, takže ak chcete začať s množinou údajov s viacerými rokmi, urobte Vložiť, Kontingenčná tabuľka, potiahnite pole s dátumom do oblasti riadkov. A v programe Excel 2016 stlačte Ctrl + Z a zrušte ich automatické aktualizácie zoskupení. Prečo to nenecháme tu? No, to vlastne záleží. Ak by ste mali údaje napríklad od 2. januára do 30. decembra, nebudú sa tieto údaje správne zoskupovať. Aby ste získali ročné pole, musí to trvať celý rok. Je možné, že vám to vychádza, som trochu kontrolný čudák, radšej budem mať iba kontrolu, preto to vraciam späť. Existuje: pretiahnite výnos do oblasti hodnôt, vyberte ľubovoľnú bunku, akýkoľvek dátum v oblasti riadkov a potom zoskupte pole, vyberte mesiace a roky,pretiahnite roky po ploche stĺpca, kliknite pravým tlačidlom myši na celkový súčet, aby ste ich odstránili, a potom vytvorte varianciu, zadajte vzorec, nepoužívajte klávesy myši alebo šípky.
Netuším, ako ho vysloviť, ale vďaka tomuto čitateľovi (Александр Воробьев) za navrhnutie tohto tipu a vďaka vám, že ste sa pri ňom zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast1998.xlsx
