Pivottabeller er en av de mest kraftfulle funksjonene i excel. Selv om du er en nybegynner, kan du knuse store mengder data til nyttig informasjon. Pivottabell kan hjelpe deg med å lage rapporter på få minutter. Analyser dine rene data enkelt, og hvis dataene ikke er rene, kan det hjelpe deg å rense dataene dine. Jeg vil ikke kjede deg, så la oss hoppe inn i det og utforske.
Hvordan lage et pivottabell
Det er enkelt. Bare velg dataene dine. Gå til Sett inn. Klikk på pivottabellen og det er gjort.
Men vent. Før du lager en pivottabell, må du kontrollere at alle kolonnene har en overskrift.
Hvis en kolonneoverskrift er tom, vil ikke pivottabellen bli opprettet og vil gå gjennom en feilmelding.
Krav 1: Alle kolonner bør ha en overskrift for å komme i gang med pivottabeller i Excel
Du bør ha dataene dine organisert med riktig overskrift. Når du har det, kan du sette inn pivottabellen.
Sett inn pivottabellen fra båndet
Følg disse trinnene for å sette inn et pivottabell fra menyen:
1. Velg dataområdet
2. Gå til kategorien Sett inn
3. Klikk på Pivot Table -ikonet.
4. Opprett en Pivot Table -alternativboks.
5. Her kan du se dataområdet du valgte. Hvis du tror at dette ikke er området du vil velge, kan du endre størrelsen på området herfra i stedet for å gå tilbake og velge data igjen.
6. Deretter kan du velge hvor du vil ha pivottabellen. Jeg anbefaler å bruke det nye regnearket, men du kan også bruke det gjeldende regnearket. Bare definer plasseringen i boksen Sted.
7. Hvis du er ferdig med innstillingene, trykker du på OK -knappen. Du får pivottabellen i et nytt ark. Bare velg feltene dine for sammendrag. Vi vil se hvordan vi lager et sammendrag av data ved hjelp av pivottabellen, men la oss først gjøre det grunnleggende klart. I denne excel -pivottabellopplæringen lærer du mer enn du forventer.
Sett inn snarvei for pivottabell (Alt> N> V)
Dette er en sekvensiell hurtigtast for å åpne Opprett Pivottabell alternativboks.
Trykk på Alt -knappen og slipp den. Truffet N og slipp den. Truffet V og slipp den. Opprett en Pivot Table -alternativboks.
Følg nå fremgangsmåten ovenfor for å lage et pivottabell i excel.
Sett inn pivottabellgenvei ved hjelp av gammel Excel -snarvei (Alt> D> P)
En ting jeg liker best med Microsoft Excel er at de i hver ny versjon av Excel introduserer nye funksjoner, men de ikke kast de gamle funksjonene (som MS gjorde med seier 8. Det var patetisk). Dette lar de gamle brukerne jobbe normalt med nye versjoner slik de pleide å jobbe med eldre versjoner.
Hvis du trykker sekvensielt ALT, D og P på tastaturet, åpnes Excel for å lage en pivottabellveiviser.
Velg det riktige alternativet. Det valgte alternativet i skjermbildet ovenfor vil føre oss til å lage et pivottabell som vi opprettet før.
Trykk Enter eller klikk på Neste hvis du vil kontrollere det valgte området.
Trykk Enter igjen.
Velg et nytt regneark eller hvor du vil at pivottabellen skal trykke Enter. Og det er gjort.
Lag rapporter ved hjelp av pivottabeller
Nå vet du hvordan du setter inn et pivottabell. La oss forberede oss på å lage rapporter ved hjelp av pivottabeller på få minutter.
Vi har dataene for den stasjonære ordren.
Kolonnefeltene er:
Bestillingsdato: Bestillingsdato (åpenbart)
Region: Ordensregionen i landet
Kunde: Navn på kunde (hva annet kan det være)
Punkt: Bestilt vare
Enhet: Antall enheter av en bestilt vare
Enhetskostnad: Kostnad per enhet
Total: Total kostnad for bestillingen (enhet*enhetskostnad).
For å lage rapporter ved hjelp av pivottabeller skal vi bruke
Pivottabellfelt: Inneholder listen over navn på kolonner i dataene dine.
Dreieområder: Disse fire områdene brukes til å vise dataene dine på en måte.
FILTER: Sett inn feltene du vil bruke filtre fra i rapporten din.
KOLONNER: Sett inn feltene du ønsker i kolonnene i rapporten: (Det er bedre å vise enn å forklare)
RADER: Dra feltene du vil vise råmessig som på bildet ovenfor, jeg har vist Region i RADER.
VERDIER: Velg et felt for å få Count, Sum, Average, Procent (og mange flere) etc. du vil se.
Ved å bruke informasjonen ovenfor har vi utarbeidet denne raske pivotrapporten som viser fra hvilken region hvor mange bestillinger som er lagt for hver vare.
Nå som du forstår data- og Pivot -feltene dine (tross alt du er smart), la oss svare på noen spørsmål knyttet til disse dataene ved å bruke pivottabellen raskt.
Q1. Hvor mange bestillinger er det?
Pivottabellen er opprinnelig tom, som vist på bildet nedenfor.
Du må velge felt (kolonnenavn) i passende områder for å se sammendraget eller detaljene for det feltet.
For å svare på spørsmålet ovenfor, vil jeg velge element (velg hvilken som helst kolonne, bare pass på at den ikke har en tom celle mellom) i verdifelt.
Velg Element fra feltlisten og dra det inn i Verdifeltet.
Vi har vårt svar. Pivottabeller forteller meg at det er totalt 43 bestillinger. Dette er riktig.
Nå som jeg har valgt Bestillingsdato i Verdier -området, viser det 42. Dette midler Bestillingsdato har en tom celle siden vi vet at det totale antallet bestillinger er 43.
Identifiser uregelmessige data ved hjelp av pivottabeller og rengjør dem.
Pivottabell kan hjelpe deg med å finne feil informasjon i dataene.
Mesteparten av tiden blir dataene våre utarbeidet av dataoppføringsoperatører eller av brukere som vanligvis er uregelmessige og trenger litt rengjøring for å utarbeide nøyaktig rapport og analyse.
Du bør alltid rense og forberede dataene dine på en måte før du utarbeider rapporter. Men noen ganger først etter å ha utarbeidet rapporten, får vi vite at dataene våre har en viss uregelmessighet. Kom, jeg skal vise deg hvordan …
Q2: Fortell antall bestillinger fra hver region
Nå for å svare på dette spørsmålet:
Plukke ut Region og dra den til Rader Areal og Punkt til Verdier Område.
Vi vil få en Region-Wise-delt data. Vi kan svare fra hvilken region hvor mange bestillinger som har kommet.
Det er totalt 4 regioner i dataene våre i henhold til pivottabellen. Men vent, legg merke til det Sentralt og sentralt region. Vi vet at Centrle burde være det Sentral. Det er en uregelmessighet. Vi må gå til dataene våre og gjøre datarensing.
For å rense data i regionfeltet filtrerer vi ut feil regionnavn (Centrle) og korrigerer det (Central).
Gå tilbake til pivotdataene dine.
Høyreklikk hvor som helst på pivottabellen og klikk på Refresh.
Rapporten din er nå oppdatert.
Nå kan du se at det bare er tre regioner.
Pivot -rapportformatering med kategoriserte rader.
Noen ganger trenger du rapporter som bildet ovenfor. Dette gjør det enkelt å se dataene dine på en strukturert måte. Du kan enkelt fortelle fra hvilken region hvor mange varer som er bestilt. La oss se hvordan du kan gjøre dette.
Bevege seg Region og Punkt til RADER område. Sørg for at regionen er øverst og elementene nederst som vist på bildet.
Dra Punkt til Verdi Område.
Som et resultat vil du få denne rapporten.
Det vil gjøre. Men noen ganger vil sjefen din rapportere i tabellform uten delsummer. For å gjøre dette må vi formatere vårt pivottabell.
Fjern delsummer fra pivottabellen
Følg disse trinnene:
1. Klikk hvor som helst på pivottabellen.
2. Gå til Design Tab.
3. Klikk på delsummen Meny.
4. Klikk på Ikke vis delsummer.
Du kan se at det ikke er noen delsummer nå.
Fint. Men den er fremdeles ikke i tabellform. Regioner og elementer vises i en enkelt kolonne. Vis dem separat.
Lag en pivottabell i tabellform
Følg disse trinnene for å vise regioner og elementer i forskjellige kolonner:
1. Klikk hvor som helst på pivottabellen
2. Gå til kategorien Design
3. Klikk på Rapportoppsett.
4. Klikk på Vis for alternativet tabellform. Til slutt vil du få denne sofistikerte oversikten over rapporten din.
Nå vet vi det totale antallet bestillinger som er lagt for hver vare fra hver region. Det er vist i greven av elementkolonner.
Endre kolonnenavnet til Ordrene.
Det ser bedre ut nå.
Q3: Hvor mange enheter av hver vare er bestilt?
For å svare på dette spørsmålet trenger vi en sum av enheter. For å gjøre det, bare flytt feltet Enheter til Verdier. Det vil automatisk oppsummere antall enheter for hvert element. Hvis en kolonne i pivottabellen bare inneholder verdier, viser pivottabellen som standard summen av disse verdiene. Men den kan endres fra en verdifeltinnstilling. Hvordan? Jeg viser deg det siste.
Pivottabell Verdi Feltinnstillinger
Q4: Gjennomsnittsprisen på hver vare?
I våre eksempeldata er prisen på en vare forskjellig for forskjellige bestillinger. Se for eksempel bindemidler.
Jeg vil vite gjennomsnittskostnaden for hvert element i pivottabellen. For å finne ut av dette, dra Enhetskostnad til Verdifelt. Det vil vise summen av enhetskostnad.
Vi vil ikke Sum enhetskostnad, vi vil Gjennomsnitt av enhetskostnad. Å gjøre slik…
1. Høyreklikk hvor som helst på summen av Enhetskostnad -kolonnen på pivottabellen
2. Klikk Verdifeltinnstillinger
3. Basert på tilgjengelige alternativer, plukke ut Gjennomsnitt og trykk OK.
Til slutt vil du ha denne pivotrapporten:
Pivottabell Beregnede felt
En av de mest nyttige funksjonene i pivottabellen er dens beregnede felt. Beregnede felt er felt som er hentet ved noen operasjoner på tilgjengelige kolonner.
La oss forstå hvordan du setter inn beregnede felt i pivottabellen med ett eksempel:
Basert på våre data utarbeidet vi denne rapporten.
Her har vi Summen av enheter og Totalkostnad. Jeg har nettopp flyttet den totale kolonnen til Verdier -feltet og deretter omdøpt den til Totalkostnad. Nå vil jeg vite gjennomsnittsprisen på en enhet for hver vare for hver region. Og det ville være:
Gjennomsnittlig pris = totalkostnad / totale enheter
La oss sette inn et felt i pivottabellen som viser gjennomsnittsprisen for hvert element regionvis:
Følg disse trinnene for å sette inn et beregnet felt i pivottabellen
1. Klikk hvor som helst på pivottabellen og gå til Analyser -fanen
2. Klikk på Felt, elementer og sett i beregningsgruppen.
3. Klikk på Beregnede felt.
Du vil se denne inndataboksen for beregningsfeltet:
4. Skriv inn gjennomsnittskostnaden eller noe du liker i navnefeltet, excel vil ikke ha noe imot det. I formelinnmatingsboksen skriver du og klikker OK.
= Totalt / enheter
Du kan skrive dette manuelt fra tastaturet, eller du kan dobbeltklikke feltnavnene som er oppført i feltet Felt for å utføre operasjoner.
5. Du har lagt det beregnede feltet ditt nå til pivottabellen. Den er oppkalt som summen av gjennomsnittlig kostnad, men det er ikke en sum. Excel kjører bare standardfunksjonen for å navngi kolonnen (som et ritual). Gi nytt navn til denne kolonnen og begrense desimalsifrene som vises.
Og der har du et beregnet felt. Du kan gjøre det så komplekst du vil. For et eksempels skyld tok jeg denne enkle gjennomsnittsoperasjonen.
Gruppering i pivottabellen
Du har denne pivotrapporten utarbeidet.
Nå vil jeg at denne rapporten skal deles årlig. Se øyeblikksbildet nedenfor.
Vi har en kolonne om bestillingsdato. Flytt feltet OrderDate til Rader øverst.
Det ligner ingenting på den nødvendige rapporten. Vi må ha gruppedatoer årlig.
Følg disse trinnene for å gruppere et felt i Excel -pivottabell:
1. Høyreklikk på feltet du vil gruppere.
2. Klikk på Gruppe. Du vil ha denne alternativboksen for tilpasning. Siden dette er en datakolonne, viser Excel oss grupperingen tilsvarende. Du kan velge start- og sluttdato.
3. Velg i årevis og trykk OK. Du har gruppert årlig i pivottabellen i Excel.
Skiver av pivottabell
Skiver ble introdusert i Excel 2010 som et tillegg. I Excel 2013 og 2016 er den tilgjengelig som standard, akkurat som filtre.
Skiver er ingenting annet enn filtre. I motsetning til filtre viser Slicers alle tilgjengelige alternativer rett foran deg. Det gjør dashbordet mer interaktivt.
Slik legger du til skiver i pivottabell, Excel 2016 og 2013
Pivot Table Slicers er lett å legge til. Følg disse trinnene:
1. Klikk hvor som helst på pivottabellen og gå til kategorien Analyser.
2. Klikk på Sett inn skiver. Du vil ha en liste over felt for dataene dine. Velg så mange du vil.
3. For dette eksempelet, velg Region og trykk OK.
Du har lagt til Slicer i rapporten din. Påfør nå filteret med bare ett klikk.
Sett inn tidslinjen i Excel 2016 og 2013
Dette er en av mine favorittfunksjoner i Excel -pivottabeller. Denne nye funksjonaliteten fungerer bare med datoer. Ved å bruke dette kan du visuelt velge et tidsrom for å filtrere dataene dine.
Følg disse trinnene for å sette inn en tidslinje -forbannelse:
Slik legger du til tidslinje i pivottabell, Excel 2016 og 2013
1. Klikk hvor som helst på pivottabellen og gå til kategorien Analyser.
2. Klikk på Sett inn tidslinje fra filtergruppe. Alle kolonner som inneholder tidsverdier i hentede data, vil bli oppført i en alternativboks å velge mellom. Her har vi bare en. Så ja…
2. Velg alternativene og trykk Enter eller klikk OK. Det er gjort, og du har tidslinjen til pivottabellen rett foran deg.
Du kan velge å vise det daglig, månedlig, kvartalsvis eller årlig. Jeg har valgt Månedlig her.
I denne artikkelen har jeg dekket de viktigste og nyttige funksjonene i pivottabellen. Vi utforsket nye funksjoner i Pivot Table i Excel 2016 og 2013. Vi lærte om den klassiske bruken av et pivottabell som ble kjørt i Excel 2007, 2010 og eldre. De er fortsatt nyttige. Hvis du ikke fant svaret ditt knyttet til pivottabellen din her, kan du spørre i kommentarfeltet.
Det er mange andre funksjoner som ennå ikke skal forklares. Vi lærer avansert pivottabellfunksjon i neste artikkel. Inntil da Excel på alt.