Nederste linjen: finn ut forskjellene mellom Gruppen Datoer funksjonen i en pivot bord og gruppering datoer i datakilden ved å legge til ekstra kolonner for år, måned, kvartal, etc.
nivå: Middels
En av de mange grunnene til at pivot tabeller er kjempebra at de gjør det mulig for oss å raskt gruppe datoer for å opprette sammendragsrapporter. Hva gjør gruppering datoer betyr?, Vel, det betyr bare at vi gruppere alle dato verdier i en kolonne i et år, kvartal, måned, dag, osv.
Hvis vi har transaksjon nivå data med en kolonne som inneholder dato for transaksjonen, så vi kan være lurt å gruppere datoer for å rapportere på data etter år, kvartal eller måned.
Det er tre måter å gå om dato gruppering i Excel:
- kan Vi bruke Gruppen Feltet funksjon av pivot-tabell.
- Vi kan opprette flere kolonner i kildedataene med tekst eller numeriske verdier ved hjelp av formler.,
- kan Vi opprette relasjoner mellom tabeller med oppslag formler eller Power Pivot.
Er en bedre enn den andre? Ikke nødvendigvis. Som alt annet i Excel, er det alltid et par forskjellige måter å utføre en oppgave. Metoden du bruker her, avhenger av dine behov, og selskapets regnskapsår kalender.
#1 – Pro & Ulemper av Gruppen Feltet Funksjon
Gruppen Feltet funksjonen gjør det mulig for oss å raskt gruppen et felt av datoer i År, Kvartal, Måneder, osv.,
Dette betyr at kilden data trenger IKKE å inneholde kolonner med år, kvartal eller måned navn. Gruppen Feltet funksjonen vil automatisk lage grupperinger for oss og oppsummere data i hver gruppe.
Når vi lager grupper på menyen Gruppering, nye felt legges til pivot tabell felt-listen.
Slik Gruppe & Fjern Felt
Her er en rask guide på hvordan å gruppere felt i pivot tabeller.,
- Velg en celle i Rader eller Kolonner området av pivot-tabell som inneholder felt som du vil gruppere. Dette er vanligvis et dato-felt, men kan også være tall.
- Velg Analysere/Valg-fanen på Båndet.
- Klikk Gruppen Feltet knappen. Sjekk ut denne artikkelen hvis Gruppen Feltet Knappen er Deaktivert.
- Velg elementene du ønsker å gruppere felt av. For dato-feltene dette er År, Kvartal, Måneder, Dager, Timer, Minutter, Sekunder.,
- Klikk på OK.
– Feltet vil bli gruppert og nye felter vil bli lagt til felt-listen for grupper.
Hvis du vil Dele opp feltet:
- Velg en celle i pivottabellen for de grupperte feltet.
- Velg Analysere/Valg-fanen på Båndet.
- Trykk på Dele-knappen (ovenfor Gruppe-knappen).
Automatisk Dato/Tid grupperingsalternativ
I Excel-2016 denne grupperingen skjer automatisk når du legger til et dato-felt til et av områdene i pivot-tabell. Dette er en innstilling som kan nå bli slått på eller av i Excel Options-menyen.,
Her er hvordan du kan deaktivere automatisk dato gruppering for pivot tabeller:
- Gå til Fil > Alternativer i Excel for å åpne Alternativer for Excel-Vinduet.
- Klikk kategorien Data i sidepanelet til venstre. Hvis du bruker en eldre versjon av Excel-dette er på kategorien Avansert.
- Se «Deaktivere automatisk samling av Dato/Tid kolonner i Pivottabeller» – avkrysningsruten.
- Klikk på OK.
Her er et skjermbilde av innstillingen i Excel Alternativer-vinduet.,
Hvis du er på en eldre versjon av Excel 2016 deretter denne innstillingen er på Avansert-kategorien i alternativer for Excel. Her er et skjermbilde.
Hvis du vil deaktivere den automatiske gruppering funksjonen kan du fortsatt bruke Konsernet har for pivottabeller. Gruppene vil IKKE bli opprettet automatisk når du drar et felt til et område i pivot-tabell, men du kan klikke på Gruppe-knappen på Analyser-fanen for å hente opp den Gruppen Feltet menyen.
Hvor er den gruppen felt som er lagret?,
Selv om vi ser en ny Dato/Klokkeslett gruppe felt i felt-listen, disse feltene er IKKE skapt i kildedataene. De er faktisk lagret i pivot-buffer i bakgrunnen.
Dette kan være gode eller dårlige.
Pivot tabeller kan dele en pivot-buffer hvis de deler den samme datakilden område eller en tabell. Det betyr at alle pivot tabeller som deler buffer, vil også dele grupperinger.
Så, hvis du konsernet et dato-felt i en pivottabell, kan du få uventede resultater i en annen pivot-tabell., Andre pivot tabeller som deler den samme pivot cache kan også endre. Dette kan føre til frustrasjon og forvirring.
Dessverre, det er ingen måte å se hvilke pivot tabeller dele samme buffer i Excel-programmet. Vi kan bruke VBA for dette.
Du kan ikke dele hurtigbufferen ved å endre datakilde utvalg, men dette kan bli rotete hvis du har en masse av pivot tabeller. Min PivotPal Add-i har også en funksjon som viser en liste over alle pivot tabeller i arbeidsboken og tilhørende pivot cache.,
Så den største fordelen med å bruke pivottabell-gruppen feltet funksjonen er at den er rask og enkel. Hvis du bare trenger en rask pivot tabell rapporten etter år, kvartal eller måned, så dette er en enkel løsning. Nye funksjoner i pivot diagrammer også tillate oss å bore ned/opp (vis/skjul) på gruppert dato-feltene. Vi kan raskt gå fra en årlig trend, til kvartalsvis til månedlig i et diagram med å klikke på en knapp.,
#2 – Proffene & Ulemper med Gruppering Datoer i kildedataene
Det andre alternativet er å legge til kolonner til en datakilde som kan beregne grupper. Jeg forklarte dette i mer detalj i min artikkel om gruppering ganger i Excel.
Vi kan bruke TEKST-funksjonen eller noen av dato funksjoner (ÅR, MÅNED, DAG) til å beregne tekst eller numeriske verdi som representerer en gruppering.,
Dette betyr at gruppert feltene vil faktisk være kolonner i en datakilde, og bare lagret i pivot-buffer.
Hvorfor er dette viktig? Vel, det gir oss mer fleksibilitet til å gjøre endringer i våre grupper. Hvis du bruker gruppering funksjon i pivottabellen, da oppløsningen av gruppen eller legge til nye grupperinger, kan du få uventede resultater til andre pivot tabeller som deler buffer.,
Hvis vi legger til en ny dato gruppering kildedataene av pivot, så pivot tabeller som bruker som kilde data vil ikke nødvendigvis bli påvirket eller endret.
en Annen fordel med gruppering datoer i kildedataene er når selskapet rapporterer på et regnskapsår som er forskjellig fra det kalenderår. I dette tilfellet kan vi kanskje bruke en formel til å beregne kvartalene eller regnskapsåret måneder. Vi kan legge til denne formelen som en ny kolonne i datakilden, og deretter legge til at feltet til pivot-tabell.,
gruppen felt funksjonen i pivottabellen IKKE beregne regnskapsåret kalendere.
en Annen fordel er at vi ønsker kanskje å bruke den datoen konsernet kolonner i andre oppsummering rapporter, formler, eller analyse utsiden av pivot-tabell.
En stor ulempe her er sortering. Pivottabellen ikke alltid gjenkjenner den riktige rekkefølgen av fiscal måneder, og vi må kanskje legge regnskapsmåneden nummeret til fronten av måneden navn.
#3 – Hva om Power Pivot?,
Power Pivot hjelper til med dette sortering og gruppering problemet gjennom bruk av kalender bord. Kalender tabeller tabeller som inneholder en liste over datoer for hele perioden av våre data. De inneholder også flere kolonner for dato grupperinger.
Vi kan da bruke Power Pivot å opprette relasjoner mellom våre data set(s) og kalender tabellen for gruppe datoene. Dette ligner en VLOOKUP tilnærming i Excel, og bli med i databasen vilkår.,
kalender tabell tilnærming er ikke å legge til kolonner til den opprinnelige kilden data-tabellen, og det er ikke å anvende gruppering i pivot-buffer. I stedet bruker de relasjoner (delta) som er opprettet i Kraft Drei for å utføre gruppere på fly.
jeg anser dette for å være litt av en hybrid tilnærming som er mer inline med gruppering datoer i kildedataene. Imidlertid, Power Pivot har et Sortere etter Kolonne funksjon som lar deg sortere kalenderen tabell for regnskapsåret quarters, måneder, uker, etc.,
Vi kan også lage kalenderen tabeller ved hjelp av Power-Spørring. Og bruk deretter oppslag-formler for å dra feltet grupperinger i vår kilde data. Eller bruk den fusjonere funksjon av Strøm Spørring til å gjøre oppslag. Kassen min artikkel på Kalenderen Tabell Forklart for mer informasjon.
Her er artikkelen fra min venn Ken Pulserer på hvordan du kan lage en dynamisk kalender tabellen i Kraft Spørring.
Dato Gruppering Metode Sammenligningen Liste
Wow! Det er mange alternativer! Og litt av en krøllete skjorte., La oss jern ut i en organisert liste… 🙂
Pivot-Tabell-Gruppen Feltet Funksjon
- Pro – Rask og enkel å lage dato grupperinger med den innebygde-i Samling-menyen.
- Pro/Con – Pivot tabeller som deler den samme buffer vil også dele samme grupperinger.
- Pro – Bore opp/ned (skjul/utvid) dato gruppe felt i Pivot Diagrammer med knapper.
- Con – Excel-2016 automatisk grupper dato felt, så de trenger å bli opphevet manuelt hvis du ikke vil gruppere.,
Legg til Dato Kolonner til Kilden Data
- Pro – Dato kolonner er lagt til en datakilde, og som kan brukes i andre analyser utenfor en pivot-tabell.
- Con – kildedataene inneholder flere kolonner og legger til fil. Kan være ineffektiv for store datasett.
- Pro – Kan beregne regnskapsåret quarters, måneder, uker, for regnskapsåret kalendere at ikke start på januar 1st.
- Con – Sortering regnskapsåret tidsperioder kan være utfordrende og krever ekstra tall i tekst basert datoer.,
Power Pivot Dato Tabeller
- Pro – Dato grupperinger skapes gjennom relasjoner gjør rapportering mer effektiv og fleksibel ved å holde bordene smal.
- Pro – En dato tabellen kan gjenbrukes med flere datasett, og som brukes til å opprette relasjoner mellom flere (faktisk) data tabeller.
- Pro/Con – Krever at du oppretter en separat dato bordet. Makt-Spørring kan bidra til å gjøre dette enkelt.
- Con – Krever bruk av Power Pivot. Alle brukere vil ha Power Pivot installert for å endre eller samhandle med pivot tabeller.
Hva gjorde jeg savner?,
jeg tror ikke det er noe rett eller galt svar her. Metoden du velger, vil avhenge av dine behov og prosess.