Start med flere års data.
La oss si at du har to års detaljerte poster. Hver plate har en daglig dato. Når du bygger en pivottabell fra denne rapporten, vil du ha hundrevis av rader med daglige datoer i pivottabellen. Dette er ikke mye av et sammendrag.
Velg en av disse datacellene i pivottabellen. Fra gruppen Analyser i båndet velger du Gruppefelt.
Fordi du er på et datofelt, får du denne versjonen av grupperingsdialogen. I den, fjern merket for Måneder og velg År.
De daglige datoene rulles opp til år. Flytt datofeltet fra RADER til KOLONNER.
Resultatet er nesten perfekt. Men i stedet for en totalsum i kolonne D, vil du sannsynligvis ha en prosentvarians.
For å bli kvitt Grand Total-kolonnen, velg Grand Totals, på Only for Column, i kategorien Design. (Jeg er enig, dette er en av de mer vanskelige formuleringene i Excel.)
For å bygge varianskolonnen, må du skrive en formel utenfor pivottabellen som peker inne i pivottabellen. Ikke berør musen eller piltastene mens du bygger formelen, ellers vises den ekle GETPIVOTDATA-funksjonen. I stedet skriver du bare =C5/B5-1og trykker Enter.
Takk til Александр Воробьев for å foreslå dette tipset.
Se på video
- Start med flere års data
- Sett inn, pivottabell
- Dra datofeltet til radområdet
- Excel 2016: Trykk Ctrl + Z for å gruppere datoene
- Dra inntektene til verdiområdet
- Velg hvilken som helst dato i radområdet
- Bruk gruppefelt
- Velg måneder og år
- Hvordan legge til delsummer i år-feltet etter gruppering av en pivottabell
- Bruk tabellform i en pivottabell for å gi hvert radfelt sin egen kolonne
- Gjenta alle radetiketter for å fylle ut emnene i en pivottabell
- Dra år til kolonneområdet
- Høyreklikk Totalt kolonneoverskrift og fjern
- Hvordan unngå GetPivotData-funksjon når formelen peker på en pivottabell
- For å bygge en varians, skriv formelen uten mus eller piler
- Takk til Александр Воробьев for å foreslå dette tipset
Videoutskrift
Lær Excel fra MrExcel podcast, episode 1998. Pivottabell År over år.
Jeg podcaster hele denne boken. Gå og klikk på det "i" øverst til høyre for MrExcel Excel-spillelisten.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. I dag det første emnet i boken om pivottabeller og hvordan du lager en årsrapport. Så vi starter med datasettet, som strekker seg over to år, skjønner, vi går fra 2018 til 2019. Jeg skal lage en pivottabell, Sett inn, pivottabell, OK, det er greit, la det gå til en nytt regneark.
På det nye regnearket skal vi ta datofeltet og dra det til radområdet. Og så, ser du, i Excel 2016 grupperer det automatisk ting. Nå vet jeg at folk fortsatt er i Excel 2013, kanskje Excel 2010, så jeg skal trykke Ctrl + Z for å angre. Det er opplevelsen du får i 2010 og 2013, vi skal ta inntektsfeltet og dra det til Verdier-området. Så vi starter med en stygg rapport, det kommer til å være hundrevis og hundrevis av rader, for det var hver eneste dag vi hadde salg. Men jeg skal gå til det aller første datofeltet, aller første datofeltet, gå til PivotTable Tools, Analyze, i 2013 eller 2016 og 2010 ble det kalt Alternativer, og velg Group Field. Forsikre deg om at du velger en dato før du velger Gruppefelt, og rull ting opp til måneder og år. Eller kvartaler og år,eller Måneder og kvartaler og år, klikk OK. Og det vi får, er et nytt virtuelt felt som heter Years her nede i Rows-området.
Nå noen irritasjoner. Det er irriterende at de ikke gir oss årssummer, det er lett nok å fikse det, selv om jeg ikke trenger å fikse det i mitt tilfelle. Vi klikker på feltinnstillinger og automatisk, fordi jeg ikke planlegger å la årene være her. Selvfølgelig hver gang jeg oppretter en pivottabell, går jeg til Design og sier Vis i tabellform for å få alt inn i sin egen kolonne. Og gjenta deretter alle elementetikettene for å fylle ut disse emnene langs venstre side. Igjen, det ville ikke være nødvendig her, fordi jeg ikke planlegger å forlate årene der det er, jeg skal ta årene og dra det over til kolonnene, og det er rapporten jeg skjøt for.
Greit, så vi har måneder som går ned på venstre side, 2018 til 2019. I kolonne D, hva vil jeg egentlig, jeg vil ikke ha en total, jeg skal høyreklikke og si Fjern den store Totalt, og nå er vi utenfor en pivottabell. Så,% Change, veldig fristende å bygge denne formelen ved hjelp av musen eller piltastene, men du kan ikke, for du får … få Pivot Data-funksjonen, som ikke enkelt kopieres. Så = C5 / B5-1, vi formaterer det i prosent og dobbeltklikker for å kopiere det ned, til høyre. Vakker rapport som viser hvor vi var i fjor, hvor vi var i år og deretter prosentvis endring fra år til år.
Vel, dette tipset og 40 andre, er alle i denne boka. Gå videre, klikk på “i” øverst til høyre, ved boken. OK, så du begynner med datasettet med flere år, gjør Sett inn, pivottabell, dra datofeltet til radområdet. Og i Excel 2016 trykker du på Ctrl + Z for å angre de automatiske grupperingsoppdateringene. Hvorfor lar vi ikke dette være her? Vel, det kommer faktisk an på. Hvis du vil ha data fra, la oss si, 2. januar til 30. desember, kommer de ikke til å gruppere dataene riktig. Det må tilbringe et helt år for at du skal få årsfeltet. Det kan være at det ordner seg for deg, jeg er litt av en kontrollfreak, jeg vil heller bare ha kontroll, det er derfor jeg angrer. Det er: dra inntektene til verdiområdet, velg hvilken som helst celle, hvilken som helst dato i radområdet og deretter Gruppefelt, velg Måneder og år,dra år over kolonneområdet, høyreklikk Grand Total for å fjerne og bygg deretter en avvik, skriv inn formelen, ikke bruk mus eller piltastene.
Jeg aner ikke hvordan jeg skal uttale det, men takk til denne leseren (Александр Воробьев) for å foreslå dette tipset og takk til deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast1998.xlsx
