Sommer les heures travaillées par semaine 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 :


Heures travaillées par semaine

Avec cette nouvelle astuce Excel, nous allons voir comment il est possible d'obtenir le cumul des heures travaillées chaque semaine, pour tous les salariés et pour toutes les semaines de l'année et ce, à l'aide d'une seule formule à répliquer.



Dans l'exemple illustré par la capture, les heures réalisées pour chaque salarié et pour chaque jour de l'année, sont archivées dans un premier tableau. C'est dans un second tableau situé sur la droite que nous devons fournir le cumul de ces heures pour chaque semaine consolidée. Les heures sont ici inscrites en version décimale. Mais nous y reviendrons, le principe serait identique, à un format de cellule près, pour consolider des heures au format heure.

Classeur source
Pour la mise en place de cette nouvelle astuce, nous suggérons d'appuyer l'étude sur un classeur offrant déjà ce tableau des heures effectuées.

• Télécharger le classeur cumul-heures-travaillees.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 donc le tableau des heures travaillées pour quatre salariés et pour les deux premiers mois de l'année, soit jusqu'au Vendredi 26 Février 2021 en ligne 43. Naturellement, le principe que nous allons mettre en place vaut aussi pour une année complète. Nous l'avons dit, ces heures sont présentées au format décimal. De plus, elles sont toutes rondes. Là encore, qu'il s'agisse de véritables heures et que certaines soient fractionnées ou non, ne change rien au problème.

Sur la droite, vous notez la présence d'un deuxième tableau, vide pour l'instant. Pour chaque numéro de semaine mentionné en première colonne et pour chaque salarié rappelé en première ligne, nous devons calculer le cumul des heures travaillées. Et comme nous l'avons déjà évoqué, pour cela, nous devons construire une formule unique à répliquer sur l'ensemble des cellules. Enfin, une mise en forme conditionnelle est déjà en place sur ce second tableau. Son rôle est de surligner automatiquement en couleur les dépassements, soit les heures supplémentaires.

Positions des débuts de semaine
La formule doit être construite à partir de la première case vide du tableau, soit en cellule I4. Elle doit premièrement être répliquée sur la droite pour les quatre salariés. Puis, elle doit être répliquée sur les lignes du dessous pour le cumul de chaque semaine. La somme des heures doit être réalisée sur une hauteur de cinq lignes pour les cinq jours de la semaine. Mais c'est le point de départ de cette somme qui reste indéterminé. Pour chaque cumul suivant, l'addition doit débuter cinq lignes plus bas.

Le premier jour de semaine est placé en ligne 4. Les suivants sont situés en ligne 9, 14, 19 etc... Mais en répliquant la formule, nous ne progresserons que d'une ligne à chaque fois. Nous avons donc besoin d'une astuce pour déplacer le pointeur cinq cases plus bas à chaque changement de ligne.

Cette astuce est simple. Comme le premier jour débute en ligne 4, nous devons multiplier le numéro de semaine par 5, pour les cinq jours de la semaine. Puis, nous devons lui retrancher une unité pour exclure le point de départ inclus dans le calcul : (H4*5)-1. Le premier résultat fournira la ligne (1*5)-1, soit 4, le deuxième fournira la ligne : (2*5)-1, soit 9 puis 14 etc...Si l'énumération avait par exemple débuté en ligne 2, nous aurions retranché 3 unités : (H2*5)-3.

Dans un premier temps et pour y voir plus clair, sur la droite du second tableau, nous proposons de repérer les coordonnées des cellules des premiers jours de semaine pour chaque salarié. A l'issue, nous les consoliderons avec la formule finale pour livrer les résultats sans étape intermédiaire. Comme vous le savez, c'est la fonction Excel Adresse qui permet de recomposer dynamiquement les coordonnées des cellules.

• Sélectionner la cellule M4 à droite du second tableau,
• Taper le symbole égal (=) pour initier la syntaxe du calcul,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : Adresse(,

En premier argument, nous devons lui indiquer l'indice de ligne de la cellule pour laquelle il s'agit de reconstruire les coordonnées. C'est cet indice qui varie selon la règle que nous avons démontrée précédemment.

• Sélectionner le premier numéro de semaine en cliquant sur sa cellule H4,
• Puis, enfoncer trois fois la touche F4 du clavier pour la figer seulement en colonne, soit : $H4,

Les coordonnées doivent être calculées sur les lignes du dessous en fonction du numéro de semaine qui évolue. Donc, la ligne de la cellule doit être libre de se déplacer avec la formule répliquée. Sur les colonnes placées à droite en revanche, pour chaque salarié, cette information sur le numéro de semaine doit inévitablement être prélevée en colonne H. Elle ne doit donc pas se déplacer d'où la raison de la présence du dollar devant l'indice H de la colonne.

• Multiplier le numéro de semaine prélevé par 5, soit : *5,
• Puis, soustraire une unité à ce résultat, soit : -1,
• Taper alors un point-virgule (;) pour passer dans l'argument de l'indice de colonne,

Cette colonne varie en fonction du salarié. La première est la colonne C, soit à l'indice 3. C'est tout simplement la fonction Excel Colonne qui retourne ces positions.

• Inscrire le nom de la fonction suivi d'une parenthèse, soit : Colonne(,
• Désigner une cellule de la colonne C en cliquant par exemple sur C2,

De cette manière, en répliquant la formule sur la droite, la fonction pointera naturellement sur les colonnes D, E puis F pour retourner les indices 4, 5 et 6.

• Fermer la parenthèse de la fonction Colonne,
• Taper un point-virgule (;) pour passer dans le dernier argument de la fonction Adresse,
• Inscrire le chiffre 4 pour obtenir des coordonnées relatives, sans les dollars donc,
• Fermer la parenthèse de la fonction Adresse,
• Enfin, valider la formule avec le raccourci clavier CTRL + Entrée,

Ainsi nous conservons active la cellule du calcul pour l'exploiter dans l'enchaînement. Le premier résultat tombe et il est parfaitement cohérent. Il indique que le total d'heures du premier Lundi pour le premier salarié est placé en cellule C4. Et c'est bien le cas.

• Cliquer et glisser la poignée du résultat sur les trois colonnes à droite, soit jusqu'en P4,
• Puis, cliquer et glisser la poignée de la sélection en dessous, jusqu'en ligne 11,

Comme vous pouvez l'apprécier, nous obtenons bien les coordonnées respectives des premiers jours de semaine pour chaque salarié. Chaque cellule est effectivement séparée de la suivante par cinq cases. La syntaxe complète de la formule que nous avons bâtie pour ce repérage est relativement simple : =ADRESSE($H4*5-1; COLONNE(C2); 4).

Cumul des heures par semaine
Maintenant, il est question d'exploiter ce premier résultat pour consolider les heures travaillées pour chaque semaine du calendrier. Pour réaliser les additions, nous connaissons désormais les points de départ de chaque plage. De plus, nous savons aussi que chaque plage s'étend sur une hauteur de cinq cellules, pour les cinq jours de la semaine.

• Sélectionner le premier cumul à consolider en cliquant sur sa cellule I4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,

Pour que le premier point de départ précédemment calculé soit considéré comme une cellule et non comme un texte, nous devons exploiter la fonction Excel d'interprétation.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner le premier point de départ en cliquant sur sa cellule M4,
• Puis, fermer la parenthèse de la fonction Indirect,

De cette manière, nous venons d'indiquer dynamiquement à la somme que l'opération doit débuter à partir de la cellule C4. A partir de cette cellule, nous devons la faire agir sur les quatre cellules du dessous. Pour cela, nous allons exploiter la fonction Excel Decaler sur ce point de référence.

• Taper le symbole deux points (:) pour annoncer la borne inférieure de la plage,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,

Nous sommes dans l'argument du point de référence ou point de départ de cette fonction. Il s'agit toujours du premier jour de la semaine pour le salarié désigné. Les coordonnées doivent de nouveau être interprétées.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Cliquer de nouveau sur la cellule M4 pour la désigner,
• Fermer la parenthèse de la fonction Indirect,
• Puis, taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,
• Inscrire le chiffre 4 pour ajouter les quatre cellules du dessous à celle de départ,
• Taper un nouveau point-virgule (;) pour passer dans l'argument du décalage en colonne,
• Dès lors, inscrire le chiffre 0,

En effet, nous sommes déjà placés dans la bonne colonne. Il n'est pas question d'en changer.

• Fermer la parenthèse de la fonction Decaler,
• Puis, fermer la parenthèse de la fonction Somme,
• Enfin, valider le calcul par le raccourci clavier CTRL + Entrée,

Le premier résultat tombe. Il affiche un cumul de 30 heures. Sa cohérence est très simple à valider. Il suffit de sélectionner les heures des cinq premiers jours pour le premier salarié et de consulter l'information sur la somme de la sélection, donnée dans la barre d'état, en bas à droite de la fenêtre Excel. La consolidation est effectivement la même.

• Tirer la poignée du résultat sur la droite jusqu'en cellule L4,
• Puis, tirer la poignée de la sélection vers le bas jusqu'en ligne 11,

Tous les cumuls sont parfaitement consolidés pour chaque semaine et pour chaque salarié. Et comme nous l'annoncions, vous voyez surgir des effets de couleur lorsque le total des heures dépasse les 39 heures par semaine. Il est ainsi beaucoup plus simple de repérer les heures supplémentaires à payer. Comme précédemment, il est très simple de vérifier rapidement la cohérence de ces cumuls, en sélectionnant les groupes d'heures, pour le salarié concerné et en consultant l'indication de synthèse fournie par la barre d'état.

La syntaxe de la formule que nous avons bâtie pour la consolidation des heures travaillées par semaine est la suivante : =SOMME(INDIRECT(M4):DECALER(INDIRECT(M4); 4; 0)).

Et pour qu'une seule formule soit capable, sans les résultats intermédiaires, de livrer tous ces cumuls, il suffit de remplacer à deux reprises la cellule M4 par la syntaxe du calcul de repérage :

=SOMME(INDIRECT(ADRESSE($H4*5-1; COLONNE(C2); 4)):DECALER(INDIRECT(ADRESSE($H4*5-1; COLONNE(C2); 4)); 4; 0))

Dès lors, les calculs intermédiaires de repérage peuvent être supprimés. Bien entendu, elle est tout à fait prête pour s'exécuter sur un calendrier qui considère toutes les semaines d'une année complète.



Enfin et comme nous le disions, si nous avions agi sur des données au format heure, nous aurions dû adapter le format des cellules des résultats comme suit : [h]:mm. C'est lui qui permet de prolonger l'addition des heures au-delà de la boucle des 24 heures d'une journée. Et c'est notamment ce que démontre la formation des opérations sur les heures avec Excel.