Enkel pivot-tabellering

Arkivert som Tips&Triks av i 2012 0 kommentarer

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»)

Rådata og hjelpekolonner

Klikk for større bilde

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.

Sett inn pivot-tabell

Klikk for større bilde

Lag tabell

Klikk for større bilde

Når du har gjort det får du opp et liste av felt du kan velge mellom:

Lag tabell

Klikk for større bilde

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:

Valg av felter

Klikk for større bilde

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:

Filtrert pivot-tabell

Klikk for større bilde

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:

Pivotgraf

Klikk for større bilde

Velg så de samme feltene og filterne som forrige gang.

Enkel pivot-graf

Klikk for større bilde

Du kan selvsagt bruke Excels vanlige grafer og formateringer, og dessuten endre i presentasjonen direkte i grafen.

Formatert pivot-graf

Klikk for større bilde

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:

Forfallsliste

Klikk for større bilde

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:

Bruke av slicers til filtrering

Klikk for større bilde

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.

Verdifelt

Klikk for større bilde

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.

Se også: «Oppdater alle»-makro til pivot-tabeller

Emner: , , , , ,

Legg igjen en kommentar