Indhold
Drejeborde er et af de mest kraftfulde værktøjer i Excel. De giver dig mulighed for at analysere og opsummere forskellige opsummeringer af store mængder data med blot et par museklik. I denne artikel vil vi stifte bekendtskab med pivottabeller, forstå, hvad de er, lære at oprette og tilpasse dem.
Denne artikel er skrevet ved hjælp af Excel 2010. Konceptet med pivottabeller har ikke ændret sig meget gennem årene, men måden du opretter dem på er lidt anderledes i hver ny version af Excel. Hvis du har en version af Excel end 2010, så vær forberedt på, at skærmbillederne i denne artikel vil afvige fra det, du ser på din skærm.
Lidt historie
I de tidlige dage af regnearkssoftware regerede Lotus 1-2-3-bolden. Dens dominans var så fuldstændig, at Microsofts bestræbelser på at udvikle sin egen software (Excel) som et alternativ til Lotus virkede som spild af tid. Spol nu frem til 2010! Excel dominerer regneark mere end Lotus-kode nogensinde har gjort i sin historie, og antallet af personer, der stadig bruger Lotus, er tæt på nul. Hvordan kunne dette ske? Hvad var årsagen til en så dramatisk vending?
Analytikere identificerer to hovedfaktorer:
- Først besluttede Lotus, at denne nymodens GUI-platform kaldet Windows blot var en forbigående mode, der ikke ville vare længe. De nægtede at bygge en Windows-version af Lotus 1-2-3 (men kun i et par år), idet de forudsagde, at DOS-versionen af deres software ville være alt, hvad forbrugerne nogensinde ville have brug for. Microsoft udviklede naturligvis Excel specifikt til Windows.
- For det andet introducerede Microsoft et værktøj i Excel kaldet PivotTables, som ikke var tilgængeligt i Lotus 1-2-3. Pivottabeller, eksklusive til Excel, viste sig at være så overvældende nyttige, at folk havde en tendens til at holde sig til den nye Excel-softwarepakke i stedet for at fortsætte med Lotus 1-2-3, som ikke havde dem.
Pivottabeller, sammen med at undervurdere succesen med Windows generelt, spillede dødsmarchen for Lotus 1-2-3 og indvarslede succesen med Microsoft Excel.
Hvad er pivottabeller?
Så hvad er den bedste måde at karakterisere, hvad pivottabeller er?
Enkelt sagt er pivottabeller opsummeringer af nogle data, skabt for at lette analysen af disse data. I modsætning til manuelt oprettede totaler er Excel-pivottabeller interaktive. Når de er oprettet, kan du nemt ændre dem, hvis de ikke giver det billede, du havde håbet på. Med blot et par museklik kan totaler vendes, så kolonneoverskrifter bliver til rækkeoverskrifter og omvendt. Du kan gøre mange ting med pivottabeller. I stedet for at prøve at beskrive alle funktionerne i pivottabeller med ord, er det lettere at demonstrere det i praksis ...
De data, du analyserer med pivottabeller, kan ikke være tilfældige. Det skal være rå data, som en liste af en slags. Det kan for eksempel være en liste over salg foretaget af virksomheden inden for de seneste seks måneder.
Se på dataene vist i figuren nedenfor:
Bemærk, at dette ikke er rådata, da det allerede er opsummeret. I celle B3 ser vi $30000, hvilket formentlig er det samlede resultat, som James Cook gjorde i januar. Hvor er de originale data så? Hvor kom tallet 30000 $ fra? Hvor er den oprindelige liste over salg, hvorfra denne månedlige total blev afledt? Det er tydeligt, at nogen har gjort et godt stykke arbejde med at organisere og sortere alle salgsdata for de sidste seks måneder og omsætte det til den tabel over totaler, vi ser. Hvor lang tid tror du, det tog? Time? Klokken ti?
Faktum er, at tabellen ovenfor ikke er en pivottabel. Det blev håndlavet ud fra rådata lagret andetsteds og tog mindst et par timer at behandle. Netop sådan en oversigtstabel kan oprettes ved hjælp af pivottabeller på få sekunder. Lad os finde ud af hvordan...
Hvis vi går tilbage til den oprindelige salgsliste, ville den se sådan ud:
Du kan blive overrasket over, at vi fra denne liste over handler ved hjælp af pivottabeller og på få sekunder kan oprette en månedlig salgsrapport i Excel, som vi analyserede ovenfor. Ja, det og mere kan vi!
Hvordan opretter man en pivottabel?
Først skal du sørge for, at du har nogle kildedata i et Excel-ark. Listen over finansielle transaktioner er den mest typiske, der forekommer. Faktisk kan det være en liste over hvad som helst: medarbejderkontaktoplysninger, en cd-samling eller din virksomheds brændstofforbrugsdata.
Så vi starter Excel ... og indlæser sådan en liste ...
Efter at vi har åbnet denne liste i Excel, kan vi begynde at oprette en pivottabel.
Vælg en hvilken som helst celle fra denne liste:
Så på fanen Indsættelse (Indsæt) vælg kommando Pivottabel (Pivot tabel):
En dialogboks vises Opret pivottabel (Oprettelse af en pivottabel) med to spørgsmål til dig:
- Hvilke data skal bruges til at oprette en ny pivottabel?
- Hvor skal man placere pivotbordet?
Da vi i det foregående trin allerede har valgt en af listecellerne, vil hele listen automatisk blive valgt for at oprette en pivottabel. Bemærk, at vi kan vælge et andet område, en anden tabel og endda en ekstern datakilde, såsom en Access- eller MS-SQL-databasetabel. Derudover skal vi vælge, hvor vi skal placere det nye pivotbord: på et nyt ark eller på et af de eksisterende. I dette eksempel vil vi vælge muligheden – Nyt regneark (til et nyt ark):
Excel opretter et nyt ark og placerer en tom pivottabel på det:
Så snart vi klikker på en celle i pivottabellen, vises en anden dialogboks: Pivottabel-feltliste (Pivottabelfelter).
Listen over felter øverst i dialogboksen er en liste over alle titlerne fra den originale liste. De fire tomme områder nederst på skærmen giver dig mulighed for at fortælle pivottabellen, hvordan du vil opsummere dataene. Så længe disse områder er tomme, er der heller ikke noget i tabellen. Alt vi skal gøre er at trække overskrifterne fra det øverste område til de tomme områder nedenfor. Samtidig genereres der automatisk en pivottabel i overensstemmelse med vores instruktioner. Hvis vi laver en fejl, kan vi fjerne overskrifterne fra det nederste område eller trække andre for at erstatte dem.
Miljø Værdier (Betydninger) er nok den vigtigste af de fire. Hvilken overskrift der placeres i dette område afgør hvilke data der vil blive opsummeret (sum, gennemsnit, maksimum, minimum osv.) Dette er næsten altid numeriske værdier. En fremragende kandidat til en plads i dette område er dataene under overskriften beløb (omkostninger) af vores originale bord. Træk denne titel til området Værdier (Værdier):
Bemærk venligst, at titlen beløb er nu markeret med et flueben, og i området Værdier (Værdier) en post er dukket op Sum af beløb (Beløbsfelt Beløb), hvilket angiver, at kolonnen beløb opsummeret.
Hvis vi ser på selve pivottabellen, vil vi se summen af alle værdier fra kolonnen beløb originalt bord.
Så vores første pivottabel er skabt! Praktisk, men ikke særlig imponerende. Vi ønsker nok at få mere information om vores data, end vi har i øjeblikket.
Lad os vende os til de originale data og prøve at identificere en eller flere kolonner, der kan bruges til at opdele denne sum. For eksempel kan vi danne vores pivottabel på en sådan måde, at den samlede salgsmængde beregnes for hver sælger individuelt. De der. rækker vil blive tilføjet til vores pivottabel med navnet på hver sælger i virksomheden og deres samlede salgsbeløb. For at opnå dette resultat skal du blot trække i titlen Sælger (sælger) til regionen Rækkeetiketter (strenge):
Det bliver mere interessant! Vores pivottabel begynder at tage form...
Ser du fordelene? Med et par klik oprettede vi en tabel, som ville have taget meget lang tid at oprette manuelt.
Hvad kan vi ellers gøre? På en måde er vores pivottabel klar. Vi har lavet en nyttig oversigt over de originale data. Vigtig information allerede modtaget! I resten af denne artikel vil vi se på nogle måder at skabe mere komplekse pivottabeller på, samt lære at tilpasse dem.
Opsætning af pivottabel
Først kan vi oprette en todimensionel pivottabel. Lad os gøre dette ved at bruge kolonneoverskriften Betalingsmetode (Betalingsmetode). Træk bare titlen Betalingsmetode til området Kolonneetiketter (Kolonner):
Vi får resultatet:
Ser meget sejt ud!
Lad os nu lave en tredimensionel tabel. Hvordan ville sådan et bord se ud? Lad os se…
Træk overskrift Pakke (Kompleks) til området rapportfiltre (Filtre):
Bemærk hvor han er...
Dette giver os mulighed for at filtrere rapporten ud fra "Hvilket feriekompleks er der betalt for." For eksempel kan vi se en opdeling efter sælgere og efter betalingsmetoder for alle komplekser, eller med et par museklik ændre visningen af pivottabellen og kun vise den samme opdeling for dem, der har bestilt komplekset Solsøgende.
Så hvis du forstår dette rigtigt, så kan vores pivottabel kaldes tredimensionel. Lad os fortsætte opsætningen...
Hvis det pludselig viser sig, at kun betaling med check og kreditkort (det vil sige kontantløs betaling) skal vises i pivottabellen, så kan vi slå visningen af titlen fra Kontanter (Kontanter). Til dette ved siden af Kolonneetiketter klik på pil ned, og fjern markeringen i feltet i rullemenuen Kontanter:
Lad os se, hvordan vores pivottabel ser ud nu. Som du kan se, kolonnen Kontanter forsvandt fra hende.
Formatering af pivottabeller i Excel
Pivottabeller er naturligvis et meget kraftfuldt værktøj, men indtil videre ser resultaterne lidt almindelige og kedelige ud. For eksempel ligner de tal, vi lægger op, ikke ud som dollarbeløb – de er bare tal. Lad os ordne dette.
Det er fristende at gøre, hvad du er vant til i sådan en situation og blot vælge hele tabellen (eller hele arket) og bruge standard talformateringsknapperne på værktøjslinjen til at indstille det ønskede format. Problemet med denne tilgang er, at hvis du nogensinde ændrer strukturen af pivottabellen i fremtiden (hvilket sker med en chance på 99 %), vil formateringen gå tabt. Det, vi har brug for, er en måde at gøre det (næsten) permanent på.
Lad os først finde posten Sum af beløb in Værdier (Værdier) og klik på den. Vælg elementet i menuen, der vises Værdi feltindstillinger (Værdifeltmuligheder):
En dialogboks vises Værdi feltindstillinger (Værdifeltmuligheder).
Klik på knappen talformat (Talformat), åbnes en dialogboks. formater celler (celleformat):
Fra listen Boligtype (Talformater) vælg Bogføring og administration (Økonomisk) og sæt antallet af decimaler til nul. Tryk nu et par gange OKfor at gå tilbage til vores pivottabel.
Som du kan se, er tallene formateret som dollarbeløb.
Mens vi er i gang med formatering, lad os konfigurere formatet for hele pivottabellen. Der er flere måder at gøre dette på. Vi bruger den, der er enklere...
Klik på knappen Pivottabelværktøjer: Design (Arbejde med pivottabeller: Konstruktør):
Udvid derefter menuen ved at klikke på pilen i nederste højre hjørne af sektionen Pivottabelstilarter (PivotTable Styles) for at se den omfattende samling af inline-stile:
Vælg en passende stil og se på resultatet i din pivottabel:
Andre pivottabelindstillinger i Excel
Nogle gange har du brug for at filtrere data efter datoer. For eksempel er der mange, mange datoer på vores liste over handler. Excel giver et værktøj til at gruppere data efter dag, måned, år og så videre. Lad os se, hvordan det gøres.
Fjern først posten. Betalingsmetode fra regionen Kolonneetiketter (Kolonner). For at gøre dette skal du trække den tilbage til listen over titler og flytte titlen i stedet for Dato reserveret (bestillingsdato):
Som du kan se, gjorde dette midlertidigt vores pivottabel ubrugelig. Excel oprettede en separat kolonne for hver dato, hvor en handel blev foretaget. Som et resultat fik vi et meget bredt bord!
For at rette dette skal du højreklikke på en dato og vælge fra kontekstmenuen GROUP (Gruppe):
Grupperingsdialogboksen vises. Vi vælger Måneder (Måneder) og klik OK:
Voila! Denne tabel er meget mere nyttig:
Denne tabel er i øvrigt næsten identisk med den, der er vist i begyndelsen af artiklen, hvor salgstotalerne blev opgjort manuelt.
Der er et andet meget vigtigt punkt, som du skal vide! Du kan ikke oprette én, men flere niveauer af række- (eller kolonne)overskrifter:
… og det vil se sådan ud …
Det samme kan gøres med kolonneoverskrifter (eller endda filtre).
Lad os vende tilbage til den oprindelige form af tabellen og se, hvordan man viser gennemsnit i stedet for summer.
Klik på for at komme i gang Sum af beløb og vælg fra den menu, der vises Værdi feltindstillinger (Værdifeltmuligheder):
Listen Opsummer værdifeltet med (Betjening) i dialogboksen Værdi feltindstillinger (Værdifeltindstillinger) vælg Gennemsnit (Gennemsnit):
På samme tid, mens vi er her, lad os ændre os CustomName (Brugerdefineret navn) med Gennemsnit af Beløb (Beløbsfelt Beløb) til noget kortere. Indtast noget lignende i dette felt Gennemsnitlig:
Presse OK og se hvad der sker. Bemærk, at alle værdier er ændret fra totaler til gennemsnit, og tabeloverskriften (i den øverste venstre celle) er ændret til Gennemsnitlig:
Hvis du vil, kan du med det samme få beløb, gennemsnit og antal (salg) placeret i én pivottabel.
Her er en trin for trin guide til, hvordan du gør dette, startende med en tom pivottabel:
- Træk overskrift Sælger (sælger) til regionen Kolonneetiketter (Kolonner).
- Træk titlen tre gange beløb (omkostninger) til området Værdier (Værdier).
- Til det første felt beløb ændre titlen til I alt (Beløb), og talformatet i dette felt er Bogføring og administration (Finansiel). Antallet af decimaler er nul.
- Andet felt beløb navn Gennemsnitlige, indstil operationen for det Gennemsnit (Gennemsnit) og talformatet i dette felt ændres også til Bogføring og administration (Økonomisk) med nul decimaler.
- For det tredje felt beløb sætte en titel Tælle og en operation til ham - Tælle (Antal)
- I Kolonneetiketter (Kolonner) felt oprettet automatisk Σ Værdier (Σ Værdier) – træk det til området Rækkeetiketter (Linjer)
Her er hvad vi ender med:
Samlet beløb, gennemsnitsværdi og antal salg – alt sammen i én pivottabel!
Konklusion
Pivottabeller i Microsoft Excel indeholder en masse funktioner og indstillinger. I sådan en lille artikel er de ikke engang tæt på at dække dem alle. Det ville tage en lille bog eller en stor hjemmeside for fuldt ud at beskrive alle mulighederne ved pivottabeller. Fed og nysgerrige læsere kan fortsætte deres udforskning af pivottabeller. For at gøre dette skal du bare højreklikke på næsten ethvert element i pivottabellen og se, hvilke funktioner og indstillinger der åbner. På båndet finder du to faner: Pivottabelværktøjer: Indstillinger (analyse) og Design (Konstruktør). Vær ikke bange for at lave en fejl, du kan altid slette pivottabellen og starte forfra. Du har en mulighed, som langtidsbrugere af DOS og Lotus 1-2-3 aldrig har haft.