Beregn værdier i en pivottabel

Til beregning af værdier i en pivottabel kan du bruge en af eller alle de følgende typer beregningsmetoder:

• Summeringsfunktioner i værdifelter    Dataene i værdiområdet opsummerer de underliggende kildedata i pivottabellen. F.eks. følgende kildedata:

• Producerer følgende pivottabeller og pivotdiagrammer. Hvis du opretter et pivotdiagram ud fra data i en pivottabel, så afspejler værdierne i det pågældende pivotdiagram beregningerne i tilknyttet pivottabelrapport.

• I pivottabellen indeholder kolonnefeltet Måned elementerne Marts og April. Rækkefeltet Område indeholder elementerne Nord, Syd, Øst og Vest. Værdien ved skæringen af kolonnen April og rækken Nord er den samlede omsætning fra posterne i kildedataene, der har Månedsværdierne for April og Områdeværdierne for Nord.

• I et pivotdiagram kan feltet Område være et kategorifelt, der viser Nord, Syd, Øst og Vest som kategorier. Feltet Måned kunne være et seriefelt, der viser elementerne Marts, April og Maj som serier, der er repræsenteret i forklaringen. Et Værdifelt med navnet Sum af salg kan indeholde datamærker, der repræsenterer den samlede omsætning i hvert område for hver måned. Ét datamærke repræsenterer f.eks. ved sin placering på den lodrette (værdi-) akse det samlede salg for April i området Nord.

• For at beregne værdifelterne er de følgende summeringsfunktioner tilgængelige for alle typer kildedata undtagen kildedata for onlineanalysebehandling (OLAP).

  Funktion	Opsummerer
  Sum	Summen af værdierne. Dette er standardfunktionen for numeriske data.
  Antal	Antallet af dataværdier. Sumfunktionen Antal fungerer på samme måde som funktionen TÆLV. Antal er standardfunktionen for andre data end tal.
  Middel	Gennemsnittet af værdierne.
  Maks.	Den største værdi.
  Min.	Den mindste værdi.
  Produkt	Produktet af værdierne.
  Antal tal	Antallet af dataværdier, der er tal. Summeringsfunktionen Antal tal fungerer på samme måde som regnearkets funktion TÆL.
  Stdafv	En estimering af standardafvigelsen i en population, hvor prøven er et undersæt af hele populationen.
  StdafvP	Standardafvigelsen for en population, hvor populationen er alle de data, der skal summeres.
  Varians	En estimering af variansen af en population, hvor prøven er et undersæt af hele populationen.
  Variansp	Variansen i en population, hvor populationen er alle de data, der skal summeres.

• Brugerdefinerede beregninger    En brugerdefineret beregning viser værdier, der er baseret på andre elementer eller celler i dataområdet. Du kan f.eks. få vist værdierne i datafeltet Summen af salg som en procentdel af salget for Marts eller som en løbende total af elementerne i feltet Måned.

  Følgende funktioner er tilgængelige for brugerdefinerede beregninger i værdifelter.

  Funktion	Resultat
  Ingen beregning	Viser den værdi, der er angivet i feltet.
  % af hovedtotal	Viser værdier som en procentdel af hovedtotalen af alle værdierne eller datapunkterne i rapporten.
  % af kolonnetotal	Viser alle værdierne i hver kolonne eller serie som en procentdel af totalen for kolonnen eller serien.
  % af rækketotal	Viser værdien i hver enkelt række eller kategori som en procentdel af totalen for rækken eller kategorien.
  % af	Viser værdier som en procentdel af værdien af grundtabelposten i dens grundfelt.
  % af overordnet rækketotal	Beregner værdier som følger: (værdi for element)/(værdien af det overordnede element i rækker)
  % af overordnet kolonnetotal	Beregner værdier som følger: (værdi for element)/(værdien af det overordnede element i kolonner)
  % af overordnet total	Beregner værdier som følger: (værdien af elementet)/(værdien af det overordnede element til det valgte grundfelt)
  Forskellig fra	Viser værdier som forskellen fra værdien af grundtabelposten i grundfeltet.
  % forskellig fra	Viser værdier som procentuel forskel fra værdien af grundtabelposten i grundfeltet.
  Løbende total i	Viser værdien for efterfølgende elementer i grundfeltet som en løbende total.
  % af løbende total i...	Beregner værdien for efterfølgende elementer i grundfeltet, der vises som løbende total i procenter.
  Rangér fra mindste til største	Viser rangeringen af de valgte værdier i et bestemt felt. Viser det mindste element i feltet som 1, og hver større værdi vises med en højere rangeringsværdi.
  Rangér fra største til mindste	Viser rangeringen af de valgte værdier i et bestemt felt. Viser det største element i feltet som 1, og hver mindre værdi vises med en højere rangeringsværdi.
  Indeks	Beregner værdier som følger: ((værdi i celle) x (Samlet hovedtotal)) / ((Hovedtotal i række) x (Hovedtotal i kolonne))

• Formler    Hvis summeringsfunktioner og brugerdefinerede beregninger ikke giver de resultater, du ønsker, kan du oprette dine egne formler i beregnede felter eller beregnede elementer. Du kan f.eks. tilføje et beregnet element med formlen for salgsprovisioner, som kunne være forskellige fra område til område. Rapporten vil derefter automatisk medtage provision i subtotaler og hovedtotaler.

Beregninger og indstillinger, der er tilgængelige i en rapport, afhænger af, om kildedataene stammer fra en OLAP-database eller en ikke-OLAP-datakilde.

• Beregninger baseret på OLAP-kildedata    For pivottabeller, der er oprettet ud fra OLAP-kuber, er de opsummerede værdier forudberegnede på OLAP-serveren, før Excel viser resultaterne. Du kan ikke ændre, hvordan disse forudberegnede værdier beregnes i pivottabellen. Du kan f.eks. ikke ændre summeringsfunktionen, der bruges til at beregne datafelter eller subtotaler, eller tilføje beregnede felter eller beregnede elementer.

  Hvis OLAP-serveren leverer beregnede felter, kaldet beregnede medlemmer, vises disse felter også på pivottabelfeltlisten. Du får ligeledes vist alle de beregnede felter og beregnede elementer, der er oprettet af makroer, som blev skrevet i Visual Basic for Applications (VBA) og gemt i projektmappen, men du kan ikke ændre disse felter eller elementer. Hvis du har brug for andre typer beregninger, kan du kontakte administratoren af OLAP-databasen.

  For OLAP-kildedata kan du medtage eller udelade værdierne for skjulte elementer ved beregning af subtotaler og hovedtotaler.

• Beregninger baseret på ikke-OLAP-kildedata    I pivottabeller, der er baseret på andre typer eksterne data eller regnearksdata, anvender Excel summeringsfunktionen Sum til at beregne værdifelter, der indeholder numeriske data, og summeringsfunktionen Tæl til at beregne datafelter, der indeholder tekst. Du kan vælge en anden summeringsfunktion, f.eks. Gennemsnit, Maks eller Min til videre analyse og tilpasning af dine data. Du kan også oprette dine egne formler, der bruger elementer fra rapporten eller andre regnearksdata ved at oprette et beregnet felt eller et beregnet element i et felt.

Du kan kun oprette formler i rapporter, der er baseret på ikke-OLAP-kildedata. Du kan ikke bruge formler i rapporter, der er baseret på en OLAP-database. Når du bruger formler i pivottabeller, skal du kende de følgende formelsyntaksregler og formelfunktionalitet:

• Formelelementer til pivottabeller    I formler, som du opretter for beregnede felter og beregnede elementer, kan du bruge operatorer og udtryk, som du gør det i andre regnearksformler. Du kan bruge konstanter og referere til data fra rapporten, men du kan ikke bruge cellereferencer eller definerede navne. Du kan ikke bruge regnearksfunktioner, der kræver cellereferencer eller definerede navne som argumenter, og du kan ikke bruge matrixfunktioner.

• Felt- og elementnavne    Excel bruger felt- og elementnavne til at identificere elementerne i en rapport i dine formler. I følgende eksempel bruger dataene i området C3:C9 feltnavnet Mejeriprodukter. Et beregnet element i feltet Type, der foretager et skøn over salget af et nyt produkt baseret på salget af mejeriprodukter, kunne anvende en formel som f.eks = Mejeriprodukter * 115%.

  

  : I et pivotdiagram vises feltnavnene i pivottabelfeltlisten, og elementnavne kan ses i hver feltrulleliste. Du må ikke forveksle navnene med dem, du ser i tip til diagrammer, som i stedet afspejler serie- og datapunktnavne.

• Formler, der arbejder med summeringstotaler og ikke med individuelle poster    Formler for beregnede felter arbejder med summering af de underliggende data for alle felter i formlen. Formlen for beregnet felt = Salg * 1.2 multiplicerer f.eks. summen af salg for hver type og område med 1,2; den multiplicerer ikke hvert enkelt salg med 1,2 for derefter at lægge de multiplicerede beløb sammen.

  Formler for beregnede elementer arbejder med de enkelte poster. Formlen for beregnede elementer = Mejeriprodukter * 115% multiplicerer f.eks. hver enkelt salg af mejeriprodukter med 115 %, hvorefter de multiplicerede beløb lægges sammen i Værdiområdet.

• Mellemrum, tal og symboler i navne    I et navn, der indeholder mere end ét felt, kan felterne være i vilkårlig rækkefølge. I eksemplet ovenfor kan cellerne C6:D6 være 'April Nord' eller 'Nord April'. Brug anførselstegn omkring navne, der er mere end ét ord lange, eller som indeholder tal eller symboler.

• Totaler    Formler kan ikke referere til totaler (f.eks. Total for marts, Total for april og Hovedtotal i eksemplet).

• Feltnavne i elementreferencer    Du kan medtage feltnavnet i en reference til et element. Elementnavnet skal være i kantede parenteser – f.eks. Område[Nord]. Brug dette format for at undgå #NAME? fejl, når to elementer i to forskellige felter i en rapport har samme navn. Hvis en rapport f.eks. har et element med navnet Kød i feltet Type og et andet element med navnet Kød i feltet Kategori, kan du forhindre #NAME? fejl ved at referere til elementer som Type[Kød] og Kategori[Kød].

• Henvisning til elementer efter placering    Du kan referere til et element ud fra dets placering i rapporten, således som det aktuelt er sorteret og vist. Type[1] er Mejeriprodukter og Type[2] er Alt godt fra havet. Det element, der henvises til på denne måde, kan ændres, når placeringen af elementer skifter, eller forskellige elementer vises eller skjules. Skjulte elementer tælles ikke med i dette indeks.

  Du kan bruge relative placeringer til at referere til elementer. Placeringerne bestemmes i forhold til det beregnede element, der indeholder formlen. Hvis Syd er det aktuelle område, er Område[-1]Nord; hvis Nord er det aktuelle område, er Område[+1]Syd. Et beregnet element kan f.eks. bruge formlen = Område[-1] * 3%. Hvis den placering, du angiver, befinder sig før det første element eller efter det sidste element i feltet, resulterer formlen i en #REF! fejl.

Hvis du vil bruge formler i et pivotdiagram, kan du oprette formlerne i den tilknyttede pivottabel, hvor du kan se de enkelte værdier, der udgør dine data, og derefter kan du se resultaterne i pivotdiagrammet grafisk.

Nedenstående pivotdiagram viser f.eks. den enkelte sælgers salg pr. område:

For at se, hvordan salget ville se ud, hvis det steg med 10 procent, kan du oprette et beregnet felt i den tilknyttede pivottabel, der bruger en formel som = Salg * 110%.

Resultatet vises straks i pivotdiagrammet som vist i nedenstående diagram:

For at få vist et separat datamærke for salg i område Nord minus transportomkostninger på 8 %, kan du oprette et beregnet element i feltet Område med en formel, f.eks. = Nord – (Nord * 8%).

Det resulterende diagram ville se således ud:

Et beregnet element, der er oprettet i feltet Sælger ville imidlertid vises som en serie, der er repræsenteret i forklaringen, og vises i diagrammet som et datapunkt i hver kategori.