Arbejde med pivottabeller i Microsoft Excel

Pivottabeller er en af ​​de mest kraftfulde funktioner i Microsoft Excel. De giver mulighed for at analysere store mængder data og opsummeres på blot et par klik med musen. I denne artikel udforsker vi pivottabeller, forstår hvad de er, og lærer at oprette og tilpasse dem.

Bemærk: Denne artikel er skrevet ved hjælp af Excel 2010 (Beta). Konceptet med en PivotTable har ændret sig lidt gennem årene, men metoden til at skabe en er ændret i næsten hver iteration af Excel. Hvis du bruger en version af Excel, der ikke er 2010, forventer du forskellige skærmbilleder fra dem, du ser i denne artikel.

En lille historie

I de tidlige dage af regnearksprogrammer styrede Lotus 1-2-3 roosteren. Dens dominans var så fuldstændig, at folk troede, at det var spild af tid for Microsoft at forstyrre at udvikle deres eget regnearksoftware (Excel) for at konkurrere med Lotus. Flash-frem til 2010, og Excel's dominans på regnearkmarkedet er større end Lotus nogensinde, mens antallet af brugere, der stadig kører Lotus 1-2-3 nærmer sig nul. Hvordan skete dette? Hvad forårsagede en så dramatisk omstilling af formuer?

Industrianalytikere satte det ned på to faktorer: For det første besluttede Lotus at denne nye nye GUI platform kaldet "Windows" var en forbipasserende fad, der aldrig ville tage ud. De nægtede at oprette en Windows-version af Lotus 1-2-3 (i et par år, alligevel), forudsigede at deres DOS-version af softwaren var alt, hvad nogen nogensinde ville have brug for. Microsoft udviklede naturligvis Excel udelukkende til Windows. For det andet udviklede Microsoft en funktion til Excel, som Lotus ikke leverede i 1-2-3, nemlig pivottabeller.  PivotTables-funktionen, eksklusiv til Excel, blev anset så uhyre nyttig, at folk var villige til at lære en helt ny softwarepakke (Excel) i stedet for at holde fast i et program (1-2-3), der ikke havde det. Denne ene funktion sammen med fejlkonklusionen af ​​succesen i Windows var dødsklokken for Lotus 1-2-3 og begyndelsen af ​​succesen med Microsoft Excel.

Forståelse af pivottabeller

Så hvad er en PivotTable, nøjagtigt?

Enkeltvis er en PivotTable et resumé af nogle data, der er oprettet for at gøre det let at analysere dataene. Men i modsætning til en manuelt oprettet oversigt er Excel PivotTables interaktive. Når du har oprettet en, kan du nemt ændre den, hvis den ikke giver den nøjagtige indsigt i dine data, som du havde håbet på. Med et par klik kan sammendraget "drejes" - drejes på en sådan måde, at kolonneoverskrifterne bliver radoverskrifter og omvendt. Der er meget mere, der kan gøres også. I stedet for at prøve at beskrive alle funktionerne i PivotTables, demonstrerer vi dem simpelthen ...

De data, du analyserer ved hjælp af en PivotTable, kan ikke være lige nogen data - det skal være rå data, tidligere ubearbejdet (umummariseret) - typisk en liste af en slags. Et eksempel herpå kan være listen over salgstransaktioner i et selskab i de sidste seks måneder.

Undersøg dataene vist nedenfor:

Bemærk, at dette er ikke rådata. Faktisk er det allerede et resumé af en slags. I celle B3 kan vi se $ 30.000, hvilket tilsyneladende er summen af ​​James Cooks salg for januar måned. Så hvor er de rå data? Hvordan kom vi til tallet på $ 30.000? Hvor er den oprindelige liste over salgstransaktioner, som denne figur blev genereret af? Det er klart, at et eller andet sted har været nødt til at samle alle salgstransaktionerne i de sidste seks måneder i det resumé, vi ser ovenfor. Hvor længe antager du det tog? En time? Ti?

Sandsynligvis ja. Du ser, at regnearket over er faktisk ikke en pivottabel. Det blev oprettet manuelt fra rå data lagret andetsteds, og det tog faktisk et par timer at kompilere. Men det er netop den slags opsummering der kunne oprettes ved hjælp af pivottabeller, i hvilket tilfælde det ville have taget kun få sekunder. Lad os finde ud af hvordan ...

Hvis vi skulle spore den oprindelige liste over salgstransaktioner, kan det se ud som sådan:

Du kan blive overrasket over at vide, at ved hjælp af PivotTable-funktionen i Excel kan vi oprette et månedligt salgssammendrag, der ligner det ovenstående i løbet af få sekunder, med kun et par klik med musen. Vi kan gøre dette - og meget mere også!

Sådan oprettes en pivottabel

Først skal du sikre dig, at du har nogle rå data i et regneark i Excel. En liste over finansielle transaktioner er typisk, men det kan være en liste over næsten alt: Medarbejderkontaktoplysninger, din cd-samling eller brændstofforbrugstal for din virksomheds bilflåde.

Så vi starter Excel ... og vi indlæser en sådan liste ...

Når vi har listen åben i Excel, er vi klar til at begynde at oprette PivotTable.

Klik på en enkelt enkelt celle i listen:

Så fra Indsæt fanebladet, klik på knappen pivottabel ikon:

Det Opret PivotTable feltet vises, spørger du to spørgsmål: Hvilke data skal din nye PivotTable være baseret på, og hvor skal den oprettes? Fordi vi allerede har klikket på en celle i listen (i ovenstående trin), er hele listen omkring den pågældende celle allerede valgt for os ($ A $ 1: $ G $ 88 på den Betalinger ark, i dette eksempel). Bemærk, at vi kunne vælge en liste i en hvilken som helst anden region i et andet regneark eller endda en ekstern datakilde, som f.eks. En database til Access-database eller endda en MS-SQL Server-database. Vi skal også vælge, om vi ønsker, at vores nye PivotTable skal oprettes på en ny regneark eller på en eksisterende en. I dette eksempel vælger vi en ny en:

Det nye regneark er oprettet for os, og der oprettes en tom PivotTable på det regneark:

En anden boks vises også: The PivotTable Field List.  Denne feltliste vises, når vi klikker på en celle i pivottabellen (ovenfor):

Listen over felter i den øverste del af boksen er faktisk indsamlingen af ​​kolonneoverskrifter fra det oprindelige rådataarkblad. De fire blanke felter i den nederste del af skærmen giver os mulighed for at vælge den måde, hvorpå vores PivotTable vil opsummere de rå data. Indtil videre er der intet i disse bokse, så PivotTable er tomt. Alt vi skal gøre er at trække felter ned fra listen ovenfor og slippe dem i de nederste felter. En PivotTable oprettes derefter automatisk for at matche vores instruktioner. Hvis vi tager fejl, skal vi kun trække felterne tilbage til, hvor de kom fra og / eller trække ny felter ned for at erstatte dem.

Det Værdier boks er uden tvivl den vigtigste af de fire. Feltet, der trækkes i denne boks, repræsenterer de data, der skal opsummeres på en eller anden måde (ved at summere, gennemsnitlige, finde maksimum, minimum osv.). Det er næsten altid numerisk data. En perfekt kandidat til denne boks i vores eksempeldata er feltet "Beløb" / kolonne. Lad os trække dette felt ind i Værdier boks:

Bemærk, at (a) feltet "Beløb" i listen over felter nu er krydset, og "Summen af ​​beløb" er blevet tilføjet til Værdier boks, hvilket indikerer at summeskolonnen er opsummeret.

Hvis vi undersøger PivotTable'en selv, finder vi faktisk summen af ​​alle "Mængde" -værdierne fra raw data-regnearket:

Vi har oprettet vores første PivotTable! Handy, men ikke særlig imponerende. Det er sandsynligt, at vi har brug for lidt mere indsigt i vores data end det.

Med henvisning til vores stikprøvedata skal vi identificere en eller flere kolonneoverskrifter, som vi kunne tænke dig at bruge til at opdele denne total. For eksempel kan vi beslutte at vi gerne vil se et resumé af vores data, hvor vi har en række overskrift for hver af de forskellige sælgere i vores firma, og en samlet for hver. For at opnå dette er alt, hvad vi skal gøre, at trække feltet "Sælger" i Row Etiketter boks:

Nu, Endelig begynder tingene at blive interessante! Vores PivotTable begynder at tage form ... .

Med et par klik har vi lavet et bord, der ville have taget lang tid at gøre manuelt.

Så hvad kan vi ellers gøre? Nå er vores PivotTable på en måde færdig. Vi har oprettet et nyttigt resumé af vores kildedata. De vigtige ting er allerede lært! For resten af ​​artiklen vil vi undersøge nogle måder, hvorpå mere komplekse PivotTables kan oprettes, og måder at disse PivotTables kan tilpasses.

For det første kan vi oprette en to-dimensionelle bord. Lad os gøre det ved at bruge "Betalingsmetode" som en kolonneoverskrift. Du skal blot trække overskriften "Betalingsmetode" til Kolonne etiketter boks:

Hvilket ser ud som dette:

Begynder at få meget fedt nok!

Lad os gøre det til en tre-dimensionelle bord. Hvad kunne et sådant bord muligvis se ud? Nå, lad os se ...

Træk kolonnen "Pakke" / overskrift til Rapport filter boks:

Bemærk, hvor det ender ... .

Dette gør det muligt for os at filtrere vores rapport ud fra hvilken "feriepakke" blev købt. For eksempel kan vi se fordelingen af ​​sælger vs betalingsmetode for alle pakker eller, med et par klik, ændre det for at vise den samme sammenbrud for "Sunseekers" -pakken:

Og så, hvis du tænker på den rigtige måde, er vores PivotTable nu tredimensionel. Lad os fortsætte med at tilpasse ...

Hvis det viser sig, at vi kun vil se check og kreditkort transaktioner (dvs. ingen kontanttransaktioner), så kan vi fravælge "Cash" -objektet fra kolonneoverskrifterne. Klik på rullemenuen ved siden af Kolonne etiketter, og afkryds "Cash":

Lad os se, hvordan det ser ud ... Som du kan se, er "Cash" væk.

Formatering

Dette er naturligvis et meget kraftfuldt system, men resultaterne ser hidtil meget tydeligt og kedeligt ud. For en start ser de tal, som vi opsummerer, ikke ud som dollarbeløb - bare gamle tal. Lad os rette op på det.

En fristelse kan være at gøre, hvad vi er vant til at gøre under sådanne omstændigheder, og vælg blot hele tabellen (eller hele regnearket) og brug standardnummerformateringsknapperne på værktøjslinjen for at fuldføre formateringen. Problemet med denne tilgang er, at hvis du nogensinde ændrer PivotTables struktur i fremtiden (hvilket er 99% sandsynligt), vil disse talformater gå tabt. Vi har brug for en måde, der vil gøre dem (semi-) permanente.

Først finder vi indgangen "Sum of Amount" i Værdier boks og klik på den. Der vises en menu. Vi vælger Værdifeltindstillinger ... fra menuen:

Det Værdifeltindstillinger feltet vises.

Klik på knappen Nummerformat knappen og standarden Format Cells box kommer til syne:

Fra Kategori liste, vælg (siger) Regnskab, og slip antallet af decimaler til 0. Klik på Okay et par gange for at komme tilbage til pivottabellen ...

Som du kan se, er tallene blevet korrekt formateret som dollarbeløb.

Mens vi er underlagt formatering, lad os formatere hele PivotTable. Der er et par måder at gøre dette på. Lad os bruge en simpel en ...

Klik på knappen PivotTable Værktøjer / Design fane:

Dernæst slip pilen i bunden til højre af PivotTable Styles liste for at se en bred vifte af indbyggede stilarter:

Vælg en, der appellerer, og se resultatet i din PivotTable:

Andre muligheder

Vi kan også arbejde med datoer. Nu er der normalt mange, mange datoer i en transaktionsliste, som den vi startede med. Men Excel giver mulighed for at gruppere dataelementer sammen efter dag, uge, måned, år osv. Lad os se, hvordan dette er gjort.

Lad os først fjerne kolonnen "Betalingsmetode" fra Kolonne etiketter boks (skal du blot trække den tilbage til feltlisten) og erstatte den med kolonnen "Dato bogført":

Som du kan se, gør dette vores PivotTable øjeblikkeligt ubrugelig og giver os en kolonne for hver dato, hvor en transaktion fandt sted - et meget bredt bord!

For at løse dette skal du højreklikke på en dato og vælge Gruppe… fra kontekstmenuen:

Gruppeboksen vises. Vi vælger måneder og klik på OK:

Voila! EN meget mere nyttigt bord:

(I øvrigt er denne tabel næsten identisk med den, der vises i begyndelsen af ​​denne artikel - det oprindelige salgssammendrag, der blev oprettet manuelt.)

En anden cool ting at være opmærksom på er, at du kan have mere end et sæt af rækkeoverskrifter (eller kolonneoverskrifter):

... som ser sådan ud ... .

Du kan gøre en lignende ting med kolonneoverskrifter (eller endda rapportfiltre).

At holde tingene enkle igen, lad os se, hvordan man plotte gennemsnit værdier i stedet for summerede værdier.

Klik først på "Summen af ​​beløb", og vælg Værdifeltindstillinger ... fra den kontekstmenu, der vises:

I Opsummer værdiværdien ved liste i Værdifeltindstillinger boks, vælg Gennemsnit:

Mens vi er her, lad os ændre Brugernavn, fra "gennemsnittet af beløb" til noget lidt mere præcist. Indtast noget som "Avg":

Klik Okay, og se hvordan det ser ud. Bemærk, at alle værdierne skifter fra summerede totals til middelværdier, og tabeltitlen (øverste venstre celle) er ændret til "Avg":

Hvis vi kan lide, kan vi endda have summer, gennemsnit og tællinger (tæller = hvor mange salg der var) alle på samme pivottabel!

Her er trinene for at få noget sådan på plads (startende fra en tom pivotabel):

1. Træk "Sælger" ind i Kolonne etiketter
2. Træk "Amount" feltet ned i Værdier boks tre gange
3. For det første "Beløb" -felt skal du ændre sit brugernavn til "Total", og det er talformat til Regnskab (0 decimaler)
4. For det andet "Beløb" -felt skal du ændre sit brugernavn til "Gennemsnit", dets funktion til Gennemsnit og det er nummerformat til Regnskab (0 decimaler)
5. For det tredje "Beløb" -felt skal du ændre navnet til "Count" og dets funktion til Tælle
6. Træk den automatisk oprettede felt fra Kolonne etiketter til Row Etiketter

Her er hvad vi ender med:

I alt, gennemsnit og tæller på samme pivottabel!

Konklusion

Der er mange, mange flere funktioner og muligheder for PivotTables skabt af Microsoft Excel - alt for mange til at liste i en artikel som denne. For fuldt ud at dække potentialet i PivotTables, ville en lille bog (eller en stor hjemmeside) være påkrævet. Modige og / eller geeky læsere kan udforske PivotTables yderligere ganske let: Bare højreklik på næsten alt, og se hvilke muligheder der bliver tilgængelige for dig. Der er også de to bånd-faner: PivotTable Tools / Options og Design.  Det er ligegyldigt, om du laver en fejl - det er nemt at slette pivottabellen og starte igen - en mulighed gamle DOS-brugere af Lotus 1-2-3 aldrig havde.

Hvis du arbejder i Office 2007, kan du måske se vores artikel om, hvordan du opretter en pivottabel i Excel 2007.

Vi har medtaget en Excel-projektmappe, som du kan downloade for at øve dine PivotTable-færdigheder på. Det skal fungere sammen med alle versioner af Excel fra 97 og fremefter.

Download vores praksis Excel-projektmappe