Répartir la population par tranches d'âge et ville

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Répartition par tranches d'âges

Nous l'avons constaté dans les mises en pratiques précédentes, les calculs matriciels regorgent de ressources et permettent des prouesses. Dans le dernier exercice, nous avons manipulé les heures afin de dresser un bilan des horaires cumulés sur des critères recoupés. Ici, nous allons voir qu'il est aussi évident de manipuler des dates.



Le résultat finalisé à aboutir est illustré par la capture ci-dessus. Nous parvenons à établir la répartition de la population par tranches d'âges et par villes. Ces calculs sont posés sur une base de données dans laquelle des individus sont référencés. Parmi les éléments les détaillant, figure la date de naissance pour chacun. C'est elle qui doit nous permettre de déduire l'âge pour réaliser la catégorisation.

Source et concept
Cette source d'information est nécessaire pour démarrer l'étude.

• Télécharger le classeur population-employes.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Des personnes sont donc référencées dans un tableau s'étendant de la colonne B à la colonne F. On y retrouve notamment l'information sur la date de naissance et sur la ville dans les deux dernières rangées. Si vous déployez la liste déroulante de la zone Nom, en haut à gauche de la feuille Excel, vous remarquez que chaque colonne est nommée en fonction de son propre titre.



Nous exploiterons ces noms dans la syntaxe des formules pour simplifier la construction.

Sur la droite, nous trouvons le tableau de synthèse. Il attend les calculs permettant d'établir précisément la répartition de la population selon la tranche d'âge et la ville. Si vous sélectionnez l'une des cellules de ces tranches en ligne 6, en consultant la barre de formule, vous notez qu'un format spécifique est appliqué.



La cellule affiche l'information textuelle complète. Les intitulés sont ainsi plus clairs pour l'utilisateur. Mais la barre de formule qui restitue le contenu exact, ne conserve que la valeur numérique. C'est ainsi que nous allons pouvoir exploiter ces cellules dans la construction des critères à vérifier.

Pour constater la création de ces formats, après avoir sélectionné l'une des cellules de la ligne 6 :

• Dans la section Nombre du ruban Accueil, déployer la liste déroulante des formats,
• Tout en bas de la liste, choisir Autres formats numériques,

La boîte de dialogue des formats apparaît. Elle pointe sur la catégorie Personnalisée. En consultant la zone Type, vous remarquez que cet affichage explicatif consiste en une concaténation avec la valeur numérique d'origine.

Pour établir la répartition des individus par tranches d'âges, nous aurons besoin d'exploiter la fonction Excel volatile DateDif. En effet, nous ne disposons pas de l'information sur l'âge. Seule l'indication sur la date de naissance est fournie. C'est une bonne chose dans la mesure où l'âge évolue à chaque jour qui passe. Nous allons donc devoir reconstruire ces âges grâce à la fonction DateDif :

=Datedif(date_debut ; date_fin ; 'unite_pour_difference')

En fonction d'une première date mentionnée en premier paramètre et d'une seconde inscrite en deuxième argument, elle calcule la différence selon l'unité stipulée en dernier argument. Avec la valeur y pour Year, nous indiquerons que nous souhaitons calculer cet écart en années. C'est ainsi que nous en déduirons l'âge de chacun pour établir cette répartition dynamique.

Croiser les conditions sur des matrices
Par défaut, la fonction matricielle SommeProd permet de multiplier les valeurs respectives des matrices qui lui sont passées en paramètres. Mais, comme nous l'avons déjà démontré à maintes reprises, en émettant des conditions sur chacune de ces matrices, elle renvoie des indicateurs booléens repérant les enregistrements concordants. Ces indicateurs sont fournis sous forme de chiffres : 1 pour Vrai et 0 pour Faux.

La condition est double sur l'âge. Il s'agit en effet d'une fourchette. Le résultat de son calcul doit être compris entre une borne inférieure et une borne supérieure. Cette double condition doit être recoupée avec le critère sur la ville. Les matrices impliquées correspondent aux colonnes DateN et Ville.

• Sélectionner la case du premier résultat à trouver soit la cellule I7,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Taper la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir une nouvelle parenthèse pour accueillir la première matrice conditionnelle,
• Saisir la fonction pour le calcul de l'âge suivie d'une parenthèse, soit : Datedif(,
• Désigner la colonne des dates de naissance par son nom, soit : DateN,
• Taper un point-virgule (;) pour passer dans l'argument de la date de fin,

Le calcul de l'âge doit s'opérer en fonction de la date du jour qui évolue avec le calendrier. Et comme vous le savez, c'est la fonction Excel Aujourdhui qui renvoie cette information.

• Saisir la fonction pour la date du jour suivie de deux parenthèses, soit : Aujourdhui(),
• Taper un point-virgule (;) pour passer dans l'argument de l'unité,
• Saisir la lettre y entre guillemets, soit : 'y',

Comme nous l'avons évoqué, c'est ce paramètre qui impose de réaliser le calcul sur la différence, en nombre d'années. Il en résultera l'âge.

• Fermer la parenthèse de la fonction DateDif,
• Puis, taper l'inégalité suivante : >H$6,

Nous désignons ainsi la cellule vide située juste avant la première tranche. Sa valeur sera considérée comme nulle. En conséquence ce premier critère consiste à isoler les âges positifs. Mais ils doivent aussi être inférieurs à 20 ans. Nous devons donc recouper cette condition. Et c'est cette construction qui va nous permettre de répliquer la formule pour effectuer la répartition sur toutes les tranches.

Notez que nous avons pris soin de figer la ligne de la cellule H6 et de libérer sa colonne (H$6). En répliquant la formule sur les colonnes de droite, la borne inférieure se déplacera naturellement pour honorer ces fameuses tranches.

• Fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la nouvelle condition à recouper,
• Ouvrir une parenthèse pour accueillir la nouvelle matrice conditionnelle,
• Saisir la fonction pour le calcul de l'âge, suivie d'une parenthèse, soit : DateDif(,
• Désigner de nouveau la plage des dates par son nom, soit : DateN,
• Taper un point-virgule (;) pour passer dans le paramètre de la date de fin,
• Saisir la fonction pour la date du jour, suivie de deux parenthèses, soit : Aujourdhui(),
• Taper un point-virgule (;) pour passer dans l'argument de l'unité,
• Saisir la lettre y entre guillemets, soit : 'y',
• Fermer la parenthèse de la fonction DateDif,
• Puis, taper l'inégalité suivante : <=I$6,

Il s'agit cette fois de la borne supérieure de la tranche dans laquelle nous devons répartir les personnes. Là encore, nous prenons soin de figer la ligne et de libérer la colonne (<=I$6).

• Fermer la parenthèse de cette deuxième matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la dernière condition à recouper,
• Ouvrir une parenthèse pour accueillir la dernière matrice conditionnelle,
• Désigner la plage des villes par son nom, soit : Ville,
• Taper le symbole égal (=) pour annoncer la contrainte à honorer,
• Taper les coordonnées de la première ville du tableau de bord, soit : H7,
• Enfoncer trois fois la touche F4 du clavier, ce qui donne : $H7,

Ainsi, nous la figeons en colonne et la libérons en ligne. Nous devons en effet répliquer ce calcul sur les lignes du dessous et considérer les autres villes mentionnées. Par contre, pour les autres tranches, placées sur les colonnes de droite, la contrainte doit toujours être vérifiée dans cette rangée. Donc, la colonne ne doit pas bouger.

• Fermer la parenthèse de cette dernière matrice conditionnelle,
• Fermer la parenthèse de la fonction SommeProd,
• Puis, valider la formule matricielle par le raccourci clavier CTRL + Entrée,

De cette manière, nous conservons active la cellule du résultat. Le but est de l'exploiter dans l'enchaînement. Le premier résultat tombe. Vraisemblablement, aucune personne de cette base de données de moins de 20 ans n'habite la ville de Lyon.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur les villes du dessous,
• Cliquer et glisser la poignée de la sélection sur les trois colonnes situées à droite,

Cette fois la répartition semble se dessiner.



D'ailleurs, si vous sélectionnez l'intégralité des résultats et que vous consultez la barre d'état en bas de la fenêtre Excel, vous notez que la somme des valeurs conduit à 18 personnes. Il s'agit effectivement du nombre de lignes dans ce tableau de données. Nous avons donc solutionné la répartition de la population par calculs matriciels, selon la formule suivante :

=SOMMEPROD((DATEDIF(DateN; AUJOURDHUI(); 'y')>H$6)*(DATEDIF(DateN; AUJOURDHUI(); 'y')<=I$6)*(Ville=$H7))