Editions ENI Livres | Vidéos | e-Formations
Gestion des employés : exploitation de données brutes
Formules Excel avancées : description de l’exemple
Présentation de l’exemple
Ouvrez le classeur Enoncé_2-ABC.xlsx.
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 :
-
Technique
-
Fonctionnelle
-
Business
-
Fonctions transverses
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 :
-
La première feuille Employé contient les informations des employés de l’année N.
|
Colonne |
Champ |
Description |
|
A |
Prénom |
Prénom de l’employé. |
|
B |
Nom |
Nom de l’employé. |
|
C |
Sexe |
Sexe de l’employé : H pour homme, F pour femme. |
|
D |
Date de naissance |
Date de naissance au format JJ/MM/AAAA.... |
Formules Excel avancées : notions de cours
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.
Pour accéder à cette fonctionnalité, sélectionnez une plage de cellules. Dans l’onglet Données, le bouton Validation de données est accessible.
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 :
-
Options ;
-
Message de saisie ;
-
Alerte d’erreur.
-
L’onglet Options permet de définir le format et/ou la valeur attendu(e) dans la cellule. Cela se présente avec trois niveaux : le format attendu, l’opérateur et la valeur attendue. Par exemple, vous pouvez définir un format nombre entier compris entre 100 et 200.
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 :
-
Saisie d’une liste de valeurs successives séparées par un point-virgule : Valeur1 ;Valeur2 ; … ; ValeurN.
-
Saisie d’une référence de cellules =A1:A4 qui contient les valeurs.
-
Saisie d’une référence nommée=MaReference qui contient les valeurs.
Au niveau du champ Source, il n’est pas possible de saisir une référence de cellules dans une autre feuille que celle en cours, toutefois il est possible de contourner cette limitation avec une référence nommée.
-
L’onglet Message de saisiecorrespond à une petite fenêtre qui est associée à la cellule. Celle-ci comporte un titre et un message. Elle peut être déclenchée à la sélection de la cellule ou visible en permanence grâce à la case à cocher Quand la cellule...
Formules Excel avancées : réalisation de l’exemple
Cet exemple va nous permettre de synthétiser les informations contenues dans la feuille de calcul Employé. Nous allons utiliser les informations présentes dans les feuilles EmployéN-1 et Filières pour agrémenter les données présentes dans la feuille Employé.
Ouvrez le fichier Enoncé_2-ABC.xlsx qui contient les feuilles Employé, EmployéN-1 et Filières. Les principales actions se déroulent sur la feuille EmployéN-1.
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 :
-
Employé : il est possible d’utiliser deux fichiers. En effet, le fichier intitulé Annexe_2-ABC_Employe_1000.csv correspond aux données pour une société de 1000 salariés ou alors celui intitulé Annexe_2-ABC_Employe_3000.csv celui de l’exemple principal de ce cours avec 3000 salariés et dont voici les 7 premières lignes pour information :
-
EmployéN-1 : il est aussi possible d’utiliser deux fichiers. En effet, respectivement, vous avez le choix entre le premier fichier intitulé cette fois-ci Annexe_2-ABC_EmployeN1_1000.csvet le second intitulé Annexe_2-ABC_EmployeN1_3000.csv et dont voici d’ailleurs les 7 premières lignes :
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...
Indicateurs clés et partages : description de l’exemple
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.
Si vous préférez utiliser des fichiers dynamiques, c’est-à-dire comprenant des plages nommées variables, vous pouvez utiliser le fichier Enoncé_2-DEF_3000.xlsx pour la version avec 3000 salariés (c’est l’ équivalent du fichier Corrigé_2-ABC_3000.xlsx) ou le fichier Enoncé_2-DEF_1000.xlsx pour la version avec 1000 salariés (c’est l’équivalent du fichier Corrigé_2-ABC_1000.xlsx).
Fonctionnalités
Dans un premier temps, nous allons calculer les statistiques des salaires :
-
Calculer le rang d’un salaire par rapport à l’ensemble des salaires.
-
Calculer le premier et le neuvième décile...
Indicateurs clés et partages : notions de cours
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 :
Tapez =RECH.
La liste des fonctions apparaît :
Avec les touches du clavier Flèche en haut et Flèche en bas, positionnez-vous sur la fonction souhaitée, puis avec la touche Tab, validez votre sélection.
La fonction est affichée avec ses arguments.
Dans l’info-bulle, cliquez sur le nom de la fonction pour afficher l’aide en ligne et notamment les arguments associés.
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...
Indicateurs clés et partages : réalisation de l’exemple
Tout d’abord, ouvrez le fichier que vous avez choisi :
-
Soit Enoncé_2-DEF.xlsx pour la version plages nommées constantes.
-
soit Enoncé_2-DEF_3000.xlsx ou Enoncé_2-DEF_1000.xlsx pour les fichiers dynamiques.
Les fichiers Enoncé_2-DEF_3000.xlsx ou Enoncé_2-DEF_1000.xlsx sont identiques mais possèdent des plages de données dynamiques.
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é.
Dans la feuille Employé pour Enoncé_2-DEF.xlsx ou alors, la feuille BaseN pour Enoncé_2-DEF_3000.xlsx ou Enoncé_2-DEF_1000.xlsx, allez dans l’onglet Insertion et cliquez sur le bouton Tableau.
La fenêtre Créer un tableau apparaît.
Sélectionnez la plage correspondant à l’ensemble du tableau, cochez la case Mon tableau comporte des en-têtes.
Validez par OK.
L’onglet Création de tableau apparaît.
Dans le groupe Propriétés, modifiez le nom du tableau pour lui donner celui de TableauEmployé.
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é.
Pour mettre à jour les données de la feuille BaseN des fichiers dynamiques, mainte-nant qu’un tableau y est inséré, TableauEmployé pour mémoire, il va falloir :- Sélectionner toutes les lignes à partir de la ligne 2, et non de la première ligne, pour ne pas supprimer TableauEmployé.- Supprimer toutes les lignes sélectionnées, par clic droit puis Supprimer.Ensuite reportez-vous à la procédure d’importation de données via un fichier CSV décrite précédemment dans la section Données issues d’un fichier CSV pour importer les nouvelles données depuis le fichier CSV.
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...
