GetPivotData v aplikaci Excel - Excel tipy

Právě jste vytvořili kontingenční tabulku v aplikaci Excel. Kliknete mimo kontingenční tabulku. Vytvoříte vzorec aplikace Excel. Tento vzorec zkopírujete do všech řádků v kontingenční tabulce. Výpočet uvádí nesprávnou odpověď pro všechny kontingenční tabulky kromě prvního řádku. Právě vás zasáhla „funkce“ Generovat GetPivotData. Pojďme se podívat, co to je a jak tomu zabránit.

Stačí zabránit problému GetPivotData - rychlá metoda

Nejrychlejším způsobem, jak zabránit aplikaci Excel ve generování GetPivotData, je zadat vzorec bez použití myši nebo kláves se šipkami. Pokud jednoduše zadáte =E5/D5, Excel vytvoří „normální“ relativní vzorec, který lze zkopírovat dolů do všech řádků sousedících s kontingenční tabulkou.

Zabraňte problému GetPivotData - permanentní metoda

Existuje skryté nastavení, které zastaví Excel v generování GetPivotData. V aplikaci Excel 2003 je skrytější než v aplikaci Excel 2007.

V aplikaci Excel 2007 postupujte takto:

• Vytvořte kontingenční tabulku v aplikaci Excel 2007
• Zkontrolujte, zda je aktivní buňka uvnitř kontingenční tabulky
• Podívejte se na levou stranu karty Možnosti kontingenční tabulky na pásu karet. K dispozici je tlačítko Možnosti. Neklikejte na tlačítko Možnosti! Na pravé straně tlačítka Možnosti je rozevírací šipka. Klikněte na rozevírací šipku. Zrušte zaškrtnutí políčka Generovat GetPivotData.
• Nyní můžete zadávat vzorce pomocí myši, kláves se šipkami nebo psaní.

V aplikaci Excel 2003 a dřívějších verzích postupujte takto:

• Zvolte Nástroje, Přizpůsobit
• V dialogu Přizpůsobit jsou tři karty. Vyberte kartu Příkazy uprostřed.
• V levém seznamu vyberte 7. položku Data
• V pravém seznamu přejděte téměř úplně dolů. 8. ikona na konci seznamu je Generovat GetPivotData. Přetáhněte tuto ikonu ze seznamu a umístěte ji do středu existujícího panelu nástrojů.
• Dialog zavřete kliknutím na tlačítko Zavřít.
• Klikněte na ikonu, kterou jste právě přidali na panel nástrojů aplikace Excel. Tím se funkce vypne. Nyní můžete zadávat vzorce pomocí myši bez generování funkcí GetPivotData.

Zde je bonusový tip: Tímto prvkem jsem opovrhoval a jednoduše jsem chtěl, aby byl pořád vypnutý. Přidal jsem ikonu na svůj panel nástrojů, ikonu jsem vypnul a poté ji z panelu nástrojů odstranil. Teď … trochu jsem se uvolnil. Vidím, že ikona má občas dobré využití. Takže jsem vytvořil kontingenční tabulku v aplikaci Excel 2003 a ujistil jsem se, že ukazatel buňky byl v kontingenční tabulce. Poté jsem pomocí dialogového okna Přizpůsobit přetáhl ikonu Generovat GetPivotData na můj panel nástrojů kontingenční tabulky. Nyní - když jsem v kontingenční tabulce, můžu si tuto funkci zapnout nebo vypnout.

Proč to Microsoft udělal? Existuje pro GetPivotData nějaké dobré využití?

Co má GetPivotData dělat? Microsoftu se tato funkce zjevně líbí, protože ji přinutila milionům nic netušících lidí, kteří používají kontingenční tabulky.

GetPivotData vrátí jakoukoli viditelnou buňku z kontingenční tabulky. Spíše než ukazovat na adresu buňky můžete popsat hodnotu jako průnik různých hodnot pole.

Jak se mění kontingenční tabulka, GetPivotData bude i nadále vracet stejná logická data z kontingenční tabulky za předpokladu, že jsou data v kontingenční tabulce stále viditelná. To může být důležité, pokud přecházíte z měsíce na měsíc v poli stránky kontingenční tabulky a chcete vždy vrátit prodej konkrétního zákazníka. Jak se seznam zákazníků mění každý měsíc, mění se pozice zákazníka. Použitím =GETPIVOTDATAmůžete zajistit, že vrátíte správnou hodnotu z kontingenční tabulky.

=GETPIVOTDATAvždy začíná dvěma konkrétními argumenty. Pak má volitelně další dvojice argumentů.

Zde jsou dva požadované argumenty:

1. Název datového pole. Může to být „Součet příjmů“ nebo jednoduše „Výnosy“.
2. Libovolná buňka, která je umístěna „uvnitř“ kontingenční tabulky. Věděli jste, že vaše kontingenční tabulka má název? Pravděpodobně jste to nevěděli, protože nemůžete zjistit název v rozhraní aplikace Excel. Budete muset jít do VBA, abyste se dozvěděli název kontingenční tabulky. Takže - pro Microsoft bylo snazší vám umožnit identifikovat kontingenční tabulku tím, že ukazuje na libovolnou buňku v kontingenční tabulce. I když si můžete vybrat libovolnou buňku, je nejbezpečnější vybrat buňku v levém horním rohu. Existuje šance, že by se vaše kontingenční tabulka mohla zmenšit pro konkrétní kombinaci polí stránky. V takovém případě se pomocí buňky v levém horním rohu ujistěte, že míříte dovnitř kontingenční tabulky.

Potom budete pokračovat ve funkci s dalšími dvojicemi argumentů. Každá dvojice obsahuje název pole a hodnotu.

GetPivotData může vracet pouze hodnoty viditelné v kontingenční tabulce

Jakmile uvidíte, že funguje několik funkcí GetPivotData, můžete si myslet, že jsou výkonnější, než ve skutečnosti jsou. Ve skutečnosti bude funkce fungovat, pouze pokud je konkrétní hodnota viditelná v kontingenční tabulce. Zvažte obrázek níže.

• V buňce A2 vrátí GETPIVOTDATA tržby společnosti ABC z ledna 2004 ve střední oblasti. Toto popadlo 80003 z buňky G19.
• Vzorec v A6 se však nezdaří. Žádá o prodej ABC ve všech regionech. Kontingenční tabulka zobrazuje celkový ABC pro střední, celkový ABC pro východ, celkový ABC pro západ, ale nikdy neukazuje celkový ABC pro všechny regiony, takže výsledkem je #REF! chyba.
• Pokud byste otočili pole oblasti až k oblasti stránky, vzorec v A6 by začal fungovat, ale vzorec v A2 a A4 by začal vracet #REF !. Pokud jste z rozevíracího seznamu Region vybrali Central, pak by fungovaly všechny čtyři vzorce.
• Vypnutí velkých součtů v dialogovém okně Možnosti kontingenční tabulky BUDE způsobovat, že mnoho funkcí GetPivotData začne vracet #REF! chyby.