Excel pour les RH : fiabiliser un tableau d’effectifs et d’absences

Un tableau RH devient exploitable lorsque chaque ligne a un sens précis et que ses calculs respectent une règle connue. Compter les personnes présentes à une date et additionner des jours d’absence sur une période sont deux opérations différentes. Les réunir dans un fichier exige de vérifier les identifiants, les dates et les éventuels chevauchements.

Ce guide propose un exercice entièrement fictif, avec un modèle Excel et son corrigé. Il sert à apprendre à structurer et contrôler les données. Il ne calcule ni paie, ni droits à congés, ni effectif réglementaire.

Télécharger le modèle Excel RH, l’exercice et le corrigé (fichier XLSX).

Définir ce que le tableau mesure

Le fichier mesure deux résultats : le nombre de personnes présentes à la fin d’une période et les journées d’absence du lundi au vendredi comprises dans cette période.

Les conventions sont volontairement simples :

• Une personne correspond à un identifiant unique et à une seule période de contrat.
• La date d’entrée et la date de sortie sont incluses. Une sortie vide signifie qu’aucune fin n’est renseignée.
• Les absences couvrent des journées entières. Les demi-journées, horaires variables et temps partiels ne sont pas traités.
• Le samedi et le dimanche sont exclus du décompte. Aucun jour férié n’est retranché dans cette version.
• Une absence doit rester dans la période de contrat de la personne.
• Deux absences qui partagent au moins une date pour la même personne déclenchent un examen.

Ces règles ne décrivent pas tous les calendriers de travail. Elles définissent l’exercice. Un autre rythme, des jours fériés, plusieurs contrats successifs ou un calcul de congés demanderaient une adaptation du modèle.

Séparer les personnes et les événements

Dans Effectifs, chaque ligne représente une personne : identifiant fictif, service, date d’entrée et éventuelle date de sortie. Dans Absences, chaque ligne représente un événement : identifiant d’absence, identifiant de personne, début et fin.

Cette séparation évite de compter trois fois une personne parce qu’elle a trois absences. Elle permet aussi de vérifier qu’un événement correspond bien à une personne enregistrée.

Les cellules jaunes sont destinées à la saisie. Les colonnes grises calculent les contrôles et résultats. Les plages sont bornées : lignes 8 à 27 dans Effectifs, 8 à 207 dans Absences. Ajouter une ligne au-delà exige d’étendre et de vérifier les formules et les plages de synthèse.

Cinq contrôles avant de compter

1. Vérifier les identifiants

Un identifiant de personne doit être unique dans Effectifs. Celui d’une absence doit être unique dans Absences. L’identifiant de personne utilisé pour une absence doit correspondre exactement à une ligne du registre.

Un doublon ne se supprime pas sur la seule ressemblance de deux lignes. Il faut déterminer s’il s’agit du même événement ou d’événements distincts. La fonction NB.SI.ENS permet de repérer plusieurs occurrences selon des critères explicites.

2. Distinguer une vraie date d’un texte

Une cellule affichant « 22/10/2026 » peut contenir un texte. Le format visuel ne suffit pas : les calculs nécessitent une valeur de date Excel. Le modèle utilise ESTNUM dans ses contrôles et rejette les dates contenant une fraction de journée.

Lors d’un import, vérifiez le format de la source avant conversion. « 04/05/2026 » ne se lit pas de la même façon dans tous les pays. Ne convertissez pas une date ambiguë sans savoir quelle convention était utilisée.

3. Vérifier l’ordre et la période du contrat

La date de fin d’une absence doit être au moins égale à son début. Une entrée ne peut pas suivre une sortie renseignée. Une absence commencée avant l’entrée ou terminée après la sortie doit être examinée.

Le modèle signale ces situations. Il ne recadre pas silencieusement une absence aux dates d’un contrat, car cela masquerait une anomalie de source.

4. Détecter les chevauchements

Deux intervalles se chevauchent lorsque le début de l’un est antérieur ou égal à la fin de l’autre, et que sa fin est postérieure ou égale au début de l’autre. La recherche doit porter sur la même personne.

Avec des dates inclusives, une absence du 5 au 7 octobre et une autre du 7 au 8 octobre partagent le 7. Le fichier les signale. Si deux demi-journées légitimes expliquent cette situation, il faut un modèle plus précis : celui-ci ne les gère pas.

5. Rendre une anomalie visible dans la synthèse

Une ligne invalide ne doit pas disparaître dans un total apparemment complet. Le classeur affiche « À corriger » à la place du total des jours tant qu’une anomalie est présente dans les données d’effectifs ou d’absences. Les colonnes de contrôle donnent la cause à examiner.

Une vraie absence de jours sur la période reste différente d’une donnée inconnue. Par exemple, une absence limitée à un samedi et un dimanche donne zéro avec le calendrier retenu.

Calculer uniquement la partie comprise dans la période

Pour chaque événement valide, le fichier retient le plus tardif des deux débuts — début d’absence ou début du suivi — et le plus précoce des deux termes — fin d’absence ou fin du suivi.

Si ces bornes ne se croisent pas, le résultat vaut zéro. Sinon, NB.JOURS.OUVRES compte les journées du lundi au vendredi, bornes incluses. Aucune liste de jours fériés n’est passée à la fonction dans ce modèle.

Le principe du calcul, après les contrôles de saisie, est :

=SI(Fin_retenue<Debut_retenu;0;NB.JOURS.OUVRES(Debut_retenu;Fin_retenue))

Les noms présentés ici expliquent la logique ; le classeur contient les références de cellules correspondantes. Pour un calendrier différent, Microsoft documente NB.JOURS.OUVRES.INTL. L’adaptation doit porter à la fois sur le calcul et sur la définition de l’indicateur.

Le résultat attendu pour octobre 2026

Le scénario corrigé comprend cinq personnes enregistrées. E003 est sortie le 15 octobre ; E005 entre le 1er novembre. Trois personnes sont donc présentes au 31 octobre selon les conventions du fichier.

Les sept absences donnent le résultat suivant :

Ces 12 jours ne représentent pas douze personnes absentes. On ne peut pas non plus les diviser par les trois personnes présentes en fin de mois pour produire un taux d’absentéisme : il manquerait notamment un dénominateur de temps théorique défini pour la même population et la même période.

Réaliser l’exercice puis vérifier les corrections

Dans une copie du fichier, remplacez les valeurs de Absences!A8:D16 par les données de Exercice!A8:D16. Conservez les colonnes de formules.

Vous retrouverez un identifiant répété, deux dates inversées, une date stockée comme texte, une personne inconnue et une absence qui en chevauche une autre. Renseignez votre diagnostic avant de lire les informations confirmées en bas de la feuille Exercice.

Le scénario fournit les éléments permettant de corriger : identité fictive de la personne concernée, dates confirmées et confirmation qu’une ligne a été ajoutée par erreur. Dans un fichier réel, il faudrait obtenir ces informations de la source compétente. La formule détecte une incohérence ; elle ne décide pas quelle donnée est vraie.

Après correction, vérifiez les 7 événements, 12 jours et 3 personnes en fin de période. Puis prolongez A01 jusqu’au 8 octobre : le total doit passer à 13 jours. Rétablissez ensuite le 7. Ces résultats et des essais sur les limites de capacité ont été vérifiés dans le moteur de génération du fichier ; aucun test dans Excel bureau n’est revendiqué.

Utiliser l’exercice pour préparer une formation Excel RH

Ce travail permet de repérer les difficultés utiles à traiter : types de données, références, critères multiples, recherches exactes, contrôle des imports et explication d’une synthèse. Le test de niveau Excel aide à compléter ce positionnement.

Pour des exports mensuels récurrents, poursuivez avec l’exercice Power Query. Pour organiser les besoins de formation de l’équipe, utilisez le modèle de plan de développement des compétences, qui répond à un autre objectif.

Préparer votre parcours de formation Excel ou demander un devis adapté aux usages de votre équipe.