Sådan tilføjes og bruges et Excel -pivottabelberegnet felt

Ofte, når du opretter en pivottabel, er der brug for at udvide din analyse og inkludere flere data/beregninger som en del af den.

Hvis du har brug for et nyt datapunkt, der kan opnås ved hjælp af eksisterende datapunkter i pivottabellen, behøver du ikke gå tilbage og tilføje det i kildedataene. I stedet kan du bruge en Pivottabel Beregnet felt at gøre dette.

Download datasættet og følg med.

Hvad er et pivottabelberegnet felt?

Lad os starte med et grundlæggende eksempel på en pivottabel.

Antag, at du har et datasæt med forhandlere, og du opretter en pivottabel som vist herunder:

Ovenstående pivottabel opsummerer salgs- og fortjenstværdierne for detailhandlerne.

Hvad nu hvis du også vil vide, hvad var fortjenstmargenen for disse detailhandlere (hvor overskudsgraden er 'Profit' divideret med 'Sales').

Der er et par måder at gøre dette på:

1. Gå tilbage til det originale datasæt, og tilføj dette nye datapunkt. Så du kan indsætte en ny kolonne i kildedataene og beregne fortjenstmargenen i den. Når du har gjort dette, skal du opdatere kildedataene i pivottabellen for at få denne nye kolonne som en del af den.
   • Selvom denne metode er en mulighed, skal du manuelt gå tilbage til datasættet og foretage beregningerne. For eksempel skal du muligvis tilføje en anden kolonne for at beregne det gennemsnitlige salg pr. Enhed (salg/mængde). Igen bliver du nødt til at tilføje denne kolonne til dine kildedata og derefter opdatere pivottabellen.
   • Denne metode svulmer også op i din pivottabel, mens du tilføjer nye data til den.
2. Tilføj beregninger uden for pivottabellen. Dette kan være en mulighed, hvis din pivottabelstruktur sandsynligvis ikke ændres. Men hvis du ændrer pivottabellen, opdateres beregningen muligvis ikke i overensstemmelse hermed og kan give dig de forkerte resultater eller fejl. Som vist herunder beregnede jeg fortjenstmargenen, når der var detailhandlere i rækken. Men da jeg ændrede det fra kunder til regioner, gav formlen en fejl.
3. Brug af et pivottabelberegnet felt. Dette er mest effektive måde at bruge eksisterende pivottabeldata og beregne den ønskede metrik. Betragt Beregnet felt som en virtuel kolonne, som du har tilføjet ved hjælp af de eksisterende kolonner fra pivottabellen. Der er mange fordele ved at bruge et pivottabelberegnet felt (som vi ser om et minut):
   • Det kræver ikke, at du håndterer formler eller opdaterer kildedata.
   • Det er skalerbart, da det automatisk tager højde for alle nye data, som du kan tilføje til din pivottabel. Når du har tilføjet et Beregn felt, kan du bruge det som ethvert andet felt i din pivottabel.
   • Det er let at opdatere og administrere. For eksempel, hvis metrics ændres, eller du skal ændre beregningen, kan du nemt gøre det fra selve pivottabellen.

Tilføjelse af et beregnet felt til pivottabellen

Lad os se, hvordan du tilføjer et pivottabelberegnet felt i en eksisterende pivottabel.

Antag, at du har en pivottabel som vist herunder, og du vil beregne fortjenstmargenen for hver forhandler:

Her er trinene til at tilføje et pivottabelberegnet felt:

• Vælg en hvilken som helst celle i pivottabellen.
• Gå til pivottabelværktøjer -> analyse -> beregninger -> felter, poster og sæt.
• Vælg Beregnet felt i rullemenuen.
• I dialogboksen Indsæt beregnet fil:
  • Giv det et navn ved at indtaste det i feltet Navn.
  • Opret den ønskede formel for det beregnede felt i feltet Formel. Bemærk, at du kan vælge mellem de feltnavne, der er angivet nedenfor. I dette tilfælde er formlen '= Fortjeneste/ salg'. Du kan enten indtaste feltnavnene manuelt eller dobbeltklikke på det feltnavn, der er angivet i feltet Felter.
• Klik på Tilføj og luk dialogboksen.

Så snart du tilføjer det beregnede felt, vises det som et af felterne på listen med pivottabelfelter.

Nu kan du bruge dette beregnede felt som ethvert andet pivottabelfelt (bemærk, at du ikke kan bruge pivottabelberegnet felt som rapportfilter eller udsnit).

Som jeg nævnte før, er fordelen ved at bruge et pivottabelberegnet felt, at du kan ændre strukturen i pivottabellen, og den justeres automatisk.

For eksempel, hvis jeg trækker og slipper region i rækkeområdet, får du resultatet som vist herunder, hvor fortjenstmargenværdi rapporteres for detailhandlere såvel som regionen.

I eksemplet ovenfor har jeg brugt en simpel formel (= Fortjeneste/salg) til at indsætte et beregnet felt. Du kan dog også bruge nogle avancerede formler.

Inden jeg viser dig et eksempel på at bruge en avanceret formel til at oprette et pivottabelberegningsfelt, er her nogle ting, du skal vide:

• Du KAN IKKE bruge referencer eller navngivne områder, mens du opretter et pivottabelberegnet felt. Det ville udelukke mange formler som VLOOKUP, INDEX, OFFSET og så videre. Du kan dog bruge formler, der kan fungere uden referencer (f.eks. SUM, HVIS, TÆL og så videre …).
• Du kan bruge en konstant i formlen. Hvis du f.eks. Vil vide det forventede salg, hvor det forventes at vokse med 10%, kan du bruge formlen = Salg*1.1 (hvor 1.1 er konstant).
• Prioritetsrækkefølgen følges i formlen, der udgør det beregnede felt. Som en god praksis skal du bruge parenteser for at sikre, at du ikke behøver at huske rækkefølgen.

Lad os nu se et eksempel på at bruge en avanceret formel til at oprette et beregnet felt.

Antag, at du har datasættet som vist herunder, og du skal vise den forventede salgsværdi i pivottabellen.

For forventet værdi skal du bruge en salgsstigning på 5% for store detailhandlere (salg over 3 millioner) og en stigning på 10% for små og mellemstore detailhandlere (salg under 3 millioner).

Bemærk: Salgsnumrene her er falske og er blevet brugt til at illustrere eksemplerne i denne vejledning.

Sådan gør du dette:

• Vælg en hvilken som helst celle i pivottabellen.
• Gå til pivottabelværktøjer -> analyse -> beregninger -> felter, poster og sæt.
• Vælg Beregnet felt i rullemenuen.
• I dialogboksen Indsæt beregnet fil:
  • Giv det et navn ved at indtaste det i feltet Navn.
  • I feltet Formel skal du bruge følgende formel: = HVIS (Region = ”Syd”, Salg *1.05, Salg *1.1)
• Klik på Tilføj og luk dialogboksen.

Dette tilføjer en ny kolonne til pivottabellen med salgsforudsigelsesværdien.

Klik her for at downloade datasættet.

Et problem med pivottabelberegnede felter

Beregnet felt er en fantastisk funktion, der virkelig forbedrer værdien af ​​din pivottabel med feltberegninger, mens den stadig holder alt skalerbart og håndterbart.

Der er imidlertid et problem med pivottabelberegnede felter, som du skal kende, før du bruger det.

Antag, at jeg har en pivottabel som vist herunder, hvor jeg brugte det beregnede felt til at få de prognostiserede salgstal.

Bemærk, at delsummen og totalsummen ikke er korrekte.

Selvom disse skulle tilføre den enkelte salgsforudsigelsesværdi for hver forhandler, følger den i virkeligheden den samme beregnede feltformel, som vi oprettede.

Så for South Total, mens værdien skulle være 22.824.000, rapporterer South Total det fejlagtigt som 22.287.000. Dette sker, da det bruger formlen 21,225,800*1,05 for at få værdien.

Desværre er der ingen måde du kan rette dette på.

Den bedste måde at håndtere dette på er ved at fjerne subtotaler og Grand Totals fra dit pivottabel.

Du kan også gennemgå nogle innovative løsninger, Debra har vist at håndtere dette problem.

Sådan ændres eller slettes et pivottabelberegnet felt?

Når du har oprettet et pivottabelberegnet felt, kan du ændre formlen eller slette den ved hjælp af følgende trin:

• Vælg en hvilken som helst celle i pivottabellen.
• Gå til pivottabelværktøjer -> analyse -> beregninger -> felter, poster og sæt.
• Vælg Beregnet felt i rullemenuen.
• I feltet Navn skal du klikke på rullemenuen (lille pil nedad for enden af ​​feltet).
• Vælg det beregnede felt, du vil slette eller redigere, på listen.
• Skift formlen, hvis du vil ændre den, eller klik på Slet, hvis du vil slette den.

Hvordan får man en liste over alle de beregnede feltformler?

Hvis du opretter en masse pivottabelberegnet felt, skal du ikke bekymre dig om at holde styr på formlen, der bruges i hver af dem.

Excel giver dig mulighed for hurtigt at oprette en liste over alle de formler, der bruges til at oprette beregnede felter.

Her er trinene til hurtigt at få listen over alle beregnede felter formler:

• Vælg en hvilken som helst celle i pivottabellen.
• Gå til pivottabelværktøjer -> analyse -> felter, poster og sæt -> listeformler.

Så snart du klikker på Listeformler, vil Excel automatisk indsætte et nyt regneark med detaljer om alle de beregnede felter/elementer, du har brugt i pivottabellen.

Dette kan være et virkelig nyttigt værktøj, hvis du skal sende dit arbejde til klienten eller dele det med dit team.

Du kan også finde følgende vejledninger i pivottabeller nyttige:

• Klargøring af kildedata til pivottabel.
• Brug af skiver i Excel -pivottabel: En begyndervejledning.
• Sådan grupperes datoer i pivottabeller i Excel.
• Sådan grupperes tal i pivottabel i Excel.
• Sådan filtreres data i en pivottabel i Excel.
• Sådan udskiftes tomme celler med nuller i Excel -pivottabeller.
• Sådan anvendes betinget formatering i en pivottabel i Excel.
• Pivot -cache i Excel - hvad er det, og hvordan bruges det bedst?