Beregne verdier i en pivottabell

Hvis du vil beregne verdier i en pivottabell, kan du bruke én eller flere av følgende typer beregningsmetoder:

• Sammendragsfunksjoner i verdifelt    Dataene i verdiområdet summerer de underliggende kildedataene i pivottabellen. For eksempel følgende kildedata:

• Produserer følgende pivottabeller og pivotdiagrammer. Hvis du oppretter et pivotdiagram fra dataene i en pivottabell, gjenspeiler verdiene i det pivotdiagrammet beregningene i tilknyttet pivottabellrapport.

• I pivottabellen gir kolonnefeltet Måned elementene Mars og April. Område-radfeltet gir elementene Nord, Sør, Øst og Vest. Verdien i skjæringspunktet mellom April-kolonnen og Nord-raden er de totale salgsinntektene fra postene i kildedataene som har Måned-verdi for April og Område-verdi for Nord.

• I et pivotdiagram kan Område-feltet være et kategorifelt som viser Nord, Sør, Øst og Vest som kategorier. Måned-feltet kan være et seriefelt som viser elementene Mars, April og Mai som serier representert i forklaringen. Et Verdier-felt kalt Sum salg kan inneholde dataindikatorer som representerer den totale inntekten i hvert område for hver måned. Plasseringen til en dataindikator på den loddrette aksen (verdiaksen) representerer for eksempel totalt salg for April i området Nord.

• Hvis du vil beregne verdifeltene, er følgende sammendragsfunksjoner tilgjengelige for alle typer kildedata, unntatt kildedataene for Online Analytical Processing (OLAP).

  Funksjon	Oppsummerer
  Summer	Summen av verdiene. Dette er standardfunksjonen for numeriske data.
  Antall	Antallet dataverdier. Antallsammendragsfunksjonen fungerer på samme måte som ANTALLA-funksjonen. Antall er standardfunksjonen for andre data enn tall.
  Gjennomsnitt	Gjennomsnittet av verdiene.
  Størst	Den største verdien.
  Minst	Den minste verdien.
  Produkt	Produktet av verdiene.
  Antall tall	Antallet dataverdier som er tall. Sammendragsfunksjonen for antall tall fungerer på samme måte som ANTALL-funksjon.
  StDev	Estimert standardavvik for en populasjon, der utvalget er et delsett av hele populasjonen.
  StDevp	Standardavviket i en populasjon der populasjonen er alle dataene i sammendraget.
  Var	Estimert varians for en populasjon, der utvalget er et delsett av hele populasjonen.
  Varp	Variansen i en populasjon der populasjonen er alle dataene i sammendraget.

• Egendefinerte beregninger    En egendefinert beregning viser verdier basert på andre elementer eller celler i dataområdet. Du kan for eksempel vise verdier i datafeltet Sum salg som en prosentdel av Mars-salg, eller som en løpende sum av elementene i Måned-feltet.

  Følgende funksjoner er tilgjengelige for egendefinerte beregninger i verdifelt.

  Funksjon	Resultat
  Ingen beregning	Viser verdien som er angitt i feltet.
  % av totalsum	Viser verdier som en prosentdel av totalsummen av alle verdiene eller datapunktene i rapporten.
  % av kolonnesum	Viser alle verdiene i hver kolonne eller serie som en prosentdel av totalsummen for kolonnen eller serien.
  % av radsum	Viser verdien i hver rad eller kategori som en prosentdel av totalsummen for raden eller kategorien.
  % av	Viser verdier som en prosentdel av verdien til et Basiselement i et Basisfelt.
  % av radsum i overordnet rad	Beregner verdier slik: (verdi for elementet) / (verdi for det overordnede elementet i rader)
  % av kolonnesum i overordnet kolonne	Beregner verdier slik: (verdi for elementet) / (verdi for det overordnede elementet i kolonner)
  % av sum i overordnet	Beregner verdier slik: (verdi for elementet) / (verdi for det overordnede elementet for valgt Basisfelt)
  Forskjellig fra	Viser verdier som forskjellen fra verdien til Basiselement i Basisfelt.
  % forskjellig fra	Viser verdier som prosentvis forskjell fra verdien til Basiselement i Basisfelt.
  Løpende totalsum i	Viser verdien for etterfølgende elementer i Basisfelt som en løpende totalsum.
  % løpende totalsum i	Beregner verdien som en prosent for etterfølgende elementer i Basisfelt som vises som en løpende totalsum i prosent.
  Ranger minste til største	Viser rangeringen for valgte verdier i et bestemt felt. Det minste elementet i feltet er oppført som 1, og alle høyere verdier har en høyere rangeringsverdi.
  Ranger største til minste	Viser rangeringen for valgte verdier i et bestemt felt. Det største elementet i feltet er oppført som 1, og alle lavere verdier har en høyere rangeringsverdi.
  Indeks	Beregner verdier slik: ((celleverdi) x (samlet totalsum)) / ((totalsum for rad) x (totalsum for kolonne))

• Formler    Hvis sammendragsfunksjoner og egendefinerte beregninger ikke gir resultatene du ønsker, kan du opprette dine egne formler i beregnede felt og beregnede elementer. Du kan for eksempel legge til et beregnet element med formelen for salgsprovisjon, som kan være forskjellig for hvert område. Provisjonen i delsummene og totalsummene vil da automatisk bli inkludert i rapporten.

Beregninger og alternativer som er tilgjengelige i en rapport avhenger av om kildedataene kommer fra en OLAP-database eller en ikke-OLAP-datakilde.

• Beregninger basert på OLAP-kildedata    I pivottabeller som er opprettet fra OLAP-kuber, blir de summerte verdiene forhåndsberegnet på OLAP-serveren før resultatene vises i Excel. Du kan ikke endre måten disse forhåndsberegnede verdiene beregnes på i pivottabellen. Du kan for eksempel ikke endre sammendragsfunksjonen som brukes til å beregne datafelt eller delsummer, eller legge til beregnede felt eller beregnede elementer.

  Hvis OLAP-serveren inneholder beregnede felt, kalt beregnede medlemmer, vises også disse feltene i feltlisten for pivottabellen. Du vil også se alle beregnede felt og beregnede elementer som er opprettet av makroer som ble skrevet i Visual Basic for Applications (VBA) og lagret i arbeidsboken, men du kan ikke endre disse feltene eller elementene. Hvis du trenger flere typer beregninger, kan du kontakte administratoren for OLAP-databasen.

  Du kan inkludere eller utelate verdiene for skjulte elementer når du beregner delsummer og totalsummer for OLAP-kildedata.

• Beregninger basert på ikke-OLAP-kildedata    I pivottabeller som er basert på andre typer eksterne data eller på regnearkdata, bruker Excel sammendragsfunksjonen Summer til å beregne verdifelt som inneholder numeriske data, og sammendragsfunksjonen Antall til beregne datafeltene som inneholder tekst. Du kan velge en annen sammendragsfunksjon, for eksempel Gjennomsnitt, Størst eller Minst til å analysere og tilpasse dataene. Du kan også opprette dine egne formler som bruker elementer fra rapporten eller andre regnearkdata ved å opprette et beregnet felt eller beregnet element i et felt.

Du kan bare opprette formler i rapporter som er basert på ikke-OLAP-kildedata. Du kan ikke bruke formler i rapporter som er basert på en OLAP-database. Når du bruker formler i pivottabeller, bør du kjenne til følgende formelsyntaksregler og virkemåten for formlene:

• Formelelementer i pivottabeller    I formler som du oppretter for beregnede felt og beregnede elementer, kan du bruke operatorer og uttrykk som du gjør i andre regnearkformler. Du kan bruke konstanter og referere til data fra rapporten, men du kan ikke bruke cellereferanser eller definerte navn. Du kan ikke bruke regnearkfunksjoner som krever cellereferanser eller definerte navn som argumenter, og du kan ikke bruke matrisefunksjoner.

• Felt- og elementnavn    I Excel brukes felt- og elementnavn til å identifisere elementene i en rapport i formlene. I eksemplet nedenfor bruker dataene i området C3:C9 feltnavnet Meieriprodukter. Et beregnet element i Type-feltet som estimerer salg for et nytt produkt basert på salg av meieriprodukter kan bruke en formel som =Meieriprodukter * 115%.

  

  Obs!: Feltnavnene vises i feltlisten for pivottabell i et pivotdiagram, og elementnavn vises i rullegardinlisten for hvert felt. Disse navnene må ikke forveksles med de som vises i diagramtips, og som gjenspeiler serie-og datapunktnavn.

• Formler opererer på totalsummer, ikke individuelle poster    Formler for beregnede felt opererer på summen av de underliggende dataene for alle felt i formelen. Denne formelen for beregnet felt = Salg * 1,2 multipliserer summen av salg for hver type og hvert område med 1,2. Den multipliserer ikke hvert enkelt salg med 1,2 for deretter å summere de multipliserte beløpene.

  Formler for beregnede elementer opererer på de enkelte postene. Denne formelen for beregnet element =Meieriprodukter * 115% multipliserer hvert enkelt salg av meieriprodukter med 115 %, deretter blir de multipliserte beløpene summert sammen i området Verdier.

• Mellomrom, tall og symboler i navn    I et navn som inkluderer mer enn ett felt, kan feltene være i en hvilken som helst rekkefølge. I eksemplet ovenfor kan cellene C6:D6 være April Nord eller Nord April. Bruk enkle anførselstegn rundt navn som er mer enn ett ord, eller som inneholder tall eller symboler.

• Totaler    Formler kan ikke referere til totaler (som Mars totalt, April totalt, og Totalsum i eksemplet).

• Feltnavn i elementreferanser    Du kan inkludere feltnavnet i en referanse til et element. Elementnavnet må stå i hakeparenteser, for eksempel Område[North]. Vil du bruke dette formatet for å unngå #NAME? -feil når to elementer i to forskjellige felt i en rapport har samme navn. Hvis en rapport har et element med navnet Kjøtt i Type-feltet og et annet element med navnet Kjøtt i Kategori-feltet, kan du for eksempel hindre #NAVN? -feil ved å referere til elementene som Type[Meat] og Kategori[Meat].

• Referere til elementer etter posisjon    Du kan referere til et element ved å bruke plasseringen i rapporten som sortert og vist. Type[1] er Meieriprodukter, og Type[2] er Sjømat. Elementet det refereres til på denne måten, kan endre seg når plasseringen for elementer endres, eller forskjellige elementer vises eller skjules. Skjulte elementer telles ikke i denne indeksen.

  Du kan bruke relative plasseringer til å referere til elementer. Plasseringene bestemmes i forhold til det beregnede elementet som inneholder formelen. Hvis Sør er gjeldende område, vil Område[-1] være Nord, og hvis Nord er gjeldende område, vil Område[+1] være Sør. Et beregnet element kan for eksempel bruke formelen = Region [-1] * 3%. Hvis plasseringen du angir er foran det første elementet eller etter det siste elementet i feltet, resulterer formelen i en #REF! -feil.

Hvis du bruker formler i et pivotdiagram, oppretter du formler i den tilknyttede pivottabellen, der du kan se de enkelte verdiene som utgjør dataene, og deretter kan du vise resultatene grafisk i pivotdiagrammet.

Følgende pivotdiagram viser for eksempel salg for hver selger per område:

For å se hvordan salg ville se ut hvis det økte med 10 prosent, kan du opprette et beregnet felt i den tilknyttede pivottabellen som bruker en formel som = Salg * 110%.

Resultatet vises umiddelbart i pivotdiagrammet, som vist i følgende diagram:

Hvis du vil se en separat dataindikator for salg i området Nord minus en transportkostnad på 8 prosent, kan du opprette et beregnet element i Område-feltet med en formel som = Nord – (Nord * 8%).

Det resulterende diagrammet vil se slik ut:

Et beregnet element som er opprettet i Selger-feltet vil imidlertid vises som en serie representert i forklaringen, og vises i diagrammet som et datapunkt i hver kategori.

Før du begynner, må du bestemme om du vil bruke et beregnet felt eller et beregnet element i et felt. Bruk et beregnet felt når du vil bruke dataene fra et annet felt i formelen. Bruk et beregnet element når du vil at formelen skal bruke data fra ett eller flere bestemte elementer i et felt.

Du kan angi ulike formler celle for celle for beregnede elementer. Hvis for eksempel et beregnet element som heter Appelsinland har formelen = Appelsiner *.25 for alle månedene, kan du endre formelen til = Appelsiner *.5 for juni, juli og august.

Hvis du har flere beregnede elementer eller formler, kan du justere beregningsrekkefølgen.

Legge til et beregnet felt

1. Klikk på pivottabellen.

   Dette viser pivottabellverktøyene og legger til fanene Analyser og Utforming.

2. Klikk på Felt, elementer og sett på Analyser-fanen i Beregninger-gruppen, og klikk deretter på Beregnet felt.

   

3. Skriv inn et navn på feltet i Navn-boksen.

4. Skriv inn formelen for feltet i Formel-boksen.

   Hvis du vil bruke data fra et annet felt i formelen, klikker du på feltet i Felt-boksen, og deretter klikker du på Sett inn felt. Hvis du for eksempel vil beregne en provisjon på 15 % på hver verdi i Salg-feltet, kan du skrive inn = Salg * 15%.

5. Klikk på Legg til.

Legge til et beregnet element i et felt

1. Klikk på pivottabellen.

   Dette viser pivottabellverktøyene og legger til fanene Analyser og Utforming.

2. Hvis elementer i feltet er gruppert, klikker du på Del opp gruppe på Analyser-fanen i Grupper-gruppen.

   

3. Klikk på feltet der du vil legge til det beregnede elementet.

4. Klikk på Felt, elementer og sett på Analyser-fanen i Beregninger-gruppen, og klikk deretter på Beregnet element.

   

5. Skriv inn et navn på det beregnede elementet i Navn-boksen.

6. Skriv inn formelen for elementet i Formel-boksen.

   Hvis du vil bruke data fra et element i formelen, klikker du på elementet i Elementer-listen, og deretter klikker du på Sett inn element (elementet må være fra samme felt som det beregnede elementet).

7. Klikk på Legg til.

Angi ulike formler celle for celle for beregnede elementer.

1. Klikk på en celle der du vil endre formelen.

   Hvis du vil endre formelen for flere celler, holder du nede CTRL og klikker på de aktuelle cellene.

2. Skriv inn endringene i formelen i formellinje.

Justere beregningsrekkefølgen for flere beregnede elementer eller formler.

1. Klikk på pivottabellen.

   Dette viser pivottabellverktøyene og legger til fanene Analyser og Utforming.

2. Klikk på Felt, elementer og sett på Analyser-fanen i Beregninger-gruppen, og klikk deretter på Løsningsrekkefølge.

   

3. Klikk på en formel, og klikk deretter på Flytt opp eller Flytt ned.

4. Fortsett til formlene ligger i den rekkefølgen du vil de skal beregnes i.

Du kan vise en liste over alle formlene som brukes i den gjeldende pivottabellen.

1. Klikk på pivottabellen.

   Dette viser pivottabellverktøyene og legger til fanene Analyser og Utforming.

2. Klikk på Felt, elementer og sett på Analyser-fanen i Beregninger-gruppen, og klikk deretter på Vis formler.

   

Før du redigerer en formel, må du avgjøre om formelen er i et beregnet felt eller i et beregnet element. Hvis formelen er i et beregnet element, må du også avgjøre om formelen er den eneste for det beregnede elementet.

Hvis du har beregnede elementer, kan du redigere individuelle formler for bestemte celler i et beregnet element Hvis for eksempel et beregnet element som heter Appelsinberegning har formelen = Appelsiner *.25 for alle månedene, kan du endre formelen til = Appelsiner *.5 for juni, juli og august.

Avgjøre om en formel er i et beregnet felt eller i et beregnet element

1. Klikk på pivottabellen.

   Dette viser pivottabellverktøyene og legger til fanene Analyser og Utforming.

2. Klikk på Felt, elementer og sett på Analyser-fanen i Beregninger-gruppen, og klikk deretter på Vis formler.

   

3. I listen over formler finner du formelen du vil endre, oppført under Beregnet felt eller Beregnet element.
   
   Når det finnes flere formler for et beregnet element, vil standardformelen som ble angitt da element ble opprettet, ha navnet på det beregnede elementet i kolonne B. Hvis det er flere formler for et beregnet element, inneholder kolonne B både navnet på det beregnede elementet og navnene på kryssende elementer.

   Du kan for eksempel ha en standardformel for et beregnet element med navnet MittElement, og en annen formel for dette elementet kalt MittElement salg i januar. I pivottabellen finner du denne formelen i Salg-cellen for MittElement-raden og januarkolonnen.

4. Fortsett ved å bruke én av følgende redigeringsmetoder:

Redigere en formel for et beregnet felt

1. Klikk på pivottabellen.

   Dette viser pivottabellverktøyene og legger til fanene Analyser og Utforming.

2. Klikk på Felt, elementer og sett på Analyser-fanen i Beregninger-gruppen, og klikk deretter på Beregnet felt.

   

3. Velg det beregnede feltet der du vil endre formelen i Navn-boksen.

4. Rediger formelen i Formel-boksen.

5. Klikk på Endre.

Redigere én enkel formel for et beregnet element

1. Klikk på feltet som inneholder det beregnede elementet.

2. Klikk på Felt, elementer og sett på Analyser-fanen i Beregninger-gruppen, og klikk deretter på Beregnet element.

   

3. Velg det beregnede elementet i Navn-boksen .

4. Rediger formelen i Formel-boksen.

5. Klikk på Endre.

Redigere én enkelt formel for én bestemt celle i et beregnet element

1. Klikk på en celle der du vil endre formelen.

   Hvis du vil endre formelen for flere celler, holder du nede CTRL og klikker på de aktuelle cellene.

2. Skriv inn endringene i formelen i formellinje.

   Tips!: Hvis du har flere beregnede elementer eller formler, kan du justere beregningsrekkefølgen. Hvis du vil ha mer informasjon, kan du se Justere beregningsrekkefølgen for flere beregnede elementer eller formler.

1. Avgjør om formelen er i et beregnet felt eller i et beregnet element

   Beregnede felt vises i feltlisten for pivottabell. Beregnede elementer vises som elementer i andre felt.

2. Gjør ett av følgende:

   • Hvis du vil slette et beregnet felt, klikker du hvor som helst i pivottabellen.

   • Hvis du vil slette et beregnet element i pivottabellen, klikker du på feltet som inneholder elementet du vil slette.

     Dette viser pivottabellverktøyene og legger til fanene Analyser og Utforming.

3. Klikk på Felt, elementer og sett på Analyser-fanen i Beregninger-gruppen, og klikk deretter på Beregnet felt eller Beregnet element.

   

4. Velg feltet eller elementet du vil slette i Navn-boksen.

5. Klikk på Slett.