Vai jūs ienīstat Excel GETPIVOTDATA funkciju? Kāpēc tas parādās? Kā jūs to varat novērst? Vai GETPIVOTDATA ir labi lietojams?
Lielākā daļa cilvēku vispirms saskaras ar GETPIVOTDATA, mēģinot izveidot formulu ārpus rakurstabulas, kurā rakurstabulā tiek izmantoti skaitļi. Piemēram, šī dispersijas procentuālā daļa netiks kopēta uz citiem mēnešiem, jo Excel ievietos funkcijas GETPIVOTDATA.
Programma Excel ievieto GETPIVOTDATA ikreiz, kad izmantojat peli vai bulttaustiņus, lai norādītu uz šūnu šarnīra tabulas iekšpusē, vienlaikus veidojot formulu ārpus rakurstabulas.
Starp citu, ja nevēlaties, lai tiktu parādīta funkcija GETPIVOTDATA, vienkārši ierakstiet formulu, piemēram, = D5 / C5-1, nelietojot peles vai bulttaustiņus, lai norādītu uz šūnām. Šī formula tiek kopēta bez problēmām.
Šeit ir datu kopa, kurā vienā veikalā mēnesī ir viens plāna numurs. Ir arī faktiskais pārdošanas apjoms mēnesī vienā veikalā par pabeigtajiem mēnešiem. Jūsu mērķis ir izveidot pārskatu, kurā parādīti faktiskie dati par pabeigtajiem mēnešiem un plāns nākamajiem mēnešiem.
Izveidojiet pagrieziena tabulu ar Store in ROWS. Kolonnās ievietojiet mēnesi un veidu. Jūs saņemat zemāk redzamo pārskatu ar janvāra faktisko, janvāra plānu un pilnīgi bezjēdzīgo janvāra faktisko + plānu.
Ja atlasāt mēneša šūnu un dodaties uz lauka iestatījumiem, varat mainīt starpsummas uz Neviena.
Tas noņem bezjēdzīgo Actual + plānu. Bet jums joprojām ir jāatbrīvojas no plāna slejām no janvāra līdz aprīlim. Nav labi, kā to izdarīt šarnīra tabulas iekšpusē.
Tātad jūsu ikmēneša darbplūsma kļūst:
- Pievienojiet datu kopai faktiskos datus par jauno mēnesi.
- Veidojiet jaunu šarnīra galdu no jauna.
- Kopējiet rakurstabulu un ielīmējiet kā vērtības, lai tā vairs nebūtu rakurstabula.
- Izdzēsiet nevajadzīgās kolonnas.
Ir labāks ceļš. Šis ļoti mazais attēls parāda jaunu Excel darblapu, kas pievienota darbgrāmatai. Tas viss ir tikai taisns Excel, nav rakurstabulu. Vienīgais burvju gabals ir IF funkcija 4. rindā, kas pārslēdzas no Faktiskā uz Plānu, pamatojoties uz datumu šūnā P1.
Pirmā šūna, kas jāaizpilda, ir janvāris, Actuals for Baybrook. Noklikšķiniet šajā šūnā un ierakstiet vienādības zīmi. Izmantojot peli, dodieties atpakaļ uz rakurstabulu. Atrodiet Baybrook janvāra faktisko šūnu. Noklikšķiniet uz šīs šūnas un nospiediet taustiņu Enter. Kā parasti, Excel izveido vienu no šīm kaitinošajām GETPIVOTDATA funkcijām, kuras nevar kopēt.
Bet šodien izpētīsim GETPIVOTDATA sintaksi.
Pirmais arguments zemāk ir skaitliskais lauks "Pārdošana". Otrais arguments ir šūna, kurā atrodas rakurstabula. Pārējie argumentu pāri ir lauka nosaukums un vērtība. Vai redzat, ko darīja automātiski izveidotā formula? Tā kā veikala nosaukums bija grūti kodēts "Baybrook". Tāpēc jūs nevarat kopēt šīs automātiski ģenerētās GETPIVOTDATA formulas. Viņi faktiski cieto kodu nosaukumus formulās. Pat ja jūs nevarat kopēt šīs formulas, varat tās rediģēt. Šajā gadījumā būtu labāk, ja jūs rediģētu formulu, lai norādītu uz šūnu $ D6.
Šī ir formula pēc tās rediģēšanas. Aizgājuši "Baybrook", "Jan" un "Actual". Tā vietā jūs norādāt uz $ D6, E $ 3, E $ 4.
Nokopējiet šo formulu un pēc tam visās pārējās ciparu šūnās izvēlieties Ielīmēt speciāli, formulas.
Tagad šeit ir jūsu ikgadējā darbplūsma:
- Veidojiet neglītu pagrieziena galdu, kuru neviens nekad neredzēs.
- Iestatiet pārskata darblapu.
Katru mēnesi jums:
- Zem datiem ielīmējiet jaunus faktiskos datus.
- Atsvaidziniet neglīto rakurstabulu.
-
Pārskata lapā mainiet šūnu P1, lai atspoguļotu jauno mēnesi. Visi numuri tiek atjaunināti.
Mainīt šūnu P1
Jums jāatzīst, ka vienkārša pārskata izmantošana, kas izvelk skaitļus no rakurstabulas, sniedz labāko no abām pasaulēm. Jūs varat brīvi formatēt pārskatu tādā veidā, kā nevarat formatēt rakurstabulu. Tukšās rindas ir kārtībā. Pirmajā un pēdējā rindā var būt valūtas simboli, bet ne starp tām. Arī jūs saņemat dubultu pasvītrojumu zem kopējām summām.
Paldies @iTrainerMX par šīs funkcijas ieteikšanu.
Skatīties video
- GetPivotData notiek, kad formula norāda rakurstabulas iekšpusē
- Kamēr sākotnējā formula ir pareiza, formulu nevar kopēt
- Lielākā daļa cilvēku ienīst getpivotdata un vēlas to novērst
- 1. metode: izveidojiet formulu bez peles vai bulttaustiņiem
- 2. metode: izslēdziet GetPivotData neatgriezeniski, izmantojot nolaižamo izvēlni blakus opcijām
- Bet ir GetPivotData lietojums
- Jūs, vadītājs, vēlaties pārskatu ar Faktiskie dati par iepriekšējiem mēnešiem un budžetu nākotnei
- Parastajā darbplūsmā būtu jāizveido rakurstabula, jāpārvērš par vērtībām, jāizdzēš kolonnas
- Noņemot starpsummas, lai novērstu janvāra faktisko + plānu, izmantojot lauka iestatījumus
- Tā vietā izveidojiet rakurstabulu ar “pārāk daudz” datiem
- Izmantojiet labi formatētu pārskata darblapu
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- No pirmās darblapas datu šūnas izveidojiet formulu ar peli
- Ļaujiet GetPivotData notikt
- Pārbaudiet GetPivotData sintaksi (atgriežamais lauks, rakursa atrašanās vieta, pāri)
- Mainiet grūti kodēto vērtību, lai norādītu uz šūnu
- Trīs reizes nospiežot F4, tiek bloķēta tikai kolonna
- Divreiz nospiežot F4, tiek bloķēta tikai rinda
- Īpašu formulu ielīmēšana
- Darbplūsma nākamajā mēnesī: pievienojiet datus, atsvaidziniet rakurstabulu, mainiet datumu
- Īpaši uzmanīgi uzmanieties no jauniem veikaliem
Video atšifrējums
Uzziniet Excel no podcast, 2013. gada epizodes - GetPivotData var nebūt pilnīgi ļauns!
Apraidīšu visu šo grāmatu, augšējā labajā stūrī noklikšķiniet uz “i”, lai abonētu.
Labi, 1998. gada epizodē es īsi runāju par šo GetPivotData problēmu. Ja mēs aprēķinām% dispersiju un mēs atrodamies ārpus rakurstabulas, kas norāda uz iekšpusi, un es izmantoju peli vai bulttaustiņu, tāpēc 2019 / 2018-1. Šī atbilde, ko mēs šeit saņemsim, ir pareiza janvārim, taču, veicot dubultklikšķi, lai to nokopētu, formula netiek kopēta, mēs saņemam janvāra atbildi līdz galam. Kad mēs to aplūkojam, mēs saņemam GetPivotData, es nerakstīju GetPivotData, es tikai norādīju uz šīm šūnām, un tas sāka notikt jau Excel 2002 bez jebkāda brīdinājuma. Un tajā brīdī es teicu, ka veids, kā no tā izvairīties, ir ievadīt formulu C5 / B5-1, un jūs saņemsiet formulu, kuru varat kopēt. Vai arī, ja jūs vienkārši ienīst GetPivotData, ja tas ir “pilnīgi ļauns”, dodieties uz cilni Analizēt, don 't, starp citu, atveriet pogu Opcijas. Atgriezieties tabulā Pivot, dodieties uz cilni Analizēt, atveriet nolaižamo izvēlni blakus Opcijas, noņemiet atzīmi no šīs izvēles rūtiņas, tas ir globāls iestatījums. Tiklīdz to izslēgsit, tas būs izslēgts uz visiem laikiem, labi.
Lielākoties man rodas jautājumi: "Kā izslēgt programmu GetPivotData?" bet ik pa laikam es dabūšu kādu, kurš mīl GetPivotData. Un es pusdienoju ar Robu Koliju, kad viņš vēl bija Microsoft, un viņš teica: "Nu, mūsu iekšējie klienti mīl GetPivotData." Es teicu “Ko? Nē, visi ienīst GetPivotData! ” Robs saka: "Jums taisnība, neatkarīgi no Microsoft, viņi absolūti ienīst GetPivotData." Es runāju par grāmatvežiem Microsoft iekšienē, un vēlāk es satiku vienu, kurš tagad strādā Excel komandā, Karlosu, un Karloss bija viens no grāmatvežiem, kurš izmanto šo metodi.
Labi, tāpēc mums tas ir jādara. Mums ir mūsu pārskats, šeit ir datu kopa, kurā katram mēnesim mums ir plāns katram veikalam, un tad apakšā mēs uzkrājam faktiskos datus. Labi, tāpēc mums ir faktiskie dati par janvāri līdz decembrim, bet faktiskie dati ir tikai dažus mēnešus, pagājušos mēnešus. Un tas, ko mūsu menedžeris vēlas, lai mēs darām, ir izveidot ziņojumu ar veikaliem kreisajā pusē, protams, tikai Teksasas veikali, lai padarītu dzīvi grūtāku. Pēc tam mums ir mēneši, un, ja mums ir faktiskais mēnesis, mēs parādām faktisko, tātad faktisko janvāri, faktisko februāri, faktisko martu, faktisko aprīli. Bet pēc tam mēnešos, kad mums nav faktisko datu, mēs pārslēdzamies un parādām budžetu, tāpēc budžets jāizdara līdz decembrim, un pēc tam viss ir kārtībā, labi. Nu, mēģinot izveidot šo rakurstabulu, jā,tas nedarbojas.
Ievietojiet rakurstabulu, Jauna darblapa, jūs novietojat veikalu kreisajā pusē, skaistajā pusē, augšpusē ievietojiet Mēnešus, augšpusē - veidu Type, Labi - šeit. Tātad, lūk, ko mēs iegūstam, ar kuru mums jāsāk strādāt, tāpēc mums ir faktiskais janvāra plāns, janvāra plāns un pēc tam pilnīgi bezjēdzīgais janvāra faktiskais pluss. Neviens to nekad neizmantos, bet es varu atbrīvoties no šīm pelēkajām kolonnām, tas ir pietiekami vienkārši, daži šeit uz šo šūnu, dodieties uz lauka iestatījumiem un nomainiet Starpsummas uz Nav. Bet man pilnīgi nav iespējas noņemt janvāra plānu, kas arī nenoņems aprīļa maija jūnija jūlija plānu, labi, nav iespējas no tā atbrīvoties. Tāpēc katru mēnesi es esmu iestrēdzis, izvēloties visu rakurstabulu, dodoties uz Kopēt un pēc tam Ielīmēt, Ielīmēt vērtības. Tā vairs nav rakurstabula,un pēc tam es sāku manuāli dzēst kolonnas, kas neparādās pārskatā.
Labi, tā ir parasta metode, taču Microsoft grāmatveži janvārī ir pievienojuši papildu soli, tas prasa 15 minūtes, un šis solis ļauj šai rakurstabulai dzīvot mūžīgi, vai ne? Es to saucu par pasaules neglītāko rakurstabulu, un Microsoft grāmatveži atzīst, ka šī ir pasaulē neglītākā rakurstabula, taču šo ziņojumu neviens nekad neredzēs, izņemot viņus. Ko viņi dara, vai viņi šeit nonāk pie jaunas lapas un izveido ziņojumu, kuru vēlas viņu menedžeris. Labi, tāpēc šeit ir veikali kreisajā pusē, es to pat sagrupēju Hjūstonā, Dalasā un citur, tas ir labi formatēts ziņojums. Es esmu izcēlis kopsummas. Jūs redzēsiet, ka tad, kad mēs saņemsim dažus skaitļus, pirmajā rindā ir valūta, bet ne šīs nākamās, tukšās rindas. Ooh, tukšas rindas rakurstabulā.Un šeit ir viena niecīga loģika, kur es varu ievietot beigu datumu šūnā P1, un tad man ir formula, kas analizē, ka, ja beigu datuma mēnesis ir> šī kolonna, un pēc tam ievieto vārdu Faktiskais, citādi ieliec vārdu Plāns, labi. Tāpēc man viss, kas man jādara, ir jāmaina šis datums, un pēc tam vārds Faktiskais pāriet, lai plānotu, Labi.
Lūk, ko mēs darām, mēs atļausimies būt GetPivotData'd, vai ne? Es neesmu pārliecināts, ka tas ir darbības vārds, taču mēs ļausim Microsoft iegūt GetPivotData. Tāpēc es sāku veidot formulu ar =, paķeru peli un dodos meklēt faktisko Baybrook janvāri! Tāpēc es atgriežos pasaules neglītākajā rakurstabulā, atrodu Bībroku, atrodu janvāri, atrodu faktisko, un noklikšķinu uz Enter un ļaujiet viņiem to izdarīt man, labi, tur mēs ejam, tagad mums ir formula GetPivotData. Es atceros dienu, kad es to darīju, tas bija kā, jūs zināt, pēc tam, kad Robs man paskaidroja, ko viņi dara, un es atgriezos un izmēģināju to. Tagad pēkšņi visu savu dzīvi esmu atbrīvojusies no GetPivotData, es nekad nekad neesmu pieņēmusi GetPivotData. Kas tas ir, vai pirmais ir tas, ko mēs meklējam, turSa lauks ar nosaukumu Sales, šeit sākas rakurstabula, un tā var būt jebkura šūna rakurstabulā, viņi izmanto augšējo kreiso roku.
Labi, tas ir lauka nosaukums “Veikals”, un tad viņi ir cietā kodā ierakstījuši “Baybrook”, tas ir lauka nosaukums “Mēnesis”, viņi ir cieti kodējuši “Janvāris”, šis ir lauka nosaukums “Tips” un viņi Ve Hardcoded “Faktiskais”. Tieši tāpēc jūs to nevarat kopēt, jo viņi ir grūti kodējuši vērtības. Bet Microsoft grāmatveži, Karloss un viņa līdzstrādnieki saprot: “Ai, pagaidiet sekundi, mums šeit ir vārds Baybrook, mums šeit ir janvāris, šeit ir Actual. Mums vienkārši jāmaina šī formula, lai norādītu uz faktiskajām ziņojuma šūnām, nevis būtu kodēta. ” Labi, tāpēc viņi šo parametru sauc par GetPivotData.
Noņemiet vārdu Baybrook, nāciet šeit un noklikšķiniet uz šūnas D6. Tagad man tas ir jānoslēdz kolonnā, labi, tāpēc es nospiežu F4 taustiņu 3 reizes, iegūstu vienu $ pirms D, labi. Janvāra mēnesim es noņemu cieto kodēto janvāri, es noklikšķinu uz šūnas E3, es divreiz nospiedīšu F4, lai to bloķētu līdz rindai, E $ 3. Ierakstiet Faktiskais, noņemiet vārdu Faktiskais, noklikšķiniet uz E4, vēlreiz F4 divreiz, labi, un es saņemu formulu, kas tagad atvelk šos datus. Es to nokopēšu un pēc tam Ielīmēt īpašo, izvēlos Formāti, alt = "" ESF, skatiet, ka F ir tur pasvītrots, ESF Enter, un tagad, kad es to izdarīju, es vienkārši atkārtošu ar F4 F4 ir pārtaisījums, un F4. Labi, tāpēc tagad mums ir jauka izskata ziņojums, tajā ir tukšas vietas, formatējums, katrā sadaļā ir vienots uzskaites pasvītrojums,pašā apakšā tam ir divkāršs uzskaites pasvītrojums.
Pareizi, jūs nekad neiegūstat šo saturu rakurstabulā, tas nav iespējams, taču šis pārskats tiek virzīts no rakurstabulas. Tātad, ko mēs darām, kad mēs saņemam maija faktiskos datus, atgriezīsimies šeit, ielīmējiet tos, ejiet atsvaidzināt pasaules neglītāko rakurstabulu un tad šeit, pārskatā, vienkārši mainiet caurbraukšanas datumu no 4/30 uz 5/31. Tas, kas to dara, liek šai formulai pāriet no vārda Plāns uz Faktiskais, kas iet un izvelk faktisko no pārskata, nevis plāna, labi. Tagad, lūk, šī lieta ir lieliska, vai ne? Es redzu, kur es to darītu ļoti daudz, ja joprojām, jūs zināt, strādātu grāmatvedībā.
Lieta, pret kuru jums jābūt ļoti uzmanīgam, ir tas, ka viņi izveido jaunu veikalu, jums jāzina, lai to pievienotu manuāli, pareizi, dati tiks parādīti rakurstabulā, bet jūs tos pievienotu manuāli. Tagad šis ir visu veikalu apakškopa, ja tas ziņotu par visiem veikaliem, es, iespējams, šeit, ārpus drukas diapazona, būtu kaut kas tāds, kas izvilktu kopsummu no rakurstabulas. Un tad es zinātu, ja šī kopsumma neatbilst tabulas Pivot kopsummai, ka kaut kas nav kārtībā, un šeit ir IF funkcija, sakot: “Hei, jūs zināt, ir pievienoti jauni dati, esiet ļoti uzmanīgs. ” Viņiem ir sava veida mehānisms, lai noteiktu, ka tur ir jauni dati. Bet es to saprotu, tas ir forši lietojams. Tātad, lai gan lielākoties GetPivotData mūs vienkārši tracina, patiesībā to var izmantot. Labi,Tātad tas ir grāmatas 21. padoms no 40, nopērciet grāmatu tūlīt, pasūtiet tiešsaistē, augšējā labajā stūrī noklikšķiniet uz šī “i”.
Ilgs, ilgs atkārtojums šodien, labi: GetPivotData notiek, kad formula norāda rakurstabulas iekšpusē, formula ārpus rakurstabulas - iekšpusē. Lai gan sākotnējā formula ir pareiza, tā netiks kopēta. Lielākā daļa cilvēku ienīst GetPivotData un vēlas to novērst. Tātad jūs varat izveidot formulu bez peles vai bulttaustiņiem, vienkārši ierakstiet formulu vai neatgriezeniski izslēdziet GetPivotData, ah, bet ir lietojums, labi. Tāpēc mums ir jāizveido pārskats ar faktiskajiem datiem par pagājušo mēnesi, nākotnes budžetu. Normāla darbplūsma, izveidojiet rakurstabulu, konvertējiet uz vērtībām, dzēsiet kolonnas. Ir veids, kā noņemt starpsummas, izmantojot lauka iestatījumus, atbrīvojoties no šī janvāra faktiskā plus plāna. Tā vietā mēs vienkārši izveidosim pasaulē neglītāko rakurstabulu ar pārāk daudz datu.
Izveidojiet labi formatētu, vienkārši vecu pārskata darblapu ar varbūt mazliet loģiku, lai vārdu Faktiskais nomainītu uz Plāns. Pēc tam no pirmās atskaites šūnas, pirmajā vietā, kur skaitļi būs šajā pārskatā, ierakstiet =, dodieties uz tabulu Pivot un ļaujiet notikt GetPivotData. Mēs pārbaudām GetPivotData sintaksi, tāpēc tas ir atgriežamais lauks, Sales, kur dzīvo tabula Pivot, un pēc tam kritēriju pāri, lauka nosaukums un vērtība. Mēs noņemsim cieto kodu un norādīsim uz šūnu, 3 reizes nospiežot F4, tiek bloķēta tikai kolonna, 2 reizes nospiežot F4, tiek bloķēta tikai rinda, kopējiet šo formulu, Paste Special Formulas. Es tur iemetu papildu padomu, ka F4 ir pārtaisījums, tāpēc man tikai vienu reizi bija jāiet uz Īpašās ielīmēšanas dialoglodziņu un pēc tam nākamajās Īpašo formulu ielīmēšanas tikko izmantoja F4. Nākamajā mēnesī pievienojiet datus,atsvaidziniet rakurstabulu, mainiet beigu datumu. Pārliecinieties, ka viņi nav uzcēluši jaunus veikalus. Ziniet, vai viņiem ir sava veida mehānisms, vai nu manuāls, vai pārbaudes formula, pārbaudiet to. Paldies iTrainerMX vietnē Twitter, kurš ieteica GetPivotData, arī Karlosu un Robu no Microsoft, Robu tagad no Power Pivot Pro. Karloss par to, ka es to izmantoju, un Robs, kurš man teica, ka Karloss to lieto, es vēlāk satiku Karlosu, un viņš apstiprināja, ka jā, viņš bija viens no grāmatvežiem, kurš to visu laiku izmantoja Microsoft vietnē, labi, tur jums iet.un Robu par to, ka viņš man teica, ka Karloss to izmanto, es vēlāk satiku Karlosu, un viņš apstiprināja, ka jā, viņš bija viens no grāmatvežiem, kurš visu laiku to izmantoja Microsoft, labi, tur tev iet.un Robu par to, ka viņš man teica, ka Karloss to izmanto, es vēlāk satiku Karlosu, un viņš apstiprināja, ka jā, viņš bija viens no grāmatvežiem, kurš visu laiku to izmantoja Microsoft, labi, tur tev iet.
Nu, hei, es gribu pateikties, ka apstājies, tiksimies nākamreiz uz citu netcast no!
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2013.xlsx
