Excel voor HR: Personeelsgegevens en Analyses

Human Resources-teams verwerken dagelijks enorme hoeveelheden data: personeelsdossiers, aanwezigheidslijsten, prestatiescores, salarisschalen en verloopstatistieken. Excel blijft wereldwijd een van de meest gebruikte tools op HR-afdelingen, juist omdat het flexibel, toegankelijk en krachtig genoeg is om alles aan te kunnen, van een start-up met tien personen tot een onderneming met meerdere vestigingen. Deze gids laat je stap voor stap zien hoe je een praktisch HR-systeem bouwt in Excel, inclusief de belangrijkste sjablonen, formules en analysetechnieken die je nodig hebt om slimmer te werken.

Je masterbestand met personeelsgegevens opzetten

Elk HR-systeem in Excel begint met een schoon, goed gestructureerd masterbestand. Zie dit als je enige bron van waarheid ('single source of truth'). Elke rij vertegenwoordigt één werknemer; elke kolom een kenmerk.

Aanbevolen kolommen voor je masterbestand:

• Werknemers-ID — een unieke identificatiecode (bijv. EMP-001)
• Volledige naam
• Afdeling
• Functietitel
• Startdatum
• Dienstverband (Voltijd, Deeltijd, Contract)
• Naam leidinggevende
• Jaarsalaris
• Status (Actief, Inactief, Met verlof)

Gebruik Gegevensvalidatie om te controleren wat gebruikers kunnen invoeren in kolommen zoals Afdeling, Dienstverband en Status. Dit voorkomt typefouten en houdt je data consistent — een cruciale stap voordat je analyses uitvoert.

Geef je tabel een naam (Invoegen → Tabel, en geef deze een naam zoals tblEmployees). Benoemde tabellen worden automatisch uitgevouwen als je rijen toevoegt en maken je formules veel leesbaarder.

Diensttijd en dienstjaren berekenen

Een van de meest voorkomende HR-berekeningen is de diensttijd van een werknemer. De functie DATEDIF pakt dit elegant aan:

=DATEDIF(B2, TODAY(), "Y") & " years, " & DATEDIF(B2, TODAY(), "YM") & " months"

Waarbij B2 de startdatum van de werknemer bevat. Dit retourneert een leesbare tekstreeks zoals 3 years, 7 months. Als je alleen het aantal volledige jaren nodig hebt voor groeperingsdoeleinden:

=DATEDIF(B2, TODAY(), "Y")

Je kunt werknemers vervolgens indelen in diensttijdschalen met behulp van een IF-functie met geneste logische tests:

=IF(E2<1,"New Hire",IF(E2<3,"Junior",IF(E2<7,"Mid-Level","Senior")))

Waarbij E2 de waarde van de diensttijd in jaren bevat. Deze schalen zijn handig voor personeelsrapportages en retentieanalyses.

Een sjabloon voor aanwezigheidsregistratie bouwen

Een maandelijkse aanwezigheidsregistratie houdt de dagelijkse aanwezigheid van elke werknemer bij. Stel dit zo in dat de werknemers in de rijen staan en de kalenderdagen in de kolommen.

Werknemer	1-jun	2-jun	3-jun	…	Totaal aanwezig	Totaal afwezig	Aanwezigheid %
Jane Doe	P	P	A	…	=COUNTIF(B2:AF2,"P")	=COUNTIF(B2:AF2,"A")	=AG2/22
John Smith	P	L	P	…	=COUNTIF(B3:AF3,"P")	=COUNTIF(B3:AF3,"A")	=AG3/22

Veelgebruikte statuscodes zijn: P = Aanwezig (Present), A = Afwezig (Absent), L = Verlof (Leave), WFH = Thuiswerken (Work From Home). COUNTIF telt elke code onafhankelijk op, wat je een volledig overzicht per werknemer geeft. Deel het totaal aantal aanwezige dagen door de werkdagen in de maand (meestal 22) om een aanwezigheidspercentage te krijgen. Maak deze kolom op als een percentage met één decimaal.

Pas voorwaardelijke opmaak toe om aanwezigheidsgegevens visueel te maken met kleuren — rood voor afwezigheid, groen voor volledige aanwezigheid — zodat managers patronen in één oogopslag kunnen zien.

Salarisanalyses met SUMIF en AVERAGEIF

Voor salarisanalyses moeten salarisgegevens vaak worden samengevoegd per afdeling, functieniveau of dienstverband. SUMIF en SUMIFS pakken voorwaardelijk optellen hier perfect aan:

=SUMIF(tblEmployees[Department], "Marketing", tblEmployees[Annual Salary])
=AVERAGEIF(tblEmployees[Department], "Marketing", tblEmployees[Annual Salary])
=COUNTIF(tblEmployees[Department], "Marketing")

Om deze dynamisch te maken (zodat je de afdeling in een cel kunt wijzigen en alle resultaten direct worden bijgewerkt), vervang je de hardgecodeerde tekst door een celverwijzing:

=SUMIF(tblEmployees[Department], H2, tblEmployees[Annual Salary])

Waarbij H2 een vervolgkeuzelijst is met afdelingsnamen. Dit patroon vormt de ruggengraat van een selfservice HR-analytics mini-dashboard.

Scoreringssysteem voor prestatiebeoordelingen

Een gestructureerd beoordelingsformulier verzamelt scores voor meerdere competenties en berekent automatisch een algehele score.

Voorgestelde competentiekolommen: Communicatie, Teamwerk, Technische vaardigheden, Leiderschap, Oplevering. Beoordeel elke competentie op een schaal van 1–5. Bereken een gewogen algemene score:

=SUMPRODUCT(C2:G2, $C$1:$G$1) / SUM($C$1:$G$1)

Waarbij rij 1 de wegingen voor elke competentie bevat (bijv. Communicatie = 2, Technische vaardigheden = 3, enz.) en rij 2 de scores voor één werknemer bevat. SUMPRODUCT vermenigvuldigt elke score met de bijbehorende weging, telt de resultaten op en deelt door de totale weging. Dit geeft je een echt gewogen gemiddelde zonder een complexe geneste formule.

Wijs prestatieschalen automatisch toe:

=IF(H2>=4.5,"Outstanding",IF(H2>=3.5,"Exceeds Expectations",IF(H2>=2.5,"Meets Expectations",IF(H2>=1.5,"Needs Improvement","Unsatisfactory"))))

Waarbij H2 de gewogen score is. Gebruik voorwaardelijke opmaak om de schaalkolom een kleurcode te geven — zo worden beoordelingssamenvattingen veel gemakkelijker te lezen in een groepsomgeving.

Werknemersgegevens opzoeken met INDEX MATCH

VLOOKUP is algemeen bekend, maar INDEX MATCH is een superieure zoekmethode voor HR-gegevens omdat het in elke richting werkt en niet stukgaat wanneer je kolommen invoegt.

Om een functietitel op te halen via Werknemers-ID:

=INDEX(tblEmployees[Job Title], MATCH(A2, tblEmployees[Employee ID], 0))

Om een salaris op naam op te halen (handig in een snelle zoekpaneel):

=INDEX(tblEmployees[Annual Salary], MATCH(B5, tblEmployees[Full Name], 0))

Combineer dit met een eenvoudig zoekpaneel op een apart werkblad, zodat HR-medewerkers een naam kunnen typen en direct het volledige profiel van de werknemer uit het masterbestand zien — zonder te scrollen en zonder handmatig te hoeven zoeken.

Een HR-dashboard met een draaitabel bouwen

Zodra je masterdata schoon en consistent is, zijn draaitabellen (Pivot Tables) de snelste manier om HR-gegevens samen te vatten. Voeg een draaitabel in vanuit je werknemersmastertabel en verken deze handige overzichten:

• Personeelsbestand per afdeling: Rijen = Afdeling, Waarden = COUNT van Werknemers-ID
• Gemiddeld salaris per functietitel: Rijen = Functietitel, Waarden = AVERAGE van Jaarsalaris
• Personeelsbestand per dienstverband: Rijen = Dienstverband, Waarden = COUNT van Werknemers-ID
• Verdeling van diensttijd: Rijen = Diensttijdschaal (de van IF afgeleide kolom), Waarden = COUNT van Werknemers-ID

Koppel elke draaitabel aan een grafiek — staafdiagrammen voor vergelijkingen van het personeelsbestand, een cirkeldiagram voor de verdeling van het dienstverband. Verbind meerdere draaitabellen met één Slicer (Invoegen → Slicer), zodat het klikken op een afdeling alle grafieken tegelijkertijd filtert. Dit is de basis van een echt bruikbaar dynamisch HR-dashboard in Excel.

Verloop en belangrijkste HR-statistieken

Het bijhouden van vrijwillig verloop is cruciaal voor personeelsplanning. Maak een eenvoudig register voor uitdiensttredingen met de kolommen: Werknemers-ID, Naam, Afdeling, Einddatum, Reden (Vrijwillig / Gedwongen).

Formule voor het maandelijks percentage vrijwillig verloop:

=COUNTIFS(tblTerminations[Reason],"Voluntary",tblTerminations[Month],B1) / tblEmployees_Count * 100

Waarbij B1 de geselecteerde maand is en tblEmployees_Count een benoemd bereik met het totale personeelsbestand. Dit over 12 maanden in een lijngrafiek uitzetten, geeft de directie een duidelijk beeld van retentietrends, zonder gespecialiseerde HR-software.

Andere statistieken die het volgen waard zijn in hetzelfde dashboard:

• Gemiddeld aantal dagen om openstaande vacatures te vervullen (uit het vacatureregister)
• Ziekteverzuimpercentage per afdeling (uit de aanwezigheidsregistratie)
• Trainingsuren per werknemer (uit een trainingsregister)
• Verdeling van prestatiescores (uit beoordelingsgegevens)

Repetitieve HR-rapportages automatiseren

Maandelijkse personeelsrapportages, aanwezigheidsoverzichten en salariskostenoverzichten hebben elke maand dezelfde structuur. In plaats van ze handmatig opnieuw op te bouwen, kun je ze beter automatiseren. Excel-automatisering met Power Automate kan het genereren van rapporten activeren, e-mailmeldingen sturen als de aanwezigheid onder een bepaalde drempel komt, of voltooide werkbladen automatisch naar SharePoint kopiëren — en dat alles zonder één regel code te schrijven.

Voor teams die bekend zijn met macro's, biedt rapportage-automatisering met Excel VBA de mogelijkheid om knoppen te bouwen waarmee je met één klik gegevens vernieuwt, opmaak toepast en binnen enkele seconden pdf's exporteert.

AI gebruiken om sneller HR-formules te schrijven

Het bouwen van complexe HR-formules — vooral geneste IF-functies, SUMPRODUCT-scoremethodes of COUNTIFS met meerdere voorwaarden — kan tijdrovend en foutgevoelig zijn. Als je ooit vastloopt, kun je in gewoon Nederlands beschrijven wat je nodig hebt en direct een kant-en-klare formule krijgen met GPTExcel. Bijvoorbeeld: "Bereken de gewogen gemiddelde prestatiescore waarbij de competentiewegingen in rij 1 staan en de scores in C2:G2" — en de juiste SUMPRODUCT-formule verschijnt onmiddellijk, klaar om te plakken.

Je kunt ook AI-gestuurde data-analyse in Excel verkennen om nog een stap verder te gaan, en patronen in je HR-data identificeren die bij handmatige analyse mogelijk over het hoofd worden gezien.

Veelgestelde vragen

Wat is de beste Excel-formule om diensttijd van een werknemer te berekenen?

Gebruik DATEDIF(startdatum, TODAY(), "Y") om de volledige dienstjaren te krijgen. Voor een gedetailleerder resultaat met jaren en maanden, combineer je twee DATEDIF-functies: =DATEDIF(B2,TODAY(),"Y") & " yrs " & DATEDIF(B2,TODAY(),"YM") & " mo". Dit wordt automatisch bijgewerkt telkens wanneer het bestand wordt geopend.

Hoe houd ik de aanwezigheid van meerdere werknemers bij in Excel?

Maak een maandelijks overzicht met werknemers in de rijen en datums in de kolommen. Voer in elke cel statuscodes in (bijv. P, A, L). Gebruik COUNTIF om elke status per werknemer op te tellen en COUNTIFS om dit per afdeling samen te vatten. Pas voorwaardelijke opmaak toe om afwezigheden rood te markeren voor een snelle visuele controle.

Kan Excel speciale HR-software vervangen?

Voor kleine tot middelgrote teams (tot een paar honderd werknemers) kan Excel effectief de kernfuncties van HR afhandelen: personeelsdossiers, aanwezigheid, prestatiebeoordelingen en basisanalyses. Voor grote organisaties met complexe eisen op het gebied van salarisadministratie, secundaire arbeidsvoorwaarden of compliance, is gespecialiseerde HRIS-software geschikter. Maar Excel blijft naast die systemen van onschatbare waarde voor ad-hocanalyses en rapportages.

Hoe beveilig ik gevoelige personeelsgegevens in een HR-bestand in Excel?

Gebruik werkbladbeveiliging (Controleren → Blad beveiligen) om formulecellen te vergrendelen, terwijl cellen voor gegevensinvoer bewerkbaar blijven. Gebruik wachtwoordbeveiliging op werkmapniveau (Bestand → Info → Werkmap beveiligen) om het openen van het bestand te beperken. Voor salariskolommen kun je overwegen om die werkbladen apart te verbergen en te beveiligen, en alleen overzichten te delen met managers in plaats van het volledige masterbestand.