Blog ENI : Toute la veille numérique !
En raison d'une opération de maintenance, le site Editions ENI sera inaccessible le mardi 10 décembre, en début de journée. Nous vous invitons à anticiper vos achats. Nous nous excusons pour la gêne occasionnée
En raison d'une opération de maintenance, le site Editions ENI sera inaccessible le mardi 10 décembre, en début de journée. Nous vous invitons à anticiper vos achats. Nous nous excusons pour la gêne occasionnée

Retraiter les données, calculer l’âge et l’ancienneté et utiliser les bases de données

Introduction

Les données RH sont usuellement issues des SIRH (Système d’Information RH). Les informations qu’ils contiennent composent ce que l’on appelle un datamark. Les requêteurs de données, type Business Object (BO), permettent d’exploiter et d’extraire les informations contenues dans le SIRH. Ces extractions sont très régulièrement téléchargées sur Excel pour être par la suite exploitées (elles peuvent également être extraites en format PDF notamment s’il s’agit de reportings construits directement via votre requêteur). Une colonne de votre fichier équivaut à une information contenue dans votre SIRH ou calculée directement par votre requêteur. Le fichier BDD_Chap3 disponible en téléchargement sur le site des Éditions ENI est un exemple de base de données qu’un RH peut être amené à retraiter et travailler. Il s’agit d’une base Effectifs qui est la requête la plus classique et sans doute la plus utilisée par la fonction RH. Nous allons utiliser ce modèle pour illustrer et mieux comprendre les possibilités qu’offre Excel pour retraiter des bases de données RH.

Composition d’une requête RH

Le fichier nommé BDD_Chap3.xlsx contient une feuille nommée BDD. Elle présente les informations suivantes :

  • Date Photo

  • Code Société Juridique

  • Libellé Société juridique

  • Matricule

  • Sexe

  • Nb d’enfants

  • Libellé de type de contrat

  • Code nature de contrat

  • Taux d’activité contractuel

  • Date d’entrée Groupe

  • Date de naissance

  • Code classification

  • Organisation_Convertir

  • Organisation_Remplissage Instantané

  • Cat. Pro

  • Code de famille de métiers groupe

  • Code lieu de travail du salarié

  • Salaire

Cette base constitue la matière brute nécessaire pour réaliser des études, des analyses. Elle contient des informations au format Texte (le libellé de type de contrat par exemple), au format Standard, c’est-à-dire des nombres (le nombre d’enfants) ou encore au format Date (la date de naissance). Toutes ces différences de format apportent leur lot de retraitements et de points de vigilance notamment en cas d’utilisation de formules ou de TCD (Tableaux Croisés Dynamiques).

De plus, cette requête présente quelques manquements comme, par exemple, l’âge ou l’ancienneté. En effet, elle ne présente que la date de naissance ou d’entrée dans l’entreprise. Ainsi, nous allons répondre à ces manquements. Nous...

Retraitement des données salariales

Après une extraction provenant d’un requêteur de données, le format des informations doit être retraité. Pour cela, il vous faut réaliser les actions suivantes :

 Sélectionnez la cellule I2 de la feuille BDD.

 Dans l’onglet Accueil du ruban et dans le groupe Nombre, ouvrez la liste présente.

Vous visualisez la liste de choix suivante :

images/03SOB01.png

Sur cette image, les différents formats possibles sont présentés. Excel indique pour la donnée sélectionnée sa transformation en fonction du choix effectué.

En RH, les informations liées au salaire, à l’âge ou à l’ancienneté nécessitent un retraitement de format.

Retraitement des données salariales

Nous allons étudier dans cette section comment mettre en forme les données salariales. Par défaut, ces informations sont au format Standard. Elles ne présentent aucune particularité. Pour ce type de données, trois formats de retraitements sont possibles :

  • Le format Comptabilité

  • Le format Monétaire

  • Le format personnalisé K€

Les formats Monétaire/Comptabilité

Assez proches, ces formats permettent de convertir une information en format €.

Pour les utiliser, réalisez la manipulation suivante :

 Sélectionnez la colonne R intitulée Salaire.

 Dans l’onglet Accueil du ruban et dans le groupe Nombre, ouvrez la liste présente.

 Sélectionnez le format Monétaire.

 Essayez ensuite le format Comptabilité.

Vous constaterez pour l’un comme pour l’autre les transformations suivantes :

  • Ajout du sigle € à la fin de votre chiffre

  • Ajout de 2 décimales (2 chiffres après la virgule)

  • Ajout du séparateur des milliers

Avec les chiffres présents, vous ne constaterez pas de différence. Désormais :...

Calcul de l’âge et de l’ancienneté

Sur la base de données BDD_Chap3, la date de naissance et la date d’entrée dans la société sont présentes. Éléments essentiels de la fonction RH, l’âge et l’ancienneté sont des variables régulièrement utilisées dans les analyses (ex. : salaire moyen par tranche d’âge/ancienneté…).

Excel dispose dans son répertoire de formules de la fonction FRACTION.ANNEE. Cette dernière calcule l’écart entre deux dates au format décimal. Elle fonctionne de la manière suivante :

FRACTION.ANNEE(date de début;date de fin)

Pour l’utiliser dans notre exemple, procédez comme suit :

 Ajoutez une colonne L nommée Age : réalisez un clic droit sur l’en-tête de la colonne L et choisissez Insérer.

 Sélectionnez la cellule L2.

 Tapez =Fraction.annee(

Avec la saisie automatique, Excel propose dès la saisie des premières lettres cette formule.

 Sélectionnez la cellule contenant la date de naissance (cellule K2).

 Tapez le point-virgule.

 Tapez, entre guillemets, la date à laquelle vous souhaitez connaître l’âge du collaborateur. Ici, pour notre exemple, nous prendrons la date suivante : 31/12/2018.

Le résultat final...

Retraitement des données Texte

Nous allons dans cette partie étudier comment retraiter des informations de type Texte. Nous allons voir les fonctions Convertir, Remplissage Instantané et Rechercher/Remplacer.

En RH, très régulièrement les informations organisationnelles sont présentées de la manière suivante : Nom de la société / Département / Direction / Division.

Souvent, ces informations sont contenues dans une seule cellule. Deux méthodes permettent en seulement quelques clics de les séparer sur plusieurs cellules et ainsi de pouvoir les utiliser de manière distincte pour des études. Il s’agit des fonctions Convertir et Remplissage Instantané.

Convertir

Dans la base de données BDD_Chap3, la colonne N Organisation_Convertir contient les chaînes de valeurs liées à l’organisation. Elles sont présentées comme suit :

Thorgal/DRHG/RS/REMU

Voici la méthodologie pour découper cette donnée et faire apparaître une information par colonne :

 Étant composée de quatre éléments, ajoutez trois colonnes à côté de celle qui contient la donnée (colonne N).

Pour ajouter une colonne, vous devez sélectionner la colonne à droite de celle que vous voulez ajouter puis faites un clic droit, Insérer (pour aller plus vite, le raccourci-clavier Ctrl + fonctionne également).

 Sélectionnez toute la colonne Organisation_Convertir, soit en cliquant sur la lettre N ou en utilisant le raccourci-clavier CtrlShiftFlèche en bas après sélection de la cellule N1 ou N2.

 Cliquez sur l’onglet Données du ruban.

 Dans le groupe Outils de données, cliquez sur Convertir.

La boîte de dialogue Assistant Conversion - Étape 1 sur 3 s’ouvre.

images/03SOB09.png

Cette boîte de dialogue offre deux possibilités de découpage :

  • Délimité

  • Largeur fixe

Délimité permet de découper en fonction d’un caractère. Largeur fixe découpe en fonction d’un nombre de caractères. L’option Délimité est régulièrement à privilégier, car elle est plus simple à utiliser et plus flexible....

Figer les volets

Afin de faciliter l’utilisation des bases de données, figer les volets est particulièrement utile, car il permet d’utiliser les barres de défilement sans perdre les en-têtes de colonnes. Pour les mettre en place, procédez comme suit :

 Sélectionnez la cellule B2.

 Dans l’onglet Affichage, cliquez sur le bouton Figer les volets présent dans le groupe Fenêtre.

Excel vous propose alors de Figer les volets, de Figer la ligne supérieure ou de Figer la première colonne.

 Cliquez sur Figer les volets.

Désormais, grâce à cette manipulation, les en-têtes de colonnes ainsi que la première colonne seront toujours visibles. La sélection de la cellule en amont du clic sur le bouton Figer les volets est importante, car c’est elle qui détermine quelles lignes et quelles colonnes doivent être figées. Elle correspond au point d’intersection entre les lignes et les cellules qui seront figées.

Les tris

Les tris permettent de rendre plus accessibles et homogènes les bases de données. Ainsi, nous allons trier par type de contrat, sexe et du plus âgé au plus jeune.

 Sélectionnez la cellule A1.

 Dans le ruban, cliquez sur le bouton Trier présent dans l’onglet Données et dans le groupe Trier et filtrer.

La boîte de dialogue nommée Tri apparaît prérenseignée d’une ligne appelée Trier par.

images/03SOB19.png

Vous disposez de trois paramètres que vous devez déterminer pour chaque niveau de tri :

  • Trier par : cette liste vous permet de sélectionner la colonne qui sera utilisée pour le tri.

  • Trier sur vous avez le choix entre :

  • Valeurs de cellule

  • Couleur de cellule

  • Couleur de police

  • Icône de mise en forme conditionnelle

  • Ordre : permet de choisir de trier par ordre croissant ou décroissant ou selon une liste personnalitsée.

 Faites dérouler la liste de Trier par.

 Choisissez Libellé type de contrat.

 Dans la liste Trier sur, nous souhaitons trier sur les valeurs ; étant le paramètre indiqué par défaut, vous n’avez pas à modifier cette option. Il en est de même pour l’option Ordre.

 Cliquez sur le bouton Ajouter un niveau.

Une nouvelle ligne nommée Puis par apparaît.

images/03SOB20.png

 Faites dérouler la liste Puis...

Gestion des doublons

Excel propose une fonctionnalité nommée Supprimer les doublons. Simple et rapide, cette fonction est très efficace. Cependant, parfois, il est préférable de passer par des formules comme RECHERCHV, EQUIV ou encore les mises en forme conditionnelles.

Supprimer les doublons

 Sélectionnez la cellule A1 de votre base de données.

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

La boîte de dialogue Supprimer les doublons apparaît.

images/03SOB22.png

Via cette boîte de dialogue, vous devez sélectionner les colonnes qui seront utilisées pour déterminer les doublons. Par défaut, Excel sélectionne toutes les colonnes.

 Ici, nous n’avons pas à modifier cette sélection car nous souhaitons supprimer les lignes en double dont toutes les cellules sont identiques. Ainsi, cliquez sur OK.

Excel vous indique le nombre de valeurs trouvées et supprimées.

images/03SOB23.png

Excel supprime automatiquement les doublons. Cette méthode fonctionne parfaitement si vous connaissez votre base de données, l’existence et la nature des doublons. Si vous souhaitez auditer votre base et étudier, de manière approfondie, ces anomalies, il vous faut procéder par des méthodes manuelles. Nous allons en voir trois :

  • Via la fonction RechercheV

  • Via la fonction Equiv

  • Via les mises en forme conditionnelles

Cependant, nous avons, au travers de la manipulation précédente, supprimé nos doublons. Ainsi, agissez comme suit pour en créer de nouveaux afin de pouvoir réaliser les méthodes manuelles :

 Sélectionnez et copiez la plage de cellules A2 : Z20.

 Collez cette sélection en bas de votre base, c’est-à-dire en cellule A1065.

Pour y accéder plus rapidement, vous pouvez utiliser le raccourci-clavier CtrlFlèche en bas.

Notre base de données dispose désormais de 20 doublons.

Méthode manuelle via la fonction RECHERCHEV

 Sélectionnez la cellule AA1.

 Saisissez : Dédoublonnage via RechercheV.

La fonction RECHERCHEV permet de chercher une valeur dans la première...

Les filtres (simples/avancés)

Éléments incontournables d’Excel, les filtres proposent de nombreuses fonctionnalités que nous allons étudier.

Pour mieux les appréhender, nous allons réaliser un cas pratique RH. Nous allons prendre l’exemple des questions suivantes : « Combien de femmes sont aujourd’hui cadres et quel est leur salaire moyen ? De plus, parmi elles, combien disposent d’une rémunération supérieure à 50 000 € ? » Pour répondre à cette question, nous allons en premier lieu utiliser les filtres simples puis nous utiliserons les filtres avancés.

Les filtres simples

Rapides et efficaces, les filtres simples permettent de répondre à des thèmes de questions RH fréquents comme « Quel est l’effectif ?» ou encore « Quelle est la rémunération moyenne ? » en seulement quelques clics. Pour répondre aux questions posées ci-dessus, via les filtres simples, voici la démarche à suivre :

 Sélectionnez la cellule A1 afin de positionner votre curseur dans notre base de données.

 Cliquez sur l’onglet Données du ruban.

 Dans le groupe Trier et filtrer, cliquez sur le bouton Filtrer.

Vous constatez l’ajout de boutons de liste déroulante sur le bord bas droit de chaque cellule composant votre en-tête. Cela indique que le filtrage est activé, mais aucun filtre n’est encore appliqué.

images/03SOB31.png

 Cliquez sur le bouton de liste déroulante de l’en-tête Sexe présent en cellule E1.

La boîte de dialogue suivante apparaît :

images/03SOBp53.png

Cette dernière vous propose différentes possibilités comme :

  • Trier de A à Z : trie par ordre croissant

  • Trier de Z à A : trie par ordre décroissant

  • Trier par couleur : affiche en premier la couleur sélectionnée

  • Filtrer par couleur : ne fait apparaître que la couleur sélectionnée

  • Filtres textuels pour les données texte, Filtres numériques pour les données chiffrées ou Filtres chronologiques pour les dates : réalise un filtre en fonction d’un critère de recherche comme Contient/Ne...