Bottom line: leer de verschillen tussen de Groepsdatums functie in een draaitabel en groepeer datums in de brongegevens door extra kolommen toe te voegen voor jaar, maand, kwartaal, enz.
vaardigheidsniveau: Intermediate
een van de vele redenen dat draaitabellen geweldig zijn, is dat ze ons in staat stellen om snel data te groeperen om samenvattende rapporten te maken. Wat betekent het groeperen van data?, Nou, het betekent gewoon dat we alle datumwaarden in een kolom groeperen in een jaar, kwartaal, maand, dag, enz.
als we gegevens op transactieniveau hebben met een kolom die de transactiedatum bevat, dan willen we misschien de datums groeperen om de gegevens te rapporteren per jaar, kwartaal of maand.
Er zijn drie manieren om te gaan over de datumgroepering in Excel:
- We kunnen de functie groepsveld van de draaitabel gebruiken.
- We kunnen extra kolommen in de brongegevens maken met tekst of numerieke waarden met behulp van formules.,
- We kunnen relaties maken tussen tabellen met opzoekformules of Power Pivot.
Is de ene beter dan de andere? Niet noodzakelijk. Zoals alles in Excel, zijn er altijd een paar verschillende manieren om een taak uit te voeren. De methode die u hier gebruikt, hangt af van uw behoeften en de fiscale kalender van uw bedrijf.
#1 – De Pro & nadelen van de functie groepsveld
met de functie groepsveld kunnen we snel een veld met datums groeperen in jaar, kwartalen, maanden, enz.,
Dit betekent dat de brongegevens geen kolommen hoeven te bevatten met de naam van het jaar, kwartaal of maand. De functie groepsveld maakt automatisch de groepen voor ons en vat de gegevens in elke groep samen.
nadat we de groepen in het groeperen menu hebben gemaakt, worden nieuwe velden toegevoegd aan de lijst met draaitabelvelden.
hoe te groeperen & Ungroup Fields
Hier is een korte handleiding voor het groeperen van velden in uw draaitabellen.,
- selecteer een cel in het gebied rijen of kolommen van de draaitabel die het veld bevat dat u wilt groeperen. Dit is meestal een datumveld, maar kan ook getallen zijn.
- Selecteer het tabblad Analyseren / opties in het lint.
- klik op de knop groepsveld. Bekijk dit artikel als de knop groepsveld is uitgeschakeld.
- Selecteer de items waarmee u het veld wilt groeperen. Voor datumvelden zijn dit jaren, kwartalen, Maanden, Dagen, Uren, Minuten, Seconden.,
- klik op OK.
het veld zal worden gegroepeerd en nieuwe velden zullen worden toegevoegd aan de lijst Velden voor de groepen.
om het veld te degroeperen:
- selecteer een cel in de draaitabel voor het gegroepeerde veld.
- Selecteer het tabblad Analyseren / opties in het lint.
- druk op de knop groeperen (boven de knop Groep).
Automatische Datum / Tijdgroepering optie
in Excel 2016 deze groepering gebeurt automatisch wanneer u een datumveld toevoegt aan een van de gebieden in de draaitabel. Dit is een instelling die nu in-of uitgeschakeld kan worden in het menu Excel-opties.,
zo kunt u automatische datumgroepering voor draaitabellen uitschakelen:
- ga naar Bestand > opties in Excel om het venster Excel-opties te openen.
- klik op het tabblad Data in de linkerzijbalk. Als u een oudere versie van Excel gebruikt, staat dit op het tabblad Geavanceerd.
- Vink het selectievakje “Automatische groepering van datum/tijd-kolommen in draaitabellen uitschakelen” aan.
- klik op OK.
Hier is een schermafbeelding van de instelling in het venster Excel-opties.,
Als u een oudere versie van Excel 2016 gebruikt, bevindt deze instelling zich op het tabblad Geavanceerd van Excel-opties. Hier is een screenshot.
Als u de functie automatisch groeperen uitschakelt, kunt u de functie groep nog steeds gebruiken voor draaitabellen. De groepen worden niet automatisch gemaakt wanneer u een veld naar een gebied in de draaitabel sleept, maar u kunt op de klikken Groep knop op de analyseren tab om het menu groepsveld te openen.
waar worden de groepsvelden opgeslagen?,
hoewel we de nieuwe datum/tijd groepsvelden in de veldlijst zien, worden deze velden niet aangemaakt in de brongegevens. Ze zijn eigenlijk opgeslagen in de pivot cache op de achtergrond.
Dit kan goed of slecht zijn.
draaitabellen kunnen een draaicache delen als ze hetzelfde brongegevensbereik of dezelfde tabel delen. Dat betekent dat alle draaitabellen die de cache delen, ook de groepen Delen.
dus als u een datumveld groepeert in één draaitabel, kunt u onverwachte resultaten krijgen in een andere draaitabel., Uw andere draaitabellen die dezelfde draaitabelcache delen, kunnen ook veranderen. Dit kan frustratie en verwarring veroorzaken.
helaas is er geen manier om te zien welke draaitabellen dezelfde cache delen in de Excel-toepassing. We kunnen VBA hiervoor gebruiken.
u kunt het delen van de cache ongedaan maken door het brongegevensbereik te wijzigen, maar dit kan rommelig worden als u veel draaitabellen hebt. Mijn PivotPal-invoegtoepassing heeft ook een functie die een lijst met alle draaitabellen in de werkmap en de bijbehorende draaitabelcache toont.,
dus het belangrijkste voordeel van het gebruik van de pivot table group field functie is dat het snel en eenvoudig is. Als u alleen een snel draaitabelrapport per jaar, kwartaal of maand nodig hebt, is dit een eenvoudige oplossing. Met nieuwe functies in draaitabellen kunnen we ook naar beneden/omhoog boren (uitbreiden/samenvouwen) op de gegroepeerde datumvelden. We kunnen snel van een jaarlijkse trend gaan, naar driemaandelijks, naar maandelijks in een grafiek met een klik op een knop.,
#2 – De voors & nadelen van het groeperen van datums in de brongegevens
de andere optie is om kolommen toe te voegen aan de brongegevens die de groepen berekenen. Ik heb dit in meer detail uitgelegd in mijn artikel over het groeperen van tijden in Excel.
We kunnen de tekstfunctie of enkele van de datumfuncties (Jaar, Maand, Dag) gebruiken om de tekst of numerieke waarde te berekenen die de groepering vertegenwoordigt.,
Dit betekent dat de gegroepeerde velden kolommen in de brongegevens zijn en alleen in de draaicache worden opgeslagen.
Waarom is dit belangrijk? Het geeft ons meer flexibiliteit om wijzigingen aan te brengen in onze groepen. Als u de functie groeperen van de draaitabel gebruikt, kan het ongedaan maken of toevoegen van nieuwe groepen onverwachte resultaten opleveren voor andere draaitabellen die de cache delen.,
als we een nieuwe datumgroepering toevoegen aan de brongegevens van de pivot, dan zullen de draaitabellen die die brongegevens gebruiken niet per se worden beïnvloed of gewijzigd.
een ander voordeel van het groeperen van datums in de brongegevens is wanneer uw bedrijf rapporteert over een fiscaal jaar dat verschilt van het kalenderjaar. In dit geval moeten we misschien een formule gebruiken om kwartalen of fiscale maanden te berekenen. We kunnen deze formule toevoegen als een nieuwe kolom in de brongegevens en vervolgens dat veld toevoegen aan de draaitabel.,
De functie groepsvelden in de draaitabel berekent geen fiscale agenda ‘ s.
een ander voordeel is dat we de datumgroepkolommen in andere samenvattende rapporten, formules of analyses buiten de draaitabel willen hergebruiken.
een groot nadeel hier is Sorteren. De draaitabel herkent niet altijd de juiste volgorde van uw fiscale maanden en we moeten mogelijk het fiscale maandnummer aan de voorkant van de maandnaam toevoegen.
#3-Hoe zit het met Power Pivot?,
Power Pivot helpt bij het sorteren en groeperen door het gebruik van kalendertabellen. Kalendertabellen zijn tabellen die een lijst met datums bevatten voor de volledige periode van onze gegevens. Ze bevatten ook extra kolommen voor de datumgroepen.
We kunnen dan Power Pivot gebruiken om relaties te creëren tussen onze dataset(s) en de kalendertabel om de datums te groeperen. Dit is vergelijkbaar met een VLOOKUP-aanpak in Excel en een join in databasetermen.,
de benadering van de kalendertabel voegt geen kolommen toe aan de oorspronkelijke brongegevenstabel en past de groepering in de draaicache niet toe. In plaats daarvan maakt het gebruik van de relaties (join) gemaakt in Power Pivot om de groepering uit te voeren op de vlieg.
Ik beschouw dit als een beetje een hybride benadering die meer in lijn is met het groeperen van datums in de brongegevens. Power Pivot heeft echter een functie voor Sorteren op kolom waarmee u uw kalendertabel kunt sorteren voor fiscale kwartalen, maanden, weken, enz.,
We kunnen ook kalendertabellen maken met Power Query. En gebruik dan opzoekformules om de veldgroepen in onze brongegevens te trekken. Of gebruik de functie samenvoegen van Power Query om de lookups uit te voeren. Bekijk mijn artikel over de kalender tabel uitgelegd voor meer details.
Hier is een artikel van mijn vriend Ken Puls over het maken van een dynamische agenda tabel in Power Query.
Datum groeperen methode vergelijkingslijst
Wow! Dat zijn veel opties! En een beetje een gerimpeld shirt., Laten we strijken in een georganiseerde lijst … 🙂
Pivot Table Group Field Feature
- Pro – snel en eenvoudig om datumgroepen te maken met het ingebouwde groepsmenu.
- Pro / Con-draaitabellen die dezelfde cache delen, zullen ook dezelfde groepen Delen.
- Pro-Drill omhoog / omlaag (samenvouwen/uitvouwen) datum groepsvelden in draaitabellen met knoppen.
- Con-Excel 2016 groepeert automatisch datumvelden, dus ze moeten handmatig worden verwijderd als u de groepering niet wilt.,
Datumkolommen toevoegen aan de brongegevens
- pro-Datumkolommen worden toegevoegd aan de brongegevens en kunnen worden gebruikt in andere analyses buiten een draaitabel.
- Con-Source data bevat meer kolommen en voegt toe aan het bestand. Kan inefficiënt zijn voor grote datasets.
- Pro-kan fiscale kwartalen, maanden, weken berekenen voor fiscale kalenders die niet beginnen op 1 januari.
- het aanpassen van de fiscale tijdsperioden kan uitdagend zijn en vereisen extra getallen in de op tekst gebaseerde datums.,
Power Pivot Datumtabellen
- pro – datumgroepen gemaakt door relaties maken rapportage efficiënter en flexibeler door tabellen smal te houden.
- Pro-een datumtabel kan worden hergebruikt met meerdere datasets, en gebruikt worden om relaties te creëren tussen meerdere (fact) datatabellen.
- Pro / Con-vereist het aanmaken van een aparte datumtabel. Power Query kan helpen om dit gemakkelijk te maken.
- Con-vereist het gebruik van Power Pivot. Alle gebruikers moeten Power Pivot geïnstalleerd hebben om de draaitabellen te wijzigen of ermee te communiceren.
wat heb ik gemist?,
ik geloof niet dat er hier een goed of fout antwoord is. De methode die u kiest zal afhangen van uw behoeften en proces.