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 skutočnými údajmi 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: 1 riadok mesačne na región a produkt.
Súbor faktúr je na úrovni podrobností: tento rok zatiaľ 422 riadkov.
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.
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 údajové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.
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!
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.
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.
