Regrouper par tranche d’âge sur Excel : l'incroyable formule PLANCHER

Comment regrouper par tranche d’âge sur Excel ?

Faire des analyses statistiques par âge dans un tableau Excel, c’est bien. Mais regrouper par tranche d’âge sur Excel (de 10 ans en 10 ans, par exemple), c’est encore mieux : les résultats, les évolutions et les différences deviennent bien plus lisibles.

Dans cet article, on vous montre la formule qui crée des tranches d’âge dynamiques, comment la comprendre, et comment choisir la bonne fonction d’arrondi.

‍

Pourquoi regrouper les âges par tranches sur Excel ?

Créer des catégories d’âge rend les analyses plus pertinentes. Voici quelques exemples de questions auxquelles elles permettent de répondre :

• Comment évoluent les salaires bruts annuels en fonction de l’âge des salariés (moins de 20 ans, 20-30 ans, 30-40 ans, etc.) ?
• Les femmes et les hommes d’une même tranche d’âge sont-ils rémunérés de la même façon ?
• Quelle est la répartition de vos effectifs par tranche d’âge ?

Calculer des âges depuis les dates de naissance ne suffit donc pas : voici comment calculer un âge ou une ancienneté sur Excel. On veut aller plus loin, tout en restant dynamique. Le cheminement est le suivant :

Date de naissance → âge → tranche d’âge

Prérequis : une colonne « Âge » dans un tableau Excel. Pour que les formules s’étendent automatiquement, mettez vos données sous forme de tableau en suivant ces 3 étapes.

‍

La formule pour regrouper par tranche d’âge (étape par étape)

‍

Des formules, il peut en exister une multitude, mais nous on cherche LA formule.

• Calculer le plancher d’un âge, de 10 ans en 10 ans :  =PLANCHER([@Age];10)

• Créer une catégorie d’âge au format « X – Y ans » (toujours de 10 ans en 10 ans) :  =PLANCHER([@Age];10)&" - "&PLANCHER([@Age];10)+9&" ans"

• Bonus : Optimiser cette formule avec la fonction LET :  =LET(lintervalle;10;leplancher;PLANCHER([@Age];lintervalle); leplancher" - "&leplancher+lintervalle&" ans")

‍

‍

Un calcul dynamique pour un résultat infaillible !

‍

Comprendre la fonction PLANCHER

Si vous avez l’habitude d’Excel, vous connaissez ARRONDI.INF et ARRONDI.SUP, qui arrondissent à l’inférieur ou au supérieur. Elles ont deux grandes sœurs plus efficaces : PLANCHER et PLAFOND.

• PLANCHER fait comme ARRONDI.INF, mais simplifie beaucoup la formule pour des arrondis qui ne sont pas des puissances de 10 : par exemple un arrondi de 5, ou de 0,25 pour des notes.
• PLAFOND fait la même chose, mais vers le haut, comme ARRONDI.SUP.

Syntaxe : PLANCHER( nombre ; intervalle )

Chez Solpedinn, on n’utilise quasiment plus que PLANCHER et PLAFOND dans nos tableaux. PLANCHER est idéale pour calculer des tranches d’âge.

‍

Quelle fonction d’arrondi choisir ?

Pour arrondir un nombre dans Excel (ou dans Google Sheets), il existe 11 fonctions, selon l’objectif :

Pas besoin de toutes les retenir. C’est comme pour SOMME.SI et SOMME.SI.ENS : autant n’utiliser que SOMME.SI.ENS, qui enrichit SOMME.SI sans rien lui enlever.

Dans une formation Solpedinn typique, 3 fonctions couvrent quasiment tous les besoins :

• ARRONDI.AU.MULTIPLE
• PLANCHER
• PLAFOND

Et pour regrouper les âges en tranches, on privilégie PLANCHER.

Comment joindre des textes dans une formule ?

Notre formule assemble des nombres et du texte. Pour joindre un texte à un autre, le plus simple est souvent le symbole « & » (l’esperluette). Il fonctionne pour les nombres, les textes et même les valeurs booléennes (VRAI et FAUX).

• =1+1 donne 2 (les nombres sont additionnés)
• =1&1 donne 11 (les nombres sont joints)

Dans certains cas, les fonctions CONCAT et JOINDRE.TEXTE (disponibles depuis Excel 2019) sont plus pratiques. CONCATENER n’a plus beaucoup d’intérêt face au trio « & / CONCAT / JOINDRE.TEXTE ».

Trois façons de combiner « Pierre » et « DUPONT » :

• Avec l’esperluette : ="Pierre"&" "&"DUPONT"
• Avec CONCAT : =CONCAT("Pierre";" ";"DUPONT")
• Avec JOINDRE.TEXTE : =JOINDRE.TEXTE(" ";VRAI;"Pierre";"DUPONT")

Pour aller plus loin, découvrez les meilleures fonctions de texte d’Excel.

Deux autres méthodes pour regrouper par tranche d’âge

Pour des tranches irrégulières : RECHERCHEX

Si vos tranches n’ont pas la même taille (par exemple moins de 18 ans, 18-25 ans, 26-40 ans, 41-60 ans, plus de 60 ans), PLANCHER ne suffit plus. Créez un petit tableau de seuils, puis utilisez une recherche approximative :

=RECHERCHEX([@Age];Seuils[Âge min];Seuils[Tranche];;-1)

Le dernier argument (-1) cherche la valeur exacte ou la plus petite valeur juste en dessous. Pour maîtriser cette fonction, lisez les 10 meilleures fonctions de recherche sur Excel.

Pour une analyse rapide : le tableau croisé dynamique

Dans un tableau croisé dynamique, placez le champ Âge en lignes, faites un clic droit sur une valeur, puis choisissez Grouper. Indiquez le début, la fin et l’intervalle (par exemple 10) : Excel crée les tranches pour vous, sans formule.

Quelle méthode choisir ?

• Formule PLANCHER : la tranche s’affiche dans une colonne de votre tableau, réutilisable partout (filtres, segments, graphiques).
• RECHERCHEX : quand les tranches sont irrégulières.
• Groupement du TCD : pour une analyse ponctuelle, sans modifier les données.

Analyser vos tranches d’âge

Une fois vos tranches créées, exploitez-les :

• filtrez vos données visuellement avec les segments Excel ;
• construisez un tableau de bord RH pour suivre la répartition de vos effectifs ;
• apprenez à créer des tableaux de bord interactifs avec notre formation dédiée.

‍