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 Excel dominans av regnearkmarkedet er større enn Lotus's noensinne var, 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 satte det på 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 leverte 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) enn å holde fast ved et program (1-2-3) som ikke hadde det. Denne funksjonen, sammen med feilmeldingen av suksessen til Windows, var dødsklokken for Lotus 1-2-3, og starten på suksessen til Microsoft Excel.
Forstå pivottabeller
Så hva er en PivotTable, akkurat?
Enkelt sagt, en PivotTable er et sammendrag av noen data, opprettet for å tillate 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 "pivotert" - 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 over. 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 faktisk 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 slik ut:
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 brensel forbruk tall for din bils flåte.
Så vi starter Excel … og vi legger inn en slik liste …
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:
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 opprinnelige rådataarkbladet. De fire tomme 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 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:
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 "Sælger" -feltet inn i Row Etiketter eske:
NåTil slutt begynner ting å bli interessant! Vår PivotTable begynner å ta form ….
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 kompliserte 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:
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:
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:
Hvis det viser seg, si at vi bare vil se sjekk og kredittkort transaksjoner (det vil si ingen kontanttransaksjoner), så kan vi fjerne merket "Kontanter" fra kolonneoverskriftene. Klikk på rullegardinpilen ved siden av Kolonneetiketter, og untick "Cash":
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 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 boks, og klikk på den. En meny vises. Vi velger Verdifeltinnstillinger … 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 …
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:
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 etc. La oss se hvordan dette er gjort.
Først, la oss fjerne kolonnen "Betalingsmetode" fra Kolonneetiketter boks (bare dra den tilbake til feltlisten), og erstatt den med "Date Booked" -kolonnen:
For å fikse dette, høyreklikk på hvilken som helst dato og velg Gruppe… fra kontekstmenyen:
Gruppeboksen vises. Vi velger Måneder og klikk OK:
Voila! EN mye mer nyttig bord:
En annen kul ting å være oppmerksom på er at du kan ha mer enn ett sett med radoverskrifter (eller kolonneoverskrifter):
Å holde ting enkle igjen, la oss se hvordan du kan plotte gjennomsnitt verdier, i stedet for summerte verdier.
Først klikker du på "Sum of Amount", og velg Verdifeltinnstillinger … fra kontekstmenyen som vises:
I Oppsummer verdifeltet av liste i Verdifeltinnstillinger boks, velg Gjennomsnitt:
Mens vi er her, la oss endre Egendefinert navn, fra "Gjennomsnittlig 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":
Her er trinnene for å få noe sånt på plass (fra en tom PivotTable):
- Dra "Salesperson" inn i Kolonneetiketter
- Dra "Felt" -feltet ned i verdier boksen tre ganger
- For det første "Beløp" -feltet, endre dets egendefinerte navn til "Totalt" og det er tallformat til regnskap (0 desimaler)
- For det andre "Beløp" -feltet, endre dets egendefinerte navn til "Gjennomsnitt", dets funksjon til Gjennomsnitt og det er tallformat til regnskap (0 desimaler)
- For det tredje "Beløp" -feltet, endre navn til "Count" og dets funksjon til Telle
-
Dra den automatisk opprettede
Image felt fra Kolonneetiketter til Row Etiketter
Her er hva vi ender med:
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 PivotTable og starte igjen - en mulighet for gamle DOS-brukere av Lotus 1-2-3 aldri 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
