Pivot Table Formula i Excel (innehållsförteckning)
- Pivot Table Formula i Excel
- Anpassat fält för att beräkna vinstmängd
- Avancerad formel i beräknat fält
Pivot Table Formula i Excel
Pivot Table är ett verktyg som gör att vi kan analysera stora dataintervall. Vi kan analysera, tolka och göra många andra saker utan att bryta mycket av vårt huvud och svett. Det kan ge nästan allt som finns där i källdata.
Om en del av informationen finns i källdata kan vi behöva beräkna detta för oss själva. Om vi till exempel har ett totalt försäljningsbelopp och total kostnad kan vi behöva beräkna den totala vinsten eller förlusten på egen hand.
Vi behöver inte göra detta i källan men vi kan göra detta inuti pivottabellen, dessa kallas Beräknade fält inuti pivottabellen. Vi kan använda anpassade formler för att göra data för att berätta mer historia från data. Beräknade fält tillåter oss att bygga en ny beräknad kolumn som inte finns i den faktiska datakällan.
I den här artikeln kommer vi att demonstrera sätten att använda Pivot Table Beräknade fält för att bygga nya kolumner baserat på vårt krav.
Anpassat fält för att beräkna vinstmängd
Du kan ladda ner denna Pivot Table Formula Excel-mall här - Pivot Table Formula Excel MallDetta är det mest använda beräknade fältet i pivottabellen. Titta på nedanstående data, jag har landnamn, produktnamn, sålda enheter, enhetspris, bruttoförsäljning, COGS (kostnad för sålda varor), datum och årskolumn.
Låt mig tillämpa pivottabellen för att hitta den totala försäljningen och den totala kostnaden för varje land. Nedan är pivottabellen för ovanstående data.
Problemet är att jag inte har en vinstkolumn i källdata. Jag måste ta reda på vinsten och vinstprocenten för varje land. Vi kan lägga till dessa två kolumner i själva pivottabellen.
Steg 1: Välj en cell i pivottabellen. Gå till fliken Analys i bandet och välj fält, objekt och uppsättningar. Välj det beräknade fältet under detta.
Steg 2: Ange ett namn på det nya beräknade fältet i dialogrutan nedan.
Steg 3: Använd formeln i formeln för att hitta vinsten. Formeln för att hitta vinsten är brutto försäljning - COGS.
Gå in i formelfältet> Välj Brutto försäljning från fältet nedan och dubbelklicka på det kommer att visas i formelfältet.
Skriv nu minussymbolen (-) och välj COGS> Dubbelklicka.
Steg 4: Klicka på ADD och OK för att slutföra formeln.
Steg 5: Nu har vi vår totala vinstkolumn i pivottabellen.
Detta beräknade fält är flexibelt, det är inte bara begränsat till landsvis analys, men vi kan använda detta för all slags analys. Om jag vill se analysen landsvis och produktvis måste jag bara dra och släppa produktkolumnen till ROW-fältet och det kommer att visa vinstdelningen för varje produkt under varje land.
Steg 6: Nu måste vi beräkna vinstprocenten. Formeln för att beräkna vinstprocenten är total vinst / brutto försäljning.
Gå till Analysera och välj igen Beräknat fält under Fält, Objekt och uppsättningar.
Steg 7: Nu måste vi se det nyinförda beräknade fältet Total vinst i fältlistan. Sätt in detta fält i formeln.
Steg 8: Skriv in divideringssymbolen (/) och sätt in Brutto försäljningsfält.
Steg 9: Namnge det beräknade fältet som vinstprocent.
Steg 10: Klicka på ADD och OK för att slutföra formeln. Vi har vinstprocent som den nya kolumnen.
Avancerad formel i beräknat fält
Vad jag nu har visat är de grundläggande sakerna i Beräknat fält. I det här exemplet visar jag dig de avancerade formlerna i beräknade fält för pivottabellen. Nu vill jag beräkna incitamentsbeloppet baserat på vinstprocenten.
Om vinstprocenten är> 15% ska incitamentet vara 6% av den totala vinsten.
Om vinsten% är> 10% incitament bör vara 5% av den totala vinsten.
Om vinsten% är <10% ska incitamentet vara 3% av den totala vinsten.
Steg 1: Gå till Beräknat fält och öppna dialogrutan nedan. Ge namnet som incitamentbelopp.
Steg 2: Nu kommer jag att använda IF-villkoret för att beräkna incitamentsbeloppet. Använd formlerna nedan som visas på bilden.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Steg 3: Klicka på ADD & OK för att slutföra. Nu har vi en Incitamentbelopp-kolumn.
Begränsning av beräknat fält
Vi har sett undrarna om beräknade fält men det har några av begränsningarna också. Titta nu på bilden nedan, om jag vill se uppdelningen av produktmässigt incitamentbelopp kommer vi att ha fel SUBTALT och STORNT TOTALT AV INCENTIVBETALNING.
Så var försiktig när du visar summan av beräknade fält. Det visar dig fel belopp.
Få en lista över alla beräknade fältformler
Om du inte vet hur många formler som finns i pivottabellberäkningsfältet kan du få en sammanfattning av alla dessa i ett separat kalkylblad.
Gå till Analysera> Fält, objekt och uppsättningar -> Listformler.
Det ger dig en sammanfattning av alla formler i ett nytt kalkylblad.
Saker att komma ihåg om Pivot Table Formula i Excel
- Vi kan ta bort, ändra alla beräknade fält.
- Vi kan inte använda formler som VLOOKUP, SUMIF och mycket annat intervall involverade formler i beräknade fält, dvs alla formler som kräver intervall kan inte användas.
Rekommenderade artiklar
Detta har varit en guide till Pivot Table Formula i Excel. Här diskuterade vi steg för att använda formeln för pivottabellen i Excel tillsammans med exempel och nedladdningsbar Excel-mall. Du kan också titta på dessa användbara funktioner i Excel -
- Handledning om Pivot Chart i Excel
- Skapa pivottabell i Excel
- VLOOKUP-handledning i Excel
- Excel Skapa databas