Comment regrouper les âges de vos employés par tranche d'âge dans Excel ? Il existe une méthode qui fait ce travail sans avoir à utiliser la moindre formule.
Étape 1 : Données initiales
Prenons une liste de noms avec leurs dates de naissance. Nous voulons regrouper les salariés par tranche d'âge pour faire une pyramide des âges.
Depuis une feuille de calcul Excel, plusieurs fonctions doivent être utilisées pour effectuer ce travail (DATEDIF pour effectuer le calcul de l'âge, ARRONDIR.AU.MULTIPLE pour arrondir par tranche d'âge, SOMME.SI.ENS pour faire le regroupement par âge et par genre)
Maintenant, grâce à Power Query, vous pouvez obtenir le même résultat sans faire la moindre formule.
Étape 2 : Insérer les données dans un Tableau
Pour que vos données soient "visibles" dans Power Query, il faut impérativement les mettre dans un Tableau (Insertion > Tableau).
Par défaut, le nom d'un Tableau est "Tableau1". Il est toujours préférable de lui attribuer un nom spécifique ; ici "Employés"
Maintenant, il suffit de charger ces données dans Power Query depuis le menu Données > A partir d'un Tableau
Étape 3 : Changer le format des données
Dans Power Query, le type de données est très important. Il est donc important de modifier le type de données si jamais le format de données n'est pas celui attendu. Dans notre exemple, l'importation dans Power Query affiche les dates anniversaires avec les heures.
Pour n'afficher que les dates, sans les heures, il suffit de transformer le type de données en cliquant sur l'icône dans l'entête de colonne.
Power Query vous demande ensuite si vous voulez ajouter une nouvelle étape ou remplacer l'actuelle. Peu importe l'option, ça n'a pas d'incidence dans ce cas de figure et nous avons maintenant, les dates au bon format.
Étape 4 : Ajouter une colonne Âge
Cette étape n'est pas très compliquée mais nécessite de bien faire attention au type de données (comme toujours dans Power Query).
- Sélectionnez votre colonne contenant les Dates de naissance
- Ouvrez le menu Ajouter une colonne > Date > Âge
Le résultat est très déroutant car cette manipulation nous retourne le nombre de jours entre la date de naissance et la date du jour.
Étape 5 : Convertir en nombre année
Pour convertir ce résultat en nombre d'années, il suffit
- Sélectionnez la nouvelle colonne Âge (sans cela, l'option Durée ne sera pas accessible)
- Appliquez le menu Transformer > Durée > Total années
Et maintenant nous avons bien le nombre d'années mais avec des décimales
En cliquant sur l'icone des types de données, il est facile de transformer ce résultat en nombre entier
Étape 6 : Créer les tranches d'âge automatiquement.
Cette étape va retourner les tranches d'âges juste en saisissant quelques exemples.
- Sélectionner la colonne des âges
- Ouvrir le menu Ajouter une colonne > Colonnes à partir d'exemples > A partir de la sélection
Cette action ouvre une nouvelle colonne sur la droite de vos données dans laquelle vous allez vous-même saisir les tranches que vous souhaitez créer.
Dans la première cellule, nous allons saisir la tranche 30-34.
Automatiquement, Power Query vous propose une liste "estimée" à partir de votre saisie.
Mais on voit que le deuxième regroupement proposé n'est pas le bon. Aucun souci, il suffit d'écrire par dessus la valeur proposée pour indiquer le résultat attendu.
En seulement 2 exemples de saisie, la liste est correcte. Dès que vous avez obtenu la liste que vous attendez, appuyez sur OK
Étape 7 : Regrouper par tranches d'âge et genre
Il nous faut maintenant regrouper chaque tranche d'âges, et par genre, que nous venons de créer et comptabiliser le nombre de personnes pour chaque tranche. Cette tâche se réalise en une seule action.
- Ouvrir le menu Transformer > Regrouper par
- Cliquez sur Avancé
- Choisissez la première colonne de regroupement ; la plage des âges
- Puis cliquez sur le bouton Ajouter un regroupement
- Sélectionner la colonne Genre
- Comme calcul, indiquer que vous voulez compter les lignes
- Validez en appuyant sur Ok
Et instantanément, le résultat s'affiche dans Power Query ????
Étape 8 : Permuter les données par genre
Il ne nous reste plus qu'à indiquer que la colonne des genres doit se retrouver en colonne.
- Sélectionnez la colonne Genre
- Cliquer sur le menu Transformer > Pivoter la colonne
Indiquez la colonne qui va être répartie entre les colonnes. C'est évidemment la colonne Nombre qui est à répartir.
Et c'est terminé ???????? Nous avons obtenu le résultat attendu.
Étape 9 : Transférer dans Excel
Pour transférer le résultat dans une feuille de calcul Excel, il suffit de cliquer sur le menu Accueil > Fermer & Charger
Et le résultat s'affiche maintenant dans votre feuille de calcul
Formation Power Query
Power Query est l'outil moderne d'importation et de transformation des données. Son utilisation est très simple et ne nécessite pas de connaissance avancée d'Excel ni de connaissance en programmation.
Si vous voulez vous former à l'utilisation de Power Query, je vous ai conçu cette formation sur Udemy très simple d'accès.
Vidéo tutorielle
Retrouvez toutes ces techniques dans la vidéo suivante.
