Pivottabellerne er en af de mest kraftfulde funktioner i excel. Selvom du er nybegynder, kan du knuse store mængder data til nyttige oplysninger. Pivottabel kan hjælpe dig med at lave rapporter på få minutter. Analyser dine rene data let, og hvis dataene ikke er rene, kan det hjælpe dig med at rense dine data. Jeg vil ikke kede dig, så lad os springe ud i det og udforske.
Sådan oprettes en pivottabel
Det er simpelt. Vælg bare dine data. Gå til Indsæt. Klik på pivottabellen, og det er færdigt.
Men vent. Inden du opretter en pivottabel, skal du sikre dig, at alle kolonner har en overskrift.
Hvis en kolonneoverskrift efterlades tom, vil pivottabellen ikke blive oprettet og vil gå igennem en fejlmeddelelse.
Krav 1: Alle kolonner skal have en overskrift for at komme i gang med pivottabeller i Excel
Du bør have dine data organiseret med den korrekte overskrift. Når du har det, kan du indsætte pivottabellen.
Indsæt pivottabellen fra bånd
Følg disse trin for at indsætte en pivottabel fra menuen:
1. Vælg dit dataområde
2. Gå til fanen Indsæt
3. Klik på ikonet Pivot Table
4. Opret en pivottabel, der vises
5. Her kan du se det dataområde, du har valgt. Hvis du mener, at dette ikke er det område, du vil vælge, skal du direkte ændre størrelsen på dit område herfra i stedet for at gå tilbage og vælge data igen.
6. Dernæst kan du vælge, hvor du vil have din pivottabel. Jeg anbefaler at bruge det nye regneark, men du kan også bruge det aktuelle regneark. Du skal bare definere placeringen i placeringsboksen.
7. Hvis du nu er færdig med indstillingerne, skal du trykke på knappen OK. Du får dit pivottabel i et nyt ark. Vælg bare dine felter til resuméer. Vi vil se, hvordan vi opretter en oversigt over data ved hjælp af pivottabellen, men lad os først gøre det grundlæggende klart. I denne excel -pivottabel -tutorial lærer du mere, end du forventer.
Indsæt genvej til pivottabel (Alt> N> V)
Dette er en sekventiel tastaturgenvej for at åbne Opret Pivottabel valgfelt.
Tryk på Alt -knappen, og slip den. Hit N og slip den. Hit V og slip den. Opret en Pivot Table -indstillingsboks åbnes.
Følg nu bare ovenstående procedure for at oprette et pivottabel i excel.
Indsæt genvej til pivottabel ved hjælp af gammel Excel -genvej (Alt> D> P)
En ting jeg bedst kan lide ved Microsoft Excel er, at de i hver ny version af Excel introducerer nye funktioner og men de kasserer ikke de gamle funktioner (som MS gjorde med sejr 8. Det var ynkeligt). Det her giver den ældre bruger mulighed for at arbejde normalt på nye versioner, som de plejede at arbejde på ældre versioner.
Hvis du trykker på i rækkefølge ALT, D og P på tastaturet åbnes Excel for at oprette en pivottabelguide.
Vælg den relevante indstilling. Den valgte indstilling i ovenstående skærmbillede vil føre os til at oprette en pivottabel, som vi har oprettet før.
Tryk på Enter, eller klik på Næste, hvis du vil kontrollere dit valgte område.
Hit Enter igen.
Vælg et nyt regneark, eller hvor du vil have din pivottabel til at trykke på Enter. Og det er gjort.
Opret rapporter ved hjælp af pivottabeller.
Nu ved du, hvordan du indsætter et pivottabel. Lad os gøre klar til at lave rapporter ved hjælp af pivottabeller på få minutter.
Vi har dataene for den stationære ordre.
Kolonnefelterne er:
Bestillingsdato: Dato for ordre (Naturligvis)
Område: Ordensregionen i landet
Kunde: Kundens navn (hvad kan det ellers være)
Vare: Bestilt vare
Enhed: Antal enheder af en bestilt vare
Enhedspris: Prisen pr. Enhed
I alt: Samlede omkostninger ved ordren (enhed*enhedsomkostninger).
Drejearealer: Disse 4 områder bruges til at vise dine data på en måde.
FILTRE: Sæt felter her, hvorfra du gerne vil anvende filtre i din rapport.
KOLONNER: Sæt de felter, du ønsker her, i dine kolonner i rapporten: (Det er bedre at vise end at forklare)
RÆKKER: Træk felter, som du vil vise råt, som på billedet ovenfor, jeg har vist Region i RÆKER.
VÆRDIER: Vælg et felt for at få tælle, sum, gennemsnit, procent (og mange flere) osv., Du vil se.
Nu ved hjælp af ovenstående oplysninger har vi udarbejdet denne hurtige pivot -rapport, der viser fra hvilken region, hvor mange ordrer der er placeret for hver vare.
Nu hvor du forstår dine data og Pivot -felter (du er trods alt smart), lad os besvare nogle spørgsmål relateret til disse data ved hjælp af pivottabellen hurtigt.
Q1. Hvor mange ordrer er der?
Pivottabellen er oprindeligt tom, som vist på billedet herunder.
Du skal vælge felter (kolonnenavne) i relevante områder for at se resuméet eller detaljerne for dette felt.
Nu, for at besvare ovenstående spørgsmål, vælger jeg Vare (vælg en hvilken som helst kolonne, bare sørg for, at den ikke har en tom celle imellem) i værdifelter.
Vælg Vare fra feltlisten, og træk den ind i feltet Værdi.
Vi har vores svar. Pivottabeller fortæller mig, at der er i alt 43 ordrer. Det er rigtigt.
Pro tip: Du bør kontrollere dine data, hvis de er korrekte eller ej. Hvis dette tal ikke matcher data, betyder det, at du enten har valgt et forkert område, eller at feltet har tomme celler.
Info: Værdifeltet tæller som standard antallet af poster i en kolonne, hvis det indeholder tekst og summer, hvis feltet kun indeholder værdier. Du kan ændre dette i værdifeltindstillinger. Hvordan? Vi ses senere i denne Pivot Table Tutorial.
Nu hvor jeg har valgt Bestillingsdato i Valuesarea, viser det sig 42. Dette midler Bestillingsdato har en tom celle siden vi ved, at det samlede antal ordrer er 43.
Identificer uregelmæssige data ved hjælp af pivottabeller og rengør det.
Pivottabel kan hjælpe dig med at finde forkerte oplysninger i dataene.
Det meste af tiden udarbejdes vores data af dataindtastningsoperatører eller af brugere, som normalt er uregelmæssige og har brug for lidt rengøring for at udarbejde nøjagtig rapport og analyse.
Du bør altid rense og forberede dine data på en måde, før du udarbejder rapporter. Men nogle gange først efter at have udarbejdet rapporten får vi at vide, at vores data har en vis uregelmæssighed. Kom, jeg viser dig hvordan …
Q2: Fortæl antal ordrer fra hver region
Nu for at besvare dette spørgsmål:
Vælg Område og træk den til Rækker Område og Vare til Værdier Areal.
Vi får en Region-Wise opdelte data. Vi kan svare fra hvilken region, hvor mange ordrer der er kommet.
Der er i alt 4 regioner i vores data i henhold til pivottabellen. Men vent, læg mærke til det Central og Centrere område. Vi ved, at Centrle burde være det Central. Der er en uregelmæssighed. Vi skal gå til vores data og lave datarensning.
For at rense data i regionsfeltet filtrerer vi det forkerte regionsnavn (Centrle) ud og retter det (Central).
Gå nu tilbage til dine pivotdata.
Højreklik hvor som helst på pivottabellen, og klik på Opdater.
Din rapport er nu blevet opdateret.
INFO: Uanset hvilke ændringer du foretager i dine kildedata, vil pivottabellen fortsætte med at arbejde med gamle data indtil du opdaterer det. Excel opretter en pivot -cache, og en pivottabel kører på den cache. Når den opdateres, ændres den gamle cache med friske data.
Nu kan du se, at der faktisk kun er 3 regioner.
Pivot -rapportformatering med kategoriserede rækker.
Nogle gange har du brug for rapporter som ovenstående billede. Dette gør det let at se dine data struktureret. Du kan nemt fortælle fra hvilken region, hvor mange varer der er bestilt. Lad os se, hvordan du kan gøre dette.
Bevæge sig Område og Vare til RÆKKER areal. Sørg for, at regionen er øverst og emner i bunden som vist på billedet.
Træk Vare til Værdi Areal.
Som et resultat får du denne rapport.
Det vil gøre. Men nogle gange vil din chef rapportere i tabelform uden subtotaler. For at gøre dette skal vi formatere vores pivottabel.
Fjern delsummer fra pivottabellen
Følg disse trin:
1. Klik hvor som helst på dit pivottabel.
2. Gå til Design Tab.
3. Klik på menuen Subtotals.
4. Klik på Vis ikke delsummer.
Du kan se, at der ikke er nogen subtotaler nu.
Bøde. Men det er stadig ikke i tabelform. Regioner og elementer vises i en enkelt kolonne. Vis dem separat.
Lav en pivottabel i tabelform
Følg disse trin for at vise regioner og elementer i forskellige kolonner:
1. Klik hvor som helst på pivottabellen
2. Gå til fanen Design
3. Klik på Rapportlayout.
4. Klik på Vis for tabelformen mulighed. Endelig vil du få denne sofistikerede opfattelse af din rapport.
Nu kender vi det samlede antal ordrer, der er placeret for hver vare fra hver region. Det er vist i greven af varer kolonner.
Skift kolonnens navn til Ordre:% s.
Det ser bedre ud nu.
Q3: Hvor mange enheder af hver vare er bestilt?
For at besvare dette spørgsmål har vi brug for en sum af enheder. For at gøre det skal du bare flytte feltet Enheder til Værdier. Det opsummerer automatisk antallet af enheder for hvert element. Hvis en kolonne i pivottabellen kun indeholder værdier, viser pivottabellen som standard summen af disse værdier. Men det kan ændres fra en værdifeltindstilling. Hvordan? Jeg viser dig det sidste.
Indstillinger for pivottabelværdi
Q4: Gennemsnitsprisen for hver vare?
I vores eksempeldata er prisen på en vare forskellig for forskellige ordrer. Se f.eks. Bindemidler.
Jeg vil gerne vide de gennemsnitlige omkostninger for hver vare i pivottabellen. For at finde ud af dette skal du trække enhedsomkostninger til værdifelt. Det viser summen af enhedsomkostninger.
Vi vil ikke Summen af enhedsomkostninger, vi vil have Gennemsnit af enhedsomkostninger. For at gøre det…
- Højreklik et vilkårligt sted på summen af kolonnen Enhedsomkostninger på pivottabellen
- Klik på Værdifeltindstillinger
- Baseret på de tilgængelige muligheder, Vælg Gennemsnit og tryk OK.
Endelig har du denne pivotrapport:
Pivottabel Beregnede felter
En af de mest nyttige funktioner i pivottabellen er dens beregnede felter. Beregnede felter er felter, der opnås ved nogle operationer på tilgængelige kolonner.
Lad os forstå, hvordan du indsætter beregnede felter i pivottabellen med et eksempel:
Baseret på vores data udarbejdede vi denne rapport.
Her har vi Summen af enheder og Udgifter i alt. Jeg har lige flyttet den samlede kolonne til værdier -feltet og derefter omdøbt den til Udgifter i alt. Nu vil jeg vide den gennemsnitlige pris på en enhed for hver vare for hver region. Og det ville være:
Gennemsnitpris = Samlet pris / samlede enheder
Lad os indsætte et felt i pivottabellen, der viser gennemsnitsprisen for hver artikelområde:
Følg disse trin for at indsætte et beregnet felt i pivottabellen
1. Klik hvor som helst på pivottabellen og gå til Fanen Analyser
2. Klik på Felter, varer og sæt i beregningsgruppe.
3. Klik på Beregnede felter.
Du vil se denne inputboks til dit beregningsfelt:
4. Skriv gennemsnittet i navnefeltet Koste eller noget du kan lide, excel vil ikke have noget imod. I formel indtastningsboks, skriv og tryk Okay.
= I alt / enheder
Du kan skrive dette manuelt fra tastaturet, eller du kan dobbeltklikke på de feltnavne, der er angivet i feltet Felt for at udføre handlinger.