Pivottabeller är en av de mest kraftfulla funktionerna i excel. Även om du är nybörjare kan du knyta stora mängder data till användbar information. Pivottabell kan hjälpa dig att göra rapporter på några minuter. Analysera dina rena data enkelt och om data inte är rena kan det hjälpa dig att rengöra dina data. Jag vill inte tröttna på dig, så låt oss hoppa in i det och utforska.
Hur man skapar ett pivottabell
Det är enkelt. Välj bara dina data. Gå till Infoga. Klicka på pivottabellen och det är klart.
Men vänta. Innan du skapar en pivottabell, se till att alla kolumner har en rubrik.
Om någon kolumnrubrik lämnas tom skapas inte pivottabellen och kommer att gå igenom ett felmeddelande.
Krav 1: Alla kolumner ska ha en rubrik för att komma igång med pivottabeller i Excel
Du bör ha dina data organiserade med rätt rubrik. När du har det kan du infoga pivottabellen.
Sätt i pivottabellen från menyfliksområdet
För att infoga en pivottabell från menyn, följ dessa steg:
1. Välj ditt dataintervall
2. Gå till infoga -fliken
3. Klicka på pivottabellikonen.
4. Skapa en pivottabell -alternativruta visas.
5. Här kan du se det dataområde som du valt. Om du tror att detta inte är det intervall du vill välja, ändra storlek direkt på ditt intervall härifrån istället för att gå tillbaka och välja data igen.
6. Därefter kan du välja var du vill ha ditt pivottabell. Jag rekommenderar att du använder det nya kalkylbladet men du kan också använda det aktuella kalkylbladet. Definiera bara platsen i rutan Plats.
7. Om du är klar med inställningarna trycker du på OK -knappen. Du kommer att ha ditt svängbord i ett nytt ark. Välj bara dina fält för sammanfattningar. Vi kommer att se hur vi skapar en sammanfattning av data med hjälp av pivottabellen, men låt oss först förstå grunderna. I den här excel -pivottabellen kommer du att lära dig mer än du förväntar dig.
Infoga genväg för pivottabell (Alt> N> V)
Detta är en sekventiell kortkommando för att öppna Skapa Pivottabell alternativruta.
Tryck på Alt -knappen och släpp den. Träffa N och släpp den. Träffa V och släpp den. Skapa en pivottabell kommer att öppnas.
Följ nu ovanstående procedur för att skapa en pivottabell i excel.
Infoga genväg för pivottabell med gammal Excel -genväg (Alt> D> P)
En sak jag gillar mest med Microsoft Excel är att de i varje ny version av Excel introducerar nya funktioner men de släng inte de gamla funktionerna (som MS gjorde med vinst 8. Det var patetiskt). Detta tillåter de gamla användarna att arbeta normalt med nya versioner som de brukade arbeta med äldre versioner.
Om du trycker på sekvensiellt ALT, D och P på tangentbordet öppnas Excel och skapar en pivottabellguide.
Välj lämpligt alternativ. Det valda alternativet i skärmdumpen ovan leder oss till att skapa en pivottabell som vi skapade tidigare.
Tryck Enter eller klicka på Nästa om du vill kontrollera ditt valda intervall.
Tryck på Enter igen.
Välj ett nytt kalkylblad eller var du vill att din pivottabell ska slå Enter. Och det är klart.
Skapa rapporter med pivottabeller
Nu vet du hur du sätter in ett pivottabell. Låt oss förbereda oss för att göra rapporter med hjälp av pivottabeller på några minuter.
Vi har data för den stationära ordningen.
Kolumnfälten är:
Orderdatum: Beställningsdatum (självklart)
Område: Ordningsregionen i landet
Kund: Kundens namn (vad mer kan det vara)
Artikel: Beställd artikel
Enhet: Antal enheter för en beställd artikel
Enhetskostnad: Kostnad per enhet
Total: Total kostnad för beställningen (enhet*enhetskostnad).
För att skapa rapporter med pivottabeller använder vi
Pivottabellfält: Innehåller listan över namn på kolumner i dina data.
Pivotområden: Dessa fyra områden används för att visa dina data på ett sätt.
FILTER: Lägg här fält från vilka du vill använda filter i din rapport.
KOLUMNER: Lägg de fält du vill ha här i dina kolumner i rapporten: (Det är bättre att visa än förklara)
RADER: Dra fält som du vill visa råmässigt som i bilden ovan, jag har visat Region i RADER.
VÄRDEN: Välj ett fält för att få Count, Sum, Average, Procent (och många fler) etc. som du vill se.
Med hjälp av ovanstående information har vi utarbetat den här snabba pivotrapporten som visar från vilken region hur många beställningar som görs för varje artikel.
Nu när du förstår dina data och Pivot -fält (trots allt är du smart), låt oss svara på några frågor relaterade till dessa data med hjälp av pivottabellen snabbt.
Q1. Hur många beställningar finns det?
Pivottabellen är initialt tom, som visas på bilden nedan.
Du måste välja fält (kolumnnamn) i lämpliga områden för att se sammanfattningen eller detaljerna i det fältet.
För att svara på frågan ovan väljer jag objekt (välj valfri kolumn, se bara till att den inte har en tom cell däremellan) i värdefält.
Välj objekt från fältlistan och dra det till värdefältet.
Vi har vårt svar. Pivottabeller säger att det finns totalt 43 beställningar. Detta är rätt.
Nu när jag har valt Orderdatum i området Värden visar det 42. Detta innebär att Orderdatum har en tom cell eftersom vi vet att det totala antalet order är 43.
Identifiera oregelbundna data med hjälp av pivottabeller och rengör dem.
Pivottabell kan hjälpa dig att hitta felaktig information i data.
För det mesta utarbetas våra uppgifter av datainmatningsoperatörer eller av användare som vanligtvis är oregelbundna och behöver rengöras för att förbereda korrekt rapport och analys.
Du bör alltid rengöra och förbereda dina data på ett sätt som du gör innan du förbereder några rapporter. Men ibland först efter att vi har förberett rapporten får vi veta att våra uppgifter har en viss oegentlighet. Kom, jag visar dig hur …
Q2: Berätta antal order från varje region
Nu för att svara på denna fråga:
Välj Område och dra den till Rader Område och Artikel till Värden Område.
Vi kommer att få en Region-Wise-uppdelad data. Vi kan svara från vilken region hur många order som har kommit.
Det finns totalt 4 regioner i våra data enligt pivottabellen. Men vänta, märk det Central och Centrle område. Vi vet att Centrle borde vara det Central. Det finns en oegentlighet. Vi måste gå till våra data och göra datastädning.
För att rensa data i regionfältet filtrerar vi bort det felaktiga regionnamnet (Centrle) och korrigerar det (Central).
Gå nu tillbaka till dina pivotdata.
Högerklicka var som helst på pivottabellen och klicka på Uppdatera.
Din rapport har nu uppdaterats.
Nu kan du se att det bara finns tre regioner.
Pivotrapportformatering med kategoriserade rader.
Ibland behöver du rapporter som bilden ovan. Detta gör det enkelt att se dina data på ett strukturerat sätt. Du kan enkelt avgöra från vilken region hur många artiklar som är beställda. Låt oss se hur du kan göra detta.
Flytta Område och Artikel till RADER område. Se till att regionen är överst och objekt längst ner som visas på bilden.
Drag Artikel för Värde Område.
Som ett resultat får du den här rapporten.
Det räcker. Men ibland vill din chef rapportera i tabellform utan delsummor. För att göra detta måste vi formatera vårt pivottabell.
Ta bort delsummor från pivottabellen
Följ dessa steg:
1. Klicka var som helst på pivottabellen.
2. Gå till Design Flik.
3. Klicka på delsummorna meny.
4. Klicka på Visa inte delsummor.
Du kan se att det inte finns några subtotaler nu.
Bra. Men det finns fortfarande inte i tabellform. Regioner och objekt visas i en enda kolumn. Visa dem separat.
Gör en pivottabell i tabellform
För att visa regioner och objekt i olika kolumner, följ dessa steg:
1. Klicka var som helst på pivottabellen
2. Gå till fliken Design
3. Klicka på Rapportlayout.
4. Klicka på Visa för alternativet tabellform. Slutligen kommer du att få denna sofistikerade syn på din rapport.
Nu vet vi det totala antalet beställningar för varje artikel från varje region. Det visas i greven av Artiklar -kolumner.
Ändra kolumnnamnet till Order.
Det ser bättre ut nu.
F3: Hur många enheter av varje artikel är beställda?
För att svara på denna fråga behöver vi en summa enheter. För att göra det, flytta bara fältet Enheter till Värden. Det summerar automatiskt antalet enheter för varje artikel. Om en kolumn i pivottabellen endast innehåller värden visar pivottabellen som standard summan av dessa värden. Men det kan ändras från en värdefältinställning. Hur? Jag visar dig det senare.
Pivottabellvärde Fältinställningar
F4: Genomsnittspriset för varje vara?
I våra provdata är priset på en vara olika för olika beställningar. Se till exempel bindemedel.
Jag vill veta den genomsnittliga kostnaden för varje artikel i pivottabellen. För att ta reda på detta, dra enhetskostnad till värdefält. Den visar summan av enhetskostnad.
Vi vill inte Summan av enhetskostnad, Vi vill Genomsnitt av enhetskostnad. Att göra så…
1. Högerklicka var som helst på summan av kolumnen Enhetskostnad i pivottabellen
2. Klicka Värdefältinställningar
3. Baserat på tillgängliga alternativ, Välj Genomsnitt och tryck OK.
Slutligen kommer du att ha denna Pivot -rapport:
Pivottabell beräknade fält
En av de mest användbara funktionerna i pivottabellen är dess beräknade fält. Beräknade fält är fält som erhålls av vissa operationer på tillgängliga kolumner.
Låt oss förstå hur man infogar beräknade fält i pivottabellen med ett exempel:
Baserat på våra uppgifter utarbetade vi denna rapport.
Här har vi Summan av enheter och Total kostnad. Jag flyttade precis den totala kolumnen till fältet Värden och döpte sedan om den till Total kostnad. Nu vill jag veta genomsnittspriset för en enhet för varje artikel för varje region. Och det skulle vara:
Genomsnittligt pris = totalkostnad / totala enheter
Låt oss infoga ett fält i pivottabellen som visar genomsnittspriset för varje artikelregion:
Följ dessa steg för att infoga ett beräknat fält i pivottabellen
1. Klicka var som helst på pivottabellen och gå till fliken Analysera
2. Klicka på Fält, objekt och uppsättningar i beräkningsgruppen.
3. Klicka på Beräknade fält.
Du kommer att se den här inmatningsrutan för ditt beräkningsfält:
4. I namninmatningsrutan skriver du Genomsnittlig kostnad eller något du gillar, excel har inget emot. I formulärinmatningsrutan skriver du och trycker på OK.
= Totalt / enheter
Du kan skriva detta manuellt från tangentbordet eller så kan du dubbelklicka på fältnamnen i fältområdet för att utföra operationer.
5. Du har nu ditt beräknade fält lagt till i pivottabellen. Det kallas summan av genomsnittskostnad men det är inte en summa. Excel kör bara standardfunktionen för att namnge kolumnen (som en ritual). Byt namn på den här kolumnen och begränsa de decimaler som visas.
Och där har du ett beräknat fält. Du kan göra det så komplext som du vill. För ett exempel tog jag denna enkla genomsnittliga operation.
Gruppering i pivottabellen
Du har den här pivotrapporten förberedd.
Nu vill jag att denna rapport ska delas upp årligen. Se ögonblicksbilden nedan.
Vi har en kolumn om Beställningsdatum. Flytta fältet OrderDate till rader överst.
Det ser ingenting ut som den obligatoriska rapporten. Vi måste ha gruppdatum året efter.
Följ nu dessa steg för att gruppera ett fält i Excel -pivottabell:
1. Högerklicka på det fält du vill gruppera.
2. Klicka på Grupp. Du kommer att ha den här alternativrutan för anpassning. Eftersom detta är en datakolumn visar Excel oss gruppering i enlighet därmed. Du kan välja ditt start- och slutdatum.
3. Välj i flera år och tryck OK. Du har årligen grupperat i pivottabellen i Excel.
Skivare av vridbord
Slicers introducerades i Excel 2010 som ett tillägg. I Excel 2013 och 2016 är det tillgängligt som standard precis som filter.
Skivare är inget annat än filter. Till skillnad från filter visar Slicers alla tillgängliga alternativ framför dig. Det gör din instrumentpanel mer interaktiv.
Så här lägger du till skärare i pivottabellen, Excel 2016 och 2013
Pivot Table Slicers är lätt att lägga till. Följ dessa steg:
1. Klicka var som helst på pivottabellen och gå till fliken Analysera.
2. Klicka på Insert Slicer. Du kommer att ha en lista med fält för dina data. Välj så många du vill.
3. I det här exemplet väljer du Region och trycker på OK.
Du har lagt till Slicer i din rapport. Applicera nu filtret med bara ett klick.
Infoga tidslinje i Excel 2016 och 2013
Detta är en av mina favoritfunktioner i Excel -pivottabeller. Denna nya funktionalitet fungerar bara med datum. Med hjälp av detta kan du visuellt välja en tidsperiod för att filtrera dina data.
För att infoga en tidslinjebandare, följ dessa steg:
Hur man lägger till tidslinje i pivottabell, Excel 2016 och 2013
1. Klicka var som helst på pivottabellen och gå till fliken Analysera.
2. Klicka på Infoga tidslinje från filtergrupp. Alla kolumner som innehåller tidsvärden i data kommer att listas i en alternativruta att välja mellan. Här har vi bara en. Så ja…
2. Välj dina alternativ och tryck på Retur eller klicka på OK. Det är klart och du har din pivottabellens tidslinje precis framför dig.
Du kan välja att visa den dagligen, månadsvis, kvartalsvis eller årligen. Jag har valt Monthly här.
I den här artikeln har jag täckt de viktigaste och användbaraste funktionerna i pivottabellen. Vi undersökte nya funktioner i Pivot Table i Excel 2016 och 2013. Vi lärde oss om den klassiska användningen av en pivottabell som kördes i Excel 2007, 2010 och äldre. De är fortfarande användbara. Om du inte hittade ditt svar relaterat till din pivottabell här, fråga i kommentarfältet.
Det finns många andra funktioner som ännu inte ska förklaras. Vi lär oss avancerad pivottabellfunktion i nästa artikel. Tills dess Excel på allt.