L’affichage d’indicateurs de performance clés (KPI) est une utilisation courante des tableaux de bord. Les KPI sont des mesures qu’une organisation, comme une entreprise ou un service, a identifiées comme importantes pour mesurer ses performances. Il n’y a pas d’ensemble standard de KPI, mais il y a tellement d’exemples disponibles sur Internet que vous ne manquerez pas d’idées si vous êtes chargé de les développer.

Les KPI peuvent être financiers, opérationnels ou les deux. Ils peuvent également aller d’un niveau élevé qui concerne l’ensemble de l’entreprise à un niveau bas qui peut concerner un sous-ensemble d’un département. Les KPI peuvent changer et changent au fur et à mesure que l’entreprise évolue.

Étude de cas: indicateurs de performance clés des ressources humaines

Le service des ressources humaines (RH) a développé un ensemble de mesures relatives à l’embauche et à la fidélisation des employés qui, selon lui, sont des indicateurs clés de sa capacité à atteindre ses objectifs commerciaux. Ils vous demandent de créer un tableau de bord pour afficher les Données.

Planification et mise en page

HR vous présente la liste suivante de KPI:

• Coûts de recrutement par année: le montant total dépensé pour le recrutement
• Coût de recrutement par candidat: le montant moyen dépensé pour le recrutement par candidat et par candidat retenu
• Diversité des embauches: le nombre d’embauches réparties entre le sexe et la race
• Taux d’abandon de 90 jours: le nombre de postes vacants dans les 90 jours suivant l’embauche
• Raisons des départs volontaires: le nombre de départs volontaires dans un ensemble prédéfini de catégories
• Temps moyen pour pourvoir les postes vacants: combien de temps, en jours, il faut pour pourvoir un poste vacant

Ils veulent voir le coût de recrutement, le taux d’abandon et le temps nécessaire pour pourvoir les postes vacants pour chacune des cinq dernières années. Pour les embauches de diversité et les départs volontaires, ils aimeraient voir les Données de l’année la plus récente. Les Données seront compilées et présentées une fois par an.

Vous créez une mise en page approximative des éléments visuels et, après une discussion plus approfondie sur l’ordre dans lequel ils doivent apparaître, choisissez la mise en page illustrée à la figure 1.

Figure 1 : Une présentation approximative du tableau de bord RH

Collecte des Données

Le service RH suit les dépenses de recrutement par année et par type de dépense. Ils répartissent les dépenses entre les salaires des recruteurs internes, les honoraires des recruteurs externes et d’autres dépenses telles que le coût de la participation à des salons de l’emploi. Bien qu’ils n’aient demandé aucune visualisation par type de dépense, vous leur demandez de fournir les Données sous une forme aussi brute que possible. S’ils décident d’ajouter un indicateur de performance clé qui utilise ces informations plus tard, vous disposerez déjà des Données historiques. La figure 2 montre les Données qu’ils fournissent.

Figure 2: Coûts de recrutement Données fournies par les RH

Ensuite, ils vous fournissent une liste d’employés modifiée. Ils ont supprimé certaines informations confidentielles ou non pertinentes. La liste comprend le numéro d’identification de l’employé et sa date de début. En cas de séparation, la date de fin et le motif sont enregistrés. Une partie des Données est illustrée à la figure 3

Figure 3: Une liste d’employés avec les raisons de la séparation

Chaque fois qu’une nouvelle embauche est effectuée, le service des ressources humaines enregistre la date à laquelle le poste est devenu disponible, la date à laquelle il a été pourvu, le nombre de candidats, ainsi que le sexe et la race du candidat retenu. La figure 4 montre une partie de ces Données.

Figure 4: Le journal des nouvelles recrues

Construire les éléments visuels

Chaque élément visuel est traité dans l’une des sous-sections suivantes. Créez une feuille de calcul nommée Dashboard pour contenir ces éléments.

Coûts de recrutement par année

Pour les coûts de recrutement par élément d’année, vous décidez de créer un histogramme simple. Commencez par convertir les Données en tableau en sélectionnant les Données et en choisissant Tableau dans le menu Insertion du ruban. Dans l’onglet Création d’outils de tableau du ruban, remplacez le nom de la table par tblCout. Ajoutez une colonne nommée Total au tableau et entrez la formule suivante: = SOMME (tblCout [@ [Salaire de recrut]: [Autre]])

Pour créer le histogramme, sélectionnez le tableau et choisissez Graphiques recommandés dans l’onglet Insertion du ruban. Le graphique initial, affiché à côté du tableau de la figure 5, comprend des séries dont vous ne voulez pas, mais il est plus facile de les supprimer que d’essayer de les exclure du graphique initial.

Figure 5 : Le graphique à colonnes et les Données des coûts de recrutement initiaux

Cliquez avec le bouton droit sur le graphique et choisissez Sélectionner les Données. Dans la boîte de dialogue Sélectionner la source de Données, supprimez toutes les séries à l’exception de la série Total. Créez une nouvelle feuille de calcul appelée Tableau de bord dans le classeur. Coupez le graphique (Ctrl + X) et collez-le sur la feuille Tableau de bord (Ctrl + V).

Pour terminer la mise en forme du graphique, procédez comme suit:

1. Supprimez la légende.
2. Changez le titre en Coûts de recrutement (en milliers).
3. Cliquez avec le bouton droit sur l’axe vertical et choisissez Formater l’axe. Changer le format numérique à 0 ;; .
4. Choisissez Mise en page ➪ Thèmes dans le ruban et choisissez le thème Ion.

Le graphique final est illustré à la figure 6. Ne vous inquiétez pas du redimensionnement ou du positionnement du graphique tant que tous les éléments ne sont pas sur la feuille de calcul du tableau de bord.

Figure 6: Le graphique à colonnes des coûts de recrutement final

Coût de recrutement par candidat

Pour cet élément visuel, vous devrez ajouter plus de colonnes au tableau des coûts de recrutement. Commencez par convertir les Données de vacance en une table et nommez-la tblVacance. Ajoutez quatre nouvelles colonnes au tableau des coûts de recrutement. Utilisez les informations du tableau 1.

Tableau 1: Nouvelles colonnes de coût de recrutement

Sélectionnez le tableau et choisissez Insertion ➪ Insérer un histogramme ou un diagramme à barres ➪ Plus de diagrammes en colonnes dans le ruban. Choisissez le graphique à colonnes groupées avec l’année dans l’axe des catégories, comme illustré à la figure 7.

Cliquez avec le bouton droit sur le graphique, choisissez Sélectionner les Données et supprimez toutes les séries à l’exception du coût par candidat et du coût par réussite. La figure 8 montre le graphique à ce stade.

Modifiez la série Coût par candidat en graphique linéaire et déplacez-la sur son propre axe. Cliquez avec le bouton droit sur l’une des séries et choisissez Modifier le type de graphique de la série. Dans la boîte de dialogue Modifier le type de graphique, modifiez le type de graphique Coût par candidat en Ligne et cochez la case Axe secondaire. La figure 9 montre la boîte de dialogue Modifier le type de graphique.

Remplacez le titre du graphique par Coût de recrutement par candidat. Coupez et collez le graphique dans la feuille de calcul du tableau de bord. Modifiez la couleur de remplissage de la série de colonnes pour qu’elle corresponde à l’autre graphique des coûts de recrutement et la couleur de remplissage de la série de lignes en quelque chose de complémentaire. Les deux éléments terminés de la feuille de calcul du tableau de bord sont illustrés à la figure 10.

Embauches de diversité

Vous décidez de diviser l’élément d’embauche de diversité en deux graphiques en anneau. Les Données de poste vacant ne sont pas sous une forme prenant en charge un graphique en anneau, vous devrez donc créer des graphiques croisés dynamiques pour manipuler les Données en premier. Pour créer le premier graphique en anneau, procédez comme suit:

1. Ajoutez une nouvelle colonne au tableau des postes vacants nommée AnnéeEnCours et entrez la =ANNEE([@Plein])=MAX(ANNEE([Plein])).

Figure 7: Choisissez le graphique à colonnes groupées avec l’année dans l’axe des catégories.

Figure 8: Le graphique avec seulement deux séries

Figure 9: Remplacez la série Coût par candidat par un graphique linéaire.

Figure 10: Les deux graphiques des coûts de recrutement

2. Sélectionnez le tableau des postes vacants et choisissez Insérer ➪ Graphique croisé dynamique ➪ Graphique croisé dynamique dans le ruban.
3. Dans la boîte de dialogue Créer un graphique croisé dynamique, choisissez Feuille de calcul existante et sélectionnez une cellule dans la feuille de calcul Tableau de bord. Cliquez sur OK.
4. Faites glisser le champ CurrentYear vers la zone Filtres, le champ Genre vers la zone Axe (Catégories) et le champ Travail vers la zone Valeurs. La figure 11 montre le volet Office Champs de tableau croisé dynamique.

Figure 11: Le volet Office Champs de tableau croisé dynamique pour le graphique Sexe

5. Sur le graphique croisé dynamique, filtrez le champ CurrentYear sur VRAI pour afficher uniquement les Données de l’année en cours.
6. Choisissez Outils de graphique croisé dynamique Analyser ➪ Boutons de champ ➪ Tout masquer.
7. Supprimez la légende.
8. Ajoutez des étiquettes de Données, choisissez Pourcentage et formatez la police pour qu’elle se démarque de la série.
9. Modifiez le titre du graphique en Sexe et faites-le glisser vers le coin supérieur droit de l’anneau.

Suivez les mêmes étapes pour créer un graphique en anneau de course. Au lieu du champ Sexe, faites glisser le champ Race vers la zone Axe (Catégories). Sélectionnez chaque point de Données et formatez la couleur de remplissage pour qu’elle soit similaire aux couleurs de remplissage du graphique Genre. La figure 12 montre les deux graphiques en anneau.

Figure 12 : Diagrammes en anneau pour les KPI d’embauche de diversité

Taux d’abandon de 90 jours

Pour identifier les postes vacants dans les 90 jours, convertissez les Données des employés dans une table nommée tblEmployee. Ajoutez une nouvelle colonne à la table nommée 90Days et entrez la formule suivante:

=NON(OU(ESTVIDE([@JourFin]);[@JourFin]-[@JourDépart]>90))

Cette formule renverra VRAI si les valeurs JourDépart et JourFin sont dans les 90 jours et FAUX si elles sont en dehors de 90 jours ou si la colonne JourFin est vide.

Créez un graphique croisé dynamique à partir du tableau et placez-le dans la feuille de calcul du tableau de bord.

Dans le volet Office Champs de tableau croisé dynamique, faites glisser le champ 90 jours vers la zone Filtres, le champ Date de fin vers la zone Axe (catégories) et le champ ID employé vers la zone Valeurs, comme illustré dans la figure 13.

Ensuite, modifiez l’agrégat du champ EmployéID en cliquant avec le bouton droit de la souris sur le champ dans le tableau croisé dynamique et en choisissant Résumer les valeurs par ➪ Nombre, comme illustré à la figure 14.

Figure 13: Création du graphique croisé dynamique pour le graphique du taux d’abandon de 90 jours

Figure 14: Modifiez l’agrégat pour compter le champ EmployéID.

Déroulez le bouton de champ 90Days et filtrez sur VRAI. Déroulez le bouton de champ Années et sélectionnez uniquement les cinq dernières années. Pour terminer la mise en forme du graphique, procédez comme suit:

1. Masquez les boutons de champ en sélectionnant Outils de graphique croisé dynamique ➪ Analyser ➪ Boutons de champ ➪ Tout masquer du ruban.
2. Cliquez avec le bouton droit sur le graphique et sélectionnez Modifier le type de graphique. Choisissez l’option Ligne avec marqueurs.
3. Supprimez la légende.
4. Changez le titre en Taux d’abandon de 90 jours.

Ce graphique fonctionnerait aussi bien qu’un graphique à colonnes ou à barres. Cependant, un graphique linéaire crée une distinction visuelle par rapport aux graphiques des coûts de recrutement sur le tableau de bord. Le graphique final est présenté à la figure 15.

Figure 15: Le graphique final du taux d’abandon de 90 jours

Séparation volontaire par motif Pour cet élément, vous choisissez un graphique à barres car il offre plus d’espace pour les étiquettes de catégorie. Créez un autre graphique croisé dynamique à partir de la table des employés et placez-le dans la feuille de calcul du tableau de bord.

Pour filtrer le graphique uniquement pour l’année en cours, commencez par faire glisser la date de fin vers la zone Axe (catégories). Cela créera trois champs dans cette zone: Années, Trimestres et Date de fin. Ensuite, faites glisser le champ Années de la zone Axe (Catégories) vers la zone Filtres. Enfin, faites glisser les champs Trimestres et JourFin hors de la zone Axe (Catégories) et déposez-les de nouveau dans la zone des champs. Si vous deviez faire glisser JourFin directement vers Filtres, vous n’obtiendrez pas le champ d’agrégation Années.

Terminez la construction des Données en faisant glisser le champ Raison dans la zone Axe (Catégories) et le EmployéID dans la zone Valeurs. Modifiez l’agrégat de EmployéID de somme à NB.

Filtrez le graphique croisé dynamique en sélectionnant uniquement l’année en cours à partir du bouton de champ Années. Sélectionnez maintenant le bouton de champ Raison et décochez l’entrée Involontaire. La figure 16 montre le graphique après filtrage par année et raison.

Figure 16: Le tableau des départs volontaires filtré

Cliquez avec le bouton droit sur le graphique et choisissez Modifier le type de graphique. Sélectionnez l’option Barre groupée. Masquez les boutons de champ et supprimez la légende pour nettoyer le graphique.

Ensuite, triez les raisons par nombre. Cliquez sur le filtre Étiquettes de ligne dans le tableau croisé dynamique et choisissez Plus d’options de tri. Dans la boîte de dialogue Trier, illustrée à la figure 17, choisissez Décroissant et nombre de EmployéID et cliquez sur OK.

Figure 17: triez le graphique croisé dynamique par nombre de EmployéID.

Enfin, changez le titre du graphique en Séparations volontaires. Le graphique final est présenté à la figure 18.

Figure 18: Le graphique à barres final des séparations volontaires

Temps moyen pour pourvoir les postes vacants

Pour créer l’élément de tableau de bord final, commencez par ajouter une nouvelle colonne à la table des postes vacants nommée Days. Entrez la formule = [@ Filled] – [@ Vacant] pour compter les jours d’ouverture du poste. Créez un autre graphique croisé dynamique à partir du tableau et placez-le dans la feuille de calcul du tableau de bord.

Pour créer le graphique, faites glisser le champ Rempli vers la zone Axe (catégories) et le champ Jours vers la zone Valeurs. Si le champ Jours n’apparaît pas dans la liste des champs, c’est parce que vous avez déjà un tableau croisé dynamique créé à partir de cette table et que ce tableau croisé dynamique utilise le même cache que le précédent. Cliquez avec le bouton droit sur le tableau croisé dynamique et choisissez Actualiser pour afficher le champ Jours. Modifiez l’agrégat des jours de la somme à la moyenne.

Suivez ces étapes pour terminer le formatage du graphique:

1. Masquez les boutons de champ.
2. Supprimez la légende.
3. Modifiez le type de graphique en Ligne avec marqueurs.
4. Remplacez le titre du graphique par Nombre moyen de jours pour pourvoir les postes vacants.
5. Cliquez avec le bouton droit de la souris sur l’axe vertical, choisissez Format de l’axe et remplacez le minimum par zéro. Le graphique final est illustré à la figure 19.

Figure 19: Tableau des derniers jours moyens pour pourvoir les postes vacants

Disposition du tableau de bord

Étant donné que vous divisez les tableaux d’embauche de diversité en deux graphiques en anneau, la mise en page sera différente de la mise en page approximative d’origine. Au lieu d’un graphique en haut à droite, étendez les deux graphiques en anneau sur tout le côté droit du tableau de bord.

Vous pouvez utiliser les poignées de remplissage et de taille de chaque graphique pour les déplacer et les redimensionner. Pour ce tableau de bord, cependant, vous choisissez d’utiliser Visual Basic pour Applications (VBA) pour positionner les graphiques. Commencez par nommer chaque graphique.

Sélectionnez le graphique des coûts de recrutement et entrez RecCost dans la zone Nom (à gauche de la barre de formule) pour renommer le graphique comme illustré à la figure 20. Vous verrez ce nom dans le code VBA pour faire référence au graphique.

Figure 20: Renommez un graphique avec la zone Nom.

Renommez les autres graphiques en les sélectionnant et en saisissant un nouveau nom dans la zone Nom. Déplacez et redimensionnez manuellement le tableau des coûts de recrutement à la position et à la taille souhaitées. Appuyez sur Alt + F11 pour ouvrir Visual Basic Editor (VBE). Appuyez sur Ctrl + R pour afficher l’explorateur de projet. Recherchez le projet ResourcesHumaine.xlsm, cliquez avec le bouton droit sur le projet et choisissez Insérer ➪ Module comme illustré à la figure 21.

Figure 21: Insérez un module dans le projet.

Dans le volet de code résultant, entrez le code suivant:   

Sub MoveCharts()

   With ActiveSheet

     .Shapes(“Parcand”).Top = .Shapes(“RecCost”).Top

     .Shapes(“Parcand”).Height =.Shapes(“RecCost”).Height

     .Shapes(“Parcand”).Width = .Shapes(“RecCost”).Width

     .Shapes(“Parcand”).Left = .Shapes(“RecCost”).Left _ + .Shapes(“RecCost”).Width

     .Shapes(“QuitRate”).Top = .Shapes(“RecCost”).Top _ + .Shapes(“RecCost”).Height

     .Shapes(“QuitRate”).Left = .Shapes(“RecCost”).Left

     .Shapes(“QuitRate”).Width =.Shapes(“RecCost”).Width * 2 / 3

     .Shapes(“VolSep”).Top = .Shapes(“QuitRate”).Top

     .Shapes(“VolSep”).Height =.Shapes(“QuitRate”).Height

     .Shapes(“VolSep”).Width = .Shapes(“QuitRate”).Width

     .Shapes(“VolSep”).Left = .Shapes(“QuitRate”).Left _ + .Shapes(“QuitRate”).Width

     .Shapes(“Vacance”).Top = .Shapes(“VolSep”).Top

     .Shapes(“Vacance”)Height = .Shapes(“VolSep”).Height

     .Shapes(“Vacance”).Width = .Shapes(“VolSep”).Width

     .Shapes(“Vacance”).Left = .Shapes(“VolSep”).Left _ + .Shapes(“VolSep”).Width

     .Shapes(“Genre”).Top = .Shapes(“Parcand”).Top

     .Shapes(“Genre”).Height = .Shapes(“Parcand”).Height

     .Shapes(“Genre”).Width = .Shapes(“Parcand”).Width /2

     .Shapes(“Genre”).Left = .Shapes(“Parcand”).Left _ + .Shapes(“Parcand”).Width

     .Shapes(“Race”).Top = .Shapes(“Vacance”).Top

     .Shapes(“Race”).Height = .Shapes(“Vacance”).Height

     .Shapes(“Race”).Width = .Shapes(“Genre”).Width

     .Shapes(“Race”).Left = .Shapes(“Vacance”).Left _ + .Shapes(“Vacance”).Width

    End With

End Sub

Chaque graphique du tableau de bord est un objet Shape. Ce code définit les propriétés Top, Height, Width et Left de chaque objet par rapport à un autre graphique.

Cela garantit que les graphiques s’alignent parfaitement. Fermez le VBE et revenez à Excel. Appuyez sur Alt + F8 pour afficher la boîte de dialogue Macro et exécutez la macro MoveCharts que vous venez de créer.

Si l’un des tableaux croisés dynamiques s’affiche, vous pouvez masquer les colonnes et réexécuter la macro pour repositionner les graphiques. Pour modifier la taille ou la position globale du tableau de bord, ajustez simplement manuellement le graphique en haut à gauche et réexécutez la macro. Le tableau de bord final est illustré à la figure 22.

Figure 22: Le tableau de bord final des RH

La macro sera enregistrée avec le classeur. Enregistrez le classeur en tant que classeur prenant en charge les macros (.xlsm) si ce n’est déjà le cas pour vous assurer de ne pas perdre la macro.