Slik legger du til og bruker Excel PivotTable Calculated Fields

Vanligvis, når du har opprettet en pivottabell, må du utvide analysen og inkludere flere data/beregninger i den.

Hvis du trenger et nytt datapunkt som kan fås ved hjelp av et eksisterende datapunkt i pivottabellen, er det ikke nødvendig å gå tilbake og legge det til kildedataene.I stedet kan du brukePivottabell beregnede feltå gjøre dette.

Hva er et beregnet pivottabellfelt?

La oss starte med et grunnleggende eksempel på en pivottabell.

Anta at du har et datasett med forhandlere og du lager en pivottabell som dette:

Pivottabellen ovenfor oppsummerer forhandlerens salgs- og fortjenesteverdier.

Nå, hva om du også vil vite hva disse forhandlernes fortjenestemarginer er (fortjenestemarginen er "profit" delt på "salg")?

Det er flere måter å gjøre dette på:

1. Gå tilbake til det opprinnelige datasettet og legg til dette nye datapunktet.Så du kan sette inn en ny kolonne i kildedataene og beregne fortjenestemarginen i den.Når dette er gjort, må du oppdatere pivottabellens kildedata for å få denne nye kolonnen som en del av den.
   • Mens denne tilnærmingen fungerer, må du manuelt gå tilbake til datasettet og gjøre beregningene.Det kan for eksempel være lurt å legge til en annen kolonne for å beregne gjennomsnittlig salg per enhet (salg/antall).Igjen, du må legge til denne kolonnen i kildedataene og deretter oppdatere pivottabellen.
   • Denne metoden blåser også opp pivottabellen når du legger til nye data til den.
2. Legg til beregninger utenfor pivottabeller.Velg dette alternativet hvis pivottabellstrukturen din sannsynligvis ikke endres.Men hvis du endrer pivottabellen, kan det hende at beregningene ikke oppdateres tilsvarende og kan gi deg feil resultater eller feil.Som vist nedenfor regnet jeg ut fortjenestemarginen når det er forhandlere i rekken.Men når jeg endrer det fra kunde til område, blir formelen feil.
3. Beregn felt ved hjelp av pivottabeller.Dette erBruk eksisterende pivottabelldata og beregnmest effektive måten.Tenk på beregnede felt som virtuelle kolonner som du legger til ved å bruke eksisterende kolonner i pivottabellen.Å bruke pivottabellberegnede felt har mange fordeler (som vi skal se senere):
   • Det krever ikke at du jobber med formler eller oppdaterer kildedata.
   • Det er utvidbart fordi det automatisk tar hensyn til alle nye data du kan legge til i pivottabellen.Etter å ha lagt til et beregnet felt, kan du bruke det som alle andre felt i en pivottabell.
   • Det er enkelt å oppdatere og administrere.For eksempel, hvis en beregning endres eller du trenger å endre en beregning, kan du enkelt gjøre det fra selve pivottabellen.

Legg til beregnede felt i pivottabellen

La oss se hvordan du legger til et pivottabellberegnet felt til en eksisterende pivottabell.

Anta at du har en pivottabell som ser slik ut, og du vil beregne fortjenestemarginen for hver forhandler:

Her er trinnene for å legge til et pivottabellberegnet felt:

Etter å ha lagt til det beregnede feltet, vil det vises som et av feltene i listen over pivottabellfelt.

Du kan nå bruke dette beregnede feltet som et hvilket som helst annet pivottabellfelt (merk at du ikke kan bruke et beregnet pivottabellfelt som et rapportfilter eller slicer).

Som jeg nevnte før, er fordelen med å bruke et pivottabellberegnet felt at du kan endre strukturen til pivottabellen og den vil justere seg automatisk.

For eksempel, hvis jeg drar og slipper en region inn i radområdet, vil du få resultater som følgende, hvor fortjenestemarginverdiene for forhandleren og regionen rapporteres.

 

I eksemplet ovenfor brukte jeg en enkel formel (=Profitt/Salg) for å sette inn det beregnede feltet.Du kan imidlertid også bruke noen avanserte formler.

Før jeg viser deg et eksempel på bruk av en avansert formel for å lage et PivotTable-beregnet felt, her er noen ting du må vite:

• Du kan ikke bruke referanser eller navngitte områder når du oppretter pivottabellberegnede felt.Dette vil ekskludere mange formler som VLOOKUP, INDEX, OFFSET, etc.Du kan imidlertid bruke formler som fungerer uten referanser (f.eks. SUM, HVIS, ANTALL osv...).
• Du kan bruke konstanter i formler.Hvis du for eksempel ønsker å vite den anslåtte salgsveksten som forventes å øke med 10 %, ville du bruke formelen =Salg*1.1 (hvor 1.1 er en konstant).
• Følg prioritetsrekkefølge i formler som genererer beregnede felt.Som en beste praksis, bruk parenteser for å sikre at du ikke trenger å huske forrang.

La oss nå se på et eksempel på å lage et beregnet felt ved hjelp av en avansert formel.

Anta at du har datasettet vist nedenfor, og du må vise det anslåtte salget i en pivottabell.

For prognoseverdier vil du bruke en salgsøkning på 300 % for store forhandlere (over 5 millioner salg) og en salgsøkning på 300 % for små og mellomstore forhandlere (mindre enn 10 millioner salg).

Merk: Salgstallene her er falske og har blitt brukt til å illustrere eksemplene i denne opplæringen.

Slik gjør du det:

Dette vil legge til en ny kolonne i pivottabellen med salgsprognoseverdier.

Pivottabell beregnet feltproblem

Kalkulerte felt er en fantastisk funksjon som virkelig øker verdien av pivottabeller med feltberegninger, samtidig som alt er skalerbart og håndterbart.

Det er imidlertid et problem med PivotTable-beregnede felt som du må forstå før du bruker dem.

Anta at jeg har en pivottabell som nedenfor, og jeg bruker et beregnet felt for å få prognosesalgstall.

Merk at delsummer og totaler er feil.

Selv om disse bør legge til separate salgsprognoseverdier for hver forhandler, følger den i realiteten den samme beregnede feltformelen vi opprettet.

Så for South Total, mens verdien skal være 22,824,000 22,287,000 XNUMX, rapporterer South Total den feilaktig som XNUMX XNUMX XNUMX.Dette skjer fordi den bruker formelen 21,225,800 1.05 XNUMX*XNUMX for å få verdien.

Dessverre kan du ikke rette opp dette problemet.

Den beste måten å håndtere dette på er å fjerne delsummer og totalsummer fra pivottabellen.

Du kan også bestå Presentert av DebraNoen innovative løsninger for å håndtere dette problemet.

Hvordan endre eller slette et pivottabellberegnet felt?

Etter at du har opprettet et pivottabellberegnet felt, kan du endre formelen eller slette den ved å bruke følgende trinn:

Hvordan få en liste over alle beregnede feltformler?

Hvis du oppretter mange pivottabellberegnede felt, trenger du ikke å bekymre deg for å holde styr på formlene som brukes i hvert felt.

Excel lar deg raskt lage en liste over alle formler som brukes til å lage beregnede felt.

Her er trinnene for raskt å få en liste over alle beregnede feltformler:

• Velg en hvilken som helst celle i pivottabellen.
• Gå til Pivottabellverktøy -> Analyser -> Felter, elementer og sett -> Liste formler.

Når du klikker på listeformelen, vil Excel automatisk sette inn et nytt regneark med detaljer om alle de beregnede feltene/elementene du brukte i pivottabellen.

Dette kan være et veldig nyttig verktøy hvis du må sende arbeid til en klient eller dele det med teamet ditt.