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-1utan 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 in Rows. Sätt månad och skriv i kolumner. 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 pivottabellen.
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 komprimerade figur visar ett nytt Excel-kalkylblad som läggs till i 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 Actual 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 Faktisk 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.
Figuren nedan visar formeln efter att du redigerat den. Borta är "Baybrook", "Jan" och "Actual". Istället pekar du på $ D6, E $ 3 och E $ 4.
Kopiera den här formeln och välj sedan Klistra in special, formler i alla andra numeriska celler.
Här är ditt månatliga 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.
Du måste erkänna att användning av en rapport som drar siffror från en pivottabell ger dig det bästa från båda 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.
