Práve ste vytvorili kontingenčnú tabuľku v programe Excel. Kliknete mimo kontingenčnej tabuľky. Vytvoríte vzorec programu Excel. Tento vzorec skopírujete do všetkých riadkov v kontingenčnej tabuľke. Výpočet uvádza nesprávnu odpoveď pre všetky kontingenčnej tabuľky okrem prvého riadku. Práve vás bodla „funkcia“ Generovať GetPivotData. Pozrime sa, čo to je a ako tomu zabrániť.
Stačí zabrániť problému GetPivotData - rýchla metóda
Najrýchlejším spôsobom, ako zabrániť programu Excel vo generovaní GetPivotData, je zadanie vzorca bez použitia myši alebo klávesov so šípkami. Ak jednoducho napíšete =E5/D5
, Excel vytvorí „normálny“ relatívny vzorec, ktorý je možné skopírovať do všetkých riadkov susediacich s kontingenčnou tabuľkou.
Predchádzajte problémom GetPivotData - permanentná metóda
Existuje skryté nastavenie, ktoré zabráni programu Excel vo generovaní GetPivotData. V programe Excel 2003 je skrytejší ako v programe Excel 2007.
V programe Excel 2007 postupujte takto:
- Vytvorte kontingenčnú tabuľku v programe Excel 2007
- Skontrolujte, či je aktívna bunka vo vnútri kontingenčnej tabuľky
- Pozrite sa na ľavú stranu karty Možnosti kontingenčnej tabuľky na páse s nástrojmi. K dispozícii je tlačidlo Možnosti. Neklikajte na tlačidlo Možnosti! Na pravej strane tlačidla Možnosti je šípka rozbaľovacej ponuky. Kliknite na rozbaľovaciu šípku. Zrušte začiarknutie políčka Generovať údaje GetPivotData.
- Teraz môžete zadávať vzorce pomocou myši, klávesov so šípkami alebo písaním.
V programe Excel 2003 a starších verziách postupujte takto:
- Vyberte Nástroje, Prispôsobiť
- V dialógovom okne Prispôsobiť sú tri karty. Vyberte kartu Príkazy v strede.
- V ľavom zozname vyberte siedmu položku Údaje
- V zozname vpravo sa posuňte takmer úplne dole. 8. ikona na konci zoznamu je Generovať GetPivotData. Presuňte túto ikonu zo zoznamu a umiestnite ju do stredu existujúceho panela nástrojov.
- Kliknutím na tlačidlo Zavrieť zatvoríte dialógové okno.
- Kliknite na ikonu, ktorú ste práve pridali na panel s nástrojmi Excel. Táto funkcia sa vypne. Teraz môžete zadávať vzorce pomocou myši bez generovania funkcií GetPivotData.
Tu je bonusový tip: Kedysi som touto funkciou pohŕdal a jednoducho som chcel, aby bola neustále vypnutá. Ikonu som pridal na svoj panel s nástrojmi, ikonu som vypol a potom odstránil z panela s nástrojmi. Teraz … trochu som sa uvoľnil. Vidím, že ikona má občas nejaké dobré využitie. Takže som zostavil kontingenčnú tabuľku v programe Excel 2003 a ubezpečil sa, že ukazovateľ bunky bol v kontingenčnej tabuľke. Potom som pomocou dialógového okna Prispôsobiť pretiahol ikonu Generovať GetPivotData na svoj panel s nástrojmi kontingenčnej tabuľky. Teraz - keď som v kontingenčnej tabuľke, môžem si túto funkciu zapnúť alebo vypnúť.
Prečo to spoločnosť Microsoft urobila? Existuje nejaké dobré využitie pre GetPivotData?
Čo má robiť GetPivotData? Microsoftu sa táto funkcia zjavne páči, pretože ju vynútili voči miliónom nič netušiacich ľudí, ktorí používajú kontingenčné tabuľky.
GetPivotData vráti každú viditeľnú bunku z kontingenčnej tabuľky. Namiesto ukazovania na adresu bunky však musíte hodnotu opísať ako priesečník rôznych hodnôt poľa.
Keď sa zmení kontingenčná tabuľka, funkcia GetPivotData bude naďalej vracať rovnaké logické údaje z kontingenčnej tabuľky za predpokladu, že údaje sú v kontingenčnej tabuľke stále viditeľné. To môže byť dôležité, ak prechádzate z mesiaca na mesiac v poli stránky kontingenčnej tabuľky a chcete vždy vrátiť predaj konkrétnemu zákazníkovi. Pretože sa zoznam zákazníkov každý mesiac mení, mení sa aj pozícia zákazníka. Použitím =GETPIVOTDATA
môžete zabezpečiť, že z kontingenčnej tabuľky vrátite správnu hodnotu.
=GETPIVOTDATA
vždy začína dvoma konkrétnymi argumentmi. Potom má voliteľne ďalšie dvojice argumentov.
Tu sú dva požadované argumenty:
- Názov údajového poľa. Môže to byť „Suma z výnosov“ alebo jednoducho „Príjmy“.
- Akákoľvek bunka, ktorá je umiestnená „vnútri“ kontingenčnej tabuľky. Vedeli ste, že vaša kontingenčná tabuľka má názov? Pravdepodobne ste to nevedeli, pretože nemôžete nájsť meno v rozhraní programu Excel. Museli by ste ísť do VBA, aby ste sa dozvedeli názov kontingenčnej tabuľky. Takže - pre Microsoft bolo jednoduchšie umožniť vám identifikovať kontingenčnú tabuľku nasmerovaním na ľubovoľnú bunku v kontingenčnej tabuľke. Aj keď si môžete vybrať ľubovoľnú bunku, je najbezpečnejšie zvoliť bunku v ľavom hornom rohu. Existuje šanca, že sa vaša kontingenčná tabuľka môže zmenšiť pre konkrétnu kombináciu polí stránky. V takom prípade sa pomocou bunky v ľavom hornom rohu ubezpečte, že stále smerujete do kontingenčnej tabuľky.
Potom budete pokračovať vo funkcii s ďalšími pármi argumentov. Každá dvojica obsahuje názov poľa a hodnotu.
GetPivotData môže vracať iba hodnoty viditeľné v kontingenčnej tabuľke
Keď uvidíte, ako funguje niekoľko funkcií GetPivotData, môžete si myslieť, že sú výkonnejšie, ako v skutočnosti sú. Funkcia bude v skutočnosti fungovať, iba ak je konkrétna hodnota viditeľná v kontingenčnej tabuľke. Zvážte obrázok nižšie.
- V bunke A2 vráti GETPIVOTDATA tržby spoločnosti ABC z januára 2004 v centrálnej oblasti. Toto zachytáva 80003 z bunky G19.
- Vzorec v A6 však zlyhá. Žiada predaj spoločnosti ABC vo všetkých regiónoch. Kontingenčná tabuľka zobrazuje celkový ABC pre stred, celkový ABC pre východ, celkový ABC pre západ, ale nikdy neukazuje celkový ABC pre všetky regióny, takže výsledkom je #REF! chyba.
- Ak by ste otočili pole regiónu až k oblasti stránky, vzorec v A6 by začal fungovať, ale vzorec v A2 a A4 by začal vracať #REF !. Ak ste v rozbaľovacej ponuke Region vybrali Central, potom by fungovali všetky štyri vzorce.
- Vypnutie celkových súčtov v dialógovom okne Možnosti kontingenčnej tabuľky BUDE spôsobovať, že veľa funkcií GetPivotData začne vracať #REF! chyby.
Tento tip poskytuje Pomocník programu Excel pre program GetPivotData
Na zostavenie týchto funkcií je najjednoduchšie zostaviť vzorec pomocou myši. Do bunky mimo kontingenčnej tabuľky zadajte znak rovnosti a potom pomocou myši kliknite na bunku v kontingenčnej tabuľke. Excel bude spracovávať podrobnosti vytvárania odkazov. Na obrázku vyššie sú mesiace a roky zoskupené polia vytvorené z poľa dátumu. Pomocník programu Excel nezdokumentuje, že pre mesiac by malo byť pole mesiac zadané ako 1, ale toto sa dozviete z pozorovania výsledkov, ktoré umožňujú programu Excel zostaviť GetPivotData. Samozrejme … aby ste mohli používať túto funkciu, musíte znovu povoliť funkciu Generovať GetPivotData, ktorú ste predtým mohli zakázať.