Editions ENI Livres | Vidéos | e-Formations
Calculer et traiter l’information RH (effectif, mouvement, masse salariale…)
Introduction
Lors du chapitre Retraiter les données, calculer l’âge et l’ancienneté et utiliser les bases de données, nous avons étudié comment retraiter une base de données pour la rendre plus facilement exploitable. Désormais, nous allons voir les formules et certaines méthodologies qui permettent de calculer et traiter l’information RH.
Pour ce faire, vous trouverez parmi les fichiers en téléchargement un fichier nommé BDD_Chap4. Ce classeur est composé de trois feuilles :
-
Année N-1
-
Année N
-
Indicateurs RH
Nous allons, au travers de ces feuilles, utiliser les fonctions suivantes :
-
RECHERCHEV
-
INDEX / EQUIV
-
SI
-
NBVAL / NBSI / NB.SI.ENS
-
SOMME / MOYENNE / MEDIANE / QUARTILE / CENTILE
-
SOMMEPROD
-
Les formules matricielles
De plus, nous allons voir comment identifier les flux d’entrées et de sorties entre deux périodes.
La fonction RECHERCHEV pour récupérer des données et créer des tranches (de salaires, d’âge…)
La fonction RECHERCHEV permet de récupérer des informations contenues dans d’autres bases de données et également de créer des tranches.
Récupérer des données
Dans la base de données de la feuille Année N, vous disposez du salaire brut annuel pour chaque collaborateur. Pour calculer les évolutions de salaires, il est nécessaire de récupérer le salaire présent dans la feuille Année N-1. La fonction RECHERCHEV étudiée dans le chapitre Retraiter les données, calculer l’âge et l’ancienneté et utiliser les bases de données permet cette action.
Le V signifie Verticale, il existe également la fonction RECHERCHEH pour Horizontale. Ces deux formules sont parfaitement similaires dans leur construction. La seule différence réside dans leur fonctionnement. La fonction RECHERCHEV recherche les valeurs verticalement (par matricule par exemple) tandis que la fonction RECHERCHEH recherche les valeurs horizontalement (par les noms de colonnes par exemple).
Voici la démarche à suivre :
Sélectionnez la cellule X1 de la feuille Année N.
Saisissez Salaire N-1 dans cette cellule.
Sélectionnez la cellule X2.
Saisissez la formule suivante : =RECHERCHEV(D2;’Année N-1’!$D$1:$W$1247;20;FAUX)
D2 est le matricule de la personne recherchée, ‘Année N-1’ !$ D$1:$W$1247 est la matrice où la recherche est effectuée, 20 est le numéro de la colonne de la matrice correspondant à l’information...
La fonction SI pour distinguer des populations
Ce type de formule permet d’afficher une valeur ou d’effectuer un calcul selon une ou plusieurs conditions. Utilisez la fonction SI pour effectuer un test logique (VRAI ou FAUX) sur la valeur d’une cellule ou sur le résultat d’une autre formule ; en fonction du résultat de ce test, la fonction SI effectue une action si le résultat est vrai, ou une autre action si le résultat est faux.
La syntaxe de la fonction SI est la suivante :
=SI(test_logique;valeur si VRAI;valeur si FAUX)
La fonction SI peut également être complétée par des opérateurs booléens (ET/OU) ou faire l’objet de SI imbriqués.
Utilisation d’une fonction SI simple
Dans notre base de données, nous disposons d’une colonne Code Classification et CAT PRO. Code Classification est le niveau du collaborateur. Celui-ci détermine sa catégorie professionnelle (CAT PRO). Cependant, le requêteur de données utilisé pour extraire cette base ne distingue pas les cadres de direction des autres cadres. Après consultation de la convention collective, il a été défini que le code classification 9 correspond aux Cadres de direction. Ainsi, l’utilisation de la fonction SI est nécessaire pour retravailler les catégories professionnelles. Agissez comme suit :...
Compter des effectifs : les fonctions NB
Dans le fichier BDD_Chap4, sélectionnez la feuille nommée Indicateurs RH.
Cette feuille contient une partie nommée Effectif. Nous allons compléter les tableaux présents grâce aux différentes fonctions NB.
Les fonctions NB permettent de dénombrer. En RH, elles sont utilisées essentiellement pour calculer des effectifs.
Nous allons, au travers de cas pratiques, en étudier trois :
-
NBVAL
-
NB.SI
-
NB.SI.ENS
Enfin, nous étudierons la fonction SOMMEPROD qui peut substituer les fonctions NB.
Compter des effectifs sans critère : la fonction NBVAL
La fonction NBVAL détermine le nombre de cellules d’une plage qui ne sont pas vides. Ainsi, pour compter les effectifs, procédez comme suit :
Sélectionnez la cellule E6.
Saisissez la formule suivante : =NBVAL(’Année N’!A2:A1247) et validez.
Vous obtenez 1 246 comme résultat.
Lorsque vous sélectionnez une plage de cellules contenant des valeurs numériques, vous visualisez sur la barre d’état, pour n’importe quelle sélection, le nombre de cellules non vides. Pour visualiser d’autres résultats de fonction, cliquez avec le bouton droit de la souris sur la barre d’état et choisissez parmi les fonctions proposées (Nb (nombres), Minimum ou Maximum).
Ne permettant pas l’ajout de critères, la fonction NBVAL présente assez vite des limites.
Compter des effectifs avec un critère : la fonction NB.SI
La fonction NB.SI dont la syntaxe...
Réaliser des statistiques en RH : les fonctions MOYENNE, SOMME, MÉDIANE, QUARTILE, CENTILE et formules matricielles
Pour illustrer cette partie, nous allons utiliser les données de rémunération présentes dans l’onglet Année N colonne W du fichier BDD_Chap4.
Fonctions moyennes : calcul du salaire moyen
Il existe trois fonctions pour calculer les moyennes :
-
MOYENNE
-
MOYENNE.SI
-
MOYENNE.SI.ENS
MOYENNE
Cette formule calcule la moyenne d’un ensemble de cellules contenant des valeurs. Pour l’utiliser dans notre cas pratique, agissez comme suit :
Sélectionnez la cellule D23 de la feuille Indicateurs RH.
Saisissez la formule suivante : =MOYENNE(’Année N’!W2:W1247)
Vous obtenez comme résultat 47 904€.
Pour rappel, lorsque vous sélectionnez une plage de cellules contenant des valeurs numériques, vous visualisez sur la barre d’état, pour n’importe quelle sélection, la moyenne de ces valeurs. Pour visualiser d’autres résultats de fonction, cliquez avec le bouton droit de la souris sur la barre d’état et choisissez parmi les fonctions proposées (Nb (nombres), Minimum ou Maximum).
Cette formule ne propose pas l’application de critères. Pour en appliquer un, la formule MOYENNE.SI est requise.
MOYENNE.SI
Cette formule détermine la moyenne des cellules satisfaisant un critère particulier. Les arguments de cette fonction sont les suivants : =MOYENNE.SI(Plage ;Critères ;[Plage_moyenne])
Plage représente les cellules servant de base à l’application du critère.
Critères représente la condition sous la forme d’un nombre ou d’un texte définissant quelles cellules sont à utiliser pour déterminer la moyenne.
Plage_moyenne représente les cellules qui seront utilisées pour le calcul de la moyenne. Si omis, les cellules de la Plage sont alors utilisées.
Ainsi, pour calculer les salaires moyens par sexe, agissez comme suit :
Sélectionnez la cellule B23 de la feuille Indicateurs RH.
Saisissez la formule suivante : =MOYENNE.SI(’Année N’!$E$2:$E$1247;"M";’Année N’!$W$2:$W$1247)
Validez.
Vous obtenez 48 806...
Rechercher et analyser les flux d’entrées et de sorties
En RH, il peut être nécessaire de savoir quelles personnes sont entrées dans la société et quelles sont celles qui en sont sorties. Avec certaines formules présentées ci-dessus, nous allons voir comment identifier ces flux et comment nous assurer que nos résultats sont corrects.
Pour cela, agissez comme suit :
Ajoutez une nouvelle feuille et appelez la E-S.
Sélectionnez et copiez la liste de matricules (colonne D) de la feuille Année N-1.
Collez cette liste en cellule A2 de la feuille nouvellement créée E-S.
Sélectionnez la cellule A1 de la feuille E-S et saisissez Matricule M-1.
Sélectionnez et copiez la liste de matricules (colonne D) de la feuille Année N.
Collez cette liste en cellule C2 de la feuille E-S.
Sélectionnez la cellule C1 de la feuille E-S et saisissez : Matricule M.
Sélectionnez la cellule B1 de la feuille E-S et saisissez : Sorties.
Sélectionnez la cellule D1 de la feuille E-S et saisissez : Entrées.
Appliquez du Gras sur les cellules de la ligne 1.
En ayant suivi ces manipulations, vous obtenez l’écran suivant.
Pour identifier les sorties, procédez comme indiqué :
Sélectionnez la cellule B2 de la feuille E-S.
Saisissez la formule suivante...
