Slik legger du til og bruker et Excel -pivottabellberegnet felt

Ofte, når du oppretter en pivottabell, er det behov for å utvide analysen og inkludere flere data/beregninger som en del av den.

Hvis du trenger et nytt datapunkt som kan hentes ved å bruke eksisterende datapunkter i pivottabellen, trenger du ikke gå tilbake og legge det til i kildedataene. I stedet kan du bruke en Pivottabell beregnet felt å gjøre dette.

Last ned datasettet og følg med.

Hva er et pivottabellberegnet felt?

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

Anta at du har et datasett med forhandlere, og at du oppretter et pivottabell som vist nedenfor:

Ovenstående pivottabell oppsummerer salgs- og resultatverdiene for forhandlerne.

Hva om du også vil vite hva som var fortjenestemarginen til disse forhandlerne (hvor fortjenestemarginen er "Profitt" delt på "Salg").

Det er et par 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 du har gjort dette, må du oppdatere kildedataene i pivottabellen for å få denne nye kolonnen som en del av den.
   • Selv om denne metoden er en mulighet, må du manuelt gå tilbake til datasettet og gjøre beregningene. For eksempel må du kanskje legge til en annen kolonne for å beregne gjennomsnittlig salg per enhet (salg/mengde). Igjen må du legge denne kolonnen til kildedataene og deretter oppdatere pivottabellen.
   • Denne metoden oppblåser også pivottabellen din når du legger til nye data.
2. Legg til beregninger utenfor pivottabellen. Dette kan være et alternativ hvis pivottabellstrukturen din usannsynlig vil endre seg. Men hvis du endrer pivottabellen, kan det hende at beregningen ikke oppdateres tilsvarende og kan gi deg feil resultater eller feil. Som vist nedenfor, beregnet jeg fortjenestemarginen når det var forhandlere i rekken. Men da jeg endret det fra kunder til regioner, ga formelen en feil.
3. Bruke et pivottabellberegnet felt. Dette er mest effektive måten å bruke eksisterende pivottabelldata og beregne ønsket beregning. Vurder Beregnet felt som en virtuell kolonne som du har lagt til ved hjelp av de eksisterende kolonnene fra pivottabellen. Det er mange fordeler med å bruke et pivottabellberegnet felt (som vi ser om et minutt):
   • Det krever ikke at du håndterer formler eller oppdaterer kildedata.
   • Den er skalerbar, da den automatisk tar hensyn til alle nye data du kan legge til i pivottabellen. Når du har lagt til et beregningsfelt, kan du bruke det som alle andre felt i pivottabellen.
   • Det er enkelt å oppdatere og administrere. For eksempel, hvis beregningene endres eller du må endre beregningen, kan du enkelt gjøre det fra selve pivottabellen.

Legge til et beregnet felt i pivottabellen

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

Anta at du har en pivottabell som vist nedenfor, og du vil beregne fortjenestemarginen for hver forhandler:

Her er trinnene for å legge til et beregnet felt for pivottabell:

• Velg en celle i pivottabellen.
• Gå til Pivottabellverktøy -> Analyser -> Beregninger -> Felt, elementer og sett.
• Velg Beregnet felt fra rullegardinmenyen.
• I dialogboksen Sett inn kalkulert fil:
  • Gi det et navn ved å skrive det inn i feltet Navn.
  • I formelen -feltet oppretter du formelen du vil ha for det beregnede feltet. Vær oppmerksom på at du kan velge blant feltnavnene nedenfor. I dette tilfellet er formelen ‘= Fortjeneste/ salg’. Du kan enten angi feltnavnene manuelt eller dobbeltklikke på feltnavnet som er oppført i feltet Felt.
• Klikk på Legg til og lukk dialogboksen.

Så snart du legger til det beregnede feltet, vises det som et av feltene i pivottabellfeltlisten.

Nå kan du bruke dette beregnede feltet som alle andre pivottabellfelt (merk at du ikke kan bruke pivottabellberegnet felt som rapportfilter eller skiver).

Som jeg nevnte tidligere, er fordelen med å bruke et pivottabellberegnet felt at du kan endre strukturen til pivottabellen, og den vil automatisk justeres.

For eksempel, hvis jeg drar og slipper regionen i radområdet, får du resultatet som vist nedenfor, hvor fortjenestemarginverdien rapporteres for forhandlere så vel som regionen.

I eksemplet ovenfor har jeg brukt en enkel formel (= fortjeneste/salg) for å sette inn et beregnet felt. Du kan imidlertid også bruke noen avanserte formler.

Før jeg viser deg et eksempel på bruk av en avansert formel for å lage et pivottabellberegningsfelt, er det noen ting du må vite:

• Du KAN IKKE bruke referanser eller navngitte områder mens du oppretter et pivottabellberegnet felt. Det vil utelukke mange formler som VLOOKUP, INDEX, OFFSET, og så videre. Du kan imidlertid bruke formler som kan fungere uten referanser (for eksempel SUMMER, HVIS, TELLING og så videre …).
• Du kan bruke en konstant i formelen. For eksempel, hvis du vil vite det prognostiserte salget der det forventes å vokse med 10%, kan du bruke formelen = Salg*1.1 (der 1.1 er konstant).
• Prioritetsrekkefølgen følges i formelen som utgjør det beregnede feltet. Som en god praksis, bruk parentes for å sikre at du ikke trenger å huske rekkefølgen.

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

Anta at du har datasettet som vist nedenfor, og du må vise den forventede salgsverdien i pivottabellen.

For prognostisert verdi må du bruke en salgsøkning på 5% for store forhandlere (salg over 3 millioner) og en salgsøkning på 10% for små og mellomstore detaljister (salg under 3 millioner).

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

Slik gjør du dette:

• Velg en celle i pivottabellen.
• Gå til Pivottabellverktøy -> Analyser -> Beregninger -> Felt, elementer og sett.
• Velg Beregnet felt fra rullegardinmenyen.
• I dialogboksen Sett inn kalkulert fil:
  • Gi det et navn ved å skrive det inn i feltet Navn.
  • I Formel -feltet bruker du følgende formel: = HVIS (Region = ”Sør”, Salg *1.05, Salg *1.1)
• Klikk på Legg til og lukk dialogboksen.

Dette legger til en ny kolonne i pivottabellen med salgsvarsel.

Klikk her for å laste ned datasettet.

Et problem med pivottabellberegnede felt

Beregnet felt er en fantastisk funksjon som virkelig forbedrer verdien av pivottabellen med feltberegninger, mens den fortsatt holder alt skalerbart og håndterbart.

Det er imidlertid et problem med pivottabellberegnede felt som du må vite før du bruker det.

Anta at jeg har en pivottabell som vist nedenfor der jeg brukte det beregnede feltet for å få prognosene for salgstall.

Vær oppmerksom på at delsummen og totalsummen ikke er riktige.

Selv om disse bør legge til den individuelle salgsprognosverdien for hver forhandler, følger den i virkeligheten den samme beregnede feltformelen som vi opprettet.

Så for South Total, mens verdien skal være 22 824 000, rapporterer South Total det feilaktig som 22 287 000. Dette skjer når den bruker formelen 21,225,800*1,05 for å få verdien.

Dessverre er det ingen måte du kan korrigere dette på.

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

Du kan også gå gjennom noen innovative løsninger som Debra har vist å håndtere dette problemet.

Hvordan endre eller slette et pivottabellberegnet felt?

Når du har opprettet et pivottabellberegnet felt, kan du endre formelen eller slette den ved å følge disse trinnene:

• Velg en celle i pivottabellen.
• Gå til Pivottabellverktøy -> Analyser -> Beregninger -> Felt, elementer og sett.
• Velg Beregnet felt fra rullegardinmenyen.
• I Navn-feltet klikker du på rullegardinpilen (liten pil nedover på slutten av feltet).
• Velg det beregnede feltet du vil slette eller endre fra listen.
• Endre formelen hvis du vil endre den, eller klikk på Slett hvis du vil slette den.

Hvordan få en liste over alle de beregnede feltformlene?

Hvis du oppretter mange pivottabellberegnet felt, ikke bekymre deg for å holde oversikt over formelen som brukes i hver av dem.

Excel lar deg raskt lage en liste over alle formlene som brukes for å lage beregnede felt.

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

• Velg en celle i pivottabellen.
• Gå til Pivottabellverktøy -> Analyser -> Felt, elementer og sett -> Listeformler.

Så snart du klikker på Listeformler, vil Excel automatisk sette inn et nytt regneark som inneholder detaljene for alle de beregnede feltene/elementene du har brukt i pivottabellen.

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

Du kan også finne følgende opplæringsprogrammer for pivottabeller:

• Klargjøre kildedata for pivottabell.
• Bruke skiver i Excel -pivottabell: En nybegynnerguide.
• Slik grupperer du datoer i pivottabeller i Excel.
• Hvordan gruppere tall i pivottabell i Excel.
• Slik filtrerer du data i en pivottabell i Excel.
• Hvordan erstatte tomme celler med nuller i Excel -pivottabeller.
• Slik bruker du betinget formatering i et pivottabell i Excel.
• Pivot -cache i Excel - hva er det og hvordan bruker du det best?