Ali sovražite Excelovo funkcijo GETPIVOTDATA? Zakaj se pojavi? Kako lahko to preprečite? Ali se GETPIVOTDATA dobro uporablja?
Večina ljudi se prvič sreča z GETPIVOTDATA, ko poskuša zgraditi formulo zunaj vrtilne tabele, ki uporablja številke v vrtilni tabeli. Na primer, ta odstotek variance se ne bo kopiral na druge mesece, ker Excel vstavi funkcije GETPIVOTDATA.
Excel vstavi GETPIVOTDATA vsakič, ko z miško ali puščičnimi tipkami pokažete na celico znotraj vrtilne tabele, medtem ko gradite formulo zunaj vrtilne tabele.
Mimogrede, če ne želite, da se prikaže funkcija GETPIVOTDATA, preprosto vnesite formulo, na primer = D5 / C5-1, ne da bi z miško ali puščičnimi tipkami kazali na celice. Ta formula se brez težav kopira.
Tu je nabor podatkov, ki vsebuje eno številko načrta na mesec na trgovino. Obstajajo tudi dejanske prodaje na mesec na trgovino za mesece, ki so končani. Vaš cilj je sestaviti poročilo, ki prikazuje dejanske podatke za zaključene mesece in načrt za prihodnje mesece.
Zgradite vrtilno tabelo s storitvijo Store in ROWS. V stolpce vnesite mesec in tip. Dobite poročilo, prikazano spodaj, z dejanskim januarjem, januarskim načrtom in popolnoma nesmiselnim januarskim dejanskim + načrtom.
Če izberete celico za mesec in odprete Nastavitve polja, lahko vmesne seštevke spremenite v Brez.
S tem odstranite neuporaben načrt Actual +. A vseeno se morate znebiti stolpcev načrta za januar do aprila. Tega v vrtilni tabeli ni mogoče narediti na dober način.
Torej, vaš mesečni potek dela postane:
- V nabor podatkov dodajte dejanske podatke za novi mesec.
- Izdelajte novo vrtilno tabelo iz nič.
- Kopirajte vrtilno tabelo in prilepite kot vrednosti, tako da ta ni več vrtilna tabela.
- Izbrišite stolpce, ki jih ne potrebujete.
Obstaja boljša pot. Naslednja zelo majhna slika prikazuje nov Excelov delovni list, dodan v delovni zvezek. Vse to je samo naravnost Excel, brez pivot tabel. Edina čarovnija je funkcija IF v vrstici 4, ki preklopi iz dejanskega v načrt glede na datum v celici P1.
Prva celica, ki jo je treba izpolniti, je januar, Actuals za Baybrook. Kliknite to celico in vnesite znak enakosti. Z miško se pomaknite nazaj do vrtilne tabele. Poiščite celico za januarske dejanske podatke za Baybrook. Kliknite to celico in pritisnite Enter. Kot običajno Excel ustvari eno izmed tistih nadležnih funkcij GETPIVOTDATA, ki jih ni mogoče kopirati.
Danes pa preučimo sintakso GETPIVOTDATA.
Prvi argument spodaj je številčno polje "Prodaja". Drugi argument je celica, v kateri je vrtilna tabela. Preostali pari argumentov so ime polja in vrednost. Ali vidite, kaj je storila samodejno ustvarjena formula? Kot ime trgovine je bilo težko kodirano "Baybrook". Zato teh samodejno ustvarjenih formul GETPIVOTDATA ne morete kopirati. Imena dejansko trdo kodirajo v formule. Čeprav teh formul ne morete kopirati, jih lahko uredite. V tem primeru bi bilo bolje, če bi formulo uredili tako, da kaže na celico $ D6.
Tukaj je formula, potem ko jo uredite. Gone so "Baybrook", "Jan" in "Actual". Namesto tega kažete na $ D6, E $ 3, E $ 4.
Kopirajte to formulo in nato izberite Paste Special, Formulas v vseh drugih numeričnih celicah.
Zdaj je vaš letni potek dela:
- Zgradite grdo vrtilno tabelo, ki je nihče ne bo videl.
- Nastavite delovni list poročila.
Vsak mesec morate:
- Pod podatke prilepite nove dejanske podatke.
- Osvežite grdo vrtilno tabelo.
-
Spremenite celico P1 na poročilu, da bo odražala nov mesec. Vse številke se posodobijo.
Spremeni celico P1
Morate priznati, da vam uporaba navadnega poročila, ki potegne številke iz vrtilne tabele, daje najboljše iz obeh svetov. Poročilo lahko formatirate tako, da vrtilne tabele ne morete oblikovati. Prazne vrstice so v redu. V prvi in zadnji vrstici so lahko simboli valut, vmes pa ne. Pod velikimi vsotami dobite tudi dvojna podčrtanja.
Hvala @iTrainerMX, ker je predlagal to funkcijo.
Oglejte si video
- GetPivotData se zgodi, ko formula kaže znotraj vrtilne tabele
- Čeprav je začetna formula pravilna, formule ne morete kopirati
- Večina ljudi sovraži getpivotdata in jih želi preprečiti
- 1. način: Sestavite formulo brez miške ali puščičnih tipk
- 2. način: s spustnim menijem poleg možnosti trajno izklopite GetPivotData
- Vendar obstaja uporaba za GetPivotData
- Vaš upravitelj želi poročilo z dejanskimi podatki za pretekle mesece in proračunom za prihodnost
- Z običajnim potekom dela bi ustvarili vrtilno tabelo, pretvorili v vrednosti in izbrisali stolpce
- Odstranjevanje vmesnih seštevkov za preprečitev januarja Actual + Plan z uporabo nastavitev polja
- Namesto tega ustvarite vrtilno tabelo s "preveč" podatki
- Uporabite lepo oblikovan delovni list poročila
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Iz prve podatkovne celice na delovnem listu z miško zgradite formulo
- Dovolite, da se zgodi GetPivotData
- Preučite sintakso GetPivotData (polje za vrnitev, vrtilna lokacija, pari)
- Spremenite trdo kodirano vrednost tako, da kaže na celico
- Trikratno pritiskanje tipke F4 zaklene samo stolpec
- Če dvakrat pritisnete F4, se zaklene samo vrstica
- Prilepite posebne formule
- Potek dela naslednji mesec: dodajte podatke, osvežite vrtilno tabelo, spremenite datum
- Zelo previdno pazite na nove trgovine
Prepis videoposnetka
Naučite se Excel iz podcasta, epizoda 2013 - GetPivotData morda ne bo popolnoma zlo!
Celotno knjigo bom objavil v podcastu, kliknite »i« v zgornjem desnem kotu, da se naročite.
V redu, že v epizodi 1998 sem na kratko spregovoril o tej težavi z GetPivotData. Če izračunamo odstotek% in smo zunaj vrtilne tabele, ki kaže znotraj, in uporabim miško ali puščično tipko, torej 2019 / 2018-1. Ta odgovor, ki ga bomo dobili tukaj, je pravilen za januar, toda ko dvakrat kliknemo, da to kopiramo, formula ne kopira, dobimo januarski odgovor do konca. In ko ga pogledamo, dobimo GetPivotData, nisem vpisal GetPivotData, samo kazal sem na te celice in to se je začelo dogajati že v Excelu 2002 brez kakršnega koli opozorila. In takrat sem rekel, da se temu lahko izognemo tako, da vtipkamo formulo C5 / B5-1 in dobili bomo formulo, ki jo lahko kopiramo. Ali če sovražite GetPivotData, če je "popolnoma zlo", pojdite na zavihek Analiziraj, net mimogrede odprite gumb Možnosti. Vrnite se v vrtilno tabelo, pojdite na zavihek Analizirajte, odprite spustni meni poleg Možnosti, počistite to polje, to je globalna nastavitev. Ko ga izklopite, bo za vedno izklopljen, v redu.
Večina vprašanj, ki jih dobim, so »Kako izklopim GetPivotData?« toda vsake toliko časa dobim nekoga, ki ima rad GetPivotData. In kosila sem z Robom Colliejem, ko je bil še v Microsoftu, in rekel: "No, naše notranje stranke imajo radi GetPivotData." Sem rekel: "Kaj? Ne, vsi sovražijo GetPivotData! " Rob pravi: "Prav imaš, zunaj Microsofta, absolutno sovražijo GetPivotData." Govorim o računovodjih znotraj Microsofta, kasneje pa sem spoznal enega, ki zdaj dela za Excelovo ekipo, Carlos, Carlos pa je bil eden od računovodjev, ki uporablja to metodo.
Torej, tukaj je, kaj moramo storiti. Imamo svoje poročilo, tukaj je nabor podatkov, da imamo za vsak mesec načrt za vsako trgovino, nato pa na dnu zbiramo dejanske podatke. V redu, tako da imamo dejanske podatke od januarja do decembra, dejanske pa imamo le za nekaj mesecev, mesecev, ki so minili. In kar želi naš vodja, je, da sestavimo poročilo s trgovinami po levi strani, seveda le v trgovinah v Teksasu, da si življenje otežimo. In potem gremo čez mesece in če imamo dejansko vrednost za ta mesec, prikažemo dejansko, torej januar dejansko, februar dejansko, marec dejansko, april dejansko. Potem pa za mesece, ko nimamo dejanskih stvari, preklopimo in prikažemo proračun, torej proračun do decembra, nato pa vse skupaj, v redu. No, ko poskusite ustvariti to vrtilno tabelo, ja,ne deluje.
Torej vstavite vrtilno tabelo, nov delovni list, na levo stran, ki je čudovita, postavite Store, na vrh postavite Mesece, na vrh vnesite Type, tukaj dajte prodajo. Tako dobimo, s čimer moramo začeti delati, tako da imamo dejanski januarski načrt, januarski načrt in nato povsem neuporaben januarski dejanski plus. Tega ne bo nihče nikoli uporabil, lahko pa se znebim teh sivih stolpcev, kar je dovolj enostavno, nekateri tukaj v to celico, pojdite v Nastavitve polja in spremenite vmesne seštevke v Brez. Ampak absolutno nikakor ne morem odstraniti januarskega načrta, ki ne bo odstranil tudi aprilskega maja junijskega julijskega načrta, prav, tega se nikakor ne morem rešiti. Na tej točki vsak mesec torej zataknem izbiro celotne vrtilne tabele, grem na Kopiraj in nato Prilepi, Prilepi vrednosti. To ni več vrtilna tabela,in nato začnem ročno brisati stolpce, ki niso prikazani v poročilu.
V redu, to je običajna metoda, toda računovodje v Microsoftu so januarja dodali dodaten korak, traja 15 minut in ta korak omogoča tej vrtilni tabeli, da živi večno, kajne? Temu pravim najgrša vrtilna tabela na svetu, računovodje v Microsoftu pa se strinjajo, da je to najgrša vrtilna tabela na svetu, vendar tega poročila nihče ne bo videl razen njih. Kar storijo, pridejo na nov list in sestavijo poročilo, ki ga želi njihov vodja. Torej, tukaj so trgovine po levi strani, celo združil sem jih v Houston, Dallas in druge, to je lepo oblikovano poročilo. Poudaril sem vsote, videli boste, da ko dobimo nekaj številk, je v prvi vrstici valuta, ne pa tudi naslednje vrstice, prazne vrstice. Ooh, prazne vrstice v vrtilni tabeli.In en majhen košček logike tukaj, kjer lahko v celico P1 vnesem datum poteka, nato pa imam tukaj formulo, ki analizira, da ČE je mesec datuma> ta stolpec, in nato vpišem besedo dejansko dajte besedo načrt, v redu. Torej vse, kar moram storiti, je, da to spremenim skozi datum, nato pa beseda dejansko preusmerim v načrt, v redu.
Zdaj je to, kar počnemo, dopustili si bomo GetPivotData'd, kajne? Nisem prepričan, da je to glagol, vendar bomo Microsoftu dovolili GetPivotData. Tako začnem graditi formulo z =, zagrabim miško in grem iskat dejanski Baybrook iz januarja! Torej se vrnem v najgršo vrtilno tabelo na svetu, najdem Baybrooka, najdem januarja, najdem dejanskega in kliknem Enter in pustim, da mi to naredijo, prav, zdaj imamo, zdaj imamo formulo GetPivotData. Spomnim se dneva, ko sem to naredil, bilo je, kot da veste, potem ko mi je Rob razložil, kaj počnejo, in sem se vrnil in poskusil. Zdaj pa sem se naenkrat, vse življenje rešil GetPivotData, dejansko še nikoli nisem sprejel GetPivotData. Torej, kaj je, prvi element je tisto, kar iščemo, tam 'v polju, imenovanem Prodaja, se tu začne vrtilna tabela in je lahko katera koli celica v vrtilni tabeli, pri čemer uporabljajo zgornji levi rob.
V redu, to je ime polja »Trgovina«, nato so trdo kodirali »Baybrook«, to je ime polja »Mesec«, trdo so označili »Januar«, to je ime polja »Tip« in Vec kodirano "Actual". ZATO ga ne morete kopirati, ker so vrednote kodirali. Toda računovodje v Microsoftu, Carlos in njegovi sodelavci se zavedajo: "Počakajte malo, tukaj imamo besedo Baybrook, tukaj imamo januar, tukaj imamo Actual. To formulo moramo samo spremeniti tako, da namesto na trdo kodirano kaže na dejanske celice v poročilu. " V redu, temu pravijo parametrizacija GetPivotData.
Odstranite besedo Baybrook, pridite sem in kliknite celico D6. Zdaj moram to zakleniti v stolpec, v redu, zato trikrat pritisnem tipko F4 in dobim en dolar pred D, v redu. Za mesec januar odstranim kodirani januar, kliknem celico E3, dvakrat pritisnem F4, da jo zaklenem v vrstico, E $ 3. Vnesite Actual, odstranite besedo Actual, kliknite E4, še enkrat dvakrat F4, v redu, in dobim formulo, ki zdaj te podatke povleče nazaj. Kopirala bom to, nato pa prilepila Special, izbrala Format, alt = "" ESF, glej, da je F podčrtano, ESF Enter, in potem, ko sem to storil, bom samo ponovil s F4, F4 je ponovitev in F4. Torej, zdaj imamo lepo videti poročilo, ima prazne prostore, ima oblikovanje, pod vsakim odsekom je podčrtano računovodstvo,na dnu ima dvojno računovodsko podčrtaje.
Kakorkoli, teh stvari nikoli ne dobite v vrtilni tabeli, to je nemogoče, vendar je to poročilo narejeno iz vrtilne tabele. Torej, kaj storimo, ko dobimo majske dejanske podatke, se vrnemo sem, jih prilepimo, osvežimo najgršo vrtilno tabelo na svetu, nato pa tukaj na poročilu samo spremenimo datum poteka s 4. na 30. 5. 31 In to povzroči, da ta formula preide z besede Načrt na Dejansko, ki gre in izvleče dejanske podatke iz poročila, namesto načrta, v redu. Zdaj, tukaj je nekaj … to je super, kajne? Vidim, kje bi to veliko počel, če bi še vedno, veste, delal v računovodstvu.
Stvar, pri kateri morate biti zelo previdni, je, da če zgradijo novo trgovino, jo morate znati dodati ročno, kajne, podatki bodo prikazani v vrtilni tabeli, vendar bi jih dodali ročno. Zdaj je ta podmnožica vseh trgovin, če bi poročal o vseh trgovinah, bi verjetno tu zunaj obsega tiskanja imel nekaj, kar je vsoto potegnilo iz vrtilne tabele. In potem bi vedel, če se ta vsota ne ujema s skupno vsoto iz vrtilne tabele, da je nekaj narobe, in imam spodaj funkcijo IF, ki pravi »Hej, veste, dodani so novi podatki, bodite zelo previdni. " Imajo nekakšen mehanizem za zaznavanje novih podatkov. Ampak razumem, to je kul uporaba. Torej, medtem ko nas GetPivotData večino časa samo spravlja ob pamet, ga dejansko lahko uporabimo. Vredu,Torej, to je nasvet št. 21 od 40 v knjigi, knjigo kupite zdaj, naročite po spletu in kliknite na i v zgornjem desnem kotu.
Dolgo, dolgo povzemanje danes, v redu: GetPivotData se zgodi, ko formula kaže znotraj vrtilne tabele, formula zunaj vrtilne tabele pa kaže znotraj. Čeprav je začetna formula pravilna, se ne bo kopirala. Večina ljudi sovraži GetPivotData in jih želi preprečiti. Tako lahko sestavite formulo brez miške ali puščičnih tipk, preprosto vtipkate formulo ali trajno izklopite GetPivotData, ah, vendar je uporaba v redu. Zato moramo sestaviti poročilo z dejanskimi podatki za pretekli mesec in proračunom za prihodnost. Običajni potek dela, ustvarite vrtilno tabelo, pretvorite v vrednosti, izbrišite stolpce. Vmesne seštevke lahko odstranite z uporabo nastavitev polja, tako da se znebite dejanskega plus januarskega načrta. Namesto tega bomo samo ustvarili najgršo vrtilno tabelo na svetu s preveč podatkov.
Sestavite lepo oblikovan, navaden star delovni list poročila z morda malo logike, da besedo Actual spremenite v načrt. In nato iz prve celice poročila, prvega mesta, kjer bodo številke v tem poročilu, vnesite =, pojdite na vrtilno tabelo in dovolite, da se zgodi GetPivotData. Preučimo sintakso GetPivotData, torej je to polje za vrnitev, Prodaja, kjer živi vrtilna tabela, in nato pari meril, ime polja in vrednost. Odstranili bomo trdno kodirano vrednost in pokazali na celico, s pritiskom na F4 3-krat zaklenemo samo stolpec, s pritiskom na F4 2-krat zaklenemo samo vrstico, kopiramo to formulo, Prilepi posebne formule. Tam sem dal dodaten namig, da je F4 ponovitev, zato sem moral samo enkrat odpreti pogovorno okno Paste Special, nato pa za naslednje Paste Special Formule pravkar uporabil F4. Naslednji mesec dodajte podatke,osvežite vrtilno tabelo, spremenite datum. Prepričajte se, da niso zgradili nobenih novih trgovin, saj veste, imajo kakšen mehanizem, bodisi ročni ali kontrolno formulo, preverite. Zahvaljujoč iTrainerMX na Twitterju, ki je predlagal GetPivotData, tudi Carlosa in Roba iz Microsofta, Roba zdaj iz Power Pivot Pro. Carlos, ker je to uporabil, in Rob, ker mi je povedal, da Carlos to uporablja, kasneje sem ga spoznal, in potrdil je, da, bil je eden od računovodjev, ki je to ves čas uporabljal v Microsoftu.in Rob, ker mi je povedal, da ga Carlos uporablja, sem kasneje srečal Carlosa in potrdil je, da, bil je eden od računovodjev, ki je to ves čas uporabljal v Microsoftu.in Rob, ker mi je povedal, da ga Carlos uporablja, sem kasneje srečal Carlosa in potrdil je, da, bil je eden od računovodjev, ki je to ves čas uporabljal v Microsoftu.
No hej, rad bi se vam zahvalil, da ste se ustavili, se vidimo naslednjič za še eno oddajo!
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2013.xlsx
