nedre raden: lär dig skillnaderna mellan Gruppdatumfunktionen i en pivottabell och grupperingsdatum i källdata genom att lägga till extra kolumner för år, månad, kvartal etc.
färdighetsnivå: mellanliggande
en av de många anledningarna till att pivottabeller är fantastiska är att de tillåter oss att snabbt gruppera Datum för att skapa sammanfattningsrapporter. Vad betyder grouping dates?, Tja, det betyder bara att vi grupperar alla datumvärden i en kolumn i ett år, kvartal, månad, dag etc.
om vi har transaktionsnivådata med en kolumn som innehåller transaktionsdatumet kanske vi vill gruppera datumen för att rapportera data per år, kvartal eller månad.
det finns tre sätt att gå om datumgrupperingen i Excel:
- Vi kan använda Gruppfältsfunktionen i pivottabellen.
- vi kan skapa ytterligare kolumner i källdata med text-eller numeriska värden med hjälp av formler.,
- vi kan skapa relationer mellan tabeller med lookup formler eller Power Pivot.
är en bättre än den andra? Inte nödvändigtvis. Liksom allt i Excel finns det alltid några olika sätt att utföra en uppgift. Den metod du använder här beror på dina behov och ditt företags skattekalender.
#1 – Pro& nackdelar med Gruppfältsfunktionen
Gruppfältsfunktionen gör det möjligt för oss att snabbt gruppera ett datumfält i år, kvartal, månader etc.,
detta innebär att källdata inte behöver innehålla kolumner med år, kvartal eller månad namn. Funktionen Gruppfält skapar automatiskt grupperingarna för oss och sammanfattar data i varje grupp.
När vi har skapat grupperna på Grupperingsmenyn läggs nya fält till i pivottabellfältlistan.
så här grupperar du& Ungroup Fields
här är en snabbguide om hur du grupperar fält i pivottabellerna.,
- välj en cell i området rader eller kolumner i pivottabellen som innehåller det fält du vill gruppera. Detta är vanligtvis ett datumfält, men kan också vara siffror.
- välj fliken Analysera / alternativ i menyfliksområdet.
- Klicka på knappen Gruppfält. Kolla in den här artikeln om knappen Gruppfält är inaktiverad.
- Välj de objekt som du vill gruppera fältet efter. För datumfält är detta år, kvartal, månader, dagar, Timmar, Minuter, sekunder.,
- Klicka på OK.
fältet grupperas och nya fält läggs till i fältlistan för grupperna.
för att gruppera fältet:
- välj en cell i pivottabellen för det grupperade fältet.
- välj fliken Analysera / alternativ i menyfliksområdet.
- tryck på Ungroup-knappen (ovanför Gruppknappen).
alternativ för automatisk datum – /Tidsgruppering
i Excel 2016 sker denna gruppering automatiskt när du lägger till ett datumfält i ett av områdena i pivottabellen. Det här är en inställning som nu kan slås på eller av i Excel-alternativmenyn.,
Så här inaktiverar du automatisk datumgruppering för pivottabeller:
- gå till fil> alternativ i Excel för att öppna fönstret Excel-alternativ.
- Klicka på fliken Data i det vänstra sidofältet. Om du använder en äldre version av Excel är detta på fliken Avancerat.
- markera kryssrutan ”Inaktivera automatisk gruppering av datum – / tidskolumner i PivotTables”.
- Klicka på OK.
här är en skärmdump av inställningen i Excel Options-fönstret.,
om du är på en äldre version av Excel 2016 så är den här inställningen på fliken Avancerat i Excel-alternativ. Här är en skärmdump.
om du inaktiverar funktionen för automatisk gruppering kan du fortfarande använda gruppfunktionen för pivottabeller. Grupperna skapas inte automatiskt när du drar ett fält till ett område i pivottabellen, men du kan klicka på Gruppknappen på fliken Analysera för att visa Gruppfältmenyn.
var lagras gruppfälten?,
även om vi ser de nya datum – /Tidsgruppfälten i fältlistan skapas inte dessa fält i källdata. De lagras faktiskt i pivotcachen i bakgrunden.
detta kan vara bra eller dåligt.
pivottabeller kan dela en pivotcache om de delar samma källdataintervall eller tabell. Det innebär att alla pivottabeller som delar cacheminnet också delar grupperingarna.
om du grupperar ett datumfält i en pivottabell kan du få oväntade resultat i en annan pivottabell., Dina andra pivottabeller som delar samma pivotcache kan också ändras. Detta kan orsaka frustration och förvirring.
tyvärr finns det inget sätt att se vilka pivottabeller som delar samma cache i Excel-programmet. Vi kan använda VBA för detta.
Du kan frigöra cacheminnet genom att ändra källdataintervallet, men det kan bli rörigt om du har många pivottabeller. Mitt PivotPal-tillägg har också en funktion som visar en lista över alla pivottabeller i arbetsboken och den tillhörande pivotcachen.,
så den största fördelen med att använda funktionen pivot table group field är att det är snabbt och enkelt. Om du bara behöver en snabb pivot tabellrapport för år, kvartal eller månad, då detta är en enkel lösning. Nya funktioner i pivotdiagram tillåter oss också att borra ner / upp (expandera/kollapsa) på de grupperade datumfälten. Vi kan snabbt gå från en årlig trend, till kvartalsvis, till månadsvis i ett diagram med ett klick på en knapp.,
#2 – proffsen & nackdelar med att gruppera datum i källdata
det andra alternativet är att lägga till kolumner i källdata som beräknar grupperna. Jag förklarade detta mer detaljerat i min artikel om grupperingstider i Excel.
Vi kan använda textfunktionen eller några av datumfunktionerna (År, Månad, Dag) för att beräkna texten eller det numeriska värdet som representerar grupperingen.,
det betyder att de grupperade fälten faktiskt kommer att vara kolumner i källdata och bara lagras i pivotcachen.
Varför är detta viktigt? Det ger oss större flexibilitet att göra ändringar i våra grupper. Om du använder grupperingsfunktionen i pivottabellen kan ungrouping eller lägga till nya grupperingar orsaka oväntade resultat i andra pivottabeller som delar cachen.,
om vi lägger till en ny datumgruppering till källdata i pivot, kommer pivottabellerna som använder källdata inte nödvändigtvis att påverkas eller ändras.
en annan fördel med att gruppera datum i källdata är när ditt företag rapporterar ett räkenskapsår som skiljer sig från kalenderåret. I det här fallet kan vi behöva använda en formel för att beräkna kvartal eller finanspolitiska månader. Vi kan lägga till denna formel som en ny kolumn i källdata och sedan lägga till det fältet i pivottabellen.,
gruppfälten i pivottabellen beräknar inte finanspolitiska kalendrar.
en annan fördel är att vi kanske vill återanvända datumgruppskolumnerna i andra sammanfattningsrapporter, formler eller analyser utanför pivottabellen.
en stor nackdel här är sortering. Pivottabellen känner inte alltid igen rätt ordning för dina finanspolitiska månader, och vi kan behöva lägga till skattemånadernumret på framsidan av månadens namn.
#3 – Vad sägs om Power Pivot?,
Power Pivot hjälper till med detta sorterings-och grupperingsproblem genom att använda kalendertabeller. Kalendertabeller är tabeller som innehåller en lista över Datum för hela tidsperioden för våra data. De innehåller också ytterligare kolumner för datumgrupperingar.
Vi kan sedan använda Power Pivot för att skapa relationer mellan våra datamängder och kalendertabellen för att gruppera datumen. Detta liknar en VLOOKUP strategi i Excel, och en koppling i Databas termer.,
kalendertabellen lägger inte till några kolumner i den ursprungliga källdatatabellen, och den tillämpar inte grupperingen i pivotcachen. Istället använder den de relationer (join) som skapas i Power Pivot för att utföra grupperingen i farten.
jag anser att detta är lite av en hybridstrategi som är mer inline med grupperingsdatum i källdata. Power Pivot har dock en Sortera efter Kolumnfunktion som låter dig Sortera kalendertabellen för skattekvarter, månader, veckor etc.,
Vi kan också skapa kalendertabeller med hjälp av Power Query. Och sedan använda lookup formler för att dra fältgrupperingar i vår källdata. Eller använd kopplingsfunktionen i Power Query för att göra uppslagningarna. Kassan min artikel på kalendern tabellen förklaras För mer information.
här är artikeln av min vän Ken Puls om hur man skapar en dynamisk kalendertabell i Power Query.
Datumgrupperingsmetod jämförelse lista
Wow! Det är många alternativ! Och lite skrynklig skjorta., Låt oss stryka ut det i en organiserad lista …
Pivot Table Group Field Feature
- Pro – snabb och enkel att skapa datumgrupperingar med den inbyggda Grupperingsmenyn.
- pro / con – Pivot tabeller som delar samma cache kommer också att dela samma grupperingar.
- pro – Drill up/down (collapse / expand) datumgruppfält i pivotdiagram med knappar.
- con – Excel 2016 grupperar automatiskt datumfält, så de måste vara grupperade manuellt om du inte vill att grupperingen.,
Lägg till Datumkolumner i källdata
- pro – Datumkolumner läggs till källdata och kan användas i annan analys utanför en pivottabell.
- Con – källdata innehåller fler kolumner och lägger till filen. Kan vara ineffektivt för stora datamängder.
- Pro-kan beräkna finanspolitiska kvartal, månader, veckor, för finanspolitiska kalendrar som inte startar den 1 januari.
- Con-sortering de finanspolitiska tidsperioderna kan vara utmanande och kräva extra nummer i de textbaserade datumen.,
Power Pivot datum tabeller
- pro – Date grupperingar som skapats genom relationer gör rapportering mer effektiv och flexibel genom att hålla tabeller smal.
- pro – One datumtabell kan återanvändas med flera datamängder och användas för att skapa relationer mellan flera (fakta) datatabeller.
- Pro / Con – kräver att du skapar en separat datumtabell. Power Query kan hjälpa till att göra det enkelt.
- Con – kräver att du använder Power Pivot. Alla användare måste ha Power Pivot installerat för att ändra eller interagera med pivottabellerna.
vad missade jag?,
Jag tror inte att det finns något rätt eller fel svar här. Den metod du väljer beror på dina behov och processer.