Arbeide med pivottabeller i Microsoft Excel

Pivottabeller er en av de kraftigste funksjonene i Microsoft Excel. De tillater store mengder data å bli analysert og oppsummert på bare noen få museklikk. I denne artikkelen undersøker vi PivotTables, forstår hva de er, og lærer å lage og tilpasse dem.

Merk: Denne artikkelen er skrevet ved hjelp av Excel 2010 (Beta). Konseptet med en PivotTable har endret seg lite gjennom årene, men metoden for å skape en har endret seg i nesten hver iterasjon av Excel. Hvis du bruker en versjon av Excel som ikke er 2010, kan du forvente forskjellige skjermer fra de som du ser i denne artikkelen.

En liten historie

I de tidlige dagene av regnearkprogrammer styrte Lotus 1-2-3 roosten. Dens dominans var så komplett at folk trodde det var bortkastet tid for Microsoft å plage å utvikle sin egen regnearkprogramvare (Excel) for å konkurrere med Lotus. Flash-frem til 2010, og Excels dominans av regnearkmarkedet er større enn Lotus's noensinne, mens antall brukere som fortsatt kjører Lotus 1-2-3 nærmer seg null. Hvordan skjedde dette? Hva forårsaket en så dramatisk reversering av formuer?

Industrianalytikere legger det ned til to faktorer: For det første bestemte Lotus seg for at denne fancy nye GUI-plattformen, kalt "Windows", var en forbigående kjepp som aldri ville ta av. De nektet å lage en Windows-versjon av Lotus 1-2-3 (i noen år, uansett), forutsi at deres DOS-versjon av programvaren var alt det noen ville trenge. Microsoft utviklet selvsagt Excel utelukkende for Windows. For det andre utviklet Microsoft en funksjon for Excel som Lotus ikke ga i 1-2-3, nemlig pivottabeller.  PivotTables-funksjonen, eksklusiv til Excel, ble ansett så overveldende nyttig at folk var villige til å lære en helt ny programvarepakke (Excel) i stedet for å holde fast ved et program (1-2-3) som ikke hadde det. Denne funksjonen, sammen med feilverdien av suksessen til Windows, var dødsklokken for Lotus 1-2-3, og starten på suksessen til Microsoft Excel.

Forstå PivotTables

Så hva er en PivotTable, akkurat?

Enkelt sagt, en PivotTable er et sammendrag av noen data, opprettet for å muliggjøre enkel analyse av dataene. Men i motsetning til en manuelt opprettet sammendrag, er Excel PivotTables interaktive. Når du har opprettet en, kan du enkelt endre den hvis den ikke gir nøyaktig innsikt i dataene du håpet på. Med et par klikk kan sammendraget "pivoted" - roteres slik at kolonneoverskriftene blir rad overskrifter, og omvendt. Det er mye mer som kan gjøres også. I stedet for å prøve å beskrive alle funksjonene i PivotTables, demonstrerer vi dem enkelt ...

Dataene du analyserer ved hjelp av en PivotTable, kan ikke være bare noen data - det må være rå data, tidligere ubehandlet (umuliggjort) - vanligvis en liste av noe slag. Et eksempel på dette kan være listen over salgstransaksjoner i et selskap de siste seks månedene.

Undersøk dataene som er vist nedenfor:

Legg merke til at dette er ikke rådata. Faktisk er det allerede en oppsummering av noe slag. I celle B3 kan vi se $ 30.000, som tilsynelatende er summen av James Cooks salg for januar måned. Så hvor er rådataene? Hvordan kom vi til tallet på $ 30.000? Hvor er den opprinnelige listen over salgstransaksjoner som denne figuren ble generert fra? Det er klart at et sted, noen må ha gått i trøbbel med å samle alle salgstransaksjonene de siste seks månedene i sammendraget vi ser ovenfor. Hvor lenge antar du at dette tok? En time? Ti?

Sannsynligvis ja. Du ser, regnearket over er faktisk ikke en pivottabell. Det ble opprettet manuelt fra rå data lagret andre steder, og det tok egentlig et par timer å kompilere. Det er imidlertid akkurat slik sammendrag som kunne bli opprettet ved hjelp av pivottabeller, i så fall ville det ha tatt noen få sekunder. La oss finne ut hvordan ...

Hvis vi skulle spore opp den opprinnelige listen over salgstransaksjoner, kan det se ut slik:

Du kan bli overrasket over å vite at ved hjelp av PivotTable-funksjonen i Excel kan vi opprette et månedlig salgssammendrag som ligner på det ovenfor over noen sekunder, med bare noen få museklikk. Vi kan gjøre dette - og mye mer også!

Slik lager du en pivottabell

Først må du sørge for at du har noen rå data i et regneark i Excel. En liste over finansielle transaksjoner er typisk, men det kan være en liste med omtrent alt: Medarbeider kontaktdetaljer, din CD-samling, eller forbruksstall for din bils flåte.

Så vi starter Excel ... og vi legger inn en slik liste ...

Når vi har listen åpen i Excel, er vi klare til å begynne å lage PivotTable.

Klikk på en enkelt celle i listen:

Deretter fra Sett inn fanen, klikk på pivottabell ikon:

De Opprett pivottabell boksen vises, spør du to spørsmål: Hvilke data skal din nye PivotTable være basert på, og hvor skal den bli opprettet? Fordi vi allerede har klikket på en celle i listen (i trinnet ovenfor), er hele listen rundt den cellen allerede valgt for oss ($ A $ 1: $ G $ 88 på betalinger ark, i dette eksemplet). Legg merke til at vi kunne velge en liste i en hvilken som helst annen region i et annet regneark, eller til og med noen ekstern datakilde, for eksempel en Access-database-tabell, eller til og med en MS-SQL Server-databasetabell. Vi må også velge om vi vil at vår nye PivotTable skal opprettes på en ny regneark, eller på en eksisterende en. I dette eksemplet velger vi en ny en:

Det nye regnearket er opprettet for oss, og en tom pivottabell er opprettet på det regnearket:

En annen boks vises også: PivotTable Field List.  Denne feltlisten vises når vi klikker på en celle i pivottabellen (ovenfor):

Listen over felt i den øverste delen av boksen er faktisk samlingen av kolonneoverskrifter fra det originale rådataarkbladet. De fire boksene i nedre del av skjermen lar oss velge hvordan vi vil at vår PivotTable skal oppsummere de raske dataene. Så langt er det ingenting i disse boksene, så PivotTable er tomt. Alt vi trenger å gjøre er å dra feltene ned fra listen over og slipp dem i de nedre boksene. En pivottabell blir da automatisk opprettet for å matche våre instruksjoner. Hvis vi får feil, trenger vi bare å dra feltene tilbake til hvor de kom fra og / eller dra ny feltene ned for å erstatte dem.

De verdier boks er uten tvil den viktigste av de fire. Feltet som er trukket inn i denne boksen representerer dataene som må oppsummeres på en eller annen måte (ved å summere, gjennomsnitt, finne maksimum, minimum osv.). Det er nesten alltid numerisk data. En perfekt kandidat for denne boksen i våre eksempeldata er feltet "Beløp" / kolonne. La oss trekke det feltet inn i verdier eske:

Legg merke til at (a) feltet "Beløp" i listen over felt nå er krysset, og "Sum of Amount" er lagt til i verdier boks, som indikerer at summeskolonnen er oppsummert.

Hvis vi undersøker PivotTable selv, finner vi faktisk summen av alle "Beløp" -verdiene fra rådataarketet:

Vi har laget vår første PivotTable! Praktisk, men ikke spesielt imponerende. Det er sannsynlig at vi trenger litt mer innsikt i våre data enn det.

Med henvisning til våre utvalgsdata må vi identifisere en eller flere kolonneoverskrifter som vi kunne tenke oss å dele for denne summen. For eksempel kan vi bestemme at vi ønsker å se et sammendrag av våre data der vi har en rad overskrift for hver av de forskjellige selgerne i vårt firma, og totalt for hver. For å oppnå dette er alt vi trenger å gjøre, å dra feltet "Sælger" i Row Etiketter eske:

Nå, Til slutt begynner ting å bli interessant! Vår PivotTable begynner å ta form ... .

Med et par klikk har vi opprettet et bord som ville ha tatt lang tid å gjøre manuelt.

Så hva annet kan vi gjøre? Vel, på en måte er vår PivotTable fullført. Vi har opprettet et nyttig sammendrag av kildedataene våre. De viktige tingene er allerede lært! For resten av artikkelen vil vi undersøke noen måter at mer komplekse pivottabeller kan opprettes, og måter at disse pivottabeller kan tilpasses.

Først kan vi lage en to-dimensjonalt bord. La oss gjøre det ved å bruke "Betalingsmetode" som en kolonneoverskrift. Bare dra "Betalingsmetode" overskriften til Kolonneetiketter eske:

Som ser slik ut:

Begynner å få veldig kul!

La oss gjøre det til en tre-dimensjonalt bord. Hva kan et slikt bord muligens se ut? Vel, la oss se…

Dra kolonnen "Pakke" / overskriften til Rapporter filter eske:

Legg merke til hvor det ender ... .

Dette tillater oss å filtrere vår rapport basert på hvilken "feriepakke" ble kjøpt. For eksempel kan vi se fordelingen av selger vs betalingsmåte for alle pakker, eller, med et par klikk, endre det for å vise samme sammenbrudd for "Sunseekers" -pakken:

Og så, hvis du tenker på det på riktig måte, er vår PivotTable nå tredimensjonal. La oss fortsette å tilpasse ...

Hvis det viser seg, si at vi bare vil se sjekk og kredittkort transaksjoner (det vil si ingen kontanttransaksjoner), så kan vi avmarkere "Cash" -elementet fra kolonneoverskriftene. Klikk på rullegardinmenyen ved siden av Kolonneetiketter, og untick "Cash":

La oss se hvordan det ser ut ... Som du kan se, er "Cash" borte.

formatering

Dette er åpenbart et veldig kraftig system, men så langt ser resultatene seg veldig vanlig og kjedelig ut. For en start ser tallene vi summerer ikke ut som dollarbeløp - bare gamle tall. La oss rette det.

En fristelse kan være å gjøre det vi pleier å gjøre under slike omstendigheter, og velg helt hele tabellen (eller hele regnearket) og bruk standardnummerformateringsknappene på verktøylinjen for å fullføre formateringen. Problemet med denne tilnærmingen er at hvis du noen gang endrer strukturen til PivotTable i fremtiden (som er 99% sannsynlig), vil disse tallformatene gå tapt. Vi trenger en måte som vil gjøre dem (semi-) permanente.

Først finner vi inntastingens "Sum of Amount" i verdier boksen, og klikk på den. En meny vises. Vi velger Innstillinger for verdifelt ... fra menyen:

De Verdifeltinnstillinger boksen vises.

Klikk på Nummerformat knappen og standarden Format Cells-boksen vises:

Fra Kategori liste, velg (si) regnskap, og slipp antall desimaler til 0. Klikk OK noen ganger for å komme tilbake til pivottabellen ...

Som du ser, har tallene blitt riktig formatert som dollarbeløp.

Mens vi er underlagt formatering, la oss formatere hele pivottabellen. Det er noen måter å gjøre dette på. La oss bruke en enkel en ...

Klikk på PivotTable Verktøy / Design tab:

Deretter faller du ned pilen nederst til høyre på PivotTable-stiler liste for å se en enorm samling av innebygde stiler:

Velg en som appellerer, og se på resultatet i PivotTable:

Andre muligheter

Vi kan også jobbe med datoer. Nå er det vanligvis mange, mange datoer i en transaksjonsliste som den vi startet med. Men Excel gir muligheten til å gruppere dataobjekter sammen etter dag, uke, måned, år osv. La oss se hvordan dette er gjort.

La oss først fjerne kolonnen "Betalingsmetode" fra Kolonneetiketter boks (bare dra den tilbake til feltlisten), og erstatt den med "Date Booked" -kolonnen:

Som du kan se, gjør dette vår PivotTable umiddelbart ubrukelig, og gir oss en kolonne for hver dato som en transaksjon skjedde på - et veldig bredt bord!

For å fikse dette, høyreklikk på en dato og velg Gruppe… fra kontekstmenyen:

Gruppeboksen vises. Vi velger Måneder og klikk OK:

Voila! EN mye mer nyttig bord:

(For øvrig er dette bordet nesten identisk med det som vises i begynnelsen av denne artikkelen - det opprinnelige salgssammendraget som ble opprettet manuelt.)

En annen kul ting å være klar over er at du kan ha mer enn ett sett med radoverskrifter (eller kolonneoverskrifter):

... som ser slik ut ... .

Du kan gjøre en lignende ting med kolonneoverskrifter (eller til og med rapportfiltre).

Å holde ting enkle igjen, la oss se hvordan du kan plotte gjennomsnitt verdier, i stedet for summerte verdier.

Først klikker du på "Summen av beløp", og velg Innstillinger for verdifelt ... fra kontekstmenyen som vises:

I Oppsummer verdifeltet ved liste i Verdifeltinnstillinger boks, velg Gjennomsnitt:

Mens vi er her, la oss endre Egendefinert navn, fra "Gjennomsnitt av beløp" til noe litt mer konsistent. Skriv inn noe som "Avg":

Klikk OK, og se hvordan det ser ut. Legg merke til at alle verdiene endres fra summerte totals til gjennomsnitt, og tabelltittel (øverste venstre celle) er endret til "Avg":

Hvis vi liker, kan vi til og med ha summer, gjennomsnitt og teller (teller = hvor mange salg det var) alle på samme pivottabel!

Her er trinnene for å få noe sånt på plass (starter fra en tom PivotTable):

1. Dra "Salesperson" inn i Kolonneetiketter
2. Dra "Antall" -feltet ned i verdier boksen tre ganger
3. For det første "Beløp" -feltet, endre dets egendefinerte navn til "Totalt", og det er tallformat til regnskap (0 desimaler)
4. For det andre "Beløp" -feltet, endre dets egendefinerte navn til "Gjennomsnitt", dets funksjon til Gjennomsnitt og det er nummerformat til regnskap (0 desimaler)
5. For det tredje "Beløp" -feltet, endre navn til "Count" og dets funksjon til Telle
6. Dra den automatisk opprettede felt fra Kolonneetiketter til Row Etiketter

Her er hva vi ender med:

Totalt, gjennomsnittlig og telle på samme PivotTable!

Konklusjon

Det er mange, mange flere funksjoner og alternativer for PivotTables laget av Microsoft Excel - alt for mange til å liste i en artikkel som dette. For å fullt ut dekke potensialet til pivottabeller, ville det være nødvendig med en liten bok (eller et stort nettsted). Modige og / eller geeky lesere kan utforske PivotTables ytterligere ganske enkelt: Bare høyreklikk på omtrent alt, og se hvilke alternativer som blir tilgjengelige for deg. Det er også de to bånd-fanene: PivotTable Tools / Options og Design.  Det spiller ingen rolle om du gjør en feil - det er enkelt å slette pivottabellen og starte igjen - en mulighet for at gamle DOS-brukere av Lotus 1-2-3 aldri hadde hatt.

Hvis du jobber i Office 2007, vil du kanskje sjekke ut vår artikkel om hvordan du oppretter en pivottabell i Excel 2007.

Vi har tatt med en Excel-arbeidsbok som du kan laste ned for å øve dine PivotTable-ferdigheter på. Den skal fungere med alle versjoner av Excel fra 97 og fremover.

Last ned vår praksis Excel-arbeidsbok