Neznášate funkciu GETPIVOTDATA v Exceli? Prečo sa to javí? Ako tomu môžete zabrániť? Existuje dobré použitie pre GETPIVOTDATA?
Väčšina ľudí sa prvýkrát stretne s GETPIVOTDATA, keď sa pokúsia 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 ako = D5 / C5-1 bez použitia myši alebo klávesov so šípkami na smerovanie do buniek. 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 aplikácie Store in ROWS. Do stĺpcov vložte mesiac a typ. 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 malý 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úskom 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 január, skutočné údaje pre Baybrook. Kliknite do tejto bunky a zadajte znak rovnosti. Pomocou myši prejdite späť na kontingenčnú tabuľku. Nájdite bunku pre januárové skutočné údaje 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.
Tu je vzorec po jeho úprave. Preč sú „Baybrook“, „Jan“ a „Skutočné“. Namiesto toho poukazujete na $ D6, E $ 3, 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áš ročný pracovný tok:
- 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ú.
Zmeňte bunku P1
Musíte uznať, že použitie obyčajného 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.
Pozeraj video
- GetPivotData sa stane, keď vzorec ukazuje do kontingenčnej tabuľky
- Aj keď je počiatočný vzorec správny, vzorec nie je možné skopírovať
- Väčšina ľudí neznáša getpivotdata a chce tomu zabrániť
- Metóda 1: Vytvorte vzorec bez použitia myši alebo klávesov so šípkami
- Metóda 2: Pomocou rozbaľovacej ponuky vedľa možností natrvalo vypnite funkciu GetPivotData
- Existuje však použitie pre GetPivotData
- Váš manažér chce prehľad so skutočnosťou za posledné mesiace a rozpočtom do budúcnosti
- Bežný pracovný postup by bol, keby ste vytvorili kontingenčnú tabuľku, konvertovali na hodnoty a odstránili stĺpce
- Odstránenie medzisúčtov, aby sa zabránilo januárovému aktuálnemu + plánu, pomocou nastavenia poľa
- Namiesto toho vytvorte kontingenčnú tabuľku s „príliš veľkým“ údajom
- Použite pekne naformátovaný pracovný hárok správy
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Z prvej údajovej bunky v hárku vytvorte pomocou myši vzorec
- Umožnite uskutočnenie funkcie GetPivotData
- Preskúmajte syntax GetPivotData (pole, ktoré sa má vrátiť, umiestnenie pivotnej dvojice)
- Zmeňte pevne nastavenú hodnotu tak, aby ukazovala na bunku
- Stlačením F4 trikrát sa uzamkne iba stĺpec
- Stlačením F4 dvakrát sa uzamkne iba riadok
- Prilepte špeciálne vzorce
- Pracovný postup budúci mesiac: pridávanie údajov, obnovenie kontingenčnej tabuľky, zmena dátumu
- Dajte si pozor na nové obchody
Prepis videa
Naučte sa Excel z podcastu, epizóda 2013 - GetPivotData nemusí byť úplne zlé!
Budem podcastovať celú túto knihu. Kliknutím na písmeno „i“ v pravom hornom rohu sa prihláste na odber.
Dobre, späť v epizóde 1998 som krátko hovoril o tomto probléme GetPivotData. Ak vypočítame% rozptyl a sme mimo kontingenčnej tabuľky smerujúcej dovnútra, používam myš alebo kláves so šípkou, takže 2019 / 2018-1. Táto odpoveď, ktorú tu dostaneme, je správna pre január, ale keď to dvakrát skopírujeme, vzorec sa nekopíruje, januárovú odpoveď dostaneme úplne dole. A keď sa na to pozrieme, dostávame GetPivotData, nenapísal som GetPivotData, iba som ukázal na tieto bunky a toto sa začalo diať späť v Exceli 2002 bez akéhokoľvek varovania. A v tom okamihu som povedal, že spôsobom, ako sa tomu vyhnúť, je napísať vzorec C5 / B5-1 a dostanete vzorec, ktorý môžete skopírovať. Alebo ak nenávidíte GetPivotData, ak je „úplne zlý“, prejdite na kartu Analyzovať, nedávajtet mimochodom otvorte tlačidlo Možnosti. Vráťte sa späť do kontingenčnej tabuľky, prejdite na kartu Analýza, otvorte rozbaľovaciu ponuku vedľa položky Možnosti, zrušte začiarknutie tohto políčka, jedná sa o globálne nastavenie. Akonáhle to vypnete, bude vypnuté navždy, v poriadku.
Najčastejšie dostávam otázky „Ako vypnem funkciu GetPivotData?“ ale raz za čas si nájdu niekoho, kto miluje GetPivotData. A ešte som bol na obede s Robom Collie, keď bol ešte v spoločnosti Microsoft, a povedal: „No, naši interní zákazníci milujú GetPivotData.“ Povedal som: „Čo? Nie, každý nenávidí GetPivotData! “ Rob hovorí: „Máte pravdu, mimo spoločnosti Microsoft absolútne neznášajú GetPivotData.“ Hovorím o účtovníkoch v spoločnosti Microsoft a neskôr som sa stretol s jedným, ktorý teraz pracuje pre tím Excel, Carlosom a Carlos bol jedným z účtovníkov, ktorí používajú túto metódu.
Dobre, takže musíme urobiť. Máme tu našu správu, súbor údajov, ktorý pre každý mesiac máme plánovaný pre každý obchod, a potom v dolnej časti zhromažďujeme skutočnosti. Dobre, takže máme údaje za január až december, ale máme iba údaje za niekoľko mesiacov, teda mesiacov, ktoré uplynuli. A to, čo od nás chce náš manažér, je vytvoriť prehľad s obchodmi na ľavej strane, iba Texaské obchody, samozrejme, uľahčujú život. A potom ideme naprieč, máme mesiace, a ak máme skutočnú hodnotu pre tento mesiac, ukážeme skutočnú hodnotu, teda skutočný január, skutočný február, skutočný marec, skutočný apríl. Ale potom na mesiace, keď nemáme skutočné údaje, prepneme a zobrazíme rozpočet, takže rozpočet vyjdeme do decembra a potom celkom, všetko. No, keď sa pokúsite vytvoriť túto kontingenčnú tabuľku, jo,nefunguje to.
Takže vložte kontingenčnú tabuľku, nový pracovný hárok, dajte Store dole na ľavú stranu, ktorá je krásna, do horného rohu vložte mesiace, do horného konca vložte Type, do tohto vložte Sales. Takže toto je to, čo dostaneme, s čím musíme začať pracovať, takže máme januárový skutočný, januárový plán a potom úplne zbytočný januárový skutočný plus plán. Toto nikdy nikto nepoužije, ale týchto sivých stĺpcov sa môžem zbaviť, to je dosť ľahké, niektoré tu do tejto bunky, prejsť na Nastavenia poľa a Medzisúčty zmeniť na Žiadne. Ale neexistuje pre mňa absolútne žiadny spôsob, ako odstrániť januárový plán, ktorý neodstráni ani plán z apríla, mája, júna, júla, v poriadku, neexistuje spôsob, ako sa toho zbaviť. V tomto okamihu teda každý mesiac vyberiem celú kontingenčnú tabuľku, idem na položku Kopírovať a potom na položku Prilepiť, prilepiť hodnoty. Už to nie je kontingenčná tabuľka,a potom začnem ručne mazať stĺpce, ktoré sa v prehľade nezobrazia.
Dobre, je to obvyklá metóda, ale účtovníci v spoločnosti Microsoft pridali v januári ďalší krok, ktorý trvá 15 minút a tento krok umožňuje tejto kontingenčnej tabuľke žiť večne, však? Tomu hovorím najškaredšia kontingenčná tabuľka na svete a účtovníci spoločnosti Microsoft akceptujú, že ide o najškaredšiu kontingenčnú tabuľku na svete, ale okrem nich túto správu nikto nikdy neuvidí. Čo robia, je to, že prídu k novému hárku a vytvoria správu, ktorú chce ich manažér. Dobre, takže tu sú obchody na ľavej strane, dokonca som to zoskupil do Houstonu, Dallasu a ďalších, je to pekne naformátovaná správa. Zvýraznil som súčty, uvidíte, že keď dostaneme nejaké čísla, v prvom riadku je mena, ale nie tieto nasledujúce riadky, prázdne riadky. Ou, prázdne riadky v kontingenčnej tabuľke.A ešte jedna malá logika tu, kde môžem vložiť dátum skončenia do bunky P1, a potom tu mám vzorec, ktorý analyzuje, že AK je mesiac tohto dátumu> tento stĺpec, a potom vložiť slovo Skutočný, inak dajte slovo Plán, dobre. Takže všetko, čo musím urobiť, je zmeniť to cez dátum a potom slovo Skutočné otočiť podľa plánu, dobre.
Teraz je to, čo robíme, dovolíme si byť GetPivotData'd, že? Nie som si istý, či ide o sloveso, ale umožníme Microsoftu získať GetPivotData. Takže začnem vytvárať vzorec s =, chytím myš a idem hľadať januárový skutočný Baybrook! Takže sa vrátim do najškaredšej Pivot tabuľky na svete, nájdem Baybrook, nájdem január, nájdem skutočný a kliknem na Enter a nech mi to urobia, dobre, ideme na to, teraz máme vzorec GetPivotData. Pamätám si na deň, keď som to urobil, bolo to ako, keď mi Rob vysvetlil, čo robia, a ja som sa vrátil a skúsil to. Teraz som sa zrazu celý svoj život zbavoval GetPivotData, nikdy som sa do jeho života skutočne nedostal. Takže to, čo to je, je prvá položka, ktorú hľadáme. “sa pole s názvom Predaj, tu sa začína kontingenčná tabuľka a môže to byť akákoľvek bunka v kontingenčnej tabuľke, používajú ľavú hornú ruku.
V poriadku, toto je názov poľa „Obchod“, a potom nastavili pevný kód „Baybrook“, toto je názov poľa „Mesiac“, majú pevný kód „január“, toto je názov poľa „Typ“ a oni ' mám napevno zakódované „Skutočné“. Z tohto dôvodu to nemôžete kopírovať, pretože majú pevne napísané hodnoty. Ale účtovníci spoločnosti Microsoft, Carlos a jeho spolupracovníci si uvedomujú: „Fíha, počkajte, máme tu slovo Baybrook, máme tu január, máme tu skutočný. Potrebujeme iba zmeniť tento vzorec tak, aby ukazoval na skutočné bunky v správe namiesto toho, aby boli napevno. “ Dobre, tak to nazývajú parametrizovanie GetPivotData.
Odstráňte slovo Baybrook, poďte sem a kliknite na bunku D6. Teraz to musím uzamknúť do stĺpca, v poriadku, takže stlačím kláves F4 trikrát, dostanem jeden dolár pred D, v poriadku. Za mesiac január odstránim pevne zakódovaný január, kliknem na bunku E3, dvakrát stlačím kláves F4, aby som ju uzamkol na riadok, E $ 3. Zadajte Skutočný, odstráňte slovo Skutočný, kliknite na E4, znova dvakrát F4, dobre, a dostanem vzorec, ktorý teraz stiahne tieto údaje späť. Skopírujem to a potom prilepím Špeciálne, vyberiem Formáty, alt = "" ESF, pozri, že F je tam podčiarknuté, ESF Enter, a potom, keď som to urobil, zopakujem to s F4, F4 je prerobený a F4. Dobre, takže teraz máme pekne vyzerajúcu správu, ktorá má prázdne miesta, má formátovanie, v každej sekcii je podčiarknuté jedno účtovníctvo,úplne dole má dvojité účtovné podčiarknutie.
Správne, tieto veci nikdy nedostanete v kontingenčnej tabuľke, to je nemožné, ale tento prehľad vychádza z kontingenčnej tabuľky. Takže potom, čo robíme, keď dostaneme májové skutočnosti, vráťme sa sem, vložíme ich, obnovíme najškaredšiu kontingenčnú tabuľku na svete a potom tu v prehľade stačí zmeniť konečný dátum z 30. na 5. mája. A to, čo to urobí, je to, že spôsobí, že tento vzorec prejde zo slova Plán na Skutočný, ktoré bude v poriadku a namiesto plánu vytiahne zo správy skutočnosti. Teraz je tu vec, ktorá je skvelá, však? Vidím, kde by som to veľa robil, keby som stále pracoval v účtovníctve.
Na čo musíte byť naozaj opatrní, je to, že keď postavia nový obchod, musíte ich vedieť pridať manuálne, správne, údaje sa zobrazia v kontingenčnej tabuľke, ale vy by ste ich pridali manuálne. Teraz je tento podmnožinou všetkých obchodov, ak by vykazoval všetky obchody, pravdepodobne by som tu mimo rozsah tlače mal niečo, čo vytiahlo celkový súčet z kontingenčnej tabuľky. A potom by som vedel, že ak sa tento súčet nezhoduje s celkovým súčtom z kontingenčnej tabuľky, že niečo nie je v poriadku, a budem mať funkciu IF tu dole. Hovoriac „Hej, viete, nové údaje, ktoré boli pridané, buďte veľmi opatrní. “ Majú nejaký druh mechanizmu na zisťovanie, či sú tam nové údaje. Ale chápem, je to super využitie. Takže aj keď nás GetPivotData väčšinou privádza do šialenstva, v skutočnosti to môže byť využité. Dobre,takže to je tip # 21 zo 40 v knihe, kúpte si knihu hneď teraz, objednajte si online, kliknite na to „i“ v pravom hornom rohu.
Dlhá, dnes veľká rekapitulácia, v poriadku: GetPivotData sa stane, keď vzorec ukazuje dovnútra kontingenčnej tabuľky, vzorec mimo kontingenčnej tabuľky smeruje dovnútra. Aj keď je počiatočný vzorec správny, nebude sa kopírovať. Väčšina ľudí nenávidí GetPivotData a chce tomu zabrániť. Takže môžete vytvoriť vzorec bez myši alebo klávesov so šípkami, jednoducho zadajte vzorec alebo natrvalo vypnite funkciu GetPivotData, ach, ale má to svoje využitie, dobre. Musíme teda zostaviť správu s aktuálnymi údajmi za minulý mesiac, rozpočtom do budúcnosti. Normálny pracovný tok, vytvorenie kontingenčnej tabuľky, prevod na hodnoty, odstránenie stĺpcov. Existuje spôsob, ako odstrániť medzisúčty pomocou nastavenia poľa a zbaviť sa tak aktuálneho januárového plánu plus. Namiesto toho vytvoríme najškaredšiu kontingenčnú tabuľku na svete s príliš veľkým počtom údajov.
Vytvorte pekne naformátovaný, jednoducho obyčajný starý pracovný hárok zostavy s možno trochou logiky, aby ste zmenili slovo Skutočné na Plán. A potom z prvej bunky správy, na prvé miesto, kde sa v tejto správe budú nachádzať čísla, zadajte an =, prejdite na kontingenčnú tabuľku a nechajte uskutočniť GetPivotData. Preskúmame syntax GetPivotData, takže ide o pole na vrátenie, Sales, kde žije kontingenčná tabuľka, a potom dvojice kritérií, názov poľa a hodnota. Chystáme sa odstrániť pevne zakódovanú hodnotu a ukázať na bunku. Stlačením klávesu F4 trikrát uzamknete iba stĺpec, stlačením klávesu F4 dvakrát uzamknete iba riadok, skopírujeme tento vzorec a prilepíme špeciálne vzorce. Vhodil som tam ďalší tip, že F4 je prerobený, takže mi stačilo prejsť na dialógové okno Prilepiť špeciálne iba raz a potom pre ďalšie Prilepiť špeciálne vzorce sa použila iba klávesnica F4. Budúci mesiac doplňte údaje,obnovte kontingenčnú tabuľku a zmeňte dátum ukončenia. Uistite sa, že nevybudovali žiadne nové obchody, viete, že majú nejaký druh mechanizmu, buď manuálny, alebo kontrolný vzorec, pozrite sa na to. Vďaka iTrainerMX na Twitteri, ktorý navrhol GetPivotData, tiež Carlos a Rob z Microsoftu, Rob teraz z Power Pivot Pro. Carlos za to, že to použil, a Rob za to, že mi povedal, že to používa Carlos, som sa s Carlosom stretol neskôr a on potvrdil, že áno, bol jedným z účtovníkov, ktorý to v spoločnosti Microsoft neustále používal, dobre, choď.a Robovi za to, že mi povedal, že to používa Carlos, som sa stretol s Carlosom neskôr a on potvrdil, že áno, bol jedným z účtovníkov, ktorý to celý čas používal v spoločnosti Microsoft, dobre, choď.a Robovi za to, že mi povedal, že to používa Carlos, som sa stretol s Carlosom neskôr a on potvrdil, že áno, bol jedným z účtovníkov, ktorý to celý čas používal v spoločnosti Microsoft, dobre, choď.
No hej, chcem sa ti poďakovať, že si sa zastavil, uvidíme sa nabudúce pri ďalšom netcaste od!
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2013.xlsx
