bundlinie: Lær forskellene mellem Gruppedatoerfunktionen i en pivottabel og gruppering af datoer i kildedataene ved at tilføje ekstra kolonner for år, måned, kvartal osv.
færdighedsniveau: mellemprodukt
en af de mange grunde til, at pivottabeller er fantastiske, er, at de giver os mulighed for hurtigt at gruppere datoer for at oprette sammenfattende rapporter. Hvad betyder grupperingsdatoer?, Godt, det betyder bare, at vi grupperer alle datoværdier i en kolonne i et år, kvartal, Måned, Dag, etc.
Hvis vi har transaktionsniveaudata med en kolonne, der indeholder transaktionsdatoen, vil vi måske gruppere datoerne for at rapportere om dataene efter år, kvartal eller måned.
Der er tre måder at gå om datogrupperingen i E .cel:
- Vi kan bruge Gruppefeltfunktionen i pivottabellen.
- Vi kan oprette yderligere kolonner i kildedataene med tekst eller numeriske værdier ved hjælp af formler.,
- Vi kan skabe relationer mellem tabeller med opslag formler eller Po .er Pivot.
er den ene bedre end den anden? Ikke nødvendigvis. Ligesom alt i E .cel er der altid et par forskellige måder at udføre en opgave på. Den metode, du bruger her, afhænger af dine behov og din virksomheds skattekalender.
#1 – Pro & ulemper ved Gruppefeltfunktionen
Gruppefeltfunktionen giver os mulighed for hurtigt at gruppere et felt med datoer i år, kvartaler, måneder osv.,
dette betyder, at kildedataene ikke behøver at indeholde kolonner med år, kvartal eller måned navn. Gruppefeltfunktionen opretter automatisk grupperingerne for os og opsummerer dataene i hver gruppe.
Når vi har oprettet grupperne i Grupperingsmenuen, tilføjes nye felter til pivottabellefeltlisten.
sådan grupperes & grupper felter
Her er en hurtig guide til, hvordan du grupperer felter i dine pivottabeller.,
- Vælg en celle i den række eller Kolonne område af den pivottabel, der indeholder det felt, du vil gruppere. Dette er normalt et datofelt, men kan også være tal.
- vælg fanen analyser / indstillinger i båndet.
- Klik på Gruppefeltknappen. Tjek denne artikel, hvis Gruppefeltknappen er deaktiveret.
- Vælg de elementer, du vil gruppere feltet efter. For datofelter er dette år, kvartaler, Måneder, Dage, Timer, Minutter, Sekunder.,
- Klik på OK.
feltet vil blive grupperet, og nye felter vil blive tilføjet til felterne liste for grupperne.
for at opdele feltet:
- vælg en celle i pivottabellen for det grupperede felt.
- vælg fanen analyser / indstillinger i båndet.
- tryk på knappen Ungroup (placeret over Gruppeknappen).
Automatisk dato/tid gruppering mulighed
i e .cel 2016 sker denne gruppering automatisk, når du tilføjer et datofelt til et af områderne i pivottabellen. Dette er en indstilling, der nu kan tændes eller slukkes i menuen E .cel-indstillinger.,
Sådan deaktiveres automatisk datogruppering for pivottabeller:
- gå til fil > Indstillinger I E .cel for at åbne e .cel-indstillingsvinduet.
- Klik på fanen Data i venstre sidepanel. Hvis du bruger en ældre version af E .cel, er dette på fanen Avanceret.marker afkrydsningsfeltet “Deaktiver automatisk gruppering af dato / klokkeslæt kolonner i PivotTables”.
- Klik på OK.
Her er et skærmbillede af indstillingen i E .cel-indstillingsvinduet.,
Hvis du er på en ældre version af Excel til 2016, så denne indstilling er på Avanceret-fanen i Excel-indstillinger. Her er et skærmbillede.
Hvis du deaktiverer den automatiske grupperingsfunktion, kan du stadig bruge Gruppefunktionen til pivottabeller. Grupperne oprettes ikke automatisk, når du trækker et felt til et område i pivottabellen, men du kan klikke på Gruppeknappen på fanen analyser for at få vist Gruppefeltmenuen.
hvor gemmes gruppefelterne?,
selvom vi ser de nye dato/tid gruppefelter i feltlisten, oprettes disse felter ikke i kildedataene. De er faktisk gemt i pivot cache i baggrunden.
dette kan være godt eller dårligt.
pivottabeller kan dele en pivot-cache, hvis de deler det samme Kildedataområde eller tabel. Det betyder, at alle pivottabeller, der deler cachen, også vil dele grupperingerne.så hvis du grupperer et datofelt i en pivottabel, kan du få uventede resultater i en anden pivottabel., Dine andre pivottabeller, der deler den samme pivot-cache, kan også ændres. Dette kan forårsage frustration og forvirring.
Desværre er der ingen måde at se, hvilke pivottabeller der deler den samme cache i e .cel-applikationen. Vi kan bruge VBA til dette.
Du kan fjerne cachen ved at ændre kildedataområdet, men det kan blive rodet, hvis du har mange pivottabeller. Min PivotPal-tilføjelse har også en funktion, der viser en liste over alle pivottabellerne i projektmappen og den tilhørende pivot-cache.,
så den største fordel ved at bruge funktionen pivot table group field er, at det er hurtigt og nemt. Hvis du bare har brug for en hurtig pivottabelrapport efter år, kvartal eller måned, er dette en nem løsning. Nye funktioner i pivot diagrammer giver os også mulighed for at bore ned/op (udvid/skjul) på de grupperede datofelter. Vi kan hurtigt gå fra en årlig tendens, til kvartalsvis, til månedligt i et diagram med et klik på en knap.,
#2 – profferne & ulemper ved gruppering af datoer i kildedataene
den anden mulighed er at tilføje kolonner til kildedataene, der beregner grupperne. Jeg forklarede dette mere detaljeret i min artikel om grupperingstider i E .cel.
Vi kan bruge tekstfunktionen eller nogle af datofunktionerne (År, Måned, Dag) til at beregne den tekst eller numeriske værdi, der repræsenterer grupperingen.,
dette betyder, at de grupperede felter faktisk vil være kolonner i kildedataene og bare gemmes i pivot-cachen.
hvorfor er dette vigtigt? Nå det giver os mere fleksibilitet til at foretage ændringer i vores grupper. Hvis du bruger grupperingsfunktionen i pivottabellen, kan ungrouping eller tilføjelse af nye grupperinger forårsage uventede resultater til andre pivottabeller, der deler cachen.,
Hvis vi tilføjer en ny datogruppe til kildedataene for pivoten, vil de pivottabeller, der bruger disse kildedata, ikke nødvendigvis blive påvirket eller ændret.
en anden fordel ved at gruppere datoer i kildedataene er, når din virksomhed rapporterer om et regnskabsår, der adskiller sig fra kalenderåret. I dette tilfælde skal vi muligvis bruge en formel til at beregne kvartaler eller finanspolitiske måneder. Vi kan tilføje denne formel som en ny kolonne i kildedataene og derefter tilføje dette felt til pivottabellen.,
funktionen gruppefelter i pivottabellen beregner ikke finanspolitiske kalendere.
en anden fordel er, at vi måske ønsker at genbruge datogruppens kolonner i andre sammenfattende rapporter, formler eller analyser uden for pivottabellen.
en stor ulempe her er sortering. Pivottabellen genkender ikke altid den rigtige rækkefølge af dine finanspolitiske måneder, og vi skal muligvis tilføje finansmånedenummeret til forsiden af månedens navn.
#3 – Hvad med Po ?er Pivot?,
Po .er Pivot hjælper med denne sortering og gruppering problem ved hjælp af kalender tabeller. Kalender tabeller er tabeller, der indeholder en liste over datoer for hele tidsperioden for vores data. De indeholder også yderligere kolonner til datogrupperingerne.
Vi kan derefter bruge Po .er Pivot til at oprette forhold mellem vores datasæt og kalendertabellen for at gruppere datoerne. Dette svarer til en VLOOKUP tilgang i E .cel, og en deltage i database vilkår.,
kalendertabelmetoden tilføjer ikke nogen kolonner til den originale kildedatatabel, og den anvender ikke grupperingen i pivot-cachen. I stedet bruger den de relationer (join), der er oprettet i Po .er Pivot, til at udføre grupperingen på farten.
jeg anser dette for at være lidt af en hybrid tilgang, der er mere inline med gruppering datoer i kildedataene. Po .er Pivot har dog en sortering efter Kolonnefunktion, der giver dig mulighed for at sortere din kalendertabel for finanspolitiske kvartaler, måneder, uger osv.,
Vi kan også oprette kalendertabeller ved hjælp af Po .er Queryuery. Og brug derefter opslagsformler til at trække feltgrupperingerne ind i vores kildedata. Eller brug fusionsfunktionen i Po .er Queryuery til at foretage opslag. Kassen min artikel på kalenderen tabellen forklaret for flere detaljer.
Her er artikel af min ven Ken Puls om, hvordan man opretter en dynamisk kalendertabel i Po .er Queryuery.
Dato gruppering metode sammenligningsliste
Wowo!! Det er mange muligheder! Og lidt af en rynket skjorte., Lad os stryge det ud i en organiseret liste …
pivottabel Gruppefelt funktion
- Pro – hurtig og nem at oprette datogrupperinger med den indbyggede Grupperingsmenu.
- Pro / Con – pivottabeller, der deler den samme cache, deler også de samme grupperinger.
- Pro-Bor op/ned (skjul / Udvid) datogruppefelter i pivotdiagrammer med knapper.
- Con-e .cel 2016 grupperer automatisk datofelter, så de skal afgrupperes manuelt, hvis du ikke ønsker grupperingen.,
Tilføj Datokolonner til kildedataene
- Pro – Datokolonner føjes til kildedataene og kan bruges i anden analyse uden for en pivottabel.
- Con – Source data indeholder flere kolonner og tilføjer til filen. Kan være ineffektiv for store datasæt.
- Pro – kan beregne finanspolitiske kvartaler, måneder, uger, for finanspolitiske kalendere, der ikke starter den 1. januar.
- Con – sortering de finanspolitiske tidsperioder kan være udfordrende og kræver ekstra numre i tekstbaserede datoer.,
Po .er Pivot Date Tables
- Pro – Date grupperinger oprettet gennem relationer gør rapportering mere effektiv og fleksibel ved at holde tabeller smalle.
- Pro-en datatabel kan genbruges med flere datasæt og bruges til at oprette forhold mellem flere (fakta) datatabeller.
- Pro / Con-kræver oprettelse af en separat datatabel. Po .er Queryuery kan hjælpe med at gøre det nemt.
- Con-kræver brug af Po .er Pivot. Alle brugere skal have Po .er Pivot installeret for at ændre eller interagere med pivottabellerne.
Hvad gik jeg glip af?,
Jeg tror ikke, at der er noget rigtigt eller forkert svar her. Den metode, du vælger, afhænger af dine behov og proces.