UNE PYRAMIDE DES ÂGES EN 1 CLIC SUR EXCEL !

Dans ce tutoriel, je vais vous montrer comment créer une pyramide des âges sur Excel en partant d’une table des membres du personnel d’une entreprise. Nous verrons deux méthodes, une première dite classique dans laquelle nous étudierons une à une chacune des étapes de la construction de la pyramide des âges. Puis dans un second temps, nous verrons comment il est possible d’arriver à un résultat équivalent en un seul clic !

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

1. Présentation

Pour développer chacune des étapes de la construction de la pyramide des âges, nous allons partir du listing du personnel d’une entreprise.

Cette base de données est composée de près de 200 personnes, pour lesquelles nous retrouvons les informations utiles à la construction de la pyramide des âges : nom, prénom, sexe et date de naissance.

Ces données sont représentées dans Excel sous la forme d’un tableau de données structurées, ce qui permet de pouvoir les exploiter de manière très simple ! Pour en savoir plus sur ce type de tableaux, je vous invite à consulter le tutoriel dédié en cliquant ici.

Ce tableau porte le nom de « _effectif ».

À partir de cette base de données, nous allons créer un tableau de synthèse qui va ensuite servir de base pour la construction de la pyramide des âges.

Dans ce tableau de synthèse, nous retrouverons les différentes lignes, regroupées en fonction d’une tranche d’âges et par sexe.

2. Calcul des tranches d’âges

La première chose à mettre en place pour permettre la synthèse des données va être de classer les personnes de la liste en fonction de tranches d’âges.

Et pour cela, nous allons d’abord devoir calculer l’âge de chacune d’entre elles.

Nous avons déjà eu l’occasion de découvrir comment procéder pour calculer l’âge d’une personne dans un précédent tutoriel.

Nous avions alors découvert plusieurs méthodes, et nous avions alors vu que la méthode la plus rapide et la plus fiable consiste à utiliser la formule secrète DATEDIF() !

 =DATEDIF([@Naissance];AUJOURDHUI();"y") 

Celle-ci attend trois arguments :

• La date de début, laquelle correspond dans notre cas à la date de naissance de la personne,
• La date de fin, il s’agit ici et la date du jour,
• Et l’unité suivant laquelle nous souhaitons récupérer le résultat, il s’agit ici de récupérer l’âge des personnes, celle doit donc être exprimée en année (year en anglais)

Ensuite, nous allons pouvoir répartir ces âges en fonction de tranches, par exemple des tranches de 10 ans.

Pour cela, nous utilisons la formule ARRONDI.AU.MULTIPLE() que nous avons également découverte dans un précédent tutoriel et qui permet d’arrondir un nombre en fonction d’un multiple que nous pouvons librement spécifier.

  =ARRONDI.AU.MULTIPLE(DATEDIF([@Naissance];AUJOURDHUI();"y")-5;10) 

Comme vous pouvez le constater, l’astuce consiste à retrancher 5 à l’âge obtenu précédemment afin que l’arrondi le plus proche se fasse sur l’unité inférieure !

Ensuite, nous pouvons construire le tableau de synthèse dans lequel nous viendrons dénombrer les effectifs correspondant au sexe (en colonne) et à la tranche d’âges (en ligne).

Pour alimenter ce tableau, nous allons utiliser la fonction NB.SI.ENS(), qui permet de compter le nombre de fois qu’une série de critères sont remplis.

Celle-ci fonctionne de la même manière que la formule NB.SI(), c’est-à-dire que nous venons saisir en paramètre :

• Une plage sur laquelle nous souhaitons effectuer une analyse,
• Puis le résultat de l’analyse à effectuer

Et évidemment, nous pouvons démultiplier les tests en alternant successivement les plages d’analyses avec les résultats à obtenir (jusqu’à 255 analyses sont possibles !) :

 =NB.SI.ENS(_effectifs[Sexe];H$7;_effectifs[Âge];$G8)  

Attention lors de la rédaction de la formule à l’utilisation de formules mixtes afin de verrouiller l’appel aux en-têtes de colonnes (avec le symbole dollar devant le numéro de la ligne dans laquelle est saisie Femme : H$7) et de ligne (cette fois-ci devant la lettre de colonne dans laquelle se trouve la tranche d’âge : $G8).

De cette manière, nous pourrons étendre la formule à l’ensemble des cellules de la table.

La fonction NB.SI.ENS() ayant été introduite avec la version 2007 d’Excel, il est possible si vous utilisez une ancienne version que celle-ci ne soit pas disponible.

Dans ce cas, vous pouvez alors utiliser la formule SOMMEPROD() d’une manière détournée, comme vous pouvez la découvrir dans cet article.

Dans ce cas, la formule SOMMEPROD() sera la suivante :

 =SOMMEPROD((_effectifs[Sexe]=H$7)*(_effectifs[Âge]=$G8)*1)  

À présent, pour pouvoir différencier les données correspond aux femmes (qui seront situées sur la partie gauche de la pyramide) de celles correspondantes aux hommes (sur la partie droite), nous multiplions simplement le résultat obtenu par -1 :

 =NB.SI.ENS(_effectifs[Sexe];H$7;_effectifs[Âge];$G8)*-1  

3. Création de la pyramide

Maintenant que le tableau de données est correctement défini, nous pouvons maintenant créer la pyramide des âges, qui nous intéresse !

Pour cela, nous commençons par sélectionner l’ensemble des cellules de la table de synthèse, puis insérons un graphique en barre en nous rendant dans le menu Insertion du ruban et choisissons d’insérer un graphique de type Barres (en 2D ou en 3D comme bon vous semble !).

Cela étant fait, nous il ne reste plus qu’à mettre en forme les barres pour donner au graphique l’apparence d’une pyramide des âges :

• Nous commençons par effectuer un clic-droit sur l’une des barres qui représente le nombre de femme, puis nous choisissons Mettre en forme une série de données

• Dans le menu de personnalisation des options des séries, nous modifions les curseurs Superposition de séries (100%) et Largeur de l’intervalle (0%)

p>Et voilà, notre pyramide est maintenant terminée !

4. Création de la pyramide en 1 clic !

Et voilà, la surprise promise : dans le fichier disponible au téléchargement je vous ai concocté une petite macro commande qui va vous permettre de créer directement la pyramide des âges en cliquant sur un petit bouton !

Pour l’utiliser, il suffit de sélectionner l’une des cellules du tableau de synthèse, puis de cliquer sur le bouton « Créer la pyramide des âges », la macro s’occupe de tout le reste !