Mrzite li Excelovu funkciju GETPIVOTDATA? Zašto se pojavljuje? Kako to možete spriječiti? Postoji li dobra upotreba za GETPIVOTDATA?
Većina se ljudi prvi put susreće s GETPIVOTDATA kada pokušavaju izgraditi formulu izvan zaokretne tablice koja koristi brojeve u zaokretnoj tablici. Na primjer, ovaj postotak varijance neće se kopirati na ostale mjesece zbog Excel-a koji je umetnuo funkcije GETPIVOTDATA.
Excel ubacuje GETPIVOTDATA svaki put kada mišem ili tipkama sa strelicama pokažete na ćeliju unutar zaokretne tablice dok gradite formulu izvan zaokretne tablice.
Usput, ako ne želite da se pojavi funkcija GETPIVOTDATA, jednostavno upišite formulu kao što je = D5 / C5-1 bez korištenja miša ili tipki sa strelicama za usmjeravanje na stanice. Ta se formula kopira bez problema.
Evo skupa podataka koji sadrži jedan broj plana mjesečno po trgovini. Postoje i stvarne mjesečne prodaje po prodavaonici za mjesece koji su završeni. Cilj vam je izraditi izvješće koje prikazuje stvarne podatke za završene mjesece i plan za buduće mjesece.
Izgradite zaokretnu tablicu s Spremi u REDOVE. U STUPCE stavite Mjesec i Upišite. Dobit ćete dolje prikazano izvješće sa stvarnim siječanjskim planom, siječanjskim planom i potpuno besmislenim siječanjskim stvarnim + planom.
Ako odaberete ćeliju za mjesec i odete na Postavke polja, međuzbrojeve možete promijeniti u Ništa.
Ovo uklanja beskorisni plan Actual +. Ali ipak se morate riješiti stupaca plana za siječanj do travnja. Ne postoji dobar način za to unutar pivot tablice.
Dakle, vaš mjesečni tijek rada postaje:
- Skupu podataka dodajte stvarne podatke za novi mjesec.
- Izgradite novu stožernu tablicu od nule.
- Kopirajte zaokretnu tablicu i zalijepite kao vrijednosti tako da više nije zaokretna tablica.
- Izbrišite stupce koji vam nisu potrebni.
Postoji bolji put. Sljedeća vrlo mala slika prikazuje novi Excel radni list dodan u radnu knjigu. Sve je to samo ravni Excel, bez pivot tablica. Jedina čarolija je funkcija IF u retku 4 koja se prebacuje sa Stvarnog na Plan na temelju datuma u ćeliji P1.
Prva ćelija koju treba popuniti je siječanj, Actuals za Baybrook. Kliknite tu ćeliju i upišite znak jednakosti. Pomoću miša vratite se na zaokretnu tablicu. Pronađite ćeliju za siječanjske stvarnosti za Baybrook. Kliknite tu ćeliju i pritisnite Enter. Kao i obično, Excel gradi jednu od onih dosadnih GETPIVOTDATA funkcija koje se ne mogu kopirati.
Ali danas, proučimo sintaksu GETPIVOTDATA.
Prvi argument u nastavku je numeričko polje "Prodaja". Drugi je argument ćelija u kojoj se nalazi stožerna tablica. Preostali parovi argumenata su ime polja i vrijednost. Vidite li što je radila automatski generirana formula? Tvrdo je kodiran "Baybrook" kao naziv trgovine. Zbog toga ne možete kopirati ove automatski generirane GETPIVOTDATA formule. Oni zapravo tvrdo kodiraju imena u formule. Iako ove formule ne možete kopirati, možete ih urediti. U ovom slučaju, bilo bi bolje da formulu uredite tako da pokazuje na ćeliju $ D6.
Evo formule nakon što je uredite. Nestali su "Baybrook", "Jan" i "Actual". Umjesto toga, pokazujete na $ D6, E $ 3, E $ 4.
Kopirajte ovu formulu, a zatim odaberite Paste Special, Formulas u svim ostalim numeričkim ćelijama.
Sada je vaš godišnji tijek rada:
- Izgradite ružnu stožernu tablicu koju nitko nikada neće vidjeti.
- Postavite radni list izvješća.
Svaki mjesec morate:
- Zalijepite nove stvarne podatke ispod podataka.
- Osvježite ružnu zaokretnu tablicu.
-
Promijenite ćeliju P1 na listu izvješća kako bi odražavao novi mjesec. Svi se brojevi ažuriraju.
Promijenite ćeliju P1
Morate priznati da vam korištenje običnog izvješća koje povlači brojeve iz zaokretne tablice daje najbolje od oba svijeta. Izvješće možete formatirati na načine na koje ne možete oblikovati zaokretnu tablicu. Prazni redovi su u redu. Na prvom i posljednjem retku možete imati simbole valuta, ali ne između. Dobivate i dvostruko podcrtavanje ispod ukupnih ukupaca.
Zahvaljujem @iTrainerMX-u što je predložio ovu značajku.
Gledaj video
- GetPivotData se događa kada formula usmjerava unutar zaokretne tablice
- Iako je početna formula točna, formulu ne možete kopirati
- Većina ljudi mrzi getpivotdata i želi ih spriječiti
- Metoda 1: Izradite formulu bez miša ili tipki sa strelicama
- 2. način: Trajno isključite GetPivotData pomoću padajućeg izbornika pored opcija
- Ali postoji upotreba za GetPivotData
- Vaš menadžer želi izvješće sa stvarnim podacima za protekle mjesece i proračunom za budućnost
- Uobičajeni tijek rada trebao bi stvoriti pivot tablicu, pretvoriti u vrijednosti, izbrisati stupce
- Uklanjanje međuzbrojeva kako bi se spriječio stvarni i plan za siječanj pomoću postavki polja
- Umjesto toga, stvorite zaokretnu tablicu s podacima "previše"
- Upotrijebite lijepo oblikovan radni list izvješća
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Iz prve ćelije podataka na radnom listu izgradite formulu mišem
- Dopustite da se dogode GetPivotData
- Ispitajte sintaksu GetPivotData (polje za povratak, mjesto okretanja, parovi)
- Promijenite tvrdo kodiranu vrijednost tako da pokazuje na ćeliju
- Pritiskom na F4 tri puta zaključava se samo stupac
- Pritiskom na F4 dva puta zaključava se samo red
- Zalijepite posebne formule
- Tijek rada sljedeći mjesec: dodajte podatke, osvježite zaokretnu tablicu, promijenite datum
- Izuzetno oprezno pripazite na nove trgovine
Prijepis videozapisa
Naučite Excel iz podcasta, epizoda 2013. - GetPivotData možda neće biti potpuno zao!
Podcast ću cijelu ovu knjigu, kliknite "i" u gornjem desnom kutu da biste se pretplatili.
Dobro, još u epizodi 1998. kratko sam razgovarao o ovom problemu GetPivotData. Ako izračunamo% varijance i nalazimo se izvan zaokretne tablice koja pokazuje prema unutra, a ja koristim miša ili tipku sa strelicom, dakle 2019 / 2018-1. Ovaj odgovor koji ćemo ovdje dobiti točan je za siječanj, ali kad dvaput kliknemo da to kopiramo, formula se ne kopira, dobivamo odgovor za siječanj do kraja. I kad to pogledamo, dobivamo GetPivotData, nisam tipkao GetPivotData, samo sam pokazao na te stanice, a to se počelo događati još u programu Excel 2002 bez ikakvog upozorenja. I tada sam rekao da je način da se to izbjegne utipkavanjem formule C5 / B5-1 i dobit ćete formulu koju možete kopirati. Ili ako samo mrzite GetPivotData, ako je "potpuno zao", idite na karticu Analiziraj, nemojte 't usput otvorite gumb Opcije. Vratite se u pivot tablicu, idite na karticu Analyze, otvorite padajući izbornik pored Options, poništite ovaj okvir, to je globalna postavka. Jednom kad ga isključite, zauvijek će se isključiti, u redu.
Većinu pitanja koja dobivam su "Kako mogu isključiti GetPivotData?" ali svako malo naći ću nekoga tko voli GetPivotData. A ja sam ručao s Robom Colliejem dok je još bio u Microsoftu i rekao je: "Pa, naši interni kupci vole GetPivotData." Rekao sam „Što? Ne, svi mrze GetPivotData! " Rob kaže: "U pravu ste, izvan Microsofta, apsolutno, oni mrze GetPivotData." Govorim o računovođama unutar Microsofta, a kasnije sam upoznao jednog koji sada radi za Excel tim, Carlos, a Carlos je bio jedan od računovođa koji koriste ovu metodu.
U redu, evo što moramo učiniti. Imamo naše izvješće, ovdje postavljen podatak da za svaki mjesec imamo plan za svaku trgovinu, a zatim na dnu sakupljamo stvarne podatke. U redu, tako da imamo aktualne podatke za razdoblje od siječnja do prosinca, ali stvarne podatke imamo tek za nekoliko mjeseci, mjeseci koji su prošli. A ono što naš menadžer želi da napravimo je da napravimo izvještaj s trgovinama s lijeve strane, samo trgovine u Teksasu, naravno, da bi život učinili težim. A onda prelazeći preko imamo mjeseci, a ako imamo stvarni za taj mjesec, prikazujemo stvarni, dakle stvarni siječanj, veljaču, stvarni ožujak, stvarni travanj. Ali onda se za mjesece u kojima nemamo stvarnosti prebacujemo i prikazujemo proračun, tako da budžet traje do prosinca, a onda sveukupno, sve u redu. Pa, kad pokušate stvoriti ovu pivot tablicu, da,ne radi.
Dakle, umetnite zaokretnu tablicu, novi radni list, stavite Store na lijevu, lijepu stranu, stavite Mjesece na vrh, stavite Type na vrh, stavite Sales ovdje, u redu. Dakle, evo što smo dobili s čime moramo početi surađivati, tako da imamo stvarni siječanjski plan, siječanjski plan, a zatim potpuno beskoristan siječanjski stvarni plus plan. Nitko to nikada neće koristiti, ali mogu se riješiti ovih sivih stupaca, to je dovoljno jednostavno, neki ovdje u ovu ćeliju, idite na Postavke polja i promijenite međuzbrojeve u Ništa. Ali apsolutno ne postoji način da uklonim plan za siječanj koji neće ukloniti i plan za travanj, lipanj, srpanj, u redu, ne postoji način da se toga riješim. Tako da sam u ovom trenutku svakog mjeseca zaglavio pri odabiru cijele zaokretne tablice, prelasku na Kopiraj, a zatim Zalijepi, zalijepi vrijednosti. To više nije pivot tablica,i tada počinjem ručno brisati stupce koji se ne pojavljuju u izvješću.
U redu, to je normalna metoda, ali računovođe u Microsoftu dodali su dodatni korak u siječnju, traje 15 minuta, a ovaj korak omogućuje ovoj Pivot tablici da živi vječno, zar ne? To nazivam najružnijom pivot tablicom na svijetu, a računovođe u Microsoftu prihvaćaju da je ovo najružnija pivot tablica na svijetu, ali ovo izvješće nitko neće vidjeti osim njih. Ono što rade je da dođu ovdje na novi list i naprave izvještaj koji želi njihov menadžer. Dobro, evo evo trgovina s lijeve strane, čak sam ih i grupirao u Houston, Dallas i ostalo, to je lijepo oblikovano izvješće. Istaknuo sam ukupne iznose, vidjet ćete da kada unesemo neke brojeve, u prvom redu je valuta, ali ne i sljedeći, prazni redovi. Ooh, prazni redovi u zaokretnoj tablici.I jedan mali djelić logike ovdje, gdje mogu staviti datum prolaska u ćeliju P1, a zatim ovdje imam formulu koja analizira da AKO je mjesec prolaznog datuma> ovaj stupac, a zatim stavim riječ Actual, inače stavi riječ Plan, u redu. Dakle, sve što moram učiniti je promijeniti to kroz datum, a zatim riječ Stvarno prebaciti na plan, u redu.
Eto, ovo što radimo, dopustit ćemo si da budemo GetPivotData'd, zar ne? Nisam siguran da je to glagol, ali dopustit ćemo Microsoftu da dobije GetPivotData. Dakle, počinjem graditi formulu s =, uhvatim miša i idem potražiti stvarni Baybrook iz siječnja! Tako se vraćam u najružniju pivot tablicu na svijetu, pronalazim Baybrooka, pronalazim siječanj, smatram stvarnim i klikćem Enter i dopuštam da mi to čine, u redu, eto, sad imamo GetPivotData formulu. Sjećam se dana kada sam to učinio, bilo je to, znaš, nakon što mi je Rob objasnio što rade, a ja sam se vratio i pokušao. Sad sam se odjednom, cijeli život rješavao GetPivotData, zapravo nikada nisam prihvatio GetPivotData. Dakle, ono što je, prva stavka je ono što tražimo, tamo 'sa poljem Prodaja, ovdje započinje pivot tablica, a to može biti bilo koja ćelija pivot tablice, koriste gornju lijevu ruku.
U redu, ovo je naziv polja "Trgovina", a zatim su čvrsto kodirali "Baybrook", ovo je naziv polja "Mjesec", oni su čvrsto kodirali "Siječanj", ovo je naziv polja "Tip", i oni ' vec sam kodiran "Stvarno". ZATO ga ne možete kopirati, jer su vrijednosti dobro kodirali. No, računovođe u Microsoftu, Carlos i njegovi suradnici shvaćaju: "Joj, pričekajte malo, ovdje imamo riječ Baybrook, ovdje imamo siječanj, ovdje imamo Actual. Moramo samo promijeniti ovu formulu kako bi ukazali na stvarne stanice u izvješću, umjesto da budu kodirane. " U redu, pa to nazivaju parametriziranjem GetPivotData.
Uklonite riječ Baybrook, dođite ovdje i kliknite ćeliju D6. Sad, moram to zaključati na stupac, u redu, pa pritisnem tipku F4 3 puta, uzmem jedan $ prije D, u redu. Za mjesec siječanj uklanjam kodirani siječanj, kliknem na ćeliju E3, dvaput ću pritisnuti F4 da je zaključam na red, 3 dolara. Upišite Actual, uklonite riječ Actual, kliknite E4, ponovno dvaput F4, u redu, i dobit ću formulu koja sada te podatke vraća natrag. Kopirat ću to, a zatim zalijepiti Special, odabrati Formati, alt = "" ESF, vidjeti da je F podvučen tamo, ESF Enter, a onda to, učinio sam, ponovit ću s F4, F4 je ponoviti, a F4. U redu, sada imamo lijep izvještaj, ima praznih mjesta, ima oblikovanje, ima jednu računovodstvenu podvlaku ispod svakog odjeljka,na samom dnu ima dvostruko računovodstveno podcrtavanje.
Tačno, ove stvari nikada ne stavite u pivot tablicu, to je nemoguće, ali ovo se izvješće pokreće iz pivot tablice. Pa, što onda radimo kad dobijemo aktualije iz svibnja, vratimo se ovdje, zalijepimo ih, osvježimo najružniju pivot tablicu na svijetu, a zatim ovdje na izvješću samo promijenimo datum trajanja sa 4/30 na 5/31. A ono što čini je to što uzrokuje da se ova formula prebaci s riječi Plan na Actual, koja ide i izvlači stvarne podatke iz izvještaja, umjesto plana, u redu. Eto, stvar je u tome … ovo je sjajno, zar ne? Vidim gdje bih to puno radio da sam i dalje, znate, radio u računovodstvu.
Morate biti jako oprezni ako grade novu trgovinu, morate je dodati ručno, zar ne, podaci će se prikazati u pivot tablici, ali biste ih dodali ručno. Sad je ovaj podskup svih trgovina, da izvještava o svim trgovinama, vjerojatno bih ovdje, izvan dometa ispisa, imao nešto što je izvuklo ukupni zbroj iz pivot tablice. I onda bih znao, ako se ovaj ukupan iznos ne podudara sa ukupnim zbrojem iz pivot tablice, da nešto nije u redu, i ovdje imam funkciju IF koji kaže „Hej, znate, dodani su novi podaci, budite vrlo oprezni. " Oni imaju nekakav mehanizam za otkrivanje postojanja novih podataka. Ali shvaćam, to je super upotreba. Dakle, dok nas većinu vremena GetPivotData samo izluđuje, od toga zapravo može biti koristi. U redu,Dakle, to je savjet br. 21 od 40 u knjizi, kupite knjigu odmah, naručite putem interneta, kliknite ono „i“ u gornjem desnom kutu.
Dugo, dugo rekapitiranje danas, u redu: GetPivotData se događa kada formula usmjerava unutar pivot tablice, a formula izvan pivot tablice pokazuje unutra. Iako je početna formula točna, neće se kopirati. Većina ljudi mrzi GetPivotData i želi ih spriječiti. Dakle, možete izgraditi formulu bez miša ili tipki sa strelicama, samo upišite formulu ili trajno isključite GetPivotData, ah, ali korist je, u redu. Stoga moramo sastaviti izvješće sa stvarnim podacima za prošli mjesec, proračunom za budućnost. Uobičajeni tijek rada, stvorite zaokretnu tablicu, pretvorite u vrijednosti, izbrišite stupce. Postoji način da uklonite međuzbrojeve pomoću Postavki polja, riješavajući se stvarnog plus plana za siječanj. Umjesto toga, mi ćemo samo stvoriti najružniju pivot tablicu na svijetu s previše podataka.
Izradite lijepo oblikovan, običan stari radni list izvještaja s možda malo logike da biste riječ Actual promijenili u Plan. A zatim iz prve ćelije izvješća, prvog mjesta na kojem će se brojevi nalaziti u tom izvješću, upišite =, idite na pivot tablicu i dopustite da se dogodi GetPivotData. Ispitujemo sintaksu GetPivotData, pa je to polje za povratak, Prodaja, gdje živi izvedena tablica, a zatim parovi kriterija, naziv polja i vrijednost. Uklonit ćemo kodiranu vrijednost i pokazati na ćeliju, pritiskom na F4 3 puta zaključava se samo stupac, pritiskom na F4 dva puta se zaključava samo redak, kopirajte tu formulu, zalijepite posebne formule. Tamo sam dao dodatni savjet da je F4 ponovljeni, pa sam samo jednom morao otvoriti dijaloški okvir Paste Special, a zatim za sljedeću Paste Special Formule upravo upotrijebio F4. Sljedeći mjesec dodajte podatke,osvježite pivot tablicu, promijenite datum. Pazite da nisu izgradili nove trgovine, znate, imaju li nekakav mehanizam, bilo ručni, bilo provjernu formulu, provjerite. Zahvaljujući iTrainerMX-u na Twitteru, koji je predložio GetPivotData, također Carlosu i Robu iz Microsofta, Robu sada iz Power Pivot Pro-a. Carlos što je to koristio, a Rob što mi je rekao da ga koristi, kasnije sam ga upoznao, a on je potvrdio da, bio je jedan od knjigovođa koji su to cijelo vrijeme koristili u Microsoftu, u redu, eto.i Roba što mi je rekao da ga Carlos koristi, kasnije sam ga upoznao, a on je potvrdio da, bio je jedan od računovođa koji je to cijelo vrijeme koristio u Microsoftu, u redu, eto.i Roba što mi je rekao da ga Carlos koristi, kasnije sam ga upoznao, a on je potvrdio da, bio je jedan od računovođa koji je to cijelo vrijeme koristio u Microsoftu, u redu, eto.
Pa, hej, želim vam zahvaliti što ste navratili, vidimo se sljedeći put za još jedan prijenos od!
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2013.xlsx
