Calculs de primes selon ancienneté avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Calcul des primes selon l'ancienneté

Dans ce cas pratique, l'entreprise souhaite automatiser le calcul des primes de ses salariés, selon un barème établi sur l'ancienneté de chacun.


Calcul de l'ancienneté d'un salarié
Pour les besoins de l'application, nous démarrons d'un tableau déjà mis en forme. Pour ce faire :

• Télécharger le classeur Excel primes-anciennete-salaries.xlsx en cliquant sur son lien,
• Puis, l'ouvrir dans Excel et cliquer si nécessaire sur le bouton Activer le contenu,

Comme l'illustre la capture ci-dessus, la troisième colonne (D) indique les dates d'entrée de chacun des salariés dans l'entreprise. Ces données sont bien écrites au format date. En cellule C4 est inscrite la date du jour. Cette dernière est dynamique car écrite avec la fonction Aujourdhui() qui permet de réactualiser cette date avec le calendrier. C'est en réalisant la différence entre la date du jour et la date d'entrée dans l'entreprise que nous pourrons calculer l'ancienneté du salarié. La formation Excel sur le calcul des heures montre notamment comment exploiter la fonction volatile Datedif.

La fonction Datedif() permet de calculer la différence entre deux dates en nombre de jours, de mois ou d'années, selon l'argument qui lui est passé. Sa syntaxe est la suivante :

=DATEDIF(date_debut; date_fin; format_sortie)

La date de début est la date d'entrée du salarié dans l'entreprise. La date de fin correspond à la date du jour. Le format de sortie s'exprime entre guillemets. 'm' pour calculer une différence en nombre de mois et 'y' pour calculer la différence entre les deux dates en nombre d'années. Selon ce principe :

• Sélectionner l'ensemble des anciennetés à calculer, soit la plage E7:E19,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit Datedif(,
• Sélectionner la première cellule de la date d'entrée, soit D7,
• Taper un point-virgule pour passer à l'argument de la date de fin,
• Sélectionner la cellule de la date du jour, soit C4,
• Enfoncer la touche F4 du clavier pour figer cette cellule,
• Taper un point-virgule pour passer à l'argument du format de sortie,
• Taper la lettre y entre guillemets, soit 'y',
• Fermer la parenthèse de la fonction,
• Puis réaliser le raccourci clavier CTRL + Entrée pour valider le calcul et le répercuter,

Nous obtenons la différence entre les deux dates en années, grâce au format de sortie, le Y pour Year. Nous avons figé la cellule de la date du jour dans le calcul à l'aide de la touche F4. Nous transformons ainsi la cellule en référence absolue pour qu'elle ne se déplace pas avec le calcul au moment de le reproduire sur les cellules du dessous. Enfin le raccourci CTRL + Entrée a permis de valider le calcul et de le reproduire sur l'ensemble des cellules présélectionnées. C'est une méthode que nous avons apprise dans la formation Excel sur les techniques professionnelles pour reproduire un calcul.

Néanmoins le résultat n'est pas pleinement satisfaisant. La différence calculée en années conduit fort logiquement à des nombres entiers. Or la majorité de ces différences ne conduit pas à des nombres ronds. Par exemple, pour une ancienneté de 15 ans et 3 mois, nous aimerions afficher le résultat 15,25, soit 15 ans et un quart d'une année. Pour ce faire, nous allons calculer la différence entre les deux dates en nombre de mois et diviser le résultat par 12. Dans un premier temps, le format de sortie 'y' de la fonction Datedif doit être remplacé par le format 'm'.

• Sélectionner de nouveau toutes les cellules de l'ancienneté, soit E7:E19,
• Enfoncer la touche F2 du clavier pour accéder à la saisie de la première des formules,
• Remplacer le y par un m dans le troisième paramètre de la fonction Datedif,
• Puis, cliquer après la parenthèse fermante de la fonction pour y placer le point d'insertion,
• Ajouter un slash (/) pour la division puis taper 12 pour les douze mois de l'année,
• Valider le calcul par CTRL + Entrée pour répercuter les modifications sur toute la plage,

Nous obtenons toutes les différences entre les deux dates en valeurs décimales. Le calcul que nous avons conçu est le suivant :

=DATEDIF(D8; $C$4; 'm')/12



Primes d'ancienneté par tranches
Désormais nous souhaitons afficher la prime correspondant à l'ancienneté du salarié, que nous venons de calculer. Le tableau situé en K6:L11 présente la grille des pourcentages accordés selon des tranches d'ancienneté.


Ainsi un salarié dont l'ancienneté dans l'entreprise est située entre 4 et 8 ans se verra attribuer une prime d'ancienneté de 5%. Un salarié dont l'ancienneté est supérieure à 16 ans se verra attribuer une prime de 17%.

Deux méthodes sont proposées pour extraire le pourcentage de prime correspondant à l'ancienneté de l'employé. En colonne F, le calcul doit se faire avec les fonctions Si et leurs imbrications. En colonne G, le calcul doit se faire avec la fonction RechercheV pour extraire de l'information de base de données.

Cette comparaison permettra de mettre en lumière les limitations des imbrications de fonctions conditionnelles, lorsque le nombre de critères devient important. En colonne F, il s'agit de traduire par formule, le raisonnement suivant :

Si l'ancienneté du salarié (E7) est inférieure à 4 ans (K8), alors la prime est de 0% (L7) sinon si l'ancienneté est inférieure à 8 ans (K9), alors la prime est de 5% (L8), sinon si l'ancienneté est inférieure à 12 ans (K10), alors la prime est de 9% (L9), sinon si l'ancienneté est inférieure à 16 ans (K11) alors la prime est de 13% (L10) sinon la prime est de 17% (L11). Comme il y a 5 situations à envisager, nous devons imbriquer 4 fonctions Si (n-1). Comme les cellules des anciennetés doivent être comparées à des cellules fixes dans un tableau de bord, ces dernières (Tranches et pourcentages) devront être figées (Touche F4).

• Sélectionner l'ensemble des cellules à calculer, soit la plage F7:F19,
• Saisir la formule suivante, en cliquant les cellules correspondantes pour les désigner :

=SI(E7 < $K$8; $L$7; SI(E7 < $K$9; $L$8; SI(E7 < $K$10; $L$9; SI(E7 < $K$11; $L$10; $L$11))))



Toutes les cellules issues de la grille sont effectivement figées dans le calcul par la touche F4, expliquant la présence des dollars. Tous les résultats obtenus sont cohérents. Cependant, comme vous le constatez, la résolution d'un problème qui engage un grand nombre de conditions, conduit à une formulation complexe. Mais ce n'est pas le seul souci. Si la grille des barèmes venait à évoluer, avec de nouvelles tranches de primes, elles seraient ignorées. Ce calcul, par imbrications de fonctions SI, bien que puissant, est à la fois relativement complexe et surtout n'est pas dynamique, ni évolutif.

C'est pourquoi nous envisageons de résoudre le problème en colonne G avec une fonction permettant d'extraire dynamiquement les informations de cette source de données. La fonction RechercheV permet de retourner une donnée (Le pourcentage de prime) en fonction d'une valeur cherchée (L'ancienneté). Sa syntaxe est la suivante :

=RechercheV(valeur_cherchee ; tableau_de_recherche ; numero_colonne ; correspondance)

Le troisième argument est le numéro de colonne où se trouve l'information à récupérer. Le pourcentage de la prime à rapatrier en fonction de l'ancienneté, se trouve dans la deuxième colonne du tableau de recherche. Donc nous inscrirons le chiffre 2. Enfin, le dernier paramètre concerne la correspondance. Il s'agit d'indiquer une valeur booléenne, Vrai ou Faux. Si nous indiquons Faux, la rechercheV tentera de trouver la valeur exacte de l'ancienneté. Or, dans la majorité des cas, elle ne la trouvera pas, puisque cette grille recense uniquement des tranches. C'est pourquoi nous utiliserons le paramètre booléen Vrai pour demander à la fonction Excel RechercheV de se rapprocher de la valeur si elle ne la trouve pas, afin de retourner le pourcentage correspondant. Ainsi, chaque salarié entrera bien dans une tranche correspondant à une prime.

• Sélectionner toutes les cellules du calcul, soit la plage G7:G19,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit Recherchev(,
• Cliquer sur la cellule de la valeur recherchée, soit E7 pour la première ancienneté,
• Taper un point-virgule pour passer à l'argument du tableau de recherche,
• Sélectionner la grille des primes, soit la plage de cellules K6:L11,
• Enfoncer la touche F4 pour figer cette plage de recherche,
• Taper un point-virgule pour passer à l'argument de l'indice de colonne de retour,
• Taper le chiffre 2 pour indiquer qu'il s'agit de la deuxième colonne dans la sélection,
• Taper un point-virgule pour passer à l'argument booléen de la correspondance,
• Saisir VRAI puis fermer la parenthèse de la fonction RechercheV,
• Puis, valider et répercuter le calcul à l'aide du raccourci clavier CTRL + Entrée,

La formule est la suivante :

=RECHERCHEV(E7; $K$6:$L$11; 2; VRAI)

Sa syntaxe est beaucoup plus simple que la précédente sachant que nous obtenons exactement les mêmes résultats. Comme vous l'avez compris, la fonction RechercheV avec le paramètre booléen Vrai, se place sur la valeur inférieure la plus proche, lorsque la valeur cherchée n'est pas trouvée. Donc, à l'instar des fonctions SI imbriquées, lorsque l'ancienneté n'a pas atteint la valeur de la nouvelle tranche, c'est la prime inférieure qui lui est attribuée.


Cette utilisation était l'occasion d'illustrer l'intérêt de ce dernier paramètre pour une recherche avec, une correspondance exacte ou approximative. Une fois de plus, la mise en pratique prouve la puissance et l'efficacité de la fonction Excel RechercheV pour extraire des informations d'une source de données.

Néanmoins, comme pour les fonctions SI précédentes, ces résultats ne sont pas dynamiques. Si de nouvelles tranches sont ajoutées dans la grille, ni la première formule, ni la seconde ne les considèrerons.

Extraction dynamique et évolutive des données
Pour que la recherche d'information dans la source de données devienne évolutive, la recherche doit être réalisée dans un tableau dont les bornes s'adaptent au contenu. C'est la fonction Excel DECALER qui permet de changer ces bornes. Sa syntaxe est la suivante :

=Decaler( cellules_de_depart ; decalage_ligne ; decalage_colonne ; [hauteur] ; [largeur])

Dans notre cas, les cellules de départ correspondent aux cellules d'entête de la source de données, soit K6:L6. Comme il ne s'agit pas de décaler la recherche par rapport à ce point de départ, nous indiquerons 0 en deuxième et troisième argument. En revanche, il s'agit de décaler la borne inférieure du tableau, tant qu'il y a des données inscrites. Donc nous renseignerons le paramètre facultatif [hauteur] que nous calculerons grâce à la fonction Nbval d'Excel. Cette fonction compte le nombre de cellules qui ne sont pas vides dans une plage. Donc cette méthode nous permettra de déplacer la borne inférieure du tableau de recherche, tant qu'il y a des cellules à considérer.

• Sélectionner les cellules du calcul, soit la plage G7:G19,
• Enfoncer la touche F2 du clavier pour activer la saisie de la première formule de la plage,
• Remplacer le deuxième paramètre ($K$6:$L$11) du tableau de recherche par la fonction Decaler suivante :

DECALER($K:$L; 6;0; NBVAL($K:$L))

• Puis valider le calcul par CTRL + Entrée pour le reproduire sur toute la plage,

Aucun résultat ne change pour l'instant puisque la grille des primes n'a pas évolué. En revanche, si vous ajoutez une nouvelle tranche, comme illustré sur la capture ci-dessus, vous constatez que le résultat de la fonction RechercheV intègre la nouvelle donnée, tandis que celui de l'imbrication des fonctions Si reste stoïque.

Apportons quelques explications à la fonction Excel Decaler qui a permis de déplacer les bornes du tableau de recherche, en fonction de son contenu :

DECALER($K:$L; 6;0; NBVAL($K:$L))

En premier argument, nous désignons l'intégralité des deux colonnes, dans lesquelles se situe le tableau des barèmes. Nous figeons ces colonnes, même si la réplication en lignes ne le nécessite pas : $K:$L. Comme la première cellule de ce tableau, commence à la ligne 6, nous décalons la plage ainsi désignée avec le deuxième argument. Nous saisissons donc le chiffre 6. En revanche, le tableau ne doit pas être décalé en colonne. Nous saisissons le chiffre 0 en troisième argument. Enfin, il s'agit de définir la hauteur de la plage de cellules sur laquelle la recherche s'opère. Cette hauteur dépend des valeurs renseignées et des nouvelles potentielles saisies. Donc, nous utilisons la fonction Excel NbVal qui retourne une valeur entière correspondant au nombre de cellules non vides, situées en dessous. Cette valeur indique ainsi le nombre de lignes, soit la hauteur sur laquelle le tableau doit être considéré pour la recherche. C'est la raison pour laquelle, lorsque nous ajoutons une nouvelle tranche, la fonction Nbval l'intègre et permet d'agrandir le tableau dynamiquement. La donnée est trouvée et le calcul est à la fois dynamique et évolutif.