On ouvre un fichier RH, on a 300 lignes avec des dates de naissance, et la demande tombe : produire une pyramide des âges lisible pour le prochain comité. Le réflexe, c’est de taper DATEDIF dans la première cellule et de tirer la formule. Sauf que le résultat change à chaque ouverture du fichier si on s’appuie sur AUJOURDHUI(). La pyramide de janvier ne correspond plus à celle de mars, et personne ne sait quel état elle reflète.
Le calcul d’âge dans Excel mérite qu’on s’arrête sur trois points concrets : le choix de la date de référence, la construction des tranches, et la mise en forme du graphique pour qu’il soit réellement exploitable.
Date de référence fixe : la base d’un calcul d’âge fiable dans Excel
La plupart des tutoriels utilisent la fonction AUJOURDHUI() comme second argument de DATEDIF. En production, c’est un piège. Chaque ouverture du classeur recalcule les âges, ce qui décale certains salariés d’une tranche à l’autre selon le jour de consultation.
La bonne pratique consiste à figer la date de référence sur une date de clôture, par exemple le 31 décembre de l’exercice en cours. On place cette date dans une cellule dédiée (par exemple B1), puis on écrit la formule :
=DATEDIF(C2;$B$1; »Y »)
C2 contient la date de naissance, $B$1 pointe vers la date de référence verrouillée. Le « Y » renvoie l’âge en années révolues. Avec cette approche, le fichier produit toujours le même résultat, quel que soit le moment où on l’ouvre. On peut aussi dupliquer l’onglet en changeant juste B1 pour comparer deux exercices.
DATEDIF ou ENT : quelle formule choisir
DATEDIF reste la formule la plus lisible pour ce type de calcul. Son inconvénient : elle n’apparaît pas dans l’auto-complétion d’Excel, ce qui déroute les utilisateurs occasionnels. L’alternative courante est :
=ENT((B1-C2)/365,25)
Cette formule divise l’écart en jours par 365,25 et arrondit à l’entier inférieur. Les retours varient sur ce point : dans la majorité des cas, les deux formules donnent le même résultat, mais DATEDIF gère mieux les années bissextiles sur de grands jeux de données. Pour une pyramide des âges RH, l’écart reste négligeable.
Tranches d’âge et tableau croisé dynamique : structurer les données avant le graphique
Une fois l’âge calculé pour chaque ligne, on doit regrouper les effectifs par tranche et par sexe. C’est là que beaucoup de fichiers deviennent illisibles, parce qu’on saute directement au graphique sans passer par un tableau intermédiaire propre.
Créer la colonne tranche d’âge
La méthode la plus directe utilise des SI imbriqués ou, mieux, la fonction RECHERCHEV sur une table de correspondance. On définit les bornes dans un petit tableau annexe :
- 0 → « Moins de 25 ans »
- 25 → « 25-34 ans »
- 35 → « 35-44 ans »
- 45 → « 45-54 ans »
- 55 → « 55 ans et plus »
Avec =RECHERCHEV(D2;table_bornes;2;VRAI), chaque salarié est automatiquement classé. On évite les formules SI à rallonge et on peut modifier les tranches en un seul endroit.
Passer par le tableau croisé dynamique
Le tableau croisé dynamique (TCD) est la brique intermédiaire standard entre les données brutes et le graphique. On place la tranche d’âge en lignes, le sexe en colonnes, et le nombre d’enregistrements en valeurs. En deux clics, on obtient un tableau d’effectifs hommes/femmes par tranche, prêt à alimenter le graphique.
Un point souvent négligé : chaque ligne de la base doit correspondre à une seule personne, avec au minimum une colonne Sexe et une colonne Âge (ou Date de naissance). Si des doublons existent ou si le sexe est mal renseigné, la pyramide sera faussée sans qu’aucune erreur ne s’affiche.
Graphique en barres empilées : obtenir l’effet pyramide dans Excel
Excel ne propose pas de type « pyramide des âges » natif. On utilise un graphique à barres empilées, avec une astuce sur les valeurs d’une des deux séries.
Le principe : on passe les effectifs d’un sexe en valeurs négatives. Si les femmes sont à gauche, on multiplie leur colonne par -1. Le graphique affiche alors les barres de part et d’autre de l’axe vertical, ce qui crée visuellement la forme pyramidale.
Rendre l’axe horizontal lisible
Le problème immédiat, c’est que l’axe horizontal affiche des valeurs négatives (-10, -20, etc.), ce qui n’a aucun sens pour le lecteur. Pour corriger ça, on applique un format de nombre personnalisé sur l’axe :
0;0
Ce format affiche la valeur absolue, que le nombre soit positif ou négatif. On fait un clic droit sur l’axe, « Format de l’axe », puis on colle ce code dans le champ format personnalisé.
Ajuster les couleurs et l’espacement
Deux réglages améliorent la lisibilité de la pyramide des âges :
- Réduire la largeur de l’intervalle entre les barres (passer à 10-20 % dans les options de série) pour que les barres se touchent presque, comme un histogramme classique
- Attribuer une couleur distincte par sexe et supprimer la légende si les étiquettes de données sont explicites
- Inverser l’ordre des catégories sur l’axe vertical pour que les tranches les plus jeunes apparaissent en bas, conformément à la convention démographique
Pour inverser l’axe : clic droit sur l’axe vertical, « Format de l’axe », cocher « Catégories en ordre inverse ».
Erreurs fréquentes qui faussent la pyramide des âges Excel
Quelques pièges reviennent systématiquement dans les fichiers qu’on récupère.
Le premier : des dates de naissance stockées en texte. DATEDIF renvoie alors une erreur #VALEUR!. On le détecte en vérifiant l’alignement (les dates texte sont calées à gauche). La correction passe par un « Convertir » via l’assistant ou par DATEVAL.
Le deuxième : utiliser AUJOURDHUI() dans un fichier partagé. Chaque collaborateur ouvre le fichier à une date différente, et les effectifs par tranche fluctuent. La date de référence fixe résout ce problème définitivement.
Le troisième : oublier de trier les tranches dans le TCD. Excel les classe parfois par ordre alphabétique (« 25-34 ans » avant « Moins de 25 ans »), ce qui casse la logique visuelle de la pyramide.
Un fichier bien structuré au départ, avec une date de référence explicite et un nettoyage des données avant la construction du TCD, évite la majorité de ces corrections après coup. La pyramide des âges n’est pas un graphique complexe à produire dans Excel, à condition que le calcul d’âge en amont soit solide.
