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

  

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