Väčšina ľudí sa s GETPIVOTDATA stretne prvýkrát, keď sa pokúša vytvoriť vzorec mimo kontingenčnej tabuľky, ktorý používa čísla v kontingenčnej tabuľke. Napríklad toto percento odchýlky sa nebude kopírovať do ďalších mesiacov kvôli tomu, že program Excel vkladá funkcie GETPIVOTDATA.
Excel vloží GETPIVOTDATA kedykoľvek, keď pomocou klávesov myši alebo šípok ukážete na bunku v kontingenčnej tabuľke a zároveň vytvoríte vzorec mimo kontingenčnej tabuľky.
Mimochodom, ak nechcete, aby sa zobrazovala funkcia GETPIVOTDATA, jednoducho zadajte vzorec, napríklad =D5/C5-1bez použitia myši alebo klávesov so šípkami na ukazovanie na bunky. Tento vzorec kopíruje bez problémov.
Tu je množina údajov, ktorá obsahuje jedno číslo plánu mesačne na obchod. Existujú aj skutočné mesačné tržby za obchod za celé mesiace. Vaším cieľom je zostaviť prehľad, ktorý zobrazuje skutočné údaje za dokončené mesiace a plán pre ďalšie mesiace.
Vytvorte kontingenčnú tabuľku pomocou funkcie Uložiť v riadkoch. Vložte mesiac a zadajte do stĺpcov. Získate prehľad uvedený nižšie s januárovým skutočným, januárovým plánom a úplne nezmyselným januárovým skutočným + plánom.
Ak vyberiete bunku za mesiac a prejdete na Nastavenia poľa, môžete zmeniť medzisúčty na Žiadne.
Týmto sa odstráni zbytočný plán Skutočné +. Stále sa však musíte zbaviť stĺpcov plánu na január až apríl. Vo vnútri kontingenčnej tabuľky neexistuje dobrý spôsob.
Váš mesačný pracovný tok sa teda stane:
- Pridajte do množiny údajov skutočné údaje za nový mesiac.
- Vytvorte nový kontingenčný stôl od nuly.
- Skopírujte kontingenčnú tabuľku a prilepte ju ako hodnoty, aby už viac nebola kontingenčnou tabuľkou.
- Odstráňte stĺpce, ktoré nepotrebujete.
Existuje lepšia cesta. Nasledujúci veľmi komprimovaný obrázok zobrazuje nový pracovný hárok programu Excel pridaný do zošita. Toto je všetko iba priamy program Excel, žiadne kontingenčné tabuľky. Jediným kúzlom mágie je funkcia IF v riadku 4, ktorá prepína zo skutočného na plán na základe dátumu v bunke P1.
Úplne prvou bunkou, ktorú je potrebné vyplniť, je aktuálny január pre Baybrook. Kliknite do tejto bunky a zadajte znak rovnosti.
Pomocou myši prejdite späť na kontingenčnú tabuľku. Nájdite bunku pre aktuálny január pre Baybrook. Kliknite na túto bunku a stlačte kláves Enter. Ako obvykle, Excel vytvára jednu z nepríjemných funkcií GETPIVOTDATA, ktoré nie je možné kopírovať.
Ale dnes si preštudujme syntax GETPIVOTDATA.
Prvý argument uvedený nižšie je číselné pole „Predaj“. Druhým argumentom je bunka, v ktorej sa nachádza kontingenčná tabuľka. Zvyšné páry argumentov sú názov poľa a hodnota. Vidíte, čo urobil automaticky generovaný vzorec? Je pevne zakódovaný ako názov obchodu „Baybrook“. Preto nemôžete kopírovať tieto automaticky generované vzorce GETPIVOTDATA. V skutočnosti pevne zakódujú mená do vzorcov. Aj keď tieto vzorce nemôžete kopírovať, môžete ich upraviť. V takom prípade by bolo lepšie, keby ste vzorec upravili tak, aby ukazoval na bunku $ D6.
Na nasledujúcom obrázku je uvedený vzorec po jeho úprave. Preč sú „Baybrook“, „Jan“ a „Skutočné“. Namiesto toho poukazujete na $ D6, E $ 3 a E $ 4.
Skopírujte tento vzorec a potom zvoľte Prilepiť špeciálne, Vzorce do všetkých ostatných číselných buniek.
Tu je váš mesačný pracovný postup:
- Vytvorte škaredú kontingenčnú tabuľku, ktorú nikto nikdy neuvidí.
- Nastavte pracovný hárok prehľadu.
Každý mesiac musíte:
- Prilepte nové údaje pod údaje.
- Obnovte škaredú kontingenčnú tabuľku.
-
Zmeňte bunku P1 na hárku správy tak, aby odrážala nový mesiac. Všetky čísla sa aktualizujú.
Musíte uznať, že použitie prehľadu, ktorý čerpá čísla z kontingenčnej tabuľky, vám poskytne to najlepšie z oboch svetov. Prehľad môžete naformátovať spôsobom, ktorý neumožňuje formátovanie kontingenčnej tabuľky. Prázdne riadky sú v poriadku. Symboly meny môžete mať v prvom a poslednom riadku, ale nie medzi nimi. Dvojité podčiarknutia získate aj pod celkovým súčtom.
Ďakujeme @iTrainerMX za navrhnutie tejto funkcie.
