Innehåll
Pivottabeller är ett av de mest kraftfulla verktygen i Excel. De låter dig analysera och summera olika sammanfattningar av stora datamängder med bara några musklick. I den här artikeln kommer vi att bekanta oss med pivottabeller, förstå vad de är, lära oss hur man skapar och anpassar dem.
Den här artikeln skrevs med Excel 2010. Konceptet med pivottabeller har inte förändrats mycket under åren, men sättet du skapar dem på är något annorlunda i varje ny version av Excel. Om du har en version av Excel som inte är 2010, var beredd på att skärmdumparna i den här artikeln kommer att skilja sig från vad du ser på din skärm.
Lite historia
I början av kalkylprogram, Lotus 1-2-3 härskar bollen. Dess dominans var så fullständig att Microsofts ansträngningar att utveckla sin egen mjukvara (Excel) som ett alternativ till Lotus verkade som ett slöseri med tid. Snabbspolning nu till 2010! Excel dominerar kalkylark mer än vad Lotus-kod någonsin har gjort i sin historia, och antalet personer som fortfarande använder Lotus är nära noll. Hur kunde detta hända? Vad var anledningen till en så dramatisk händelseutveckling?
Analytiker identifierar två huvudfaktorer:
- Först bestämde Lotus att denna nymodiga GUI-plattform kallad Windows bara var en övergående modefluga som inte skulle vara länge. De vägrade att bygga en Windows-version av Lotus 1-2-3 (men bara under några år), och förutspådde att DOS-versionen av deras mjukvara skulle vara allt konsumenter någonsin skulle behöva. Microsoft utvecklade naturligtvis Excel specifikt för Windows.
- För det andra introducerade Microsoft ett verktyg i Excel som heter PivotTables som inte var tillgängligt i Lotus 1-2-3. Pivottabeller, exklusiva för Excel, visade sig vara så överväldigande användbara att människor tenderade att hålla fast vid den nya Excel-programsviten snarare än att fortsätta med Lotus 1-2-3, som inte hade dem.
Pivottabeller, tillsammans med att underskatta framgången för Windows i allmänhet, spelade dödsmarschen för Lotus 1-2-3 och inledde framgången för Microsoft Excel.
Vad är pivottabeller?
Så, vad är det bästa sättet att karakterisera vad pivottabeller är?
Enkelt uttryckt är pivottabeller sammanfattningar av vissa data, skapade för att underlätta analysen av dessa data. Till skillnad från manuellt skapade totaler är Excel-pivottabeller interaktiva. När du väl har skapat dem kan du enkelt ändra dem om de inte ger den bild du hoppades på. Med bara ett par musklick kan summor vändas så att kolumnrubriker blir radrubriker och vice versa. Du kan göra många saker med pivottabeller. Istället för att försöka beskriva alla funktioner i pivottabeller med ord, är det lättare att demonstrera det i praktiken ...
Datan du analyserar med pivottabeller kan inte vara slumpmässig. Det ska vara rådata, som en lista av något slag. Det kan till exempel vara en lista över försäljningar som företaget gjort under de senaste sex månaderna.
Titta på data som visas i figuren nedan:
Observera att detta inte är rådata, eftersom det redan har sammanfattats. I cell B3 ser vi $30000, vilket förmodligen är det totala resultatet som James Cook gjorde i januari. Var finns originaldatan då? Var kom siffran 30000 XNUMX $ ifrån? Var finns den ursprungliga försäljningslistan från vilken denna månadssumma härleddes? Det är tydligt att någon har gjort ett jättebra jobb med att organisera och sortera all försäljningsdata för de senaste sex månaderna och omvandla den till tabellen över totaler som vi ser. Hur lång tid tror du att det tog? Timme? Klockan tio?
Faktum är att tabellen ovan inte är en pivottabell. Den var handgjord från rådata lagrad på annat håll och tog minst ett par timmar att bearbeta. Just en sådan sammanfattningstabell kan skapas med hjälp av pivottabeller på bara några sekunder. Låt oss ta reda på hur...
Om vi går tillbaka till den ursprungliga försäljningslistan skulle den se ut ungefär så här:
Du kanske blir förvånad över att vi från denna lista över affärer med hjälp av pivottabeller och på bara några sekunder kan skapa en månatlig försäljningsrapport i Excel, som vi analyserade ovan. Ja, vi kan göra det och mer!
Hur skapar man en pivottabell?
Se först till att du har lite källdata i ett Excel-ark. Listan över finansiella transaktioner är den mest typiska som inträffar. I själva verket kan det vara en lista över vad som helst: kontaktuppgifter till anställda, en CD-samling eller ditt företags bränsleförbrukningsdata.
Så vi startar Excel ... och laddar en sådan lista ...
Efter att vi har öppnat den här listan i Excel kan vi börja skapa en pivottabell.
Välj valfri cell från den här listan:
Sedan på fliken Införande (Infoga) välj kommando pivottabellen (pivottabell):
En dialogruta visas Skapa pivottabell (Skapa en pivottabell) med två frågor till dig:
- Vilken data ska jag använda för att skapa en ny pivottabell?
- Var ska man placera pivotbordet?
Eftersom vi i föregående steg redan har valt en av listcellerna kommer hela listan automatiskt att väljas för att skapa en pivottabell. Observera att vi kan välja ett annat intervall, en annan tabell och till och med någon extern datakälla som en Access- eller MS-SQL-databastabell. Dessutom måste vi välja var vi ska placera det nya pivotbordet: på ett nytt ark eller på ett av de befintliga. I det här exemplet kommer vi att välja alternativet – Nytt arbetsblad (till ett nytt blad):
Excel skapar ett nytt ark och placerar en tom pivottabell på det:
Så snart vi klickar på någon cell i pivottabellen kommer en annan dialogruta att visas: Pivottabellfältlista (Pivottabellfält).
Listan med fält överst i dialogrutan är en lista över alla titlar från den ursprungliga listan. De fyra tomma områdena längst ner på skärmen låter dig berätta för pivottabellen hur du vill sammanfatta data. Så länge dessa ytor är tomma finns det inget i tabellen heller. Allt vi behöver göra är att dra rubrikerna från det övre området till de tomma områdena nedan. Samtidigt genereras en pivottabell automatiskt, i enlighet med våra instruktioner. Om vi gör ett misstag kan vi ta bort rubrikerna från det nedre området eller dra andra för att ersätta dem.
Area Värden (Betydningar) är förmodligen den viktigaste av de fyra. Vilken rubrik som placeras i detta område avgör vilken data som kommer att summeras (summa, medelvärde, maximum, minimum, etc.) Dessa är nästan alltid numeriska värden. En utmärkt kandidat för en plats inom detta område är uppgifterna under rubriken Antal (Kostnad) för vårt ursprungliga bord. Dra den här titeln till området Värden (Värden):
Observera att titeln Antal är nu markerad med en bock, och i området Värden (Värden) en post har dykt upp Summa av belopp (Beloppsfält Belopp), som anger att kolumnen Antal sammanfattad.
Om vi tittar på själva pivottabellen kommer vi att se summan av alla värden från kolumnen Antal originalbord.
Så, vår första pivottabell är skapad! Bekvämt, men inte särskilt imponerande. Vi vill förmodligen få mer information om vår data än vad vi har för närvarande.
Låt oss gå till originaldata och försöka identifiera en eller flera kolumner som kan användas för att dela upp denna summa. Vi kan till exempel forma vår pivottabell på ett sådant sätt att den totala försäljningen beräknas för varje säljare individuellt. De där. rader kommer att läggas till i vår pivottabell med namnet på varje säljare i företaget och deras totala försäljningsbelopp. För att uppnå detta resultat, dra bara i titeln säljare (säljrepresentant) till regionen Radetiketter (Strängar):
Det blir mer intressant! Vår pivottabell börjar ta form...
Ser du fördelarna? Med ett par klick skapade vi en tabell som skulle ha tagit väldigt lång tid att skapa manuellt.
Vad mer kan vi göra? Nåväl, på sätt och vis är vårt pivotbord klart. Vi har skapat en användbar sammanfattning av originaldata. Viktig information har redan mottagits! I resten av den här artikeln kommer vi att titta på några sätt att skapa mer komplexa pivottabeller, samt lära oss hur man anpassar dem.
Inställning av pivottabell
Först kan vi skapa en tvådimensionell pivottabell. Låt oss göra detta med hjälp av kolumnrubriken Betalningsmetod (Betalningsmetod). Dra bara titeln Betalningsmetod till området Kolumnetiketter (Kolumner):
Vi får resultatet:
Ser väldigt coolt ut!
Låt oss nu göra en tredimensionell tabell. Hur skulle ett sådant bord se ut? Låt oss se…
Dra Header Paket (Komplex) till området rapportfilter (Filter):
Notera var han är...
Detta ger oss möjlighet att filtrera rapporten utifrån "Vilket semesterkomplex betalades för." Till exempel kan vi se en uppdelning efter säljare och efter betalningsmetoder för alla komplex, eller med ett par musklick, ändra vy av pivottabellen och visa samma uppdelning endast för de som beställt komplexet Solsökare.
Så, om du förstår detta rätt, kan vår pivottabell kallas tredimensionell. Låt oss fortsätta konfigureringen...
Om det plötsligt visar sig att endast betalning med check och kreditkort (det vill säga kontantlös betalning) ska visas i pivottabellen, då kan vi stänga av visningen av titeln Kontanter (Kontanter). För detta, bredvid Kolumnetiketter klicka på nedåtpilen och avmarkera rutan i rullgardinsmenyn Kontanter:
Låt oss se hur vår pivottabell ser ut nu. Som du kan se, kolumnen Kontanter försvann från henne.
Formatera pivottabeller i Excel
Pivottabeller är uppenbarligen ett mycket kraftfullt verktyg, men än så länge ser resultaten lite enkla och tråkiga ut. Till exempel, siffrorna vi lägger ihop ser inte ut som dollarbelopp – de är bara siffror. Låt oss fixa det här.
Det är frestande att göra det du är van vid i en sådan situation och helt enkelt välja hela tabellen (eller hela arket) och använda standardknapparna för nummerformatering i verktygsfältet för att ställa in önskat format. Problemet med detta tillvägagångssätt är att om du någonsin ändrar strukturen på pivottabellen i framtiden (vilket händer med en chans på 99 %), kommer formateringen att gå förlorad. Vad vi behöver är ett sätt att göra det (nästan) permanent.
Låt oss först hitta posten Summa av belopp in Värden (Värden) och klicka på den. Välj objektet i menyn som visas Värde fältinställningar (Värdefältsalternativ):
En dialogruta visas Värde fältinställningar (Värdefältsalternativ).
tryck nummer Format (Nummerformat), en dialogruta öppnas. Formatera celler (cellformat):
Från listan Kategori (Nummerformat) välj Redovisning (Finansiellt) och ställ in antalet decimaler till noll. Tryck nu några gånger OKför att gå tillbaka till vår pivottabell.
Som du kan se är siffrorna formaterade som dollarbelopp.
Medan vi håller på med formatering, låt oss ställa in formatet för hela pivottabellen. Det finns flera sätt att göra detta. Vi använder den som är enklare...
Klicka på Pivottabellverktyg: Design (Arbeta med pivottabeller: Konstruktör):
Expandera sedan menyn genom att klicka på pilen i det nedre högra hörnet av avsnittet Pivottabellstilar (PivotTable Styles) för att se den omfattande samlingen av inline-stilar:
Välj en lämplig stil och titta på resultatet i din pivottabell:
Andra pivottabellinställningar i Excel
Ibland behöver du filtrera data efter datum. Till exempel, i vår lista över affärer finns det många, många datum. Excel tillhandahåller ett verktyg för att gruppera data efter dag, månad, år och så vidare. Låt oss se hur det går till.
Ta först bort posten. Betalningsmetod från regionen Kolumnetiketter (Kolumner). För att göra detta, dra den tillbaka till listan med titlar och flytta titeln i dess ställe Bokat datum (bokningsdatum):
Som du kan se gjorde detta tillfälligt vår pivottabell värdelös. Excel skapade en separat kolumn för varje datum då en affär gjordes. Som ett resultat fick vi ett väldigt brett bord!
För att åtgärda detta, högerklicka på valfritt datum och välj från snabbmenyn GRUPP (Grupp):
Grupperingsdialogrutan visas. Vi väljer Månader (månader) och klicka OK:
Voila! Den här tabellen är mycket mer användbar:
Den här tabellen är förresten nästan identisk med den som visas i början av artikeln, där försäljningssummorna sammanställdes manuellt.
Det finns en annan mycket viktig punkt som du behöver veta! Du kan skapa inte en utan flera nivåer av rad- (eller kolumn)rubriker:
... och det kommer att se ut så här ...
Detsamma kan göras med kolumnrubriker (eller till och med filter).
Låt oss återgå till den ursprungliga formen av tabellen och se hur man visar medelvärden istället för summor.
För att komma igång, klicka på Summa av belopp och välj från menyn som visas Värde fältinställningar (Värdefältsalternativ):
Listan Sammanfatta värdefältet med (Operation) i dialogrutan Värde fältinställningar (Värdefältsalternativ) välj Genomsnitt (Medel):
Samtidigt, medan vi är här, låt oss ändra oss CustomName (Anpassat namn) med Genomsnitt av belopp (Beloppsfält Belopp) till något kortare. Ange något liknande i det här fältet Avg:
Presse OK och se vad som händer. Observera att alla värden har ändrats från totaler till medelvärden, och tabellrubriken (i den övre vänstra cellen) har ändrats till Avg:
Om du vill kan du omedelbart få belopp, snitt och antal (försäljningar) placerade i en pivottabell.
Här är en steg-för-steg-guide om hur du gör detta, som börjar med en tom pivottabell:
- Dra Header säljare (säljrepresentant) till regionen Kolumnetiketter (Kolumner).
- Dra titeln tre gånger Antal (Kostnad) till område Värden (Värden).
- För det första fältet Antal ändra titeln till Totalt (Belopp), och nummerformatet i detta fält är Redovisning (Finansiell). Antalet decimaler är noll.
- Andra fältet Antal namn Genomsnittlige, ställ in operationen för det Genomsnitt (Genomsnitt) och sifferformatet i detta fält ändras också till Redovisning (ekonomisk) med noll decimaler.
- För det tredje fältet Antal ange en titel Att Räkna och en operation för honom - Att Räkna (Kvantitet)
- I Kolumnetiketter Fältet (kolumner) skapas automatiskt Σ Värden (Σ Värden) – dra den till området Radetiketter (Rader)
Här är vad vi kommer att sluta med:
Totalt belopp, medelvärde och antal försäljningar – allt i en pivottabell!
Slutsats
Pivottabeller i Microsoft Excel innehåller många funktioner och inställningar. I en så liten artikel är de inte ens i närheten av att täcka dem alla. Det skulle krävas en liten bok eller en stor webbplats för att fullständigt beskriva alla möjligheter med pivottabeller. Djärva och nyfikna läsare kan fortsätta sin utforskning av pivottabeller. För att göra detta, högerklicka bara på nästan alla element i pivottabellen och se vilka funktioner och inställningar som öppnas. På bandet hittar du två flikar: Pivottabellverktyg: Alternativ (analys) och Designa (Konstruktör). Var inte rädd för att göra ett misstag, du kan alltid ta bort pivottabellen och börja om. Du har en möjlighet som länge användare av DOS och Lotus 1-2-3 aldrig haft.