Většina lidí se poprvé setká s GETPIVOTDATA, když se pokusí vytvořit vzorec mimo kontingenční tabulku, která používá čísla v kontingenční tabulce. Například toto procento odchylky se nebude kopírovat dolů do ostatních měsíců kvůli tomu, že aplikace Excel vkládá funkce GETPIVOTDATA.
Excel vloží GETPIVOTDATA pokaždé, když pomocí myši nebo kláves se šipkami ukážete na buňku uvnitř kontingenční tabulky při vytváření vzorce mimo kontingenční tabulku.
Mimochodem, pokud nechcete, aby se objevila funkce GETPIVOTDATA, jednoduše zadejte vzorec, například =D5/C5-1bez použití myši nebo kláves se šipkami k ukazování na buňky. Tento vzorec kopíruje bez problémů.
Zde je sada dat, která obsahuje jedno číslo plánu za měsíc na obchod. Existují také skutečné tržby za měsíc na obchod za měsíce, které jsou kompletní. Vaším cílem je sestavit zprávu, která zobrazuje skutečné údaje za dokončené měsíce a plán pro budoucí měsíce.
Vytvořte kontingenční tabulku pomocí funkce Store in Rows. Vložte měsíc a zadejte do sloupců. Získáte přehled zobrazený níže s lednovým skutečným, lednovým plánem a zcela nesmyslným lednovým skutečným + plánem.
Pokud vyberete buňku měsíce a přejdete na Nastavení pole, můžete změnit Mezisoučty na Žádné.
Tím se odstraní zbytečný plán Aktuální +. Stále se ale musíte zbavit sloupců plánu pro leden až duben. Uvnitř kontingenční tabulky neexistuje dobrý způsob.
Váš měsíční pracovní tok se tedy stává:
- Přidejte do datové sady skutečné údaje pro nový měsíc.
- Vytvořte nový kontingenční stůl od nuly.
- Zkopírujte kontingenční tabulku a vložte ji jako hodnoty, takže již nebude kontingenční tabulkou.
- Odstraňte sloupce, které nepotřebujete.
Existuje lepší způsob, jak jít. Následující velmi komprimovaný obrázek ukazuje nový list aplikace Excel přidaný do sešitu. To vše je jen přímý Excel, žádné kontingenční tabulky. Jediným kouskem magie je funkce IF v řádku 4, která přepíná ze skutečného na plán na základě data v buňce P1.
Úplně první buňkou, kterou je třeba vyplnit, je aktuální leden pro Baybrook. Klikněte do této buňky a zadejte znaménko rovná se.
Pomocí myši přejděte zpět na kontingenční tabulku. Najděte buňku pro aktuální leden pro Baybrook. Klikněte na tuto buňku a stiskněte klávesu Enter. Jako obvykle Excel vytváří jednu z těch nepříjemných funkcí GETPIVOTDATA, které nelze kopírovat.
Ale dnes pojďme studovat syntaxi GETPIVOTDATA.
První argument níže je číselné pole „Prodej“. Druhým argumentem je buňka, kde se nachází kontingenční tabulka. Zbývající dvojice argumentů jsou název pole a hodnota. Vidíte, co udělal automaticky generovaný vzorec? Je pevně zakódován jako název obchodu „Baybrook“. Proto nemůžete kopírovat tyto automaticky generované vzorce GETPIVOTDATA. Ve skutečnosti pevně zakódují jména do vzorců. I když tyto vzorce nemůžete kopírovat, můžete je upravit. V takovém případě by bylo lepší, kdyby jste vzorec upravili tak, aby ukazoval na buňku $ D6.
Následující obrázek ukazuje vzorec po jeho úpravě. Pryč jsou „Baybrook“, „Jan“ a „Skutečné“. Místo toho ukazujete na $ D6, E $ 3 a E $ 4.
Zkopírujte tento vzorec a pak vyberte Vložit jinak, Vzorce do všech ostatních číselných buněk.
Tady je váš měsíční pracovní postup:
- Vytvořte ošklivou kontingenční tabulku, kterou nikdo nikdy neuvidí.
- Nastavte list sestavy.
Každý měsíc musíte:
- Vložte nové údaje pod data.
- Obnovte ošklivou kontingenční tabulku.
-
Změňte buňku P1 na listu sestavy tak, aby odrážela nový měsíc. Všechna čísla se aktualizují.
Musíte uznat, že použití přehledu, který získává čísla z kontingenční tabulky, vám poskytne to nejlepší z obou světů. Sestavu můžete formátovat způsobem, že nemůžete formátovat kontingenční tabulku. Prázdné řádky jsou v pořádku. Symboly měny můžete mít v prvním a posledním řádku, ale ne mezi nimi. Pod celkovými součty získáte také dvojité podtržení.
Děkujeme @iTrainerMX za navržení této funkce.
