Compter les congés et absences des salariés

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


Sujets que vous pourriez aussi aimer :


Absences et congés des salariés

Synthétiser efficacement les jours de congés et nombres d'absences des salariés est l'objectif de ce cas pratique.



Dans l'exemple illustré par la capture, les dates des congés sont repérées par la lettre C sur chaque ligne du salarié concerné. De la même façon, les absences sont marquées par la lettre A. Pour simplifier l'inscription, le responsable désigne une période globale chevauchant potentiellement les jours de Week-End et les jours fériés. Sur la droite, en bout de tableau, notre calcul matriciel doit être capable de faire le tri pour dénombrer précisément les jours de congés et d'absences hors Week-End et jours fériés.

Source et présentation du concept
Pour réaliser ces travaux, nous devons récupérer un classeur offrant une ébauche avancée de ce planning automatisé.

• Télécharger le classeur synthese-conges.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Comme vous pouvez le voir, le calendrier existe déjà et il est parfaitement fonctionnel. Si vous changez d'année et/ou de mois à l'aide des listes déroulantes en C3 et F3, il se recompose automatiquement. Les Week-End et jours fériés sont dynamiquement repérés par des règles de mise en forme conditionnelle. Ces jours fériés sont calculés dynamiquement en colonne AK en fonction de l'année choisie et selon une technique que nous avons démontrée dans une formation précédente.

Fort logiquement, l'énumération des jours se stoppe à la fin du mois désigné. Et c'est encore une fois une règle de mise en forme conditionnelle qui agit. Elle sert de leurre afin d'hachurer le ou les jours en trop. Et ce sont aussi de nouvelles règles qui permettent de faire ressortir dynamiquement les périodes de congés et d'absences, lorsqu'elles sont explicitement marquées des lettres C et A dans les cases des dates correspondantes.

• Sélectionner l'une des cellules du calendrier, par exemple C6,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, cliquer sur la commande Gérer les règles,

Nous affichons ainsi le gestionnaire qui résume les règles en vigueur sur les cellules du calendrier. Leur hiérarchie est très importante. Celles qui sont situées en tête de liste prennent le pas sur celles qui sont situées en queue de liste. C'est ainsi et par exemple que les hachures prédominent sur les autres critères lorsqu'ils sont recoupés, pour neutraliser un jour n'appartenant plus au mois.



En gris, nous réalisons l'alternance de couleurs une ligne sur deux : =MOD(LIGNE();2)=1. En vert, nous marquons les congés : =C6='C'. En rouge-orange, nous marquons les absences : =C6='A'. En bleu, nous repérons les jours de Week-End, soit les jours pour lesquels le numéro est nécessairement supérieur à 5 : =JOURSEM(C$5;2)>5. Comme cette règle est empilée au-dessus, elle prend le pas sur les congés et les absences. Il en va de même pour la suivante en gris foncé. Elle repère les jours fériés : =NB.SI($AK$7:$AK$17;C$5)>0. Dans la plage de ces derniers, elle compte la présence éventuelle de chaque date du calendrier grâce à la fonction Excel Nb.Si. Enfin, les hachures permettent de stopper artificiellement l'énumération des jours du mois, lorsque la date déborde sur le mois suivant : =MOIS(C$5)<>MOIS($C$5).

• Cliquer sur le bouton Fermer pour revenir sur la feuille Excel,

Ces règles régissent l'aspect visuel mais pas la comptabilité à honorer par salarié en colonnes AH et AI.

Compter les congés et absences
Comme nous le disions, pour simplifier le processus d'inscription, l'utilisateur est autorisé à sélectionner l'intégralité de la plage, de la date de début des congés ou absences jusqu'à la date de fin. Dès lors, il n'a plus qu'à inscrire la lettre C ou la lettre A et à valider par CTRL + Entré pour répliquer la logique sur l'ensemble de la plage. Dans ces conditions, il y a de forts risques que ces mentions chevauchent des jours de Week-End voire des jours fériés. Or, ces derniers ne doivent pas être considérés dans le dénombrement à livrer. Notre calcul doit donc être en mesure de compter ces mentions tout en vérifiant que la date correspondante n'est pas un jour non travaillé.

Pour atteindre cet objectif, nous suggérons d'exploiter la fonction Excel SommeProd dans un raisonnement matriciel. L'idée consiste à lui passer des matrices conditionnelles à recouper pour vérifier l'ensemble des conditions attendues.

• Sélectionner la cellule AH6 pour les congés du premier salarié,
• Taper le symbole égal (=) pour initier la syntaxe de 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,
• Sélectionner la ligne du premier salarié, soit la plage de cellules C6:AG6,
• Enfoncer trois fois la touche F4 du clavier, ce qui donne : $C6:$AG6,

De cette manière, nous figeons cette plage en colonne et la libérons en ligne. En effet, ce calcul est destiné à être répliqué sur les lignes du dessous pour les autres salariés, mais aussi sur la colonne de droite pour les absences. Pour chaque salarié, c'est bien sa propre ligne qui doit être considérée. Elle doit donc suivre le déplacement de la formule répliquée. Pour les absences en revanche, l'analyse porte bien sur la même plage dont les bornes ne doivent pas changer.

• Taper le symbole égal (=) pour annoncer le critère à honorer,

Il s'agit de vérifier la présence de la lettre C. Cette lettre est aussi la première lettre du titre (Congés) de la colonne AH. Nous pouvons la prélever à l'aide de la fonction Gauche. Ainsi, en répliquant ce calcul sur la colonne de droite, nous prélèverons la première lettre de son titre pour compter les Absences reconnues par ce premier caractère.

• Inscrire la fonction Gauche suivie d'une parenthèse, soit : Gauche(,
• Désigner le titre de la colonne en cliquant sur sa cellule AH5,
• Enfoncer deux fois la touche F4 du clavier, ce qui donne : AH$5,

Ainsi libérée en colonne, elle servira à pointer sur les absences lors de la réplication à l'horizontale. Concernant la réplication à la verticale en revanche, pour chaque salarié, la lettre doit être prélevée dans cette cellule qui ne doit pas bouger en ligne.

• Taper un point-virgule (;) pour passer dans l'argument de la longueur à prélever,
• Saisir le chiffre 1 pour ne ponctionner que la première lettre de ce mot, soit C,
• Fermer la parenthèse de la fonction Gauche,
• Puis, fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la condition à recouper,
• Ouvrir une parenthèse pour accueillir la nouvelle matrice conditionnelle,

Nous devons maintenant nous assurer que chaque case marquée de la lettre C n'est pas un jour de Week-End.

• Inscrire la fonction pour le numéro de semaine, suivie d'une parenthèse, soit : JourSem(,
• Désigner l'ensemble des dates du calendrier par sa ligne de titre, soit la plage C5:AG5,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $C$5:$AG$5,

En effet, quel que soit le salarié, la vérification s'opère nécessairement sur cette ligne bornée. Elle ne doit donc pas bouger, ni en ligne ni en colonne.

• Taper un point-virgule suivi du chiffre 2, soit : ;2,

De cette manière, nous raisonnons sur le calendrier français. Le Samedi et le Dimanche sont respectivement les sixième et septième jours de la semaine. Par défaut, c'est en effet le calendrier américain qui prime. Et dans ce contexte, le Dimanche est considéré comme le premier jour de la semaine.

• Fermer la parenthèse de la fonction JourSem,
• Puis, taper l'inégalité suivante : <6,

Donc, en même temps qu'un jour de congés est identifié, nous cherchons à vérifier que son jour de semaine n'est pas situé au-delà du cinquième. En d'autres termes, nous nous assurons qu'il ne s'agit pas d'un jour de Week-End.

• Fermer la parenthèse de cette deuxième matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer le troisième critère à recouper,

Nous l'avons dit, un jour de congés ne doit pas être cumulé ni avec un jour de Week-End, ni avec un jour férié. Et ce dernier cas n'est pas encore vérifié.

• Ouvrir une dernière parenthèse pour accueillir la troisième matrice conditionnelle,
• Inscrire la fonction de dénombrement conditionnel, suivie d'une parenthèse, soit : Nb.Si(,
• Désigner tous les jours fériés, soit la plage de cellules AK7:AK17,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $AK$7:$AK$17,

Quel que soit le calcul, la date doit toujours être cherchée dans cette plage inamovible. Elle ne doit donc pas bouger.

• Taper un point-virgule (;) pour passer dans l'argument du critère de la fonction Nb.Si,

Dans ce raisonnement matriciel, en guise de critère, c'est toute la matrice des dates que nous allons lui passer. Ainsi, chaque date sera tour à tour confrontée avec chaque jour férié.

• Sélectionner toutes les dates, soit la plage de cellules C5:AG5,
• Comme précédemment enfoncer la touche F4 pour la figer, ce qui donne : $C$5:$AG$5,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, taper le critère suivant : =0,

S'il est vérifié, il confirme que la date repérée comme un congé et non identifiée comme un Week-End, n'est pas non plus un jour férié.

• Fermer la parenthèse de cette troisième matrice conditionnelle,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

De cette manière, nous conservons active la case du résultat pour l'exploiter dans la foulée. Et précisément, ce premier résultat est parfaitement cohérent. Il confirme que ce premier salarié n'a encore posé aucun congé sur le mois désigné.

• Cliquer et glisser la poignée de la cellule sur la droite pour les absences,
• Avec la balise active en bas du calcul, choisir Recopier les valeurs sans la mise en forme,

Un format conditionnel différent régit en effet l'apparence des cellules dans ces deux colonnes. Il ne s'agit donc pas d'écraser le second par le premier.

• Puis, double cliquer sur la poignée de la sélection pour répliquer la logique sur la hauteur,

Après une rapide vérification, vous pouvez constater que tous les calculs sont parfaitement cohérents. Toutes les absences et tous les congés sont bien consolidés sans cumuler ni les jours de Week-End, ni les jours fériés lorsqu'ils se chevauchent.

• Pour le dernier salarié, sélectionner les douze dernières cases, soit la plage U17:AF17,

Dans cette sélection, nous incluons donc deux jours de Week-End.

• Inscrire la lettre C et valider par le raccourci clavier CTRL + Entrée,

De cette manière, nous répliquons l'inscription de la lettre sur l'ensemble des cellules présélectionnées. Et malgré la sélection des 12 cases, notre calcul nous confirme que le salarié n'a bien posé que 10 jours en raison du Week-End intercalé.