Pivottabeller 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 tror, at dette ikke er det område, du vil vælge, skal du æ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 feltet Placering.
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 til 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, men de kassér ikke de gamle funktioner (som MS gjorde med sejr 8. Det var ynkeligt). Det her giver de gamle brugere 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 åbner Excel, og opret 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: Bestillingsdato (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).
For at oprette rapporter ved hjælp af pivottabeller vil vi bruge
Pivottabelfelter: Indeholder listen over navne på kolonner i dine data.
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.
For at besvare ovenstående spørgsmål vil jeg vælge Item (vælg en hvilken som helst kolonne, bare sørg for, at den ikke har en tom celle imellem) i værdifelter.
Vælg element på feltlisten, og træk det ind i feltet Værdi.
Vi har vores svar. Pivottabeller fortæller mig, at der er i alt 43 ordrer. Det er rigtigt.
Nu hvor jeg har valgt Bestillingsdato i området Værdier, viser det 42. Det her 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 Centrle 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.
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å delsummen menu.
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 indstillingen Tabularform. 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 varekolonner.
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…
1. Højreklik et vilkårligt sted på summen af kolonnen Enhedsomkostninger på pivottabellen
2. Klik på Værdifeltindstillinger
3. 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 et vilkårligt sted på pivottabellen og gå til fanen Analyse
2. Klik på Felter, poster og sæt i beregningsgruppen.
3. Klik på Beregnede felter.
Du vil se denne inputboks til dit beregningsfelt:
4. Skriv gennemsnitsomkostningen eller noget, du kan lide, i navneindtastningsboksen, excel vil ikke have noget imod det. I formelindtastningsboksen skal du skrive og trykke på OK.
= 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.
5. Du har nu dit beregnede felt tilføjet til din pivottabel. Det er navngivet som summen af gennemsnitlige omkostninger, men det er ikke en sum. Excel kører bare standardfunktionen for at navngive kolonnen (som et ritual). Omdøb denne kolonne, og begræns de viste decimalcifre.
Og der har du et beregnet felt. Du kan gøre det så komplekst, som du vil. Af hensyn til et eksempel tog jeg denne enkle gennemsnitlige operation.
Gruppering i pivottabellen
Du har denne pivotrapport udarbejdet.
Nu vil jeg have, at denne rapport deles op årligt. Se snapshotet herunder.
Vi har en kolonne om ordredato. Flyt feltet OrderDate til rækker øverst.
Det ligner ikke den påkrævede rapport. Vi skal have gruppedatoer årligt.
Følg disse trin for at gruppere et felt i Excel -pivottabel:
1. Højreklik på det felt, du vil gruppere.
2. Klik på Gruppe. Du har denne indstillingsboks til tilpasning. Da dette er en datakolonne, viser Excel os grupperingen i overensstemmelse hermed. Du kan vælge din start- og slutdato.
3. Vælg i årevis, og tryk på OK. Du har foretaget årlig gruppering i pivottabellen i Excel.
Udsnit af drejebord
Skiver blev introduceret i Excel 2010 som et tilføjelsesprogram. I Excel 2013 og 2016 er den tilgængelig som standard ligesom filtre.
Slicers er intet andet end filtre. I modsætning til filtre viser Slicers alle tilgængelige muligheder lige foran dig. Det gør dit dashboard mere interaktivt.
Sådan tilføjes udsnit i pivottabel, Excel 2016 og 2013
Pivot Table Slicers er let at tilføje. Følg disse trin:
1. Klik et vilkårligt sted på pivottabellen og gå til fanen Analyser.
2. Klik på Insert Slicer. Du vil have en liste over felter til dine data. Vælg så mange du vil.
3. I dette eksempel skal du vælge Region og trykke på OK.
Du har føjet Slicer til din rapport. Anvend nu filteret med et enkelt klik.
Indsæt tidslinje i Excel 2016 og 2013
Dette er en af mine foretrukne Excel -pivottabeller med funktionalitet. Denne nye funktionalitet fungerer kun med datoer. Ved hjælp af dette kan du visuelt vælge et tidsrum for at filtrere dine data.
Følg disse trin for at indsætte en tidslinje -forbandelse:
Sådan tilføjes tidslinje i pivottabel, Excel 2016 og 2013
1. Klik et vilkårligt sted på pivottabellen, og gå til fanen Analyser.
2. Klik på Indsæt tidslinje fra filtergruppe. Alle kolonner, der indeholder tidsværdier i hentede data, vil blive angivet i en indstillingsboks at vælge imellem. Her har vi kun en. Så ja…
2. Vælg dine valgmuligheder, og tryk på Enter, eller klik på OK. Det er gjort, og du har din pivottabel tidslinje lige foran dig.
Du kan vælge at vise det dagligt, månedligt, kvartalsvis eller årligt. Jeg har valgt Monthly her.
I denne artikel har jeg dækket de vigtigste og nyttige funktioner i pivottabellen. Vi udforskede nye funktioner i Pivot Table i Excel 2016 og 2013. Vi lærte om den klassiske brug af en pivottabel, der blev kørt i Excel 2007, 2010 og ældre. De er stadig nyttige. Hvis du ikke fandt dit svar relateret til din pivottabel her, så spørg i kommentarfeltet.
Der er mange andre funktioner, der endnu ikke skal forklares. Vi lærer avanceret Pivot Table -funktion i næste artikel. Indtil da Excel på alt.