Pivottabeller er et fantastisk innebygd rapporteringsverktøy i Excel. Selv om de vanligvis brukes til å oppsummere data med totaler, kan du også bruke dem til å beregne prosentandelen av endring mellom verdier. Enda bedre: Det er enkelt å gjøre.
Du kan bruke denne teknikken til å gjøre alle slags ting – stort sett hvor som helst du ønsker å se hvordan en verdi sammenlignes med en annen. I denne artikkelen skal vi bruke det enkle eksempelet på å beregne og vise prosenten som den totale salgsverdien endres med måned for måned.
Her er arket vi skal bruke.
Det er et ganske typisk eksempel på et salgsark som viser bestillingsdato, kundenavn, selger, total salgsverdi og et par andre ting.
For å gjøre alt dette, skal vi først formatere verdiområdet vårt som en tabell i Excel, og så skal vi lage en pivottabell for å lage og vise beregningene våre for prosentvis endring.
Innholdsfortegnelse
Formatere området som en tabell
Hvis dataområdet ditt ikke allerede er formatert som en tabell, oppfordrer vi deg til å gjøre det. Data lagret i tabeller har flere fordeler fremfor data i celleområder i et regneark, spesielt når du bruker pivottabeller (les mer om fordelene ved å bruke tabeller).
For å formatere et område som en tabell, velg celleområdet og klikk på Sett inn > Tabell.
Sjekk at området er riktig, at du har overskrifter i den første raden i det området, og klikk deretter «OK.»
Området er nå formatert som en tabell. Å navngi tabellen vil gjøre det lettere å referere til i fremtiden når du oppretter pivottabeller, diagrammer og formler.
Klikk på «Design»-fanen under Tabellverktøy, og skriv inn et navn i boksen ved starten av båndet. Denne tabellen har fått navnet «Salg».
Du kan også endre stilen på bordet her hvis du vil.
Lag en pivottabell for å vise prosentvis endring
La oss nå fortsette med å lage pivottabellen. Fra den nye tabellen klikker du på Sett inn > Pivottabell.
Opprett pivottabell-vinduet vises. Den vil automatisk ha oppdaget bordet ditt. Men du kan velge tabellen eller området du vil bruke for pivottabellen på dette tidspunktet.
Grupper datoene i måneder
Vi vil deretter dra datofeltet som vi vil gruppere etter inn i radområdet i pivottabellen. I dette eksemplet heter feltet Bestillingsdato.
Fra og med Excel 2016 blir datoverdier automatisk gruppert i år, kvartaler og måneder.
Hvis din versjon av Excel ikke gjør dette, eller du bare vil endre grupperingen, høyreklikker du på en celle som inneholder en datoverdi og velger deretter «Gruppe»-kommandoen.
Velg gruppene du vil bruke. I dette eksemplet er bare år og måneder valgt.
Året og måneden er nå felt som vi kan bruke til analyse. Månedene er fortsatt navngitt som bestillingsdato.
Legg til verdifeltene i pivottabellen
Flytt År-feltet fra rader og inn i filterområdet. Dette gjør det mulig for brukeren å filtrere pivottabellen i et år, i stedet for å fylle pivottabellen med for mye informasjon.
Dra feltet som inneholder verdiene (Total salgsverdi i dette eksemplet) du vil beregne og presentere endring inn i Verdi-området to ganger.
Det ser kanskje ikke så mye ut ennå. Men det vil endre seg veldig snart.
Begge verdifeltene vil som standard ha summen og har for øyeblikket ingen formatering.
Verdiene i den første kolonnen ønsker vi å beholde som totaler. De krever imidlertid formatering.
Høyreklikk på et tall i den første kolonnen og velg «Nummerformatering» fra hurtigmenyen.
Velg «Regnskap»-formatet med 0 desimaler fra dialogboksen Formater celler.
Pivottabellen ser nå slik ut:
Opprett kolonnen Prosentvis endring
Høyreklikk på en verdi i den andre kolonnen, pek på «Vis verdier», og klikk deretter på alternativet «% forskjell fra».
Velg «(Forrige)» som basiselement. Dette betyr at gjeldende månedsverdi alltid sammenlignes med forrige måneds verdi (Ordre Dato-feltet).
Pivottabellen viser nå både verdiene og prosentvis endring.
Klikk i cellen som inneholder radetiketter og skriv inn «Måned» som overskrift for den kolonnen. Klikk deretter i overskriftscellen for den andre verdikolonnen og skriv inn «Varians».
Legg til noen variasjonspiler
For virkelig å finpusse denne pivottabellen, ønsker vi å visualisere prosentvis endring bedre ved å legge til noen grønne og røde piler.
Disse vil gi oss en fin måte å se om en endring har vært positiv eller negativ.
Klikk på en av verdiene i den andre kolonnen, og klikk deretter Hjem > Betinget formatering > Ny regel. I vinduet Rediger formateringsregel som åpnes, gjør du følgende:
Velg alternativet «Alle celler som viser «Varians»-verdier for bestillingsdato.
Velg «Icon Sets» fra Format Style-listen.
Velg de røde, gule og grønne trekantene fra Icon Style-listen.
I Type-kolonnen endrer du listealternativet til å si «Nummer» i stedet for prosentandel. Dette vil endre verdikolonnen til 0-er. Akkurat det vi ønsker.
Klikk «OK» og den betingede formateringen brukes på pivottabellen.
Pivottabeller er et utrolig verktøy og en av de enkleste måtene å vise den prosentvise endringen over tid for verdier.