Om du läser denna handledning finns det en stor chans att du har hört talas om (eller till och med använt) Excel -pivottabellen. Det är en av de mest kraftfulla funktionerna i Excel (ingen skoj).
Det bästa med att använda ett pivottabell är att även om du inte vet något i Excel, kan du fortfarande göra ganska fantastiska saker med det med en mycket grundläggande förståelse för det.
Låt oss börja.
Klicka här för att ladda ner provdata och följa med.
Vad är ett vridbord och varför ska du bry dig?
Ett pivottabell är ett verktyg i Microsoft Excel som låter dig snabbt sammanfatta enorma datamängder (med några klick).
Även om du är helt ny i Excel -världen kan du enkelt använda ett pivottabell. Det är lika enkelt som att dra och släppa rader/kolumnrubriker för att skapa rapporter.
Anta att du har en dataset som visas nedan:
Detta är försäljningsdata som består av ~ 1000 rader.
Den har försäljningsdata efter region, återförsäljartyp och kund.
Nu kanske din chef vill veta några saker från dessa data:
- Vad var den totala försäljningen i södra regionen 2016?
- Vilka är de fem bästa återförsäljarna efter försäljning?
- Hur jämförde The Home Depots prestanda sig med andra återförsäljare i söder?
Du kan fortsätta och använda Excel -funktioner för att ge dig svaren på dessa frågor, men tänk om din chef plötsligt kommer med en lista med ytterligare fem frågor.
Du måste gå tillbaka till data och skapa nya formler varje gång det sker en förändring.
Det är här Excel -pivottabellerna är riktigt användbara.
Inom några sekunder kommer en pivottabell att svara på alla dessa frågor (som du lär dig nedan).
Men den verkliga fördelen är att den kan rymma din fiffiga datadrivna chef genom att svara på hans frågor omedelbart.
Det är så enkelt, du kan lika gärna ta några minuter och visa din chef hur du gör det själv.
Förhoppningsvis har du en uppfattning om varför pivottabeller är så fantastiska. Låt oss fortsätta och skapa en pivottabell med hjälp av datamängden (visas ovan).
Infoga en pivottabell i Excel
Här är stegen för att skapa en pivottabell med data som visas ovan:
- Klicka var som helst i datauppsättningen.
- Gå till Infoga -> Tabeller -> Pivottabell.
- I dialogrutan Skapa pivottabell fungerar standardalternativen bra i de flesta fall. Här är några saker att kolla in:
- Tabell/intervall: Den fylls i som standard baserat på din datamängd. Om dina data inte har några tomma rader/kolumner identifierar Excel automatiskt rätt intervall. Du kan ändra detta manuellt om det behövs.
- Om du vill skapa pivottabellen på en specifik plats, under alternativet "Välj var du vill att pivottabellrapporten ska placeras", ange platsen. Annars skapas ett nytt kalkylblad med pivottabellen.
- Klicka på OK.
Så snart du klickar på OK skapas ett nytt kalkylblad med pivottabellen i det.
Medan pivottabellen har skapats ser du ingen data i den. Allt du ser är pivottabellnamnet och en enda radinstruktion till vänster och pivottabellfält till höger.
Nu innan vi hoppar in i att analysera data med hjälp av detta pivottabell, låt oss förstå vad som är muttrar och bultar som gör en Excel -pivottabell.
Muttrarna och bultarna i ett Excel -pivottabell
För att använda ett pivottabell effektivt är det viktigt att känna till komponenterna som skapar ett pivottabell.
I det här avsnittet lär du dig om:
- Pivot -cache
- Värdeområde
- Rader område
- Kolumner Område
- Filterområde
Pivot -cache
Så snart du skapar en pivottabell med hjälp av data händer något i backend. Excel tar en ögonblicksbild av data och lagrar dem i minnet. Denna ögonblicksbild kallas Pivot Cache.
När du skapar olika vyer med en pivottabell går Excel inte tillbaka till datakällan, utan använder Pivot -cachen för att snabbt analysera data och ge dig sammanfattningen/resultaten.
Anledningen till att en pivotcache genereras är att optimera pivottabellens funktion. Även när du har tusentals rader med data är en pivottabell supersnabb i att sammanfatta data. Du kan dra och släppa objekt i raderna/kolumner/värden/filter så uppdateras resultaten direkt.
Obs! En nackdel med pivotcachen är att den ökar storleken på din arbetsbok. Eftersom det är en kopia av källdata, när du skapar en pivottabell, lagras en kopia av den data i Pivot -cachen.
Läs mer: Vad är Pivot Cache och hur man använder det bäst.
Värdeområde
Värdeområdet är det som innehåller beräkningarna/värdena.
Baserat på datauppsättningen som visas i början av självstudien, om du snabbt vill beräkna den totala försäljningen efter region i varje månad kan du få en pivottabell som visas nedan (vi får se hur du skapar detta senare i självstudien) .
Området markerat med orange är värdesområdet.
I det här exemplet har den totala försäljningen i varje månad för de fyra regionerna.
Rader område
Rubrikerna till vänster om området Values gör raderna.
I exemplet nedan innehåller området Rader regionerna (markerade med rött):
Kolumner Område
Rubrikerna högst upp i området Värden gör området Kolumner.
I exemplet nedan innehåller kolumnområdet månaderna (markerade med rött):
Filterområde
Filterområdet är ett valfritt filter som du kan använda för att gå vidare i datamängden.
Om du till exempel bara vill se försäljningen för multiline -återförsäljare kan du välja det alternativet från rullgardinsmenyn (markerad i bilden nedan), och pivottabellen uppdateras endast med data för multiline -återförsäljare.
Analysera data med hjälp av pivottabellen
Låt oss nu försöka svara på frågorna med hjälp av pivottabellen vi har skapat.
Klicka här för att ladda ner provdata och följa med.
För att analysera data med en pivottabell måste du bestämma hur du vill att datasammanfattningen ska se ut i det slutliga resultatet. Till exempel kanske du vill ha alla regioner till vänster och den totala försäljningen bredvid den. När du har denna klarhet i åtanke kan du helt enkelt dra och släppa relevanta fält i pivottabellen.
I avsnittet Pivot Tabe Fields har du fälten och områdena (som markeras nedan):
Fälten skapas baserat på backenddata som används för pivottabellen. Området Område är där du placerar fälten, och beroende på var ett fält går uppdateras dina data i pivottabellen.
Det är en enkel drag -och -släpp -mekanism, där du helt enkelt kan dra ett fält och placera det i ett av de fyra områdena. Så snart du gör detta visas det i pivottabellen i kalkylbladet.
Låt oss nu försöka svara på de frågor din chef hade med denna pivottabell.
Q1: Vad var den totala försäljningen i södra regionen?
Dra fältet Region i området Rader och fältet Intäkter i området Värden. Det skulle automatiskt uppdatera pivottabellen i kalkylbladet.
Observera att så snart du släpper fältet Intäkter i området Värden blir det summan av intäkter. Som standard summerar Excel alla värden för en viss region och visar summan. Om du vill kan du ändra detta till räkna, genomsnitt eller andra statistiska mätvärden. I det här fallet är summan vad vi behövde.
Svaret på denna fråga skulle vara 21225800.
Q2 Vilka är de fem bästa återförsäljarna efter försäljning?
Dra kundfältet i radområdet och fältet Intäkter i värdeområdet. Om det finns andra fält i områdesdelen och du vill ta bort det, välj det helt enkelt och dra ut det.
Du får ett pivottabell enligt nedan:
Observera att föremålen (i det här fallet kunderna) som standard är sorterade i alfabetisk ordning.
För att få de fem bästa återförsäljarna kan du helt enkelt sortera den här listan och använda de fem bästa kundnamnen. Att göra detta:
- Högerklicka på valfri cell i området Värden.
- Gå till Sortera -> Sortera störst till minsta.
Detta ger dig en sorterad lista baserad på total försäljning.
F3: Hur jämförde The Home Depots prestanda sig med andra återförsäljare i söder?
Du kan göra en hel del analys för denna fråga, men låt oss bara försöka jämföra försäljningen.
Dra regionfältet i raderna. Dra nu fältet Kund i området Rader under fältet Region. När du gör detta förstår Excel att du vill kategorisera dina data först efter region och sedan efter kunder inom regionerna. Du kommer att ha något som visas nedan:
Dra nu fältet Intäkter i området Värden och du får försäljningen för varje kund (liksom den totala regionen).
Du kan sortera återförsäljarna utifrån försäljningssiffrorna genom att följa stegen nedan:
- Högerklicka på en cell som har försäljningsvärdet för alla återförsäljare.
- Gå till Sortera -> Sortera störst till minsta.
Detta skulle omedelbart sortera alla återförsäljare efter försäljningsvärdet.
Nu kan du snabbt skanna genom södra regionen och identifiera att The Home Depot -försäljningen var 3004600 och det gick bättre än fyra återförsäljare i södra regionen.
Nu finns det mer än ett sätt att flå katten. Du kan också placera regionen i filterområdet och sedan bara välja södra regionen.
Klicka här för att ladda ner provdata.
Jag hoppas att denna handledning ger dig en grundläggande översikt över Excel -pivottabeller och hjälper dig att komma igång med det.
Här är några fler pivottabellstudier du kanske gillar:
- Förbereda källdata för pivottabell.
- Så här använder du villkorlig formatering i en pivottabell i Excel.
- Hur man grupperar datum i pivottabeller i Excel.
- Hur man grupperar nummer i pivottabellen i Excel.
- Så här filtrerar du data i en pivottabell i Excel.
- Använda skärare i Excel -pivottabell.
- Så här ersätter du tomma celler med nollor i Excel -pivottabeller.
- Hur man lägger till och använder en Excel -pivottabell Beräknade fält.
- Hur man uppdaterar pivottabellen i Excel.
