Editions ENI Livres | Vidéos | e-Formations
Utilisation des TCD : l’exemple des Révisions Salariales Individuelles
Introduction
Lors du chapitre Calculer et traiter l’information RH (effectif, mouvement, masse salariale…), nous avons étudié les fonctions SOMME, NB et MOYENNE. Ces formules peuvent aussi être calculées via les TCD (Tableaux Croisés Dynamiques). Ces derniers permettent également de calculer des répartitions en pourcentage et proposent des fonctions pour analyser vos données. Nous allons étudier dans ce chapitre leur utilisation autour d’un cas pratique récurrent en RH : l’étude des révisions salariales individuelles (RSI). Vous trouverez en téléchargement sur le site des Éditions ENI le fichier nommé : BDD_Chap5. Ce fichier comporte une feuille nommée RSI.
Cette feuille contient pour chaque collaborateur les mesures salariales attribuées. Elle est composée des éléments suivants :
-
Société
-
Sexe
-
Matricule
-
Type de contrat
-
Code classification
-
Taux d’activité contractuel
-
Age
-
CATPRO
-
Salaire de Base
-
Salaire de base + Variable
-
Date d’augmentation
-
Motif augmentation individuelle
-
Montant augmentation individuelle (annuel)
Nous allons grâce à cette base de données aborder les aspects suivants :
-
Composition d’un TCD
-
Comment construire un TCD
-
Calcul des effectifs avec un TCD
-
Calcul de la masse salariale
-
Calcul de statistiques de rémunération...
Composition d’un TCD
Un TCD est principalement paramétrable avec l’encadré Champs de tableau croisé dynamique. Il est composé de champs, de lignes, de colonnes, de filtres et de valeurs. De plus, il est possible d’ajouter des segments et des séries chronologiques afin d’optimiser l’expérience utilisateur.
Les champs
Dans un TCD, les champs sont les colonnes qui composent votre base de données source.
Elles sont visibles dans le premier encadré de la fenêtre Champs de tableau croisé dynamique.
De plus, en complément des champs indiqués par défaut, il est possible d’en ajouter des nouveaux, notamment grâce aux champs calculés disponibles dans l’onglet Outils de tableau croisé dynamique - Analyse groupe Calcul et aux différents regroupements qu’il est possible de faire via un TCD (tranches d’âge…).
Les champs doivent par la suite être affectés aux encarts Lignes ou Colonnes pour devenir des variables, aux Filtres ou encore en Valeurs pour être la base de calcul.
Lignes/Colonnes
Les Lignes et les Colonnes sont les variables utilisées pour calculer un TCD. Pour définir un champ en tant que critère de lignes ou de colonnes, il faut sélectionner le champ souhaité dans la liste proposée et le faire glisser dans l’encadré...
Création d’un TCD
Pour créer un TCD, réalisez la manipulation suivante :
Cliquez sur la cellule A1 de la feuille RSI.
Dans l’onglet Insertion, cliquez sur le bouton Tableau croisé dynamique présent dans le groupe Tableaux.
La boîte de dialogue Créer un tableau croisé dynamique apparaît.
Si votre curseur se situe dans une base de données, alors Excel sélectionne automatiquement par défaut l’ensemble de cette base. Il n’est ainsi pas nécessaire de sélectionner toute la base de données en amont ou de filtrer sur les données souhaitées.
Par défaut, Excel vous propose de créer le TCD dans une nouvelle feuille.
Cliquez sur OK.
Vous obtenez le résultat suivant.
Une nouvelle feuille s’est créée avec cette information et la liste des champs du TCD est désormais affichée sur la droite de votre écran.
Nommez cette nouvelle feuille : TCD.
Le TCD est créé. Désormais, les champs doivent être glissés dans les différentes rubriques indiquées (Lignes, Colonnes, Filtres et Valeurs). Nous allons réaliser plusieurs utilisations récurrentes en RH.
Il est possible de modifier la source de données d’un TCD en cliquant sur le bouton Changer la source de données présent dans l’onglet...
Calcul des effectifs
Concernant les effectifs, deux types d’information sont utilisés en RH :
-
Le dénombrement
-
La répartition en pourcentage
Le dénombrement
Nous allons calculer les effectifs par sexe et par type de contrat. Pour cela, procédez comme suit :
Avec le TCD récemment créé, faites glisser le champ Sexe dans l’encadré Lignes.
Faites glisser le champ Type de contrat dans l’encadré Colonnes.
Faites glisser le champ Matricule dans l’encadré Valeurs.
Pour dénombrer des effectifs, il est conseillé en RH d’indiquer la colonne Matricule dans l’encadré Valeurs.
Dans le volet Champs de tableau croisé dynamique, vous obtenez le visuel suivant :
Dans l’encadré Valeurs, Excel indique quel type de calcul il effectue. Ici, il est indiqué Nombre. Excel compte donc le nombre de matricules présent dans la base de données. Attention, si des doublons de matricules sont présents, alors ils seront comptabilisés deux fois.
Reportez-vous au chapitre Retraiter les données, calculer l’âge et l’ancienneté et utiliser les bases de données pour savoir comment gérer et éliminer les doublons.
De plus, le TCD affiche les résultats suivants :
Ainsi, le nombre d’effectifs est détaillé par type de contrat...
Calcul de la masse salariale et de l’enveloppe des augmentations individuelles versées
Via les TCD nous allons calculer la masse salariale et l’enveloppe des augmentations individuelles attribuées par sexe, mais uniquement pour les niveaux 5, 6 et 7. Pour cela, réalisez la manipulation suivante :
Sélectionnez et faites glisser le champ Code Classification dans l’encadré Filtres.
Une nouvelle ligne nommée Code classification ainsi qu’une liste déroulante sont apparues.
Faites dérouler cette liste.
La liste des codes classification présente dans la base de données apparaît.
Cochez Sélectionner plusieurs éléments.
Désormais, vous pouvez cocher les codes classifications que vous voulez étudier.
Décochez les classes 2, 3 et 4.
Cliquez sur OK.
Le filtre est paramétré. De plus, vous pouvez constater que les valeurs affichées par le TCD ont été automatiquement mises à jour.
Dans le volet Champs de tableau croisé dynamique, décochez le champ Matricule.
Dans l’encadré Valeurs, le champ Matricule n’apparaît plus.
Dans le volet Champs de tableau croisé dynamique, décochez le champ CATPRO.
Dans l’encadré Colonnes, le champ CATPRO n’apparaît plus.
Sélectionnez puis...
Calcul du montant moyen d’augmentation individuelle
Les TCD permettent également de réaliser des moyennes. En RH, nous pouvons utiliser cette fonctionnalité pour calculer le salaire moyen ou encore les augmentations individuelles moyennes attribuées. Dans le cadre du cas pratique proposé, nous allons calculer l’augmentation annuelle moyenne attribuée pour les classes 5, 6 et 7 par sexe et par tranche d’âge. Voici la démarche à suivre :
Dans le volet Champs de tableau de croisé dynamique, désélectionnez le champ Salaire de base + variable.
Dans l’encadré Valeurs, le champ Salaire de base + variable a disparu.
Cliquez sur Somme de Montant augmentation individuelle (annuel).
Un menu apparaît.
Cliquez sur Paramètres des champs de valeurs.
La fenêtre Paramètres des champs de valeurs apparaît.
Dans l’onglet Synthèse des valeurs par, sélectionnez la fonction Moyenne comme type de calcul utilisé.
Cliquez sur OK.
Faites glisser le champ Sexe dans l’encadré Colonnes.
Sélectionnez et faites glisser le champ Age dans l’encadré Lignes.
Sélectionnez la cellule A5.
Dans l’onglet Outils de tableau croisé dynamique - Analyse, cliquez sur Grouper le champ présent dans le groupe Groupe.
Une fenêtre nommée...
Calcul du pourcentage d’augmentation moyen
En RH, il est intéressant de savoir quel est le pourcentage d’augmentation moyen en fonction de différents critères. Pour le calculer, les TCD proposent une fonctionnalité nommée : Champ calculé.
Dans notre cas pratique, nous allons chercher à connaître ce pourcentage par sexe et par tranche d’âge pour les salariés ayant eu une augmentation individuelle. Voici la démarche à suivre.
Dans le volet Champs de tableau croisé dynamique, désélectionnez le champ Code classification, le champ Montant augmentation individuelle (annuel) et le champs Sexe.
Faites glisser le champ Montant augmentation individuelle (annuel) dans l’encadré Filtres.
La ligne de filtre apparaît.
Déroulez la liste proposée et cochez la case Sélectionner plusieurs éléments.
Désélectionnez la valeur 0 (ou - € si vous êtes en format monétaire) afin d’exclure les personnes n’ayant pas eu d’augmentation individuelle et validez par OK.
Dans l’onglet Outils de tableau croisé dynamique - Analyse, cliquez sur Champs, éléments et jeux présent dans le groupe Calculs puis cliquez sur Champ calculé.
Une boîte de dialogue nommée Insertion d’un champ calculé apparaît....
Créer des segments
Les segments sont des filtres. Ils permettent d’apporter un esprit tableau de bord dynamique à vos TCD. Pour les mettre en place, voici la démarche à suivre :
Cliquez sur une cellule du TCD.
Dans l’onglet Insertion, cliquez sur Segment présent dans le groupe Filtres.
La fenêtre Insérer des segments apparaît. Elle propose à la sélection l’ensemble des champs présents dans le TCD.
Sélectionnez les champs pouvant être utilisés comme filtre, soit ici : Sexe, Type de contrat, Code classification, CATPRO et Motif augmentation individuelle.
Validez par OK.
Sous forme de cascade, chaque champ sélectionné dispose de son segment. Ils comportent toutes les valeurs présentes dans les champs.
Il est alors possible de sélectionner les critères souhaités. Le TCD se mettra à jour automatiquement en fonction des sélections réalisées.
Par ailleurs, dans le champ Code Classification, les valeurs 2, 3, 4 sont désélectionnées car nous les avons utilisées précédemment en tant que critère de filtre.
Cliquez sur la croix rouge présente sur le bord haut droit du segment Code classification afin de retirer le filtre suivant.
Les valeurs du TCD se sont automatiquement mises à jour.
Créer des filtres chronologiques
Proche des segments, les séries chronologiques ne concernent que les champs au format date. Elles permettent de sélectionner des périodes, des intervalles de temps. Pour les créer, voici la démarche à suivre :
Cliquez sur une cellule du TCD.
Dans l’onglet Insertion, cliquez sur Chronologie présent dans le groupe Filtres.
La fenêtre Insérer une chronologie apparaît. Elle présente les champs éligibles aux séries chronologiques.
Dans notre exemple, seul le champ Date d’augmentation est disponible à la sélection.
Sélectionnez-le et cliquez sur OK.
Vous obtenez le visuel suivant (vous pouvez obtenir un résultat différent car Excel place le curseur de la frise chronologique à la date du jour) :
Via cet outil, sélectionnez les périodes souhaitées. Par défaut, Excel regroupe les dates par mois. Via la liste déroulante présente sur la droite de cette image, il est possible de les regrouper par année, trimestre, ou encore de ne pas les grouper et faire apparaître les jours.
Cliquez sur le mois de janvier 2018 puis faites glisser le curseur de sélection jusqu’au mois de juin 2018 afin d’avoir les résultats du premier semestre.
Vous obtenez les résultats suivants :
Attention : les séries...
Créer un graphique croisé dynamique
Les graphiques croisés dynamiques permettent de mettre sous forme graphique les TCD. Pour les créer, agissez comme suit :
Sélectionnez la cellule A5.
Dans l’onglet Outils de tableau croisé dynamique - Analyse du ruban, cliquez sur le bouton Graphique croisé dynamique présent dans le groupe Outils.
La fenêtre Insérer un graphique apparaît.
Sélectionnez le premier histogramme proposé.
Cliquez sur OK.
Vous obtenez le résultat suivant :
Contrairement aux graphiques « classiques », les graphiques dynamiques proposent des listes de plusieurs filtres assimilables aux encadrés Filtres, Colonnes, Lignes et Valeurs afin de paramétrer le graphique que vous souhaitez.
Toutes les modifications apportées dans le TCD ou dans la liste de champs de tableau croisé dynamique modifieront le graphique croisé dynamique et inversement.
Mettre en forme le TCD
Il est possible d’appliquer des mises en forme préétablies. Pour accéder à celles-ci :
Cliquez dans le TCD.
Cliquez sur l’onglet Outils de tableau croisé dynamique - Création du ruban.
Cet onglet est composé de trois groupes :
-
Disposition
-
Options de style de tableau croisé dynamique
-
Styles de tableau croisé dynamique
Disposition permet de gérer les totaux, les sous-totaux et la disposition du rapport (Compactée, Plan et Tabulaire).
Options de style de tableau croisé dynamique permet d’insérer des bandes et d’afficher les en-têtes de lignes et de colonnes.
Styles de tableau croisé dynamique permet de mettre en place les mises en forme prédéfinies proposées par Excel.
Avant communication des TCD, utiliser les mises en forme proposées dans le groupe Styles de tableau croisé dynamique permet de donner un aspect visuel attractif aux données.
Mettre à jour un TCD
Il est fréquent que les TCD soient utilisés pour automatiser des analyses, des reportings…. Cependant, en cas de modifications de la base de données source, les TCD ne se mettent pas à jour automatiquement.
Ainsi, dans notre exemple :
Dans l’onglet RSI, en colonne L, supprimez les lignes contenant la valeur suivante : Retour maternité
Les valeurs du TCD, présent dans la feuille éponyme, n’ont pas évolué, pourtant plusieurs valeurs ont été supprimées. Pour le mettre à jour, procédez de la manière suivante :
Cliquez sur le TCD puis dans l’onglet Analyse, cliquez sur Actualiser présent dans le groupe Données.
Le TCD ainsi que les segments se sont mis à jour (la valeur Retour maternité n’est plus disponible à la sélection).
Ajout d’un champ dans un TCD
Parfois un TCD se doit d’évoluer par l’ajout d’une colonne ou d’une ligne. Il est important de savoir qu’une ligne ou une colonne ajoutée dans la base de données sera automatiquement prise en considération par le TCD après actualisation de ce dernier. Cependant, si vous ajoutez une colonne ou une ligne à la fin de votre base de données d’origine, il est nécessaire de changer la source de données afin que votre TCD prenne en considération l’intégralité de la base de données.
Dans l’onglet RSI, sélectionnez la cellule N1 et saisissez Test ajout colonne.
Cliquez dans le TCD présent dans l’onglet éponyme.
Actualisez le TCD.
La nouvelle colonne n’est pas présente dans le volet Champs de tableau croisé dynamique.
Dans l’onglet Analyse, cliquez sur le bouton Changer la source de données présent dans le groupe Données.
La fenêtre nommée Modifier la source de données du tableau croisé dynamique apparaît.
Sélectionnez l’ensemble de la nouvelle base de données puis cliquez sur OK.
Désormais, la colonne Test ajout colonne est présente dans la liste des champs.
Les défauts des TCD
Les TCD proposent énormément de possibilités à l’utilisateur. Ils peuvent être une solution de contournement aux formules. Cependant, ils comportent également quelques manquements. Par exemple, il est impossible de calculer des médianes ou des quartiles par leur biais.
De plus, bien qu’ils soient très pratiques, attention de ne pas en abuser, car ce sont des outils très fragiles. En effet, lors de réalisation de travaux récurrents (reporting, études régulières…) il est très tentant de les utiliser car ils sont simples à exploiter ; cependant, au moment de la mise à jour de votre étude/reporting, si la base de données source a été modifiée (lignes/colonnes supplémentaires), il est impératif d’actualiser le tableau ou de changer la source de données. Malheureusement, il arrive fréquemment que les utilisateurs oublient de faire cette mise à jour ; ainsi, leurs analyses s’en trouvent impactées.
Pour des actions d’automatisation de fichiers récurrents, privilégiez l’utilisation des formules SOMMEPROD, NB, SOMME ou MOYENNE plutôt que les TCD.
