Datový model aplikace Excel (Power Pivot) umožňuje připojit velkou podrobnou datovou sadu skutečností k rozpočtu nejvyšší úrovně pomocí stolárských tabulek.
Rozpočty se provádějí na nejvyšší úrovni - výnosy podle produktové řady podle regionu podle měsíce. Skutečnosti se časem hromadí pomalu - faktura po faktuře, řádková položka po řádkové položce. Porovnání malého souboru rozpočtu s objemnými údaji o skutečných událostech bylo navždy bolestí. Líbí se mi tento trik od Roba Collieho, známého jako PowerPivotPro.com.
Chcete-li nastavit příklad, máte 54řádkovou rozpočtovou tabulku: jeden řádek za měsíc na region a produkt.
Soubor faktury je na úrovni podrobností: letos zatím 422 řádků.
Na světě neexistuje VLOOKUP, který by vám kdy umožnil porovnat tyto dvě datové sady. Ale díky Power Pivotu (neboli datovému modelu v aplikaci Excel 2013+) je to snadné.
Chcete-li propojit dvě větší datové sady, musíte vytvořit malé tabulky, které nazývám „truhláři“. V mém případě jsou Product, Region a Date mezi oběma tabulkami společné. Tabulka Produkt je malá čtyřčlánková tabulka. Totéž pro region. Vytvořte každý z nich zkopírováním dat z jedné tabulky a pomocí Odebrat duplikáty.
Vytváření kalendářového stolu vpravo bylo ve skutečnosti těžší. Údaje o rozpočtu mají jeden řádek za měsíc, vždy spadají na konec měsíce. Údaje na faktuře zobrazují denní data, obvykle pracovní dny. Musel jsem tedy zkopírovat pole Datum z obou datových sad do jednoho sloupce a poté odstranit duplikáty, abych se ujistil, že jsou zastoupena všechna data. Poté jsem =TEXT(J4,"YYYY-MM")vytvořil denní sloupec z denních dat.
Pokud nemáte úplný doplněk Power Pivot, musíte z tabulky rozpočtu vytvořit kontingenční tabulku a zaškrtnout políčko Přidat tato data do datového modelu.
Jak je popsáno v předchozím tipu, při přidávání polí do kontingenční tabulky budete muset definovat šest vztahů. I když jste to mohli udělat se šesti návštěvami dialogu Vytvořit vztah, spustil jsem svůj doplněk Power Pivot a definoval šest vztahů pomocí zobrazení diagramu.
Zde je klíč k tomu, aby celá tato práce fungovala: Můžete používat číselná pole z rozpočtu a ze skutečného. Ale pokud chcete v kontingenční tabulce zobrazit Region, Produkt nebo Měsíc, musí pocházet z truhlářských tabulek!
Zde je kontingenční tabulka s daty pocházejícími z pěti tabulek. Sloupec A přichází od truhláře regionu. Řádek 2 pochází od truhláře kalendáře. Průřez produktu pochází od truhláře produktu. Čísla rozpočtu pocházejí z tabulky rozpočtu a skutečná čísla pocházejí z tabulky faktur.
Funguje to, protože stolní tabulky použijí filtry na tabulku Rozpočet a Skutečná. Je to nádherná technika a ukazuje, že Power Pivot není jen pro velká data.
Sledovat video
- Máte malou sadu dat rozpočtu shora dolů
- Chcete se srovnat s datovou sadou skutečných údajů zdola nahoru
- Skutečnosti mohou pocházet z registru faktur
- Datový model vám umožní porovnat tyto datové sady různé velikosti
- Vytvořte obě sady dat do tabulky Ctrl + T.
- Pro každé textové pole, které chcete nahlásit, vytvořte tabulku pro připojení
- Zkopírujte hodnoty a odeberte duplikáty
- U dat můžete zahrnout data z obou tabulek a převést je na konec měsíce
- Udělejte z truhlářů tabulky Ctrl + T.
- Volitelné, ale užitečné pro pojmenování všech pěti tabulek
- Vytvořte kontingenční tabulku z rozpočtu a vyberte datový model
- Vytvořte kontingenční tabulku pomocí rozpočtu a skutečné z původních tabulek
- Všechna ostatní pole musí pocházet z truhlářských tabulek
- Přidejte kráječe podle produktu
- Vytvořte tři vztahy od rozpočtu po truhláře
- Vytvořte tři vztahy od skutečných po truhláře
- Zítra: jak je budování vztahů jednodušší pomocí vzorců Power Pivot a DAX
Přepis videa
Naučte se Excel z podcastu, epizoda 2016 - Rozpočet shora dolů oproti skutečnosti zdola nahoru!
Ahoj, podcastuji celou tuto knihu, kliknu na to „i“ v pravém horním rohu a následuj seznam skladeb.
Hej, přeruším to, tohle je Bill Jelen od 15 minut. Teď si uvědomuji, že se jedná o neuvěřitelně dlouhý podcast, a jste v pokušení kliknout přímo skrz něj, ale dovolte mi, abych vám to zkrátil. Pokud jste v aplikaci Excel 2013 a někdy jste měli malou tabulku rozpočtu a obrovskou tabulku skutečných skutečností a musíte je mapovat společně, je to úžasná nová schopnost, kterou máme v aplikaci Excel 2013, což mnoho lidí nevysvětlilo a pravděpodobně o tom nevíš. Pokud jste to vy, jste v roce 2013 a potřebujete zmapovat tyto dvě datové sady, věnovat čas, možná dnes, možná zítra, možná přidat do seznamu sledování, stojí to za to, je to úžasná technika.
Dobře, tady je to, co máme, na levé straně máme rozpočet, tento rozpočet, je to na nejvyšší úrovni, shora dolů, vpravo pro každou produktovou řadu, pro každý region, pro každý měsíc, je tu rozpočet . Není tu mnoho záznamů, počet 55, na pravé straně se to snažíme porovnat se skutečností. Skutečnosti pocházejí z registru faktur, takže máme Region, Produkt a Výnosy, ale jsou to jednotlivé faktury, mnohem více údajů zde, už jsme v polovině roku a já už mám 423 záznamů. Dobře, tak jak zmapujete těchto 55 na těchto 423? Možná to bude těžké udělat s VLOOKUP, musíte nejprve shrnout, ale naštěstí v aplikaci Excel 2013 to díky datovému modelu dělá opravdu, opravdu snadné. To, co potřebujeme, aby tento velký masivní stůl mohl komunikovat s tímto malým stolem, jsou prostředníci, já jim říkám truhláři.Malé malé tabulky, Produkt, Region a Kalendář, připojíme se k rozpočtu k těmto třem stolům, připojíme se k těmto třem stolům a zázračně bude fungovat kontingenční tabulka. Dobře, tak to tady děláme.
Nejprve musím vytvořit truhláře, takže vezmu toto pole Produkt ze sloupce A a zkopíruji ho do sloupce F a poté Data, Odebrat duplikáty, kliknout na OK, a zbývá nám malá malá tabulka, 1 nadpis 3 řádky. Totéž pro Region, vezměte regiony, Ctrl + C, přejděte do sloupce G, Vložit, Odebrat duplikáty, klikněte na OK, 3 řádky 1 záhlaví, dobře. Nyní pro data nejsou data stejná, jedná se o data konce měsíce, jsou ve skutečnosti uložena jako data konce měsíce a jedná se o dny v týdnu. Vezmu oba seznamy, Ctrl + C druhý seznam a vložit jej sem, Ctrl + V, pak si vezmu kratší seznam, zkopíruji to a vložíte jej níže, v pořádku. A je opravdu nepříjemné, že i když jsou uloženy jako data, zobrazují se jako měsíce a funkce Odebrat duplikáty je neuvidí stejně.Takže než použiji Odstranit duplikáty, musím to změnit na krátké datum. Vyberte tato data, Data, Odebrat duplikáty, klikněte na OK a poté trochu seřaďte, aby fungovaly.
Dobře, teď nechci hlásit podle denního data, takže sem přidám sloupec, vyhledávací sloupec, který říká Měsíc, a to bude stejné EOMONTH jako toto datum, 0, což nás dostane konec měsíce. Naformátuje to jako krátké datum a zkopíruje to dolů, dobře. Nyní musíme z každého z nich udělat tabulku Ctrl + T, takže odtud Ctrl + T, můj stůl má záhlaví, krásné. Ti malí, neuvědomuje si, že to jsou hlavičky tam nahoře, takže se musíme ujistit, že je to zaškrtnuto a Ctrl + T, dobře, a nazývají tyto tabulky Table1, Table2, Table3, opravdu nudná jména, že? Přejdu je tedy přejmenovat a nazvat je BudTable, ProdTable, RegTable, můj CalTable a potom ActTable, dobře.
Začínáme od první tabulky a mimochodem, že dnes nebudeme používat PowerPivot, uděláme to všechno s datovým modelem. Excel 2013 nebo novější tedy máte tuto kontingenční tabulku Vložit, zaškrtneme políčko „Přidat tato data do datového modelu“, klikneme na OK a dostaneme náš seznam polí pomocí magického tlačítka Vše, které umožňuje já si vyberu ze všech pěti tabulek v sešitu, Skutečné, Rozpočet, Kalendář, Produkt, Region. Dobře, takže čísla budou pocházet z tabulky rozpočtu, vložím tam rozpočet a ze skutečné tabulky tam vložím skutečné, ale pak je tu věc pro zbytek kontingenční tabulky. Jakákoli další textová pole, která vložíme do oblasti řádku nebo oblasti sloupce nebo jako průřezy, musí pocházet od truhlářů, musí pocházet z těchto tabulek mezi tabulkami.
Dobře, takže z tabulky Kalendář vezmeme to pole Měsíc a umístíme ho přes horní část, budeme teď ignorovat další vztahy. Budu vytvářet vztahy, ale chci je vytvořit všechny najednou. A tabulka Region dát regiony dolů. Mohl bych dát produkty dolů, ale ve skutečnosti budu používat produktovou tabulku jako průřez, takže Analyzovat, Vložit průřez, znovu musíte přejít na Vše, pokud jste ještě produktovou tabulku nepoužívali. Jděte tedy na Vše a uvidíte, že Produkt je k dispozici k vytvoření jako kráječ z produktů, jako je tento. Dobře, v tuto chvíli jsme nevytvořili vztahy, takže všechna tato čísla jsou špatná. A vztahy, které musíme vytvořit, musíme vytvořit 3 tabulky z této malé rozpočtové tabulky, jednu pro produkty, jednu pro regiony, jednu pro kalendář,to jsou 3 vztahy. A pak musíme vytvořit vztahy z aktuální tabulky do oblasti produktu v kalendáři, tedy celkem 6 tabulek. A ano, rozhodně by to bylo jednodušší, kdybychom měli PowerPivot, ale ne nebo předpokládejme, že ne.
A tak použiji staromódní způsob, dialog Vytvořit zde, kde máme vlevo tabulku rozpočtu a použijeme pole Region a spojíme to s tabulkou Region, pole Region . Dobře, 1/6 jsou vytvořeny. Zvolím Vytvořit, znovu z tabulky Rozpočet přejdeme k Produktu a poté jej propojím s tabulkou Produktu, do Produktu, kliknu na OK. V tabulce Rozpočet v poli Datum přejdeme k tabulce Kalendář a v poli Osud klikneme na OK, jsme v polovině cesty, dobře. Z tabulky Skutečnosti přejdeme Region, do tabulky Region, klikneme na OK, z tabulky Skutečnosti do produktu a z tabulky Skutečnosti do kalendáře. Vlastně vezmu hodnoty a nechám to jít dolů, dobře. Design, Layout Report, Show in Tabular Form to get a view that I prefer, Repeat All Item Labels, alright,to je naprosto úžasné! Nyní máme tuto malou malou tabulku, 50 záznamů v této tabulce stovek záznamů a díky datovému modelu jsme vytvořili jedinou kontingenční tabulku. U každého, kde vidíme rozpočet, vidíme výnosy, jsou rozděleny podle oblastí, jsou rozděleny podle měsíců a lze je rozdělit podle produktů.
Tento koncept ke mně přišel od Roba Collieho, který provozuje Power Pivot Pro, a Rob vytvořil mnoho knih, jeho nejnovější je „Power Pivot a Power BI“. Myslím, že tenhle byl ve skutečnosti v knize „Power Pivot Alchemy“, to je ta, kterou jsem viděl a řekl jsem: „No, i když nemám miliony řádků, které bych měl hlásit prostřednictvím Power Pivot, je to ten, který by udělaly v mém životě OBROVSKÝ rozdíl, protože jsem měl dvě datové sady neodpovídajících velikostí a musel jsem podávat zprávy z obou. “ Tento příklad a mnoho dalších je v této knize, nakonec dostanu celý knižní podcast, který vypadá, že to bude trvat dva a půl měsíce. Ale dnes můžete získat celou knihu, jít tam, koupit si knihu, 10 $ za e-knihu, 25 $ za tištěnou knihu a můžete mít všechny tyto tipy najednou.
Dobře, opravdu dlouhá epizoda zde: máme malý rozpočet shora dolů a zdola nahoru Skutečné, mají různé velikosti, ale s využitím datového modelu v aplikaci Excel 2013… A mimochodem, pokud jste v roce 2010, můžete teoreticky to udělejte tak, že získáte doplněk Power Pivot a všechny tyto kroky provedete zpět v roce 2010. Vytvořte obě datové sady do tabulky Ctrl + T a poté připojte své tabulky k čemukoli, o čem chcete hlásit, v popisek řádku nebo popisek sloupce nebo průřezy, takže tyto hodnoty zkopírujte a pro data odeberte duplikáty. Ve skutečnosti jsem vzal hodnoty z obou tabulek, protože v každé byly nějaké jedinečné hodnoty, a pak jsem použil EOMONTH, abych se dostal ven, aby tyto stolní tabulky byly řízené tabulky. Je to volitelné, ale pojmenoval jsem všech 5 tabulek, protože je snazší, když nastavujete tyto vztahy, než abyste se jmenovali Table1,Tabulka2, Tabulka3.
Začněte tedy v tabulce Rozpočet, Vložit, Kontingenční tabulka, zaškrtněte políčko Datový model a poté vytvořte kontingenční tabulku pomocí Rozpočtu a Skutečné. Všechno ostatní pochází z truhlářských tabulek, takže oblast a měsíc v oblasti řádků a sloupců, průřezy pocházely z tabulky produktů. A pak jsme museli vytvořit 3 vztahy z rozpočtu na truhláře, 3 vztahy ze skutečného na truhláře a máme úžasnou kontingenční tabulku. Nyní zítra se podíváme na používání karty Power Pivot a vytvoření několika dalších výpočtů. To vše je možné, je to, když chceme vložit vypočítané pole, to je situace, kdy musíte platit 2 $ navíc měsíčně, abyste získali verzi Pro Plus Office 365.
No, díky Rob Collie z Power Pivot Pro za tento tip a díky vám, že jste se zastavili, uvidíme se příště na dalším netcastu od!
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2016.xlsx
