Hader du Excels GETPIVOTDATA-funktion? Hvorfor vises det? Hvordan kan du forhindre det? Er der god brug af GETPIVOTDATA?
De fleste mennesker støder først på GETPIVOTDATA, når de prøver at opbygge en formel uden for en pivottabel, der bruger tal i pivottabellen. For eksempel kopieres denne variansprocent ikke ned til de andre måneder på grund af Excel, der indsætter GETPIVOTDATA-funktioner.
Excel indsætter GETPIVOTDATA, når du bruger musen eller piletasterne til at pege på en celle inde i pivottabellen, mens du bygger en formel uden for pivottabellen.
Forresten, hvis du ikke ønsker, at GETPIVOTDATA-funktionen skal vises, skal du blot skrive en formel som = D5 / C5-1 uden at bruge musen eller piletasterne til at pege på celler. Denne formel kopieres uden problemer.
Her er et datasæt, der indeholder et plannummer pr. Måned pr. Butik. Der er også faktisk salg pr. Måned pr. Butik i de måneder, der er færdige. Dit mål er at oprette en rapport, der viser faktiske forhold for de afsluttede måneder og planlægger de kommende måneder.
Byg et pivottabel med Store i ROWS. Indsæt måned og skriv KOLONNER. Du får rapporten vist nedenfor med den faktiske januarplan, januarplanen og den fuldstændig meningsløse faktiske januarplan +.
Hvis du vælger en månedscelle og går til feltindstillinger, kan du ændre subtotaler til Ingen.
Dette fjerner den ubrugelige faktiske + plan. Men du er stadig nødt til at slippe af med plan-kolonnerne for januar til april. Der er ingen god måde at gøre dette inde i drejebordet.
Så din månedlige arbejdsgang bliver:
- Føj faktiske data for den nye måned til datasættet.
- Byg et nyt drejetabel fra bunden.
- Kopier pivottabellen og indsæt som værdier, så den ikke længere er en pivottabel.
- Slet de kolonner, som du ikke har brug for.
Der er en bedre vej at gå. Den følgende meget lille figur viser et nyt Excel-regneark tilføjet til projektmappen. Dette er alt sammen lige Excel, ingen pivottabeller. Den eneste bit af magi er en IF-funktion i række 4, der skifter fra Faktisk til Plan baseret på datoen i celle P1.
Den allerførste celle, der skal udfyldes, er januar, faktisk for Baybrook. Klik i den celle, og skriv et lighedstegn. Naviger tilbage til pivottabellen ved hjælp af musen. Find cellen til januar-fakta for Baybrook. Klik på den celle, og tryk på Enter. Som normalt bygger Excel en af de irriterende GETPIVOTDATA-funktioner, der ikke kan kopieres.
Men i dag, lad os studere syntaksen for GETPIVOTDATA.
Det første argument nedenfor er det numeriske felt "Salg". Det andet argument er cellen, hvor pivottabellen ligger. De resterende par af argumenter er feltnavn og værdi. Kan du se, hvad den auto-genererede formel gjorde? Det er hårdkodet "Baybrook" som navnet på butikken. Derfor kan du ikke kopiere disse auto-genererede GETPIVOTDATA-formler. De faktisk hard-code navne i formler. Selvom du ikke kan kopiere disse formler, kan du redigere dem. I dette tilfælde ville det være bedre, hvis du redigerede formlen for at pege på celle $ D6.
Her er formlen, når du har redigeret den. Borte er "Baybrook", "Jan" og "Faktisk". I stedet peger du på $ D6, E $ 3, E $ 4.
Kopier denne formel, og vælg derefter Indsæt speciel, formler i alle de andre numeriske celler.
Her er din årlige arbejdsgang:
- Byg et grimt drejebord, som ingen nogensinde vil se.
- Konfigurer rapportarket.
Hver måned skal du:
- Indsæt nye fakta under dataene.
- Opdater det grimme drejebord.
-
Skift celle P1 på rapportarket for at afspejle den nye måned. Alle numre opdateres.
Skift celle P1
Du skal indrømme, at brug af en almindelig rapport, der trækker tal fra en drejetabel, giver dig det bedste fra begge verdener. Du kan frit formatere rapporten på måder, som du ikke kan formatere en pivottabel. Tomme rækker er fine. Du kan have valutasymboler på første og sidste række, men ikke imellem. Du får også dobbelt understreget under de samlede totaler.
Tak til @iTrainerMX for at foreslå denne funktion.
Se video
- GetPivotData sker, når en formel peger inde i en pivottabel
- Mens den oprindelige formel er korrekt, kan du ikke kopiere formlen
- De fleste hader getpivotdata og vil forhindre det
- Metode 1: Byg en formel uden musen eller piletasterne
- Metode 2: Sluk GetPivotData permanent ved hjælp af rullemenuen ud for indstillinger
- Men der er brug for GetPivotData
- Din leder ønsker en rapport med Actuals for de sidste måneder og et budget til fremtiden
- Den normale arbejdsgang ville have dig til at oprette en pivottabel, konvertere til værdier, slette kolonner
- Fjernelse af subtotaler for at forhindre januar-faktisk + plan ved hjælp af feltindstillinger
- Opret i stedet en pivottabel med "for meget" data
- Brug et pænt formateret rapportark
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Fra den første datacelle på regnearket skal du oprette en formel med musen
- Tillad GetPivotData at ske
- Undersøg syntaksen for GetPivotData (felt, der skal returneres, pivotplacering, par)
- Skift den hårdkodede værdi for at pege på en celle
- Ved at trykke F4 tre gange låses kun kolonnen
- Ved at trykke F4 to gange låses kun rækken
- Indsæt specielle formler
- Workflow næste måned: Tilføj data, opdater pivottabellen, skift gennem dato
- Ultra omhyggelig med at holde øje med nye butikker
Udskrift af videoen
Lær Excel fra podcast, afsnit 2013 - GetPivotData er muligvis ikke helt onde!
Jeg podcaster hele denne bog, klik på det "i" øverst til højre for at abonnere.
Okay, tilbage i episode 1998 talte jeg kort om dette GetPivotData-problem. Hvis vi beregner en% varians, og vi er uden for pivottabellen, der peger indeni, og jeg bruger musen eller piletasten, så 2019 / 2018-1. Dette svar, som vi får her, er korrekt for januar, men når vi dobbeltklikker for at kopiere det ned, kopieres formlen ikke, vi får januar-svaret helt ned. Og når vi ser på det, får vi GetPivotData, jeg skrev ikke GetPivotData, jeg pegede bare på disse celler, og dette begyndte at ske tilbage i Excel 2002 uden nogen som helst advarsel. Og på det tidspunkt sagde jeg, at måden at undgå dette er at skrive formlen C5 / B5-1, og du får en formel, som du kan kopiere. Eller hvis du bare hader GetPivotData, hvis det er "fuldstændig ondt", skal du gå til fanen Analyser, don 't åbner knappen Indstillinger forresten. Gå tilbage til pivottabellen, gå til fanen Analyser, åbn rullemenuen ud for Indstillinger, fjern markeringen i dette felt, det er en global indstilling. Når du slukker for det, vil det være slukket for evigt, okay.
Ofte er de spørgsmål, jeg får, “Hvordan slår jeg GetPivotData fra?” men af og til får jeg nogen, der elsker GetPivotData. Og jeg spiste frokost med Rob Collie, da han stadig var hos Microsoft, og han sagde "Nå, vores interne kunder elsker GetPivotData." Jeg sagde “Hvad? Nej, alle hader GetPivotData! ” Rob siger "Du har ret uden for Microsoft, de hader GetPivotData absolut." Jeg taler om revisorer inde i Microsoft, og senere mødte jeg en, der nu arbejder for Excel-teamet, Carlos, og Carlos var en af de revisorer, der bruger denne metode.
Okay, så her er hvad vi skal gøre. Vi har vores rapport, et datasæt her, der for hver måned har vi planen for hver butik, og derefter akkumulerer vi bunden i bunden. Okay, så vi har faktisk for januar til december, men kun for nogle få måneder, de måneder der er gået forbi. Og hvad vores manager ønsker, at vi skal gøre, er at oprette en rapport med butikker på venstre side, kun Texas-butikkerne, selvfølgelig, for at gøre livet vanskeligere. Og så når vi går over, har vi måneder, og hvis vi har en faktisk for den måned, viser vi den faktiske, så januar faktisk, februar faktisk, marts faktisk, april faktisk. Men så i de måneder, hvor vi ikke har faktisk, skifter vi over og viser budgettet, så budgetter igennem december, og derefter i alt alt i alt, okay. Når du prøver at oprette denne pivottabel, ja,det virker ikke.
Så indsæt pivottabel, nyt regneark, du lægger Butik ned til venstre, den side, der er smuk, læg måneder over toppen, læg Type over toppen, læg Salg her, okay. Så her er hvad vi får, som vi skal begynde at arbejde med, så vi har januar-januar, januar-plan og derefter den helt ubrugelige januar-faktiske plus-plan. Ingen vil nogensinde bruge dette, men jeg kan slippe af med disse grå kolonner, det er let nok, nogle her til denne celle, gå til feltindstillinger og ændre subtotaler til Ingen. Men der er absolut ingen måde for mig at fjerne januarplanen, der ikke også fjerner april maj juni juli-planen, okay, der er ingen måde at slippe af med dette. Så på dette tidspunkt hver måned sidder jeg fast ved at vælge hele pivottabellen, gå til Kopi og derefter Indsæt, Indsæt værdier. Det er ikke et Pivot-bord længere,og så begynder jeg manuelt at slette de kolonner, der ikke vises i rapporten.
Okay, det er den normale metode, men revisorerne hos Microsoft har tilføjet et ekstra trin i januar, det tager 15 minutter, og dette trin giver denne Pivot-tabel mulighed for at leve for evigt, ikke? Jeg kalder dette verdens grimeste pivottabel, og revisorerne hos Microsoft accepterer, at dette er verdens grimeste pivottabel, men ingen vil nogensinde se denne rapport undtagen dem. Hvad de gør, er at de kommer her til et nyt ark og bygger den rapport, som deres leder ønsker. Okay, så her er butikkerne på venstre side, jeg grupperede det endda i Houston, Dallas og Other, det er en pænt formateret rapport. Jeg har fremhævet totalerne, du vil se, at når vi får nogle tal ind, er der valuta på den første række, men ikke disse efterfølgende rækker, tomme rækker. Ooh, blanke rækker i pivottabellen.Og en lille smule logik heroppe, hvor jeg kan sætte gennemløbsdatoen i celle P1, og så har jeg en formel her, der analyserer, at HVIS måneden for den gennemgående dato er> denne kolonne, og derefter sætter ordet Faktisk, ellers sæt ordet Plan, okay. Så alt hvad jeg skal gøre, er at ændre dette gennem datoen, og derefter vendes ordet Faktisk over til plan, okay.
Nu, her er hvad vi gør, vi vil tillade os at blive GetPivotData'd, ikke? Jeg er ikke sikker på, at det er et verbum, men vi tillader Microsoft at GetPivotData. Så jeg begynder at opbygge en formel med en =, jeg tager fat i musen, og jeg skal se efter januar-faktiske Baybrook! Så jeg går tilbage til verdens grimeste pivottabel, jeg finder Baybrook, jeg finder januar, jeg finder faktisk, og jeg klikker på Enter, og lad dem gøre det mod mig, okay, der går vi, vi har nu en GetPivotData-formel. Jeg husker den dag, jeg gjorde det, det var som, ved du, efter at Rob havde forklaret mig, hvad de lavede, og jeg gik tilbage og prøvede det. Nu pludselig har jeg hele mit liv sluppet GetPivotData, jeg har faktisk aldrig taget GetPivotData. Så hvad det er, er det første element, det vi leder efter, der 'et felt kaldet salg, det er her pivottabellen starter, og det kan være en hvilken som helst celle i pivottabellen, de bruger den øverste venstre hånd.
Okay, dette er et feltnavn "Store", og så har de hårdkodet "Baybrook", dette er et feltnavn "Måned", de har hardkodet "januar", dette er et feltnavn "Type", og de ' har hårdkodet "Faktisk". Derfor kan du ikke kopiere det, fordi de har kodet værdierne hårdt. Men revisorerne hos Microsoft, Carlos og hans kolleger indser ”Whoa, vent et øjeblik, vi har ordet Baybrook her, vi har januar her, vi har Actual her. Vi skal bare ændre denne formel for at pege på de faktiske celler i rapporten i stedet for at blive hårdkodet. ” Okay, så de kalder dette for at parametrere GetPivotData.
Fjern ordet Baybrook, kom herover og klik på celle D6. Nu skal jeg låse dette ned i kolonnen, okay, så jeg trykker på F4-tasten 3 gange, får en enkelt $ før D, okay. I januar måned fjerner jeg den hardkodede januar, jeg klikker på cellen E3, jeg trykker to gange på F4 for at låse den ned til rækken, E $ 3. Skriv Faktisk, fjern ordet Faktisk, klik på E4, igen F4 to gange, okay, og jeg får en formel, der nu trækker disse data tilbage. Jeg vil kopiere det, og derefter indsætte specielt, vælg formater, alt = "" ESF, se F er understreget der, ESF Enter, og så nu, jeg har gjort det, gentager jeg bare med F4, F4 er en gentagelse, og F4. Okay, så nu har vi en pæn rapport, den har blanke, den har formatering, den har den enkelte regnskabsunderstregning under hvert afsnit,nederst har den dobbelt understreget regnskab.
Ret, du får aldrig disse ting i en pivottabel, det er umuligt, men denne rapport er drevet fra pivottabellen. Så hvad vi gør, når vi får maj-aktualiteterne, kommer tilbage her, indsætter dem i, opdaterer verdens grimeste pivottabel, og så her på rapporten skal du bare ændre gennemløbsdatoen fra 4/30 til 5/31. Og hvad det gør, er, at denne formel skifter fra ordet Plan til faktisk, som går og trækker det faktiske fra rapporten i stedet for planen, okay. Nu, her er den ting, at - dette er en stor, ikke? Jeg kan se, hvor jeg ville gøre dette meget, hvis jeg stadig, du ved, arbejdede med regnskab.
Den ting, du skal være meget forsigtig med, er, hvis de bygger en ny butik, skal du vide at tilføje den manuelt, højre, dataene vises i pivottabellen, men du tilføjer det manuelt. Nu er denne en delmængde af alle butikkerne, hvis den rapporterede om alle butikkerne, ville jeg sandsynligvis herude uden for printområdet have noget, der trak det samlede beløb fra Pivot-tabellen. Og så ville jeg vide, at hvis denne sum ikke svarer til det samlede tal fra pivottabellen, er der noget galt og har en IF-funktion hernede og siger ”Hej, du ved, nye data der er tilføjet, vær meget forsigtig. ” De har en slags mekanisme til at opdage, at nye data er der. Men jeg forstår det, det er en cool brug. Så selvom GetPivotData for det meste bare gør os vanvittige, kan der faktisk være brug for det. I orden,så det er tip nr. 21 ud af 40 i bogen, køb bogen lige nu, bestil online, klik på det "i" i øverste højre hjørne.
Lang, lang opsummering i dag, okay: GetPivotData sker, når en formel peger inde i en pivottabel, en formel uden for pivottabellen peger indeni. Mens den oprindelige formel er korrekt, kopieres den ikke. De fleste mennesker hader GetPivotData og vil forhindre det. Så du kan opbygge en formel uden musen eller piletasterne, bare skriv formlen eller slå GetPivotData fra permanent, ah, men der er brug, okay. Så vi er nødt til at oprette en rapport med faktiske forhold for den sidste måned, budget for fremtiden. Normal arbejdsgang, opret en pivottabel, konverter til værdier, Slet kolonner. Der er en måde at fjerne subtotaler ved at bruge feltindstillinger for at slippe af med den aktuelle januar-plan. I stedet opretter vi bare verdens grimeste pivottabel med for meget data.
Byg et pænt formateret, simpelt gammelt rapportark med måske en smule logik for at ændre ordet Faktisk til Plan. Og derefter fra den første rapportcelle, det første sted, hvor tal skal være i den rapport, skal du skrive an =, gå hen til pivottabellen og lade GetPivotData ske. Vi undersøger syntaksen for GetPivotData, så det er feltet, der skal returneres, Salg, hvor pivottabellen bor, og derefter par af kriterier, feltnavnet og værdien. Vi fjerner den hårdkodede værdi og peger på en celle, ved at trykke på F4 3 gange låses kun kolonnen, ved at trykke på F4 2 gange låses kun rækken, kopieres den formel, Indsæt specielle formler. Jeg kastede et ekstra tip der, at F4 er en gentagelse, så jeg måtte kun gå til dialogboksen Indsæt speciel en gang, og derefter til de næste Indsæt specialformler brugte bare F4. Næste måned tilføj dataene,opdater pivottabellen, skift gennem datoen. Sørg for, at de ikke har bygget nye butikker, du ved, har en slags mekanisme, enten manuel eller en checkformel, tjek den ud. Tak til iTrainerMX på Twitter, der foreslog GetPivotData, også Carlos og Rob fra Microsoft, Rob nu fra Power Pivot Pro. Carlos for at bruge dette, og Rob for at fortælle mig, at Carlos brugte det, jeg mødte Carlos senere, og han bekræftede ja, han var en af revisorerne, der brugte dette hele tiden hos Microsoft, okay, der går du.og Rob for at fortælle mig, at Carlos brugte det, mødte jeg Carlos senere, og han bekræftede ja, han var en af regnskabsførerne, der brugte dette hele tiden hos Microsoft, okay, der går du.og Rob for at fortælle mig, at Carlos brugte det, mødte jeg Carlos senere, og han bekræftede ja, han var en af regnskabsførerne, der brugte dette hele tiden hos Microsoft, okay, der går du.
Nå hej, jeg vil gerne takke dig for at komme forbi, vi ses næste gang til endnu en netcast fra!
Download fil
Download eksempelfilen her: Podcast2013.xlsx
