Excel kommer med så mange kraftige dataanalyseverktøy og funksjoner at du kanskje lurer på hvorfor du trenger å lære enda en: pivottabellen. Det korte svaret er at pivottabellen er et nyttig våpen for å legge til dataanalysearsenalet ditt. Det lange svaret er at pivottabeller er verdt å lære fordi de ikke bare har én eller to, men en lang rekke fordeler.
La oss telle måtene: Excel-pivottabeller er enkle å bygge og vedlikeholde; de utfører store og komplekse beregninger utrolig raskt; du kan raskt og enkelt oppdatere dem for å ta hensyn til nye data; Pivottabeller er dynamiske, så komponenter kan enkelt flyttes, filtreres og legges til; og til slutt kan pivottabeller bruke de fleste formateringsalternativene du kan bruke på vanlige Excel-områder og celler.
Å, vent, det er en til: Pivottabeller kan tilpasses fullt ut, så du kan bygge hver rapport slik du vil. Her er ti teknikker som vil gjøre deg til en PivotTable-proff.
Finn ut hvordan du lager en pivottabell ved hjelp av Excels hurtiganalyseverktøy .
Som standard, når du klikker inne i pivottabellen, viser Excel oppgaveruten pivottabellfelt og skjuler deretter oppgaveruten pivottabellfelt igjen når du klikker utenfor pivottabellrapporten.
Ikke noe galt med det på forsiden. Men hvis du vil jobbe med kommandoene i båndets PivotTable Tools kontekstuelle fane, må du ha minst én celle i PivotTable-rapporten valgt. Men å velge en hvilken som helst pivottabellcelle betyr at du også har oppgaveruten Pivottabellfelt som tar opp dyrebare eiendommer på skjermen.
Heldigvis lar Excel deg også slå av og på oppgaveruten for pivottabellfelter for hånd, noe som gir deg mer plass til å vise pivottabellrapporten. Du kan deretter slå på oppgaveruten PivotTable Fields igjen når du trenger å legge til, flytte eller slette felt.
For å slå av og på oppgaveruten for pivottabellfelt, følg disse trinnene (alle to!):
Klikk inne i pivottabellen.
Velg Analyser → Vis → Feltliste.
En rask måte å skjule PivotTable Fields-oppgaveruten på er å klikke på Lukk-knappen i øvre høyre hjørne av ruten.
Som standard er oppgaveruten PivotTable Fields delt inn i to seksjoner: Felt-delen viser datakildens tilgjengelige felt og vises øverst i ruten, og Areas-delen inneholder PivotTable-områdene – Filtre, Kolonner, Rader og Verdier – og vises nederst i ruten. Du kan tilpasse denne layouten slik at den passer måten du jobber på. Her er mulighetene:
Her er trinnene du må følge for å endre oppgaveruteoppsettet for pivottabellfelt:
Klikk en hvilken som helst celle inne i pivottabellen.
Klikk Verktøy.
Verktøy-knappen er den med tannhjulikonet.
Klikk på Verktøy for å se layoutalternativene for PivotTable Fields-oppgaveruten.
Excel viser oppgavepanelverktøyene for pivottabellfelt.
Klikk på oppsettet du vil bruke.
Excel endrer oppgaveruteoppsettet for pivottabellfelt basert på valget ditt.
Mens du har oppgaveruteverktøyene for pivottabellfelt vist, merk at du også kan sortere feltlisten . Standard er Sorter i datakilderekkefølge, som betyr at Excel viser feltene i samme rekkefølge som de vises i datakilden. Hvis du foretrekker å sortere feltene alfabetisk, klikker du på Sorter A til Å.
Den største fordelen med å bruke pivottabeller er at de gir deg en enkel metode for å oppsummere store datamengder til en kortfattet rapport for dataanalyse. Kort sagt, pivottabeller viser deg skogen i stedet for trærne. Noen ganger trenger du imidlertid å se noen av disse trærne. Hvis du for eksempel studerer resultatene av en markedsføringskampanje, kan pivottabellen din vise deg det totale antallet ørepropper som er solgt som et resultat av en 1 gratis med 10-kampanje. Men hva om du vil se detaljene som ligger til grunn for dette nummeret? Hvis kildedataene dine inneholder hundrevis eller tusenvis av poster, må du filtrere dataene på en eller annen måte for å se akkurat postene du ønsker.
Du har solgt 792 ørepropper, men hva er detaljene bak dette nummeret?
Heldigvis gir Excel deg en enklere måte å se postene du vil ha, ved at du kan direkte se detaljene som ligger til grunn for en bestemt dataverdi. Dette kalles å bore ned til detaljene. Når du ser nærmere på en bestemt dataverdi i en pivottabell, går Excel tilbake til kildedataene, trekker ut postene som utgjør dataverdien, og viser deretter postene i et nytt regneark. For en pivottabell basert på et område eller en tabell, tar denne utvinningen bare et sekund eller to, avhengig av hvor mange poster kildedataene inneholder.
For å se nærmere på detaljene som ligger til grunn for et pivottabelldatapunkt, bruk en av følgende metoder:
Excel viser de underliggende dataene i et nytt regneark. Dette bildet viser for eksempel detaljene bak de 792 øreproppene som selges med kampanjen 1 gratis med 10 vist i bildet ovenfor.
Detaljene bak ørepluggsalget.
Når du prøver å gå ned til en dataverdis underliggende detaljer, kan Excel vise feilmeldingen Kan ikke endre denne delen av en pivottabellrapport. Denne feilen betyr at funksjonen som normalt lar deg bore ned er slått av. For å slå på denne funksjonen igjen, klikk en hvilken som helst celle inne i pivottabellen og klikk deretter på Analyser → Pivottabell → Alternativer for å vise dialogboksen Alternativer for pivottabell. Klikk kategorien Data, merk av for Aktiver Vis detaljer, og klikk deretter OK.
Den motsatte situasjonen oppstår når du distribuerer arbeidsboken som inneholder pivottabellen og du ikke vil at andre brukere skal gå ned og fylle arbeidsboken med detaljregneark. I dette tilfellet klikker du på Analyser → Pivottabell → Alternativer, klikker på Data-fanen, fjerner merket for Aktiver Vis detaljer, og klikker deretter OK.
Noen ganger vil du kanskje se alle de underliggende kildedataene til en pivottabell. Hvis kildedataene er et område eller en tabell i et annet regneark, kan du se de underliggende dataene ved å vise det regnearket. Hvis kildedataene ikke er så lett tilgjengelige, gir Excel deg imidlertid en rask måte å vise alle de underliggende dataene. Høyreklikk pivottabellens totalsum-celle (det vil si cellen nederst til høyre i pivottabellen) og klikk deretter Vis detaljer. (Du kan også dobbeltklikke på den cellen.) Excel viser alle pivottabellens underliggende data i et nytt regneark.
En av de fine tingene med en pivottabell er at den ligger på et vanlig Excel-regneark, noe som betyr at du kan bruke formateringsalternativer som justeringer og fonter på deler av pivottabellen. Dette fungerer bra, spesielt hvis du har tilpassede formateringskrav.
For eksempel kan det hende du har interne stilretningslinjer som du må følge. Dessverre kan det være tidkrevende å bruke formatering, spesielt hvis du bruker en rekke forskjellige formateringsalternativer. Og den totale formateringstiden kan bli krevende hvis du trenger å bruke forskjellige formateringsalternativer på forskjellige deler av pivottabellen. Du kan redusere tiden du bruker på å formatere pivottabellene betraktelig hvis du bruker en stil i stedet.
En stil er en samling av formateringsalternativer – skrifter, rammer og bakgrunnsfarger – som Excel definerer for forskjellige områder av en pivottabell. En stil kan for eksempel bruke fet, hvit tekst på en svart bakgrunn for etiketter og totalsummer, og hvit tekst på en mørkeblå bakgrunn for elementer og data. Å definere alle disse formatene manuelt kan ta en halv time til en time. Men med stilfunksjonen velger du den du vil bruke for pivottabellen som helhet, og Excel bruker de individuelle formateringsalternativene automatisk.
Excel definerer mer enn 80 stiler, delt inn i tre kategorier: Lys, Medium og Mørk. Light-kategorien inkluderer Pivot Style Light 16, standardformateringen som brukes på pivottabellrapporter du oppretter; og Ingen, som fjerner all formatering fra pivottabellen. Du kan også lage dine egne stilformater.
Her er trinnene du må følge for å bruke en stil på en Excel-pivottabell:
Klikk en hvilken som helst celle i pivottabellen du vil formatere.
Klikk på Design-fanen.
Klikk på Mer-knappen i gruppen Pivottabellstiler.
Stilgalleriet vises.
Klikk på stilen du vil bruke.
Excel bruker stilen.
Det kan hende du finner ut at ingen av de forhåndsdefinerte pivottabellstilene gir deg akkurat det utseendet du ønsker. I så fall kan du definere utseendet selv ved å lage en egendefinert pivottabellstil fra bunnen av.
Excel gir deg enorm fleksibilitet når du lager egendefinerte pivottabellstiler. Du kan formatere 25 separate pivottabellelementer. Disse elementene inkluderer hele tabellen, sidefeltetikettene og -verdiene, den første kolonnen, overskriftsraden, Totaltotal-raden og Totalsum-kolonnen. Du kan også definere striper, som er separate formater som brukes på alternerende rader eller kolonner. For eksempel bruker First Row Stripe formatering på rad 1, 3, 5 og så videre; den andre radstripen bruker formatering på rad 2, 4, 6 og så videre. Striper kan gjøre en lang eller bred rapport lettere å lese.
Å ha kontroll over så mange elementer gjør at du kan lage en tilpasset stil som passer dine behov. For eksempel kan det hende du trenger pivottabellen din for å matche bedriftens farger. På samme måte, hvis pivottabellen vises som en del av en større rapport, kan det hende du trenger pivottabellformateringen for å matche temaet som brukes i den større rapporten.
Den eneste ulempen med å lage en egendefinert pivottabellstil er at du må gjøre det fra bunnen av fordi Excel ikke lar deg tilpasse en eksisterende stil. Boo, Excel! Så hvis du trenger å definere formatering for alle de 25 pivottabellelementene, kan det være tidkrevende å lage en tilpasset stil.
Hvis du fortsatt er klar for det, er her trinnene for å pløye gjennom for å lage din helt egen Excel-pivottabellstil:
Klikk på Design-fanen.
Klikk på Mer i gruppen Pivottabellstiler.
Stilgalleriet vises.
Klikk Ny pivottabellstil.
Dialogboksen Ny pivottabellstil vises.
Den nye PivotTable Style-dialogboksen.
Skriv inn et navn for din egendefinerte stil.
Bruk tabellelementlisten til å velge pivottabellfunksjonen du vil formatere.
Klikk Formater.
Dialogboksen Formater celler vises.
Bruk alternativene i Font-fanen for å formatere elementets tekst.
Du kan velge en skrifttype, en skriftstil (som fet eller kursiv) og en skriftstørrelse. Du kan også velge en understreking, en farge og en gjennomstrekingseffekt.
Bruk alternativene i Border-fanen for å formatere elementets kantlinje.
Du kan velge kantstil, farge og plassering (som venstre kant, toppkant eller begge deler).
Bruk alternativene i Fyll-fanen for å formatere elementets bakgrunnsfarge.
Du kan velge en solid farge eller et mønster. Du kan også klikke på Fylleffekt-knappene for å angi en gradient som endres fra en farge til en annen.
Klikk OK.
Excel returnerer deg til dialogboksen Ny pivottabellstil.
Gjenta trinn 5 til 10 for å formatere andre tabellelementer.
Nytt pivottabellstil-dialogboksen inkluderer en forhåndsvisningsdel som viser deg hvordan stilen vil se ut når den brukes på en pivottabell. Hvis du er spesielt stolt av den nye stilen din, vil du kanskje bruke den til alle pivottabellene dine. Hvorfor ikke? For å fortelle Excel å bruke den nye stilen som standard for enhver fremtidig pivottabell du forfalsker, merk av for Angi som standard pivottabellstil for dette dokumentet.
Når du til slutt er ferdig, klikker du OK.
Excel lagrer den egendefinerte pivottabellstilen.
Merkelig nok, etter at du lukker dialogboksen Ny pivottabellstil, bruker ikke Excel den nye stilen på gjeldende pivottabell. Dum! For å bruke stilen selv, velg en hvilken som helst celle i pivottabellen, klikk Design, klikk på Mer-knappen i Pivottabellstiler-gruppen for å åpne stilgalleriet, og klikk deretter stilen din i Egendefinert-delen som nå vises øverst i galleriet.
Hvis du trenger å gjøre endringer i den egendefinerte stilen, åpner du stilgalleriet, høyreklikker den egendefinerte stilen og klikker på Endre. Bruk dialogboksen Endre pivottabellstil for å gjøre endringene, og klikk deretter OK.
Hvis du finner ut at du trenger å lage en annen egendefinert stil som ligner på en eksisterende egendefinert stil, ikke bry deg om å lage den nye stilen fra bunnen av. Åpne i stedet stilgalleriet, høyreklikk den eksisterende egendefinerte stilen og klikk deretter Dupliser. Juster stilnavnet og formateringen i dialogboksen Endre pivottabellstil, og klikk deretter OK.
Hvis du ikke lenger trenger en egendefinert stil, bør du slette den for å redusere rot i stilgalleriet. Klikk på Design-fanen, åpne PivotTable Styles-galleriet, høyreklikk på den egendefinerte stilen du ikke lenger trenger, og klikk deretter på Slett. Når Excel ber deg om å bekrefte, klikker du OK.
Excel har en ekkel vane med noen ganger ikke å bevare din egendefinerte formatering når du oppdaterer eller bygger om pivottabellen. For eksempel, hvis du brukte en fet skrift på enkelte etiketter, kan disse etikettene gå tilbake til vanlig tekst etter en oppdatering. Excel har en funksjon kalt Bevar formatering som lar deg bevare slik formatering under en oppdatering; du kan beholde din egendefinerte formatering ved å aktivere den.
Bevar formateringsfunksjonen er alltid aktivert i standard pivottabeller. En annen bruker kan imidlertid ha deaktivert denne funksjonen. Det kan for eksempel hende du jobber med en pivottabell laget av en annen person, og han eller hun kan ha deaktivert funksjonen Bevar formatering.
Vær imidlertid oppmerksom på at når du oppdaterer eller bygger om en pivottabell, bruker Excel rapportens gjeldende stilformatering på nytt. Hvis du ikke har spesifisert en stil, bruker Excel standard PivotTable-stil (kalt Pivot Style Light 16); hvis du har spesifisert en stil, bruker Excel den stilen på nytt.
Her er trinnene du må følge for å konfigurere en Excel-pivottabell for å bevare formateringen:
Klikk en hvilken som helst celle i pivottabellen du vil jobbe med.
Velg Analyser → Pivottabell → Alternativer.
Dialogboksen for pivottabellalternativer vises med fanen Layout & Format vist.
Fjern merket for Autotilpass kolonnebredder ved oppdatering.
Hvis du fjerner valget av dette alternativet, forhindrer Excel i å automatisk formatere ting som kolonnebredder når du pivoterer felt.
Merk av for Bevar celleformatering ved oppdatering.
Klikk OK.
Excel bevarer din egendefinerte formatering hver gang du oppdaterer pivottabellen.
Når du oppretter den første pivottabellen i en arbeidsbok, gir Excel den det uinspirerende navnet Pivottabell1. Påfølgende pivottabeller navngis sekvensielt (og like lite inspirerende): Pivottabell2, Pivottabell3, og så videre. Imidlertid gjentar Excel også disse navnene når du bygger nye pivottabeller basert på forskjellige datakilder. Hvis arbeidsboken din inneholder en rekke pivottabeller, kan du gjøre dem lettere å skille ved å gi hver enkelt et unikt og beskrivende navn. Dette er hvordan:
Klikk en hvilken som helst celle i pivottabellen du vil jobbe med.
Klikk Analyser → Pivottabell.
Bruk pivottabellnavn-tekstboksen til å skrive inn det nye navnet for pivottabellen.
Maksimal lengde for et pivottabellnavn er 255 tegn.
Klikk utenfor tekstboksen.
Excel gir nytt navn til pivottabellen.
En standard pivottabell som har minst ett radfelt inneholder en ekstra rad nederst i tabellen. Denne raden er merket Grand Total og inkluderer summen av verdiene knyttet til radfeltelementene. Det kan imidlertid hende at verdien i Grand Total-raden faktisk ikke er en sum. For eksempel, hvis sammendragsberegningen er Gjennomsnitt, inkluderer totalsum-raden gjennomsnittet av verdiene knyttet til radfeltelementene.
På samme måte inneholder en pivottabell som har minst ett kolonnefelt en ekstra kolonne helt til høyre i tabellen. Denne kolonnen er også merket "Grand Total" og inkluderer summen av verdiene knyttet til kolonnefeltelementene. Hvis pivottabellen inneholder både et rad- og et kolonnefelt, har Grand Total-raden også summene for hvert kolonneelement, og Grand Total-kolonnen har også summene for hvert radelement.
Foruten å ta opp plass i pivottabellen, er disse totalsummene ofte ikke nødvendige for dataanalyse. Anta for eksempel at du vil undersøke kvartalsvise salg for selgerne dine for å se hvilke beløp som er over en viss verdi for bonusformål. Fordi din eneste bekymring er de individuelle oppsummeringsverdiene for hver ansatt, er totalsummene ubrukelige. I et slikt tilfelle kan du fortelle Excel om ikke å vise totalsummen ved å følge disse trinnene:
Klikk en hvilken som helst celle i pivottabellen du vil jobbe med.
Klikk Design → Grand Totals.
Excel viser en meny med alternativer for å vise totalsummen.
Klikk på alternativet du foretrekker.
Menyen inneholder fire elementer:
Excel setter det valgte totalsum-alternativet i kraft.
Feltoverskriftene som vises i rapporten er en annen ofte plagsom pivottabellfunksjon. Disse overskriftene inkluderer sorterings- og filtreringsknapper, men hvis du ikke bruker disse knappene, roter feltoverskriftene bare pivottabellen. For å slå av feltoverskriftene, klikk inne i pivottabellen og velg deretter Analyser → Vis → Feltoverskrifter.
Pivottabeller resulterer ofte i store arbeidsbøker fordi Excel må holde styr på mye ekstra informasjon for å holde pivottabellytelsen akseptabel. For å sikre at omberegningen som er involvert i pivotering skjer raskt og effektivt, opprettholder Excel for eksempel en kopi av kildedataene i et spesielt minneområde kalt pivot-cachen.
Hvis du bygger en pivottabell fra data som ligger i en annen arbeidsbok eller i en ekstern datakilde, lagrer Excel kildedataene i pivotbufferen. Dette reduserer tiden det tar for Excel å oppdatere og beregne pivottabellen på nytt. Ulempen er at det kan øke både størrelsen på arbeidsboken og hvor lang tid Excel tar å lagre arbeidsboken. Hvis arbeidsboken din har blitt for stor eller det tar for lang tid å lagre, følg disse trinnene for å fortelle Excel om ikke å lagre kildedataene i pivotbufferen:
Klikk en hvilken som helst celle i pivottabellen.
Klikk på Analyser → Pivottabell → Alternativer.
Dialogboksen Pivottabellalternativer vises.
Klikk på Data-fanen.
Fjern merket for Lagre kildedata med fil.
Klikk OK.
Excel lagrer ikke lenger de eksterne kildedataene i pivotbufferen.
Du må kanskje bruke en pivottabellverdi i en regnearkformel. Du refererer vanligvis til en celle i en formel ved å bruke cellens adresse. Dette vil imidlertid ikke fungere med pivottabeller fordi adressene til rapportverdiene endres når du pivoterer, filtrerer, grupperer og oppdaterer pivottabellen.
For å sikre nøyaktige pivottabellreferanser, bruk Excels GETPIVOTDATA-funksjon. Denne funksjonen bruker datafeltet, pivottabellplasseringen og ett eller flere (rad eller kolonne) felt/elementpar som spesifiserer den nøyaktige verdien du vil bruke. På denne måten, uansett hva pivottabelloppsettet er, så lenge verdien forblir synlig i rapporten, forblir formelreferansen nøyaktig.
Her er syntaksen til GETPIVOTDATA-funksjonen:
GETPIVOTDATA( datafelt , pivottabell , [, felt1, element1][, …]
De to obligatoriske feltene er data_field , som er navnet på feltet du bruker i pivottabellens verdiområde , og pivot_table , som spesifiserer celleadressen til det øvre venstre hjørnet av pivottabellen. Resten av argumentene kommer i par: et feltnavn og et element i det feltet.
For eksempel, her er en GETPIVOTDATA-formel som returnerer PivotTable-verdien der produktfeltet er ørepropper og kampanjefeltet er 1 gratis med 10:
=GETPIVOTDATA("Antall", $A$3, "Produkt", "Ørepropper", "Kampanje", "1 gratis med 10")
GETPIVOTDATA-funksjonen gjør sitt.
GETPIVOTDATA er litt komplisert, men ikke bekymre deg. Du trenger nesten aldri å plukke ut denne funksjonen og alle dens argumenter for hånd. I stedet håndterer Excel alt for deg når du klikker på pivottabellverdien du vil bruke i formelen. Puh!
Hvordan blokkere Microsoft Word fra å åpne filer i skrivebeskyttet modus på Windows Microsoft Word åpner filer i skrivebeskyttet modus, noe som gjør det umulig å redigere dem? Ikke bekymre deg, metodene er nedenfor
Slik fikser du feil ved utskrift av feil Microsoft Word-dokumenter Feil ved utskrift av Word-dokumenter med endrede fonter, rotete avsnitt, manglende tekst eller tapt innhold er ganske vanlig. Men ikke gjør det
Hvis du har brukt pennen eller merkepennen til å tegne på PowerPoint-lysbildene dine under en presentasjon, kan du lagre tegningene til neste presentasjon eller slette dem, slik at du neste gang du viser dem starter med rene PowerPoint-lysbilder. Følg disse instruksjonene for å slette penn- og merkepenntegninger: Slette linje én på […]
Stilbiblioteket inneholder CSS-filer, Extensible Stylesheet Language-filer (XSL) og bilder som brukes av forhåndsdefinerte mastersider, sideoppsett og kontroller i SharePoint 2010. For å finne CSS-filer i stilbiblioteket til et publiseringsnettsted: Velg Site Actions→ View Alt innhold på nettstedet. Innholdet på nettstedet vises. Style-biblioteket ligger i […]
Ikke overveld publikum med gigantiske tall. I Microsoft Excel kan du forbedre lesbarheten til dashbordene og rapportene dine ved å formatere tallene dine slik at de vises i tusenvis eller millioner.
Lær hvordan du bruker SharePoints sosiale nettverksverktøy som lar enkeltpersoner og grupper kommunisere, samarbeide, dele og koble til.
Julianske datoer brukes ofte i produksjonsmiljøer som et tidsstempel og hurtigreferanse for et batchnummer. Denne typen datokoding lar forhandlere, forbrukere og serviceagenter identifisere når et produkt ble laget, og dermed alderen på produktet. Julianske datoer brukes også i programmering, militæret og astronomi. Forskjellig […]
Du kan lage en nettapp i Access 2016. Så hva er en nettapp egentlig? Vel, nettet betyr at det er online, og appen er bare en forkortelse for "applikasjon". En Custom Web App er en online databaseapplikasjon som du får tilgang til fra skyen ved hjelp av en nettleser. Du bygger og vedlikeholder nettappen i skrivebordsversjonen […]
De fleste sidene i SharePoint 2010 viser en liste over navigasjonskoblinger på hurtigstartlinjen langs venstre side av siden. Hurtigstartlinjen viser koblinger til innhold på nettstedet som lister, biblioteker, nettsteder og publiseringssider. Hurtigstartlinjen inneholder to svært viktige lenker: Linken for alt nettstedinnhold: […]
For enkle problemer finner Solver i Excel vanligvis raskt de optimale Solver-variabelverdiene for objektivfunksjonen. Men i noen tilfeller har Solver problemer med å finne Solver-variabelverdiene som optimerer objektivfunksjonen. I disse tilfellene viser Solver vanligvis en melding eller en feilmelding som beskriver eller diskuterer problemet som […]
