Nenávidíte funkci Excel GETPIVOTDATA? Proč se to objevuje? Jak tomu můžete zabránit? Existuje dobré využití pro GETPIVOTDATA?
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 jako = D5 / C5-1, aniž byste pomocí myši nebo kláves se šipkami ukazovali 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 typ 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 do 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 malý 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ňka, kterou je třeba vyplnit, je leden, Actuals for 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 lednové údaje 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.
Zde je vzorec po jeho úpravě. Pryč jsou „Baybrook“, „Jan“ a „Skutečné“. Místo toho ukazujete na $ D6, E $ 3, 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áš roč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í.
Změňte buňku P1
Musíte připustit, že pomocí prostého přehledu, který vytahuje čísla z kontingenční tabulky, získáte 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.
Sledovat video
- GetPivotData se stane, když vzorec ukazuje uvnitř kontingenční tabulky
- I když je počáteční vzorec správný, vzorec nelze kopírovat
- Většina lidí nenávidí getpivotdata a chce tomu zabránit
- Metoda 1: Vytvoření vzorce bez použití myši nebo kláves se šipkami
- Metoda 2: Pomocí rozbalovací nabídky vedle možností trvale vypněte GetPivotData
- Existuje ale použití pro GetPivotData
- Váš manažer chce přehled se skutečnými údaji za poslední měsíce a rozpočtem pro budoucí
- V normálním pracovním postupu byste vytvořili kontingenční tabulku, převedli na hodnoty a odstranili sloupce
- Odebrání mezisoučtů, aby se zabránilo plánování aktuálního ledna + pomocí nastavení pole
- Místo toho vytvořte kontingenční tabulku s údaji „příliš mnoho“
- Použijte pěkně naformátovaný list sestavy
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Z první datové buňky v listu vytvořte vzorec pomocí myši
- Umožněte získání GetPivotData
- Prozkoumejte syntaxi GetPivotData (pole, které se má vrátit, umístění pivotů, páry)
- Změňte pevně nastavenou hodnotu tak, aby ukazovala na buňku
- Stisknutím klávesy F4 třikrát uzamknete pouze sloupec
- Stisknutím klávesy F4 dvakrát uzamknete pouze řádek
- Vložte speciální vzorce
- Pracovní postup příští měsíc: Přidejte data, obnovte kontingenční tabulku, změňte datum
- Je velmi opatrné dávat pozor na nové obchody
Přepis videa
Naučte se Excel z podcastu, epizoda 2013 - GetPivotData nemusí být zcela zlá!
Budu podcastovat celou tuto knihu, kliknutím na toto „i“ v pravém horním rohu se přihlásíte k odběru.
Dobře, v epizodě 1998 jsem krátce hovořil o tomto problému GetPivotData. Pokud spočítáme% rozptyl a jsme mimo kontingenční tabulku směřující dovnitř, používám myš nebo klávesu se šipkami, takže 2019 / 2018-1. Tato odpověď, kterou sem dostaneme, je správná pro leden, ale když to dvakrát zkopírujeme, vzorec se nekopíruje, dostaneme lednovou odpověď úplně dolů. A když se na to podíváme, dostáváme GetPivotData, nezadal jsem GetPivotData, jen jsem ukázal na tyto buňky a toto se začalo dít v aplikaci Excel 2002 bez jakéhokoli varování. A v tom okamžiku jsem řekl, že způsob, jak se tomu vyhnout, je napsat vzorec C5 / B5-1 a dostanete vzorec, který můžete zkopírovat. Nebo pokud nenávidíte GetPivotData, pokud je „úplně zlý“, přejděte na kartu Analyzovat,Mimochodem otevřete tlačítko Možnosti. Vraťte se do kontingenční tabulky, přejděte na kartu Analyzovat, otevřete rozevírací seznam vedle Možnosti, zrušte zaškrtnutí tohoto políčka, je to globální nastavení. Jakmile ji vypnete, bude navždy vypnutá, dobře.
Otázky, které dostávám, jsou většinou „Jak vypnu GetPivotData?“ ale jednou za čas dostanu někoho, kdo miluje GetPivotData. A já jsem byl na obědě s Robem Collie, když byl ještě v Microsoftu, a on řekl: „No, naši interní zákazníci milují GetPivotData.“ Řekl jsem: „Co? Ne, každý nenávidí GetPivotData! “ Rob říká: „Máte pravdu, mimo Microsoft absolutně nenávidí GetPivotData.“ Mluvím o účetních ve společnosti Microsoft a později jsem potkal jednoho, který nyní pracuje pro tým Excel, Carlos a Carlos byl jedním z účetních, kteří používají tuto metodu.
Dobře, takže tady je to, co musíme udělat. Máme tu naši zprávu, soubor dat, že pro každý měsíc máme plán pro každý obchod, a pak dole shromažďujeme skutečnosti. Dobře, takže máme skutečné údaje za leden až prosinec, ale máme skutečné pouze za několik měsíců, měsíce, které uplynuly. A to, co po nás chce náš manažer, je sestavit zprávu s obchody dole na levé straně, samozřejmě jen Texas obchody ztěžují život. A poté, co jdeme napříč, máme měsíce, a pokud máme skutečný pro daný měsíc, ukážeme skutečný, tedy skutečný leden, skutečný únor, skutečný březen, skutečný duben. Ale pak na měsíce, kdy nemáme skutečná data, přepneme a ukážeme rozpočet, takže rozpočet do prosince, a pak celkem celkem, dobře. Když se pokusíte vytvořit tuto kontingenční tabulku, jo,nefunguje to.
Takže vložte kontingenční tabulku, nový list, dejte Store doleva, strana, která je krásná, dejte měsíce nahoře, dejte Type nahoře, dejte sem prodeje, dobře. Takže tady máme to, s čím musíme začít pracovat, takže máme skutečný leden, lednový plán a pak úplně zbytečný lednový skutečný plus plán. Nikdo to nikdy nepoužije, ale můžu se zbavit těchto šedých sloupců, to je dost snadné, některé zde do této buňky, přejít na Nastavení pole a změnit Mezisoučty na Žádné. Ale neexistuje pro mě absolutně žádný způsob, jak odstranit lednový plán, který také neodstraní plán z dubna, května, června, července, dobře, neexistuje způsob, jak se toho zbavit. V tomto okamžiku tedy každý měsíc zasekávám výběr celé kontingenční tabulky, přechod na Kopírovat a poté Vložit, Vložit hodnoty. Už to není kontingenční tabulka,a pak začnu ručně mazat sloupce, které se v sestavě neobjeví.
Dobře, to je běžná metoda, ale účetní v Microsoftu přidali v lednu další krok, trvá to 15 minut a tento krok umožňuje této kontingenční tabulce žít věčně, že? Říkám tomu nejošklivější kontingenční tabulka na světě a účetní v Microsoftu akceptují, že se jedná o nejošklivější kontingenční tabulku na světě, ale tuto zprávu nikdo kromě nich neuvidí. Co dělají, je, že sem přijdou na nový list a vytvoří zprávu, kterou chce jejich manažer. Dobře, takže tady jsou obchody po levé straně, dokonce jsem to seskupil do Houstonu, Dallasu a dalších, je to pěkně naformátovaná zpráva. Zvýraznil jsem součty, uvidíte, že když dostaneme nějaká čísla, v prvním řádku je měna, ale ne tyto následující řádky, prázdné řádky. Ooh, prázdné řádky v kontingenční tabulce.A jeden malý kousek logiky tady, kde mohu dát datum průchodu do buňky P1, a pak zde mám vzorec, který analyzuje, že KDYŽ měsíc měsíce průchodu je> tento sloupec, a pak dát slovo Skutečný, jinak řekněte slovo Plan, dobře. Takže vše, co musím udělat, je změnit to do data a potom slovo Skutečné otočit podle plánu, dobře.
Takže, tady je to, co děláme, dovolíme si být GetPivotData'd, že? Nejsem si jistý, že se jedná o sloveso, ale umožníme Microsoftu získat GetPivotData. Takže začnu vytvářet vzorec s =, chytím myš a půjdu hledat lednový skutečný Baybrook! Vracím se tedy k nejošklivější Pivotní tabulce na světě, najdu Baybrook, najdu leden, najdu aktuální a kliknu na Enter a nechám to udělat se mnou, dobře, jdeme na to, nyní máme vzorec GetPivotData. Vzpomínám si na den, kdy jsem to udělal, bylo to jako, když mi Rob vysvětlil, co dělají, a já jsem se vrátil a zkusil to. Teď jsem se najednou celý život zbavoval GetPivotData, nikdy jsem GetPivotData neobjal. Takže to, co to je, je první položka, kterou tam hledáme “sa pole s názvem Prodej, to je místo, kde začíná kontingenční tabulka, a to může být libovolná buňka v kontingenční tabulce, používají horní levou ruku.
Dobře, toto je název pole „Obchod“, a pak napevno napíšou „Baybrook“, toto je název pole „Měsíc“, napevno napíšou „Leden“, toto je název pole „Typ“ a oni ' napevno zakódováno „Aktuální“. Proto to nemůžete kopírovat, protože hodnoty napevno zakódovali. Ale účetní společnosti Microsoft, Carlos a jeho spolupracovníci si uvědomují „Whoa, počkej chvíli, tady máme slovo Baybrook, máme zde leden, máme zde Aktuální. Musíme pouze změnit tento vzorec tak, aby ukazoval na skutečné buňky ve zprávě, místo aby byly napevno zakódovány. “ Dobře, takže to nazývají parametrizací GetPivotData.
Odstraňte slovo Baybrook, pojďte sem a klikněte na buňku D6. Teď to potřebuji uzamknout na sloupec, dobře, takže třikrát stisknu klávesu F4, dostanu jeden $ před D, dobře. Pro měsíc leden odstraním napevno leden, kliknu na buňku E3, dvakrát stisknu F4 a uzamknu ji na řádek, E $ 3. Napište Skutečné, odstraňte slovo Skutečné, klikněte na E4, znovu dvakrát na F4, dobře, a dostanu vzorec, který nyní tahá tato data zpět. Zkopíruji to a pak vložím speciální, zvolím formáty, alt = "" ESF, viz F je tam podtrženo, ESF Enter, a teď, když jsem to udělal, zopakuji to s F4, F4 je redo a F4. Dobře, takže teď máme pěkně vypadající zprávu, která má mezery, má formátování, v každé sekci má jedno podtržení účetnictví,úplně dole má dvojí účetní podtržení.
Správně, tyto věci nikdy nedostanete v kontingenční tabulce, to je nemožné, ale tato zpráva je odvozena z kontingenční tabulky. Takže pak to, co děláme, když dostaneme květnové skutečnosti, vraťte se sem, vložte je, obnovte nejošklivější kontingenční tabulku na světě a pak zde ve zprávě jednoduše změňte datum průchodu z 4/30 na 5/31. A to, co dělá, je to, že způsobí, že se tento vzorec přepne ze slova Plan na Actual, které jde a vytahuje ze zprávy, namísto plánu, skutečné údaje. Tady je věc, že - to je skvělé, že? Vidím, kde bych to hodně udělal, kdybych stále, víte, pracoval v účetnictví.
Věc, na kterou musíte být opravdu opatrní, je, že když postaví nový obchod, musíte to vědět přidat ručně, správně, data se budou zobrazovat v kontingenční tabulce, ale přidali byste je ručně. Nyní je tento podmnožinou všech obchodů, pokud by hlásil všechny obchody, pravděpodobně bych tady mimo rozsah tisku měl něco, co vytáhlo z kontingenční tabulky celkový součet. A pak bych věděl, že pokud tento součet neodpovídá celkovému součtu z kontingenční tabulky, že něco není v pořádku, a mít dole funkci IF. Říká: „Hej, víte, nová data, která byla přidána, buďte velmi opatrní. “ Mají nějaký druh mechanismu, který zjišťuje, že jsou tam nová data. Ale chápu, je to skvělé použití. Ačkoli nás GetPivotData většinou poblázní, ve skutečnosti to může být užitečné. V pořádku,takže to je tip č. 21 ze 40 v knize, kupte si knihu hned teď, objednejte si online, klikněte na to „i“ v pravém horním rohu.
Dlouhá, dlouhá rekapitulace dnes, v pořádku: GetPivotData se stane, když vzorec ukazuje uvnitř kontingenční tabulky, vzorec mimo kontingenční tabulku ukazuje uvnitř. I když je počáteční vzorec správný, nebude se kopírovat. Většina lidí nenávidí GetPivotData a chce tomu zabránit. Takže můžete vytvořit vzorec bez myši nebo kláves se šipkami, jednoduše zadejte vzorec nebo trvale vypněte GetPivotData, ach, ale je to v pořádku. Musíme tedy vytvořit zprávu s aktuálními údaji za minulý měsíc, rozpočtem pro budoucnost. Normální pracovní postup, vytvoření kontingenční tabulky, převod na hodnoty, odstranění sloupců. Existuje způsob, jak odstranit mezisoučty pomocí nastavení pole a zbavit se tak skutečného plánu plus plus v lednu. Místo toho vytvoříme nejošklivější kontingenční tabulku na světě s příliš velkým množstvím dat.
Vytvořte pěkně naformátovaný, prostě obyčejný starý list sestavy s možná trochou logiky, abyste změnili slovo Skutečné na Plán. A pak z první buňky sestavy, na první místo, kde budou čísla v této sestavě, zadejte an =, přejděte na kontingenční tabulku a povolte, aby se stalo GetPivotData. Prozkoumáme syntaxi GetPivotData, takže je to pole, které se má vrátit, prodej, kde žije kontingenční tabulka, a pak dvojice kritérií, název pole a hodnota. Chystáme se odstranit pevně zakódovanou hodnotu a ukázat na buňku, stisknutím klávesy F4 třikrát uzamknete pouze sloupec, stisknutím klávesy F4 dvakrát uzamknete pouze řádek, zkopírujeme tento vzorec, vložit speciální vzorce Hodil jsem tam další tip, že F4 je redo, takže jsem musel jít do dialogu Paste Special pouze jednou a poté pro další Paste Special Formulas stačí použít F4. Příští měsíc přidejte data,obnovte kontingenční tabulku, změňte datum průchodu. Ujistěte se, že nevytvořili žádné nové obchody, víte, mají nějaký mechanismus, ať už manuální, nebo kontrolní vzorec, podívejte se na to. Díky iTrainerMX na Twitteru, který navrhl GetPivotData, také Carlos a Rob z Microsoftu, Rob nyní z Power Pivot Pro. Carlos za to, že to použil, a Rob, když mi řekl, že to Carlos používá, jsem se setkal s Carlosem později a on potvrdil, že ano, byl jedním z účetních, kteří to po celou dobu používali v Microsoftu, dobře, tady to máš.a Robovi za to, že mi řekl, že to Carlos používá, jsem se s Carlosem setkal později a on potvrdil, že ano, byl jedním z účetních, kteří to po celou dobu používali v Microsoftu, dobře, tady to máš.a Robovi za to, že mi řekl, že to Carlos používá, jsem se s Carlosem setkal později a on potvrdil, že ano, byl jedním z účetních, kteří to po celou dobu používali v Microsoftu, dobře, tady to máš.
No ahoj, chci vám poděkovat, že jste se zastavili, uvidíme se příště na dalším netcastu od!
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2013.xlsx
