Beräkna värden i en pivottabell

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:

• 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.

• 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.

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.

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%.

  

  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 summan 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 objekt 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!.

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:

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:

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:

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.

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.

   

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 objekten i fältet är grupperade går du till fliken Analysera och gruppen Grupper, och klickar på Dela upp grupp.

   

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.

   

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.

   

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.

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.

   

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.

   

3. Leta rätt på den formel du vill ändra under Beräknade fält eller Beräknade objekt i listan med formler.
   
   När det finns flera formler för ett beräknat objekt visas namnet för standardformeln som angavs när detta objekt skapades i kolumn B. För de andra formlerna innehåller kolumn B både namnet på det beräknade objektet och namnen för korsande objekt.

   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.

   

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.

   

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.

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.

   

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

5. Klicka på Ta bort.