Innhold
Pivottabeller er et av de kraftigste verktøyene i Excel. De lar deg analysere og oppsummere ulike sammendrag av store datamengder med bare noen få museklikk. I denne artikkelen vil vi bli kjent med pivottabeller, forstå hva de er, lære å lage og tilpasse dem.
Denne artikkelen ble skrevet med Excel 2010. Konseptet med pivottabeller har ikke endret seg mye gjennom årene, men måten du lager dem på er litt forskjellig i hver nye versjon av Excel. Hvis du har en versjon av Excel som ikke er 2010, så vær forberedt på at skjermbildene i denne artikkelen vil avvike fra det du ser på skjermen.
Litt historie
I de tidlige dagene av regnearkprogramvaren regjerte Lotus 1-2-3 ball. Dens dominans var så fullstendig at Microsofts innsats for å utvikle sin egen programvare (Excel) som et alternativ til Lotus virket som bortkastet tid. Spol nå frem til 2010! Excel dominerer regneark mer enn Lotus-kode noen gang har gjort i sin historie, og antallet personer som fortsatt bruker Lotus er nær null. Hvordan kunne dette skje? Hva var årsaken til en så dramatisk vending?
Analytikere identifiserer to hovedfaktorer:
- Først bestemte Lotus at denne nymotens GUI-plattformen kalt Windows bare var en forbigående kjepphest som ikke ville vare lenge. De nektet å bygge en Windows-versjon av Lotus 1-2-3 (men bare i noen få år), og spådde at DOS-versjonen av programvaren deres ville være alt forbrukere noensinne ville trenge. Microsoft utviklet naturligvis Excel spesielt for Windows.
- For det andre introduserte Microsoft et verktøy i Excel kalt PivotTables som ikke var tilgjengelig i Lotus 1-2-3. Pivottabeller, eksklusive for Excel, viste seg å være så overveldende nyttige at folk hadde en tendens til å holde seg til den nye Excel-programvarepakken i stedet for å fortsette med Lotus 1-2-3, som ikke hadde dem.
Pivottabeller, sammen med å undervurdere suksessen til Windows generelt, spilte dødsmarsjen for Lotus 1-2-3 og innledet suksessen til Microsoft Excel.
Hva er pivottabeller?
Så, hva er den beste måten å karakterisere hva pivottabeller er?
Enkelt sagt er pivottabeller sammendrag av noen data, laget for å lette analysen av disse dataene. I motsetning til manuelt opprettede totaler, er Excel-pivottabeller interaktive. Når de er opprettet, kan du enkelt endre dem hvis de ikke gir bildet du håpet på. Med bare et par museklikk kan totaler snus slik at kolonneoverskrifter blir radoverskrifter og omvendt. Du kan gjøre mange ting med pivottabeller. I stedet for å prøve å beskrive med ord alle funksjonene til pivottabeller, er det lettere å demonstrere det i praksis ...
Dataene du analyserer med pivottabeller kan ikke være tilfeldige. Det bør være rådata, som en liste av noe slag. Det kan for eksempel være en liste over salg foretatt av selskapet de siste seks månedene.
Se på dataene vist i figuren nedenfor:
Merk at dette ikke er rådata, da det allerede er oppsummert. I celle B3 ser vi $30000, som sannsynligvis er det totale resultatet som James Cook gjorde i januar. Hvor er de opprinnelige dataene da? Hvor kom $30000 XNUMX-tallet fra? Hvor er den opprinnelige listen over salg som denne månedlige summen ble hentet fra? Det er tydelig at noen har gjort en kjempejobb med å organisere og sortere alle salgsdata for de siste seks månedene og gjøre det om til tabellen over totaler som vi ser. Hvor lang tid tror du det tok? Time? Klokken ti?
Faktum er at tabellen ovenfor ikke er en pivottabell. Den ble håndlaget fra rådata lagret andre steder og tok minst et par timer å behandle. Akkurat en slik oppsummeringstabell kan lages ved hjelp av pivottabeller på bare noen få sekunder. La oss finne ut hvordan...
Hvis vi går tilbake til den opprinnelige salgslisten, vil den se omtrent slik ut:
Du kan bli overrasket over at fra denne listen over handler ved hjelp av pivottabeller og på bare noen få sekunder, kan vi lage en månedlig salgsrapport i Excel, som vi analyserte ovenfor. Ja, vi kan gjøre det og mer!
Hvordan lage en pivottabell?
Først må du sørge for at du har noen kildedata i et Excel-ark. Listen over finansielle transaksjoner er den mest typiske som forekommer. Faktisk kan det være en liste over hva som helst: kontaktinformasjon for ansatte, en CD-samling eller bedriftens drivstofforbruksdata.
Så vi starter Excel ... og laster en slik liste ...
Etter at vi har åpnet denne listen i Excel, kan vi begynne å lage en pivottabell.
Velg en celle fra denne listen:
Så på fanen Innsetting (Sett inn) velg kommando Pivottabell (Pivottabell):
En dialogboks vises Lag pivottabell (Opprette en pivottabell) med to spørsmål til deg:
- Hvilke data skal du bruke for å lage en ny pivottabell?
- Hvor skal du plassere pivottabellen?
Siden vi i forrige trinn allerede har valgt en av listecellene, vil hele listen automatisk bli valgt for å lage en pivottabell. Merk at vi kan velge et annet område, en annen tabell, og til og med en ekstern datakilde som en Access- eller MS-SQL-databasetabell. I tillegg må vi velge hvor vi skal plassere det nye pivotbordet: på et nytt ark eller på et av de eksisterende. I dette eksemplet vil vi velge alternativet – Nytt arbeidsark (til et nytt ark):
Excel vil opprette et nytt ark og plassere en tom pivottabell på det:
Så snart vi klikker på en celle i pivottabellen, vises en annen dialogboks: Pivottabell-feltliste (Pivottabellfelt).
Listen over felt øverst i dialogboksen er en liste over alle titlene fra den opprinnelige listen. De fire tomme områdene nederst på skjermen lar deg fortelle pivottabellen hvordan du vil oppsummere dataene. Så lenge disse områdene er tomme, er det heller ingenting i tabellen. Alt vi trenger å gjøre er å dra overskriftene fra det øverste området til de tomme områdene nedenfor. Samtidig genereres det automatisk en pivottabell, i henhold til våre instruksjoner. Hvis vi gjør en feil, kan vi fjerne overskriftene fra bunnområdet eller dra andre for å erstatte dem.
Område Verdier (Betydninger) er sannsynligvis den viktigste av de fire. Hvilken overskrift som plasseres i dette området avgjør hvilke data som skal oppsummeres (sum, gjennomsnitt, maksimum, minimum osv.) Dette er nesten alltid numeriske verdier. En utmerket kandidat for en plass i dette området er dataene under overskriften Beløp (Kostnad) for vårt originale bord. Dra denne tittelen til området Verdier (Verdier):
Vær oppmerksom på at tittelen Beløp er nå merket med hake, og i området Verdier (Verdier) en oppføring har dukket opp Sum av Beløp (Beløpsfeltet Beløp), som indikerer at kolonnen Beløp oppsummerte.
Hvis vi ser på selve pivottabellen, vil vi se summen av alle verdiene fra kolonnen Beløp originalt bord.
Så vårt første pivotbord er laget! Praktisk, men ikke spesielt imponerende. Vi ønsker sannsynligvis å få mer informasjon om dataene våre enn vi har i dag.
La oss gå til de opprinnelige dataene og prøve å identifisere en eller flere kolonner som kan brukes til å dele denne summen. For eksempel kan vi danne pivottabellen vår på en slik måte at det totale salgsbeløpet beregnes for hver selger individuelt. De. rader vil bli lagt til vår pivottabell med navnet på hver selger i selskapet og deres totale salgsbeløp. For å oppnå dette resultatet, bare dra tittelen selger (salgsrepresentant) til regionen Radetiketter (Strenger):
Det blir mer interessant! Pivottabellen vår begynner å ta form...
Ser du fordelene? I løpet av et par klikk laget vi en tabell som ville tatt veldig lang tid å lage manuelt.
Hva annet kan vi gjøre? Vel, på en måte er pivotbordet vårt klart. Vi har laget et nyttig sammendrag av de opprinnelige dataene. Viktig informasjon allerede mottatt! I resten av denne artikkelen skal vi se på noen måter å lage mer komplekse pivottabeller på, samt lære hvordan du tilpasser dem.
Pivottabelloppsett
Først kan vi lage en todimensjonal pivottabell. La oss gjøre dette ved å bruke kolonneoverskriften Betalingsmetode (Betalingsmetode). Bare dra tittelen Betalingsmetode til området Kolonneetiketter (Kolonner):
Vi får resultatet:
Ser veldig kult ut!
La oss nå lage en tredimensjonal tabell. Hvordan ville et slikt bord se ut? La oss se…
Dra overskrift Pakke (Kompleks) til området rapportfiltre (Filtre):
Legg merke til hvor han er...
Dette gir oss muligheten til å filtrere rapporten på grunnlag av "Hvilket feriekompleks ble betalt for." For eksempel kan vi se en oversikt etter selgere og etter betalingsmåter for alle komplekser, eller i et par museklikk endre visningen av pivottabellen og vise den samme inndelingen kun for de som har bestilt komplekset Solsøkere.
Så hvis du forstår dette riktig, kan pivottabellen vår kalles tredimensjonal. La oss fortsette å sette opp...
Hvis det plutselig viser seg at kun betaling med sjekk og kredittkort (det vil si kontantløs betaling) skal vises i pivottabellen, kan vi slå av visningen av tittelen Kontanter (Penger). For dette, ved siden av Kolonneetiketter klikk på pil ned og fjern merket i boksen i rullegardinmenyen Kontanter:
La oss se hvordan pivottabellen vår ser ut nå. Som du kan se, kolonnen Kontanter forsvant fra henne.
Formatering av pivottabeller i Excel
Pivottabeller er åpenbart et veldig kraftig verktøy, men så langt ser resultatene litt enkle og kjedelige ut. For eksempel, tallene vi legger sammen ser ikke ut som dollarbeløp – de er bare tall. La oss fikse dette.
Det er fristende å gjøre det du er vant til i en slik situasjon og ganske enkelt velge hele tabellen (eller hele arket) og bruke standard tallformateringsknapper på verktøylinjen for å stille inn ønsket format. Problemet med denne tilnærmingen er at hvis du noen gang endrer strukturen til pivottabellen i fremtiden (noe som skjer med 99 % sjanse), vil formateringen gå tapt. Det vi trenger er en måte å gjøre det (nesten) permanent på.
Først, la oss finne oppføringen Sum av Beløp in Verdier (Verdier) og klikk på den. Velg elementet i menyen som vises Verdifeltinnstillinger (Alternativer for verdifelt):
En dialogboks vises Verdifeltinnstillinger (Verdifeltalternativer).
Klikk på knappen Tallformat (Tallformat), en dialogboks åpnes. Formater celler (celleformat):
Fra listen Kategori (Tallformater) velg Regnskap og administrasjon (Økonomi) og sett antall desimaler til null. Trykk nå noen ganger OKfor å gå tilbake til pivottabellen vår.
Som du kan se, er tallene formatert som dollarbeløp.
Mens vi er i gang med formatering, la oss sette opp formatet for hele pivottabellen. Det er flere måter å gjøre dette på. Vi bruker den som er enklere...
Klikk på Pivottabellverktøy: Design (Jobber med pivottabeller: Konstruktør):
Deretter utvider du menyen ved å klikke på pilen i nedre høyre hjørne av delen Pivottabellstiler (PivotTable Styles) for å se den omfattende samlingen av innebygde stiler:
Velg en passende stil og se på resultatet i pivottabellen din:
Andre pivottabellinnstillinger i Excel
Noen ganger må du filtrere data etter datoer. For eksempel, i vår liste over handler er det mange, mange datoer. Excel gir et verktøy for å gruppere data etter dag, måned, år og så videre. La oss se hvordan det gjøres.
Fjern først oppføringen. Betalingsmetode fra regionen Kolonneetiketter (Kolonner). For å gjøre dette, dra den tilbake til listen over titler, og flytt tittelen i stedet for Dato bestilt (bestillingsdato):
Som du kan se, gjorde dette pivottabellen vår midlertidig ubrukelig. Excel opprettet en egen kolonne for hver dato en handel ble gjort. Som et resultat fikk vi et veldig bredt bord!
For å fikse dette, høyreklikk på en hvilken som helst dato og velg fra hurtigmenyen GROUP (Gruppe):
Grupperingsdialogboksen vises. Vi velger Måneder (Måneder) og klikk OK:
Voila! Denne tabellen er mye mer nyttig:
Denne tabellen er forresten nesten identisk med den som vises i begynnelsen av artikkelen, hvor salgssummene ble satt sammen manuelt.
Det er et annet veldig viktig poeng du trenger å vite! Du kan ikke lage én, men flere nivåer av rad- (eller kolonne)overskrifter:
... og det vil se slik ut ...
Det samme kan gjøres med kolonneoverskrifter (eller til og med filtre).
La oss gå tilbake til den opprinnelige formen av tabellen og se hvordan du viser gjennomsnitt i stedet for summer.
For å komme i gang, klikk på Sum av Beløp og velg fra menyen som vises Verdifeltinnstillinger (Alternativer for verdifelt):
Listen Oppsummer verdifeltet med (Operasjon) i dialogboksen Verdifeltinnstillinger (Verdifeltalternativer) velg Gjennomsnitt (Gjennomsnitt):
Samtidig, mens vi er her, la oss forandre oss Egendefinert navn (Egendefinert navn) med Gjennomsnitt av beløp (Beløpsfeltet Mengde) til noe kortere. Skriv inn noe sånt i dette feltet Gjennomsnittlig:
Press OK og se hva som skjer. Merk at alle verdier har endret seg fra totaler til gjennomsnitt, og tabelloverskriften (i cellen øverst til venstre) er endret til Gjennomsnittlig:
Hvis du vil, kan du umiddelbart få beløp, gjennomsnitt og antall (salg) plassert i én pivottabell.
Her er en trinnvis veiledning for hvordan du gjør dette, og starter med en tom pivottabell:
- Dra overskrift selger (salgsrepresentant) til regionen Kolonneetiketter (Kolonner).
- Dra tittelen tre ganger Beløp (Kostnad) til området Verdier (Verdier).
- For det første feltet Beløp endre tittelen til Totalt (Beløp), og tallformatet i dette feltet er Regnskap og administrasjon (Finansiell). Antall desimaler er null.
- Andre felt Beløp navn Gjennomsnittlige, angi operasjonen for det Gjennomsnitt (Gjennomsnitt) og tallformatet i dette feltet endres også til Regnskap og administrasjon (økonomisk) med null desimaler.
- For det tredje feltet Beløp angi en tittel Telle og en operasjon for ham - Telle (Mengde)
- på Kolonneetiketter (Kolonner)-feltet opprettet automatisk Σ Verdier (Σ Verdier) – dra den til området Radetiketter (Linjer)
Her er hva vi ender opp med:
Totalbeløp, gjennomsnittsverdi og antall salg – alt i én pivottabell!
konklusjonen
Pivottabeller i Microsoft Excel inneholder mange funksjoner og innstillinger. I en så liten artikkel er de ikke engang i nærheten av å dekke dem alle. Det ville kreve en liten bok eller et stort nettsted for å beskrive alle mulighetene til pivottabeller fullt ut. Fet og nysgjerrige lesere kan fortsette sin utforskning av pivottabeller. For å gjøre dette, høyreklikk på nesten hvilket som helst element i pivottabellen og se hvilke funksjoner og innstillinger som åpnes. På båndet finner du to faner: Pivottabellverktøy: Alternativer (analyse) og utforming (Konstruktør). Ikke vær redd for å gjøre en feil, du kan alltid slette pivottabellen og begynne på nytt. Du har en mulighet som langtidsbrukere av DOS og Lotus 1-2-3 aldri har hatt.