Om een evenwichtig team qua leeftijdsverdeling op te bouwen is het gewenst om inzicht te verkrijgen in de leeftijdsverdeling. Met de ingebouwde Power Query tool in Excel is het mogelijk om extracten of bestanden te importeren in Excel en transformaties uit te voeren die leiden tot een grafiek/tabel waarin de leeftijdsopbouw naar leeftijdscategorie kan worden weergegeven.
Opzet van het model
Het bronbestand beslaat meerdere kolommen, maar de belangrijkste kolommen zijn geboortedatum en geslacht. De gegevens zijn opgemaakt als een Excel-tabel met de naam DataEmployees.
>> Lees ook: Excel-tutorial: Leeftijd berekenen
Importeren van het bestand
U start met een nieuw bestand en uit het Lint selecteert u: Gegevens > Gegevens ophalen en transformeren > Gegevens ophalen > Uit bestand > Uit werkmap. U zoekt het bestand op, selecteert deze en klik op Importeren. U ziet dan het volgende dialoogvenster:
U selecteert DataEmployees en klikt op Gegevens transformeren waarna u in de Power Query-omgeving komt.
Transformeren van de data
U doorloopt de volgende transformatiestappen.
Berekenen leeftijd
Maak een kopie van de kolom geboortedatum: Kies uit het Lint: Kolom toevoegen > Aangepaste kolom. Vul deze als volgt en klik dan op OK.
>> Lees ook: 7 simpele Excel-tips die tijd besparen
Markeer kolom Leeftijd dagen en uit het Lint kiest u: Transformeren > Kolom Datum en tijd > Datum > Leeftijd. Er wordt daarop een nieuwe kolom Leeftijd aangemaakt.
Klik op het klokje-symbool in de kolom Leeftijd en verander deze in Geheel getal.
Voeg nu een kolom toe om de leeftijd in jaren te berekenen. Uit het Lint kiest u: Kolom toevoegen > Aangepaste kolom. Vul deze als volgt en klik dan op OK:
Verander de opmaak van de kolom Leeftijd jaren in een Geheel getal.
>> Lees ook: Excel-tutorial: Weeknummer omzetten naar datum
Met ingedrukte Control-toets markeert u nu de kolommen die uw wenst te behouden, te weten Geboortedatum, Geslacht en Leeftijd jaren, waarna u met de rechtermuisknop klikt. Uit het verkorte menu kiest u: Andere kolommen verwijderen.
Toevoegen leeftijdscategorie
We wensen de volgende zes leeftijdscategorieën aan te maken:
- Tot en met 25 jaar
- 26-35 jaar
- 36-45 jaar
- 46-55 jaar
- 56-65 jaar
- Ouder van 65 jaar
In Power Query kiest u uit het Lint: Kolom Toevoegen > Voorwaardelijke kolom, waarna u het dialoogvenster als volgt invult en op OK klikt.
Verander de opmaak van de nieuwe kolom in tekst.
>> Lees ook: Tony De Jonker: ‘Wat is het verhaal achter een rapport?’
U bent nu klaar met de transformaties en kunt Power Query verlaten via: Start > Sluiten > Sluiten en laden > Sluiten en laden naar… > Alleen verbinding maken > OK.
Nu bent u terug aan de voorkant van Excel en aan de rechterkant ziet u het Query-paneel met de gemaakte query.
Maken draaitabel-draaigrafiek en slicer
U kunt een draaitabel maken door met de rechtermuisknop op de query te klikken. Uit het verkorte menu kiest u: Laden naar…, waarna u kiest: Draaitabelrapport > Nieuw werkblad > OK.
U maakt de volgende indeling:
>> Lees ook: Praktische tips voor het werken met draaitabellen
U kunt de rijen in een draaitabel verplaatsen en een draaigrafiek annex slicer toevoegen voor het geslacht. Het dashboard ziet er dan als volgt uit:
Auteur: Tony De Jonker
Dit artikel is verschenen in cm: 2021, afl. 6.
Hier leest u meer Excel tips en trucs door Tony De Jonker
Geef een antwoord