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 f.eks. =D5/C5-1Uden 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 en pivottabel med Store in Rows. Sæt måned og skriv i 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 pivottabellen.
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å. Følgende meget komprimerede figur viser et nyt Excel-regneark fø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 Faktisk 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.
Figuren nedenfor viser formlen, når du har redigeret den. Borte er "Baybrook", "Jan" og "Faktisk". I stedet peger du på $ D6, E $ 3 og E $ 4.
Kopier denne formel, og vælg derefter Indsæt speciel, formler i alle de andre numeriske celler.
Her er din månedlige 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.
Du skal indrømme, at brug af en rapport, der trækker tal fra en pivottabel, 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.
