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. Srovnání malého souboru rozpočtu s objemnými skutečnými daty 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: 1 řá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 vytvořit kontingenční tabulku z tabulky rozpočtu 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.
