Utálja az Excel GETPIVOTDATA funkcióját? Miért jelenik meg? Hogyan akadályozhatja meg? Jól használható a GETPIVOTDATA?
A legtöbb ember akkor találkozik először a GETPIVOTDATA-val, amikor megpróbál olyan képletet felépíteni egy olyan kimutatótáblán kívül, amely számokat használ a kimutató táblázatban. Például ez az eltérési százalék nem másolható le a többi hónapra, mivel az Excel beszúrja a GETPIVOTDATA függvényeket.
Az Excel bármikor beszúrja a GETPIVOTDATA-t, amikor az egérrel vagy a nyílbillentyűkkel a pivot-tábla belsejében lévő cellára mutat, miközben képletet épít a pivot-táblán kívül.
Egyébként, ha nem szeretné, hogy a GETPIVOTDATA függvény megjelenjen, egyszerűen írja be a következő képletet: = D5 / C5-1, anélkül, hogy az egérrel vagy a nyíl billentyűkkel mutatna a cellákra. Ez a képlet gond nélkül másol.
Itt van egy adatkészlet, amely üzletenként havonta egy tervszámot tartalmaz. Vannak tényleges havi eladások is üzletenként a teljes hónapokra. A cél egy olyan jelentés készítése, amely megmutatja az elvégzett hónapok tényállását és megtervezi a következő hónapokat.
Hozzon létre egy forgatótáblát a Store in ROWS szolgáltatással. Tegye a hónapot és a gépet az OSZLOPOKba. Megkapja az alább látható jelentést, a januári tényleges, a januári tervet és a teljesen értelmetlen januári tényleges + tervet.
Ha kiválaszt egy hónapos cellát, és a Mezőbeállítások menüpontra lép, akkor az összesítést megváltoztathatja Nincs értékre.
Ez eltávolítja a haszontalan Actual + tervet. De még mindig meg kell szabadulnia a januártól áprilisig tartó tervoszlopoktól. Erre nincs jó módszer a forgóasztalon belül.
Tehát a havi munkafolyamat:
- Adja hozzá az új hónap tényállását az adatkészlethez.
- Készítsen egy új forgóasztalt a semmiből.
- Másolja a pivot táblázatot, és illessze be értékként, így az már nem pivot tábla.
- Törölje azokat az oszlopokat, amelyekre nincs szüksége.
Van egy jobb út. A következő nagyon kicsi ábra egy új Excel munkalapot mutat be a munkafüzethez hozzáadva. Ez csak egyenes Excel, nincsenek forgó táblák. Az egyetlen varázslat egy IF függvény a 4. sorban, amely a P1 cellában szereplő dátum alapján átvált aktuálisról tervre.
A legelső cellát, amelyet ki kell tölteni, január, a Actuals for Baybrook. Kattintson erre a cellára, és írja be az egyenlőségjelet. Az egérrel navigáljon vissza a forgatótáblához. Keresse meg a Baybrook januári tényleges celláját. Kattintson arra a cellára, és nyomja meg az Enter billentyűt. Szokás szerint az Excel létrehozza az egyik bosszantó GETPIVOTDATA függvényt, amelyet nem lehet másolni.
De ma tanulmányozzuk a GETPIVOTDATA szintaxisát.
Az első érv az "Értékesítés" számmező. A második érv az a cella, ahol a pivot tábla található. A fennmaradó argumentumpár a mező neve és értéke. Látja, mit tett az automatikusan létrehozott képlet? Keményen kódolt "Baybrook" az üzlet neve. Ezért nem másolhatja ezeket az automatikusan létrehozott GETPIVOTDATA képleteket. Valójában keményen kódolják a neveket képletekké. Annak ellenére, hogy ezeket a képleteket nem tudja lemásolni, szerkesztheti őket. Ebben az esetben jobb lenne, ha a képletet úgy szerkesztené, hogy a $ D6 cellára mutasson.
Itt van a képlet a szerkesztés után. Elmúltak a "Baybrook", a "Jan" és a "Actual". Ehelyett a $ D6, E $ 3, E $ 4-re mutat.
Másolja ezt a képletet, majd válassza a Speciális beillesztés, képletek lehetőséget az összes többi numerikus cellában.
Itt van az éves munkafolyamat:
- Készítsen egy csúnya forgóasztalt, amelyet soha senki sem fog látni.
- Állítsa be a jelentés munkalapot.
Minden hónapban:
- Illesszen be új tényleges adatokat az adatok alá.
- Frissítse a csúnya forgóasztalt.
-
Módosítsa a jelentési lap P1 celláját az új hónap tükrözése érdekében. Minden szám frissül.
Változtassa meg a P1 cellát
Be kell vallanod, hogy egy sima jelentés használata, amely a számokat kihúzza az elfordító táblából, mindkét világ legjavát nyújtja. Szabadon formázhatja a jelentést oly módon, hogy ne formázhassa a kimutató táblázatot. Az üres sorok rendben vannak. Az első és az utolsó sorban lehetnek valutaszimbólumok, de a kettő között nem. Kettős aláhúzást kap a végösszeg alatt is.
Köszönet @iTrainerMX-nek, hogy javasolta ezt a funkciót.
Videót nézni
- A GetPivotData akkor történik, amikor egy képlet egy kimutató tábla belsejében mutat
- Amíg a kezdeti képlet helyes, a képletet nem lehet másolni
- A legtöbb ember utálja a getpivotdata-t, és meg akarja akadályozni
- 1. módszer: Készítsen képletet az egér vagy a nyíl billentyűk nélkül
- 2. módszer: Kapcsolja ki a GetPivotData adatot véglegesen a lehetőségek melletti legördülő menü használatával
- De a GetPivotData használható
- Ön menedzser jelentést szeretne a Tényleges adatokról az elmúlt hónapokról és a jövő költségvetéséről
- A normál munkafolyamat során létre kellene hozni egy kimutatótáblát, konvertálni értékekké, törölni az oszlopokat
- Részösszegek eltávolítása a januári tényleges + terv megakadályozásához a Field Settings használatával
- Ehelyett hozzon létre egy "túl sok" adatot tartalmazó forgatótáblát
- Használjon szépen formázott jelentés munkalapot
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- A munkalap első adatcellájából készítsen képletet az egérrel
- Engedje meg, hogy a GetPivotData megtörténjen
- Vizsgálja meg a GetPivotData szintaxisát (visszatérő mező, kimutatás helye, párok)
- Módosítsa a keményen kódolt értéket egy cellára
- Az F4 háromszori megnyomásával csak az oszlop zárolódik
- Az F4 kétszeri megnyomása csak a sort zárja le
- Különleges képletek beillesztése
- Munkafolyamat a következő hónapban: Adatok hozzáadása, pivot-tábla frissítése, dátumváltás
- Nagyon óvatos, hogy vigyázzon az új üzletekre
A videó átirata
Ismerje meg az Excel programot a podcastból, 2013. epizód - a GetPivotData nem lehet teljesen gonosz!
Podcastolom ezt az egész könyvet, a feliratkozáshoz kattintson a jobb felső sarokban található „i” gombra.
Rendben, még az 1998. epizódban röviden beszéltem erről a GetPivotData problémáról. Ha kiszámítunk egy% -os varianciát, és kívül esünk a kimutatott Pivot-táblán, és én az egeret vagy a nyílbillentyűt használom, akkor 2019 / 2018-1. Ez a válasz, amelyet ide fogunk kapni, januárra helyes, de amikor duplán kattintunk, hogy ezt lemásoljuk, a képlet nem másolódik, a januári választ egészen lefelé kapjuk. És amikor ránézünk, kapunk GetPivotData-t, én nem gépeltem be GetPivotData-t, csak rámutattam ezekre a cellákra, és ez mindenféle figyelmeztetés nélkül elkezdődött az Excel 2002-ben. És ekkor azt mondtam, hogy ennek elkerülése a C5 / B5-1 képlet beírása, és kapsz egy másolatot. Vagy ha csak gyűlöli a GetPivotData-t, ha „teljesen gonosz”, akkor lépjen az Elemzés fülre, ne ”t ne nyissa meg az Opciók gombot. Menjen vissza a Pivot táblához, menjen az Elemzés fülre, nyissa meg az Opciók melletti legördülő menüt, törölje a jelölést a négyzetből, ez globális beállítás. Miután kikapcsolta, örökké kikapcsol, rendben.
Legtöbbször a következő kérdéseket kapom: „Hogyan kapcsolhatom ki a GetPivotData-t?” de időnként kapok valakit, aki szereti a GetPivotData-t. Rob Collie-val ebédeltem, amikor még a Microsoftnál dolgozott, és azt mondta: "Nos, belső ügyfeleink imádják a GetPivotDatát." Azt mondtam: „Mi? Nem, mindenki utálja a GetPivotDatát! ” Rob azt mondja: "Igazad van, a Microsofton kívül abszolút, utálják a GetPivotDatát." A Microsofton belüli könyvelőkről beszélek, később pedig megismerkedtem azzal, aki most az Excel csapatnál dolgozik, Carlos-szal, és Carlos volt az egyik könyvelő, aki ezt a módszert alkalmazza.
Rendben, szóval itt kell tennünk. Megvan a jelentésünk, egy itt beállított adat, amely minden hónapra megtervezi az egyes üzletek tervét, majd alul aktuális adatokat halmozunk fel. Rendben, tehát január és december között van aktuális számunk, de csak néhány hónapra, az elmúlt hónapokra. A menedzserünk azt akarja, hogy tegyünk egy jelentést a bal oldalon lévő üzletekkel, természetesen csak a texasi üzletek, hogy megnehezítsék az életet. És akkor áttekintve hónapjaink vannak, és ha van tényleges adatunk arra a hónapra, akkor megmutatjuk a tényleges, tehát a január tényleges, a tényleges február, a március tényleges, az április tényleges értékét. De aztán azokra a hónapokra, amikor nincsenek tényadataink, átkapcsoljuk és megmutatjuk a költségvetést, tehát decemberig egészítsük ki a költségvetést, utána pedig összesen mindent, rendben. Nos, amikor megpróbálja létrehozni ezt a Pivot-táblázatot,nem működik.
Tehát helyezze be a PivotTable-t, az új munkalapot, a Store bal oldalát tegye le a gyönyörű oldalra, tegye a hónapokat a tetejére, a Type-t tegye a tetejére, tegye ide az Értékesítés rendben. Tehát íme, mit kapunk, amellyel el kell kezdenünk dolgozni, tehát megvan a januári tényleges, januári terv, majd a teljesen haszontalan januári tényleges plusz terv. Ezt soha senki nem fogja használni, de megszabadulhatok ezektől a szürke oszlopoktól, ez elég könnyű, van, aki itt erre a cellára megy, a Mezőbeállítások menüpontba állítja, és az Részösszegeket Nincs értékre változtatja. De semmi esetre sem tudom eltávolítani a januári tervet, amely nem távolítja el az áprilisi májusi júniusi júliusi tervet sem, rendben, nincs mód erre megszabadulni. Tehát havonta ezen a ponton elakadtam a teljes kimutatási tábla kijelölésével, a Másolás, majd az Értékek beillesztése, beillesztése elemre. Ez már nem Pivot-asztal,majd elkezdem manuálisan törölni azokat az oszlopokat, amelyek nem jelennek meg a jelentésben.
Rendben, ez a normális módszer, de a Microsoft könyvelői januárban újabb lépést tettek hozzá, ez 15 percet vesz igénybe, és ez a lépés lehetővé teszi, hogy ez a Pivot-tábla örökké éljen, igaz? Ezt hívom a világ legrondább Pivot-táblázatának, és a Microsoft könyvelői elfogadják, hogy ez a világ legrondább Pivot-táblája, de rajtuk kívül soha senki nem fogja látni ezt a jelentést. Amit csinálnak, idejönnek egy új lapra, és elkészítik azt a jelentést, amelyet a menedzserük szeretne. Rendben, szóval itt vannak az üzletek a bal oldalon, még Houstonba, Dallasba és másba is csoportosítottam, ez egy szépen formázott jelentés. Kiemeltem az összesítést, látni fogja, hogy amikor beérünk néhány számmal, az első sorban van pénznem, de nem ezek a következő sorok, üres sorok. Ó, üres sorok a Pivot-táblában.És itt van egy aprócska logika, ahová a P1 cellába be tudom tenni az átfutási dátumot, és itt van egy képletem, amely elemzi, hogy HA az áthidalás hónapja> ez az oszlop, majd felteszem az aktuális szót, különben tedd a Terv szót, rendben. Tehát csak annyit kell tennem, hogy megváltoztatom ezt az időpontot, majd a Tényleges szó átfordul a tervhez, Rendben.
Itt van, amit csinálunk, megengedjük magunknak, hogy GetPivotData'd legyenek, igaz? Nem vagyok biztos benne, hogy ez egy ige, de engedélyezni fogjuk a Microsoft számára a GetPivotData használatát. Tehát elkezdek egy képletet építeni egy = -el, megragadom az egeret, és megyek keresni a tényleges januári Baybrook-ot! Tehát visszamegyek a világ legrondább Pivot-táblázatába, megtalálom Baybrook-ot, megtalálom a Januárt, ténylegesnek találom, és rákattintok az Enterre, és hagyom, hogy tegyék meg velem, rendben, ott vagyunk, most van egy GetPivotData képlet. Emlékszem arra a napra, amikor ezt tettem, olyan volt, mint, tudod, miután Rob elmagyarázta nekem, mit csinálnak, én pedig visszamentem és kipróbáltam. Most hirtelen, egész életemben megszabadultam a GetPivotData-tól, még soha nem fogadtam el a GetPivotData-t. Tehát mi ez, az az első elem, amit keresünk, ottA Sales nevű mező, itt kezdődik a Pivot tábla, és a Pivot tábla bármely cellája lehet, ők a bal felső kézet használják.
Rendben, ez egy „Store” mezőnév, majd kemény kódolással ellátták a „Baybrook”, ez a „hónap” mezőnévvel, a „január” kemény kódolással, ez a „Type” mezőnévvel és keményen kódolt „Aktuális”. Éppen ezért nem lehet lemásolni, mert keményen kódolták az értékeket. De a Microsoft könyvelői, Carlos és munkatársai rájönnek: „Hú, várj csak egy percet, itt van a Baybrook szó, itt van január, itt van a Actual. Csak meg kell változtatnunk ezt a képletet, hogy a kódolás helyett a jelentés tényleges celláira mutassunk. " Rendben, ezért ezt a paraméterezést GetPivotData néven hívják.
Távolítsa el a Baybrook szót, jöjjön ide, és kattintson a D6 cellára. Nos, ezt le kell zárnom az oszlopig, rendben, ezért háromszor megnyomom az F4 billentyűt, kapok egyetlen dollárt a D előtt, rendben. Január hónapra eltávolítom a keményen kódolt januárt, rákattintok az E3 cellára, kétszer megnyomom az F4 billentyűt, hogy lezárjam a sorig, E $ 3. Írja be a Tényleges szót, távolítsa el a Tényleges szót, kattintson az E4-re, majd kétszer az F4-re, rendben, és kapok egy képletet, amely most visszahúzza ezeket az adatokat. Ezt átmásolom, majd a Speciális beillesztés lehetőséget, majd a Formátumok lehetőséget választom, alt = "" ESF, lásd: az F alá van húzva, az ESF Enter, majd most, hogy megtettem, csak megismételem az F4-tel, F4 egy újrakezdés, és F4. Rendben, szóval most szép megjelenésű jelentésünk van, üres, formázott, minden szakasz alatt egyetlen számviteli aláhúzás van,legalsó részén kettős számviteli aláhúzás van.
Igaz, soha nem kapja meg ezeket a dolgokat a Pivot-táblában, ez lehetetlen, de ezt a jelentést a Pivot-táblázat vezérli. Tehát akkor mit tegyünk, amikor megkapjuk a májusi tényeket, térjünk vissza ide, illesszük be őket, menjünk frissíteni a világ legrondább Pivot-táblázatát, majd itt a jelentésben csak változtassuk meg az átmenő dátumot 4/30-ról 5/31-re. Ez pedig azt eredményezi, hogy ez a képlet áttér a Terv szóra a Tényleges szóra, ami a terv helyett rendben a jelentésből kivonja a tényeket. Na, itt van ez a dolog - ez nagyszerű, igaz? Látom, hol csinálnám ezt sokat, ha még mindig, tudod, a könyvelésben dolgoznék.
Nagyon óvatosnak kell lennie, ha új boltot építenek, akkor tudnia kell, hogy manuálisan adja hozzá, ugye, az adatok megjelennek a Pivot táblában, de manuálisan hozzáadná. Most ez az összes üzlet részhalmaza, ha az összes üzletről beszámolna, valószínűleg itt lennék, a nyomtatási tartományon kívül, van valami, ami kihúzta a végösszeget a Pivot táblázatból. És akkor tudnám, ha ez az összeg nem egyezik meg a Pivot-tábla összesítésével, akkor valami nincs rendben, és itt van egy IF funkciója. Azt mondja: "Hé, tudod, új adatok kerültek hozzá, legyen nagyon óvatos. ” Valamiféle mechanizmusuk van annak felismerésére, hogy új adatok vannak. De értem, klassz felhasználás. Tehát, bár a GetPivotData legtöbbször csak megőrjít minket, valójában felhasználható lehet rá. Rendben,Tehát ez a 21. tipp a 40-ből a könyvben, most vásárolja meg a könyvet, rendeljen online, kattintson a jobb felső sarokban található „i” gombra.
Hosszú, hosszú összefoglalás ma, rendben: A GetPivotData akkor történik, amikor egy képlet a kimutatási táblázat belsejében mutat, a képlet pedig a kimutatási táblán kívül. Bár a kezdeti képlet helyes, nem másolja. A legtöbb ember utálja a GetPivotData-t, és meg akarja akadályozni. Tehát összeállíthat egy képletet az egér vagy a nyílbillentyűk nélkül, csak be kell írnia a képletet, vagy véglegesen kikapcsolhatja a GetPivotData-t, ah, de van rá haszna, rendben. Tehát jelentést kell készítenünk az elmúlt hónap tényállásáról, a jövő költségvetéséről. Normál munkafolyamat, hozzon létre egy kimutató táblázatot, konvertáljon értékekre, oszlopok törlése. Van egy módja a részösszegek eltávolítására a Mezőbeállítások használatával, megszabadulva a januári tényleges plusz tervtől. Ehelyett csak a világ legrondább Pivot-tábláját fogjuk létrehozni, túl sok adattal.
Készítsen egy szépen formázott, egyszerűen régi jelentés munkalapot, talán egy kis logikával, hogy a tényleges szót megváltoztassa tervre. Ezután az első jelentés cellából, az első helyre, ahol a számok szerepelnek abban a jelentésben, írja be az = értéket, lépjen a Pivot táblára, és hagyja, hogy a GetPivotData megtörténjen. Megvizsgáljuk a GetPivotData szintaxisát, tehát ez a visszatérő mező, az Értékesítés, ahol a kimutatás tábla él, majd pár feltétel, a mező neve és az értéke. Eltávolítjuk a keményen kódolt értéket, és egy cellára mutatunk. Az F4 háromszori megnyomásával csak az oszlop zárolódik, az F4 kétszeres megnyomásával pedig csak a sor záródik, másoljuk le ezt a képletet. Egy olyan tippet dobtam oda, hogy az F4 egy újrakezdés, így csak egyszer kellett elmenni a Paste Special párbeszédablakba, majd a következő Paste Special Formulas esetében csak az F4-et használtam. A következő hónapban adja hozzá az adatokat,frissítse a kimutatási táblázatot, módosítsa az átfutási dátumot. Győződjön meg róla, hogy nem építettek új üzleteket, tudják, hogy van valamilyen mechanizmusuk, akár kézi, akár ellenőrző képlet, ellenőrizze. Köszönet a Twitteren lévő iTrainerMX-nek, aki a GetPivotData-t javasolta, szintén Carlos és Rob a Microsoft-tól, Rob pedig a Power Pivot Pro-tól. Carlos, hogy ezt használta, és Rob, aki elmondta, hogy Carlos használja, később találkoztam Carlosszal, és ő megerősítette, hogy igen, ő volt az egyik könyvelő, aki ezt állandóan használta a Microsoftnál, rendben, tessék.és Rob, aki elmondta nekem, hogy Carlos használja, később találkoztam Carlossal, és ő megerősítette, hogy igen, ő volt az egyik könyvelő, aki ezt állandóan használta a Microsoftnál, rendben, tessék.és Rob, aki elmondta nekem, hogy Carlos használja, később találkoztam Carlossal, és ő megerősítette, hogy igen, ő volt az egyik könyvelő, aki ezt állandóan használta a Microsoftnál, rendben, tessék.
Nos, hála, szeretném megköszönni, hogy megálltál, legközelebb találkozunk egy újabb netcast-lal!
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2013.xlsx
