Pivottabellene 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 posisjonsboksen.
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 -pivottabellen vil du lære mer enn du forventer.
Sett inn snarvei for pivottabell (Alt> N> V)
Dette er en sekvensiell hurtigtast for å åpne Create 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 og men de kaster ikke de gamle funksjonene (som MS gjorde med seier 8. Det var patetisk). Dette lar den eldre brukeren arbeide normalt med nye versjoner slik de pleide å jobbe med eldre versjoner.
Hvis du trykker sekvensielt ALT, D og P på tastaturet, åpnes Excel for å opprette 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: Dato for bestilling (Å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).
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 vil ha 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.
Nå, for å svare på spørsmålet ovenfor, vil jeg velge Punkt (velg hvilken som helst kolonne, bare pass på at den ikke har en tom celle mellom) i verdifelt.
Plukke ut Punkt fra feltlisten og dra den til Verdi -feltet.
Vi har vårt svar. Pivottabeller forteller meg at det er totalt 43 bestillinger. Dette er riktig.
Profftips: Du bør sjekke dataene dine hvis de er riktige eller ikke. Hvis dette tallet ikke samsvarer med data, betyr det at du enten har valgt et feil område eller at feltet har tomme celler.
Info: Verdifeltet teller som standard antall oppføringer i en kolonne hvis det inneholder tekst og summer hvis feltet bare inneholder verdier. Du kan endre dette i verdifeltinnstillinger. Hvordan? Vi ses senere i denne opplæringen for pivottabeller.
Nå som jeg har valgt Bestillingsdato i Valuesarea, viser det seg 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 Sentral 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.
INFO: Uansett hvilke endringer du gjør i kildedataene, vil pivottabellen fortsette å jobbe med gamle data til du oppdaterer den. Excel oppretter en pivotbuffer, og en pivottabell kjøres på den bufferen. Når den oppdateres, endres den gamle cachen med friske data.
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å Subtotals -menyen.
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
For å vise regioner og elementer i forskjellige kolonner, følg disse trinnene:
1. Klikk hvor som helst på pivottabellen
2. Gå til kategorien Design
3. Klikk på Rapportoppsett.
4. Klikk på Vis for tabellformen alternativ. 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 varer kolonner.
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…
- Høyreklikk hvor som helst på summen av Enhetskostnad -kolonnen på pivottabellen
- Klikk Verdifeltinnstillinger
- Basert på de tilgjengelige alternativene, 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 Analyse Tab
2. Klikk på Felt, varer og sett i beregningsgruppen.
3. Klikk på Beregnede felt.
Du vil se denne inndataboksen for beregningsfeltet:
4. Skriv gjennomsnittet i boksen navn Koste eller noe du liker, excel vil ikke ha noe imot det. I formel skriv inn, og trykk 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.