Hvis du læser denne vejledning, er der en stor chance for, at du har hørt om (eller endda brugt) Excel -pivottabellen. Det er en af de mest kraftfulde funktioner i Excel (ingen sjov).
Den bedste del ved at bruge en pivottabel er, at selvom du ikke ved noget i Excel, kan du stadig gøre ret fantastiske ting med det med en meget grundlæggende forståelse af det.
Lad os komme igang.
Klik her for at downloade eksempeldataene og følge med.
Hvad er et drejebord, og hvorfor skal du være ligeglad?
En pivottabel er et værktøj i Microsoft Excel, der giver dig mulighed for hurtigt at opsummere enorme datasæt (med et par klik).
Selvom du er helt ny i Excel -verdenen, kan du nemt bruge en pivottabel. Det er lige så let som at trække og slippe rækker/kolonneoverskrifter for at oprette rapporter.
Antag, at du har et datasæt som vist herunder:
Dette er salgsdata, der består af ~ 1000 rækker.
Det har salgsdata efter region, forhandlertype og kunde.
Nu vil din chef måske gerne vide et par ting fra disse data:
- Hvad var det samlede salg i region Syd i 2016?
- Hvad er de fem bedste forhandlere efter salg?
- Hvordan kunne Home Depots ydelse sammenlignes med andre detailhandlere i Syd?
Du kan fortsætte og bruge Excel -funktioner til at give dig svarene på disse spørgsmål, men hvad nu hvis din chef pludselig kommer med en liste med fem flere spørgsmål.
Du bliver nødt til at gå tilbage til dataene og oprette nye formler, hver gang der sker en ændring.
Det er her, Excel -pivottabeller er virkelig nyttige.
Inden for få sekunder vil en pivottabel besvare alle disse spørgsmål (som du vil lære nedenfor).
Men den reelle fordel er, at den kan rumme din småfine datadrevne chef ved at besvare hans spørgsmål med det samme.
Det er så enkelt, at du lige så godt kan tage et par minutter og vise din chef, hvordan du gør det selv.
Forhåbentlig har du nu en idé om, hvorfor pivottabeller er så fantastiske. Lad os gå videre og oprette en pivottabel ved hjælp af datasættet (vist ovenfor).
Indsætning af en pivottabel i Excel
Her er trinene til at oprette en pivottabel ved hjælp af ovenstående data:
- Klik et vilkårligt sted i datasættet.
- Gå til Indsæt -> Tabeller -> Pivottabel.
- I dialogboksen Opret pivottabel fungerer standardindstillingerne fint i de fleste tilfælde. Her er et par ting at tjekke ind:
- Tabel/område: Det er udfyldt som standard baseret på dit datasæt. Hvis dine data ikke har tomme rækker/kolonner, identificerer Excel automatisk det korrekte område. Du kan ændre dette manuelt, hvis det er nødvendigt.
- Hvis du vil oprette pivottabellen på et bestemt sted, skal du angive placeringen under indstillingen 'Vælg, hvor du vil have pivottabelrapporten'. Ellers oprettes et nyt regneark med pivottabellen.
- Klik på OK.
Så snart du klikker på OK, oprettes et nyt regneark med pivottabellen i det.
Mens pivottabellen er blevet oprettet, ser du ingen data i den. Det eneste, du vil se, er pivottabellens navn og en enkeltlinjeanvisning til venstre og pivottabelfelter til højre.
Nu, før vi hopper ind i at analysere data ved hjælp af denne pivottabel, lad os forstå, hvad der er møtrikker og bolte, der laver en Excel -pivottabel.
Møtrikker og bolte i en Excel -pivottabel
For at bruge en pivottabel effektivt er det vigtigt at kende de komponenter, der opretter en pivottabel.
I dette afsnit lærer du om:
- Pivot -cache
- Værdier Område
- Rækker område
- Kolonner Område
- Filtreringsområde
Pivot -cache
Så snart du opretter en pivottabel ved hjælp af dataene, sker der noget i backend. Excel tager et øjebliksbillede af dataene og gemmer dem i sin hukommelse. Dette øjebliksbillede kaldes Pivot Cache.
Når du opretter forskellige visninger ved hjælp af en pivottabel, går Excel ikke tilbage til datakilden, snarere bruger den Pivot -cachen til hurtigt at analysere dataene og give dig resuméet/resultaterne.
Grunden til, at en pivotcache genereres, er for at optimere pivottabellens funktion. Selv når du har tusindvis af rækker med data, er en pivottabel superhurtig med at opsummere dataene. Du kan trække og slippe elementer i felterne rækker/kolonner/værdier/filtre, og det opdaterer øjeblikkeligt resultaterne.
Bemærk: En ulempe ved pivot -cache er, at den øger størrelsen på din projektmappe. Da det er en kopi af kildedataene, gemmes en kopi af disse data i Pivot -cachen, når du opretter en pivottabel.
Læs mere: Hvad er Pivot Cache, og hvordan bruges den bedst.
Værdier Område
Værdiområdet er det, der indeholder beregningerne/værdierne.
Baseret på datasættet vist i begyndelsen af selvstudiet, hvis du hurtigt vil beregne det samlede salg efter region i hver måned, kan du få en pivottabel som vist nedenfor (vi ser, hvordan du opretter dette senere i selvstudiet) .
Området markeret med orange er værdierne.
I dette eksempel har det det samlede salg i hver måned for de fire regioner.
Rækker område
Overskrifterne til venstre for området Værdier udgør området Rækker.
I eksemplet herunder indeholder området Rækker regionerne (markeret med rødt):
Kolonner Område
Overskrifterne øverst i området Værdier gør området Kolonner.
I eksemplet herunder indeholder området Kolonner månederne (markeret med rødt):
Filtreringsområde
Filtreringsområdet er et valgfrit filter, som du kan bruge til at uddybe yderligere i datasættet.
Hvis du f.eks. Kun vil se salget for multiline -forhandlere, kan du vælge denne mulighed fra rullemenuen (fremhævet på billedet herunder), og pivottabellen opdateres kun med dataene til multiline -forhandlere.
Analyse af data ved hjælp af pivottabellen
Lad os nu prøve at besvare spørgsmålene ved at bruge den pivottabel, vi har oprettet.
Klik her for at downloade eksempeldataene og følge med.
For at analysere data ved hjælp af en pivottabel skal du beslutte, hvordan du vil have dataoversigten til at se ud i det endelige resultat. For eksempel vil du muligvis have alle regionerne i venstre side og det samlede salg lige ved siden af. Når du har denne klarhed i tankerne, kan du blot trække og slippe de relevante felter i pivottabellen.
I sektionen Pivot Tabe Fields har du felterne og områderne (som fremhævet nedenfor):
Felterne oprettes baseret på backenddataene, der bruges til pivottabellen. Området Område er, hvor du placerer felterne, og alt efter hvor et felt går, opdateres dine data i pivottabellen.
Det er en simpel træk -og -slip -mekanisme, hvor du simpelthen kan trække et felt og sætte det i et af de fire områder. Så snart du gør dette, vises det i pivottabellen i regnearket.
Lad os nu prøve at besvare de spørgsmål, din leder havde ved hjælp af denne pivottabel.
Q1: Hvad var det samlede salg i regionen Syd?
Træk feltet Region i området Rækker og feltet Indtægter i området Værdier. Det ville automatisk opdatere pivottabellen i regnearket.
Bemærk, at så snart du slipper feltet Omsætning i området Værdier, bliver det Sum af indtægter. Som standard summerer Excel alle værdierne for en given region og viser totalet. Hvis du vil, kan du ændre dette til Count, Average eller andre statistiske metrics. I dette tilfælde er summen det, vi havde brug for.
Svaret på dette spørgsmål ville være 21225800.
Q2 Hvad er de fem bedste forhandlere efter salg?
Træk feltet Kunde i området Række og Omsætning i værdiområdet. Hvis der er andre felter i områdesektionen, og du vil fjerne det, skal du blot markere det og trække det ud af det.
Du får en pivottabel som vist herunder:
Bemærk, at varerne (i dette tilfælde kunderne) som standard er sorteret i alfabetisk rækkefølge.
For at få de fem bedste forhandlere kan du blot sortere denne liste og bruge de fem bedste kundenavne. At gøre dette:
- Højreklik på en celle i området Værdier.
- Gå til Sorter -> Sorter størst til mindste.
Dette vil give dig en sorteret liste baseret på det samlede salg.
Spørgsmål 3: Hvordan sammenlignede The Home Depots ydelse sig med andre detailhandlere i Syd?
Du kan foretage en masse analyse af dette spørgsmål, men lad os bare prøve at sammenligne salget.
Træk regionfeltet i området Rækker. Træk nu feltet Kunde i området Rækker under feltet Region. Når du gør dette, ville Excel forstå, at du først vil kategorisere dine data efter område og derefter efter kunder inden for regionerne. Du har noget som vist herunder:
Træk nu feltet Omsætning i området Værdier, og du får salget for hver kunde (såvel som den samlede region).
Du kan sortere forhandlerne baseret på salgstallene ved at følge nedenstående trin:
- Højreklik på en celle, der har salgsværdien for enhver forhandler.
- Gå til Sorter -> Sorter størst til mindste.
Dette ville øjeblikkeligt sortere alle detailhandlere efter salgsværdien.
Nu kan du hurtigt scanne gennem Syd -regionen og identificere, at salget af Home Depot var 3004600, og det klarede sig bedre end fire detailhandlere i Syd -regionen.
Nu er der mere end én måde at huden katten på. Du kan også placere regionen i filterområdet og derefter kun vælge den sydlige region.
Klik her for at downloade eksempeldata.
Jeg håber, at denne vejledning giver dig et grundlæggende overblik over Excel -pivottabeller og hjælper dig med at komme i gang med det.
Her er nogle flere Pivot Table Tutorials, du måske kan lide:
- Klargøring af kildedata til pivottabel.
- Sådan anvendes betinget formatering i en pivottabel i Excel.
- Sådan grupperes datoer i pivottabeller i Excel.
- Sådan grupperes tal i pivottabel i Excel.
- Sådan filtreres data i en pivottabel i Excel.
- Brug af skiver i Excel -pivottabel.
- Sådan udskiftes tomme celler med nuller i Excel -pivottabeller.
- Sådan tilføjes og bruges en Excel -pivottabel Beregnede felter.
- Sådan opdateres pivottabel i Excel.
