Hvordan bygge en pivottabell i Excel. Hvordan jobbe med data
MS Excel Pivot Table er et kraftig dataanalyseverktøy. Dette verktøyet lar deg trekke ut informasjon fra et stort utvalg av data, i enhver sammenheng, med bare noen få museklikk. I denne artikkelen vil jeg prøve å forklare på et enkelt og forståelig språk, trinn for trinn, hva og hvordan du skal gjøre for å få dette ekstremt praktiske MS Excel-verktøyet til din disposisjon.
Krav til kildedata.
Så grunnlaget for enhver pivottabell er en riktig konstruert rekke data - den "riktige tabellen". I figuren nedenfor kan du se et eksempel på en korrekt utformet datamatrise:
La oss se på hva som faktisk er "riktigheten" til denne tabellen? Korrektheten er at:
- Hver kolonne inneholder bare samme type data, kolonnen EN bare datoer, i kolonne I kun dokumenter, i kolonnen MED kun kunder, Money in Money, Leverandører i Leverandører, Produktkategorier i kategorier og så videre... I en slik tabell kan vi veldig enkelt bruke Filter;
- Kolonner med samme type data gjentas ikke;
- Det er ingen totale rader i tabellen, bare "rene data";
- Det er ingen tomme celler i tekstdataene i tabellen; hver linje inneholder navnet på kunden, produktet, leverandøren, lederen og så videre...
Forresten, i en matrise spiller det ingen rolle i hvilken rekkefølge kolonnene er plassert og etter hvilket felt dataene er sortert. Dette påvirker ikke konstruksjonen av pivottabellen på noen måte.
Og her er et eksempel på en "feil tabell", som, selv om du sprekker, ikke vil bygge pivottabellen, og selv om den er bygget, vil det være helt umulig å jobbe med dataene ... Vår " favoritt" regnskapssystemer "behager" oss vanligvis med slike tabeller, som gir oss dem i form av rapporter som er fullstendig uegnet for videre analyse:
Her er et annet eksempel på "Invalid Array":
Det er en hel haug med "uregelmessigheter" her:
- For det første har kolonnene "Leverandør" og "Kategori" tomme celler, så vi kan ikke bruke filteret;
- For det andre, i nesten hver kolonne er det linjer "Totalt...", de er helt unødvendige for å bygge pivottabellen, dessuten vil de bare komme i veien;
- For det tredje er kolonnen "Grand Total" heller ikke nødvendig;
- For det fjerde er den samme typen data, nemlig "Money", i så mange som tre kolonner: "Jan", "Feb" og "Mar", noe som vil komplisere konstruksjonen av pivottabellen betydelig, noe som betyr at vi må gjør noe med det...
Men dette er alt for en egen samtale, hvis du vil lære hvordan du raskt konverterer slike "skjeve tabeller" til "riktige arrays", les artiklene: "Hvordan bygger du raskt en pivottabell fra en 1C- eller SAP-rapport?" og "Hvordan konverterer du raskt en tabell til en matrise for pivottabeller?"
Faktisk, å bygge pivottabellen:
Ta den "riktige matrisen", plasser markøren i en hvilken som helst celle i matrisen, velg "Sett inn" -fanen i hovedmenyen, i venstre hjørne, i "Tabeller" -delen, klikk på "Pivottabell" -knappen:
Klikk på "OK" i dialogboksen "Opprett pivottabell" som åpnes:
MS Excel vil lage et nytt ark der det vil indikere stedet der pivottabellen skal settes inn, og til høyre vil det vise et vindu for å sette opp pivottabellfelt, der du vil se alle navnene på kolonnene til din matrise:
La oss begynne å sette opp pivottabellen. Hva slags kutt ønsker vi? Produktkategorier etter leder - takk. Flytt musen over "Kategori"-feltet, klikk på det med venstre museknapp og dra det til "RADER"-feltet. "Manager"-feltet dras inn i "KOLONNER", og "Beløp" dras inn i "VERDIER":
Pivottabeller er et av de mest effektive verktøyene i MS Excel. Med deres hjelp kan du transformere en million rader med data til en kortfattet rapport på sekunder. I tillegg til å raskt oppsummere resultater, lar pivottabeller deg bokstavelig talt endre måten du analyserer på i farten ved å dra felt fra ett område av rapporten til et annet.
Pivottabeller er også et av de mest undervurderte Excel-verktøyene. De fleste brukere er uvitende om mulighetene som ligger i deres hender. La oss forestille oss at pivottabeller ikke er oppfunnet ennå. Du jobber for et selskap som selger produktene sine til ulike kunder. For enkelhets skyld er det kun 4 varer i sortimentet. Produktene kjøpes regelmessig av et par dusin kunder lokalisert i forskjellige regioner. Hver transaksjon legges inn i databasen og representerer en egen linje.
Din direktør ber deg lage en kort rapport om salget av alle varer etter region (region). Problemet kan løses som følger.
La oss først lage et tabelloppsett, det vil si en overskrift som består av unike verdier for produkter og regioner. La oss lage en kopi av produktkolonnen og fjerne duplikater. Deretter, ved å bruke insert special, transponerer vi kolonnen til en rad. Vi gjør det samme med regioner, bare uten å transponere. Vi vil motta overskriften på rapporten.
Denne platen skal fylles ut, dvs. oppsummere inntektene for de relevante produktene og regionene. Dette er enkelt å gjøre ved å bruke SUMIFS-funksjonen. La oss også legge til resultatene. Du vil få en oppsummerende rapport om salg etter region og produkt.
Du har fullført oppgaven og viser rapporten til direktøren. Når han ser på bordet, genererer han flere gode ideer på en gang.
— Er det mulig å lage en rapport ikke på inntekter, men på resultat?
— Er det mulig å vise produkter etter rader og regioner etter kolonner?
— Er det mulig å lage slike tabeller for hver leder for seg?
Selv om du er en erfaren Excel-bruker, vil det ta mye tid å lage nye rapporter. Dette er for ikke å snakke om mulige feil. Men hvis du kjenner pivottabeller, så er svaret: ja, jeg trenger 5 minutter, kanskje mindre.
Her er hvordan det gjøres. Åpne kildedataene. En pivottabell kan bygges ved å bruke et vanlig område, men det ville være mer riktig å transformere det. Dette vil umiddelbart løse problemet med automatisk innhenting av nye data. Velg en celle og gå til fanen Sett inn. På venstre side av båndet er det to knapper: og Anbefalte pivottabeller.
Hvis du ikke vet hvordan du organiserer eksisterende data, kan du bruke kommandoen Anbefalte pivottabeller. Excel vil vise miniatyrbilder av mulige oppsett basert på dataene dine.
Klikk på det aktuelle alternativet og pivottabellen er klar. Det gjenstår bare å tenke på det, fordi... Det er usannsynlig at en standard forberedelse vil falle helt sammen med dine ønsker. Hvis du trenger å bygge en pivottabell fra bunnen av, eller du har en gammel versjon av programmet, klikker du på knappen. Et vindu vises der du må spesifisere kildeområdet (hvis du aktiverer en Excel-tabellcelle, bestemmes den av seg selv) og plasseringen av den fremtidige pivottabellen (et nytt ark vil bli valgt som standard).
Svært ofte er det ikke nødvendig å endre noe her. Etter å ha klikket OK, opprettes et nytt Excel-ark med et tomt pivottabelloppsett.
Før vi går videre til innstillingene, la oss bli kjent med grensesnittet og grunnleggende konsepter. Tabelloppsettet kan tilpasses i panelet Pivottabellfelt, som er plassert på høyre side av arket.
Øverst i panelet er det en liste over alle tilgjengelige felt, det vil si kolonner i kildedataene. Hvis du trenger å legge til et nytt felt i oppsettet, kan du krysse av i boksen ved siden av - Excel bestemmer selv hvor dette feltet skal plasseres. Det er imidlertid ikke alltid riktig, så det er bedre å dra musen til ønsket sted på oppsettet. Felt kan også slettes ved å fjerne merket eller dra dem tilbake.
Pivottabellen består av 4 områder, som er plassert nederst i panelet: verdier, rader, kolonner, filtre. La oss se nærmere på formålet deres.
Rekkevidde av verdier– dette er den sentrale delen av pivottabellen med verdier som oppnås ved å aggregere kildedataene ved å bruke den valgte metoden.
I de fleste tilfeller skjer aggregering ved Oppsummering. Hvis alle dataene i det valgte feltet er i numerisk format, vil Excel tildele en standardsum. Hvis kildedataene inneholder minst én tekst eller tom celle, beregnes den i stedet for summen Mengde celler. I vårt eksempel er hver celle summen av alle tilsvarende produkter i den tilsvarende regionen.
Du kan bruke andre beregningsmetoder i pivottabellceller. Det er omtrent 20 typer (gjennomsnitt, minimumsverdi, andel osv.). Det er flere måter å endre beregningsmetoden på. Det enkleste er å høyreklikke på en hvilken som helst celle i ønsket felt i selve pivottabellen og velge en annen aggregeringsmetode.
Linjeområde– navnene på linjene som er plassert i kolonnen lengst til venstre. Dette er alle de unike verdiene til det valgte feltet (kolonnen). Det kan være flere felt i radområdet, da blir tabellen flernivå. Kvalitative variabler som produktnavn, måneder, regioner osv. plasseres vanligvis her.
Kolonneområde– ligner på rader, viser de unike verdiene til det valgte feltet, bare i kolonner. Kolonnetitler er også vanligvis en kvalitativ indikator. For eksempel år og måneder, produktgrupper.
Filterområde– brukes, som navnet tilsier, for filtrering. For eksempel viser selve rapporten produkter etter region. Du må begrense pivottabellen til en spesifikk bransje, periode eller leder. Deretter plasseres et filterfelt i filterområdet og ønsket verdi velges fra nedtrekkslisten.
Ved å legge til og fjerne felt til spesifiserte områder, kan du tilpasse hvilken som helst del av dataene dine på sekunder.
La oss se hvordan dette fungerer i aksjon. For nå, la oss lage den samme tabellen som allerede ble opprettet ved å bruke SUMIFS-funksjonen. For å gjøre dette, dra inn i området Verdier feltet "Inntekter", i området Strenger dra "Region"-feltet (salgsregion) til Kolonner- "Produkt".
Som et resultat får vi en ekte pivottabell.
Det tok bokstavelig talt 5-10 sekunder å bygge den. Det er også enkelt å endre en eksisterende pivottabell. La oss se hvordan regissørens ønsker lett blir oversatt til virkelighet.
La oss erstatte inntekter med profitt.
Produkter og områder kan også byttes ved å dra musen.
Det finnes flere verktøy tilgjengelig for filtrering av pivottabeller. I dette tilfellet plasserer vi ganske enkelt "Manager"-feltet i filterområdet.
Alt tok noen sekunder. Her er hvor enkelt det egentlig er å jobbe med pivottabeller. Selvfølgelig er ikke alle oppgaver så trivielle. Det er også tilfeller der det er nødvendig å bruke en mer sofistikert aggregeringsmetode, legge til beregnede felt, betinget formatering, etc. Men dette er en mer avansert bruk av pivottabeller.
Innledende data
For å kunne jobbe med pivottabeller må kildedataene oppfylle en rekke krav. En forutsetning er tilstedeværelsen av navn over hvert felt (kolonne) som disse feltene vil bli identifisert med. Nå noen nyttige tips.
1. Det beste formatet for data er et Excel-regneark. Det er bra fordi hvert felt har et navn og når nye rader legges til blir de automatisk inkludert i pivottabellen.
2. Unngå å gjenta grupper i kolonner. For eksempel skal alle datoer være i ett felt, og ikke delt opp for eksempel etter måned i separate kolonner.
3. Fjern hull og tomme celler, ellers kan denne raden falle ut av analysen.
4. Bruk riktig formatering på felt. Tall må være i numerisk format, datoer må være datoer. Ellers vil det oppstå problemer under gruppering og matematisk bearbeiding. Men Excel vil hjelpe deg her, fordi... Det bestemmer dataformatet ganske godt.
Generelt er det få krav, men du bør kjenne til dem.
Hvis du gjør endringer i kilden (for eksempel legger til nye rader), vil ikke pivottabellen endres før du oppdaterer den med høyre museknapp
eller
via kommandoen i fanen Data – Oppdater alle.
Dette ble gjort med vilje fordi pivottabellen tar opp mye plass i RAM. Derfor, for å bruke dataressurser mer økonomisk, arbeides det ikke direkte med kilden, men med cachen, der et øyeblikksbilde av kildedataene er plassert.
Ved å bruke pivottabeller selv på dette grunnleggende nivået kan du øke hastigheten og kvaliteten på behandlingen av store datamengder betydelig. Hvis noe er uklart, skriv i kommentarfeltet.
Pivottabeller er et av de kraftigste Excel-verktøyene. De lar deg analysere og oppsummere ulike resultater for store datamengder med bare noen få klikk. I denne artikkelen vil vi bli kjent med pivottabeller, forstå hva de er, lære hvordan du oppretter og konfigurerer dem.
Når jeg skrev denne artikkelen brukte jeg Excel 2010. Konseptet med pivottabeller har stort sett holdt seg uendret i mange år, men måten de lages på er litt forskjellig i hver nye versjon av Excel. Hvis du har en annen versjon av Excel enn 2010, så vær forberedt på at skjermbildene i denne artikkelen vil avvike fra det du ser på skjermen.
Litt historie
Ved begynnelsen av utviklingen av programmer for å lage regneark var reglene for Lotus 1-2-3. Dens dominans var så fullstendig at Microsofts innsats for å utvikle sin egen programvare (Excel) som et alternativ til Lotus virket bortkastet tid. Nå spol frem til 2010! Excel dominerer regnearkindustrien mer enn Lotus noen gang har gjort i sin historie, og antallet personer som fortsatt bruker Lotus nærmer seg null. Hvordan kunne dette skje? Hva var årsaken til en så dramatisk vending?
Analytikere identifiserer to hovedfaktorer:
- Først bestemte Lotus at denne nymotens GUI-plattformen kalt Windows bare var en forbigående kjepphest som ikke ville vare lenge. De nektet å lage en Windows-versjon av Lotus 1-2-3 (men bare i noen få år), og spådde at DOS-versjonen av programvaren deres var alt forbrukerne noen gang ville trenge. Microsoft utviklet selvfølgelig Excel spesielt for Windows.
- For det andre utviklet Microsoft et verktøy i Excel kalt pivottabeller, som ikke var tilgjengelig i Lotus 1-2-3. Pivottabeller, som er eksklusive for Excel, var så utrolig nyttige at folk var tilbøyelige til å lære den nye Excel-programvaren i stedet for å holde seg til Lotus 1-2-3, som ikke hadde dem.
Pivottabeller, kombinert med undervurderingen av suksessen til Windows generelt, spilte dødsmarsjen for Lotus 1-2-3 og markerte begynnelsen på suksessen til Microsoft Excel.
Hva er pivottabeller?
Så hva er den beste måten å beskrive hva pivottabeller er?
Enkelt sagt er pivottabeller sammendrag av noen data, laget for å lette analysen av disse dataene. I motsetning til manuelt opprettede totaler, er Excel-pivottabeller interaktive. Når de er opprettet, kan du enkelt endre dem hvis de ikke gir bildet du håpet å få. Med bare et par klikk kan totaler snus slik at kolonneoverskrifter blir radoverskrifter og omvendt. Det er mange forskjellige ting du kan gjøre med pivottabeller. I stedet for å prøve å beskrive med ord alle funksjonene til pivottabeller, er det lettere å demonstrere det i praksis...
Dataene du analyserer med pivottabeller kan ikke være noen form for data. Dette bør være rådata, som en liste av noe slag. Dette kan for eksempel være en liste over gjennomførte salg i selskapet de siste seks månedene.
Se på dataene vist i figuren nedenfor:
Vær oppmerksom på at dette ikke er rådata da de allerede er oppsummert. I celle B3 ser vi $30 000, som sannsynligvis er summen som James Cook tjente i januar. Hvor er da kildedataene? Hvor kom $30 000-tallet fra? Hvor er den opprinnelige salgslisten som denne månedlige summen ble hentet fra? Det er tydelig at noen har gått langt for å organisere og sortere alle salgsdata for de siste seks månedene og gjøre det om til oppsummeringstabellen vi ser. Hvor lang tid tror du det tok? Time? Klokken ti?
Faktum er at tabellen ovenfor ikke er en oppsummeringstabell. Den ble opprettet manuelt fra rådata lagret andre steder og tok minst et par timer å behandle. Dette er akkurat den typen sammendragstabell som kan lages ved hjelp av pivottabeller, og bruker bare noen få sekunder på den. La oss finne ut hvordan...
Hvis vi går tilbake til den opprinnelige salgslisten, vil den se omtrent slik ut:
Du kan bli overrasket over at fra denne listen over handelstransaksjoner, ved hjelp av pivottabeller og på bare noen få sekunder, kan vi lage en månedlig salgsrapport i Excel, som vi diskuterte ovenfor. Ja, vi kan gjøre dette og mye mer!
Hvordan lage en pivottabell?
Først må du sørge for at du har noen kildedata på et Excel-ark. Listen over finansielle transaksjoner er det mest typiske som finnes. Faktisk kan det være en liste over hva som helst: kontaktinformasjon for ansatte, en samling av CD-er eller bedriftens drivstofforbruksdata.
Så la oss starte Excel... og laste denne listen...
Når vi har åpnet denne listen i Excel, kan vi begynne å lage en pivottabell.
Velg en celle fra denne listen:
Så på fanen Sett inn(Sett inn) velg kommando Pivottabell(Sammendragstabell):
En dialogboks vises Lag pivottabell(Opprette en pivottabell) med to spørsmål til deg:
- Hvilke data bør jeg bruke for å lage en ny pivottabell?
- Hvor skal jeg plassere pivotbordet?
Siden vi i forrige trinn allerede har valgt en av listecellene, vil hele listen velges automatisk for å lage en pivottabell. Merk at vi kan velge et annet område, en annen tabell eller til og med en ekstern datakilde, for eksempel en Access- eller MS-SQL-databasetabell. I tillegg må vi velge hvor vi skal plassere det nye pivotbordet: på et nytt ark eller på et av de eksisterende. I dette eksemplet vil vi velge alternativet - Nytt arbeidsark(På et nytt ark):
Excel vil opprette et nytt regneark og plassere en tom pivottabell på det:
Så snart vi klikker på en celle i pivottabellen, vises en annen dialogboks: Pivottabellfeltliste(Pivottabellfelt).
Listen over felt øverst i dialogboksen er en liste over alle titlene i den opprinnelige listen. De fire tomme områdene nederst på skjermen lar deg fortelle pivottabellen hvordan du vil oppsummere dataene. Mens disse områdene er tomme, er det heller ingenting i tabellen. Alt vi trenger å gjøre er å dra overskriftene fra det øverste området til de tomme områdene nedenfor. I dette tilfellet genereres en pivottabell automatisk i henhold til våre instruksjoner. Hvis vi har gjort en feil, kan vi fjerne overskriftene fra bunnområdet eller dra andre for å erstatte dem.
Region Verdier(Betydninger) er sannsynligvis den viktigste av de fire. Hvilken overskrift som plasseres i dette området avgjør hvilke data som skal oppsummeres (sum, gjennomsnitt, maksimum, minimum osv.) Dette er nesten alltid numeriske verdier. En utmerket kandidat til en stilling innen dette området - data under overskriften Beløp(Kostnad) for vårt originale bord. La oss dra denne tittelen til området Verdier(Verdier):
Vær oppmerksom på at tittelen Beløp er nå merket med hake, og i området Verdier(Verdier) oppføring dukket opp Sum av Beløp(Sum etter beløp-feltet), som indikerer at kolonnen Beløp oppsummerte
Hvis vi ser på selve pivottabellen, vil vi se summen av alle verdiene fra kolonnen Beløp originalt bord.
Så vår første pivottabell er laget! Praktisk, men ikke spesielt imponerende. Vi ønsker sannsynligvis mer informasjon om dataene våre enn vi har i dag.
La oss se på kildedataene og prøve å identifisere en eller flere kolonner som kan brukes til å bryte ned denne summen. For eksempel kan vi lage vår pivottabell på en slik måte at det totale salgsbeløpet beregnes for hver selger separat. De. Rader vil bli lagt til pivottabellen vår med navnet på hver firmaselger og hans totale salgsbeløp. For å oppnå dette resultatet, bare dra tittelen Selger Radetiketter(Linjer):
Det blir mer interessant! Pivotbordet vårt begynner å ta form...
Ser du fordelene? I løpet av et par klikk laget vi en tabell som ville tatt veldig lang tid å lage manuelt.
Hva annet kan vi gjøre? Vel, på en måte er pivottabellen vår allerede klar. Vi har laget et nyttig sammendrag av rådataene. Viktig informasjon er allerede mottatt! I resten av denne artikkelen skal vi se på noen måter å lage mer komplekse pivottabeller på og lære hvordan du tilpasser dem.
Sette opp en pivottabell
Først kan vi lage en todimensjonal pivottabell. La oss gjøre dette ved å bruke kolonneoverskriften Betalingsmetode(Betalingsmetode). Bare dra tittelen Betalingsmetode til regionen Kolonneetiketter(Kolonner):
Vi får resultatet:
Ser veldig kult ut!
La oss nå lage en tredimensjonal tabell. Hvordan kan et slikt bord se ut? La oss ta en titt...
Dra tittel Pakke(Kompleks) til området Rapportfilter(Filtre):
Legg merke til hvor han havnet...
Dette gir oss muligheten til å filtrere rapporten etter attributtet "Hvilket feriekompleks ble betalt for". For eksempel kan vi se en oversikt etter selgere og betalingsmåter for alle komplekser, eller i et par klikk kan vi endre visningen av sammendragstabellen og vise den samme inndelingen kun for de som har bestilt komplekset Solsøkere.
Så hvis du forstår dette riktig, kan pivottabellen vår kalles tredimensjonal. La oss fortsette å konfigurere...
Hvis det plutselig viser seg at bare betalinger med sjekk og kredittkort (det vil si bankoverføring) skal vises i pivottabellen, kan vi deaktivere overskriftsvisningen Penger(Penger). For å gjøre dette ved siden av Kolonneetiketter klikk på pil ned og fjern merket for elementet i rullegardinmenyen Penger:
La oss se hvordan pivottabellen vår ser ut nå. Som du kan se, kolonnen Penger forsvant fra henne.
Formatering av pivottabeller i Excel
Selvfølgelig er pivottabeller et veldig kraftig verktøy, men så langt ser resultatene litt enkle og kjedelige ut. For eksempel ser ikke tallene vi legger opp som dollarbeløp – de er bare tall. La oss fikse dette.
Det er fristende å gjøre de vanlige handlingene i en slik situasjon og ganske enkelt velge hele tabellen (eller hele arket) og bruke standard tallformateringsknapper på verktøylinjen for å angi ønsket format. Problemet med denne tilnærmingen er at hvis du noen gang endrer strukturen til pivottabellen i fremtiden (og det er 99 % sjanse for at dette skjer), vil formateringen gå tapt. Vi trenger en måte å gjøre det (nesten) permanent på.
Først, la oss finne oppføringen Sum av Beløp i området Verdier(Verdier) og klikk på den. Velg elementet i menyen som vises Innstillinger for verdifelt(Alternativer for verdifelt):
En dialogboks vises Innstillinger for verdifelt(Verdifeltparametere).
Klikk på knappen Tallformat(Tallformat), vises en dialogboks Formater celler(Celleformat):
Fra listen Kategori(Tallformater) velg Regnskap(Økonomi) og sett antall desimaler til null. Trykk nå flere ganger OK for å gå tilbake til pivottabellen vår.
Som du kan se, endte tallene opp med å bli formatert som dollarbeløp.
Nå som vi er ferdige med formatering, la oss sette opp formatet for hele pivottabellen. Det er flere måter å gjøre dette på. La oss bruke den enklere...
Åpne fanen Pivottabellverktøy: Design(Jobber med pivottabeller: Designer):
Velg en passende stil og se på resultatet i pivottabellen din:
Andre innstillinger for pivottabeller i Excel
Noen ganger må du filtrere data etter datoer. For eksempel inneholder listen vår over handelstransaksjoner mange, mange datoer. Excel gir et verktøy for å gruppere data etter dag, måned, år osv. La oss se hvordan det gjøres.
Fjern først oppføringen. Betalingsmetode fra regionen Kolonneetiketter(Kolonner). For å gjøre dette, dra den tilbake til listen over overskrifter, og flytt overskriften i stedet for Dato bestilt(Bestillingsdato):
Som du kan se, har dette midlertidig gjort pivottabellen vår ubrukelig. Excel opprettet en egen kolonne for hver dato en handel ble gjort. Som et resultat fikk vi et veldig bredt bord!
For å fikse dette, høyreklikk på en hvilken som helst dato og velg fra hurtigmenyen Gruppe(Gruppe):
Grupperingsdialogboksen vises. Vi velger Måneder(Måneder) og trykk OK:
Voila! Denne tabellen er mye mer nyttig:
Denne tabellen er forresten nesten identisk med den som vises i begynnelsen av artikkelen, hvor salgsresultatene ble satt sammen manuelt.
Det er enda et veldig viktig poeng du trenger å vite! Du kan ikke lage én, men flere nivåer av rad- (eller kolonne)overskrifter:
...og det vil se slik ut...
Det samme kan gjøres med kolonneoverskrifter (eller til og med filtre).
La oss gå tilbake til den opprinnelige tabellvisningen og se hvordan du viser gjennomsnittsverdier i stedet for summer.
For å komme i gang, klikk på Sum av Beløp og fra menyen som vises, velg Innstillinger for verdifelt(Alternativer for verdifelt):
På listen Oppsummer verdifeltet etter(Operasjon) i dialogboksen Innstillinger for verdifelt(Alternativer for verdifelt) velg Gjennomsnitt(Gjennomsnitt):
Samtidig, mens vi er her, la oss forandre oss Egendefinert navn(Egendefinert navn) med Gjennomsnitt av beløp(Antall etter Mengde-feltet) til noe kortere. Skriv inn noe slikt i dette feltet Gj.sn:
Klikk OK og se hva som skjedde. Legg merke til at alle verdier har endret seg fra totaler til gjennomsnitt, og tabelltittelen (i cellen øverst til venstre) er endret til Gj.sn:
Hvis du vil, kan du umiddelbart få sum, gjennomsnitt og mengde (salg) plassert i én pivottabell.
Her er en trinn-for-trinn-guide for hvordan du gjør dette, og starter med en tom pivottabell:
- Dra tittel Selger(salgsrepresentant) til regionen Kolonneetiketter(Kolonner).
- Dra tittelen tre ganger Beløp(Kostnad) til området Verdier(Verdier).
- For det første feltet Beløp endre navnet til Total(Beløp), og tallformatet i dette feltet er Regnskap(Finansiell). Antall desimaler er null.
- Andre felt Beløp Navn Gjennomsnittlig e, angi operasjonen for det Gjennomsnitt(Gjennomsnitt) og endre tallformatet i dette feltet til Regnskap(Finansielt) med antall desimaler lik null.
- For det tredje feltet Beløp angi tittel Telle og en operasjon for ham - Telle(Mengde)
- I området Kolonneetiketter(Kolonner)-feltet opprettet automatisk Σ Verdier(Σ Verdier) – dra den til området Radetiketter(strenger)
Dette er hva vi får til slutt:
Totalt, gjennomsnitt og antall salg - alt i en pivottabell!
Konklusjon
Pivottabeller i Microsoft Excel inneholder mange, mange funksjoner og innstillinger. En så kort artikkel kan ikke engang komme i nærheten av å dekke dem alle. For å fullstendig beskrive alle funksjonene til pivottabeller ville det kreve en liten bok eller et stort nettsted. Modige og nysgjerrige lesere kan fortsette å utforske pivottabeller. For å gjøre dette, bare høyreklikk på nesten hvilket som helst element i pivottabellen og se hvilke funksjoner og innstillinger som åpnes. På båndet finner du to faner: Pivottabellverktøy: Alternativer(Analyse) og Design(Konstruktør). Ikke vær redd for å gjøre en feil; du kan alltid slette pivottabellen og begynne på nytt. Du har en mulighet som mangeårige DOS- og Lotus 1-2-3-brukere aldri har hatt.
Pivottabeller i Excel lar deg analysere data som er i ett stort område. Dessuten kan kildetabellen som skal brukes til analyse lages enten i Excel eller i en annen database eller et annet dokument.
Alt du trenger å gjøre er å velge de nødvendige overskriftene for rader og kolonner fra de eksisterende. Du kan også bruke et filter på hele pivottabellen som helhet, eller bare på verdiene som er angitt i navnene på radene og kolonnene.
Nå om kravene som må følges når du oppretter den i Excel. Kildedataene skal presenteres i form av en tabell, som skal ha en tittel for kolonnene, det vil si en overskrift. For disse formålene er det perfekt å lage en smart tabell i Excel. Den skal heller ikke inneholde tomme rader, kolonner eller celler. Det skal ikke være skjulte rader/kolonner eller sammenslåtte celler.
Hvordan lage
La oss nå se på et eksempel på å lage en pivottabell. Anta at vi har data om salg av en klesbutikk: hvilken selger, hvilket antall, hvilke varer han solgte og for hvilket beløp.
For å lage en pivottabell, velg hvilken som helst celle fra den opprinnelige, og gå deretter til "Sett inn"-fanen og klikk på knappen "Pivottabell".
Følgende dialogboks vises. I den må du angi området eller navnet på tabellen med kildedataene, og her kan du velge den fra en annen kilde. Merk deretter med en markør hvor rapporten som skal opprettes skal plasseres. La oss gjøre det på et nytt ark. Klikk OK.
Et nytt ark vil bli opprettet i den åpne Excel-arbeidsboken, der den fortsatt tomme pivottabellen vil bli plassert.
En liste over felt og områder vises på høyre side. Feltene er alle kolonneoverskriftene som var i det opprinnelige området. Ved hjelp av musen vil vi dra dem inn i et av de fire områdene nedenfor. Dermed danner en pivottabell.
Feltene som er lagt til vil merkes med et hakemerke. I tabellområdene kan du endre plassering for å oppnå det utseendet som er best egnet for å analysere kildedataene dine.
Hvordan ordne felt
La oss nå bestemme hvilket prinsipp vi skal analysere dataene. Du må for eksempel finne ut hvilken selger som solgte hvilket produkt i hver måned og for hvilket beløp.
Vi vil filtrere dataene i henhold til det valgte området etter selgere. Det vil si at vi velger en selger, og tabellen vil vise varene som selges av ham. Venstreklikk på "Leverandør"-feltet og dra det til "Rapportfilter"-området. Tabellen er endret og det tilføyde feltet er nå merket med en hake.
For linjene, velg "Produkter". På samme måte drar du ønsket felt inn i området "Linjetitler".
Vær oppmerksom på at du kan sette inn flere felt i et område. For eksempel vil vi i linjene velge produkter og angi prisen. Produkter har blitt nedtrekkslister som viser prisen. Hvis du først spesifiserer prisen og deretter produktene, blir nedtrekkslisten pris. Her har rekkefølgen på feltene betydning.
Hvis vi hadde en kolonne "Enheter" i den opprinnelige tabellen. Så vil denne oppsummeringstabellen vise hvilken selger, i hvilken måned, hvor mange vareenheter han solgte til en bestemt pris.
Til regionen "Kolonnetitler" Dra "Dato"-feltet. For å vise salg ikke for hver dag, men for eksempel etter måned, høyreklikk på en hvilken som helst dato og velg "Gruppe" fra menyen.
Tabellen vil ha følgende form.
Dra nå "Beløp"-feltet inn i "Verdier"-området.
Som du kan se, ble bare tall vist, selv om det numeriske formatet til cellene for denne kolonnen ble satt i det opprinnelige området; det kan også være monetært eller økonomisk. I kolonnen "Dato" var celleformatet også passende - dato.
For å fikse dette, velg ønsket celleområde i sammendraget og trykk på høyre museknapp. velg deretter fra menyen "Tallformat".
I neste vindu velger du "Numerisk", du kan merke av i boksen "Bitgruppeseparator" og klikk "OK".
Hvordan jobbe med data
Etter at vi har tatt hensyn til alle nødvendige felt, kan vi begynne å jobbe med pivottabellen i Excel. Velge en selger: du kan velge én, flere eller alle samtidig ved å merke av i boksen "Velg flere elementer".
Du kan også bruke et filter for rader og kolonner. I eksemplet er dette produkter og måneder. For eksempel, ved å merke av i boksene for Dress og Bukser, kan du finne ut hvor mye de ble solgt for av alle selgere eller av en bestemt selger.
I Verdi-området kan du konfigurere innstillinger for feltet. Eksemplet viser summen av verdiene: Roma solgte skjorter for 1800,00 i februar. la oss se hvor mange stykker det er. Venstreklikk på linjen "Sum etter felt..." og velg fra menyen "Alternativer for verdifelt".
I det neste vinduet, velg "Antall" fra listen, velg det som passer spesifikt for ditt tilfelle, og klikk "OK".
Når vi ser på verdiene, kan vi forstå at Roma solgte to skjorter i februar.
La oss nå lage det generelle filteret for tabellen etter måned. Vi endrer områdene: i "Rapportfilter" drar vi "Dato"-feltet inn "Kolonnetitler"- "Selger".
En slik oppsummeringstabell vil vise hvor mye og hva slags varer hver selger solgte for hele perioden eller for en bestemt måned.
Vær også oppmerksom på båndet. Når du velger noen celler fra en pivottabell, vises en fane på den "Jobbe med pivottabeller" med to underfaner "Parameters" og "Designer".
Problemet vi alle står overfor er ikke mangel på data; tvert imot – dette er enorme mengder data! Dette er grunnen til at jeg anbefaler å bruke en flott Excel-funksjon - Pivottabeller for å oppsummere og analysere dataene dine.
Å se kraften i pivottabeller er like enkelt som å sammenligne de originale dataene med en versjon av pivottabellen; Med bare noen få klikk var jeg i stand til å se gjennomsnittsprisen på en vare etter stat.
Som finansprofesjonell er jeg genetisk tilbøyelig til å elske regneark. Men jeg har også funnet ut at jeg bruker regneark til å organisere mitt kreative og frilansarbeid. Uansett hva du bruker regneark til, kan en pivottabell hjelpe deg med å få mer mening ut av dataene dine.
Vi vil bygge denne leksjonen i henhold til vår, for bedre arbeid med data. Jeg skal vise deg fem av mine beste fremgangsmåter for pivottabeller.
Gjennom denne opplæringen vil jeg bruke eksempeldataene levert av Microsoft på denne siden. Bruk disse dataene til å gjenskape eksemplene eller teste funksjonene jeg demonstrerer.
Slik bruker du avanserte pivottabellteknikker i Excel (kort video)
Jeg liker å undervise ved hjelp av screencasts, som gir deg muligheten til å se meg bruke funksjoner steg-for-steg. Sjekk ut den korte videoen nedenfor som dekker fem av mine favoritt avanserte Excel PivotTable-funksjoner:
5 avanserte pivottabelltriks i Excel
Fortsett å lese for å lære hvordan du bruker hver av disse fem funksjonene i leksjonen nedenfor, inkludert: Slicers, Tidslinje, Tabellvisning, Kalkulerte felt og Utvalgte pivottabeller. La oss komme i gang.
1. Seksjoner
Skiver- Et pek-og-klikk-verktøy for å avgrense dataene som er inkludert i en Excel-pivottabell. Sett inn en slicer og du kan enkelt endre dataene som er inkludert i pivottabellen.
I dette eksemplet har jeg satt inn en skive for type Punkt. Etter at jeg har klikket på Ryggsekk, viser pivottabellen kun denne parameteren i tabellen.Mange ganger utvikler jeg dashbordrapporter som vil bli brukt av andre. Å legge til slicere kan hjelpe sluttbrukeren med å tilpasse rapporten etter eget ønske.
For å legge til en slicer, klikk i pivottabellen og finn fanen Analyse på Excel-båndet.
Merk av for flere bokser for å inkludere disse kolonnene, som hver vil være en skive.Holde Ctrl på tastaturet for å velge flere slicer-elementer som vil inkludere flere valg fra en kolonne som pivottabelldata.
2. Tidslinje
Tidslinjer Dette er en spesiell type slicer som brukes til å justere datoene som er inkludert som en del av pivottabelldataene. Hvis dataene dine inneholder datoer, må du virkelig prøve Timeline som en måte å velge data fra bestemte tidsperioder på.
Gå til seksjon Analyser > Sett inn tidslinje for å legge til en tidslinje, en spesiell type slicer som administrerer dataene som er inkludert i en avansert Excel-pivottabell basert på dato.Tips: Hvis denne funksjonen ikke fungerer for deg, sørg for at kildedataene dine har en dato formatert i regnearket som en dato.
For å legge til en tidslinje, sørg for at du velger pivottabellen (klikk inne i den) og klikk deretter på knappen Analyser > Sett inn tidslinje på Excel-båndet. I popup-vinduet velger du en datokolonne (eller flere kolonner) og klikker OK for å lage en tidslinje.
Klikk og dra i tidslinjevinduet for å velge et spesifikt tidsrom for pivottabellen.Når tidslinjen er satt inn, kan du klikke og flytte håndtakene på tidslinjen for å endre hva som er inkludert i pivottabellen.
Du kan endre måten tidslinjen fungerer på ved å klikke på rullegardinlisten nederst i høyre hjørne. Du kan endre tidslinjen for å vise kvartalsvise eller årlige data i stedet for spesifikke datoer, for eksempel.
3. Tabellvisning
Standardvisningen av pivottabeller i Excel ser ut som en foss; Etter hvert som du drar flere felt inn på rader, oppretter Excel flere "lag" i dataene.
Problemet er at det er vanskelig å passe formler inn i pivottabeller i standardvisning. Hvis du har data i en pivottabell, men ønsker å se den som et vanlig regneark, må du bruke en tabellvisning for pivottabellen.
Plukke ut Designer > Rapportoppsett > Vis i tabellformå jobbe med pivotbordet ditt som et vanlig bord.Hvorfor bør du bruke en tabellvisning? Ved å bringe pivottabelldataene inn i en klassisk tabellstil, kan du lettere legge inn dataformler eller sette dem inn i en separat rapport.
Bruk tabellvisning for å få pivottabellen til å se mer ut som standardrader og -kolonner.Mesteparten av tiden foretrekker jeg å bruke Excels tabellvisning. Det ser mer ut som et standard regneark og er lettere å skrive formler og arbeide med data i det. Jeg kan like gjerne ta denne visningen og lime den inn i en ny fane.
4. Beregnede felt
Beregnede felt er en måte å legge til en kolonne i en pivottabell som ikke er i kildedataene. Du kan bruke standard matematiske operasjoner for å lage helt nye felt å jobbe med. Ta to eksisterende kolonner og bruk matematikk for å lage helt nye.
La oss si at vi har salgsdata i et regneark. Vi har antall solgte varer og salgspris for hver vare. Dette er det ideelle tidspunktet for å bruke et beregnet felt for å beregne ordresummen.
For å komme i gang med å jobbe med beregnede felt, start med å klikke inne i pivottabellen og finn deretter på båndet Analyse. Klikk på menyen Felt, elementer og sett og velg deretter Beregnet felt.
Bruk Analyse > Felt, medlemmer og sett > Beregnet felt for å sette inn det beregnede feltet i pivottabellen.I det nye popup-vinduet starter du med å gi det beregnede feltet et navn. i mitt tilfelle vil jeg kalle det Total ordre. Den totale ordreverdien er mengden multiplisert med prisen på hver enhet. Deretter dobbeltklikket jeg på det første tittelfeltet (antall) i listen over felt i dette vinduet.
For å beregne den totale ordrekostnaden multipliserte jeg det eksisterende enhetsprisfeltet ( Enhetspris) i mengdefeltet ( Mengde).Etter å ha lagt til navnet på dette feltet, vil jeg legge til et multiplikasjonstegn * , og dobbeltklikk deretter på det totale antallet (antall). La oss gå videre og klikke OK.
Nå har Excel oppdatert min utvidede pivottabell og lagt til et nytt beregnet felt. Du vil også se en liste over pivottabeller i feltlisten slik at du kan dra dem hvor som helst i rapporten når du trenger dem.
Hvis du ikke vil bruke to-kolonne aritmetikk, kan du også legge inn dine egne aritmetiske verdier i det beregnede feltet. For eksempel, hvis jeg bare ønsker å legge til 5 % merverdiavgift for hver ordre, kan jeg skrive følgende i det beregnede feltet:
Jeg multipliserte den totale ordrekostnaden med 1,05 for å beregne kostnaden inkludert merverdiavgift; du kan bruke numeriske verdier sammen med eksisterende felt.I prinsippet kan beregnede felt inneholde alle standard matematiske operatorer som addisjon, subtraksjon, multiplikasjon og divisjon. Bruk disse beregnede feltene hvis du ikke vil oppdatere de opprinnelige dataene.
Gå til Sett inn > for å prøve denne funksjonen.Denne funksjonen er så enkel å bruke at den sier seg selv. Du kan bruke den til å raskt lage avanserte pivottabeller i Excel. Bare marker dataene dine, gå til fanen Sett inn på Excel-båndet og velg Anbefalte pivottabeller.
Popup-vinduet inneholder mange alternativer for å lage en pivottabell fra kildedataene. Klikk på miniatyrbildene på venstre side av dette vinduet for å se de anbefalte pivottabellalternativene fra Excel.
Pivottabellanbefalingsfunksjonen tilbyr mange alternativer for å analysere dataene dine med ett klikk.Selv om dette er en avansert funksjon som få brukere er klar over, er den også et flott verktøy for å kjøre pivottabeller. Det er ingenting som hindrer deg i å endre pivottabellen ved å erstatte feltene selv, men det er et godt utgangspunkt.
Dessuten liker jeg denne funksjonen som et datautforskningsverktøy. Hvis jeg ikke vet hva jeg ser etter når jeg begynner å utforske dataene, er de anbefalte Excel-pivottabellene ofte mer innsiktsfulle enn jeg er!
Gjenta og fortsett å lære (med enda flere Excel-timer)
Denne avanserte Excel-leksjonen hjalp deg med å dykke dypere inn i pivottabeller, en av mine favorittfunksjoner for å analysere og vise et Excel-regneark. Jeg bruker pivottabeller for å finne mening i store datasett, slik at jeg kan ta bedre beslutninger og handle.
Disse leksjonene tar ferdighetene dine med Excel og pivottabeller til neste nivå. Sjekk de ut:
- ExcelZoo har en flott oversikt over pivottabellteknikker i artikkelen deres, 10 Lessons for Mastering Pivot Tables (på engelsk).
- Vi i Envato Tuts+ dekket pivottabeller med en nybegynneropplæring.
- For en enklere introduksjon til Microsoft Excel, sjekk opplæringsserien vår.
Hva vil du fortsatt vite om pivottabeller? Gi meg beskjed om dine ideer eller spørsmål i kommentarene under denne opplæringen.
