Hater du Excels GETPIVOTDATA-funksjon? Hvorfor vises det? Hvordan kan du forhindre det? Er det god bruk for GETPIVOTDATA?
De fleste møter først GETPIVOTDATA når de prøver å bygge en formel utenfor en pivottabell som bruker tall i pivottabellen. For eksempel vil denne variansprosenten ikke kopiere til de andre månedene på grunn av at Excel setter inn GETPIVOTDATA-funksjoner.
Excel setter inn GETPIVOTDATA når du bruker musen eller piltastene for å peke på en celle inne i pivottabellen mens du bygger en formel utenfor pivottabellen.
Forresten, hvis du ikke vil at GETPIVOTDATA-funksjonen skal vises, skriver du bare inn en formel som = D5 / C5-1 uten å bruke musen eller piltastene til å peke på celler. Formelen kopieres uten problemer.
Her er et datasett som inneholder ett plannummer per måned per butikk. Det er også faktisk salg per måned per butikk for månedene som er fullført. Målet ditt er å lage en rapport som viser faktiske forhold for de fullførte månedene og planlegger for de kommende månedene.
Bygg et pivottabell med Store i ROWS. Sett måned og skriv inn KOLONNENE. Du får rapporten vist nedenfor, med januar-faktisk, januar-plan og den helt meningsløse januar-faktiske + -planen.
Hvis du velger en månedscelle og går til feltinnstillinger, kan du endre delsummer til Ingen.
Dette fjerner den ubrukelige faktiske + planen. Men du må fortsatt kvitte deg med plankolonnene for januar til april. Det er ingen god måte å gjøre dette på innsiden av pivottabellen.
Så den månedlige arbeidsflyten blir:
- Legg til fakta for den nye måneden i datasettet.
- Bygg et nytt pivottabell fra bunnen av.
- Kopier pivottabellen og lim inn som verdier, så den ikke lenger er en pivottabell.
- Slett kolonnene du ikke trenger.
Det er en bedre vei å gå. Følgende veldig lille figur viser et nytt Excel-regneark lagt til arbeidsboken. Dette er bare rett Excel, ingen pivottabeller. Den eneste biten av magi er en IF-funksjon i rad 4 som bytter fra Faktisk til Plan basert på datoen i celle P1.
Den aller første cellen som må fylles ut er januar, faktisk for Baybrook. Klikk i den cellen og skriv et likhetstegn. Naviger tilbake til pivottabellen ved hjelp av musen. Finn cellen for januar-fakta for Baybrook. Klikk på cellen og trykk Enter. Som vanlig bygger Excel en av de irriterende GETPIVOTDATA-funksjonene som ikke kan kopieres.
Men i dag, la oss studere syntaksen til GETPIVOTDATA.
Det første argumentet nedenfor er det numeriske feltet "Salg". Det andre argumentet er cellen der pivottabellen ligger. De resterende parene av argumenter er feltnavn og verdi. Ser du hva den automatisk genererte formelen gjorde? Det er hardkodet "Baybrook" som navnet på butikken. Derfor kan du ikke kopiere disse automatisk genererte GETPIVOTDATA-formlene. De hardkoder navn til formler. Selv om du ikke kan kopiere disse formlene, kan du redigere dem. I dette tilfellet ville det være bedre hvis du redigerte formelen for å peke på celle $ D6.
Her er formelen etter at du har redigert den. Borte er "Baybrook", "Jan" og "Actual". I stedet peker du på $ D6, E $ 3, E $ 4.
Kopier denne formelen og velg deretter Lim inn spesielle, formler i alle de andre numeriske cellene.
Her er din årlige arbeidsflyt:
- Bygg et stygt pivottabell som ingen noen gang vil se.
- Sett opp rapportarket.
Hver måned må du:
- Lim inn nye fakta under dataene.
- Oppdater det stygge svingbordet.
-
Endre celle P1 på rapportarket for å gjenspeile den nye måneden. Alle tallene oppdateres.
Endre celle P1
Du må innrømme at bruk av en vanlig rapport som trekker tall fra en pivottabell, gir deg det beste fra begge verdener. Du kan formatere rapporten slik at du ikke kan formatere en pivottabell. Tomme rader er fine. Du kan ha valutasymboler på første og siste rad, men ikke mellom. Du får også dobbel understreking under totalsummene.
Takk til @iTrainerMX for å foreslå denne funksjonen.
Se på video
- GetPivotData skjer når en formel peker inne i en pivottabell
- Selv om den opprinnelige formelen er riktig, kan du ikke kopiere formelen
- De fleste hater getpivotdata og vil forhindre det
- Metode 1: Bygg en formel uten mus eller piltastene
- Metode 2: Slå av GetPivotData permanent ved hjelp av rullegardinmenyen ved siden av alternativene
- Men det er bruk for GetPivotData
- Din leder ønsker en rapport med Actuals for siste måneder og budsjett for fremtiden
- Den normale arbeidsflyten ville ha deg til å lage en pivottabell, konvertere til verdier, slette kolonner
- Fjerne delsummer for å forhindre januar-faktisk + plan ved hjelp av feltinnstillinger
- I stedet lager du en pivottabell med "for mye" data
- Bruk et pent formatert rapportark
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Fra den første datacellen på regnearket, bygg en formel med musen
- La GetPivotData skje
- Undersøk syntaksen til GetPivotData (felt for retur, pivotplassering, par)
- Endre den hardkodede verdien for å peke på en celle
- Ved å trykke F4 tre ganger låses bare kolonnen
- Ved å trykke F4 to ganger låses bare raden
- Lim inn spesielle formler
- Arbeidsflyt neste måned: Legg til data, oppdater pivottabellen, endre gjennom dato
- Svært nøye med å se opp for nye butikker
Transkripsjon av videoen
Lær Excel fra podcast, episode 2013 - GetPivotData kan ikke være helt ondt!
Jeg podcaster hele denne boken, klikker på det "i" øverst til høyre for å abonnere.
Greit, tilbake i episode 1998 snakket jeg kort om dette GetPivotData-problemet. Hvis vi beregner en% varians og vi er utenfor pivottabellen og peker på innsiden, og jeg bruker musen eller piltasten, så 2019 / 2018-1. Dette svaret vi kommer hit er riktig for januar, men når vi dobbeltklikker for å kopiere det ned, kopieres ikke formelen, vi får januar-svaret helt ned. Og når vi ser på det, får vi GetPivotData, jeg skrev ikke GetPivotData, jeg pekte bare på disse cellene, og dette begynte å skje tilbake i Excel 2002 uten noen advarsel. Og på det tidspunktet sa jeg at måten å unngå dette på er å skrive opp formelen C5 / B5-1, og du får en formel som du kan kopiere. Eller hvis du bare hater GetPivotData, hvis det er "helt ondt", gå til Analyser-fanen, ikke gjør dett åpne alternativknappen forresten. Gå tilbake til pivottabellen, gå til kategorien Analyser, åpne rullegardinmenyen ved siden av Alternativer, fjern merket for denne boksen, det er en global innstilling. Når du har slått den av, vil den være av for alltid, ok.
Ofte er spørsmålene jeg får “Hvordan slår jeg av GetPivotData?” men innimellom vil jeg få noen som elsker GetPivotData. Og jeg spiste lunsj med Rob Collie da han fremdeles var hos Microsoft, og han sa "Vel, våre interne kunder elsker GetPivotData." Jeg sa “Hva? Nei, alle hater GetPivotData! ” Rob sier "Du har rett, utenfor Microsoft, absolutt, de hater GetPivotData." Jeg snakker om regnskapsførerne i Microsoft, og senere møtte jeg en som nå jobber for Excel-teamet, Carlos, og Carlos var en av regnskapsførerne som bruker denne metoden.
OK, så her er hva vi må gjøre. Vi har rapporten vår, et datasett her som for hver måned vi har planen for hver butikk, og nederst akkumulerer vi faktiske fakta. Greit, så vi har faktisk for januar til desember, men bare for noen måneder, månedene som har gått. Og det som lederen vår vil at vi skal gjøre er å lage en rapport med butikker på venstre side, bare Texas-butikkene, selvfølgelig, for å gjøre livet vanskeligere. Og når vi går over, har vi måneder, og hvis vi har en faktisk for den måneden, viser vi den faktiske, så januar faktisk, februar faktisk, mars faktisk, april faktisk. Men så for månedene hvor vi ikke har faktiske ting, bytter vi over og viser budsjettet, så budsjetter ut i desember, og deretter totalt alt, ok. Vel, når du prøver å lage dette pivottabellen, ja,det fungerer ikke.
Så sett inn pivottabell, nytt regneark, du legger Store ned til venstre, den siden som er vakker, legg Måneder over toppen, legg Type over toppen, legg Salg her, ok. Så her er hva vi får som vi må begynne å jobbe med, så vi har januar-faktisk, januar-plan, og deretter den helt ubrukelige januar-faktiske pluss-planen. Ingen vil noen gang bruke dette, men jeg kan kvitte meg med disse grå kolonnene, det er enkelt nok, noen her til denne cellen, gå til Feltinnstillinger og endre delsummene til Ingen. Men det er absolutt ingen måte for meg å fjerne januarplanen som ikke vil fjerne april-mai-juni-juli-planen, ok, det er ingen måte å bli kvitt dette. Så på dette punktet hver måned sitter jeg fast med å velge hele pivottabellen, går til Kopier og deretter Lim inn, Lim inn verdier. Det er ikke et pivottabell lenger,og så begynner jeg manuelt å slette kolonnene som ikke vises i rapporten.
Ok, det er den vanlige metoden, men regnskapsførerne hos Microsoft har lagt til et ekstra trinn i januar, det tar 15 minutter, og dette trinnet gjør at denne pivottabellen kan leve for alltid, ikke sant? Jeg kaller dette verdens styggeste pivottabell, og regnskapsførerne hos Microsoft aksepterer at dette er verdens styggeste pivottabell, men ingen vil noen gang se denne rapporten bortsett fra dem. Det de gjør, er at de kommer hit til et nytt ark, og bygger rapporten som lederen deres ønsker. Ok, så her er butikkene på venstre side, jeg grupperte den til og med i Houston, Dallas og Other, det er en pent formatert rapport. Jeg har markert totalene, du vil se at når vi får inn noen tall, er det valuta på første rad, men ikke disse påfølgende radene, tomme rader. Ooh, blanke rader i pivottabellen.Og en liten bit av logikk her oppe, hvor jeg kan legge gjennom datoen i celle P1, og så har jeg en formel her som analyserer at HVIS måneden med gjennom datoen er> denne kolonnen, og deretter legger ordet Faktisk, ellers legg ordet Plan, ok. Så alt jeg trenger å gjøre er å endre dette gjennom datoen, og så snu ordet faktisk til plan, ok.
Nå, her er hva vi gjør, vi skal tillate oss å bli GetPivotData'd, ikke sant? Jeg er ikke sikker på at det er et verb, men vi skal tillate Microsoft å GetPivotData. Så jeg begynner å bygge en formel med en =, jeg tar tak i musen, og jeg skal se etter januar-faktiske Baybrook! Så jeg går tilbake til verdens styggeste pivottabell, jeg finner Baybrook, jeg finner januar, jeg finner faktisk, og jeg klikker på Enter, og lar dem gjøre det mot meg, ok, der har vi en GetPivotData-formel. Jeg husker dagen jeg gjorde dette, det var som, vet du, etter at Rob forklarte meg hva de gjorde, og jeg gikk tilbake og prøvde det. Nå har jeg plutselig, i hele mitt liv, blitt kvitt GetPivotData, jeg har faktisk aldri omfavnet GetPivotData. Så hva det er, er det første elementet er det vi leter etter, der 'et felt som heter Salg, det er her pivottabellen starter, og det kan være hvilken som helst celle i pivottabellen, de bruker øverst til venstre.
OK, dette er et feltnavn "Store", og så har de hardkodet "Baybrook", dette er et feltnavn "Måned", de har hardkodet "januar", dette er et feltnavn "Type", og de ' har hardkodet “Faktisk”. Det er derfor du ikke kan kopiere det, fordi de har kodet verdiene hardt. Men regnskapsførerne hos Microsoft, Carlos og hans medarbeidere innser at ”Whoa, vent et sekund, vi har ordet Baybrook her, vi har januar her, vi har Actual her. Vi trenger bare å endre denne formelen for å peke på de faktiske cellene i rapporten i stedet for å bli hardkodet. " Greit, så de kaller dette å parameterisere GetPivotData.
Fjern ordet Baybrook, kom hit og klikk på celle D6. Nå må jeg låse dette ned til kolonnen, ok, så jeg trykker på F4-tasten tre ganger, får en $ $ før D, ok. For januar måned fjerner jeg den hardkodede januar, jeg klikker på cellen E3, jeg trykker på F4 to ganger for å låse den ned til raden, E $ 3. Skriv Actual, fjern ordet Actual, klikk på E4, igjen F4 to ganger, ok, og jeg får en formel som nå trekker dataene tilbake. Jeg skal kopiere det, og deretter Lim inn spesial, velg Formater, alt = "" ESF, se F er understreket der, ESF Enter, og så nå, jeg har gjort det, jeg bare gjentar med F4, F4 er en gjenta, og F4. OK, så nå har vi en fin rapport, den har blanke, den har formatering, den har den eneste bokføringen under hver seksjon,helt nederst har den den doble bokføringen.
Ikke sant, du får aldri disse tingene i en pivottabell, det er umulig, men denne rapporten er hentet fra pivottabellen. Så det vi gjør når vi får mai-fakta, kommer tilbake hit, limer dem inn, oppdaterer verdens styggeste pivottabell, og så endrer du her på rapporten bare gjennomdatoen fra 4/30 til 5/31. Og hva det gjør er at denne formelen bytter fra ordet Plan til faktisk, som går og trekker fakta fra rapporten, i stedet for planen, ok. Nå, her er tingen at - dette er flott, ikke sant? Jeg kan se hvor jeg ville gjort dette mye hvis jeg fortsatt, vet du, jobbet med regnskap.
Det du må være veldig forsiktig med er at hvis de bygger en ny butikk, må du vite å legge den til manuelt, ikke sant, dataene kommer til å vises i pivottabellen, men du vil legge den til manuelt. Nå er denne en delmengde av alle butikkene. Hvis den rapporterte om alle butikkene, ville jeg sannsynligvis her ute, utenfor utskriftsområdet, ha noe som trakk den totale summen fra pivottabellen. Og så ville jeg vite, hvis denne summen ikke samsvarer med totalsummen fra pivottabellen, at noe er galt, og har en IF-funksjon her nede og sier "Hei, du vet, nye data som er lagt til, vær veldig forsiktig. ” De har en slags mekanisme for å oppdage at nye data er der. Men jeg skjønner det, det er en kul bruk. Så, mens det meste av tiden GetPivotData bare gjør oss gal, kan det faktisk være en bruk for det. Ok,så det er tips nr. 21 av 40 i boken, kjøp boka akkurat nå, bestill online, klikk på det "i" øverst til høyre.
Lang, lang oppsummering i dag, ok: GetPivotData skjer når en formel peker inne i et pivottabell, en formel utenfor pivottabellen peker inne. Selv om den opprinnelige formelen er riktig, kopieres den ikke. De fleste hater GetPivotData og vil forhindre det. Så du kan bygge en formel uten musen eller piltastene, bare skriv inn formelen eller slå av GetPivotData permanent, ah, men det er en bruk, ok. Så vi må lage en rapport med faktiske forhold for siste måned, budsjett for fremtiden. Normal arbeidsflyt, lag en pivottabell, konverter til verdier, slett kolonner. Det er en måte å fjerne delsummene ved å bruke feltinnstillinger, og bli kvitt den faktiske planen i januar. I stedet skal vi bare lage verdens styggeste pivottabell med for mye data.
Bygg et pent formatert, rett og slett gammelt rapportark med kanskje litt logikk for å endre ordet Faktisk til Plan. Og så fra den første rapportcellen, det første stedet hvor tallene skal være i den rapporten, skriver du inn =, peker på pivottabellen og lar GetPivotData skje. Vi undersøker syntaksen til GetPivotData, så det er feltet du skal returnere, Salg, der pivottabellen bor, og deretter par av kriterier, feltnavnet og verdien. Vi skal fjerne den hardkodede verdien og peke på en celle, ved å trykke på F4 tre ganger låses bare kolonnen, trykker du på F4 2 ganger låses bare raden, kopierer formelen, Lim inn spesielle formler. Jeg kastet inn et ekstra tips der om at F4 er en redo, så jeg måtte bare gå til Paste Special-dialogen en gang, og deretter for de neste Paste Special Formulas brukte jeg bare F4. Neste måned legg til dataene,oppdater pivottabellen, endre gjennomdatoen. Forsikre deg om at de ikke har bygget noen nye butikker, vet du, har en slags mekanisme, enten manuell eller en sjekkformel, sjekk den ut. Takk til iTrainerMX på Twitter, som foreslo GetPivotData, også Carlos og Rob fra Microsoft, Rob nå fra Power Pivot Pro. Carlos for å bruke dette, og Rob for å fortelle meg at Carlos brukte det, møtte jeg Carlos senere, og han bekreftet ja, han var en av regnskapsførerne som brukte dette hele tiden hos Microsoft, ok, der går du.og Rob for å fortelle meg at Carlos brukte den, jeg møtte Carlos senere, og han bekreftet ja, han var en av regnskapsførerne som brukte dette hele tiden hos Microsoft, ok, der går du.og Rob for å fortelle meg at Carlos brukte den, jeg møtte Carlos senere, og han bekreftet ja, han var en av regnskapsførerne som brukte dette hele tiden hos Microsoft, ok, der går du.
Vel hei, jeg vil takke deg for at du var innom, vi sees neste gang for nok en netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2013.xlsx
