Name: Analyse et reporting RH Excel au service des Ressources Humaines
Rating: 4.5 (2 reviews)

Introduction

Au travers des chapitres précédents, nous avons étudié comment appréhender les données RH et comment les présenter. Nous allons dans ce chapitre voir comment créer des rapports et des outils de pilotage RH. Le classeur nommé Report_Absentéisme_Chap7 est disponible en téléchargement. Ce dernier est composé de quatre feuilles :

Report_ABS
BDD_ABS
BDD_Effectif
Param

Ce document se base sur les données d’absentéisme. Élément récurrent des études RH, nous allons utiliser cette thématique pour créer un reporting/outil de pilotage RH sous Excel. Cependant, les méthodes qui vont être présentées peuvent être appliquées à toutes les thématiques. Concernant l’absentéisme, pour rappel, il se calcule de différente manière. Ici, nous utiliserons la formule suivante :

Nbr de jours d’absence (calendaire) / Effectif moyen de la période x 365

Avant toute création de reportings et d’outils de pilotage, il est important de s’assurer que cela répond à un besoin. De plus, la question centrale à se poser avant de commencer la création d’un tableau de bord RH est : « Qu’est-ce que nous souhaitons démontrer ? » Une fois la réponse à...

Création des listes de contrôles de formulaire

Les listes de contrôles de formulaire permettent de sélectionner une population voulue. Elles permettent de rendre dynamique votre rapport. Ici, pour notre étude de l’absentéisme nous allons en créer cinq : Type Absence, Sexe, Age, Statut et Métier.

Ces listes déroulantes sont les variables explicatives d’une analyse. Une fois créées, n’hésitez pas à jouer avec pour découvrir des phénomènes et procéder à de nouvelles analyses.

Pour les créer, agissez comme suit :

Sélectionnez et copiez la plage de cellules N2:N1025 de la feuille BDD_Abs contenant les libellés des motifs d’absence.

Collez cette sélection en cellule A3 de la feuille Param.

C’est sur cette feuille que seront établis les différents paramétrages du rapport.

Sélectionnez toutes les données présentes en colonne A soit de A2 à A1026.

Dans l’onglet Données, cliquez sur le bouton Supprimer les doublons du groupe Outils de données.

Excel affiche un message d’alerte nommé : Supprimer les avertissements en double.

Cochez Continuer avec la sélection en cours puis cliquez sur Supprimer les doublons.

La fenêtre Supprimer les doublons apparaît. Cliquez de nouveau sur OK.

Pour plus d’informations concernant cette fonctionnalité, reportez-vous au chapitre Retraiter les données, calculer l’âge et l’ancienneté et utilisation des bases de données.

Excel vous indique qu’il ne reste plus que trois valeurs uniques.

À la fin de la liste restante, en cellule A6 saisissez Tout.

Réalisez la même manipulation pour la colonne S de l’onglet BDD_Effectif que vous devez coller dans...

Noms définis

La création des noms définis (ou zones nommées) peut être réalisée par sélection manuelle ou automatisée. Dans la création d’un reporting/outil de pilotage, l’utilisation des deux méthodes peut être nécessaire.

Leur création et leur utilisation permettent de faciliter la lecture des formules, mais également de faciliter leur mise à jour et ainsi de pérenniser leur utilisation. Chaque nom est en réalité une plage de cellules.

Création de noms définis simples

Pour continuer à créer ce reporting, nous allons créer des noms définis sur les choix de l’utilisateur. Nous créerons un nom pour les codes et un nom pour les libellés. Pour cela, procédez comme indiqué :

Sélectionnez la cellule I3 de la feuille Param.

Cette cellule contient en code le choix de l’utilisateur à propos du type d’absentéisme étudié.

Cliquez dans la zone Nom située à gauche de la barre de formule.

Saisissez le nom suivant : Choix_Type_Absence_Code.

Un nom peut contenir 255 caractères au maximum. Les espaces ne sont pas autorisés. Le premier caractère doit être une lettre, un caractère de soulignement (_) ou une barre oblique inversée (\). Les autres caractères peuvent être des lettres, des chiffres, des points et des caractères de soulignement ; les noms ne peuvent pas être identiques à une référence de cellules ; les noms peuvent contenir des majuscules et des minuscules (Excel ne les distingue pas).

Faites Entrée.

Sélectionnez la cellule I4.

Cette cellule contient le libellé du choix de l’utilisateur à propos du type d’absentéisme étudié.

Cliquez dans la zone Nom située à gauche de la barre de formule.

Saisissez le nom suivant : Choix_Type_Absence_Lib.

Vous disposez désormais de deux noms définis pour les choix de Type absence. Un pour le code et un pour le libellé.

Répétez ces manipulations pour les listes Sexe, Age, Statut et Métiers en respectant le même formalisme de noms. Par exemple, pour la liste Sexe, les noms seront :...

Création des codes de sélection

Les codes de sélection permettent de sélectionner les lignes correspondant aux différents choix de l’utilisateur. Les listes de contrôles de formulaires permettent de générer un code. Afin de disposer de l’ensemble des choix en une seule donnée, procédez comme suit :

Sélectionnez la cellule N3 de la feuille Param.

Dans cette cellule, saisissez la formule suivante : =CONCAT(I3:M3)

La formule CONCAT est la nouvelle version de la formule CONCATENER sur Excel 2019. Via cette nouvelle formule, la sélection de plages de cellules est désormais possible contrairement à CONCATENER où la sélection devait s’effectuer cellule par cellule.

Vous obtenez alors le code de sélection voulu par l’utilisateur.

Créez le nom défini Choix_utilisateur_global pour cette cellule (utilisez la méthode manuelle expliquée précédemment).

Désormais, pour chaque ligne des bases de données BDD_Effectif et BDD_Absence, il est nécessaire de créer un code afin de savoir si elles correspondent au code de sélection de l’utilisateur. Pour cela, il est nécessaire de tester chaque critère (type d’absence, sexe, statut, âge et métier). La formule à utiliser est une fonction SI, car différentes...

Calcul des indicateurs et des graphiques

Les indicateurs

Ce reporting/outil de pilotage créé présente les trois indicateurs suivants :

Taux d’absentéisme
Nombre d’arrêts
Durée moyenne des arrêts

Taux d’absentéisme

Pour rappel, pour calculer le taux d’absentéisme 2018, nous appliquerons la formule suivante :

Nbr de jours d’absence (calendaire) / Effectif moyen de la période x 365

Le calcul effectué n’étant pas en jours ouvrés, la différence plus 1 entre les dates permet de déterminer le nombre de jours.

Pour établir la formule permettant de calculer le nombre de jours d’absence, procédez comme suit :

Sélectionnez la cellule O2 de la feuille BDD_Abs.

Dans cette colonne, sur laquelle a été créée précédemment la variable Abs_Nb_jours, doit apparaître le nombre de jours d’absence entre les deux dates indiquées en colonne K (Date de début d’absence) et L (Date de fin d’absence). Les arrêts maladie pouvant être longs, certaines dates de début sont en 2016. De plus, certains arrêts peuvent démarrer courant décembre et se terminer courant janvier. Il est donc nécessaire de borner les arrêts. Pour cela, la fonction SI est nécessaire (reportez-vous au chapitre Calculer et traiter l’information RH (effectif, mouvement, masse salariale…) pour connaître sa composition et son fonctionnement).

Ainsi, saisissez la formule suivante : =SI(L2>DATEVAL("31/12/2018");DATEVAL("31/12/2018");L2)-SI(K2<DATEVAL("01/01/2018");DATEVAL("01/01/2018");K2)+1

Cette formule se lit de la manière suivante :

Calcule la différence plus 1 entre :

si la date de fin d’absence est supérieure au 31 décembre 2018
	alors au 31 décembre 2018 sinon à la date de fin d’absence
et avec si la date de début d’absence est inférieure au 1^er janvier 2018
	alors au 1^er janvier 2018 sinon à la date de début d’absence.

Il est possible d’utiliser la fonction JOURS qui calcule le nombre de jours entre deux dates. Cependant, dans notre exemple, l’utilisation de la formule SI reste indispensable, la fonction Jours...

Gestion des messages d’erreur

Sur la feuille Report_ABS, sélectionnez, via les listes de contrôles de formulaire, créées précédemment, les critères suivants :

Maternité pour l’argument Type absentéisme
M pour l’argument Sexe
20 ans - 30 ans pour l’argument Age
C pour l’argument Statut
Réassurance pour l’argument Métier

Vous obtenez le visuel suivant.

Des #DIV/0 ! sont apparus et les graphiques n’affichent aucune valeur. Lors de la création d’un outil d’analyse ou d’un reporting sous Excel, il est nécessaire d’anticiper les cas où la sélection, le critère de calcul, ne donnent aucun résultat afin d’éviter les messages d’erreur qui peuvent décrédibiliser votre travail.

Les messages d’erreur #N/A, #DIV/0, #REF ou encore #VALEUR font partie de ces messages qui parfois inquiètent et peuvent ternir un tableau de bord. Pourtant, ils peuvent être source d’information dans la création de vos formules et de vos tableaux. Par exemple, au cours du chapitre Retraiter les données, calculer l’âge et l’ancienneté et utiliser les bases de données dans la recherche de doublons, les formules EQUIV et RECHERCHEV faisaient apparaître un #N/A pour les valeurs uniques. Ainsi, ce message permettait d’identifier les doublons.

Chaque message d’erreur à sa propre signification, les plus courants sont :

#DIV/0! : Erreur de division par zéro. Ce message vous indique que le dénominateur de votre division est égal à 0, ce qui rend impossible son calcul.
#VALEUR! : Erreur de valeur. Cette erreur survient lorsqu’un type d’argument inapproprié est utilisé, comme par exemple :

Attribution d’une plage de cellules à...

Mise en forme du reporting

Mise en page

Le visuel créé est pour le moment sur deux pages. Pour y remédier, agissez comme suit :

Dans l’onglet Affichage, cliquez sur Avec saut de page dans le groupe Modes d’affichage.

Ce bouton est également accessible en bas de votre écran :

Vous obtenez le visuel suivant.

Vous disposez d’une vision d’ensemble du reporting créé. Il est possible d’identifier les différentes pages et leur contenu.

Ici, la deuxième page présente est superflue, car elle ne contient aucune information. Pour la supprimer, procédez comme suit :

Sélectionnez et faites glisser la ligne bleue à droite de la Page 2 vers les pointillés bleus de la page une.

Vous obtenez le résultat suivant.

Le reporting créé n’est désormais plus que sur une page.

Grouper/dissocier des lignes

Parfois, il peut être intéressant de masquer des lignes de détail afin de ne pas « noyer » l’utilisateur avec un nombre important d’indicateurs et lui offrir la possibilité de zoomer sur les éléments qui l’intéressent.

La manipulation, bien connue de tous, qui consiste à masquer des lignes fonctionne, mais elle rend invisibles les informations concernées. Si l’utilisateur ne connait pas leur présence...

Protéger un reporting/outil de pilotage RH

Excel propose trois types de protection qui permettent de :

Protéger la feuille
Protéger la structure du classeur
Protéger le classeur

Protéger la feuille

Pour protéger une feuille Excel des éventuelles modifications, réalisez la manipulation suivante :

Sélectionnez la feuille Report_ABS.

Dans l’onglet Révision, cliquez sur le bouton Protéger la feuille présent dans le groupe Modifications.

La boîte de dialogue Protéger la feuille apparaît.

Cette boîte de dialogue permet d’appliquer un mot de passe nécessaire pour réaliser des modifications sur la feuille concernée. De plus, elle permet de sélectionner les actions réalisables par l’utilisateur. Par défaut, Excel sélectionne toujours les deux premières options : Sélectionner les cellules verrouillées et Sélectionner les cellules déverrouillées.

Une fois la sélection des actions possibles effectuées, et les étapes de création de mot de passe réalisées, cliquez sur OK pour appliquer la protection.

Il est également possible de créer des cas particuliers. C’est-à-dire des plages de cellules ouvertes à la modification et qui ne seront pas concernées par la protection....