Hatar du Excels GETPIVOTDATA-funktion? Varför visas det? Hur kan du förhindra det? Finns det en bra användning för GETPIVOTDATA?
De flesta människor möter först GETPIVOTDATA när de försöker bygga en formel utanför en pivottabell som använder siffror i pivottabellen. Till exempel kommer denna variansprocent inte att kopieras ner till de andra månaderna på grund av att Excel infogar GETPIVOTDATA-funktioner.
Excel infogar GETPIVOTDATA när du använder musen eller piltangenterna för att peka på en cell inuti pivottabellen medan du bygger en formel utanför pivottabellen.
Förresten, om du inte vill att GETPIVOTDATA-funktionen ska visas, skriv helt enkelt en formel som = D5 / C5-1 utan att använda musen eller piltangenterna för att peka på celler. Den formeln kopieras utan problem.
Här är en datamängd som innehåller ett plannummer per månad och butik. Det finns också faktisk försäljning per månad per butik för de månader som är färdiga. Ditt mål är att skapa en rapport som visar faktiska faktum för de avslutade månaderna och plan för de kommande månaderna.
Bygg ett pivottabell med Store i ROWS. Sätt in månad och skriv in KOLONNEN. Du får rapporten som visas nedan, med januari-faktiskt, januari-plan och den helt meningslösa januari-faktiska + -planen.
Om du väljer en månadscell och går till fältinställningar kan du ändra delsummor till Ingen.
Detta tar bort den värdelösa faktiska + planen. Men du måste fortfarande bli av med plankolonnerna för januari till april. Det finns inget bra sätt att göra detta inuti svängbordet.
Så ditt månatliga arbetsflöde blir:
- Lägg till fakta för den nya månaden i datamängden.
- Bygg ett nytt vridbord från grunden.
- Kopiera pivottabellen och klistra in som värden så att den inte längre är en pivottabell.
- Ta bort de kolumner som du inte behöver.
Det finns ett bättre sätt att gå. Följande mycket lilla figur visar ett nytt Excel-kalkylblad lagt till arbetsboken. Det här är bara rakt Excel, inga pivottabeller. Det enda magiska är en IF-funktion i rad 4 som växlar från Faktisk till Plan baserat på datumet i cell P1.
Den allra första cellen som måste fyllas i är januari, faktiskt för Baybrook. Klicka i den cellen och skriv ett likhetstecken. Navigera tillbaka till pivottabellen med musen. Hitta cellen för januari-fakta för Baybrook. Klicka på den cellen och tryck på Enter. Som vanligt bygger Excel en av de irriterande GETPIVOTDATA-funktionerna som inte kan kopieras.
Men idag, låt oss studera syntaxen för GETPIVOTDATA.
Det första argumentet nedan är det numeriska fältet "Försäljning". Det andra argumentet är cellen där pivottabellen finns. Återstående parpar är fältnamn och värde. Ser du vad den automatiskt genererade formeln gjorde? Det hårdkodade "Baybrook" som namnet på butiken. Det är därför du inte kan kopiera dessa auto-genererade GETPIVOTDATA-formler. De hårdkodar faktiskt namn i formler. Även om du inte kan kopiera dessa formler kan du redigera dem. I det här fallet skulle det vara bättre om du redigerade formeln för att peka på cell $ D6.
Här är formeln efter att du har redigerat den. Borta är "Baybrook", "Jan" och "Actual". Istället pekar du på $ D6, E $ 3, E $ 4.
Kopiera den här formeln och välj sedan Klistra in special, formler i alla andra numeriska celler.
Här är ditt årliga arbetsflöde:
- Bygg ett fult svängbord som ingen någonsin kommer att se.
- Ställ in rapportens kalkylblad.
Varje månad måste du:
- Klistra in nya fakta under data.
- Uppdatera det fula svängbordet.
-
Ändra cell P1 på rapportbladet för att återspegla den nya månaden. Alla siffror uppdateras.
Byt cell P1
Du måste erkänna att användning av en vanlig rapport som drar siffror från en pivottabell ger dig det bästa från två världar. Du kan formatera rapporten så att du inte kan formatera en pivottabell. Tomma rader är bra. Du kan ha valutasymboler på första och sista raden men inte däremellan. Du får också dubbla understrykningar under totalsummorna.
Tack till @iTrainerMX för att du föreslog den här funktionen.
Kolla på video
- GetPivotData händer när en formel pekar inuti en pivottabell
- Medan den ursprungliga formeln är korrekt kan du inte kopiera formeln
- De flesta hatar getpivotdata och vill förhindra det
- Metod 1: Bygg en formel utan mus eller piltangenter
- Metod 2: Stäng av GetPivotData permanent med rullgardinsmenyn bredvid alternativ
- Men det finns en användning för GetPivotData
- Din chef vill ha en rapport med Actuals för de senaste månaderna och en budget för framtiden
- I det normala arbetsflödet skulle du skapa en pivottabell, konvertera till värden, radera kolumner
- Ta bort delsummor för att förhindra faktiska januari-plan med hjälp av fältinställningar
- Skapa istället en pivottabell med "för mycket" data
- Använd ett snyggt formaterat rapportark
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- Från den första datacellen i kalkylbladet, bygg en formel med musen
- Låt GetPivotData hända
- Undersök syntaxen för GetPivotData (fält för att returnera, pivotplats, par)
- Ändra det hårdkodade värdet så att det pekar på en cell
- Om du trycker på F4 tre gånger låses endast kolumnen
- Att trycka två gånger på F4 låser bara raden
- Klistra in speciella formler
- Arbetsflöde nästa månad: Lägg till data, uppdatera pivottabellen, ändra genom datum
- Ultra noga med att se upp för nya butiker
Avskrift av videon
Lär dig Excel från podcast, avsnitt 2013 - GetPivotData kanske inte är helt ondt!
Jag podcastar hela denna bok, klicka på det "i" i det övre högra hörnet för att prenumerera.
Okej, tillbaka i avsnitt 1998 pratade jag kort om detta GetPivotData-problem. Om vi beräknar en varians i procent och vi befinner oss utanför pivottabellen och pekar inuti, och jag använder musen eller piltangenten, så 2019 / 2018-1. Det här svaret som vi kommer att få här är korrekt för januari, men när vi dubbelklickar för att kopiera det ner, kopieras inte formeln, vi får januari-svaret hela vägen ner. Och när vi tittar på det får vi GetPivotData, jag skrev inte GetPivotData, jag pekade bara på dessa celler, och detta började hända tillbaka i Excel 2002 utan någon varning alls. Och vid den tiden sa jag att sättet att undvika detta är att skriva upp formeln C5 / B5-1, och du får en formel som du kan kopiera. Eller om du bara hatar GetPivotData, om det är "helt ont", gå till fliken Analysera, intet öppna alternativknappen förresten. Gå tillbaka till pivottabellen, gå till fliken Analysera, öppna rullgardinsmenyn bredvid Alternativ, avmarkera den här rutan, det är en global inställning. När du stänger av den kommer den att vara avstängd för alltid, okej.
Frågorna jag får är oftast ”Hur stänger jag av GetPivotData?” men då och då får jag någon som älskar GetPivotData. Och jag åt lunch med Rob Collie när han fortfarande var på Microsoft, och han sa "Tja, våra interna kunder älskar GetPivotData." Jag sa ”Vad? Nej, alla hatar GetPivotData! ” Rob säger "Du har rätt, utanför Microsoft, absolut, de hatar GetPivotData." Jag pratar om revisorerna i Microsoft, och senare träffade jag en som nu arbetar för Excel-teamet, Carlos, och Carlos var en av revisorerna som använder den här metoden.
Okej, så här är vad vi måste göra. Vi har vår rapport, en datauppsättning här som för varje månad vi har planen för varje butik, och sedan längst ner samlar vi fakta. Okej, så vi har fakta för januari till december, men har bara faktiska under några månader, de månader som gått. Och vad vår chef vill att vi ska göra är att bygga en rapport med butiker på vänster sida, bara Texas-butikerna förstås för att göra livet svårare. Och sedan går vi över har vi månader, och om vi har en faktisk för den månaden, visar vi den faktiska, så januari faktiska, februari faktiska, mars faktiska, april faktiska. Men sedan för de månader där vi inte har faktiskt byter vi över och visar budgeten, så budgetera fram till december, och sedan totalt totalt allt, okej. När du försöker skapa den här pivottabellen, ja,det fungerar inte.
Så sätt in pivottabell, nytt kalkylblad, du lägger butik ner till vänster, den sida som är vacker, lägg månader överst, lägg typ överst, lägg försäljning här, okej. Så här är vad vi får som vi måste börja arbeta med, så vi har januari-faktiska, januari-plan och sedan den helt värdelösa januari-faktiska plus-planen. Ingen kommer någonsin att använda det här, men jag kan bli av med de grå kolumnerna, det är lätt nog, en del här till den här cellen, gå till Fältinställningar och ändra delsummorna till Ingen. Men det finns absolut inget sätt för mig att ta bort januari-planen som inte tar bort april-maj-juli-juli-planen, okej, det finns inget sätt att bli av med detta. Så vid denna punkt varje månad sitter jag fast med att välja hela pivottabellen, går till Kopiera och sedan Klistra in, klistra in värden. Det är inte ett pivottabell längre,och sedan börjar jag manuellt radera de kolumner som inte visas i rapporten.
Okej, det är den vanliga metoden, men revisorerna på Microsoft har lagt till ett extra steg i januari, det tar 15 minuter, och det här steget gör att denna pivottabell kan leva för alltid, eller hur? Jag kallar detta världens fulaste pivottabell, och revisorerna på Microsoft accepterar att detta är världens fulaste pivottabell, men ingen kommer någonsin att se denna rapport förutom dem. Vad de gör är att de kommer hit till ett nytt ark och bygger den rapport som deras chef vill ha. Okej, så här är butikerna på vänster sida, jag grupperade det till och med i Houston, Dallas och Other, det är en snyggt formaterad rapport. Jag har markerat de totala, du kommer att se att när vi får in några siffror finns det valuta på den första raden, men inte dessa efterföljande rader, tomma rader. Ooh, tomma rader i pivottabellen.Och en liten bit av logik här uppe, där jag kan lägga genomgångsdatum i cell P1, och sedan har jag en formel här som analyserar att OM månaden för genomgångsdatum är> den här kolumnen och sedan sätter ordet Faktiskt, annars sätt ordet Plan, okej. Så allt jag behöver göra har ändrat detta genom datum, och sedan vänder ordet Faktiskt över till plan, okej.
Nu, här är vad vi gör, vi kommer att tillåta oss att bli GetPivotData'd, eller hur? Jag är inte säker på att det är ett verb, men vi kommer att tillåta Microsoft att GetPivotData. Så jag börjar bygga en formel med en =, jag tar tag i musen, och jag ska leta efter januari faktiska Baybrook! Så jag går tillbaka in i världens fulaste pivottabell, jag hittar Baybrook, jag hittar januari, jag hittar faktisk, och jag klickar på Enter och låter dem göra det mot mig, okej, nu har vi en GetPivotData-formel. Jag kommer ihåg dagen då jag gjorde det, det var som, förstår du, efter att Rob förklarat för mig vad de gjorde, och jag gick tillbaka och provade det. Nu har jag plötsligt, hela mitt liv, blivit av med GetPivotData, jag har faktiskt aldrig omfamnat GetPivotData. Så vad det är, är det första objektet är det vi letar efter, där 'ett fält som heter Försäljning, det är här pivottabellen börjar, och det kan vara vilken cell som helst i pivottabellen, de använder den övre vänstra handen.
Okej, det här är ett fältnamn "Store", och sedan har de kodat "Baybrook", det här är ett fältnamn "Månad", de har kodat "Januari", det här är ett fältnamn "Type", och de " har hårdkodat ”Faktiskt”. Det är därför du inte kan kopiera det, eftersom de har kodat värdena hårt. Men revisorerna hos Microsoft, Carlos och hans medarbetare inser ”Whoa, vänta en sekund, vi har ordet Baybrook här, vi har januari här, vi har Actual här. Vi behöver bara ändra den här formeln för att peka på de faktiska cellerna i rapporten istället för att vara hårdkodade. ” Okej, så de kallar detta för att parametrisera GetPivotData.
Ta bort ordet Baybrook, kom hit och klicka på cell D6. Nu måste jag låsa detta i kolumnen, okej, så jag trycker på F4-tangenten tre gånger, får en enda $ före D, okej. För januari månad tar jag bort den hårdkodade januari, jag klickar på cellen E3, jag trycker två gånger på F4 för att låsa ner den på raden, E $ 3. Skriv Actual, ta bort ordet Actual, klicka på E4, igen F4 två gånger, okej, och jag får en formel som nu drar tillbaka data. Jag kommer att kopiera det och sedan klistra in Special, välj Formater, alt = "" ESF, se F är understruken där, ESF Enter, och nu har jag gjort det, jag upprepar bara med F4, F4 är en gör om och F4. Okej, så nu har vi en snygg rapport, den har tomma, den har formatering, den har den enda bokföringen under varje avsnitt,längst ner har den dubbla bokföringen.
Okej, du får aldrig de här sakerna i en pivottabell, det är omöjligt, men den här rapporten drivs från pivottabellen. Så vad vi gör när vi får maj-fakta, kommer tillbaka hit, klistrar in dem, uppdaterar världens fulaste pivottabell och ändrar bara här i rapporten genomgående datum från 4/30 till 5/31. Och vad det gör är att denna formel växlar över från ordet Plan till Actual, som går och drar fakta från rapporten, istället för planen, okej. Nu är det här som - det här är bra, eller hur? Jag kan se var jag skulle göra det mycket om jag fortfarande, du vet, arbetade med redovisning.
Det du måste vara riktigt försiktig med är om de bygger en ny butik, du måste veta att lägga till den manuellt, eller hur, data kommer att visas i pivottabellen, men du skulle lägga till den manuellt. Nu är den här en delmängd av alla butiker, om den rapporterar alla butiker, skulle jag förmodligen här ute, utanför utskriftsområdet, ha något som drog summan från pivottabellen. Och då skulle jag veta, om denna summa inte överensstämmer med totalsumman från pivottabellen, att något är fel och har en IF-funktion här nere och säger ”Hej, du vet, nya data som har lagts till, var mycket försiktiga. ” De har någon form av en mekanism för att upptäcka att nya data finns där. Men jag förstår det, det är en cool användning. Så, medan GetPivotData för det mesta bara gör oss galna, kan det faktiskt vara en användning för det. OK,så det är tips nr 21 av 40 i boken, köp boken just nu, beställ online, klicka på det "i" i det övre högra hörnet.
Lång, lång sammanfattning idag, okej: GetPivotData händer när en formel pekar inuti en pivottabell, en formel utanför pivottabellen pekar inuti. Medan den ursprungliga formeln är korrekt kommer den inte att kopieras. De flesta hatar GetPivotData och vill förhindra det. Så du kan bygga en formel utan musen eller piltangenterna, skriv bara formeln eller stäng av GetPivotData permanent, ah, men det finns en användning, okej. Så vi måste bygga en rapport med faktiska uppgifter för den senaste månaden, budget för framtiden. Normalt arbetsflöde, skapa en pivottabell, konvertera till värden, radera kolumner. Det finns ett sätt att ta bort delsummorna med hjälp av fältinställningar och bli av med den faktiska planen i januari. Istället ska vi bara skapa världens fulaste pivottabell med för mycket data.
Bygg ett snyggt formaterat, helt enkelt gammalt rapportark med kanske lite logik för att ändra ordet Faktiskt till Plan. Och sedan från den första rapportcellen, den första platsen där siffrorna kommer att finnas i den rapporten, skriv an =, gå peka på pivottabellen och låt GetPivotData hända. Vi undersöker syntaxen för GetPivotData, så det är fältet att returnera, Försäljning, där pivottabellen bor och sedan par av kriterier, fältnamnet och värdet. Vi ska ta bort det hårdkodade värdet och peka på en cell, genom att trycka på F4 tre gånger låses endast kolumnen, trycker på F4 två gånger låses bara raden, kopierar den formeln, Klistra in speciella formler. Jag slängde in ett extra tips där att F4 är en gör om, så jag var bara tvungen att gå till Paste Special-dialogen en gång, och sedan för nästa Paste Special Formulas använde bara F4. Nästa månad lägg till data,uppdatera pivottabellen, ändra genomgångsdatum. Se till att de inte byggde några nya butiker, du vet, har någon form av en mekanism, antingen manuell eller en checkformel, kolla in den. Tack till iTrainerMX på Twitter, som föreslog GetPivotData, även Carlos och Rob från Microsoft, Rob nu från Power Pivot Pro. Carlos för att han använde det här och Rob för att han sa att Carlos använde det, jag träffade Carlos senare, och han bekräftade ja, han var en av revisorerna som använde detta hela tiden på Microsoft, okej.och Rob för att han berättade att Carlos använde det, jag träffade Carlos senare, och han bekräftade ja, han var en av revisorerna som använde det här hela tiden på Microsoft, okej.och Rob för att han berättade att Carlos använde det, jag träffade Carlos senare, och han bekräftade ja, han var en av revisorerna som använde det här hela tiden på Microsoft, okej.
Tja hej, jag vill tacka dig för att du stannade, vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2013.xlsx
