Excel — Verzuimanalyse en -registratie
Module 4 — Arbeidsongeschiktheid
FILTER voor verzuimregistratie, PivotTable voor verzuimpatronen, frequentieanalyse van ziekteverzuim.
Concepts
Verzuimregistratie: de basis voor goed re-integratiebeleid
Bij VGS heeft Karin twee medewerkers met een lopend ziekteverzuim. Ze weet uit Module 4 dat ze als werkgever verplichtingen heeft rondom de Wet verbetering poortwachter: een plan van aanpak opstellen, bijhouden en evalueren. Zonder een goede verzuimregistratie kan ze deze verplichtingen niet nakomen.
Excel biedt met FILTER en PivotTables krachtige tools om:
- het verzuim per medewerker bij te houden
- patronen te herkennen (frequent kort verzuim vs. langdurig verzuim)
- de poortwachter-deadlines te bewaken
FILTER: dynamische selectie uit verzuimdata
`FILTER()` is een moderne Excel-functie die rijen retourneert die aan een voorwaarde voldoen. Het resultaat is dynamisch: verandert de brondata, dan past het gefilterde resultaat automatisch mee.
FILTER SYNTAXIS
=FILTER(matrix; filter_conditie; [als_leeg])
Voorbeeld: alle verzuimregistraties van "Jurgen":
=FILTER(Verzuim_tabel; Verzuim_tabel[Naam]="Jurgen"; "Geen data")
Voorbeeld: alle verzuimmeldingen langer dan 14 dagen:
=FILTER(Verzuim_tabel; Verzuim_tabel[Duur_dagen]>14; "Geen langdurig verzuim")
Voorbeeld: actief (nog niet hersteld) verzuim:
=FILTER(Verzuim_tabel; Verzuim_tabel[Hersteldatum]="";"Geen actief verzuim")
Meerdere voorwaarden (AND):
=FILTER(Verzuim_tabel;
(Verzuim_tabel[Naam]="Jurgen") * (Verzuim_tabel[Duur_dagen]>7);
"Geen resultaat")
Meerdere voorwaarden (OR):
=FILTER(Verzuim_tabel;
(Verzuim_tabel[Duur]>42) + (Verzuim_tabel[Terugval]="Ja");
"Geen resultaat")> EXAMTIP: FILTER() werkt alleen in Excel 365 en Excel 2019+. Op oudere versies gebruik je SOMALS of handmatige filters. In een examen- of praktijkcontext: controleer altijd welke Excel-versie beschikbaar is.
Poortwachter-deadlines berekenen
De Wet verbetering poortwachter legt strakke deadlines op bij langdurig verzuim. Excel bewaakt deze termijnen automatisch.
Week 6 — Probleemanalyse | Bedrijfsarts
Uiterlijk in week 6: oordeel bedrijfsarts
Start re-integratiedossier
Melding bij UWV nog niet verplicht
---
Week 8 — Plan van aanpak | Werkgever + werknemer
Uiterlijk in week 8: plan van aanpak opstellen
Moet worden ondertekend door beide partijen
Eerste streefdoel en tijdpad vastleggen
---
Maand 6 en daarna | Evaluatie en bijstelling
Elke 6 weken evalueren en bijstellen
Bij dreigend langdurig verzuim: second opinion
Week 42: ziekmelding bij UWV verplichtPOORTWACHTER-DEADLINES IN EXCEL
Ziekmelding: 12-01-2026 (Jurgen)
Week 6 bedrijfsarts: =A2 + 42 → 23-02-2026
Week 8 plan aanpak: =A2 + 56 → 09-03-2026
Week 13 evaluatie: =A2 + 91 → 13-04-2026
Week 42 UWV-melding: =A2 + 294 → 02-11-2026
Week 52 eerstejaarsevaluatie: =A2 + 365 → 12-01-2027
104 weken (einde loondoorbet.): =A2 + 730 → 12-01-2028
Waarschuwing als deadline nadert (< 14 dagen):
=ALS(deadline - VANDAAG() <= 14;
"⚠ Deadline nadert: " & TEKST(deadline; "dd-mm-jjjj");
"OK - " & (deadline - VANDAAG()) & " dagen")PivotTable voor verzuimpatronen
Een PivotTable (draaitabel) maakt het eenvoudig om grote hoeveelheden verzuimdata samen te vatten. Je kunt snel zien: wie verzuimt het meest, welke maanden zijn problematisch, wat is het gemiddelde verzuim?
PIVOTTABLE OPZETTEN
1. Zorg dat je brondata een echte tabel is (Ctrl+T)
2. Klik ergens in de tabel
3. Invoegen → Draaitabel
4. Kies "Nieuw werkblad"
AANBEVOLEN DRAAITABEL-INDELING:
Rijen: Naam medewerker
Kolommen: Jaar en Maand (of Kwartaal)
Waarden: Som van Duur_dagen (totaal verzuimdagen)
Aantal van Verzuim_ID (aantal meldingen)
Filter: Type verzuim (kort/middellang/langdurig)
AANVULLENDE ANALYSE:
- Gemiddeld verzuim per medewerker: waarden → Gemiddelde van Duur
- Frequentie: aantal meldingen per persoon
- Percentage van totaal: waarden → % van kolomtotaalVerzuimfrequentie en Bradford-factor
De Bradford-factor is een maatstaf voor frequent kort verzuim. Dit is vaak veelzeggender dan langdurig verzuim.
BRADFORD-FACTOR BEREKENING
Formule: B = S² × D
Waarbij:
S = aantal verzuimperioden in een jaar
D = totaal aantal verzuimdagen in een jaar
Voorbeeld:
Medewerker met 3 perioden van elk 2 dagen:
S = 3, D = 6
B = 3² × 6 = 9 × 6 = 54
Medewerker met 1 periode van 6 dagen:
S = 1, D = 6
B = 1² × 6 = 6
Interpretatie:
0–10: Geen punt
11–49: Let op
50–99: Gesprek nodig
100+: Actie vereist
Excel-formule:
Kolom S (# perioden): =AANTALALS(Verzuim[Naam];A2)
Kolom D (# dagen): =SOMALS(Verzuim[Duur];Verzuim[Naam];A2)
Bradford: =S2^2*D2> EXAMTIP: De Wet verbetering poortwachter heeft als doel re-integratie te bevorderen. Als de werkgever de verplichtingen niet nakomt (loonsanctie), kan UWV de loondoorbetalingstermijn met maximaal 1 jaar verlengen. Dit is een dure fout: tot 52 extra weken loondoorbetaling. Een goede verzuimregistratie in Excel voorkomt gemiste deadlines.
Missie
STORY: Jurgen is nu al 5 maanden ziek. Karin heeft de verzuimdata van het afgelopen jaar bij elkaar gezocht en wil een volledig verzuimoverzicht maken: een FILTER-analyse van actief verzuim, een PivotTable met patronen, en een Bradford-factor berekening voor alle medewerkers. Ze vraagt jou om het werkboek op te zetten.
Stap 1 — Bouw de verzuimregistratietabel
VERZUIMDATA VGS 2025-2026 (FICTIEF)
ID | Naam | Ziekmelding | Hersteld | Duur | Type
----|---------|-------------|------------|------|----------
001 | Jurgen | 12-01-2026 | | ? | Langdurig
002 | Ahmed | 03-03-2025 | 10-03-2025 | 7 | Kort
003 | Noor | 15-04-2025 | 17-04-2025 | 2 | Kort
004 | Bas | 02-06-2025 | 04-06-2025 | 2 | Kort
005 | Ahmed | 14-07-2025 | 18-07-2025 | 4 | Kort
006 | Noor | 01-09-2025 | 12-09-2025 | 11 | Middellang
007 | Bas | 10-10-2025 | 14-10-2025 | 4 | Kort
008 | Ahmed | 20-11-2025 | 27-11-2025 | 7 | Kort
009 | Bas | 05-01-2026 | 09-01-2026 | 4 | Kort
Formule duur (als hersteld): =C2-B2 (in dagen)
Formule duur Jurgen (nog ziek): =VANDAAG()-B2
Formule type verzuim:
=ALS(E2<=7;"Kort";ALS(E2<=42;"Middellang";"Langdurig"))Stap 2 — FILTER voor actief verzuim en poortwachter-deadlines
FILTER ACTIEF VERZUIM (apart tabblad)
=FILTER(Verzuim_tabel;
Verzuim_tabel[Hersteldatum]="";
"Geen actief verzuim")
→ Resultaat: alleen Jurgen (rij 001)
POORTWACHTER-DEADLINES JURGEN:
Ziekmelding: 12-01-2026
Week 6 bedrijfsarts: =B_Jurgen + 42 → 23-02-2026
Week 8 plan aanpak: =B_Jurgen + 56 → 09-03-2026
Week 42 UWV-melding: =B_Jurgen + 294 → 02-11-2026
104 weken einde: =B_Jurgen + 730 → 12-01-2028
Status per deadline:
=ALS(deadline<VANDAAG();"✓ Verlopen/Gepasseerd";
ALS(deadline-VANDAAG()<=14;"⚠ Nadert!";
"OK - nog " & (deadline-VANDAAG()) & " dagen"))Stap 3 — PivotTable en Bradford-factor analyse
PIVOTTABLE VERZUIMPATRONEN
1. Maak PivotTable van de verzuimtabel
2. Indeling:
Rijen: Naam
Waarden: Aantal van ID (= aantal meldingen)
Som van Duur (= totaal dagen)
3. Maak handmatige Bradford-berekening naast de PivotTable:
Kolom A: Naam
Kolom B: Aantal perioden (S) — uit PivotTable
Kolom C: Totaal dagen (D) — uit PivotTable
Kolom D: Bradford-factor — =B2^2*C2
Kolom E: Klasse
=ALS(D2>=100;"Actie vereist";
ALS(D2>=50;"Gesprek nodig";
ALS(D2>=11;"Let op";"Geen punt")))
VERWACHTE UITKOMSTEN:
Ahmed: S=3, D=18 → B = 9×18 = 162 → Actie vereist
Bas: S=3, D=10 → B = 9×10 = 90 → Gesprek nodig
Noor: S=2, D=13 → B = 4×13 = 52 → Gesprek nodig
Jurgen: S=1, D=? → B laag (1 periode, ongeacht duur)
Conclusie: Ahmed heeft het hoogste Bradford-getal.
Karin plant een gesprek in met Ahmed en Bas.
Sla op als: VGS_verzuimanalyse_2026.xlsx