Kas sa vihkad Exceli funktsiooni GETPIVOTDATA? Miks see ilmub? Kuidas saab seda ära hoida? Kas GETPIVOTDATA on hästi kasutatav?
Enamik inimesi puutub GETPIVOTDATA-ga esmakordselt kokku, kui proovib valemit luua väljaspool pöördtabelit, mis kasutab pöördetabelis numbreid. Näiteks ei kopeerita seda variatsiooniprotsenti teistele kuudele, kuna Excel sisestab funktsioonid GETPIVOTDATA.
Excel lisab GETPIVOTDATA alati, kui kasutate hiirt või nooleklahve, et osutada pöördtabelis olevale lahtrile, ehitades samal ajal valemit väljaspool liigendtabelit.
Muide, kui te ei soovi, et funktsioon GETPIVOTDATA ilmuks, sisestage lihtsalt valem, näiteks = D5 / C5-1, ilma et hiirt või nooleklahve lahtritele osutamiseks kasutataks. See valem kopeerib ilma probleemideta.
Siin on andmekogum, mis sisaldab ühte plaani numbrit kuus poe kohta. Lõplike kuude jooksul toimub ka tegelik müük poe kohta. Teie eesmärk on koostada aruanne, mis näitab lõppenud kuude tegelikke andmeid ja tulevaste kuude plaani.
Koostage poes ROWS pivot-tabel. Pange veergudesse Kuu ja tüüp. Saate allpool näidatud aruande koos jaanuari tegeliku, jaanuari plaaniga ja täiesti mõttetu jaanuari tegeliku + plaaniga.
Kui valite kuu lahtri ja avate välja sätted, saate muuta vahesummad väärtuseks Puudub.
See eemaldab kasutu Tegelik + kava. Kuid ikkagi peate jaanuari kuni aprilli plaaniveergudest lahti saama. Pöördtabeli sees pole selleks head viisi.
Teie igakuine töövoog muutub:
- Lisage andmekogumisse uue kuu tegelikud andmed.
- Ehitage nullist uus pöördtabel.
- Kopeerige pöördtabel ja kleepige väärtustena, nii et see pole enam pöördtabel.
- Kustutage veerud, mida te ei vaja.
On parem viis minna. Järgmine väga väike joonis näitab uut Exceli töölehte, mis on lisatud töövihikusse. See kõik on lihtsalt sirge Excel, pole ühtegi pöördtabelit. Ainuke maagia on IF-i funktsioon 4. real, mis lülitub lahtris P1 oleva kuupäeva põhjal tegelikust plaanini.
Kõige esimene lahter, mis tuleb täita, on jaanuar, Actuals for Baybrook. Klõpsake selles lahtris ja tippige võrdusmärk. Liikuge hiirega pöördtabeli juurde. Leidke Baybrooki jaanuari tegelike andmete lahter. Klõpsake sellel lahtril ja vajutage sisestusklahvi. Nagu tavaliselt, ehitab Excel ühe neist tüütutest GETPIVOTDATA funktsioonidest, mida ei saa kopeerida.
Kuid täna uurime GETPIVOTDATA süntaksit.
Esimene argument allpool on numbriline väli "Müük". Teine argument on lahter, kus pöördtabel asub. Ülejäänud argumentide paarid on välja nimi ja väärtus. Kas näete, mida tegi automaatselt loodud valem? Poe nimeks kodeeriti see kõvasti "Baybrook". Sellepärast ei saa te neid automaatselt loodud GETPIVOTDATA valemeid kopeerida. Nad kodeerivad nimed tegelikult valemiteks. Kuigi te ei saa neid valemeid kopeerida, saate neid muuta. Sel juhul oleks parem, kui muudaksite valemit, et osutada lahtrile $ D6.
Siin on valem pärast selle muutmist. Kadunud on "Baybrook", "Jan" ja "Actual". Selle asemel osutate osadele $ D6, E $ 3, E $ 4.
Kopeerige see valem ja valige kõigis teistes arvulistes lahtrites Kleebi spetsiaalne, valemid.
Siin on teie iga-aastane töövoog:
- Ehitage kole pöördtabel, mida keegi kunagi ei näe.
- Seadistage aruande tööleht.
Igal kuul peate:
- Kleepige uued tegelikud andmed alla.
- Värskendage koledat pöördtabelit.
-
Uue kuu kajastamiseks muutke aruandelehe lahtrit P1. Kõik numbrid uuenevad.
Muutke lahtrit P1
Peate tunnistama, et lihtsa aruande kasutamine, mis tõmbab pöördtabelist numbreid, annab teile mõlemast maailmast parima. Aruannet saate vabalt vormindada viisil, mis ei võimalda pöördtabelit vormindada. Tühjad read on korras. Esimeses ja viimases reas võivad olla valuutasümbolid, kuid mitte nende vahel. Saate topeltjoonte ka üldsummade all.
Täname @iTrainerMX selle funktsiooni soovitamise eest.
Vaata videot
- GetPivotData juhtub siis, kui valem osutab pöördtabeli sisemusse
- Kuigi algne valem on õige, ei saa te valemit kopeerida
- Enamik inimesi vihkab getpivotdata ja soovib seda ära hoida
- 1. meetod: koostage valem ilma hiire või nooleklahvita
- 2. meetod: lülitage GetPivotData jäädavalt välja, kasutades valikute kõrval olevat rippmenüüd
- GetPivotData on aga kasutatav
- Teie juht soovib aruannet tegelike andmete kohta eelmiste kuude kohta ja tulevase eelarve kohta
- Tavalise töövoo korral oleks teil vaja luua liigendtabel, teisendada väärtusteks, kustutada veerud
- Vahesummade eemaldamine, et välistada jaanuari tegelik + plaan väljaväljaseadete abil
- Selle asemel looge "liiga palju" andmeid sisaldav pöördtabel
- Kasutage kenasti vormindatud aruande töölehte
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Ehitage töölehe esimesest andmerakust hiirega valem
- Luba GetPivotData-l toimuda
- Uurige GetPivotData süntaksit (tagastatav väli, pöördeteabe asukoht, paarid)
- Muutke kõvakodeeritud väärtus lahtrile osutamiseks
- Kolm korda klahvi F4 vajutades lukustatakse ainult veerg
- Kaks korda klahvi F4 vajutades lukustatakse ainult rida
- Kleepige erivalemid
- Järgmise kuu töövoog: lisage andmeid, värskendage pöördtabelit, muutke kuupäeva
- Eriti ettevaatlik, et uusi poode jälgida
Video ärakiri
Õppige Exceli podcast'ist, episood 2013 - GetPivotData ei pruugi olla täiesti kuri!
Podcastin kogu seda raamatut, tellimiseks klõpsake paremas ülanurgas oleval tähel „i”.
Hästi, tagasi 1998. aasta osas rääkisin sellest GetPivotData probleemist lühidalt. Kui arvutame dispersiooni% ja oleme väljaspool Pivoti tabelit, mis osutab seespool, ja ma kasutan hiirt või nooleklahvi, siis 2019 / 2018-1. See vastus, mille me siia saame, on jaanuari kohta õige, kuid kui me topeltklõpsame selle kopeerimiseks, siis valemit ei kopeerita, saame jaanuari vastuse lõpuni alla. Ja kui me seda vaatame, saame GetPivotData, ma ei kirjutanud GetPivotData, ma lihtsalt osutasin nendele lahtritele ja see hakkas Excel 2002-s juhtuma ilma igasuguse hoiatuseta. Ja sel hetkel ütlesin, et selle vältimiseks tuleb sisestada valem C5 / B5-1 ja saate valemi, mille saate kopeerida. Või kui sa lihtsalt vihkad GetPivotData-d, siis kui see on "täiesti kuri", minge vahekaardile Analüüs, ära 't muide avage nupp Valikud. Minge tagasi Pivoti tabelisse, minge vahekaardile Analüüs, avage rippmenüü suvandi Valikud kõrval, tühjendage see ruut, see on globaalne seade. Kui olete selle välja lülitanud, on see igavesti välja lülitatud, olgu.
Enamasti on mul küsimused: "Kuidas GetPivotData välja lülitada?" aga iga natukese aja tagant saan ma endale kellegi, kes armastab GetPivotDatat. Ja ma sõin Rob Collie'ga lõunatamas, kui ta veel Microsoftis oli, ja ta ütles: "Noh, meie sisekliendid armastavad GetPivotDatat." Ma ütlesin “Mis? Ei, kõik vihkavad GetPivotDatat! " Robi sõnul on teil õigus, väljaspool Microsofti, nad vihkavad GetPivotDatat. Ma räägin Microsofti raamatupidajatest ja hiljem kohtasin nüüd ühte Exceli meeskonnas töötavat Carlost ja Carlos oli üks seda meetodit kasutavatest raamatupidajatest.
Hästi, nii et siin peame tegema. Meil on oma aruanne, siin on andmekogum, et iga kuu jaoks on meil iga poe plaan ja seejärel kogume tegelikke andmeid. Hästi, nii et meil on jaanuari kuni detsembri tegelikud andmed, kuid tegelikud andmed on vaid mõned kuud, möödunud kuud. Ja mida meie juht soovib, et me teeksime, on koostada aruanne, mille vasakusse serva jäävad poed, loomulikult muudavad elu keerulisemaks ainult Texase kauplused. Ja siis on meil mitu kuud ja kui meil on selle kuu tegelik arv, siis näitame tegelikku, seega jaanuari tegelikku, veebruari tegelikku, märtsi tegelikku, aprilli tegelikku. Kuid siis nende kuude jaoks, kus meil tegelikke andmeid pole, läheme üle ja kuvame eelarve, seega eelarve läbi detsembri ja siis kokku, kõik korras. Noh, kui proovite seda Pivoti tabelit luua, siis jahsee ei tööta.
Nii et sisestage PivotTable-liigendtabel, uus tööleht, panete poodi vasakule vasakule, ilusale küljele, asetage kuud ülemisse ossa, tippige tippu tipp, panete müügi siia, olgu. Nii et siin on see, mida saame, millega peame hakkama töötama, nii et meil on jaanuari tegelik, jaanuari plaan ja seejärel täiesti kasutu jaanuari tegelik pluss plaan. Keegi seda kunagi ei kasuta, kuid ma saan neist hallidest veergudest lahti, see on piisavalt lihtne, mõned siin sellesse lahtrisse, minge väljade seadetesse ja muutke vahesummad olekuks Puudub. Kuid mul pole mingit võimalust jaanuariplaani eemaldada, mis ei eemaldaks ka aprilli mai juuni juuliplaani, pole midagi, sellest pole võimalik lahti saada. Nii et iga kuu olen jäänud kogu Pivoti tabeli valimise juurde, liikudes jaotisse Kopeeri ja seejärel Kleebi, Kleebi väärtused. See pole enam Pivoti tabel,ja siis hakkan käsitsi kustutama veerge, mida aruandes ei kuvata.
Hea küll, see on tavaline meetod, kuid Microsofti raamatupidajad on jaanuaris lisanud täiendava sammu, see võtab 15 minutit ja see samm võimaldab sellel Pivoti tabelil igavesti elada, eks? Ma nimetan seda maailma kõige koledamaks Pivoti tabeliks ja Microsofti raamatupidajad nõustuvad, et see on maailma kõige koledam Pivoti tabel, kuid keegi ei näe seda aruannet kunagi peale nende. Mida nad teevad, kas nad tulevad siia uue lehe juurde ja koostavad aruande, mida nende juhataja soovib. Hästi, nii et siin on vasakpoolsel küljel asuvad poed, ma rühmitasin selle isegi Houstoni, Dallase ja teiste hulka, see on kenasti vormindatud aruanne. Olen kokku toonud, näete, et kui saame mõned numbrid sisse, on esimeses reas valuuta, kuid mitte järgnevad, tühjad read. Oeh, tühjad read Pivoti tabelis.Ja siin on üks väike loogika, kuhu saan panna lahtrisse P1 lõppkuupäeva, ja siis on mul siin valem, mis analüüsib, et KUI lõppkuupäeva kuu on> see veerg ja seejärel panen sõna Tegelik, muidu pane sõna Plaan, olgu. Nii et mul jääb üle vaid muuta see kuupäev läbi ja siis läheb sõna Tegelik üle plaanimiseks, Alright.
Nüüd, mida me teeme, lubame meil olla GetPivotData'd, eks? Ma pole kindel, et see on tegusõna, kuid lubame Microsoftil saada GetPivotData. Nii et hakkan valemit ehitama tähega =, haaran hiirest ja lähen otsima jaanuari tegelikku Baybrookit! Nii et lähen tagasi maailma kõige koledamasse Pivoti tabelisse, leian Baybrooki, leian jaanuari, leian tegeliku ja klõpsan Enter ja lasen neil seda mulle teha, olgu, seal me läheme, meil on nüüd valem GetPivotData. Ma mäletan seda päeva, kui ma seda tegin, see oli umbes nii, nagu teate, pärast seda, kui Rob mulle selgitas, mida nad tegid, ja ma läksin tagasi ja proovisin seda. Nüüd olen kogu elu järsku GetPivotData'st lahti saanud, pole tegelikult kunagi GetPivotDatat omaks võtnud. Mis see on, on esimene asi, mida me otsime, seal onsa väli nimega Müük, siin algab Pivoti tabel ja see võib olla Pivoti tabeli mis tahes lahter, nad kasutavad vasakut ülemist kätt.
Hea küll, see on välja nimi "Store" ja siis on nad kõvakodeeritud "Baybrook", see on välja nimi "Month", nad on hardcodeeritud "jaanuar", see on välja nimi "Type" ja nad " ve-kodeeritud „Tegelik”. Sellepärast ei saa te seda kopeerida, sest nad on väärtused kõvasti kodeerinud. Kuid Microsofti raamatupidajad, Carlos ja tema kaastöötajad mõistavad: „Ohoo, oota hetk, meil on siin sõna Baybrook, meil siin jaanuar, meil siin Actual. Peame seda valemit lihtsalt muutma, et viidata aruande tegelikele lahtritele, selle asemel et neid kodeerida. " Olgu, nii et nad nimetavad seda parameetrite muutmiseks GetPivotData.
Eemaldage sõna Baybrook, tulge siia ja klõpsake lahtril D6. Nüüd pean selle lukustama veergu, nii, nii et vajutan klahvi F4 kolm korda, saan ühe dollari enne tähte D, olgu. Jaanuari kuu puhul eemaldan kõvakoodiga jaanuari, klõpsan lahtril E3, vajutan kaks korda klahvi F4, et lukustada see reale, E $ 3. Sisestage tegelik, eemaldage sõna Tegelik, klõpsake E4, uuesti kaks korda F4, olgu, saan valemi, mis tõmbab need andmed nüüd tagasi. Kopeerin selle ja seejärel Kleebi spetsiaalselt, valige Vormingud, alt = "" ESF, vaadake, et F on seal allajoonitud, ESF Enter ja siis nüüd, ma olen seda teinud, kordan lihtsalt F4-ga, F4 on ümbertegemine ja F4. Hästi, nii et nüüd on meil kena välimusega aruanne, see on tühi, sellel on vorming, iga jaotise all on üks raamatupidamisjoon,kõige alumises osas on sellel topeltarvestusjoon.
Eks, seda kraami ei saa kunagi Pivoti tabelisse, see on võimatu, kuid see aruanne juhitakse Pivoti tabelist. Niisiis, mida me teeme, kui saame mai tegelikud andmed, tuleme siia tagasi, kleepige need sisse, minge värskendama maailma kõige koledamat Pivoti tabelit ja siis siin aruandes muutke lihtsalt läbimise kuupäev 4/30 asemel 5/31. Ja mis see teeb, on see, et see valem lülitub sõnast Plaan tegelikuks, mis läheb ja tõmbab aruande tegelikud plaani asemel. Siin on see asi - see on suurepärane, eks? Ma näen, kus ma seda palju teeksin, kui töötaksin ikka veel raamatupidamises.
Asi, mille suhtes peate olema väga ettevaatlik, on see, et kui nad ehitavad uue poe, peate teadma, et lisate selle käsitsi, eks, andmed kuvatakse Pivoti tabelis, kuid lisate need käsitsi. Nüüd on see üks kõigi poodide alamhulk, kui ta esitaks aruandeid kõigist poodidest, oleks mul ilmselt siin, väljaspool trükivalikut, midagi, mis tõmbaks Pivoti tabelist kokku. Ja siis ma teaksin, et kui see summa ei ühti Pivoti tabeli kogusummaga, siis on midagi valesti ja kui mul on siin funktsioon IF, öeldes: "Hei, teate, on lisatud uusi andmeid, olge väga ettevaatlik. ” Neil on mingisugune mehhanism, mis tuvastab uute andmete olemasolu. Aga saan aru, see on lahe kasutus. Niisiis, kuigi enamasti ajab GetPivotData meid lihtsalt hulluks, võib seda tegelikult kasutada. Hästi,nii et see on vihje nr 21 raamatu 40-st, ostke raamat kohe, tellige veebis, klõpsake paremas ülanurgas oleval tähel “i”.
Pikk-pikk kokkuvõte täna, olgu: GetPivotData juhtub siis, kui valem osutab Pivoti tabeli sisemusse, valem Pivoti tabelist väljapoole. Kuigi algne valem on õige, ei kopeerita seda. Enamik inimesi vihkab GetPivotDatat ja soovib seda takistada. Nii saate valemi koostada ilma hiire või nooleklahvideta, lihtsalt sisestage valem või lülitage GetPivotData jäädavalt välja, ah, kuid on ka kasutust, olgu. Seega peame koostama aruande koos eelmise kuu tegelike andmetega, tulevase eelarvega. Tavaline töövoog, looge Pivoti tabel, teisendage väärtuseks, kustutage veerud. Vahesummade eemaldamiseks võite kasutada väli seadeid, vabanedes sellest jaanuari tegelikust plussplaanist. Selle asemel loome lihtsalt maailma kõige koledama Pivoti tabeli, kus on liiga palju andmeid.
Koostage kenasti vormindatud, lihtsalt vana aruande tööleht, millel võib olla natuke loogikat, et muuta sõna Tegelik plaaniks. Ja siis esimesest aruande lahtrist tippige esimene koht, kus arvud selles aruandes asuvad, tippige =, minge osutage tabelile Pivot ja lubage GetPivotData toimuda. Uurime GetPivotData süntaksit, seega on see tagastatav väli, Müük, kus elab tabel Pivot, ja seejärel paar kriteeriume, välja nimi ja väärtus. Eemaldame kõvakodeeritud väärtuse ja osutame lahtrile, kui vajutate kolm korda klahvi F4, lukustatakse ainult veerg, vajutades klahvi F4 kaks korda, lukustatakse ainult rida, kopeerime selle valemi, Kleepige erivalemid. Viskasin sinna ühe lisanõu, et F4 on ümber tehtud, nii et pidin minema ainult üks kord Kleebi spetsiaalse dialoogi ja seejärel kasutasin järgmiste Paste erivalemite jaoks lihtsalt F4. Järgmisel kuul lisage andmed,värskendage Pivoti tabelit, muutke läbimise kuupäeva. Veenduge, et nad ei ehitanud ühtegi uut poodi, teate, et neil on mingi mehhanism, kas käsiraamat või kontrollvalem, vaadake seda. Tänu Twitteris asuvale iTrainerMX-ile, kes soovitas GetPivotData, ka Microsofti Carlos ja Rob, Rob nüüd Power Pivot Pro'st. Carlos selle kasutamise eest ja Rob selle eest, et ta ütles mulle, et Carlos kasutab seda, kohtusin hiljem Carlosega ja ta kinnitas jah, ta oli üks raamatupidajatest, kes kasutas seda Microsoftis kogu aeg, olgu, seal sa lähed.ja Rob selle eest, et ta mulle ütles, et Carlos seda kasutab, kohtusin hiljem Carlosega ja ta kinnitas jah, ta oli üks raamatupidajatest, kes seda Microsoftis kogu aeg kasutas, olgu, seal sa lähed.ja Rob selle eest, et ta mulle ütles, et Carlos seda kasutab, kohtusin hiljem Carlosega ja ta kinnitas jah, ta oli üks raamatupidajatest, kes seda Microsoftis kogu aeg kasutas, olgu, seal sa lähed.
Hei hei, ma tahan tänada teid peatumast, näeme järgmine kord järgmise netisaate jaoks!
Laadige fail alla
Laadige näidisfail alla siit: Podcast2013.xlsx
