Hvis du leser denne opplæringen, er det en stor sjanse for at du har hørt om (eller til og med brukt) Excel -pivottabellen. Det er en av de kraftigste funksjonene i Excel (ingen spøk).
Den beste delen med å bruke et pivottabell er at selv om du ikke vet noe i Excel, kan du fortsatt gjøre ganske fantastiske ting med det med en veldig grunnleggende forståelse av det.
La oss komme i gang.
Klikk her for å laste ned eksempeldataene og følge med.
Hva er et svingbord og hvorfor bør du bry deg?
Et pivottabell er et verktøy i Microsoft Excel som lar deg raskt oppsummere enorme datasett (med noen få klikk).
Selv om du er helt ny i Excel -verdenen, kan du enkelt bruke et pivottabell. Det er like enkelt som å dra og slippe rader/kolonneoverskrifter for å lage rapporter.
Anta at du har et datasett som vist nedenfor:
Dette er salgsdata som består av ~ 1000 rader.
Den har salgsdataene etter region, forhandlertype og kunde.
Nå vil sjefen din kanskje vite noen ting fra disse dataene:
- Hva var det totale salget i Sør -regionen i 2016?
- Hva er de fem beste forhandlerne etter salg?
- Hvordan sammenlignet The Home Depots ytelse seg med andre forhandlere i Sør?
Du kan fortsette og bruke Excel -funksjoner for å gi deg svarene på disse spørsmålene, men hva om plutselig sjefen din kommer med en liste med fem spørsmål til.
Du må gå tilbake til dataene og lage nye formler hver gang det er en endring.
Det er her Excel pivottabeller er veldig nyttige.
I løpet av sekunder vil et pivottabell svare på alle disse spørsmålene (som du vil lære nedenfor).
Men den virkelige fordelen er at den kan imøtekomme din flinke datadrevne sjef ved å svare på spørsmålene hans umiddelbart.
Det er så enkelt, du kan like gjerne ta noen minutter og vise sjefen din hvordan du gjør det selv.
Forhåpentligvis har du en ide om hvorfor pivottabeller er så fantastiske. La oss gå videre og lage et pivottabell ved hjelp av datasettet (vist ovenfor).
Sette inn et pivottabell i Excel
Her er trinnene for å lage en pivottabell ved hjelp av dataene vist ovenfor:
- Klikk hvor som helst i datasettet.
- Gå til Sett inn -> Tabeller -> Pivottabell.
- I dialogboksen Opprett pivottabell fungerer standardalternativene fint i de fleste tilfeller. Her er et par ting du kan sjekke inn på:
- Tabell/område: Det er fylt ut som standard basert på datasettet ditt. Hvis dataene dine ikke har tomme rader/kolonner, identifiserer Excel automatisk det riktige området. Du kan endre dette manuelt om nødvendig.
- Hvis du vil opprette pivottabellen på et bestemt sted, under alternativet "Velg hvor du vil at pivottabellrapporten skal plasseres", angir du plasseringen. Ellers opprettes et nytt regneark med pivottabellen.
- Klikk OK.
Så snart du klikker OK, opprettes et nytt regneark med pivottabellen i.
Mens pivottabellen er opprettet, ser du ingen data i den. Alt du ser er pivottabellnavnet og en enkeltlinjeanvisning til venstre og pivottabellfelt til høyre.
Nå før vi hopper inn i å analysere data ved hjelp av dette pivottabellen, la oss forstå hva som er muttere og bolter som lager et Excel -pivottabell.
Nøtter og bolter i et Excel -pivottabell
For å bruke et pivottabell effektivt, er det viktig å kjenne komponentene som lager et pivottabell.
I denne delen lærer du om:
- Pivot Cache
- Verdier Område
- Rader område
- Kolonner Område
- Filtreringsområde
Pivot Cache
Så snart du oppretter en pivottabell ved hjelp av dataene, skjer det noe i backend. Excel tar et øyeblikksbilde av dataene og lagrer dem i minnet. Dette øyeblikksbildet kalles Pivot Cache.
Når du oppretter forskjellige visninger ved hjelp av en pivottabell, går ikke Excel tilbake til datakilden, snarere bruker den Pivot -hurtigbufferen til å raskt analysere dataene og gi deg sammendraget/resultatene.
Grunnen til at en pivotbuffer blir generert, er for å optimalisere pivottabellens funksjon. Selv når du har tusenvis av rader med data, er en pivottabell superrask i å oppsummere dataene. Du kan dra og slippe elementer i rader/kolonner/verdier/filtre -bokser, og det vil umiddelbart oppdatere resultatene.
Merk: En ulempe med pivotbufferen er at den øker størrelsen på arbeidsboken din. Siden det er en kopi av kildedataene, blir en kopi av dataene lagret i Pivot Cache når du oppretter en pivottabell.
Les mer: Hva er Pivot Cache og hvordan bruke den best.
Verdier Område
Verdiområdet er det som inneholder beregningene/verdiene.
Basert på datasettet vist i begynnelsen av opplæringen, hvis du raskt vil beregne totalt salg etter region i hver måned, kan du få en pivottabell som vist nedenfor (vi ser hvordan du lager dette senere i opplæringen) .
Området markert med oransje er Verdier -området.
I dette eksemplet har den det totale salget i hver måned for de fire regionene.
Rader område
Overskriftene til venstre for Verdier -området utgjør rader -området.
I eksemplet nedenfor inneholder radene områdene (markert med rødt):
Kolonner Område
Overskriftene øverst i Verdier -området gjør kolonnene til området.
I eksemplet nedenfor inneholder kolonneområdet månedene (markert med rødt):
Filtreringsområde
Filtreområdet er et valgfritt filter som du kan bruke til å gå nærmere inn i datasettet.
For eksempel, hvis du bare vil se salget for multiline -forhandlere, kan du velge det alternativet fra rullegardinmenyen (uthevet i bildet nedenfor), og pivottabellen oppdateres bare med dataene for multiline -forhandlere.
Analysere data ved hjelp av pivottabellen
La oss nå prøve å svare på spørsmålene ved å bruke pivottabellen vi har laget.
Klikk her for å laste ned eksempeldataene og følge med.
For å analysere data ved hjelp av en pivottabell, må du bestemme hvordan du vil at dataoppsummeringen skal se ut i det endelige resultatet. For eksempel vil du kanskje ha alle regionene til venstre og det totale salget rett ved siden av det. Når du har denne klarheten i tankene, kan du ganske enkelt dra og slippe de relevante feltene i pivottabellen.
I delen Pivot Tabe Fields har du feltene og områdene (som fremhevet nedenfor):
Feltene opprettes basert på backend -dataene som brukes for pivottabellen. Område -delen er stedet du plasserer feltene i, og i henhold til hvor et felt går, oppdateres dataene dine i pivottabellen.
Det er en enkel dra og slipp -mekanisme, der du ganske enkelt kan dra et felt og sette det i et av de fire områdene. Så snart du gjør dette, vises det i pivottabellen i regnearket.
La oss nå prøve å svare på spørsmålene lederen din hadde ved å bruke dette pivottabellen.
Q1: Hva var det totale salget i Sør -regionen?
Dra Region -feltet i Rad -området og Inntekt -feltet i Verdier -området. Det ville automatisk oppdatere pivottabellen i regnearket.
Vær oppmerksom på at så snart du slipper Inntekt -feltet i Verdier -området, blir det Sum av inntekter. Som standard summerer Excel alle verdiene for en gitt region og viser totalen. Hvis du vil, kan du endre dette til tellings-, gjennomsnitts- eller andre statistikkberegninger. I dette tilfellet er summen det vi trengte.
Svaret på dette spørsmålet ville være 21225800.
Q2 Hva er de fem beste forhandlerne etter salg?
Dra kundefeltet i radområdet og inntektsfeltet i verdiområdet. I tilfelle det er andre felt i områdeseksjonen, og du vil fjerne det, bare velg det og dra det ut av det.
Du får et pivottabell som vist nedenfor:
Vær oppmerksom på at elementene (i dette tilfellet kundene) som standard er sortert i alfabetisk rekkefølge.
For å få de fem beste forhandlerne, kan du ganske enkelt sortere denne listen og bruke de fem beste kundenavnene. Å gjøre dette:
- Høyreklikk på en celle i Verdier-området.
- Gå til Sorter -> Sorter størst til minste.
Dette vil gi deg en sortert liste basert på totalt salg.
Spørsmål 3: Hvordan sammenlignet The Home Depots ytelse med andre forhandlere i Sør?
Du kan gjøre mye analyse for dette spørsmålet, men la oss bare prøve å sammenligne salget.
Dra regionfeltet i radene. Dra nå Kundefeltet i Rader -området under Region -feltet. Når du gjør dette, vil Excel forstå at du først vil kategorisere dataene dine etter region og deretter etter kunder i regionene. Du har noe som vist nedenfor:
Dra nå Inntektsfeltet i Verdier -området, og du får salget for hver kunde (så vel som den generelle regionen).
Du kan sortere forhandlerne basert på salgstallene ved å følge trinnene nedenfor:
- Høyreklikk på en celle som har salgsverdien for enhver forhandler.
- Gå til Sorter -> Sorter størst til minste.
Dette ville umiddelbart sortere alle forhandlerne etter salgsverdien.
Nå kan du raskt skanne gjennom Sør -regionen og identifisere at Home Depot -salget var 3004600, og det gjorde det bedre enn fire forhandlere i Sør -regionen.
Nå er det mer enn én måte å flå katten på. Du kan også sette regionen i filterområdet og deretter bare velge Sør -regionen.
Klikk her for å laste ned eksempeldata.
Jeg håper denne opplæringen gir deg en grunnleggende oversikt over Excel -pivottabeller og hjelper deg med å komme i gang med det.
Her er noen flere pivottabellopplæringer du kan like:
- Klargjøre kildedata for pivottabell.
- Slik bruker du betinget formatering i et pivottabell i Excel.
- Slik grupperer du datoer i pivottabeller i Excel.
- Hvordan gruppere tall i pivottabell i Excel.
- Slik filtrerer du data i en pivottabell i Excel.
- Bruke skiver i Excel -pivottabell.
- Hvordan erstatte tomme celler med nuller i Excel -pivottabeller.
- Slik legger du til og bruker et Excel -pivottabell Beregnede felt.
- Slik oppdaterer du pivottabell i Excel.
