Dénombrement statistique multi-critères 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 :


Gestion statistique conditionnelle des arrêts de travail

Dans cette formation Excel, nous proposons de réaliser un tableau de bord pour la gestion et le suivi des arrêts de travail au sein d'une entreprise. Il s'agit de fournir des statistiques précises afin de mieux appréhender les causes et anticiper les risques. Pour un meilleur dénombrement, nous devons être en mesure de fournir un recensement catégorisé en fonction de nombreux critères à recouper. Excel propose des fonctions permettant de réaliser des statistiques conditionnelles. NB.SI par exemple, permet de comptabiliser des éléments selon un critère. Mais des statistiques précises imposent souvent de recouper plusieurs critères ensemble, pour un dénombrement pertinent. Excel propose alors les fonctions Nb.Si.Ens et Moyenne.Si.Ens. Elles retournent des données synthétisées, répondant à un ensemble de critères.

Recensement statistique des arrêts de travail
Nous partons d'un tableau existant dans lequel sont recensés les salariés de l'entreprise avec la liste des arrêts de travail, leur gravité et leur durée.

• Télécharger le classeur suivi-arrets-travail.xlsx en cliquant sur son lien,

Ce classeur est constitué de trois feuilles, Liste AT, Questions Liste AT et Tableau bord AT, AT pour Arrêt de Travail. La feuille Liste AT recense les arrêts de travail rattachés aux employés. Y sont reliées des informations sur le sexe de la personne, la durée de l'arrêt et la date de l'accident par exemple. La feuille Questions Liste AT énumère les consignes à suivre pour monter le tableau de bord. Pour suivre ces consignes, la feuille Tableau bord AT propose le squelette des fiches statistiques à renseigner, afin de gérer au mieux ces arrêts de travail.


Recenser le nombre d'arrêts de travail
Pour comptabiliser le nombre d'arrêts de travail survenus, il suffit de compter le nombre de lignes renseignées dans le tableau de la feuille Liste AT. Nous partons du principe que ce tableau peut évoluer à tout moment, en fonction des nouveaux arrêts de travail, renseignés à la suite. Nous allons donc exploiter la fonction Excel NBVal sur la colonne A du tableau de la feuille Liste AT. NBVal compte le nombre de cellules non vides dans la colonne. Donc si un nouvel arrêt de travail est ajouté à la suite du tableau, il est pris en compte et comptabilisé.

• Cliquer sur l'onglet Tableau bord AT en bas de la fenêtre Excel, pour activer sa feuille,
• Sélectionner la cellule B4 dans laquelle doit être calculée le nombre dynamique d'arrêts,
• Taper le symbole = suivi du nom de la fonction et d'une parenthèse ouvrante, soit =NBVAL(,

La fonction NBVal demande un argument, la plage sur laquelle elle doit compter les cellules non vides. Comme sa longueur est indéfinie, nous allons lui passer l'intégralité de la colonne, en cliquant sur son étiquette.

• Cliquer sur l'onglet de la feuille Liste AT puis cliquer sur l'étiquette de colonne A,
• Fermer la parenthèse de la fonction et saisir -1 à la suite,
• Valider le calcul en enfonçant la touche Entrée du clavier,

Nous retranchons 1 au résultat du décompte retourné par la fonction NBVal, afin de ne pas considérer la ligne de titre du tableau des arrêts de travail. Nous avions déjà exploité cette fonction NBVal, notamment pour créer des listes déroulantes au contenu évolutif et réaliser des recherches dynamiques dans Excel. En effet, si vous ajoutez une ligne, à la suite du tableau de la feuille Liste AT, vous constatez que le résultat est incrémenté, dans le tableau de bord. Le décompte est donc bien dynamique.

Statistiques sur la durée des arrêts de travail
Le petit tableau sur la droite propose d'afficher des valeurs statistiques sur la durée des arrêts de travail. Ils sont référencés en nombres de jours dans le tableau source. Il s'agit simplement d'exploiter les fonctions Excel Moyenne, Min et Max, respectivement en E4, E5 et E6. Comme pour le précédent calcul, ce résultat statistique doit être dynamique. Cela signifie que si de nouveaux arrêts sont renseignés à la suite du tableau, les nouvelles durées doivent être considérées. L'intérêt de ces trois fonctions est qu'elles réalisent leur calcul sur des valeurs numériques. En d'autres termes, elles ignorent tout ce qui n'est pas un nombre, soit les cellules vides et les cellules de texte. Il suffit donc de leur indiquer la colonne entière plutôt qu'une plage de cellules aux bornes fixes pour réaliser le calcul.

• Sélectionner la cellule E4 et taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction suivi d'une parenthèse ouvrante, soit =MOYENNE(,
• Sélectionner la feuille Liste AT en cliquant sur son onglet,
• Désigner la colonne des durées en cliquant sur son étiquette I, en haut de la feuille,
• Fermer la parenthèse et valider le calcul par la touche Entrée,
• De la même manière, réaliser le calcul du minimum et du maximum, en E5 et E6,

Ces trois fonctions en effet ne demandent qu'un seul paramètre. Ce paramètre est la plage de cellules à partir de laquelle réaliser le calcul. Le fait de désigner la colonne par son étiquette indique une plage de cellules, dont les bornes finales ne sont pas définies. D'ailleurs, si vous ajoutez des durées, dans la colonne, à la suite du tableau, vous constaterez que les résultats du tableau de bord varient. Nos calculs sont donc bien dynamiques.


Il s'agit maintenant de calculer en pourcentage, le nombre d'incidents qui se traduisent en arrêts de travail. Nous connaissons le nombre total d'AT calculé en cellule B4. La colonne H du tableau de la feuille Liste AT indique si l'arrêt de travail est confirmé (O). Il s'agit donc de comptabiliser ces arrêts et de les diviser par le nombre total d'AT, pour obtenir le ratio. Pour ce faire la fonction NB.SI qui permet de comptabiliser toutes les cellules d'une plage répondant à un critère, est tout à fait indiquée. Sa syntaxe est la suivante : =nb.si(plage_ou_compter ; critere_a_compter). Une fois encore, pour un résultat dynamique, la plage est la colonne toute entière, soit la colonne H. Le critère est la lettre O. Comme il s'agit d'un texte, nous devons l'indiquer entre guillemets.

• En cellule E8, taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit =NB.SI(,
• Cliquer sur l'onglet Liste AT pour activer sa feuille,
• Désigner la plage de cellules en cliquant sur l'étiquette de colonne H,
• Taper un point-virgule (;) pour passer à l'argument du critère,
• Saisir la lettre O entre guillemets, soit 'O',
• Fermer la parenthèse de la fonction et taper un Slash (/) pour la division,
• Cliquer sur l'onglet Tableau bord AT pour revenir sur sa feuille,
• Cliquer sur la cellule qui comptabilise le nombre d'AT, soit B4,
• Valider le calcul en enfonçant la touche Entrée,

Nous obtenons un résultat de 80%. La capture ci-dessus illustre le calcul bâti, dans la barre de formule de la cellule.

Le tableau de la feuille Liste AT, recense en colonne F, les heures auxquelles sont intervenus ces accidents. Ces heures sont écrites au format Heure et non au format décimal . La fonction Moyenne sait très bien manipuler les heures en même temps qu'elle sait ignorer les cellules vides ou les cellules de texte, comme nous l'avons vu précédemment. Pour le calcul suivant en cellule E10, il s'agit donc d'appliquer la fonction Moyenne sur la colonne F de la feuille Liste AT. Ainsi et comme précédemment, nous obtiendrons un résultat dynamique, si le tableau venait à évoluer.

• En E10, saisir la formule suivante : =MOYENNE('Liste AT'!F:F),

Le résultat retourne 12:48.

Taux d'arrêt de travail selon le sexe - Statistiques conditionnelles
Le petit tableau qui suit consiste à comparer la proportion d'arrêts de travail entre les hommes et les femmes. Ce résultat donnera sans doute une indication importante pour mieux appréhender les risques dans l'entreprise. Il s'agit donc de diviser le nombre total d'AT correspond au sexe par le nombre total d'AT tout sexe confondu. C'est une fois de plus la fonction NB.SI appliquée sur la colonne D avec en critère le sexe, qui permettra de retourner ce résultat à diviser. Le principe étant toujours le même :

• En B14, réaliser le calcul suivant : =NB.SI('Liste AT'!D:D; 'H')/B4,
• En B15, réaliser le calcul suivant : =NB.SI('ListeAT'!D:D; 'F')/B4,

Les résultats sont sans appels. Le nombre d'arrêts de travail est beaucoup plus important chez les hommes. Comme il s'agit de ratios, Excel peut rendre cette différence encore plus flagrante graphiquement, avec les secteurs. La formation Excel pour interpréter les données graphiquement, aborde ces représentations de ratios. Pour représenter graphiquement la répartition du nombre d'AT hommes et femmes, il s'agit premièrement de sélectionner l'intégralité du tableau.

• Sélectionner la plage de cellules A13:B15,
• Cliquer sur l'onglet Insérer pour activer son ruban,
• Dans la section Graphiques, cliquer sur la flèche des secteurs,
• Dans la liste, choisir Secteurs 3D,

Nous aurions pu passer par le bouton Graphiques recommandés. Excel aurait reconnu les ratios et aurait naturellement proposé une représentation sous forme de secteurs. Le graphique étant déposé sur la feuille, comme l'illustre la capture ci-dessous, il s'agit de le personnaliser, notamment en affichant les étiquettes de données, pour les pourcentages.

• Cliquer une première fois sur le titre du graphique pour le sélectionner,
• Cliquer une seconde fois à l'intérieur du cadre pour activer sa saisie,
• Réaliser le raccourci clavier CTRL + A pour sélectionner tout le texte du titre,
• Le remplacer par : Nombre d'AT selon le sexe et le formater en gras,
• Cliquer sur le symbole + en haut à droite du graphique,
• Dans la liste, décocher la case Légende et cocher la case Etiquettes de données,
• Cliquer ensuite sur la flèche à l'extrémité droite de la rubrique Etiquettes de données,
• Dans la liste, choisir Légendes de données,

Nous obtenons une représentation graphique sans appel, facilitant l'interprétation des données en temps réel.

Dénombrement conditionnel avec critères de bases de données
Le tableau suivant, sur la plage A28:B40, propose d'afficher les statistiques des nombres d'arrêts de travail par mois. Ces mois sont saisis en date complète. Par exemple, le contenu exact de la cellule A29 est 01/01/2014. C'est son format de cellule personnalisé qui permet de n'afficher que le mois de la date en texte. C'est d'ailleurs l'une des astuces proposées par la formation sur les calculs avec des heures et des dates dans Excel.

Chaque arrêt de travail est en effet recensé avec sa date, dans la colonne E du tableau de la feuille Liste AT. Il s'agit donc de dénombrer les AT pour lesquels la date est comprise entre le début du mois en cours et le début du mois suivant. Mais la fonction NB.SI permet de dénombrer selon un seul critère à vérifier sur une plage de cellules. C'est pourquoi il existe la fonction Excel NB.SI.ENS qui permet de compter les cellules correspondant à un ensemble de critères sur des plages de cellules.

• Sélectionner la cellule B29 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction et ouvrir la parenthèse, soit nb.si.ens(,
• Puis, cliquer sur l'onglet de la feuille Liste AT et cliquer sur l'étiquette de colonne E,
• Taper un point-virgule (;) pour passer à l'argument du premier critère,
• Saisir l'expression suivante '>=' & A29,

Dans la zone de critère, l'opérateur doit être spécifié entre guillemets. Grâce à la concaténation (&), nous spécifions que la date de l'AT doit être supérieure ou égale à la date en A29, soit au 1er janvier 2014.

• Taper de nouveau un point-virgule (;) afin de définir la plage pour le critère suivant,
• Cliquer de nouveau sur l'étiquette de colonne E de la feuille Liste AT,
• Taper un point-virgule (;) pour passer à l'argument du premier critère,
• Saisir l'expression suivante '<' & A30,
• Fermer la parenthèse de la fonction et valider le calcul par CTRL + Entrée,
• Puis, double cliquer sur la poignée de la cellule pour le répliquer sur toute la colonne,

Le dénombrement multicritère fonctionne parfaitement. Pour chaque mois, nous parvenons à comptabiliser les arrêts de travail dont la date est supérieure ou égale au début du mois et inférieure strictement à celle du début du mois suivant. Comme nous désignons des plages de cellules par les étiquettes de colonne, pour les rendre dynamiques, l'emploi des références absolues afin de figer les plages, n'est pas nécessaire.


Nous proposons désormais de rendre évidente la répartition des arrêts de travail au cours de l'année, en représentant graphiquement leur évolution au cours des mois. Les graphiques courbes sont adaptés pour représenter les évolutions des données sur une échelle de temps.

• Sélectionner l'intégralité du tableau à représenter, soit A28:B40,
• Cliquer sur l'onglet Insérer, en haut de la fenêtre Excel, pour activer son ruban,
• Dans la section Graphiques, cliquer sur la flèche des graphiques en courbes,
• Dans la liste, choisir le premier de la deuxième ligne, Courbes avec marqueurs,

Le graphique est déposé sur la feuille. Il apparaît très nettement qu'une recrudescence des AT se produit à partir du mois de Mai, jusqu'à la fin de l'été.

• Modifier son titre en Nombre d'AT par mois,
• Cliquer sur le bouton + en haut à droite du graphique,
• Dans la liste, cocher la case Etiquettes de données,

Ainsi les valeurs exactes s'affichent sur les pics de la courbe, désignés par les marqueurs. Le tableau qui suit propose de catégoriser statistiquement ces arrêts de travail. Il s'agit de recenser leur nombre selon leur nature, siège des lésions. Cette information est renseignée dans le tableau d'origine de la feuille Liste AT, en colonne J. Une fonction NB.SI usuelle, appliquée sur la colonne entière, avec comme critère la lésion, suffira à dénombrer ces AT par catégorie.

• Sélectionner la première cellule du calcul, soit B44,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit Nb.si(,
• Cliquer sur l'onglet Liste AT pour activer sa feuille,
• Cliquer sur l'étiquette de colonne J pour désigner l'intégralité de la colonne comme plage de cellules où rechercher le critère,
• Taper un point-virgule (;) pour passer à l'argument du critère,
• Saisir A44 qui correspond aux références de la cellule des lésions à comptabiliser,
• Fermer la parenthèse et valider la formule par le raccourci CTRL + Entrée,
• Double cliquer sur la poignée de la cellule du résultat pour répliquer le calcul sur la colonne,

Tous les résultats répartis par lésions sont désormais clairement comptabilisés sur chaque ligne du tableau. Une fois de plus, ces résultats restent dynamiques et évolutifs grâce à la désignation de la plage de cellules par l'étiquette de colonne. Ainsi, si de nouveaux AT venaient à être renseignés, à la suite du tableau, ils seraient comptabilisés. Pour une meilleure identification des causes, nous proposons de représenter graphiquement la répartition des lésions avec un graphique permettant de les comparer. Les histogrammes sont parfaitement adaptés pour ce type de rendu.

• Sélectionner l'intégralité du tableau à représenter, soit la plage A43:B53,
• Dans la section Graphiques du ruban Insérer, cliquer sur la flèche des Histogrammes,
• Choisir le premier de la section Histogrammes 3D, Histogramme 3D groupé,
• Une fois posé sur la feuille, changer son titre en : Nombre d'AT par siège des lesions,
• Puis, comme toujours, afficher les étiquettes de données de manière à afficher les valeurs sur le graphique,

Il apparaît très nettement que les accidents aux mains sont les plus fréquents. Puis suivent les genoux et le dos. Ces graphiques associés aux données statistiques extraites du tableau source, offrent une meilleure appréhension des problèmes. Ils permettent peu à peu d'envisager des protocoles de sécurité plus rigoureux, afin de minimiser les accidents.


Moyenne statistique selon un ensemble de critères
Pour une compréhension encore accrue, le dernier tableau propose de représenter la moyenne de l'âge des salariés impliqués, sectorisés selon le sexe et la gravité de la lésion. Nous avons donc besoin de calculer l'âge du salarié au moment de l'accident. C'est une information qui est en effet laissée vide, dans le tableau source de la feuille Liste AT. Pour ce faire, nous devons utiliser la fonction DateDif abordée par le support de formation sur les heures et les dates. Sa syntaxe est la suivante : =Datedif(date_depart ; date_arrivee ; 'y'). Le dernier argument indique que nous souhaitons obtenir l'écart entre les deux dates, en nombre d'années, y pour Year, soit année en anglais.

• Cliquer sur l'onglet Liste AT pour afficher sa feuille,
• Sélectionner toutes les cellules à calculer dans la colonne C de l'âge, soit C2:C36,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit Datedif(,
• Sélectionner la date de départ soit B2 pour la date de naissance,
• Taper un point-virgule (;) pour passer à l'argument suivant, celui de la date d'arrivée,
• Cliquer sur E2 pour désigner la date de l'accident,
• Taper un point-virgule (;) pour passer à l'argument sur le format de sortie du calcul,
• Taper la lettre Y entre guillemets 'y' puis fermer la parenthèse,
• Valider le calcul par CTRL + Entrée pour le répliquer sur l'ensemble de la colonne,

La colonne affiche désormais l'âge de la personne, en années, au moment de l'accident.

• Cliquer sur l'onglet Tableau bord AT pour revenir sur la feuille des calculs,

Nous avons déjà appris la fonction Excel Moyenne.Si , dans l'une de nos formations. Mais cette fonction permet de réaliser une moyenne conditionnelle sur une plage de cellules, selon un critère posé sur une plage de cellules qui d'ailleurs, peut-être la même que celle du calcul de la moyenne. Or ici, il s'agit bien de réaliser une moyenne conditionnelle mais en vérifiant deux critères, le sexe et la gravité de la lésion. Au même titre que la fonction Nb.Si.Ens que nous avons abordée plus haut pour le décompte, il s'agit donc de vérifier un ensemble de critères. C'est pourquoi Excel propose la fonction Moyenne.Si.Ens dont la syntaxe est la suivante : =MOYENNE.SI.ENS(Plage_moyenne ; Plage_critere1 ; Critere1 ; Plage_critere2 ; Critere2 ; etc...).

Nous allons donc définir trois plages de cellules et deux critères. Il faut une plage de cellules pour désigner les valeurs à moyenner selon deux critères posés sur des plages respectives.

• Sélectionner la cellule B61 et taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction suivi d'une parenthèse ouvrante, soit Moyenne.si.Ens(,
• Cliquer sur l'onglet Liste AT pour activer sa feuille,
• Cliquer sur l'étiquette C de la colonne de l'âge pour désigner les valeurs à moyenner,
• Taper un point-virgule (;) pour passer à l'argument de la plage pour le critère,
• Cliquer sur l'étiquette D pour désigner intégralement la colonne du sexe,
• Taper un point-virgule (;) pour passer à l'argument du critère sur cette plage,
• Taper H entre guillemets ('H') pour isoler les hommes dans la moyenne à calculer,
• Taper un point-virgule (;) pour passer à l'argument de la plage pour le second critère,
• Cliquer sur l'étiquette K afin de désigner toute la colonne des codes lésions,
• Taper un point-virgule (;) pour passer à l'argument du second critère,
• Cliquer sur l'onglet Tableau bord AT pour revenir sur la feuille du calcul,
• Sélectionner la cellule A61 qui correspond à la première valeur pour le critère des lésions,
• Fermer la parenthèse de la formule et valider le calcul par CTRL + Entrée,
• Le reproduire sur l'ensemble de la colonne en double cliquant sur la poignée de la cellule,

Pour reproduire le calcul dans la colonne Age moyen des femmes, nous aurions pu figer les références dans le calcul. Ainsi, il aurait suffi de modifier uniquement le premier critère 'H' en 'F'. Mais nous pouvons aussi facilement répliquer ce calcul, sans déplacer les références, notamment celui du critère sur le code lésion. Pour ce faire :

• Sélectionner la première cellule du calcul soit B61,
• Puis, sélectionner l'intégralité de la formule depuis la barre de formule, au-dessus de la feuille,
• La copier par le raccourci CTRL + C,
• Enfoncer la touche Entrée pour valider la formule sans l'endommager,
• Sélectionner la première cellule de calcul pour les femmes, soit C61,
• Cliquer dans sa barre de formule pour y placer le point d'insertion,
• Coller la formule par le raccourci CTRL + V,
• Modifier le paramètre 'H' du premier critère par 'F',
• Valider le calcul par CTRL + Entrée et double cliquer sur la poignée de la cellule pour le reproduire,

Vous remarquez qu'une erreur est générée pour le dernier code gravité. Une moyenne est une division de la somme des éléments par le nombre de ces éléments. Si aucun ne correspond au critère de la fonction Moyenne.Si.Ens, la fonction divise par Zéro, ce qui est impossible, d'où le message d'erreur : #Div/0. Pour pallier le problème, il suffit de tester dans une fonction Si, si le calcul renvoie une erreur, auquel cas, il ne doit pas être réalisé. Dans le cas contraire, il est effectué, si bien que la formule reste active dans le cas où les critères se déclenchent. La fonction booléenne EstErreur permet de savoir (Vrai ou Faux) si le résultat du calcul retourne une erreur. Vous pouvez, si vous le souhaitez modifier le calcul de la façon suivante, afin de gérer les exceptions : =SI( ESTERREUR( MOYENNE.SI.ENS( 'ListeAT'!C:C; 'Liste AT'!D:D; 'F'; 'Liste AT'!K:K; A61)); ''; MOYENNE.SI.ENS('Liste AT'!C:C; 'Liste AT'!D:D ;'F'; 'Liste AT'!K:K; A61))


Nous souhaitons maintenant représenter graphiquement les accidents selon leur gravité et l'âge, tout en comparant la répartition d'un sexe à l'autre. Nous choisissons la représentation sous forme de barres. Si nous sélectionnons l'ensemble du tableau pour en construire un graphique, nous n'obtiendrons aucun résultat exploitable. Nous allons donc décomposer la procédure.

• Dans un premier temps, sélectionner les deux premières colonnes du tableau, soit A60:B65,
• Dans la zone Graphiques du ruban Insérer, cliquer sur la flèche des histogrammes,
• Dans la section Barres 2D de la liste, choisir le premier, Barres groupées,

Le graphique est posé sur la feuille. Il propose la répartition des âges sur l'axe des abscisses et celui des codes gravité sur l'axe des ordonnées. Les données ne sont pas encore correctement interprétées puisque, les codes gravité sont représentés au même titre que la répartition des âges pour les hommes. Nous allons réorganiser et remodeler cette représentation graphique en redéfinissant certaines des données.

• Cliquer sur le graphique pour le sélectionner,
• Dans la section Données du ruban Création, cliquer sur le bouton Sélectionner les données,
• Dans la liste des Entrées de légende de la boîte de dialogue qui suit, décocher la case GRAVITE,
• Cliquer sur le bouton Ajouter de cette même zone, pour intégrer la représentation des femmes,
• Dans la nouvelle boîte de dialogue, cliquer dans la zone Nom de la série pour l'activer,
• Sélectionner le titre de la colonne pour les femmes, soit la cellule C60,
• Cliquer alors dans la zone du dessous Valeurs de la série,
• Supprimer l'information {1} tout en conservant le symbole =,
• Puis, sélectionner les données numériques de la colonne, soit C61:C65,
• Cliquer alors sur le bouton Modifier de la zone Etiquettes de l'axe horizontal,
• Puis, sélectionner tous les codes gravité du tableau, soit A61:A65,
• Cliquer deux fois sur Ok pour valider les deux boîtes de dialogue,

Nous venons de redéfinir l'axe des ordonnées du graphique pour la représentation des arrêts selon le code gravité. De même, nous avons inséré la plage de données concernant la répartition des femmes selon l'âge. Désormais, nous obtenons un graphique qui permet de comparer très clairement ces valeurs.