Høyt på listen over virkelig hjelpsomme funksjoner i Excel har Pivot-tabellering sin rettmessige plass. Dersom du skal behandle store datamengder, tror jeg kanskje det ligger på 1.plass. Pivot-tabeller gjør det mulig å trekke informasjon ut av store datamengder på en måte som ville ta ørkesløst mye tid på andre måter.
I et regneark presenteres gjerne data i flate tabeller med flere dimensjoner. En Pivot-tabell oppsummerer og beregner nøkkeltall på data raskt og effektivt, og gjør det lett å endre den dimensjonen i dataene som ønskes studert.
Data i eksempelet
For å vise hvordan dette kan brukes har jeg tatt utgangspunkt i en fil med 4 300 fakturalinjer. Det sier seg selv at dette er en uhåndterlig datamengde om den skal behandles manuelt. Hver linje inneholder følgende interessante informasjon:
- Fakturanummer
- Produkt
- Kunde
- Avdeling
- Fakturabeløp
- Forfallsdato
Listen kan selvsagt sorteres manuelt, og den kan filtreres, men alle de metodene er ganske manuelle, og dersom du ønsker nøkkeltall for dataene, må disse hentes ut ved hjelp av formler, og disse tar det tid å lage. Men pivot-verktøyet i Excel har alt dette innebygget.
For å kunne bruke pivot-tabell, må alle kolonnene ha et navn. Har de ikke det, kan du ikke lage en tabell. I mitt eksempel har jeg laget noen hjelpekolonner for å kunne presentere tidsdimensjonen på en bedre måte. Datofunksjoner i Excel har jeg beskrevet i forrige post.
Jeg lager 3 datobaserte kolonner med formler som leser kolonnen Betalingsfrist:
- År
- Måned
- Dag
I tillegg lager jeg en kolonne som sier om fordringen er forfalt eller ikke. Den tar utgangspunkt i kolonnen Betalingsfrist og Dagens dato, og formelen ser slik ut (i celle J2):
=IF(F2>$M$1;»Ikke forfalt»;»Forfalt»)
Pivot på 1-2-3
Da er det bare å pivotere tallene. Merk alle data, inklusive header, gå til fanen Insert (Sett inn på norsk) og klikk på Pivot-tabell. Den foreslår å bruke det området du har merket, og du kan enten få dataene plassert i eksisterende ark, eller et nytt ark. Jeg bruker som regel et nytt ark, ellers blir det fort uoversiktlig.
Når du har gjort det får du opp et liste av felt du kan velge mellom:
Før du gjør det bør du tenke gjennom hva det er du ønsker å studere. Det vil avgjøre hva det er hensiktsmessig å ha i kolonner, hva det er lurt å ha på radene og hva det kan være lurt å filtrere bort. La oss anta at vi ønsker å se på våre produkter, produsert i ulike avdelinger. Dessuten ønsker vi mulighet til å bare se på forfalte fordringer og i tillegg å velge bare noen kunder. Oppsett av en tabell med de ønskene vil se omtrent slik ut i vårt eksempel:
Om du så ønsker å filtrere bort noe informasjon er det enkelt, ved hjelp av de filterfeltene som ble definert i oppsettet av tabellen. La oss anta at vi ønsker å se bare forfalte fordringer, og bare for Kunde 3:
Pivot-graf
I tillegg kan pivot presenteres grafisk. Det gjør du på samme måte som når du lager en tabell. Merk det samme området, og i stedet for å sette inn en pivot-tabell, setter du inn en pivot-graf:
Velg så de samme feltene og filterne som forrige gang.
Du kan selvsagt bruke Excels vanlige grafer og formateringer, og dessuten endre i presentasjonen direkte i grafen.
Flere tabeller i en arbeidsbok
Om du ønsker å fokusere på andre dimesjoner i datasettet, kan du enten endre på den eksisterende tabellen, eller lage en ny basert på de samme dataene. La oss anta at du ønsker å se på forfallsstrukturen i ikke forfalte fordringer for alle kunder. Jeg lager en ny tabell:
I dette eksempelet har jeg brukt hjelpekolonnene jeg laget. Det er selvsagt mulig å bruke feltet Betalingsfrist direkte også, men jeg synes systematikken blir bedre på en slik forfallsliste ved å bruke hjelpekolonnene. Data kan enklere drilles opp og ned, og filtreres på en vesentlig bedre måte.
Filtrering ved hjelp av slicers
Som vist kan data filtreres bort. En raffere måte å gjøre det på enn inne i selve tabellen, er å bruke såkalte slicers. Det er små menyer som lages slik at de er lett tilgjengelige og kan klikkes på. Denne muligheten finnes i Pivot-tabell menyen. Stå inne i tabellen og klikk på fanen Options under Pivot-table tools. Da kan du velge felter du ønsker å bruke til å filtrere data og får dem opp som menyer:
I en Pivottabell som skal brukes mer enn én gang, kan det være en bedre løsning enn å filtrere som vist lenger oppe.
Valg av nøkkeltall på verdifelter
Det er mulig å bruke flere typer nøkkeltall for presentasjon av tall i en pivot-tabell. I eksempelet over er det Sum som er brukt. Men du kan også bruke for eksempel antall, standardavvik, varians, produkt og så videre. Høyreklikk på verdifeltet i tabelllisten til høyre, velg value field (verdifelt) og velg det nøkkeltallet du ønsker å bruke.
Det er mange muligheter for formatering og presentasjon, egentlig like mange som det er i et vanlig Excel regneark. I tillegg er det mulig å hente data fra en ekstern datakilde, for eksempel et datavarehus. Mer om det en annen gang.
Det er egentlig bare å sette i gang, bruke hjelpefunksjonen og lage de lekreste og mest fleksible rapporter på en utrolig effektiv måte.
