Rozpočet verzus skutočný - tipy pre Excel

Dátový model programu Excel (Power Pivot) vám umožňuje pripojiť veľkú podrobnú množinu údajov o skutočnostiach k rozpočtu na najvyššej úrovni pomocou stolárskych tabuliek.

Rozpočty sa robia na najvyššej úrovni - výnosy podľa produktových radov podľa regiónov podľa mesiacov. Skutočnosti sa časom hromadia pomaly - faktúra po faktúre, riadková položka po riadkovej položke. Porovnanie malého súboru rozpočtu s objemnými údajmi o skutočných skutočnostiach bolo navždy utrpením. Páči sa mi tento trik od Roba Collieho, alias PowerPivotPro.com.

Na nastavenie príkladu máte 54-riadkovú rozpočtovú tabuľku: jeden riadok mesačne na región a produkt.

Vzorový súbor údajov

Súbor faktúr je na úrovni podrobností: tento rok zatiaľ 422 riadkov.

Detailné zobrazenie faktúry

Na svete neexistuje VLOOKUP, ktorý by vám kedykoľvek umožnil priradiť tieto dve množiny údajov. Vďaka Power Pivotu (alias dátový model v programe Excel 2013+) je to ľahké.

Musíte vytvoriť malé malé tabuľky, ktoré nazývam stolári, aby ste prepojili dve väčšie množiny údajov. V mojom prípade sú Product, Region a Date medzi týmito dvoma tabuľkami spoločné. Tabuľka Produkt je malá štvorčlánková tabuľka. To isté pre región. Vytvorte ich každý tak, že skopírujete údaje z jednej tabuľky a použijete príkaz Odstrániť duplikáty.

George Berlin
Stolári

Tabuľka kalendára vpravo sa skutočne vytvárala ťažšie. Údaje o rozpočte majú jeden riadok mesačne, vždy však pripadajú na koniec mesiaca. Fakturačné údaje zobrazujú denné dátumy, zvyčajne pracovné dni. Musel som teda skopírovať pole Dátum z oboch súborov údajov do jedného stĺpca a potom odstrániť duplikáty, aby som sa ubezpečil, že sú zastúpené všetky dátumy. =TEXT(J4,"YYYY-MM")Z denných dátumov som potom vytvoril stĺpec Mesiac.

Ak nemáte úplný doplnok Power Pivot, musíte z tabuľky rozpočtu vytvoriť kontingenčnú tabuľku a začiarknuť políčko Pridať tieto údaje do dátového modelu.

Pridať do dátového modelu

Ako bolo uvedené v predchádzajúcom tipe, pri pridávaní polí do kontingenčnej tabuľky budete musieť definovať šesť vzťahov. Aj keď ste to mohli urobiť so šiestimi návštevami dialógového okna Vytvoriť vzťah, spustil som svoj doplnok Power Pivot a na definovanie týchto šiestich vzťahov som použil zobrazenie diagramu.

Vytvorte dialógové okno vzťahov

Tu je kľúč k tomu, aby celá táto práca fungovala: Môžete slobodne používať numerické polia z možností Rozpočet a Skutočné. Ak však chcete v kontingenčnej tabuľke zobraziť oblasť, produkt alebo mesiac, musia pochádzať z tabuliek stolárov!

Kľúčový bod

Tu je kontingenčná tabuľka s údajmi pochádzajúcimi z piatich tabuliek. Stĺpec A pochádza od stolára regiónu. 2. riadok pochádza od stolára z Kalendára. Krájač produktov je od produktového stolára. Čísla rozpočtu pochádzajú z tabuľky rozpočtu a skutočné čísla pochádzajú z tabuľky faktúry.

Výsledok

Funguje to preto, lebo stolárske tabuľky používajú filtre na tabuľku Rozpočet a Skutočné. Je to krásna technika a ukazuje, že Power Pivot neslúži iba na veľké dáta.

Pozeraj video

  • Máte malú množinu údajov o rozpočte zhora nadol
  • Chcete to porovnať so súborom údajov so skutočnými údajmi smerom zdola nahor
  • Skutočnosti môžu pochádzať z registra faktúr
  • Dátový model vám umožní porovnať tieto súbory údajov rôznej veľkosti
  • Vytvorte obe množiny údajov do tabuľky Ctrl + T.
  • Pre každé textové pole, ktoré chcete nahlásiť, vytvorte stolársku tabuľku
  • Skopírujte hodnoty a odstráňte duplikáty
  • Pre dátumy môžete zahrnúť dátumy z oboch tabuliek a previesť ich na koniec mesiaca
  • Urobte z truhlárov tabuľky Ctrl + T.
  • Voliteľné, ale užitočné pri pomenovaní všetkých piatich tabuliek
  • Vytvorte kontingenčnú tabuľku z rozpočtu a vyberte dátový model
  • Z pôvodných tabuliek môžete zostaviť kontingenčnú tabuľku pomocou možnosti Rozpočet a Skutočná
  • Všetky ostatné polia musia pochádzať z stolárskych tabuliek
  • Pridajte krájače podľa produktu
  • Vytvorte tri vzťahy od rozpočtu po stolára
  • Vytvorte tri vzťahy od skutočných po stolárov
  • Zajtra: ako je budovanie vzťahov jednoduchšie pomocou vzorcov Power Pivot a DAX

Prepis videa

Naučte sa Excel z podcastu, epizóda 2016 - Rozpočet zhora nadol oproti skutočným hodnotám zdola nahor!

Ahoj, podcastujem celú túto knihu, kliknite na písmeno „i“ v pravom hornom rohu a postupujte podľa zoznamu skladieb.

Hej, preruším to, tu je Bill Jelen o 15 minút. Teraz si uvedomujem, že ide o neuveriteľne dlhý podcast, a vy ste v pokušení jednoducho sa cez neho prekliknúť, ale dovoľte mi, aby som vám to priblížil. Ak ste v programe Excel 2013 a niekedy ste mali malú rozpočtovú tabuľku a rozsiahlu tabuľku skutočných údajov a musíte ich zmapovať spoločne, je to úžasná nová schopnosť, ktorú máme v programe Excel 2013 a ktorú vysvetlilo málo ľudí , a asi o tom nevieš. Ak ste to práve vy, ste v roku 2013 a potrebujete zmapovať tieto dva súbory údajov, venovať si čas, možno dnes, možno zajtra, možno pridať do zoznamu sledovaných položiek, stojí to za to, je to úžasná technika.

Dobre, máme to, na ľavej strane máme rozpočet, tento rozpočet sa robí na najvyššej úrovni, zhora nadol, vpravo pre každý produktový rad, pre každý región, pre každý mesiac, je tu rozpočet . Nie je tu veľa záznamov, počet 55, na pravej strane sa to snažíme porovnať so skutočnosťou. Skutočnosti pochádzajú z registra faktúr, takže máme Región, Produkt a Výnosy, ale sú to jednotlivé faktúry, oveľa viac údajov tu, už sme v polovici roka a mám už 423 záznamov. Dobre, tak ako zmapujete týchto 55 na týchto 423? Možno bude ťažké urobiť s VLOOKUP, najskôr by ste mali zhrnúť, ale našťastie to v Exceli 2013 umožňuje dátový model, ktorý je naozaj, naozaj ľahký. To, čo potrebujeme, aby sme umožnili komunikácii tohto veľkého mohutného stola s týmto malým stolom, sú sprostredkovatelia, ja ich nazývam stolári.Maličké malé tabuľky, Produkt, Región a Kalendár, pripojíme sa k rozpočtu k týmto trom tabuľkám, pripojíme sa k skutočným k týmto trom tabuľkám a zázračne bude fungovať kontingenčná tabuľka. Dobre, tak to robíme takto.

Najprv potrebujem vytvoriť stolárov, takže vezmem toto pole produktu zo stĺpca A a skopírujem ho do stĺpca F a potom položky Údaje, Odstrániť duplikáty, kliknite na tlačidlo OK a zostane nám malá tabuľka, 1 nadpis 3 riadky. To isté platí pre Región, vezmite regióny, Ctrl + C, prejdite do stĺpca G, Prilepiť, Odstrániť duplikáty, kliknite na OK, 3 riadky 1 hlavička, v poriadku. Pokiaľ ide o dátumy, dátumy nie sú rovnaké, jedná sa o dátumy ukončenia mesiaca, skutočne sa ukladajú ako dátumy ukončenia mesiaca a jedná sa o pracovné dni. Vezmem oba zoznamy, Ctrl + C druhý zoznam a prilepím ich sem, Ctrl + V, potom vezmem kratší zoznam, skopírujem ho a prilepím dole, v poriadku. A je skutočne nepríjemné, že aj keď sú uložené ako dátumy, zobrazujú sa ako mesiace a nástroj Odstrániť duplikáty ich nebude vidieť rovnako.Takže predtým, ako použijem Odstrániť duplikáty, musím to zmeniť na krátky dátum. Vyberte tieto údaje, Údaje, Odstrániť duplikáty, kliknite na OK a potom ich trochu roztriedte, aby fungovali.

Dobre, teraz sa nechcem hlásiť podľa denného dátumu, takže sem pridám stĺpec, vyhľadávací stĺpec, ktorý hovorí „Mesiac“, a bude sa rovnať EOMONTH tomuto dátumu, „0“, čo nás dostane von koniec mesiaca. Naformátuje to ako krátky dátum a v poriadku to skopíruje. Teraz musíme z každého urobiť tabuľku Ctrl + T, takže odtiaľto Ctrl + T, môj stôl má hlavičky, nádherné. Tí malí, neuvedomuje si, že to sú hlavičky tam hore, takže musíme skontrolovať, či je to začiarknuté a Ctrl + T, v poriadku, a oni tieto tabuľky nazývajú Table1, Table2, Table3, skutočne nudné mená, však? Takže ich premenujem a budem to volať BudTable, ProdTable, RegTable, môj CalTable a potom ActTable.

Začíname od úplne prvej tabuľky a mimochodom, že dnes nebudeme používať PowerPivot, urobíme to všetko s dátovým modelom. Takže v programe Excel 2013 alebo novšom máte túto kontingenčnú tabuľku Vložiť, začiarkneme políčko „Pridať tieto údaje do dátového modelu“, klikneme na tlačidlo OK a náš zoznam polí dostaneme pomocou magického tlačidla Všetko, ktoré umožňuje ja si vyberiem zo všetkých piatich tabuliek v zošite, Skutočná, Rozpočet, Kalendár, Produkt, Región. Dobre, takže čísla budú pochádzať z tabuľky rozpočtu, vložím tam rozpočet a zo skutočnej tabuľky do tabuľky skutočný, ale potom tu je vec pre zvyšok kontingenčnej tabuľky. Akékoľvek ďalšie textové polia, ktoré vložíme do oblasti riadkov alebo stĺpov, alebo ako priečniky, musia pochádzať od stolárov, musia pochádzať z tých tabuliek medzi tabuľkami.

Dobre, takže z tabuľky Kalendár vezmeme to pole Mesiac a umiestnime ho cez hornú časť, teraz budeme ignorovať ďalšie vzťahy. Budem vytvárať vzťahy, ale chcem ich vytvárať všetky naraz. A tabuľku Regióny položte regióny bokom. Mohol by som dať produkty bokom, ale v skutočnosti budem používať tabuľku Produktov ako krájač, takže Analyzovať, Vložiť krájač, opäť musíte ísť na Všetko, ak ste ešte tabuľku Produktov nepoužívali. Prejdite teda na Všetky a uvidíte, že Produkt je k dispozícii na vytváranie ako krájača z podobných produktov. Dobre, v tejto chvíli sme nevytvorili vzťahy, takže všetky tieto čísla sú nesprávne. A vzťahy, ktoré musíme vytvoriť, musíme vytvoriť 3 tabuľky z tejto malej tabuľky rozpočtu, jednu pre produkty, jednu pre regióny, jednu pre kalendár,to sú 3 vzťahy. A potom musíme v Kalendári vytvoriť vzťahy z tabuľky Skutočné do oblasti Produkt, teda celkom 6 tabuliek. A áno, určite by to bolo jednoduchšie, keby sme mali PowerPivot, ale nie alebo predpokladajme, že nie.

A tak použijem staromódny spôsob, dialóg Vytvoriť tu, kde máme vľavo tabuľku Rozpočet a použijeme pole Region a spojíme ho s tabuľkou Region, poľom Region. . Dobre, vytvorí sa 1/6. Vyberiem možnosť Vytvoriť, opäť z tabuľky rozpočtu prejdeme k produktu a potom ho prepojím s tabuľkou produktu, s produktom, kliknite na tlačidlo OK. V tabuľke Rozpočet v poli Dátum prejdeme k tabuľke Kalendár a v poli Osud kliknite na tlačidlo OK, sme v polovici cesty, v poriadku. Z tabuľky Skutočnosti ideme na oblasť, do tabuľky oblastí, klikneme na OK, z tabuľky skutočností na produkt a z tabuľky skutočností do kalendára. Vlastne budem brať hodnoty a urobím to bokom, dobre. Dizajn, Rozloženie prehľadu, Zobraziť v tabuľkovej podobe, aby som získal preferovaný pohľad, Opakovať všetky štítky položiek, v poriadku,toto je úplne úžasné! Teraz máme túto malú tabuľku, 50 záznamov v tejto stovke záznamov a vďaka dátovému modelu sme vytvorili jednu kontingenčnú tabuľku. Pre každý, kde vidíme rozpočet, vidíme výnosy, sú rozdelené podľa regiónov, sú rozdelené podľa mesiacov a dajú sa posúvať podľa produktov.

Tento koncept mi ​​teraz prišiel od Roba Collieho, ktorý vedie Power Pivot Pro, a Rob tam vytvoril veľa kníh, jeho najnovšou je „Power Pivot a Power BI“. Myslím, že tento sa v skutočnosti nachádzal v knihe „Power Pivot Alchemy“, to je ten, ktorý som videl, a povedal som: „No, aj keď nemám milióny riadkov, ktoré by som mohol prostredníctvom Power Pivot nahlásiť urobili v mojom živote OBROVSKÉ rozdiely, pretože mám dva súbory údajov nezodpovedajúcich veľkostí a musím z nich hlásiť. “ Tento príklad a mnoho ďalších je v tejto knihe, nakoniec dostanem celý knižný podcast, ktorý vyzerá, že to bude trvať dva a pol mesiaca. Ale dnes môžete získať celú knihu, ísť tam, kúpiť si knihu, 10 dolárov za elektronickú knihu, 25 dolárov za tlačenú knihu a všetky tieto tipy môžete mať naraz.

Dobre, skutočne dlhá epizóda tu: máme malý rozpočet zhora nadol a zdola nahor. Skutočné, majú rozdielne veľkosti, ale pomocou dátového modelu v programe Excel 2013 … A mimochodom, ak ste v roku 2010, môžete teoreticky to urobte tak, že získate doplnok Power Pivot a všetky tieto kroky vykonáte späť v roku 2010. Vytvorte oba súbory údajov do tabuľky Ctrl + T a potom do svojich tabuliek pripojte čokoľvek, o čom chcete informovať, v štítok riadku alebo štítku stĺpca alebo priečniky, takže tieto hodnoty skopírujte a odstráňte duplikáty dátumov. Vlastne som vzal hodnoty z oboch tabuliek, pretože v každej boli nejaké jedinečné hodnoty, a potom som pomocou EOMONTHU dostal von, aby tieto stolárske tabuľky boli riadené. Je to voliteľné, ale pomenoval som všetkých 5 tabuliek, pretože je jednoduchšie, keď nastavujete tieto vzťahy, než aby ste sa volali Table1,Tabuľka2, tabuľka3.

Začnite teda od tabuľky Rozpočet, Vložiť, Kontingenčná tabuľka, začiarknite políčko Dátový model a potom vytvorte kontingenčnú tabuľku pomocou položiek Rozpočet a Skutočná. Všetko ostatné pochádza z stolárskych tabuliek, takže oblasť a mesiac v oblasti riadkov a stĺpcov, krájače pochádzali z tabuľky produktov. A potom sme museli vytvoriť 3 vzťahy z rozpočtu pre stolárov, 3 vzťahy zo skutočného na stolárov a máme úžasnú kontingenčnú tabuľku. Teraz zajtra sa pozrieme na použitie karty Power Pivot a na vytvorenie ďalších výpočtov. Takže toto všetko je možné, je to vtedy, keď chceme vložiť vypočítané pole, vtedy je potrebné zaplatiť ďalšie 2 doláre mesačne, aby ste získali verziu Office 365 Pro Plus.

No ahoj, vďaka Robovi Collieovi z Power Pivot Pro za tento tip a vďaka ti, že si sa zastavil, uvidíme sa nabudúce pri ďalšom netcaste od!

Stiahnuť súbor

Stiahnutie vzorového súboru nájdete tu: Podcast2016.xlsx

Zaujímavé články...