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

Composition d’une requête RH

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

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 :

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.

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 :

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

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.

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....

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.

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

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

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

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.

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.

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.

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é.

 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 :

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...