✅ Så här lägger du till och använder ett Excel -pivottabellberäknat fält

Innehållsförteckning

Vad är ett pivottabellberäknat fält?
Lägga till ett beräknat fält i pivottabellen
Ett problem med pivottabellberäknade fält
Hur ändrar eller tar jag bort ett pivottabellberäknat fält?
Hur får man en lista över alla beräknade fältformler?

Ofta, när du väl har skapat en pivottabell, behöver du utöka din analys och inkludera mer data/beräkningar som en del av den.

Om du behöver en ny datapunkt som kan erhållas med hjälp av befintliga datapunkter i pivottabellen behöver du inte gå tillbaka och lägga till den i källdatan. Istället kan du använda en Pivottabell beräknat fält att göra detta.

Ladda ner datasetet och följ med.

Vad är ett pivottabellberäknat fält?

Låt oss börja med ett grundläggande exempel på ett pivottabell.

Anta att du har en datamängd med återförsäljare och att du skapar en pivottabell enligt nedan:

Ovanstående pivottabell sammanfattar försäljnings- och vinstvärdena för detaljhandlarna.

Vad händer om du också vill veta vad som var vinstmarginalen för dessa återförsäljare (där vinstmarginalen är "Vinst" dividerat med "Försäljning").

Det finns ett par sätt att göra detta:

Gå tillbaka till den ursprungliga datamängden och lägg till den nya datapunkten. Så du kan infoga en ny kolumn i källdatan och beräkna vinstmarginalen i den. När du har gjort detta måste du uppdatera källdata för pivottabellen för att få den här nya kolumnen som en del av den.
- Även om denna metod är en möjlighet, måste du manuellt gå tillbaka till datamängden och göra beräkningarna. Till exempel kan du behöva lägga till en annan kolumn för att beräkna den genomsnittliga försäljningen per enhet (försäljning/kvantitet). Återigen måste du lägga till den här kolumnen i dina källdata och sedan uppdatera pivottabellen.
- Den här metoden uppblåser också din pivottabell när du lägger till ny data till den.
Lägg till beräkningar utanför pivottabellen. Detta kan vara ett alternativ om din pivottabellstruktur inte kommer att förändras. Men om du ändrar pivottabellen kanske beräkningen inte uppdateras därefter och kan ge dig fel resultat eller fel. Som visas nedan beräknade jag vinstmarginalen när det fanns återförsäljare i raden. Men när jag ändrade det från kunder till regioner gav formeln ett fel.
Använda ett pivottabellberäknat fält. Det här är mest effektiva sättet att använda befintliga pivottabelldata och beräkna önskat mått. Betrakta beräknat fält som en virtuell kolumn som du har lagt till med hjälp av de befintliga kolumnerna från pivottabellen. Det finns många fördelar med att använda ett pivottabellberäknat fält (som vi ser om en minut):
- Det kräver inte att du hanterar formler eller uppdaterar källdata.
- Det är skalbart eftersom det automatiskt kommer att redogöra för alla nya data som du kan lägga till i din pivottabell. När du har lagt till ett beräkningsfält kan du använda det som alla andra fält i din pivottabell.
- Det är enkelt att uppdatera och hantera. Om till exempel mätvärdena ändras eller om du behöver ändra beräkningen kan du enkelt göra det från själva pivottabellen.

Lägga till ett beräknat fält i pivottabellen

Låt oss se hur du lägger till ett pivottabellberäknat fält i en befintlig pivottabell.

Antag att du har en pivottabell som visas nedan och du vill beräkna vinstmarginalen för varje återförsäljare:

Här är stegen för att lägga till ett pivottabellberäknat fält:

Markera vilken cell som helst i pivottabellen.
Gå till Pivottabellverktyg -> Analysera -> Beräkningar -> Fält, objekt och uppsättningar.
Välj Beräknat fält i listrutan.
I dialogrutan Infoga beräknad fil:
- Ge det ett namn genom att ange det i fältet Namn.
- I formulärfältet skapar du den formel du vill ha för det beräknade fältet. Observera att du kan välja bland fältnamnen nedanför. I det här fallet är formeln ‘= Vinst/ försäljning’. Du kan antingen ange fältnamnen manuellt eller dubbelklicka på fältnamnet som anges i rutan Fält.
Klicka på Lägg till och stäng dialogrutan.

Så snart du lägger till det beräknade fältet visas det som ett av fälten i listan med pivottabellfält.

Nu kan du använda detta beräknade fält som alla andra pivottabellfält (observera att du inte kan använda pivottabellberäknat fält som rapportfilter eller skivare).

Som jag nämnde tidigare är fördelen med att använda ett pivottabellberäknat fält att du kan ändra strukturen på pivottabellen och det kommer att justeras automatiskt.

Om jag till exempel drar och släpper region i raderna får du resultatet enligt nedan, där vinstmarginalvärdet rapporteras för såväl återförsäljare som regionen.

I exemplet ovan har jag använt en enkel formel (= vinst/försäljning) för att infoga ett beräknat fält. Men du kan också använda några avancerade formler.

Innan jag visar dig ett exempel på hur du använder en avancerad formel för att skapa ett pivottabellberäkningsfält, här är några saker du måste veta:

Du KAN INTE använda referenser eller namngivna intervall när du skapar ett pivottabellberäknat fält. Det skulle utesluta många formler som VLOOKUP, INDEX, OFFSET och så vidare. Du kan dock använda formler som kan fungera utan referenser (t.ex. SUMMA, OM, RÄKNA osv.).
Du kan använda en konstant i formeln. Om du till exempel vill veta den prognostiserade försäljningen där den beräknas växa med 10%kan du använda formeln = Försäljning*1.1 (där 1.1 är konstant).
Ordningsföljden följs i formeln som gör det beräknade fältet. Som bästa praxis använder du parentes för att se till att du inte behöver komma ihåg prioritetsordningen.

Låt oss nu se ett exempel på att använda en avancerad formel för att skapa ett beräknat fält.

Anta att du har datauppsättningen enligt nedan och du måste visa det prognostiserade försäljningsvärdet i pivottabellen.

För prognostiserat värde måste du använda en försäljningsökning på 5% för stora detaljister (försäljning över 3 miljoner) och en försäljningsökning på 10% för små och medelstora detaljhandlare (försäljning under 3 miljoner).

Obs! Försäljningsnumren här är falska och har använts för att illustrera exemplen i den här självstudien.

Så här gör du:

Markera vilken cell som helst i pivottabellen.
Gå till Pivottabellverktyg -> Analysera -> Beräkningar -> Fält, objekt och uppsättningar.
Välj Beräknat fält i listrutan.
I dialogrutan Infoga beräknad fil:
- Ge det ett namn genom att ange det i fältet Namn.
- I formulärfältet använder du följande formel: = OM (Region = ”Syd”, Försäljning *1.05, Försäljning *1.1)
Klicka på Lägg till och stäng dialogrutan.

Detta lägger till en ny kolumn i pivottabellen med försäljningsprognosvärdet.

Klicka här för att ladda ner datasetet.

Ett problem med pivottabellberäknade fält

Beräknat fält är en fantastisk funktion som verkligen ökar värdet på din pivottabell med fältberäkningar, samtidigt som allt är skalbart och hanterbart.

Det finns dock ett problem med pivottabellberäknade fält som du måste känna till innan du använder det.

Antag att jag har en pivottabell som visas nedan där jag använde det beräknade fältet för att få prognosförsäljningsnumren.

Observera att delsumman och totalsumman inte är korrekta.

Även om dessa bör lägga till det individuella försäljningsprognosvärdet för varje återförsäljare, följer det i själva verket samma beräknade fältformel som vi skapade.

Så för South Total, medan värdet bör vara 22 824 000, rapporterar South Total fel det som 22 287 000. Detta händer när det använder formeln 21,225,800*1,05 för att få värdet.

Tyvärr finns det inget sätt att korrigera detta.

Det bästa sättet att hantera detta skulle vara att ta bort subtotaler och totalsummor från ditt pivottabell.

Du kan också gå igenom några innovativa lösningar som Debra har visat för att hantera detta problem.

Hur ändrar eller tar jag bort ett pivottabellberäknat fält?

När du har skapat ett pivottabellberäknat fält kan du ändra formeln eller ta bort den med hjälp av följande steg:

Markera vilken cell som helst i pivottabellen.
Gå till Pivottabellverktyg -> Analysera -> Beräkningar -> Fält, objekt och uppsättningar.
Välj Beräknat fält i listrutan.
I fältet Namn klickar du på rullgardinspilen (liten nedåtpilen i slutet av fältet).
I listan väljer du det beräknade fältet du vill radera eller ändra.
Ändra formeln om du vill ändra den eller klicka på Ta bort om du vill ta bort den.

Hur får man en lista över alla beräknade fältformler?

Om du skapar mycket Pivottabellberäknat fält, oroa dig inte för att hålla reda på formeln som används i var och en av dem.

Med Excel kan du snabbt skapa en lista över alla formler som används för att skapa beräknade fält.

Här är stegen för att snabbt få listan över alla beräknade fältformler:

Markera vilken cell som helst i pivottabellen.
Gå till Pivottabellverktyg -> Analysera -> Fält, objekt och uppsättningar -> Lista formler.

Så snart du klickar på Lista formler, skulle Excel automatiskt infoga ett nytt kalkylblad med detaljer om alla beräknade fält/objekt som du har använt i pivottabellen.