Statistiques Excel sur le cumul des heures travaillées

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


Sujets que vous pourriez aussi aimer :


Cumul des heures travaillées et majorées

Manipuler les heures de travail, y compris supplémentaires, est un jeu d'enfant avec les calculs matriciels. Et c'est ce que nous proposons de découvrir dans ce cas pratique.



La capture ci-dessus illustre la solution aboutie. A partir de la liste des heures travaillées pour chaque jour du mois, nous dressons un bilan détaillé. Il est question de totaliser les heures de travail mais aussi d'isoler les heures supplémentaires effectuées. Selon les taux, les calculs des montants soit des salaires à verser, sont effectués.

Source et présentation de la problématique
Pour réaliser ces travaux, nous avons besoin de substance que nous proposons de récupérer.

• Télécharger le classeur cumul-heures-travail.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons un tableau énumérant les heures de travail effectuées par un salarié, pour les 31 jours du mois de Mars 2020. Ces heures ont été effectuées sur différents postes mentionnés en colonne D. Il s'agira d'une contrainte à considérer pour dresser des statistiques précises.



Sur la droite, un tableau de bord fixe certaines contraintes et certains objectifs. Le coût horaire est fixé à 10 Euros. Les heures supplémentaires sont déclenchées au-delà de la huitième heure. Et elles sont majorées de 25%. Ces conditions sont inscrites en ligne 6 entre les colonnes F et H.

Entre les colonnes J et K, nous devons tout d'abord comptabiliser toutes les heures travaillées en excluant les Week-End. Selon les conventions collectives, des majorations supplémentaires sont en effet à entrevoir. De plus, il s'agit d'une contrainte intéressante additionnée pour mesurer la souplesse et la puissance des calculs matriciels. Sur ce total d'heures, nous devrons donc en déduire le montant, soit le coût.

Ensuite, il conviendra de réaliser le découpage de ces heures, heures supplémentaires et coûts associés par poste. Ce sont les résultats qu'attendent les deux petits tableaux entre les colonnes F et H. Cela va sans dire, outre la manipulation de données spécifiques comme les heures, il va être question de recouper maintes conditions pour aboutir aux résultats affinés escomptés.

Repérer visuellement les Week-End
Pour plus de clarté mais aussi pour renforcer l'interprétation des calculs de synthèse, nous proposons d'appliquer une mise en forme différente aux jours de Week-End repérés dynamiquement. Nous connaissons la fonction Excel JourSem. Elle retourne le numéro du jour de semaine pour une date qui lui est passée en paramètre. Mais attention, dans sa version classique, elle fonctionne sur le calendrier américain. Le Dimanche est identifié par le chiffre 1 tandis que le Samedi est identifié par le chiffre 7. Ce sont ces valeurs que nous devons déceler dans le tableau des heures.

• Sélectionner l'intégralité des données du tableau, soit la plage de cellules B4:D34,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Y saisir la syntaxe suivante : =Ou(JourSem($B4)=1; JourSem($B4)=7),

Un jour de Week-End peut en effet prendre deux valeurs. C'est la raison pour laquelle nous incluons les deux tests dans la fonction Ou. Le raisonnement d'une mise en forme conditionnelle est chronologique. Toutes les cellules sélectionnées sont susceptibles d'être analysées. Pour respecter cette chronologie, nous débutons l'étude à partir de la première date, soit de la cellule B4. Pour chaque enregistrement, la date est nécessairement placée en colonne B. Donc nous figeons cette dernière ($B4) et libérons la ligne pour qu'elles soient toutes passées en revue.

Lorsqu'un jour de Week-End est ainsi décelé, nous devons faire réagir la ligne de l'enregistrement. Nous proposons de changer sa couleur de fond.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un bleu clair par exemple,
• Puis dans l'onglet Police, choisir un bleu foncé pour le texte,
• Valider ces attributs de format par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle résume et illustre le contexte. Chaque enregistrement d'un jour de Week-End devra se parer d'un fond bleu.

• Cliquer de nouveau sur Ok pour valider la création de la règle de mise en forme,

Comme vous pouvez le voir, les Samedis et Dimanches sont désormais automatiquement et distinctement repérés.

Calcul du cumul des heures travaillées
Nous devons tout d'abord nous concentrer sur le calcul le moins contraint. Il concerne le petit tableau tout à fait à droite, situé entre les colonnes J et K. Il s'agit de fournir le cumul des heures travaillées pour les jours ouvrés. Donc, les jours de Week-End doivent être exclus du décompte.



Comme vous le savez, la fonction SommeProd, dans sa version classique, permet de multiplier les lignes respectives de plusieurs matrices et d'additionner les résultats à l'issue. Mais en guise de matrice, nous allons lui passer des matrices conditionnelles, soit des colonnes sur lesquelles des critères doivent être posés. Pour ce premier calcul, une seule matrice est concernée par les contraintes. Il s'agit de la colonne des dates. Sur cette dernière, nous devons vérifier deux conditions. Le jour de semaine ne doit correspondre ni au numéro 1, ni au numéro 7. A chaque fois que les conditions croisées seront satisfaites, la fonction SommeProd répondra par le chiffre 1 en regard de chaque enregistrement concordant. Nous n'aurons plus qu'à multiplier ces résultats par la matrice des heures pour obtenir le cumul souhaité. A ce titre, en déployant la liste de la zone Nom, en haut à gauche de la feuille Excel, vous constatez que chaque colonne est nommée en fonction de son titre. Nous allons exploiter ces noms pour simplifier la construction des formules matricielles.

• Sélectionner la cellule J7 et taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir une nouvelle parenthèse pour accueillir la première matrice conditionnelle,
• Taper la fonction donnant le jour de semaine, suivie d'une parenthèse, soit : JourSem(,
• Désigner la colonne des dates par son nom, soit : Date,
• Fermer la parenthèse de la fonction JourSem,
• Puis, taper l'inégalité suivante : <>1,

De cette manière, nous excluons les Dimanches du décompte.

• Fermer la parenthèse de la matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la nouvelle contrainte à recouper,
• Ouvrir une nouvelle parenthèse pour accueillir la seconde matrice conditionnelle,
• Taper de nouveau la fonction pour le jour de semaine, suivie d'une parenthèse, soit : JourSem(,
• Désigner encore la colonne des dates par son nom, soit : Date,
• Fermer la parenthèse de la fonction JourSem,
• Taper l'inégalité suivante : <>7,

De la sorte, nous excluons les Samedis du décompte.

• Fermer la parenthèse de cette seconde matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la matrice à multiplier,
• Désigner la colonne des heures travaillées par son nom, soit : Heures,
• Fermer la parenthèse de la fonction SommeProd,
• Puis, valider la formule par la touche Entrée du clavier,

Le résultat obtenu est tout à fait surprenant. Au vu de la source de données, il n'est pas possible que le cumul des heures de travail conduise à un résultat si faible.



Tout est une question de format en réalité. Par défaut, à chaque passage de la boucle des 24 heures, Excel réinitialise le compteur. Nous devons lui indiquer de poursuivre le cumul. C'est une des notions que nous avions apprises au travers de la formation Excel sur les heures et les dates.

• Sélectionner de nouveau le résultat, soit la cellule J7,
• Déployer la liste déroulante des formats dans la section Nombre du ruban Accueil,
• Tout en bas de la liste, choisir Autres formats numériques,
• Dans la boîte de dialogue qui suit, sélectionner la catégorie Personnalisée,
• Dans la zone Type, remplacer le format existant par le suivant : [h]:mm,

Ce sont les crochets encadrant la lettre pour les heures qui indiquent à Excel de poursuivre le cumul.

• Valider ce format en cliquant sur le bouton Ok de la boîte de dialogue,

De retour sur la feuille le résultat est beaucoup plus cohérent. Il conduit à total de 221 heures travaillées. C'est sur cette base que nous allons pouvoir poursuivre les calculs. Mais avant cela et par anticipation, il est opportun de répliquer ce format sur les cellules totalisant les heures de travail.

• Sélectionner de nouveau la cellule J7,
• Dans la section Presse-papiers, à gauche dans le ruban Accueil, double cliquer sur le pinceau,

Il s'agit de l'outil reproduire la mise en forme. Le double clic permet une réplication en cascade des attributs de format.

• Sélectionner la plage de cellules F10:H10,
• Puis, sélectionner la plage de cellules F15:H15,

Comme ces cellules sont vides de contenu, rien ne se produit a priori. Mais les formats ont bien été appliqués. Et nous le constaterons à l'occasion des prochains calculs.

• Enfoncer la touche Echap pour abandonner l'outil Reproduire la mise en forme,

En cellule K7 suivante, nous devons désormais déduire le coût de ces heures travaillées sur la base du taux fixé en cellule F6. Certes, il ne tient pas compte des heures supplémentaires qui devraient être majorées. Mais qu'à cela ne tienne, le résultat livrera un ordre de grandeur riche d'enseignements. N'oublions pas néanmoins que les heures doivent préalablement être converties en valeurs décimales pour honorer le calcul. C'est la division par 1:00 qui force cette conversion.

• En cellule K7, taper et valider la formule suivante : =J7/'1:00'*F6,

Les guillemets ne doivent pas être omis pour réaliser la conversion par la division.

Additionner les heures par service
Notre attention se tourne désormais vers le premier des deux petits tableaux, situé entre les colonnes F et H. La logique est la même que pour le cumul précédent des heures travaillées. Mais une contrainte s'additionne. Il s'agit d'établir la répartition des heures travaillées par poste. Il suffit de répliquer la formule précédente et de recouper les critères avec une nouvelle matrice conditionnelle sur la colonne des postes.

• En répliquant le précédant calcul, construire la syntaxe suivante en cellule F10 :

=SOMMEPROD((JOURSEM(Date) <> 1)*(JOURSEM(Date) <> 7)*Heures*(Postes = F9))

• Puis, tirer la poignée du résultat sur les deux colonnes situées sur sa droite,

Les résultats obtenus sont parfaitement cohérents. En effet, la somme des trois cumuls par service conduit bien au cumul global calculé en cellule J7.



Le coût du travail doit être livré sur la ligne du dessous. Le résultat sera erroné pour les mêmes raisons que précédemment. Nous ne tenons pas compte de la majoration pour les heures supplémentaires. Mais comme le dernier tableau propose précisément de les isoler, la différence entre les deux calculs livrera le coût pour les horaires normaux.

• En cellule F11, construire et valider la formule suivante : =F10/'1:00'*$F$6,

Il est bien sûr nécessaire de figer la cellule du taux (F6) afin de permettre la réplication de la logique pour les autres services.

Comptabiliser les heures supplémentaires
Désormais en ligne 15, nous souhaitons livrer le cumul des heures supplémentaires par service. Les heures supplémentaires sont déclenchées au-delà de la huitième. C'est ce que mentionne l'indication de référence en cellule G6. Il s'agit une fois encore de décliner la syntaxe précédente.

• En cellule F15, adapter le calcul précédent comme suit :

=SOMMEPROD((JOURSEM(Date) <> 1)*(JOURSEM(Date) <> 7)*(Heures > $G$6)*(Heures - $G$6)*(Postes = F9))

Un critère est émis sur la matrice des heures (Heures>$G$6). Seuls les enregistrements pour lesquels des heures supplémentaires sont constatées, doivent être considérés. Et pour chacun, nous ne retenons que ces heures supplémentaires en réalisant la différence avec la valeur de référence (Heures-$G$6). Il convient ensuite de répliquer la formule sur les deux colonnes de droite pour les deux autres services. Le coût de ces heures supplémentaires doit tenir compte de la majoration indiquée en cellule H6. Une majoration de 25% revient à multiplier le coût horaire initial par (1+0,25) soit par 1,25.

• En cellule F16, construire et répliquer le calcul suivant : =(F15/'1:00')*$F$6*(1+$H$6),

Nous avons ainsi clairement identifié et évalué les heures supplémentaires, selon de multiples conditions croisées, grâce aux calculs matriciels. Désormais et comme nous le disions, nous pourrions réajuster le précédent calcul pour en déduire les heures normales. Il suffit de réaliser la différence entre le total de toutes les heures et celui des heures supplémentaires. Par déclinaison, nous connaîtrions ainsi leur coût. Additionné à celui des heures supplémentaires, nous livrerions le coût total du travail sur la période.