Beräkna värden i en pivottabell

I pivottabeller kan du använda summeringsfunktioner i värdefält till att kombinera värden från underliggande källdata. Om summeringsfunktioner och anpassade beräkningar inte ger de resultat du vill ha kan du skapa egna formler i beräknade fält och beräknade element. Du kan till exempel lägga till ett beräknat element med formeln för provision, som kan variera mellan olika regioner. Pivottabellen skulle sedan automatiskt ta med provisionen i delsummor och totalsummor.

Pivottabeller innehåller många sätt att beräkna data. Läs mer om vilka beräkningsmetoder som är tillgängliga, hur beräkningar påverkas av typen av källdata och hur du använder formler i pivottabeller och pivotdiagram.

Tillgängliga beräkningsmetoder

När du ska beräkna värden i en pivottabell kan du använda några eller alla av följande beräkningsmetoder:

  • Summafunktioner i värdefält    Data i värdeområdet summerar underliggande källdata i pivottabellen. Till exempel skulle följande källdata:

    Exempel på källdata för pivottabell

  • Ger följande pivottabeller och pivotdiagram. Om du skapar ett pivotdiagram från data i en pivottabell återger värdena i pivotdiagrammet beräkningarna i kopplad Pivottabellrapport.

    Exempel på pivottabellrapport

    Exempel på en pivotdiagramrapport

  • I pivottabellen innehåller kolumnfältet Månad elementen Mars och April. Radfältet Region innehåller elementen Nord, Syd, Öst och Väst. Värdet i skärningspunkten mellan kolumnen April och raden Nord är den totala säljintäkten från posterna i källdata där värdet för Månad är April och värdet för Region är Nord.

  • I ett pivotdiagram kan fältet Region vara ett kategorifält där Nord, Syd, Öst och Väst visas som kategorier. Fältet Månad kan vara ett seriefält där elementen Mars, April och Maj visas som serier i förklaringen. Ett Värde-fält med namnet Försäljningssumma kan innehålla databrytpunkter som representerar den totala intäkten i varje region för varje månad. En brytpunkt skulle till exempel via placeringen på den lodräta (värde)axeln representera den totala försäljningen för April i regionen Nord.

  • När värdefälten ska beräknas är följande summafunktioner tillgängliga för alla typer av källdata utom OLAP-källdata (Online Analytical Processing).

Funktion

Summerar

Summa

Summan av värdena. Det här är standardfunktionen för numeriska data.

Antal

Antalet datavärden. Summeringsfunktionen Antal fungerar som funktionen ANTALV. Antal är standardfunktionen för data som inte är tal.

Medel

Värdenas medelvärde.

Max

Det största värdet.

Min

Det minsta värdet.

Produkt

Produkten av värdena.

Antal tal

Antalet datavärden som är tal. Summeringsfunktionen Antal tal fungerar som funktionen ANTAL.

STDAV

En uppskattning av standardavvikelsen i en population där urvalet är en delmängd av hela populationen.

STDAVP

Standardavvikelsen för en population, där populationen är alla data som ska summeras.

VARIANS

En uppskattning av variansen i en population där urvalet är en delmängd av hela populationen.

VARIANSP

Variansen för en population, där populationen är alla data som ska summeras.

  • Anpassade beräkningar    En anpassad beräkning visar värden som baseras på andra element eller celler i dataområdet. Du kan till exempel visa värden i datafältet Försäljningssumma som en procentandel av försäljningen för Mars, eller som en löpande summa av posterna i fältet Månad.

    Följande funktioner är tillgängliga för anpassade beräkningar i värdefält.

Funktion

Resultat

Ingen beräkning

Visar värdet som anges i fältet.

% av totalsumman

Visar värden som en procentandel av totalsumman för alla värden eller datapunkter i rapporten.

% av kolumnsumma

Visar alla värden i varje kolumn eller serie som en procentandel av totalsumman för kolumnen eller serien.

% av radsumma

Visar värdena i varje rad eller kategori som en procentandel av totalsumman för raden eller kategorin.

% av

Visar värden som en procentandel av värdet för Baselementen i Basfältet.

% av summa för överordnad rad

Värden beräknas enligt följande:

(elementets värde) / (värdet för det överordnade elementet i rader)

% av summa för överordnad kolumn

Värden beräknas enligt följande:

(elementets värde) / (värdet för det överordnade elementet i kolumner)

% av överordnad summa

Värden beräknas enligt följande:

(elementets värde) / (värdet för det överordnade elementet i det valda basfältet)

Skillnad

Visar värden som skillnaden från baselementet i basfältet.

% skillnad

Visar värden som en procentuell skillnad från baselementet i basfältet.

Löpande summa

Visar värdet för på varandra följande element i basfältet som en löpande summa.

% löpande summa

Beräknar värdet som en procentandel för på varandra följande element i basfältet som visas som en löpande summa.

Rangordna från minst till störst

Visar rangordningen för valda värden i en specifik lista där det minsta elementet i fältet listas som 1 och varje större element får ett högre värde i rangordningen.

Rangordna från störst till minst

Visar rangordningen för valda värden i en specifik lista där det största elementet i fältet listas som 1 och varje mindre element får ett högre värde i rangordningen.

Index

Värden beräknas enligt följande:

((värde i cell) x (Totalsumma för totalsummor)) / ((Totalsumma för rader) x (Totalsumma för kolumner))

  • Formler    Om summeringsfunktioner och anpassade beräkningar inte ger de resultat du vill ha kan du skapa egna formler i beräknade fält och beräknade element. Du kan till exempel lägga till ett beräknat element med formeln för provision, som kan variera mellan olika regioner. Rapporten skulle sedan automatiskt ta med provisionen i delsummor och totalsummor.

Så påverkas beräkningar av typen av källdata

Vilka beräkningar och alternativ som är tillgängliga i en rapport beror på om källdata kommer från en OLAP-databas eller en annan typ av datakälla.

  • Beräkningar baserade på OLAP-källdata    I pivottabeller som skapas från OLAP-kuber förberäknas summerade värden på OLAP-servern innan resultatet visas i Excel. Du kan inte ändra hur de här värdena beräknas i pivottabellen. Du kan till exempel inte ändra summeringsfunktionen som används till att beräkna datafält eller delsummor, eller lägga till beräknade fält och beräknade element.

    Även om OLAP-servern tillhandahåller beräknade fält, som kallas för beräknade medlemmar, kommer du att se de här fälten i fältlistan för pivottabellen. Du ser också eventuella beräknade fält och beräknade element som skapats av makron som har skrivits i Visual Basic for Applications (VBA) och lagras i arbetsboken, men du kan inte ändra de här fälten eller elementen. Om du behöver ytterligare typer av beräkningar kan du kontakta administratören för OLAP-databasen.

    För OLAP-källdata kan du inkludera eller exkludera värden för dolda element när du beräknar delsummor och totalsummor.

  • Beräkningar baserade på andra typer av källdata än OLAP    I pivottabeller som är baserade på andra typer av externa data eller kalkylbladsdata används summeringsfunktionen Summa till att beräkna värdefält som innehåller numeriska data och summeringsfunktionen Antal till att beräkna datafält som innehåller text. Du kan välja en annan summeringsfunktion, som Medelvärde, Max eller Min, om det behövs i din analys. Du kan också skapa egna formler som använder element i rapporten eller andra data i kalkylbladet genom att skapa ett beräknat fält eller ett beräknat element i ett fält.

Använda formler i pivottabeller

Du kan bara skapa formler i rapporter som baseras på data från andra källor än OLAP. Du kan inte använda formler i rapporter som baseras på en OLAP-databas. När du använder formler i pivottabeller bör du känna till följande syntaxregler och beteenden:

  • Element i pivottabellformler    I formler som du skapar för beräknade fält och beräknade element kan du använda operatorer och uttryck precis som i andra kalkylbladsformler. Du kan använda konstanter och hänvisa till data från rapporten, men du kan inte använda cellreferenser eller definierade namn. Du kan inte använda kalkylbladsfunktioner där cellreferenser eller definierade namn ingår argument, och du kan inte använda matrisfunktioner.

  • Namn på fält och element    I Excel används fält- och elementnamn till att identifiera motsvarande rapportelement i dina formler. I exemplet nedan har data i området C3: C9 fältnamnet Mejeri. Ett beräknat element i fältet Typ som uppskattar försäljningen för en ny produkt baserat på försäljningen av mejeriprodukter skulle kunna ha formeln =Mejeri * 115%.

    Pivottabellrapport, exempel

    Obs!: I ett pivotdiagram visas fältnamnen i fältlistan för pivottabellen, och du ser elementnamnen i listrutan för respektive fält. Blanda inte ihop dessa namn med namnen du ser i Diagramtips, som istället baseras på namn på serier och datapunkter.

  • Formler används på totalsummor, inte på enskilda poster    I formler för beräknade fält används summor av underliggande data för fälten i formeln. I formeln =Försäljning * 1,2 för ett beräknat fält multipliceras till exempel summan av försäljningen för varje typ och region med 1,2. Varje enskild försäljning multipliceras inte med 1,2 innan summeringen.

    I formler för beräknade element används de enskilda posterna. I formeln =Mejeri * 115 % för ett beräknat element multipliceras till exempel varje enskild försäljning av mejeriprodukter med 115 %, och därefter summeras de multiplicerade värdena i värdeområdet.

  • Blanksteg, siffror och symboler i namn    I namn som innehåller fler än ett fält kan fälten stå i valfri ordning. I exemplet ovan kan cellerna C6: D6 vara April Nord eller Nord April. Använd enkla citattecken runt namn som består av fler än ett ord eller som innehåller siffror och symboler.

  • Totalsummor    Formler kan inte referera till totalsummor (som Mars totalt, April totalt eller Totalsumma i exemplet).

  • Fältnamn i elementreferenser    Du kan ta med fältnamnet i en referens till ett element. Elementnamnet måste stå inom hakparenteser, till exempel Region[Nord]. Använd det här formatet så att du undviker fel av typen #NAME? när två element i två olika fält i en rapport har samma namn. Om en rapport till exempel innehåller elementet kött i fältet Typ och samtidigt elementet Kött i fältet Kategori kan du förhindra fel av typen #NAME? genom att referera till elementen som Typ[Kött] och Kategori[Kött].

  • Referera till element utifrån position    Du kan referera till ett element utifrån dess position i rapporten så som den är sorterad och visas för närvarande. Typ[1] är Mejeri och Typ[2] är Skaldjur. Vilket element som refereras kan när som helst ändras om elementen byter plats eller om andra element visas eller döljs. Dolda element räknas inte i det här indexet.

    Du kan använda relativa positioner när du refererar till element. Positionerna avgörs i förhållande till det beräknade element som innehåller formeln. Om Syd är aktuell region så är Region[-1]Nord och om Nord är aktuell region så är Region[+1]Syd. Ett beräknat element kan till exempel ha formeln =Region[-1] * 3 %. Om den position du anger är före det första objektet eller efter det sista objektet i fältet ger formeln ett fel av typen #REF!

Använda formler i pivotdiagram

Om du vill använda formler i ett pivotdiagram kan du skapa formlerna i den associerade pivottabellen, där du kan se de enskilda värdena som dina data består av, och sedan kan du visa resultatet grafiskt i pivotdiagrammet.

Följande pivotdiagram visar till exempel försäljningen per region för varje säljare:

Pivotdiagramrapport som visar försäljningen per säljare och region

Om du vill se hur försäljningen skulle se ut om den ökade med 10 procent kan du skapa ett beräknat fält i den associerade pivottabellen med formeln =Försäljning * 110 %.

Resultatet visas omedelbart i pivotdiagrammet, som i följande diagram:

Pivotdiagramrapport som visar att försäljningen ökade med 10 procent per region

Om du vill se en separat brytpunkt för försäljningen i region Nord minus transportkostnader på 8 procent kan du skapa ett beräknat element i fältet Region med formeln =Nord – (Nord * 8 %).

Resultatet blir ett diagram som ser ut så här:

Pivotdiagramrapport med ett beräknat objekt.

Ett beräknat element som har skapats i fältet Försäljare skulle däremot visas som en serie representerad i förklaringen och visas i diagrammet som en datapunkt i varje kategori.

Skapa formler i en pivottabell

Viktigt!: Du kan inte skapa formler i en pivottabell som är kopplad till en OLAP-datakälla (Online Analytical Processing).

Innan du börjar måste du bestämma om du vill ha ett beräknat fält eller ett beräknat element i ett fält. Använd ett beräknat fält när du vill använda data från ett annat fält i formeln. Använd ett beräknat element när du vill använda data från ett eller flera specifika element i ett fält i formeln.

Du kan ange olika formler cell för cell för beräknade element. Om ett beräknat element med namnet OrangeCounty till exempel har formeln =Apelsiner * 0,25 för alla månader kan du ändra formeln till =Apelsiner * 0,5 för juni, juli och augusti.

Om du har flera beräknade element eller formler kan du justera beräkningsordningen.

Lägga till ett beräknat fält

  1. Klicka på pivottabellen.

    Då visas Verktyg för pivottabell med flikarna Analysera och Design.

  2. Gå till fliken Analysera och gruppen Beräkningar. Klicka på Fält, objekt och uppsättningar och klicka sedan på Beräknat fält.

    Bild av menyfliksområdet i Excel

  3. Ange ett namn på fältet i rutan Namn.

  4. Ange fältets formel i rutan Formel.

    Om du vill använda data från ett annat fält i formeln klickar du på fältet i rutan Fält och klickar sedan på Infoga fält. Om du till exempel vill beräkna en provision på 15 % på varje värde i fältet Försäljning anger du =Försäljning * 15 %.

  5. Klicka på Lägg till.

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

  1. Klicka på pivottabellen.

    Då visas Verktyg för pivottabell med flikarna Analysera och Design.

  2. Om elementen i fältet är grupperade går du till fliken Analysera och gruppen Grupper, och klickar på Dela upp grupp.

    Bild av menyfliksområdet i Excel

  3. Klicka på fältet där du vill lägga till det beräknade elementet.

  4. Gå till fliken Analysera och gruppen Beräkningar. Klicka på Fält, objekt och uppsättningar och klicka sedan på Beräknat element.

    Bild av menyfliksområdet i Excel

  5. Skriv ett namn på det beräknade elementet i rutan Namn.

  6. Ange elementets formel i rutan Formel.

    Om du vill använda data från ett element i formeln klickar du på elementet i listan Element och klickar på Infoga element (elementet måste komma från samma fält som det beräknade elementet).

  7. Klicka på Lägg till.

Ange olika formler cell för cell för beräknade element.

  1. Klicka på en cell som du vill ändra formeln för.

    Om du vill ändra formeln för flera celler håller du ned Ctrl och klickar på de andra cellerna.

  2. Ange dina formeländringar i formelfältetet.

Justera beräkningsordningen för flera beräknade element eller formler

  1. Klicka på pivottabellen.

    Då visas Verktyg för pivottabell med flikarna Analysera och Design.

  2. Gå till fliken Analysera och gruppen Beräkningar. Klicka på Fält, objekt och uppsättningar och klicka sedan på Lösningsordning.

    Bild av menyfliksområdet i Excel

  3. Klicka på en formel och sedan på Flytta upp eller Flytta ned.

  4. Fortsätt tills formlerna står i den ordning du vill att de ska beräknas.

Visa alla formler som används i en pivottabell

Du kan visa en lista med alla formler som används i den aktuella pivottabellen.

  1. Klicka på pivottabellen.

    Då visas Verktyg för pivottabell med flikarna Analysera och Design.

  2. Gå till fliken Analysera och gruppen Beräkningar. Klicka på Fält, objekt och uppsättningar och klicka sedan på Lista formler.

    Bild av menyfliksområdet i Excel

Redigera en pivottabellformel

Innan du redigerar en formel måste du avgöra om formeln används i ett beräknat fält eller ett beräknat element. Om formeln används i ett beräknat element ska du även avgöra om det är den enda formeln i det beräknade elementet.

Du kan redigera enskilda formler för specifika celler i ett beräknat element. Om ett beräknat element med namnet OrangeCalc till exempel har formeln =Apelsiner * 0,25 för alla månader kan du ändra formeln till =Apelsiner * 0,5 för juni, juli och augusti.

Avgör om formeln används i ett beräknat fält eller ett beräknat element

  1. Klicka på pivottabellen.

    Då visas Verktyg för pivottabell med flikarna Analysera och Design.

  2. Gå till fliken Analysera och gruppen Beräkningar. Klicka på Fält, objekt och uppsättningar och klicka sedan på Lista formler.

    Bild av menyfliksområdet i Excel

  3. Leta rätt på den formel du vill ändra under Beräknade fält och Beräknade element i listan med formler.

    När det finns flera formler för ett beräknat element så visas namnet för standardformeln som angavs när objektet skapades i kolumn B. För de andra formlerna så innehåller kolumn B både namnet på det beräknade elementet och namnen för korsande element.

    Du kan till exempel ha en standardformel för ett beräknat element med namnet MittElement och en annan formel definierad för elementet med namnet MittElement Januari Försäljning. I pivottabellen skulle du hitta den här formeln i cellen Försäljning för raden MittElement och kolumnen Januari.

  4. Fortsätt redigera på något av följande sätt:

Redigera formeln för ett beräknat fält

  1. Klicka på pivottabellen.

    Då visas Verktyg för pivottabell med flikarna Analysera och Design.

  2. Gå till fliken Analysera och gruppen Beräkningar. Klicka på Fält, objekt och uppsättningar och klicka sedan på Beräknat fält.

    Bild av menyfliksområdet i Excel

  3. I rutan Namn väljer du det beräknade fält som du vill ändra formeln för.

  4. Redigera formeln i rutan Formel.

  5. Klicka på Ändra.

Redigera en enstaka formel för ett beräknat element

  1. Klicka på fältet som innehåller det beräknade elementet.

  2. Gå till fliken Analysera och gruppen Beräkningar. Klicka på Fält, objekt och uppsättningar och klicka sedan på Beräknat element.

    Bild av menyfliksområdet i Excel

  3. Välj det beräknade elementet i rutan Namn.

  4. Redigera formeln i rutan Formel.

  5. Klicka på Ändra.

Redigera en enskild formel för en viss cell i ett beräknat element

  1. Klicka på en cell som du vill ändra formeln för.

    Om du vill ändra formeln för flera celler håller du ned Ctrl och klickar på de andra cellerna.

  2. Ange dina formeländringar i formelfältetet.

    Tips: Om du har flera beräknade element eller formler kan du justera beräkningsordningen. Mer information finns i Justera beräkningsordningen för flera beräknade element eller formler.

Ta bort en pivottabellformel

Obs!: När du tar bort pivottabellformel tas den bort permanent. Om du inte vill ta bort en formel permanent kan du dölja fältet eller elementet i stället genom att dra det utanför pivottabellen.

  1. Avgör om formeln används i ett beräknat fält eller ett beräknat element

    Beräknade fält visas i listan Pivottabellfält. Beräknade element visas som element i andra fält.

  2. Gör något av följande:

    • När du vill ta bort ett beräknat element klickar du var som helst i pivottabellen.

    • När du vill ta bort ett beräknat element klickar du på det fält i pivottabellen som innehåller elementet du vill ta bort.

      Då visas Verktyg för pivottabell med flikarna Analysera och Design.

  3. Gå till fliken Analysera och gruppen Beräkningar. Klicka på Fält, objekt och uppsättningar och klicka sedan på Beräknat fält eller Beräknat element.

    Bild av menyfliksområdet i Excel

  4. Välj det fält eller element du vill ta bort i rutan Namn.

  5. Klicka på Ta bort.

Överst på sidan

Visa alla formler som används i en pivottabell

Så här visar du en lista med alla formler som används i den aktuella pivottabellen:

  1. Klicka på pivottabellen.

  2. Gå till fliken Alternativ och gruppen Verktyg. Klicka på Formler och sedan på Lista formler.

Redigera en pivottabellformel

  1. Avgör om formeln används i ett beräknat fält eller ett beräknat element Om formeln används i ett beräknat element ska du avgöra om det är den enda formeln i det beräknade elementet. Gör så här:

    1. Klicka på pivottabellen.

    2. Gå till fliken Alternativ och gruppen Verktyg. Klicka på Formler och sedan på Lista formler.

    3. Leta rätt på den formel du vill ändra under Beräknade fält eller Beräknade element i listan med formler.

      När det finns flera formler för ett beräknat element så visas namnet för standardformeln som angavs när objektet skapades i kolumn B. För de andra formlerna så innehåller kolumn B både namnet på det beräknade elementet och namnen för korsande element.

      Du kan till exempel ha en standardformel för ett beräknat element med namnet MittElement och en annan formel definierad för elementet med namnet MittElement Januari Försäljning. I pivottabellen skulle du hitta den här formeln i cellen Försäljning för raden MittElement och kolumnen Januari.

  2. Gör något av följande:

    Redigera formeln för ett beräknat fält    

    1. Klicka på pivottabellen.

    2. Gå till fliken Alternativ och gruppen Verktyg. Klicka på Formler och sedan på Beräknat fält.

    3. I rutan Namn väljer du det beräknade fält som du vill ändra formeln för.

    4. Redigera formeln i rutan Formel.

    5. Klicka på Ändra.

      Redigera en enstaka formel för ett beräknat element    

    6. Klicka på fältet som innehåller det beräknade elementet.

    7. Gå till fliken Alternativ och gruppen Verktyg. Klicka på Formler och sedan på Beräknat element.

    8. Välj det beräknade elementet i rutan Namn.

    9. Redigera formeln i rutan Formel.

    10. Klicka på Ändra.

      Redigera enskilda formler för specifika celler i ett beräknat element    

      Om ett beräknat element med namnet OrangeCalc till exempel har formeln =Apelsiner * 0,25 för alla månader kan du ändra formeln till =Apelsiner * 0,5 för juni, juli och augusti.

    11. Klicka på en cell som du vill ändra formeln för.

      Om du vill ändra formeln för flera celler håller du ned Ctrl och klickar på de andra cellerna.

    12. Ange dina formeländringar i formelfältetet.

  3. Om du har flera beräknade element eller formler kan du justera beräkningsordningen. Gör så här:

    1. Klicka på pivottabellen.

    2. Gå till fliken Alternativ och gruppen Verktyg. Klicka på Formler och sedan på Lösningsordning.

    3. Klicka på en formel och sedan på Flytta upp eller Flytta ned.

    4. Fortsätt tills formlerna står i den ordning du vill att de ska beräknas.

Ta bort en pivottabellformel

Tips: Om du inte vill ta bort en formel permanent kan du dölja fältet eller elementet. Om du vill dölja ett fält drar du det ut från rapporten.

  1. Avgör om formeln används i ett beräknat fält eller ett beräknat element

    Beräknade fält visas i listan Pivottabellfält. Beräknade element visas som element i andra fält.

  2. Gör något av följande:

    Ta bort ett beräknat fält    

    1. Klicka på pivottabellen.

    2. Gå till fliken Alternativ och gruppen Verktyg. Klicka på Formler och sedan på Beräknat fält.

    3. Välj det fält du vill ta bort i rutan Namn.

    4. Klicka på Ta bort.

      Ta bort ett beräknat element    

    5. Klicka på fältet som innehåller det element du vill ta bort.

    6. Gå till fliken Alternativ och gruppen Verktyg. Klicka på Formler och sedan på Beräknat element.

    7. Välj det element du vill ta bort i rutan Namn.

    8. Klicka på Ta bort.

Överst på sidan

Utöka dina kunskaper
Utforska utbildning
Få nya funktioner först
Anslut till Office Insiders

Hade du nytta av den här informationen?

Tack för din feedback!

Tack för din feedback! Det låter som att det kan vara bra att koppla dig till en av våra Office-supportrepresentanter.

×