Arbeta med pivottabeller i Microsoft Excel

Pivottabeller är en av de mest kraftfulla funktionerna i Microsoft Excel. De tillåter stora mängder data att analyseras och sammanfattas med bara några musklick. I den här artikeln utforskar vi PivotTables, förstår vad de är och lär dig hur du skapar och anpassar dem.

Obs! Denna artikel skrivs med Excel 2010 (Beta). Konceptet med en PivotTable har förändrats lite under åren, men metoden att skapa en har förändrats i nästan varje iteration av Excel. Om du använder en version av Excel som inte är 2010, förvänta dig olika skärmar från de som du ser i den här artikeln.

En liten historia

I de tidiga dagarna av kalkylarksprogrammen rullade Lotus 1-2-3 roosten. Dess dominans var så komplett att folk tyckte att det var slöseri med tid för Microsoft att bry sig om att utveckla sin egen kalkylprogram (Excel) för att konkurrera med Lotus. Flash-forward till 2010, och Excel dominans på kalkylarknaden är större än Lotus någonsin, medan antalet användare som fortfarande kör Lotus 1-2-3 närmar sig noll. Hur hände det här? Vad orsakade en sådan dramatisk återföring av förmögenheter?

Branschanalytiker sätter ner det på två faktorer: För det första bestämde Lotus att den här nya nya GUI-plattformen "Windows" var en passande korg som aldrig skulle ta av. De nekade att skapa en Windows-version av Lotus 1-2-3 (för några år, ändå), förutsatt att deras DOS-version av programvaran var allt som någonsin skulle behövas. Microsoft utvecklade naturligtvis Excel exklusivt för Windows. För det andra utvecklade Microsoft en funktion för Excel som Lotus inte tillhandahöll i 1-2-3, nämligen pivottabeller. PivotTables-funktionen, exklusiv till Excel, ansågs så oerhört användbar att människor var villiga att lära sig ett helt nytt mjukvarupaket (Excel) istället för att hålla sig i ett program (1-2-3) som inte hade det. Den här funktionen, tillsammans med felbedömningen av framgången för Windows, var döds-knell för Lotus 1-2-3 och början på framgången med Microsoft Excel.

Förstå pivottabeller

Så vad är en PivotTable, exakt?

Enkelt sagt, en PivotTable är en sammanfattning av vissa data, skapade för att möjliggöra enkel analys av nämnda data. Men till skillnad från en manuellt skapad sammanfattning är Excel PivotTables interaktiva. När du har skapat en, kan du enkelt ändra den om den inte erbjuder exakta insikter i dina data som du hoppades på. Med ett par klick kan sammanfattningen "svängas" - roteras på ett sådant sätt att kolumnrubrikerna blir radrubriker och vice versa. Det finns mycket mer som kan göras också. Istället för att försöka beskriva alla funktioner i PivotTables, visar vi dem enkelt ...

De data som du analyserar med en PivotTable kan inte vara rättvisa några data - det måste vara rå data, tidigare obearbetad (omärkad) - vanligtvis en lista av något slag. Ett exempel på detta kan vara listan över försäljningsaffärer i ett företag under de senaste sex månaderna.

Undersök data som visas nedan:

Observera att detta är inte rådata. Det är faktiskt redan en sammanfattning av något slag. I cell B3 kan vi se $ 30,000, vilket uppenbarligen är summan av James Cooks försäljning för januari månad. Så var är rådata? Hur kom vi fram till $ 30.000? Var är den ursprungliga listan över försäljningsaffärer som den här siffran genererades av? Det är klart att någonstans måste någon ha gått i besväret med att samla alla försäljningsaffärer under de senaste sex månaderna i den sammanfattning vi ser ovan. Hur lång tid antar du att det tog? En timma? Tio?

Förmodligen ja. Du ser att kalkylbladet ovan är faktiskt inte en pivottabell. Det skapades manuellt från rådata lagrade på annat håll, och det tog faktiskt ett par timmar att sammanställa. Men det är exakt den typen av sammanfattning som skulle kunna skapas med hjälp av pivottabeller, i vilket fall det skulle ha tagit några sekunder. Låt oss ta reda på hur ...

Om vi skulle spåra upp den ursprungliga listan över försäljningsaffärer kan det se ut så här:

Du kan bli förvånad att veta att med hjälp av PivotTable-funktionen i Excel kan vi skapa en månadsomsättningssammanfattning som liknar den ovanstående inom några sekunder, med några få musklick. Vi kan göra det - och mycket mer också!

Hur man skapar en pivottabell

Först, se till att du har några rådata i ett kalkylblad i Excel. En lista över finansiella transaktioner är typisk, men det kan vara en lista med nästan allt: Medarbetarnas kontaktuppgifter, din cd-samling eller bränsleförbrukningsuppgifter för ditt företags bilpark.

Så vi börjar Excel ... och vi laddar en sådan lista ...

När vi väl har listan öppen i Excel är vi redo att börja skapa PivotTable.

Klicka på en enda cell i listan:

Sedan, från Föra in fliken, klicka på pivottabellen ikon:

De Skapa pivottabell rutan visas och frågar dig två frågor: Vilka data borde din nya pivottabell baseras på och var ska den skapas? Eftersom vi redan klickat på en cell i listan (i steget ovan) är hela listan kring den cellen redan vald för oss ($ A $ 1: $ G $ 88 på betalningar ark, i detta exempel). Observera att vi kunde välja en lista i någon annan region i något annat kalkylblad, eller till och med en extern datakälla, till exempel en databas för Access-databasen eller till och med en MS-SQL Server-databas tabell. Vi måste också välja om vi vill att vår nya PivotTable ska skapas på en ny kalkylblad, eller på en existerande ett. I det här exemplet väljer vi en ny ett:

Det nya kalkylbladet är skapat för oss, och en tom PivotTable skapas på det här kalkylbladet:

En annan låda visas också: The PivotTable Field List. Denna fältlista visas när vi klickar på någon cell i pivottabellen (ovan):

Listan med fält i den övre delen av rutan är faktiskt samlingen av kolumnrubriker från det ursprungliga rådatabladet. De fyra tomma rutorna i nedre delen av skärmen gör att vi kan välja hur vi vill att vår PivotTable ska sammanfatta de råa data. Hittills finns det inget i dessa lådor, så PivotTable är tomt. Allt vi behöver göra är att dra fält ner från listan ovan och släpp dem i de nedre rutorna. En PivotTable skapas sedan automatiskt för att matcha våra instruktioner. Om vi får fel, behöver vi bara dra fälten tillbaka till var de kom ifrån och / eller dra ny fält ner för att ersätta dem.

De värden låda är förmodligen den viktigaste av de fyra. Fältet som släpas in i den här rutan representerar de data som måste sammanfattas på något sätt (genom summering, medelvärde, att hitta maximalt, minimalt osv.). Det är nästan alltid numerisk data. En perfekt kandidat för denna ruta i vår provdata är fält / kolumn "Mängd". Låt oss dra det fältet in i värden låda:

Observera att (a) fältet "Belopp" i fältlistan är kryssat och "Summan av belopp" har lagts till värden rutan, vilket indikerar att summan kolumn har summerats.

Om vi granskar själva pivottabellen hittar vi faktiskt summan av alla värdena "Mängd" från det rada databladet:

Vi har skapat vår första PivotTable! Praktiskt, men inte särskilt imponerande. Det är troligt att vi behöver lite mer inblick i våra data än det.

Med hänvisning till våra provdata måste vi identifiera en eller flera kolumnrubriker som vi kan tänka sig använda för att dela upp denna summa. Vi kan till exempel bestämma att vi skulle vilja se en sammanfattning av våra data där vi har en radrubrik för var och en av de olika säljarena i vårt företag, och totalt för varje. För att uppnå detta är allt vi behöver göra för att dra fältet "Säljare" i Rad etiketter låda:

Nu, äntligen börjar saker bli intressanta! Vår PivotTable börjar ta form ... .

Med ett par klick har vi skapat ett bord som skulle ha gått lång tid att göra manuellt.

Så vad kan vi göra? Tja, på ett sätt är vår PivotTable komplett. Vi har skapat en användbar sammanfattning av vår källdata. De viktiga sakerna är redan lärt! För resten av artikeln kommer vi att undersöka några sätt att mer komplexa pivottabeller kan skapas och sätt att dessa pivottabeller kan anpassas.

Först kan vi skapa en två-dimensionell bord. Låt oss göra det genom att använda "Betalningsmetod" som en kolumnrubrik. Dra bara rubriken "Betalningsmetod" till Kolonnetiketter låda:

Som ser ut så här:

Börjar bli mycket Häftigt!

Låt oss göra det en tre-dimensionell bord. Vad kan ett sådant bord eventuellt se ut? Vi får se…

Dra kolumnen "Paket" / rubrik till Rapportera filter låda:

Lägg märke till var det slutar ... .

Detta gör att vi kan filtrera vår rapport utifrån vilket "semesterpaket" köptes. Till exempel kan vi se fördelningen mellan säljare och betalningsmetod för Allt paket eller, med ett par klick, ändra det för att visa samma uppdelning för "Sunseekers" -paketet:

Och så, om du tänker på det på rätt sätt, är vår PivotTable nu tredimensionell. Låt oss fortsätta anpassa ...

Om det visar sig, att vi bara vill se checka och kreditkort transaktioner (det vill säga inga kontanttransaktioner), då kan vi avmarkera "Cash" -objektet från kolumnrubrikerna. Klicka på rullgardinsmenyn bredvid Kolonnetiketter, och avmarkera "Cash":

Låt oss se hur det ser ut ... Som du kan se är "Cash" borta.

formatering

Detta är uppenbarligen ett mycket kraftfullt system, men hittills ser resultaten ut väldigt vanligt och tråkigt. För en början ser siffrorna som vi summerar inte ut som dollarbelopp - bara vanliga gamla nummer. Låt oss rätta till det.

En frestelse kan vara att göra vad vi brukar göra under sådana omständigheter och helt enkelt välj hela bordet (eller hela kalkylbladet) och använd standardnummerformatknapparna på verktygsfältet för att slutföra formateringen. Problemet med det här tillvägagångssättet är att om du någonsin ändrar strukturen hos pivottabellen i framtiden (som är 99% sannolikt), kommer dessa nummerformat att gå vilse. Vi behöver ett sätt som gör dem (semi-) permanenta.

Först hittar vi inmatningsbeloppet "Summan av beloppet" i värden rutan och klicka på den. En meny visas. Vi väljer Valfältinställningar ... från menyn:

De Värdefältinställningar rutan visas.

Klicka på Nummerformat knappen och standarden Format Cells box visas:

Från Kategori lista, välj (säg) Bokföring, och släpp antal decimaler till 0. Klicka på ok några gånger för att komma tillbaka till pivottabellen ...

Som du kan se har siffrorna formats korrekt som dollarbelopp.

Medan vi är föremål för formatering, låt oss formatera hela pivottabellen. Det finns några sätt att göra detta. Låt oss använda en enkel en ...

Klicka på PivotTable Verktyg / Design flik:

Släpp sedan ned pilen längst ned till höger om PivotTable Styles lista för att se en stor samling inbyggda stilar:

Välj någon som appellerar och titta på resultatet i din PivotTable:

Andra alternativ

Vi kan också arbeta med datum. Nu brukar det finnas många, många datum i en transaktionslista som den vi började med. Men Excel ger möjlighet att gruppera dataobjekt tillsammans efter dag, vecka, månad, år etc. Låt oss se hur det här görs.

Låt oss först ta bort kolumnen "Betalningsmetod" från Kolonnetiketter rutan (dra bara tillbaka den till fältlistan) och ersätt den med kolumnen "Datum bokad":

Som du kan se gör det här vårt PivotTable direkt värdelöst, vilket ger oss en kolumn för varje datum då en transaktion inträffade - ett mycket brett bord!

För att åtgärda detta, högerklicka på vilket datum som helst och välj Grupp… från kontextmenyn:

Grupprutan visas. Vi väljer månader och klicka på OK:

Voila! en mycket mer användbar tabell:

(I övrigt är denna tabell nästan identisk med den som visas i början av den här artikeln - den ursprungliga säljöversikten som skapades manuellt.)

En annan cool sak att vara medveten om är att du kan ha mer än en uppsättning radrubriker (eller kolumnrubriker):

... som ser ut så här ... .

Du kan göra en liknande sak med kolumnrubriker (eller till och med rapportfiler).

Håll sakerna enkla igen, låt oss se hur man plottar genomsnitt värden, snarare än summerade värden.

Klicka först på "Summan av belopp" och välj Valfältinställningar ... från kontextmenyn som visas:

I Summarisera värdefältet med lista i Värdefältinställningar rutan, välj Medel:

Medan vi är här, låt oss ändra Anpassat namn, från "medelvärde" till något lite mer koncis. Skriv in något som "Avg":

Klick ok, och se hur det ser ut. Observera att alla värden ändras från summerade totaler till medelvärden, och tabelltiteln (övre vänstra cellen) har ändrats till "Avg":

Om vi vill kan vi till och med ha summor, medelvärden och räkningar (antal = hur många försäljningar det fanns) alla på samma pivottabell!

Här är stegen för att få något sådant på plats (från en tom PivotTable):

Dra "Säljare" i Kolonnetiketter
Dra "Fält" fält ner i värden låda tre gånger
För det första fältet "Amount", ändra dess anpassade namn till "Total" och det är nummerformat till Bokföring (0 decimaler)
För det andra fältet "Amount", ändra dess anpassade namn till "Average", dess funktion till Medel och det är nummerformat till Bokföring (0 decimaler)
För det tredje fältet "Amount" ändras namn till "Count" och dess funktion till Räkna
Dra det automatiskt skapade fält från Kolonnetiketter till Rad etiketter

Här är vad vi hamnar med:

Totalt, genomsnittligt och räknar med samma pivottabell!

Slutsats

Det finns många, många fler funktioner och alternativ för PivotTables skapade av Microsoft Excel - alldeles för många att lista i en artikel som denna. För att fullt ut täcka potentialen i pivottabeller, skulle en liten bok (eller en stor webbplats) krävas. Modiga och / eller geekiga läsare kan utforska PivotTables vidare ganska enkelt: Högerklicka bara på allt och se vilka alternativ som blir tillgängliga för dig. Det finns också de två band-flikarna: PivotTable Tools / Options och Design. Det spelar ingen roll om du gör ett misstag - det är lätt att ta bort pivottabellen och starta igen - en möjlighet att gamla DOS-användare av Lotus 1-2-3 aldrig hade.

Om du arbetar i Office 2007 kan du kolla in vår artikel om hur du skapar en pivottabell i Excel 2007.

Vi har inkluderat en Excel-arbetsbok som du kan ladda ner för att öva dina PivotTable-färdigheter på. Den ska fungera med alla versioner av Excel från och med 97.

Ladda ner vår praxis i Excel-arbetsboken