Ar nekenčiate „Excel“ funkcijos GETPIVOTDATA? Kodėl jis pasirodo? Kaip galite to išvengti? Ar yra tinkamas GETPIVOTDATA naudojimas?
Daugelis žmonių pirmą kartą susiduria su GETPIVOTDATA, kai bando sukurti formulę už suvestinės lentelės, kurioje naudojami skaičiai suvestinėje lentelėje. Pavyzdžiui, šis dispersijos procentas nebus nukopijuotas į kitus mėnesius dėl to, kad „Excel“ įterpė GETPIVOTDATA funkcijas.
„Excel“ įterpia GETPIVOTDATA bet kuriuo metu, kai naudojate pelės arba rodyklių klavišus, kad nukreiptumėte į langelį suvestinės lentelės viduje, o kurdami formulę už suvestinės lentelės.
Beje, jei nenorite, kad atsirastų funkcija GETPIVOTDATA, tiesiog įveskite formulę, pvz., = D5 / C5-1, nenaudodami pelės ar rodyklių klavišų, norėdami nukreipti į langelius. Ta formulė kopijuojama be problemų.
Čia yra duomenų rinkinys, kuriame yra vienas plano numeris per mėnesį vienoje parduotuvėje. Taip pat yra faktinių pardavimų per mėnesį kiekvienoje parduotuvėje už tuos mėnesius, kurie yra baigti. Jūsų tikslas yra sudaryti ataskaitą, kurioje būtų rodoma baigtų mėnesių faktinė informacija ir planuojamas ateinantis mėnuo.
Sukurkite sukamą lentelę naudodami „Store in ROWS“. Į stulpelius įrašykite mėnesį ir įveskite. Gaunate žemiau pateiktą ataskaitą su sausio faktiniu, sausio mėnesio planu ir visiškai beprasmišku sausio mėnesio faktiniu + planu.
Jei pasirenkate mėnesio langelį ir einate į lauko parametrus, galite pakeisti tarpines sumas į Nėra.
Tai pašalina nenaudingą „Actual +“ planą. Bet jūs vis tiek turite atsikratyti plano stulpelių nuo sausio iki balandžio. Nėra gero būdo tai padaryti suvestinės lentelės viduje.
Taigi jūsų mėnesio darbo eiga tampa:
- Pridėkite naujo mėnesio faktinius duomenis prie duomenų rinkinio.
- Sukurkite naują sukamą lentelę nuo nulio.
- Nukopijuokite suvestinę lentelę ir įklijuokite kaip reikšmes, kad ji nebebūtų suvestinė lentelė.
- Ištrinkite jums nereikalingus stulpelius.
Yra geresnis būdas eiti. Šiame labai mažame paveikslėlyje parodytas naujas „Excel“ darbalapis, pridėtas prie darbaknygės. Visa tai yra tiesiog tiesi „Excel“, nėra jokių sukimosi lentelių. Vienintelis stebuklingas dalykas yra IF funkcija 4 eilutėje, kuri persijungia iš Faktinis į Planą, atsižvelgiant į datą langelyje P1.
Pirmoji langelis, kurį reikia užpildyti, yra sausio mėn. „Actuals for Baybrook“. Spustelėkite tą langelį ir įveskite lygybės ženklą. Naudodamiesi pele, grįžkite į sukimo lentelę. Raskite „Baybrook“ sausio mėnesio faktinės langelį. Spustelėkite tą langelį ir paspauskite Enter. Kaip įprasta, „Excel“ sukuria vieną iš tų erzinančių „GETPIVOTDATA“ funkcijų, kurios negalima nukopijuoti.
Bet šiandien panagrinėkime GETPIVOTDATA sintaksę.
Pirmasis žemiau pateiktas argumentas yra skaitinis laukas „Pardavimai“. Antrasis argumentas yra langelis, kuriame yra sukimosi lentelė. Likusios argumentų poros yra lauko pavadinimas ir vertė. Ar matote, ką padarė automatiškai sukurta formulė? Joje buvo užkoduotas „Baybrook“ kaip parduotuvės pavadinimas. Štai kodėl negalite nukopijuoti šių automatiškai sukurtų GETPIVOTDATA formulių. Jie iš tikrųjų sunkiai koduoja pavadinimus į formules. Nors negalite nukopijuoti šių formulių, galite jas redaguoti. Tokiu atveju būtų geriau, jei redaguotumėte formulę, kad nukreiptumėte į langelį $ D6.
Čia yra formulė, kai ją redaguojate. Dingo „Baybrook“, „Jan“ ir „Actual“. Vietoj to jūs nurodote $ D6, E $ 3, E $ 4.
Nukopijuokite šią formulę ir tada pasirinkite Įklijuoti specialų, Formulės visose kitose skaitinėse ląstelėse.
Dabar pateikiama jūsų metinė darbo eiga:
- Sukurkite negražią sukamą lentelę, kurios niekas niekada nematys.
- Nustatykite ataskaitos darbalapį.
Kiekvieną mėnesį turite:
- Žemiau duomenų įklijuokite naujus faktinius duomenis.
- Atnaujinkite negražią sukimosi lentelę.
-
Ataskaitos lape pakeiskite langelį P1, kad atspindėtų naują mėnesį. Visi numeriai atnaujinami.
Pakeiskite langelį P1
Turite pripažinti, kad naudodami paprastą ataskaitą, kuri ištraukia skaičius iš suvestinės lentelės, galite gauti geriausią iš abiejų pasaulių. Galite laisvai formatuoti ataskaitą taip, kad negalėtumėte suformatuoti suvestinės lentelės. Tuščios eilutės yra tinkamos. Pirmoje ir paskutinėje eilutėse galite turėti valiutos simbolius, bet ne tarp jų. Jūs taip pat gausite dvigubus pabraukimus po didžiosiomis sumomis.
Ačiū @iTrainerMX, kad pasiūlėte šią funkciją.
Žiūrėti video
- „GetPivotData“ įvyksta, kai formulė nukreipta į suvestinės lentelės vidų
- Nors pradinė formulė yra teisinga, negalite jos nukopijuoti
- Daugelis žmonių nekenčia „getpivotdata“ ir nori to išvengti
- 1 metodas: sukurkite formulę be pelės ar rodyklių klavišų
- 2 būdas: visam laikui išjunkite „GetPivotData“ naudodamiesi išskleidžiamuoju meniu šalia parinkčių
- Bet yra „GetPivotData“ naudojimas
- Jūs, vadybininkas, norite ataskaitos apie faktinius duomenis apie praėjusius mėnesius ir ateities biudžetą
- Įprasta darbo eiga būtų sukurti sukimo lentelę, konvertuoti į reikšmes, ištrinti stulpelius
- Pašalinant tarpines sumas, kad būtų išvengta „sausio faktinio + plano“, naudojant lauko nustatymus
- Vietoj to sukurkite suvestinę lentelę su „per daug“ duomenų
- Naudokite gražiai suformatuotą ataskaitos darbalapį
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Iš pirmos darbalapio duomenų langelio sukurkite formulę pele
- Leiskite „GetPivotData“ įvykti
- Išnagrinėkite „GetPivotData“ sintaksę (laukas, kurį norite grąžinti, sukimo vieta, poros)
- Pakeiskite sunkiai užkoduotą reikšmę į langelį
- Tris kartus paspaudus F4, užfiksuojamas tik stulpelis
- Du kartus paspaudus F4, užrakinama tik eilutė
- Įklijuokite specialias formules
- Darbo eiga kitą mėnesį: pridėkite duomenų, atnaujinkite suvestinę lentelę, pakeiskite datą
- Itin atsargiai saugokitės naujų parduotuvių
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“, 2013 m. Epizodo - „GetPivotData“ gali būti ne visai bloga!
Tvirtinsiu visą šią knygą, viršutiniame dešiniajame kampe spustelėkite tą „i“, kad galėtumėte užsiprenumeruoti.
Gerai, dar 1998 m. Epizode trumpai kalbėjau apie šią „GetPivotData“ problemą. Jei apskaičiuojame dispersiją% ir mes nesame už „Pivot“ lentelės, rodančios į vidų, ir aš naudoju pelę arba rodyklės klavišą, taigi 2019 / 2018-1. Šis atsakymas, kurį čia gausime, yra teisingas sausio mėnesį, tačiau dukart spustelėjus, kad nukopijuotume žemyn, formulė nekopijuojama, sausio mėnesio atsakymą gauname iki galo. Kai pažvelgsime į tai, gausime „GetPivotData“, aš nerašiau „GetPivotData“, tiesiog nurodžiau į tas langelius, ir tai prasidėjo dar „Excel 2002“ be jokio įspėjimo. Tuo metu aš pasakiau, kad to išvengti reikia surinkus formulę C5 / B5-1 ir gausite formulę, kurią galėsite nukopijuoti. Arba, jei jūs tiesiog nekenčiate „GetPivotData“, jei jis „visiškai blogas“, eikite į skirtuką „Analizuoti“.t, beje, atidarykite mygtuką „Parinktys“. Grįžkite į lentelę „Pivot“, eikite į skirtuką Analizuoti, atidarykite išskleidžiamąjį meniu šalia Parinktys, panaikinkite šio laukelio žymėjimą, tai visuotinis nustatymas. Kai jį išjungsite, jis bus amžinai išjungtas, gerai.
Dažniausiai man kyla klausimų: „Kaip išjungti„ GetPivotData “?“ bet kiekvieną kartą gausiu ką nors, kas mėgsta „GetPivotData“. Aš pietavau su Robu Collie, kai jis dar dirbo „Microsoft“, ir jis pasakė: „Na, mūsų vidiniai klientai mėgsta„ GetPivotData “. Aš pasakiau „Ką? Ne, visi nekenčia „GetPivotData“! “ Robas sako: „Jūs teisi, ne„ Microsoft “, jie visiškai nekenčia„ GetPivotData “. Kalbu apie „Microsoft“ buhalterius, o vėliau sutikau dabar „Excel“ komandoje dirbantį Carlosą, o Carlosas buvo vienas iš buhalterių, naudojančių šį metodą.
Gerai, todėl štai ką turime padaryti. Mes turime savo ataskaitą, čia nustatytą duomenų rinkinį, kad kiekvienam mėnesiui turime kiekvienos parduotuvės planą, o tada apačioje kaupiame faktinius duomenis. Gerai, todėl turime faktinius duomenis nuo sausio iki gruodžio, tačiau faktinius duomenis turime tik kelis mėnesius, praėjusius mėnesius. Tai, ko nori mūsų vadybininkas, yra sudaryti ataskaitą su parduotuvėmis kairėje pusėje, žinoma, tik Teksaso parduotuvėse, kad apsunkintų gyvenimą. Ir tada mes turime mėnesius, o jei turime faktinį tą mėnesį, parodome faktinį, taigi faktinį sausio mėnesį, faktinį vasarį, faktinį kovo mėnesį, faktinį balandį. Bet tada tiems mėnesiams, kai neturime faktinių duomenų, mes perjungiame ir parodome biudžetą, taigi biudžetą išleiskite iki gruodžio mėnesio, o paskui iš viso viską, gerai. Na, kai bandysite sukurti šią „Pivot“ lentelę, taip,tai neveikia.
Taigi įdėkite „PivotTable“, „New Worksheet“, kairėje pusėje palikite parduotuvę gražioje pusėje, viršuje įdėkite „Mėnesiai“, viršuje - „Type“, „Pardavimai“ čia, gerai. Taigi štai ką mes turime pradėti dirbti, taigi turime sausio faktinį sausio planą ir tada visiškai nenaudingą sausio faktinį pliuso planą. Niekas to niekada nenaudos, bet aš galiu atsikratyti šių pilkų stulpelių, kurie yra pakankamai lengvi, kai kurie čia patenka į šią langelį, eina į lauko parametrus ir pakeičia tarpines sumas į Nėra. Bet niekaip negaliu pašalinti sausio mėnesio plano, kuris taip pat nepašalins balandžio, gegužės, birželio, liepos mėnesio plano. Gerai, jokiu būdu negalima to atsikratyti. Taigi kiekvieną mėnesį aš įstrigau pasirinkdamas visą „Pivot“ lentelę, eidamas į Kopijuoti ir tada Įklijuoti, Įklijuoti vertes. Tai nebe „Pivot“ lentelė,tada aš pradedu rankiniu būdu ištrinti stulpelius, kurie nerodomi ataskaitoje.
Gerai, tai įprastas metodas, tačiau „Microsoft“ buhalteriai sausio mėnesį pridėjo papildomą žingsnį, tai užtrunka 15 minučių, ir šis žingsnis leidžia šiai „Pivot“ lentelei gyventi amžinai, tiesa? Aš tai vadinu bjauriausia pasaulyje „Pivot“ lentele, o „Microsoft“ buhalteriai pripažįsta, kad tai yra bjauriausia pasaulyje „Pivot“ lentelė, tačiau niekas niekada nematys šios ataskaitos, išskyrus juos. Ką jie daro, ar jie ateina čia prie naujo lapo ir sukuria ataskaitą, kurios nori jų vadovas. Gerai, taigi čia yra parduotuvės kairėje pusėje, aš ją net sugrupavau į Hiustoną, Dalasą ir kitus, tai puikiai suformatuota ataskaita. Aš paryškinau sumas, pamatysite, kad kai gausime keletą skaičių, pirmoje eilutėje yra valiuta, bet ne šios paskesnės eilutės, tuščios eilutės. Oi, tuščios eilutės „Pivot“ lentelėje.Čia yra nedidelis logikos taškas, kur galiu įrašyti pasibaigimo datą langelyje P1, tada turiu formulę, analizuojančią, kad JEI pasibaigimo datos mėnuo yra> šis stulpelis, o tada įdėkite žodį Faktinis įdėk žodį Planas, gerai. Taigi viskas, ką aš turiu padaryti, tai pakeisti datą ir tada žodis „Aktualus“ persiversti planuoti, Gerai.
Štai ką mes darome, mes leisime sau būti „GetPivotData'd“, tiesa? Nesu tikras, ar tai veiksmažodis, bet mes leisime „Microsoft“ gauti „GetPivotData“. Taigi aš pradedu kurti formulę su =, griebiu pelės ir einu ieškoti faktinio sausio mėnesio Baybrook! Taigi grįžtu į šlykščiausią „Pivot“ lentelę pasaulyje, randu „Baybrook“, randu sausį, randu tikrąjį ir spusteliu „Enter“ ir leidau jiems tai padaryti man, gerai, važiuojame, dabar turime „GetPivotData“ formulę. Prisimenu tą dieną, kai tai padariau, tai buvo, kaip žinote, po to, kai Robas man paaiškino, ką jie daro, o aš grįžau ir išbandžiau. Dabar staiga visą gyvenimą atsikračiau „GetPivotData“, iš tikrųjų niekada nepriėmiau „GetPivotData“. Taigi, tai yra pirmasis dalykas, kurio mes ieškome. “Sa laukas vadinamas Pardavimai, čia prasideda „Pivot“ lentelė, ir tai gali būti bet kuri „Pivot“ lentelės ląstelė, jie naudoja kairę viršutinę ranką.
Gerai, tai yra lauko pavadinimas „Parduotuvė“, tada jie sunkiai užkodavo „Baybrook“, tai yra lauko pavadinimas „Mėnuo“, jie užkodavo „Sausis“, tai yra lauko pavadinimas „Tipas“ ir jie ' sunkiai užkoduotas „Aktualus“. Štai kodėl jūs negalite jo nukopijuoti, nes jie sunkiai užkodavo vertybes. Tačiau „Microsoft“ buhalteriai, Carlosas ir jo bendradarbiai supranta: „Oi, palauk sekundę, čia mes turime žodį„ Baybrook “, čia yra sausis, čia yra„ Actual “. Mes tiesiog turime pakeisti šią formulę, kad nurodytume faktines ataskaitos langelius, o ne būtų koduojami. “ Gerai, todėl jie šį parametrą vadina „GetPivotData“.
Pašalinkite žodį Baybrook, eikite čia ir spustelėkite langelį D6. Dabar turiu tai užrakinti kolonėlėje, gerai, todėl 3 kartus paspaudžiu F4 klavišą, prieš D, gerai, gavau vieną $. Sausio mėnesiui pašalinu sunkiai užkoduotą sausį, spusteliu langelį E3, du kartus paspaudžiu F4, kad užfiksuočiau jį iki eilutės, E $ 3. Įveskite „Actual“, pašalinkite žodį „Actual“, spustelėkite E4, dar kartą du kartus - F4, gerai, ir aš gausiu formulę, kuri dabar tuos duomenis grąžina. Aš tai nukopijuosiu, tada įklijuosiu specialųjį, pasirinkite Formatai, alt = "" ESF, pamatykite, kad F yra ten pabrauktas, ESF Enter, o dabar tai padariau, aš tiesiog pakartosiu su F4, F4 yra perdaryti, o F4. Gerai, todėl dabar turime gražiai atrodančią ataskaitą, joje yra tuščių vietų, ji yra formato, kiekviename skyriuje yra vienas apskaitos pabraukimas,pačioje apačioje jis turi dvigubą apskaitos pabraukimą.
Teisingai, jūs niekada neturite šios informacijos „Pivot“ lentelėje, tai neįmanoma, tačiau ši ataskaita yra sukurta iš „Pivot“ lentelės. Taigi tada, ką mes darome, kai gausime gegužės mėnesio faktinius duomenis, grįžkite čia, įklijuokite juos, eikite atnaujinti bjauriausią pasaulyje „Pivot“ lentelę ir tada čia, ataskaitoje, tiesiog pakeiskite perėjimo datą iš 4/30 į 5/31. Tai reiškia, kad ši formulė pereina nuo žodžio „Plan“ į „Actual“, kuris eina ir iš ataskaitos, o ne iš plano, sutvarko faktus. Dabar štai kas - puikus, tiesa? Matau, kur aš tai daryčiau daug, jei vis dar, žinote, dirbau buhalterijoje.
Dalykas, dėl kurio turite būti labai atsargūs, yra tai, kad jei jie sukurs naują parduotuvę, turite žinoti, kad jį pridėsite rankiniu būdu, tiesa, duomenys bus rodomi „Pivot“ lentelėje, bet pridėtumėte rankiniu būdu. Dabar šis yra visų parduotuvių pogrupis, jei jis praneštų apie visas parduotuves, aš tikriausiai čia, už spausdinimo diapazono ribų, turėčiau ką nors, kas ištrauktų bendrą sumą iš „Pivot“ lentelės. Ir tada aš žinočiau, jei ši suma neatitinka „Pivot“ lentelės bendros sumos, kažkas negerai ir čia turi IF funkciją. Sakydamas „Ei, žinai, kad buvo pridėti nauji duomenys, būk labai atsargus. “ Jie turi tam tikrą mechanizmą, kad nustatytų, jog yra naujų duomenų. Bet supratau, tai šaunus naudojimas. Taigi, nors „GetPivotData“ dažniausiai mus iš proto varo, iš tikrųjų tai gali būti naudinga. Gerai,Taigi tai yra patarimas Nr. 21 iš 40 knygos, nusipirkite knygą dabar, užsisakykite internetu, spustelėkite tą „i“ viršutiniame dešiniajame kampe.
Ilgas, ilgas šiandieninis pakartojimas, gerai: „GetPivotData“ įvyksta, kai formulė nukreipta į „Pivot“ lentelės vidų, už „Pivot“ lentelės esanti formulė - į vidų. Nors pradinė formulė yra teisinga, ji nebus nukopijuota. Daugelis žmonių nekenčia „GetPivotData“ ir nori to išvengti. Taigi galite sukurti formulę be pelės ar rodyklių klavišų, tiesiog įveskite formulę arba visam laikui išjunkite „GetPivotData“, a, bet yra naudos, gerai. Taigi turime sudaryti ataskaitą su faktiniais praėjusio mėnesio duomenimis, ateities biudžetu. Įprasta darbo eiga, sukurkite „Pivot“ lentelę, konvertuokite į reikšmes, ištrinkite stulpelius. Yra būdas pašalinti tarpines sumas naudojant lauko nustatymus, atsikratant to sausio faktinio pliuso plano. Vietoj to mes tiesiog sukursime bjauriausią pasaulyje „Pivot“ lentelę, kurioje bus per daug duomenų.
Sukurkite gražiai suformatuotą, tiesiog seną ataskaitos darbalapį su galbūt šiek tiek logikos, kad pakeistumėte žodį „Actual“ į „Plan“. Tada iš pirmos ataskaitos langelio, pirmoje vietoje, kur toje ataskaitoje bus skaičiai, įveskite =, eikite į lentelę „Pivot“ ir leiskite įvykti „GetPivotData“. Nagrinėjame „GetPivotData“ sintaksę, taigi laukas „Return“, „Sales“, kur gyvena „Pivot“ lentelė, tada poros kriterijų, lauko pavadinimas ir vertė. Pašalinsime sunkiai užkoduotą reikšmę ir nurodysime langelį, 3 kartus paspaudus F4, užrakinamas tik stulpelis, 2 kartus paspaudus F4, užrakinama tik eilutė, nukopijuojama ta formulė, Įklijuokite specialias formules. Aš ten įmečiau papildomą patarimą, kad F4 yra perdarymas, todėl man tereikėjo eiti į „Įklijuoti specialųjį“ dialogą tik vieną kartą, o paskui kitose specialių formulių įklijavimo vietose tiesiog naudojo F4. Kitą mėnesį pridėkite duomenis,atnaujinkite „Pivot“ lentelę, pakeiskite pabaigos datą. Įsitikinkite, kad jie nepastatė jokių naujų parduotuvių, žinokite, kad jie turi kažkokį mechanizmą, rankinį arba patikrinimo formulę, patikrinkite. Ačiū „iTrainerMX“ sistemoje „Twitter“, kuris pasiūlė „GetPivotData“, taip pat Carlosą ir Robą iš „Microsoft“, Robą dabar iš „Power Pivot Pro“. Carlosą už tai, kad jis naudojosi, ir Robą, kuris man pasakė, kad Carlosas jį naudoja, aš vėliau sutikau Carlosą ir jis patvirtino, kad taip, jis buvo vienas iš buhalterių, kuris visą laiką tuo naudojosi „Microsoft“, gerai, važiuok.ir Robui, kad jis man pasakė, kad Carlosas jį naudoja, aš vėliau sutikau Carlosą ir jis patvirtino, kad taip, jis buvo vienas iš buhalterių, kuris visą laiką tai naudojo „Microsoft“, gerai, važiuok.ir Robui, kad jis man pasakė, kad Carlosas jį naudoja, aš vėliau sutikau Carlosą ir jis patvirtino, kad taip, jis buvo vienas iš buhalterių, kuris visą laiką tai naudojo „Microsoft“, gerai, važiuok.
Na, aš noriu padėkoti už tai, kad užsukote, mes susitiksime kitą kartą kitam netcast'ui iš!
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2013.xlsx
