Calculer le taux d'absentéisme avec Excel

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


Sujets que vous pourriez aussi aimer :


Suivi du taux d'absentéisme

Ce quatrième exercice Excel dédié aux utilisateurs avancés est l'occasion de mettre en oeuvre les fonctions de dénombrement. Elles sont précieuses et par forcément connues. Elles permettent, selon leur nature de livrer des résultats statistiques offrant de rendre rapidement compte de la situation.



Il va s'agir dans cette mise en pratique de dresser un bilan explicite et dynamique sur le taux d'absentéisme d'une équipe de Rugby.

Source et présentation de la problématique
Pour simplifier la découverte et la mise en oeuvre des fonctions de décompte, nous débutons les travaux depuis un classeur source proposant la structure du tableau.

• Télécharger le classeur controle-des-absences.xlsx en cliquant sur son lien,
• Cliquer sur le nom du fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Ce classeur Excel est constitué d'une feuille nommée Suivi_absences. Son tableau recense tous les joueurs de l'équipe en colonne B, entre les lignes 6 à 22. Les dates des entraînements passés sont inscrites en ligne 5, entre les colonnes C et H. Au croisement de chacune de ces dates et de chacun des ces joueurs, une croix identifie l'absence.

En colonne I, il est demandé de comptabiliser le total des absences par joueur sur la période. Ce résultat est précieux pour comparer facilement l'assiduité de chaque licencié.

En lignes 23 et 24, pour chaque date donc, il s'agit de dénombrer respectivement le nombre de joueurs présents et de joueurs absents. Nous connaissons déjà la fonction Excel NbVal. Sur une plage donnée, elle permet de compter toutes les cellules non vides. En d'autres termes, ici elle permettra de fournir le total des absences, correspondant au nombre de cellules non vides, identifiées par des croix. En revanche, nous ne connaissons pas encore la fonction Excel permettant de compter les cellules vides sur une plage définie. A défaut, il est toujours possible de réaliser la différence entre le nombre total de cellules et le nombre de cellules non vides.

Enfin, il s'agira de déduire un taux d'absentéisme à formater en pourcentage. Ces ordres de grandeur seront d'une aide précieuse pour les entraîneurs et dirigeants du Club. Faut-il tirer la sonnette d'alarme, prendre des décisions ou intervenir spécifiquement auprès de certains licenciés trop volatiles.

Comptabiliser les absences
Nous proposons de livrer les premiers résultats en colonne I. Nous devons donc exploiter la fonction Excel NbVal sur chacune des lignes du tableau. Et pour ne pas oublier ce que nous avons appris, nous choisissons d'exploiter la méthode professionnelle de calcul la plus efficace avec Excel. Elle consiste à présélectionner la plage des résultats à trouver. Elle est grandement détaillée dans le livre Excel pour débuter avec les calculs.

• Sélectionner toutes les cellules des résultats, soit la plage I6:I22,
• Taper le symbole égal (=) pour débuter la formule,

Nous l'avons évoqué à maintes reprises, la première cellule d'une sélection n'est pas grisée. Excel indique qu'elle est active par défaut. C'est pourquoi le symbole égal ne s'inscrit que dans cette cellule. Nous allons donc pouvoir bâtir la formule comme si elle lui était dédiée, malgré la présélection.

• Saisir le nom de la fonction de dénombrement suivi d'une parenthèse, soit NbVal(,
• Sélectionner à la souris toutes les cellules de la ligne correspondante, soit la plage C6:H6,
• Fermer la parenthèse de la fonction NbVal,

Cette fonction, tout comme la fonction Nb ne demande qu'un seul paramètre pour réaliser son calcul. Il s'agit de la plage sur laquelle le décompte doit être réalisé.

• Valider nécessairement la formule par le raccourci clavier CTRL + Entrée,

Cette combinaison de touches est absolument essentielle pour aboutir le processus. C'est elle qui permet en même temps de valider le calcul et de le répliquer sur l'ensemble des cellules présélectionnées.

Tous les résultats sont instantanément livrés dans la colonne et ils sont cohérents. Chaque joueur est désormais identifié par un compteur d'absence. Certaines valeurs sont sans appel. Quelques licenciés tranchent pour leur négligence et manque d'implication. Cette méthode de calcul, particulièrement efficace, nous a permis d'éviter de tirer la poignée du résultat. La logique de la formule s'est répliquée d'elle-même sur la hauteur du tableau.

• Double cliquer sur le dernier calcul en cellule I22 pour afficher sa syntaxe,

Cette technique permet d'identifier plus facilement les cellules impliquées dans le calcul. Excel les fait ressortir sur un léger fond de couleur, bleu ici. Et comme vous le remarquez, les références ont parfaitement suivi le déplacement en hauteur, pour compter les absences du joueur correspondant. Et sur cette dernière ligne, aucune croix n'est à déplorée, expliquant le résultat vierge.

• Enfoncer la touche Echap du clavier pour abandonner la modification de la cellule,

Cette technique est une sécurité visant à ne pas endommager la formule.

Compter les cellules vides
Pour comptabiliser les présences en ligne 23, le processus est inverse. Il s'agit d'être en mesure de compter les cellules vides sur chaque colonne, en partant de la colonne C. La fonction NbVal nous a donc permis de dénombrer les cellules non vides. Nous connaissons de même la fonction Excel Nb. Et à ce titre, nous savons qu'elle n'apporte pas la solution puisque sa vocation est de compter les cellules numériques sur une plage définie.

Nous pourrions être tentés d'exploiter la fonction Excel Nb.Si que nous connaissons bien. Elle permet de compter sur une plage définie, toutes les cellules répondant à un critère. Ce critère pourrait consister à identifier les cellules vides.

Mais cet exercice du niveau avancé est aussi l'occasion de découvrir de nouvelles fonctions, au demeurant fort simples. Excel propose la fonction Nb.Vide. Comme son nom l'indique, cette fonction comptabilise toutes les cellules vides sur une plage donnée. Son fonctionnement est donc aussi simple que la précédente. Il suffit de lui fournir la plage de cellules sur laquelle elle doit compter.

• Sélectionner tous les résultats à trouver, soit la plage de cellules C23:H23,
• Taper le symbole égal (=) pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse, soit Nb.vide(,
• Sélectionner les cellules de la première colonne, soit la plage C6:C22,
• Fermer la parenthèse de la fonction Nb.Vide,
• Puis, valider la formule par CTRL + Entrée pour la répliquer sur toute la ligne,

Le décompte des présences est ainsi livré pour chaque date, soit pour chaque entraînement. Si vous double cliquez sur le dernier résultat en H23 pour afficher sa formule, vous remarquez là encore que les références des cellules impliquées se sont parfaitement adaptées. Le décompte est bien organisé sur la dernière colonne correspondant au dernier entraînement. N'oubliez pas la touche Echap du clavier pour abandonner la modification.

Compter les cellules non vides
Nous devons de nouveau exploiter la fonction NbVal. Cette fois, il s'agit de comptabiliser les absences par entraînement. Ce résultat sera intéressant à comparer au précédent.

• Sélectionner tous les résultats à trouver, soit la plage de cellules C24:H24,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir le nom de la fonction suivi d'une parenthèse, soit NbVal(,
• Sélectionner la première plage sur laquelle compter, soit C6:C22,
• Fermer la parenthèse de la fonction NbVal,
• Puis, valider la formule par CTRL + Entrée pour la répliquer sur les cellules sélectionnées,

Comme toujours, les résultats tombent instantanément. La logique est automatiquement répliquée sur la plage. Il est intéressant, voire inquiétant, de constater que pour certaines dates, le nombre d'absents est supérieur au nombre de présents.

Vous pouvez bien sûr double cliquer sur le dernier résultat H24 pour constater que le calcul est bien appliqué sur la dernière colonne.



Calculer le taux d'absentéisme
Le dernier résultat à livrer en ligne 25 est particulièrement important. Il doit permettre de juger de la situation afin de prendre les décisions qui s'imposent. Le comportement des licenciés est-il inquiétant ? Comme il s'agit d'un taux, le résultat est à formater en pourcentage. Le calcul consiste à diviser le nombre d'absents par le nombre total de joueurs. Nous connaîtrons ainsi la proportion d'absents, soit le taux d'absentéisme. Le nombre d'absents vient d'être délivré par le calcul précédent. Le nombre total de joueurs peut se déduire. Il correspond au nombre d'absents à additionner avec le nombre de joueurs présents. Le calcul du taux consiste donc à diviser le nombre d'absents par le résultat de cette addition.

L'opération ne consistant pas à exploiter une seule fonction, nous allons devoir poser la syntaxe manuellement.

• Sélectionner le premier taux à calculer, soit la cellule C25,
• Taper le symbole égal (=) pour débuter la formule,
• Désigner le nombre d'absents de la colonne pour intégrer sa référence C24 dans le calcul,
• Taper le slash (/) du pavé numérique pour enclencher la division,
• Saisir le nom de la fonction pour l'addition suivi d'une parenthèse, soit Somme(,
• Sélectionner les deux cellules du dessus pour désigner la plage C23:C24,
• Fermer la parenthèse de la fonction Somme,
• Valider la formule par le raccourci CTRL + Entrée pour garder la cellule active,

Le résultat obtenu est fort logiquement inférieur à 1. Il s'agit d'un taux que nous formaterons en pourcentage. La valeur 0,64 indique que 64% des joueurs ont été absents pour cette première séance. Le taux d'absentéisme est donc très important. Ces résultats de synthèse facilitent la lecture des données. Ils font immédiatement ressortir les anomalies et problèmes.

• Tirer la poignée du résultat à l'horizontale jusqu'en colonne H,
• Dans la section Nombre du ruban Accueil, cliquer sur le bouton Style de pourcentage,

Les données sont plus claires à lire ainsi. Les décimales disparaissent à l'affichage. Elles ne sont en effet pas nécessaires. Seuls les ordres de grandeur nous intéressent pour fournir une indication pertinente sur le taux d'absentéisme.



L'opération est parfaitement adaptée dans chaque rangée. C'est bien le nombre d'absents qui est divisé par le nombre total de joueurs, pour chaque période respective.

Les nombres entiers des lignes 23 et 24 ne sont pas parfaitement alignés dans leur cellule. Il manque un retrait. De fait, leur présentation n'est pas homogène avec les autres données numériques.

• Sélectionner la plage de cellules C23:H24,
• Dans la section Alignement du ruban Accueil, cliquer sur le bouton Augmenter le retrait,

Les alignements sont désormais tous conformes. La présentation d'un tableau Excel ne doit jamais être négligée. Le confort et la facilité de lecture en dépendent.

Les calculs sont désormais terminés. Mais nous pouvons encore améliorer la pertinence du modèle.

Alertes visuelles sur les absences
Pour parfaire cette application en effet, nous souhaitons mettre dynamiquement en valeur les joueurs les plus assidus et les moins assidus. Les symboliques de couleurs associées doivent être adaptées. Comme vous le savez, c'est la mise en forme conditionnelle d'Excel qui permet de faire réagir des cellules en fonction de leur contenu. Mais ici, nous souhaitons que ces alertes se déclenchent sur toute la ligne.

Nous devons donc construire une règle spécifique sur l'ensemble du tableau. Elle doit dynamiquement déclencher un format lorsque le nombre d'absences est le plus petit de tous. La fonction Min doit donc être exploitée dans le critère de comparaison de la règle. Mais nous souhaitons aussi mettre en évidence les moins assidus. Cette seconde règle doit donc repérer le nombre d'absences le plus important dans la colonne. Et c'est donc la fonction Max qui doit être utilisée dans le critère de comparaison.

• Sélectionner la plage de cellules B6:I22,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie placée juste en dessous pour l'activer,
• Taper le symbole égal (=) pour débuter la syntaxe du critère,
• Désigner le premier total d'absences pour intégrer sa référence I6 dans la règle,

Comme vous le remarquez, elle est instantanément figée : $I$6. Or chaque total, donc pour chaque joueur situé sur une ligne différente, doit être comparé avec les autres. Cette cellule doit être libre de se déplacer en lignes. En revanche, nous devons la garder figée en colonne. En effet, pour un même joueur, le critère doit seulement être vérifié par rapport à son total d'absences.

• Enfoncer deux fois la touche F4 du clavier,

De cette manière, seul un dollar persiste. Il empêche le déplacement à l'horizontale car il est placé devant l'indice de colonne : $I6.

• Taper le symbole égal (=) pour enclencher la comparaison du critère,
• Saisir la fonction permettant d'extraire la plus petite valeur suivie d'une parenthèse, soit Min(,
• Sélectionner tous les totaux d'absences, soit la plage de cellules I6:I22,

Une fois encore, vous remarquez que cette plage apparaît naturellement figée : $I$6:$I$22. Mais ici, c'est bien ce que nous souhaitons. Chaque total doit être comparé aux valeurs de cette plage. Elle ne doit donc pas se déplacer ni en colonne, ni en ligne.

• Fermer la parenthèse de la fonction Min,

La syntaxe de notre règle est terminée. Nous devons désormais définir le format dynamique associé.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Choisir un vert assez clair dans la palette de couleurs,
• Puis, cliquer sur le bouton Ok pour revenir sur la première boîte de dialogue,

Le résumé offert par la boîte de dialogue est limpide. Il indique qu'un remplissage vert se déclenchera dès lors que le total d'absences est le plus petit de tous.

• Cliquer de nouveau sur le bouton Ok pour valider la nouvelle règle,

De retour sur la feuille, vous constatez que deux joueurs sont instantanément repérés. Tous deux n'ont jamais été absents en effet. Ces alertes sont dynamiques. Si ces totaux venaient à évoluer, les couleurs se déplaceraient automatiquement pour repérer d'autres licenciés. Elles attirent l'oeil rapidement et facilitent la compréhension du tableau. Deux éléments de l'équipe sont irréprochables.



Une règle similaire doit être combinée sur la même plage de cellules pour repérer les joueurs les moins sérieux. Elle doit exploiter la fonction Max : =$I6=MAX($I$6:$I$22). Il convient de l'associer avec une symbolique de couleur adaptée, comme un rouge orangé par exemple.

Grâce à nos formules et formats dynamiques, notre modèle est tout à fait opérationnel. Il délivre des informations précieuses dès la première lecture. Il ne reste plus qu'à l'exploiter pour en tirer les conclusions.