On a tous eu ce moment en réunion RH : la direction demande une pyramide des âges à jour, et la base de données salariés renvoie des dates de naissance brutes, sans colonne « âge » exploitable. Le réflexe, c’est d’écrire une formule rapide dans Excel.
Le problème, c’est que la formule choisie change le résultat de la pyramide, parfois de manière visible sur les tranches limites. Voici comment poser un calcul d’âge fiable dans Excel et l’exploiter directement pour un suivi d’effectifs propre.
A voir aussi : Tableau : comment réaliser un tableau sans utiliser Excel ?
DATEDIF, ANNEE ou ENT : quelle formule calcul âge Excel choisir
Trois approches reviennent systématiquement. Aucune n’est universelle, et le choix dépend de ce qu’on attend du résultat.
La formule la plus répandue reste =DATEDIF(DateNaissance;AUJOURDHUI(); »Y »). Elle renvoie l’âge en années révolues, comme on le calcule dans la vie courante. Son défaut : c’est une fonction « cachée » dans Excel. Elle n’apparaît pas dans l’auto-complétion, n’est pas documentée dans l’aide officielle en français, et certains utilisateurs hésitent à s’en servir pour cette raison.
A lire également : Corriger automatiquement vos erreurs avec Excel formule soustraction
La combinaison =ANNEE(AUJOURDHUI())-ANNEE(DateNaissance) est plus lisible, mais elle ignore le mois et le jour. Un salarié né en décembre sera compté avec un an de plus pendant onze mois. Pour une pyramide des âges, cette approximation peut décaler des effectifs d’une tranche à l’autre.
La troisième option, =ENT((AUJOURDHUI()-DateNaissance)/365,25), utilise la durée en jours divisée par 365,25. Elle fonctionne dans la majorité des cas, mais les arrondis sur les années bissextiles créent parfois un décalage d’un jour autour de la date anniversaire.
Recommandation pour un fichier RH partagé
DATEDIF reste le choix le plus fiable pour un calcul d’âge destiné à alimenter une pyramide. Si on veut corriger la formule ANNEE pour tenir compte du mois et du jour, on peut écrire :
=ANNEE(AUJOURDHUI())-ANNEE(B2)-SI(DATE(ANNEE(AUJOURDHUI());MOIS(B2);JOUR(B2))>AUJOURDHUI();1;0)
Cette variante soustrait 1 si l’anniversaire n’est pas encore passé dans l’année en cours. Elle produit le même résultat que DATEDIF, avec l’avantage d’être entièrement visible et documentable.
Âge exact ou âge au 31 décembre : une convention qui change la pyramide des âges
Quand on pilote un tableau de bord RH, la date de référence du calcul n’est pas un détail. En contrôle de gestion sociale, deux conventions coexistent : l’âge exact à la date du jour (ou à la date d’extraction) et l’âge atteint au 31 décembre de l’année en cours.
La différence est concrète. Un salarié né le 15 novembre 1970, consulté en mars, a 54 ans en âge exact mais sera compté comme ayant 55 ans si on retient l’âge au 31 décembre. Sur une tranche 50-54 / 55-59, ce salarié bascule d’un côté ou de l’autre selon la convention.
Définir la convention avant de construire le fichier évite de devoir recalculer toute la pyramide en fin d’année. Pour un âge au 31 décembre, on remplace AUJOURDHUI() par DATE(ANNEE(AUJOURDHUI());12;31) dans la formule choisie.
Architecture du fichier Excel pour un suivi d’effectifs fiable
Les tutoriels en ligne montrent souvent le calcul d’âge directement dans le tableau croisé dynamique ou à côté du graphique. Cette approche pose un problème dès que le fichier est mis à jour régulièrement : on perd la trace de la formule, et les incohérences s’accumulent entre les onglets.
Séparer données brutes, calculs et restitutions
La bonne pratique consiste à structurer le fichier en trois couches :
- Un onglet « Données » contenant les informations brutes importées du SIRH (nom, date de naissance, sexe, date d’entrée, service). Aucune formule dans cet onglet.
- Un onglet « Calculs » où l’âge est calculé dans une colonne dédiée, à partir des dates de naissance de l’onglet source. C’est aussi là qu’on attribue la tranche d’âge avec une formule de type RECHERCHEV ou SI imbriqués.
- Un onglet « Restitutions » pour la pyramide des âges, les graphiques et le tableau de bord. Cet onglet ne contient que des références aux calculs, jamais de formule d’âge directe.
Cette architecture garantit que chaque mise à jour de la base (ajout d’un salarié, correction d’une date de naissance) se propage automatiquement jusqu’au graphique, sans recalcul manuel.
Attribuer une tranche d’âge automatiquement
Une fois l’âge calculé, on le convertit en tranche. La méthode la plus lisible utilise RECHERCHEV avec une table de correspondance :
- Créer une petite table avec les bornes inférieures (20, 25, 30, 35, etc.) et les libellés associés (« 20-24 ans », « 25-29 ans »…).
- Appliquer =RECHERCHEV(CelluleAge;TableTranches;2;VRAI) pour affecter automatiquement chaque salarié à sa tranche.
- Modifier les bornes dans la table suffit à recalculer toute la répartition, sans toucher aux formules.
Cette méthode est plus souple que des SI imbriqués, et surtout plus facile à auditer quand un collègue reprend le fichier.
Construire la pyramide des âges Excel à partir des tranches calculées
Une fois les tranches attribuées, on dénombre les effectifs par tranche et par sexe avec NB.SI.ENS ou un tableau croisé dynamique. Pour obtenir la forme caractéristique de la pyramide (hommes à gauche, femmes à droite), on passe les effectifs masculins en valeurs négatives.
Le graphique utilisé est un histogramme horizontal en barres empilées. Dans Excel, on sélectionne les deux séries (effectifs hommes en négatif, effectifs femmes en positif), on insère un graphique à barres groupées, puis on ajuste l’axe horizontal pour masquer le signe moins via un format personnalisé (0;0).
L’axe vertical affiche les tranches d’âge. Si on a bien séparé le calcul des tranches dans un onglet dédié, toute modification des bornes met à jour le graphique sans manipulation supplémentaire.
Piège fréquent sur les séries de données
Un point sur lequel les retours varient : selon la version d’Excel, l’ordre des séries dans le graphique peut s’inverser. Si les tranches d’âge apparaissent de haut en bas au lieu de bas en haut, il faut cocher « Catégories en ordre inverse » dans les options de l’axe vertical. Ce réglage n’est pas toujours au même endroit entre Excel 2019 et Microsoft 365.
La formule de calcul d’âge dans Excel n’est que le premier maillon d’une chaîne qui va jusqu’au graphique de pyramide des âges. Poser la bonne convention de date, isoler le calcul dans un onglet séparé et automatiser l’attribution des tranches, c’est ce qui fait la différence entre un fichier jetable et un outil de suivi d’effectifs réutilisable chaque trimestre.
