Salaires et heures supplémentaires majorées Excel

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


Sujets que vous pourriez aussi aimer :


Heures supplémentaires et salaires

Dans cette formation, nous allons dresser des tableaux de bord afin de calculer le salaire des employés en considérant les heures supplémentaires effectuées. Il s'agit de considérer deux tranches d'heures supplémentaires, celles majorées de 25% et celles majorées de 50%.

L'intérêt de cet exercice est de réaliser des tableaux de bord de suivi des employés, avec interactions entre les feuilles. Au final, nous obtiendrons une fiche de paie simplifiée et complètement automatisée.

• Télécharger le classeur salaires-et-heures-supplementaires.xlsx en cliquant sur son lien,

Ce classeur est constitué de deux feuilles. La première, Salariés, recense chacun des employés avec un identifiant, son salaire et les heures réalisées pour le mois en cours. La seconde, Salarié 1, dresse le modèle de tableau de bord pour le suivi de chaque salarié avec le calcul de ses heures supplémentaires notamment. Nous allons bâtir les formules qui permettront à l'issue de calculer le salaire total de l'employé, selon les heures supplémentaires effectuées. Il suffira ensuite de répliquer ce modèle en l'adaptant à chacun des autres salariés de l'entreprise. Tous les calculs se mettront automatiquement à jour, en fonction des saisies des heures, par le gestionnaire de paie, depuis la première feuille.

• Cliquer sur l'onglet Salarié 1 en bas de la fenêtre Excel pour activer sa feuille,

Les heures réalisées pour chaque semaine du mois en cours, sont à récupérer depuis la première feuille pour les placer dans la colonne HEURES TRAVAILLEES SEMAINE. La colonne sur sa droite, HEURES NORMALES, fixe la durée légale de travail pour comptabiliser les potentielles heures supplémentaires. Comme vous le constatez, les durées sont ici exprimées au format décimal. Nous avions d'ailleurs appris à convertir les heures au format décimal dans la formation Excel pour réaliser des calculs sur les heures et les dates.

Extraire informations depuis la base de données des salariés
Nous devons commencer par récupérer et inscrire le nom du salarié, en fonction de son matricule, indiqué en B3. Plusieurs solutions s'offrent à nous pour extraire de l'information de base de données. La fonction RechercheV notamment doit permettre de récupérer le nom en fonction d'une recherche sur le matricule. Mais comme nous l'avions appris dans la formation pour extraire de l'information de base de données, la fonction BDLire, aurait aussi pu convenir. La syntaxe de la fonction RechercheV est la suivante : =RECHERCHEV(valeur_cherchee; tableau_recherche; num_col_retour).

• Sélectionner la cellule B5 et taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction suivi d'une parenthèse ouvrante, soit recherchev(,
• Cliquer sur la cellule du matricule, soit B3 pour désigner l'élément recherché,
• Taper un point-virgule (;) pour passer au paramètre suivant,
• Cliquer sur l'onglet Salariés en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner tout le tableau des salariés, soit A3:G9, pour indiquer où doit se faire la recherche,
• Taper un point-virgule (;) pour passer au paramètre suivant,
• Saisir le chiffre 2 pour indiquer que l'élément souhaité en retour, se trouve dans la deuxième colonne, celle du nom du salarié,
• Fermer la parenthèse et valider le calcul avec la touche Entrée,

Nous avons bien réussi à extraire le nom du salarié, en fonction de son matricule. Ainsi, pour le suivi des autres employés, après avoir dupliqué la feuille modèle, il suffira de changer le matricule pour récupérer le salarié.

C'est exactement le même calcul que nous devons réaliser dans la colonne HEURES TRAVAILLEES SEMAINE. Il n'est pas possible de réaliser une seule fois la formule, puis de la répercuter grâce aux références absolues sur toute la colonne, à cause du troisième paramètre numérique, qui ne s'adapte pas. Les heures à récupérer sont respectivement placées en colonnes 4, 5, 6 et 7 du tableau, par rapport à la sélection, dans la recherchev.

• En B8, saisir la formule suivante : =RECHERCHEV(B3; Salariés!A3:G9; 4),
• En B9, saisir la formule suivante : =RECHERCHEV(B3; Salariés!A3:G9; 5),
• En B10, saisir la formule suivante : =RECHERCHEV(B3; Salariés!A3:G9; 6),
• En B11, saisir la formule suivante : =RECHERCHEV(B3; Salariés!A3:G9; 7),

Toutes les heures travaillées par semaine, sont ainsi automatiquement récupérées depuis le tableau source.

Calcul des heures supplémentaires
Dans les trois colonnes D, E et F du tableau de bord, il s'agit de calculer les heures supplémentaires réalisées par le salarié. Les heures supplémentaires majorées à 25% concernant les 8 premières heures au-delà de la durée légale, fixée ici à 35 heures. Les heures supplémentaires majorées à 50% concernent les suivantes, soit les heures supplémentaires à partir de la neuvième au-delà des 35 heures.

Dans un premier temps, le calcul du nombre d'heures supplémentaires en colonne D, consiste en une banale soustraction.

• Sélectionner la plage de cellules du calcul, soit D8:D11,
• Taper le symbole = pour débuter le calcul sur la première cellule de la plage,
• Sélectionner la cellule des heures travaillées, soit B8,
• Taper le symbole moins (-) pour réaliser une soustraction,
• Sélectionner la cellule de la durée légale de travail pour la semaine, soit C8,
• Réaliser le raccourci clavier CTRL + Entrée pour valider et reproduire le calcul,

Ce raccourci associé à une plage présélectionnée permet en effet de reproduire la formule sur l'ensemble des cellules, sans devoir tirer la poignée. C'est l'une des techniques enseignées par le support de formation sur les trucs et astuces Excel. Nous obtenons bien toutes les heures supplémentaires en version décimale.

Il s'agit maintenant de calculer le nombre d'heures supplémentaires, majorées à 25%, en colonne E. Les 8 premières heures après la durée légale sont concernées. Au-delà il s'agit d'heures majorées à 50%. Il s'agit donc de poser un critère pour ne considérer que les 8 premières dans le cas où ce nombre est plus important. La formation Excel des primes sur chiffres d'affaires exploite en détail cette fonction SI.

• Sélectionner la plage de cellules concernées par le calcul, soit E8:E11,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle, suivi d'une parenthèse ouvrante, soit SI(,

Cette fonction demande trois paramètres : =SI(critere_verifier ; action_alors ; action_sinon). Tout d'abord il s'agit de spécifier le critère : Savoir si le nombre d'heures supplémentaires est inférieur ou égal à 35. Puis il faut indiquer quoi faire alors : Inscrire ce nombre d'heures dans la cellule. Et enfin, il s'agit d'indiquer quoi faire sinon : ne conserver que les 8 premières heures. Commençons donc par le critère.

• Saisir le critère suivant : D8<=8, suivi d'un point-virgule (;) pour passer au paramètre suivant,
• Sélectionner alors la cellule D8 pour désigner les heures supplémentaires à conserver,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction SI,
• Taper le chiffre 8 pour indiquer de ne conserver que les 8 premières heures dans ce cas,
• Fermer la parenthèse et réaliser la raccourci CTRL + Entrée pour valider et répliquer le calcul,

Nous obtenons ainsi les heures majorées à 25% dans la colonne. La formule fonctionne parfaitement, puisqu'en effet pour la semaine 4, seules les 8 premières heures sont conservées. Les suivantes sont à répliquer dans les heures majorées à 50%. Pour cela, nous pourrions de nouveau exploiter une fonction SI afin de savoir si le nombre d'heures supplémentaires est supérieur à 8. Dans ce cas alors, il s'agirait de réaliser la différence pour obtenir les heures majorées à 50%. Mais étant donné que nous connaissons déjà le nombre total d'heures supplémentaires et celui des heures redistribuées dans la première catégorie, il suffit de réaliser une simple soustraction pour obtenir celles qui doivent être redistribuées dans la seconde catégorie.

• Sélectionner toutes les cellules pour le calcul du nombre d'heures à 50%, soit F8:F11,
• Réaliser le calcul de la soustraction des heures comme suit : =D8-E8,
• Valider et répliquer le calcul par CTRL + Entrée,

Pour terminer la première partie des calculs de ce tableau, il s'agit de réaliser la somme des heures, dans la ligne Total. Ici, nous proposons une méthode efficace qui consiste à sélectionner les cellules qu'il faut sommer, avec la cellule dans laquelle le résultat de la somme doit être déposé, comme suit :

• Sélectionner la première plage de cellules : B8:B12,
• Tout en maintenant la touche CTRL enfoncée, sélectionner la seconde plage : D8:F12,
• Réaliser le raccourci ALT + = de la somme automatique,

Comme la plage de sélection incluait les cellules du résultat à fournir, Excel calcule les sommes en colonnes, sans ambiguïté.

Taux horaire et salaire selon heures majorées
Les deux premières cellules de la partie basse de la feuille consistent à récupérer des informations que nous connaissons déjà. Le nombre d'heures travaillées a déjà été calculé sur la ligne TOTAL MOIS. Il suffit de réaliser une égalité entre les deux cellules de la même feuille. Le salaire de base figure dans le tableau de la feuille Salariés. C'est une fois de plus, une recherchev sur le matricule du salarié qui permettra de retourner cette information.

• Sélectionner la cellule D17 pour calculer le nombre total d'heures travaillées,
• Saisir l'égalité avec le résultat précédent, soit =B12 et valider par Entrée,
• Sélectionner maintenant la celluleD20 pour effectuer le calcul de la recherche du salaire,
• Débuter le calcul par : =recherchev(,
• Sélectionner l'élément recherché, soit le matricule en B3 puis taper un point-virgule (;),
• Cliquer sur l'onglet de la feuille Salariés, en bas de la fenêtre Excel pour l'activer,
• Sélectionner tout le tableau, soitA3:G9, puis taper un point-virgule (;),
• Saisir le chiffre 3 pour indiquer le numéro relatif de colonne où se trouve le salaire,
• Fermer la parenthèse et valider le calcul par CTRL + Entrée,

Désormais, pour pouvoir réaliser les calculs sur les salaires, nous devons commencer par nous intéresser aux taux horaires. Nous considérons qu'un mois de travail correspond à 151,66 heures de travail. Ainsi, le salaire de base divisé par ce nombre d'heures fournira le taux horaire normal. Les deux autres s'en déduiront selon des majorations respectives à 25% et 50%.

• Sélectionner la cellule I20 pour calculer le taux horaire,
• Saisir le calcul suivant : =D20/151,66 pour connaître le coût d'une heure de travail,
• Valider ce calcul en enfonçant la touche Entrée,

Nous obtenons un taux horaire de 10,35. Comme toutes les cellules sont liées entre elles, tous les calculs se mettront automatiquement à jour pour les autres salariés, lorsque nous dupliquerons ce tableau de bord, avec ses formules. Calculons désormais le taux horaire pour les heures supplémentaires majorées. Pour la première catégorie, il s'agit d'ajouter 25% au montant précédent. Ces 25% sont indiqués en H21. Pour la seconde majoration, il s'agit d'ajouter 50% (indiqués en H22) au taux horaire normal. Une fois factorisés, les deux calculs respectifs sont les suivants : taux_horaire*(1 + 0,25) et taux_horaire*(1 + 0,50).

• En I21, saisir le calcul suivant : =I20*(1+H21) et le valider,
• En I22, saisir le calcul suivant : =I20*(1+H22) et le valider,

Nous disposons désormais de toutes les informations nécessaires, pour calculer les montants des heures supplémentaires et en déduire le salaire brut total. Il s'agit de multiplier le nombre d'heures supplémentaires par son taux horaire majoré. A l'issue, il ne restera plus qu'à additionner les résultats au salaire de base et la fiche de paie simplifiée du salarié sera complétée.

• Sélectionner la cellule D21 pour le calcul des heures supplémentaires à 25%,
• Taper le symbole = pour débuter le calcul,
• Sélectionner la cellule du calcul du nombre d'heures supplémentaires à 25%, soit E12,
• Taper le symbole * du pavé numérique pour réaliser une multiplication,
• Désigner la cellule du taux horaire pour les heures supplémentaires à 25%, soit I21,
• Valider le calcul en enfonçant la touche Entrée du clavier,

Nous obtenons déjà une majoration de 271,74 Euros. Il s'agit de répliquer ce calcul, en adaptant les cellules pour le montant des heures supplémentaires à 50%. Nous devons multiplier le nombre d'heures majorées à 50% par le taux horaire à 50%.

• En cellule D22, réaliser le calcul suivant : =F12*I22 et le valider,

Enfin, pour le salaire brut final prenant en compte les heures supplémentaires, il suffit de réaliser la somme du salaire de base avec les montants des heures supplémentaires. Pour cela :

• Pour cela, réaliser le calcul suivant en D24 : =SOMME(D20:D22),

Comme tous les calculs sont prêts, il est désormais très simple d'effectuer le suivi de chacun des salariés. Voyons un exemple pour le deuxième employé Galls au matricule 2. Il s'agit dans un premier temps de dupliquer la feuille de calcul. Pour ce faire :

• Réaliser un clic avec le bouton droit de la souris sur l'onglet de la feuille Salarié 1,
• Dans le menu contextuel, choisir Déplacer ou copier,
• Dans la boîte de dialogue qui suit, cocher la case Créer une copie,
• Dans la liste, sélectionner l'élément (en dernier) pour placer la copie à la suite,
• Puis, valider en cliquant sur le bouton Ok,
• Double cliquer sur l'onglet de la feuille Salarié 1 (2) ainsi créée pour activer sa saisie,
• La renommer Salarié 2 et valider par Ok,
• Dans la cellule B3 du matricule, saisir la valeur 2 à la place de la précédente,

Tous les résultats se mettent instantanément à jour. Ainsi le nom du salarié et les heures qu'il a réalisées sont rapatriées depuis la première feuille, grâce à la fonction RechercheV. Tous les calculs des nombres d'heures s'en déduisent. En découlent les taux horaires adaptés au nouveau salaire de base. Puis se calculent automatiquement les montants des heures supplémentaires majorées, ajoutés au salaire de base, pour obtenir finalement, le salaire brut final du mois.

Il suffit de reproduire ces manipulations pour les autres salariés, afin d'obtenir la fiche de paie personnalisée pour chacun, dans une feuille indépendante. Si bien qu'à l'issue, le gestionnaire de paie n'a plus qu'à mettre à jour les informations dans la feuille Salariés, pour obtenir tous les résultats automatisés dans les feuilles respectives des employés.