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, for eksempel =D5/C5-1uten å 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 in Rows. Sett måned og skriv i kolonner. Du får rapporten vist nedenfor, med januar-faktisk, januar-plan og den helt meningsløse januar-faktiske + -planen.
Hvis du velger en månedecelle og går til feltinnstillinger, kan du endre delsummene 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 inne i 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 komprimerte 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 Faktisk 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.
Figuren nedenfor viser formelen etter at du har redigert den. Borte er "Baybrook", "Jan" og "Actual". I stedet peker du på $ D6, E $ 3 og E $ 4.
Kopier denne formelen og velg deretter Lim inn spesielle, formler i alle de andre numeriske cellene.
Her er din månedlige 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.
Du må innrømme at bruk av en 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.
