Beregninger og formler i pivottabell- og pivotdiagramrapporter

Kildedata

Verdiene i dataområdet summerer de underliggende kildedataene i rapporten.

Pivottabellrapport opprettet fra kildedataene over

Kolonnefeltet Måned inneholder elementene Mars og April. Radfeltet Region inneholder elementene Nord, Sør, Øst og Vest. Verdien ved snittet til kolonnen April og raden Nord er de totale salgsinntektene fra postene i kildedataene med månedsverdiene til April og regionsverdiene til Nord.

Pivotdiagramrapport opprettet fra eksemplet pivottabellrapport

I pivotdiagramrapporten kan Region-feltet være et kategorifelt som viser Nord, Sør, Øst og Vest som kategorier. Feltet Måned kan være et seriefelt som viser elementene Mars, April og Mai som serier representert i forklaringen. Et datafelt som heter Totale inntekter kan inneholde dataindikatorer som representerer den totale inntekten i hver region for hver måned. En dataindikator kan for eksempel representere, av plasseringen på verdiaksen, total inntekt for April i region Nord.

Verdier og beregninger i en pivotdiagramrapport reflekterer de samme i den tilhørende pivottabellrapporten og omvendt.

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

Formler      Hvis sammendragsfunksjoner og egendefinerte beregninger ikke gir de ønskede resultater, 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 ville være forskjellig for hver region. Rapporten vil da automatisk inkludere provisjonen i delsummer og totalsummer.

Beregninger og alternativer tilgjengelig i en rapport avhenger av om kildedataene kommer fra en OLAP-database eller en annen databasetype.

OLAP-kildedata      For rapporter som er opprettet fra OLAP-kuber, forhåndsberegnes sammendragsverdiene på OLAP-serveren før Microsoft Excel viser resultatene. Derfor kan du ikke endre hvordan disse verdiene beregnes fra rapporten. Du kan ikke endre sammendragsfunksjonen som blir brukt til å beregne datafelt eller delsummer, eller legge til beregnede felt eller beregnede elementer. Hvis OLAP-serveren inneholder beregnede felt, også kalt beregnede medlemmer, kan du se disse feltene i feltlisten i pivottabellen. Du kan også se alle beregnede felt og beregnede elementer som er opprettet av makroer som ble skrevet i Visual Basic for Applications og lagret i arbeidsboken. Du kan imidlertid ikke endre disse feltene eller elementene. Hvis du trenger flere beregningstyper, kan du kontakte OLAP-databaseadministratoren.

Andre typer kildedata      I rapporter basert på andre typer eksterne data eller på regnearkdata, brukes sammendragsfunksjonen Summer til å beregne datafelt 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 Min, for å analysere og tilpasse dataene. Du kan også opprette dine egne formler som bruker elementer fra rapporten eller andre arbeidsbokdata, ved å opprette et beregnet felt eller et beregnet element i et felt.

Skjulte elementer i totalsummer      For OLAP-kildedata kan du ta med eller utelate verdiene for skjulte elementer når du beregner delsummer og totalsummer. For andre typer kildedata blir verdiene for skjulte elementer som standard utelatt, men du kan eventuelt ta med de skjulte elementene fra sidefelt.

Du kan bare opprette formler i rapporter som ikke er basert på OLAP-kildedata.

Formler er tilgjengelige i pivotdiagramrapporter og bruker samme syntaks som formler i pivottabellrapporter. Du får best resultater når du arbeider i en pivotdiagramrapport ved å opprette og redigere formler i den tilknyttede pivottabellrapporten, der du kan se enkeltverdiene som utgjør dataene. Deretter kan du vise resultatene i pivotdiagramrapporten.

Formelelementer      Når du oppretter formler for beregnede felt og beregnede elementer, kan du bruke operatorer og uttrykk på samme måte som 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 heller ikke bruke matrisefunksjoner.

Navn i rapporter      I Microsoft Excel finner du navn som identifiserer elementene i en rapport i formlene. Navnene er satt sammen av navnene på felt og elementer. I følgende eksempel har dataene i området C3:C9 navnet Meieriprodukter.

I en pivotdiagramrapport vises feltnavnene i feltknappene, og elementnavn vises i rullegardinlisten til hvert felt. Ikke forveksle disse navnene med de som vises i diagramtips, som viser serie- og datapunktnavn.

Eksempler      Et beregnet felt kalt Prognose kan gi en prognose over fremtidige bestillinger med en formel som denne:

=Salg * 1,2

Et beregnet element i feltet Type, som estimerer salg av et nytt produkt basert på Meieriprodukter, kan bruke følgende formel:

=Meieriprodukter * 115%

Formler opererer på totalsummer, ikke enkeltposter      Formler for beregnede felt bruker summen av de underliggende dataene for alle felt i formelen. Formelen Salg * 1,2 multipliserer for eksempel summen av salget for hver type og hver region med 1,2. Den multipliserer ikke hvert enkelt salg med 1,2 for deretter å summere de multipliserte beløpene. Formler for beregnede elementer bruker imidlertid de enkelte postene. Formelen for det beregnede elementet Meieriprodukter *115% multipliserer hvert enkelt salg av Meieriprodukter med 115%. Deretter summeres de multipliserte beløpene i dataområdet.

Mellomrom, tall og symboler i navn      I et navn som inneholder flere enn ett felt, kan feltene være i en hvilken som helst rekkefølge. I eksemplet over kan cellene C6:D6 være 'April Nord' eller 'Nord April'. Bruk enkle anførselstegn på navn som inneholder flere enn ett ord, eller som inneholder tall eller symboler.

Summer      Formler kan ikke referere til summer (for eksempel Sum Mars, Sum April og Totalt i eksemplet).

Feltnavn i elementreferanser      Du kan ta med feltnavnet i en referanse til et element. Elementnavnet må stå i hakeparentes, for eksempel Region[Nord]. Bruk dette formatet hvis du vil unngå feilverdien #NAVN når to elementer i to forskjellige felt i en pivottabell har samme navn. Hvis for eksempel en pivottabell har et element med navnet Kjøtt/fjærkre i typefeltet og et annet element med navnet Kjøtt/fjærkre i kategorifeltet, kan du forhindre at feilverdien #NAVN vises ved å referere til elementene som Type[Kjøtt/fjærkre] og Kategori[Kjøtt/fjærkre].

Referere til elementer etter plassering      Du kan referere til et element ved å bruke plasseringen i rapporten slik den sortert og vises i øyeblikket. Type[1] er Meieriprodukter og Type[2] er Sjømat. Elementet det refereres til på denne måten, kan endres når plasseringen til elementer endres eller når ulike elementer vises eller skjules. Skjulte elementer regnes ikke med i denne rekkefølgen.

Du kan bruke relativ plassering når du skal referere til elementer. Plasseringen bestemmes i forhold til det beregnede elementet som inneholder formelen. Hvis Sør er gjeldende region, er Region[-1] Nord, og hvis Nord er gjeldende region, er Region[+1] Sør. Et beregnet element kunne for eksempel bruke formelen =Region[-1] * 3%. Hvis plasseringen du oppgir er foran det første elementet eller etter det siste elementet, vil formelen resultere i feilverdien #REF.

Hvis du refererer til elementer ved å bruke plasseringen eller den relative plasseringen i formler for beregnede felt, blir alternativene du har valgt under Ti på topp autovisning og Alternativer for autosortering i dialogboksene Pivottabell Sorter og Ti på topp eller Avanserte alternativer for pivotfelt tilbakestilt til Av eller Manuell, og alternativene blir utilgjengelige.

Metodene og reglene som brukes for å lage formler i pivotdiagramrapporter, er de samme som reglene for pivottabellrapporter. Når du oppretter et beregnet felt eller beregnet element i en pivotdiagramrapport, gjenspeiles beregningene i den tilhørende pivottabellrapporten og omvendt. Du får best resultater ved å opprette formler for en pivotdiagramrapport i den tilhørende pivottabellrapporten, der du kan se enkeltverdiene som utgjør dataene, og deretter vise resultatene grafisk i pivotdiagramrapporten.

Pivotdiagramrapport nedenfor viser for eksempel salg for hver selger per region:

Hvis du vil vise hva salget vil være hvis det økte med 10 prosent, kan du lage et beregnet felt som bruker følgende formel:

=Salg * 110 %

Resultatet gjenspeiles i et diagram som ser slik ut:

Hvis du vil se en separat dataindikator for salg i Nord-regionen minus en transportkostnad på 8 prosent, kan du lage et beregnet felt i Region-feltet som bruker følgende formel:

=Nord – (Nord * 8 %)

Resultatet vil se slik ut:

Et beregnet felt som lages i Selger-feltet, derimot, vises som en serie representert i forklaringen og som et datapunkt i hver kategori i diagrammet.