Gestion des employés : exploitation de données brutes

Présentation de l’exemple

Cet exemple contient les données des employés d’une société informatique sur les années N et N-1. Cette liste se présente comme un tableau de données contenant la liste de tous les employés avec leurs informations. Il y a 3000 employés dans la société lors de l’année N. Ces 3000 employés ne sont pas forcément les mêmes que ceux de l’année N-1 puisqu’entre les deux années, certains sont partis, d’autres sont arrivés.

La société aimerait avoir plus de visibilité sur sa politique salariale et mieux connaître ses employés. L’objectif est donc d’avoir une vue complète et synthétique de la situation des employés.

Cette société informatique contient les quatre filières suivantes :

Chaque employé appartient à une filière. Ils possèdent également un grade (allant du grade G1, le plus bas, au grade G12, le plus haut). La combinaison d’un grade et d’une filière définit un poste. Par exemple, un employé qui a un grade G5 dans la filière fonctionnelle a le poste de « Consultant fonctionnel junior ».

Les employés sont identifiés uniquement grâce à la combinaison des valeurs Nom, Prénom, Date de naissance pour laquelle il n’y a pas de doublons.

L’objectif de cet exemple va être de combiner les différentes données présentes dans le classeur afin d’obtenir des informations plus pertinentes sur les employés et des représentations visuelles des données.

Présentation du classeur

Le classeur de l’exemple se décompose en trois feuilles :

Liste déroulante dans une cellule - Validation de données

Une liste déroulante dans une cellule permet de sélectionner une valeur pour la cellule parmi une liste de valeurs proposées. Cette possibilité s’intègre dans la fonctionnalité de Validation de données.

La validation de données signifie que les données sont contraintes à une valeur ou à un format spécifique, il s’agit d’une limitation et non d’une aide à l’utilisateur.

Dans la boîte à outils Validation de données, trois onglets sont disponibles :

Vous pouvez définir la longueur du texte. Par exemple, vous pouvez limiter la longueur du texte à 10 caractères :

La liste déroulante peut être rédigée de trois manières différentes :

Données issues d’un fichier CSV

Pour simplifier les échanges de données entre plusieurs systèmes d’information, il est souvent utile d’extraire des données par l’intermédiaire de fichiers au format dit « CSV » ou « Comma-separated values ».

Ces fichiers sont dans un format texte dit « ouvert » dont la particularité est de représenter au format texte les données d’un tableau.

Chaque ligne de ce fichier correspond à une ligne du tableau.

Les colonnes dans ce fichier sont séparées par des séparateurs qui peuvent être, généralement, soit des virgules, soit des points-virgules (version française).

Pour notre exemple, nous avons donc des fichiers CSV avec, comme séparateur, des points-virgules.

Aussi, pour la feuille de calcul nommée :

Ces fichiers, avec l’extension CSV, sont très utilisés car ils restent compatibles avec la plupart des logiciels de gestion de type ERP...

Présentation de l’exemple

L’objectif de cet exemple est de repartir du tableau consolidé créé dans la première partie pour en faire ressortir les indicateurs clés. Il est donc intéressant d’aborder les différentes fonctionnalités permettant de mettre en valeur les chiffres clés du tableau contenu sur la feuille Employé.

Présentation du classeur

Le fichier Enoncé_2-DEF.xlsx est équivalent au fichier Corrigé_2-ABC.xlsx. Cet exemple intervient dans la continuité de la première partie, il contient donc des plages nommées constantes.

Fonctionnalités

Dans un premier temps, nous allons calculer les statistiques des salaires :

Fonctions Excel

Par défaut, Excel propose de nombreuses fonctions statistiques. L’objectif n’est pas de détailler toutes les fonctions statistiques mais d’apprendre à les utiliser.

Où trouver les fonctions ?

Les fonctions se situent dans l’onglet Formules et sont triées par catégories : Récentes, Financier, Logique, Texte, Date/Heure…

Il est possible de rechercher une fonction via le bouton Insérer une fonction, toutefois la recherche de fonction n’aboutit que trop rarement au résultat souhaité. Par conséquent pour insérer une fonction, il est recommandé d’effectuer une recherche par thème.

Comment les comprendre ?

Prenons l’exemple d’une recherche verticale :

La liste des fonctions apparaît :

La fonction est affichée avec ses arguments.

Les arguments entre crochets ne sont pas obligatoires, comme ici [valeur_proche].

Comment les analyser ?

Dans l’onglet Formules, le groupe Vérification des formulespermet d’analyser les formules et notamment de repérer les problèmes éventuels :

Repérer les antécédents/Repérer les dépendants permet d’afficher des flèches de repérage symbolisant les liens entre les cellules concernées par une formule. Supprimer les flèches permet d’effacer ces flèches de repérage entre les cellules.

Afficher les formules permet d’afficher la formule plutôt que son résultat dans la feuille Excel. Au lieu d’avoir le résultat de la somme, la formule est affichée=SOMME(A1:A4).

L’évaluation de formule via le bouton Évaluer la formule permet de valider les différentes étapes d’une formule.

Prenez par exemple une formule qui teste...

Mise en place du tableau

Dans un premier temps, nous allons créer un tableau Excel pour faciliter la manipulation des données contenues dans la feuille Employé.

La fenêtre Créer un tableau apparaît.

La plage de données $A$1:$N$3001, ou $A$1:$I$3001 pour les fichiers dynamiques, est désormais un tableau Excel nommé TableauEmployé. 

Formules statistiques

À partir des formules statistiques, nous allons calculer trois indicateurs portant sur les salaires en cours.

Connaître le rang du salaire

Nous allons afficher en colonne O l’information du rang du salaire. Cela correspond...