Calendrier Excel des congés annuels 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 :


Congés annuels des salariés

Dans cette formation Excel, nous allons développer un outil ergonomique permettant de visualiser instantanément les congés payés, les RTT et absences des salariés.



Le modèle finalisé à rejoindre est illustré par la capture ci-dessus. Pour chaque mois de l'année, les jours de semaines sont énumérés. Les jours de Week-End sont démarqués des jours ouvrés par une légère mise en forme bleue statique.

Au choix de l'année, grâce à une liste déroulante sur la droite de la feuille, tous les mois de l'année se reconstruisent en adaptant les jours de chaque semaine. Au choix d'un salarié dans la liste déroulante du dessous, les congés payés, RTT et absences inopinées sont instantanément repérés dans des couleurs fidèles à la légende.

Ainsi, le chef d'équipe ou le responsable de service bénéficie d'une vue claire et synthétisée lui permettant de visualiser rapidement les disponibilités des uns et des autres.

Source et présentation de la problématique
Pour réaliser ces travaux, nous proposons de réceptionner un classeur hébergeant les données des salariés. Mais ce n'est pas tout et vous le constaterez, il offre surtout l'outil permettant de construire automatiquement et dynamiquement les calendriers annuels. Malgré tout et nous allons le voir, il reste du travail à accomplir pour le transformer en gestionnaire de congés.

• Télécharger le classeur calendriers-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é,

Nous débouchons sur la feuille Calendrier de ce classeur. Tous les mois de l'année y sont représentés. Et pour chacun, tous les jours de semaine sont repérés. Vous notez la présence de hachures en début et fin de chaque mois, pour les jours de semaine non concordants. En effet, un mois ne débute pas nécessairement par le Lundi et ne se termine pas nécessairement par le Dimanche.

Sur la droite de cette feuille, vous remarquez la présence de deux zones mises en valeur par un fond bleu. La seconde correspond à la légende. Il s'agit des couleurs à déclencher dynamiquement dans le calendrier annuel pour repérer les absences du salarié, selon la nature de l'indisponibilité. Le jaune doit repérer les congés payés. Le vert doit identifier les jours de RTT et le orange doit mettre en valeur les absences inopinées.

La première de ces deux zones correspond à la barre d'outils permettant d'obtenir le rendu dynamique synthétisé pour le salarié désigné. C'est pourquoi, deux listes déroulantes se suggèrent automatiquement en cellules BD8 et BD11.

• En cellule BD8, choisir par exemple l'année 2020 à la place de l'année 2019,

En scrutant la représentation des mois de l'année, vous notez que les jours de semaine ont parfaitement été réorganisés pour respecter le calendrier.

Formule de construction du calendrier annuel
Même si elle existe déjà et qu'elle est offerte, nous proposons de découvrir la formule du calendrier pour l'expliquer. Cette partie n'est pas essentielle à la conception finale de l'outil pour repérer les absences des salariés. Vous pouvez donc la sauter à votre guise.

• Cliquer sur la première case du premier mois de l'année, soit la cellule B7,

En consultant la barre de formule, vous notez la présence d'une syntaxe relativement complexe :

=SIERREUR(SI(ET(A7=''; B3=B2;ESTNUM(H6)=FAUX; ESTNUM(B6)=FAUX;B6<>''); DATE($BD$8;B1;1); SI(ET(A7<>'';MOIS(A7+1) = MOIS(A7));A7+1; SI(ET(H6<>''; MOIS(H6+1)=MOIS(H6)); H6+1;'')));'')

Il est à savoir que cette construction peut aussi s'opérer par un calcul matriciel unique. Mais malheureusement, ce dernier n'offre pas une syntaxe plus légère. De plus, sa compréhension est plus complexe à appréhender.

Ce calcul fait référence à des cellules masquées, notamment sur les lignes 1, 2 et 3. En les affichant, nous constatons l'inscription de repères numériques.

La première ligne répète le numéro du mois. La deuxième ligne isole et répète le jour de semaine du premier jour du mois, pour l'année mentionnée :

=JOURSEM(DATE($BD$8;B1;1);2)

La troisième ligne consiste en une incrémentation des numéros représentant les sept jours de la semaine.

Dans la syntaxe de la formule, vous notez l'imbrication de trois fonctions conditionnelles. La première, selon de multiples conditions, consiste à débuter l'énumération en débusquant l'emplacement dans la semaine du premier jour du mois :

=SIERREUR(SI(ET(A7=''; B3=B2;ESTNUM(H6)=FAUX; ESTNUM(B6)=FAUX; B6<>''); DATE($BD$8;B1;1)...

Pour cela, il faut que la cellule sur sa gauche soit vide (A7=''). Dans le même temps, nous devons vérifier la correspondance entre le jour en cours d'analyse et le jour de semaine pour le premier jour du mois (B3=B2). La cellule à l'extrémité droite du mois, pour la ligne du dessus, ne doit pas être numérique (ESTNUM(H6)=FAUX). Ce critère certifie que nous sommes bien situés sur la ligne de la première semaine du mois. Et enfin, la cellule du dessus ne doit pas être vide (B6<>''). C'est grâce à ce dernier critère et au premier, que l'énumération ne repartira pas du début après avoir reconstruit tous les jours du mois.

Cette formule est ensuite répliquée sur les lignes du dessous et les colonnes placées à droite. Pour que les données des autres mois n'interfèrent pas, nous avons donc ajouté des colonnes vides et masquées.



Elles sont au nombre de 7, soit la place nécessaire pour la réplication à droite de la formule. Par exemple, la cellule H6 impliquée dans le premier multicritère de ce calcul, va se déplacer jusqu'en O6.

La deuxième fonction Si se déclenche donc nécessairement lorsque les critères recoupés de la première ne sont pas vérifiés :

SI(ET(A7<>'';MOIS(A7+1)=MOIS(A7));A7+1;...

Sur une même ligne, elle considère que la date précédente a été créée. Donc, dans la mesure où le mois de la date à suivre est toujours le même (MOIS(A7+1)=MOIS(A7)), nous incrémentons la précédente d'une journée (A7+1).

La troisième fonction Si se déclenche nécessairement lorsque les critères des deux premières ont échoué :

SI(ET(H6<>'';MOIS(H6+1)=MOIS(H6));H6+1;''

Elle est utile au changement de ligne pour poursuivre l'énumération des dates pour les semaines qui suivent. Elle analyse le contenu de la cellule à l'extrémité droite du mois sur la ligne du dessus. Si son contenu existe et que le mois de la date incrémentée est toujours le même (MOIS(H6+1)=MOIS(H6)), alors nous ajoutons une journée à la précédente date.

Repérer les congés du salarié
Pour construire l'outil permettant de visualiser l'ensemble des indisponibilités d'un salarié sur l'ensemble de l'année, des opérations intermédiaires sont nécessaires. Dans la feuille Conges, le premier calcul consiste à repérer par des numéros incrémentés, toutes les lignes pour lesquelles l'année et le salarié correspondent.

Il s'agit d'une technique que nous maîtrisons bien désormais. L'incrémentation naturelle se réalise grâce à la fonction Max sur une plage de cellules qui grandit en même temps que le calcul est répliqué.

• En bas de la fenêtre Excel, cliquer sur l'onglet Conges pour activer sa feuille,
• Sélectionner la cellule E3 et taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Taper la fonction pour énumérer les critères suivie d'une parenthèse, soit : Et(,

Les conditions à remplir consistent à valider la correspondance avec la feuille Calendrier sur l'année et le nom du salarié. Dans la base de données cependant, c'est la date complète qui est mentionnée fort logiquement pour chaque absence. Nous devons donc exploiter la fonction Excel Annee pour extraire la donnée à comparer.

• Taper le nom de la fonction suivi d'une parenthèse, soit : Annee(,
• Sélectionner la première date de la base de données, soit la cellule C3,
• Fermer la parenthèse de la fonction Annee,
• Puis, taper le symbole égal (=) pour la condition à vérifier,
• En bas de la fenêtre Excel, cliquer sur l'onglet Calendrier pour activer sa feuille,
• Sélectionner la date choisie par la liste déroulante, soit la cellule BD8,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : Calendrier!$BD$8,

En effet, chaque date de la base de données doit être comparée avec la cellule de l'année spécifiée. Donc, lorsque nous répliquerons la logique du calcul sur les lignes du dessous, cette dernière ne doit pas suivre le déplacement.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• En bas de la fenêtre Excel, cliquer sur l'onglet Conges pour revenir sur sa feuille,
• Désigner alors le premier salarié, soit la cellule B3 : Conges!B3,

Ici, le nom de la feuille Conges en préfixe n'est pas utile à rappeler. La formule appartient en effet à cette feuille. Sa désignation est donc implicite. Excel nous l'impose dès lors que nous pointons sur une autre feuille dans un calcul. Vous pouvez donc supprimer ce préfixe. Vous pouvez le conserver, mais il alourdit la syntaxe.

• Taper le symbole égal (=) pour la seconde condition à vérifier sur le nom du salarié,
• En bas de la fenêtre Excel, cliquer sur l'onglet Calendrier pour activer sa feuille,
• Puis, désigner le salarié choisi par la seconde liste déroulante, soit la cellule BD11,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : Calendrier!$BD$11,
• Fermer la parenthèse de la fonction Et,
• Puis, taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction donnant la plus grande valeur d'une plage, suivie d'une parenthèse, soit Max(,
• En bas de la fenêtre Excel, cliquer sur l'onglet Conges pour revenir sur sa feuille,
• Sélectionner la cellule située juste au-dessus du calcul, soit E2,
• Taper le symbole deux point (:) pour générer la plage E2:E2,
• Cliquer sur la première des deux bornes pour y placer le point d'insertion,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $E$2:E2,

Ainsi, nous ne figeons que la borne supérieure du calcul. En même temps que nous répliquerons la formule vers le bas, la plage analysée va grandir avec les lignes passées en revue. De fait, la fonction Max va retourner la plus grande valeur trouvée et incrémentée jusqu'alors. Et précisément, l'incrémentation à produire est l'astuce encore manquante dans cette syntaxe.

• Fermer la parenthèse de la fonction Max,
• Puis ajouter une unité pour l'incrémentation, soit : +1,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour garder la cellule vide en cas de non correspondance,
• Fermer la parenthèse de la fonction Si,
• Valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous gardons active la cellule du résultat pour l'exploiter dans la foulée.

• Double cliquer alors sur la poignée du résultat pour répliquer la logique sur le tableau,

Il est même préférable de reproduire le calcul bien au-delà des bornes de la base de données. En effet, ce tableau des congés est susceptible d'évoluer régulièrement, au gré des périodes posées par les uns et les autres.



En faisant défiler les données du tableau vers le bas, vous notez la présence des numéros parfaitement incrémentés, en regard du salarié concerné et de l'année désignée. La formule de repérage que nous avons construite est la suivante :

=SI(ET(ANNEE(C3) = Calendrier!$BD$8; B3=Calendrier!$BD$11); MAX($E$2:E2)+1;'')

Isoler les congés du salarié
Dans la feuille Extraction, nous proposons d'importer les informations du salarié et de ses dates de congés. Ainsi isolées, elles seront plus faciles à comparer pour la construction des règles de mise en forme conditionnelle. Ce sont elles en effet qui permettront de mettre dynamiquement en valeur les congés du salarié dans le calendrier annuel.

Comme nous en avons l'habitude désormais, nous devons exploiter les fonctions Excel Index, Equiv et Ligne.

=Index(Tableau_de_recherche; Numéro_de_ligne; Numéro_de_colonne)
=Equiv(Valeur_cherchée; Rangée_de_recherche; Mode_de_recherche)
=Ligne(Coordonnées_cellule)

• En bas de la fenêtre Excel, cliquer sur l'onglet Extraction pour afficher sa feuille,
• Sélectionner ensuite la cellule B2 par exemple,
• Taper le symbole égal (=) pour initier la syntaxe de la formule d'extraction,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Taper la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Cliquer sur l'onglet Conges en bas de la fenêtre Excel pour activer sa feuille,
• Puis, cliquer sur l'étiquette de la colonne B pour la désigner intégralement : Conges!B:B,

Nous proposons en effet de réceptionner premièrement l'information sur le nom du salarié. Cette colonne ne doit pas être figée pour permettre de désigner les suivantes, au moment de la réplication du calcul sur la droite.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,

Celui-ci est variable. Il dépend de la position des numéros incrémentés qui eux-mêmes dépendent du choix de l'année et du salarié dans la feuille Calendrier. C'est la fonction Equiv en exploitant la fonction Ligne comme élément à chercher, qui va nous permettre de les retrouver.

• Taper le nom de la fonction suivi d'une parenthèse, soit : Equiv(,
• Saisir la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Sélectionner alors une cellule de la première ligne, par exemple A1,

Pour le premier calcul, l'indice retourné vaudra 1. Mais pour ceux répliqués sur les lignes du dessous, A1 se transformant en A2 puis A3 etc..., les indices retournés progresseront de la même façon que les numéros incrémentés à retrouver.

• Fermer la parenthèse de la fonction Ligne,
• Puis taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Sélectionner l'intégralité de la colonne E par son étiquette, soit : Conges!E:E,

En effet, tous les numéros incrémentés à débusquer sont calculés dans cette colonne. Et cette colonne ne doit pas bouger pour permettre la réplication sur la droite, afin d'extraire les autres données.

• Enfoncer la touche F4 du clavier pour figer cette plage, ce qui donne : Conges!$E:$E,
• Taper un point-virgule (;) suivi du chiffre zéro, soit : ;0, pour effectuer une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,

De fait, nous sommes de retour dans les arguments de la fonction Index. Nous venons de lui transmettre l'indice dynamique de ligne de la donnée à extraire. Il nous reste à préciser l'indice de colonne pour effectuer le croisement. Celui-ci est statique. Il s'agit nécessairement de la première puisque le tableau d'extraction est défini sur une seule rangée.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,
• Puis, saisir le chiffre 1 et fermer la parenthèse de la fonction Index,

De fait, nous sommes de retour dans les arguments de la fonction SiErreur.

• Taper un point-virgule (;) pour passer dans la branche de gestion d'erreur,
• Saisir deux guillemets ('') pour garder la cellule vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Valider le calcul par le raccourci clavier CTRL + Entrée,
• Cliquer et glisser la poignée du résultat à l'horizontale jusqu'en colonne D,

Hormis un défaut de formatage sur la date, le premier enregistrement concordant semble correctement importé. Nous pallierons cette anomalie par la suite.

• Cliquer et glisser la poignée de la sélection à la verticale sur plusieurs dizaines de lignes,
• Sélectionner toute la colonne C en cliquant sur son étiquette,
• Déployer la liste déroulante des formats dans la section Nombre du ruban Accueil,
• Dans la liste, choisir Date courte puis aligner la sélection à droite,

Si vous modifiez le nom du salarié dans la feuille Calendrier, en choisissant par exemple Céhef Hassan, vous remarquez que l'importation s'ajuste instantanément dans la feuille Extraction. La formule d'extraction que nous avons bâtie est la suivante :

=SIERREUR(INDEX(Conges!B:B; EQUIV(LIGNE(A1); Conges!$E:$E;0);1); '')

Assembler les données extraites
Pour simplifier la correspondance des règles de mise en forme conditionnelle, nous suggérons d'assembler dans une même colonne, toutes les informations d'une ligne. Ainsi, plutôt que de vérifier si la date concorde en même temps que le nom du salarié correspond et qu'il s'agit de RTT ou de congés payés, nous vérifierons l'équivalence des chaînes concaténées. La règle assemblera elle-même les informations à retrouver dans cette colonne que nous proposons d'implémenter.

• Sélectionner la cellule E2 de la feuille Extraction,
• Taper le symbole égal (=) pour initier le calcul,
• Sélectionner la première donnée à assembler, soit le nom en cellule B2,
• Inscrire le caractère de concaténation (&) avec la touche 1, en haut à gauche du clavier,

Pour les besoins des comparaisons, il est ensuite préférable de convertir explicitement l'information de date en une chaîne de caractères. Cette conversion est rendue possible grâce à la fonction Excel Texte:

=Texte(Donnée_convertir; Format_à_afficher)

• Saisir la fonction de conversion suivie d'une parenthèse, soit : Texte(,
• Sélectionner la première date, soit la cellule C2,
• Taper un point-virgule (;) pour passer dans l'argument du format,
• Saisir le code suivant : 'jj-mm-aaaa' sans oublier les guillemets,

Il s'agit d'un format classique avec deux chiffres pour le jour, deux pour le mois et quatre pour l'année. En revanche, nous préférons les tirets aux slashs. Le résultat obtenu ressemble bien à une date mais est converti en texte. Nous devrons reproduire cet assemblage strict dans la règle de mise en forme conditionnelle pour obtenir les correspondances.

• Fermer la parenthèse de la fonction Texte,
• Inscrire le caractère de concaténation (&) avec la touche 1, pour poursuivre l'assemblage,
• Puis, sélectionner la première nature de l'indisponibilité, soit la cellule D2,
• Valider le calcul par le raccourci clavier CTRL + Entrée,
• Puis, cliquer et glisser la poignée du résultat sur quelques dizaines de lignes vers le bas,

Une seule cellule résume désormais l'ensemble des informations pour chaque ligne. La formule d'assemblage que nous avons construite est la suivante :

=B2 & TEXTE(C2;'jj-mm-aaaa') & D2

Repérer les absences dans le calendrier annuel
Nous devons maintenant exploiter ces résultats d'extraction concaténés, pour mettre en valeur les cases correspondantes dans le calendrier annuel. Il faut rechercher le nom du salarié désigné, concaténé à la date de chaque cellule du calendrier, concaténée à la nature de l'absence. Concernant cette dernière, la couleur doit varier comme le mentionne la légende de la feuille Calendrier. Trois couleurs de fond différentes sont à appliquer. Ces indicateurs faciliteront la lecture et l'interprétation des indisponibilités. Donc, trois règles de mise en forme conditionnelle sont à créer. Mais elles sont toutes trois quasiment identiques dans la syntaxe. Seule la couleur de fond à enclencher doit être adaptée.

• En bas de la fenêtre Excel, cliquer sur l'onglet Calendrier pour activer sa feuille,
• Sélectionner alors toutes les cases du premier mois, soit la plage de cellules B7:H12,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, 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 située juste en-dessous pour l'activer,
• Taper alors la syntaxe suivante :

=RECHERCHEV($BD$11 & TEXTE(B7;'jj-mm-aaaa') & $BD$22; Extraction!$E:$E;1; FAUX)=$BD$11 & TEXTE(B7;'jj-mm-aaaa') & $BD$22

Nous effectuons une recherche des informations concaténées que nous passons en premier paramètre de la fonction RechercheV. Il s'agit du salarié spécifié avec la première liste déroulante ($BD$11), de la date en cours d'analyse dans le calendrier (TEXTE(B7;'jj-mm-aaaa')). Cette dernière est volontairement défigée pour qu'elles soient toutes parcourues. Enfin, il s'agit de la nature de l'absence ($BD$22), mentionnée dans la légende. Elle est figée tout comme la cellule du salarié. En effet, en même temps que les dates sont parcourues par la règle, ces informations textuelles sont immuablement placées dans leurs cellules respectives.

Nous recherchons cet assemblage dans la colonne E de la feuille Extraction. Il s'agit de la colonne dans laquelle nous avons précisément réalisé le calcul de concaténation. Et cette colonne figée bien entendu, nous la passons en deuxième paramètre de la fonction RechercheV.

En troisième paramètre, par le chiffre 1, nous indiquons à la fonction de retourner l'information située en première colonne. Il n'y en a qu'une en effet, tel que nous avons défini le tableau de recherche sur la colonne E. En dernier paramètre enfin, le booléen demande de réaliser une recherche selon une correspondance exacte. Nous comparons ce résultat issu du tableau de la feuille Extraction avec cette même concaténation que celle passée en premier paramètre. Si la correspondance est avérée, nous en concluons qu'il s'agit bien d'un congé (CP) pour le salarié désigné et la date en cours d'étude. En effet, une mise en forme conditionnelle raisonne chronologiquement sur la plage de cellules sélectionnée.

• 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 orange similaire à la légende,
• Valider ce choix par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle indique que lorsque la correspondance sera trouvée, la couleur choisie sera appliquée.

• Valider cette règle de mise en forme conditionnelle en cliquant sur le bouton Ok,

Fort logiquement, rien ne réagit pour l'instant. Nous avons défini cette règle seulement pour le premier mois. A l'instar d'une mise en forme classique, il convient de répliquer ces attributs de format dynamique par l'outil Reproduire la mise en forme. Les cellules du premier mois doivent toujours être sélectionnées.

• Tout à fait à gauche du ruban Accueil, double cliquer sur le pinceau,
• Puis, sélectionner tour à tour, l'ensemble des jours de chaque mois,
• A l'issue, enfoncer la touche Echap ou recliquer sur le pinceau pour abandonner la commande,

Comme vous le remarquez, les jours de congés du salarié désigné sont désormais automatiquement repérés. De plus, cette mise en valeur est fort heureusement dynamique.

• A l'aide de la seconde liste déroulante sur la droite de la feuille, choisir le salarié Braltar Giles,

Instantanément, la mise en valeur des congés payés s'adapte selon les indisponibilités archivées dans la base de données, pour le salarié choisi.

Deux règles similaires doivent donc être construites pour repérer dynamiquement les jours de récupération et les absences. Bien sûr, les couleurs doivent être adaptées selon les indications de la légende. La méthode la plus simple consiste à répliquer un format existant pour l'adapter.

• Sélectionner toutes les cases du premier mois, soit la plage de cellules B7:H12,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Tout en bas de la liste, cliquer sur la commande Gérer les règles,

Une boîte de dialogue apparaît. Elle résume les règles de mise en forme conditionnelle en vigueur sur cette plage sélectionnée.

• Dans la liste, sélectionner la première, celle des CP avec remplissage orange,
• En haut de la boîte de dialogue, cliquer sur le bouton Modifier la règle,

Une nouvelle boîte de dialogue surgit. Elle résume la construction que nous avions entreprise pour faire ressortir les congés payés.

• Sélectionner et copier (CTRL + C) l'intégralité de la syntaxe puis, cliquer sur le bouton Annuler,

Ainsi, nous n'avons pas altéré la première règle qui doit persister et nous sommes de retour sur la boîte de dialogue de gestion des règles.

• Cliquer alors sur le bouton Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Cliquer dans la zone de saisie située juste en-dessous pour l'activer,
• Coller (CTRL + V) la syntaxe précédemment copiée,
• Dans les deux expressions concaténées, remplacer la cellule $BD$22 par $BD$23,

Dans la légende, il s'agit de la suivante et elle concerne les jours de RTT. Ces derniers doivent être repérés dynamiquement dans le calendrier annuel sur un fond vert.

• Cliquer alors 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 vert assez soutenu,
• Valider ces réglages en cliquant sur le bouton Ok,

De fait, nous sommes de retour sur la première boîte de dialogue. La règle stipule que tous les jours de RTT pour le salarié désigné, doivent être repérés sur fond vert.

• Valider cette règle en cliquant sur le bouton Ok,

De retour sur le gestionnaire des règles, vous notez l'intégration de celle que nous venons de construire.



La même procédure doit être reproduite pour créer la dernière règle sur les absences :

=RECHERCHEV($BD$11& TEXTE(B7;'jj-mm-aaaa') & $BD$24; Extraction!$E:$E;1;FAUX)=$BD$11 & TEXTE(B7;'jj-mm-aaaa') & $BD$24

La cellule impliquée est la cellule $BD$24 et la couleur associée est un orange soutenu. Ensuite, il convient de reproduire ces règles de mise en forme conditionnelle sur tous les autres mois de l'année, grâce au pinceau du ruban Accueil.



Grâce à ces jeux de couleurs dynamiques, il devient trivial de repérer les absences du salarié et leurs causes.

Mettre en valeur les jours fériés
Pour parachever l'outil, nous proposons de faire ressortir dynamiquement les jours fériés sur un fond noir. Ils sont recensés sur quelques années dans la feuille Jour_feries. Il convient tout d'abord de produire une extraction de ces jours en fonction de l'année choisie depuis la feuille Calendrier. Ensuite, nous pourrons bâtir de règle de correspondance pour la mise en forme conditionnelle.

• En bas de la fenêtre Excel, cliquer sur l'onglet Jour_feries pour activer sa feuille,
• Sélectionner la cellule J6 pour un calcul intermédiaire d'extraction,
• Taper le symbole égal (=) pour initier la formule,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Taper la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner le tableau par ses étiquettes de colonne, soit : C:H,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Cliquer sur une cellule de la première ligne d'extraction, par exemple : I6,
• Fermer la parenthèse de la fonction Ligne,
• Puis, taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,

Cette fois c'est le repérage en colonne qui varie. Son indice dynamique dépend de la position de l'année choisie depuis le calendrier, sur la ligne 4 de la feuille Jour_feries. Et c'est encore une fois la fonction Excel Equiv qui permet de la débusquer.

• Saisir le nom de la fonction suivi d'une parenthèse, soit : Equiv(,
• En bas de la fenêtre Excel, cliquer sur l'onglet Calendrier pour activer sa feuille,
• Sélectionner la cellule de l'année choisie ou saisir ses coordonnées (BD8),
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : Calendrier!$BD$8,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la ligne 4 par son étiquette, soit : 4:4,
• Puis, la figer avec la touche F4 du clavier, ce qui donne : $4:$4,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Retrancher 2 unités à ce résultat retourné, soit : -2,

En effet, nous devons annihiler le décalage opéré par les deux premières colonnes de la feuille, non définies dans le tableau d'extraction passé en premier paramètre de la fonction Index.

• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

Comme vous pouvez le voir, une série de jours fériés est effectivement extraite. Elle correspond bien à l'année choisie depuis le calendrier annuel. Si vous modifiez ce choix, la liste des jours fériés s'adapte instantanément. C'est donc dans cette colonne J que nous devons valider la correspondance avec les dates du calendrier, pour la règle de mise enforme conditionnelle.

• En bas de la fenêtre Excel, cliquer sur l'onglet Calendrier pour activer sa feuille,
• Sélectionner toutes les dates du premier mois, soit la plage de cellules B7:H12,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir la commande 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 juste en-dessous pour l'activer,
• Construire alors la syntaxe suivante : =RechercheV(B7;Jour_feries!$J:$J;1;Faux)

Grâce à la fonction Excel RechercheV, nous recherchons chaque date du calendrier. C'est pourquoi la cellule B7 de la première date n'est pas figée. Nous exerçons cette recherche dans la colonne J d'extraction de la feuille Jour_feries. Bien sûr, nous la conservons figée car cette recherche doit toujours se réaliser dans cette même colonne. En troisième argument, le chiffre 1 demande de retourner la valeur correspondante trouvée, dans la première colonne du tableau de recherche. En l'occurrence, nous avons défini ce dernier sur une unique colonne. Le dernier argument Faux impose à la fonction de réaliser une recherche exacte.

Remarque : En raison de la nature de la valeur cherchée, contrairement aux règles précédentes, il n'est pas nécessaire de formuler l'égalité du test. A partir du moment où une donnée est trouvée, la recherche est jugée concluante, donc la règle opérationnelle.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Activer l'onglet Remplissage de la boîte de dialogue qui suit,
• Dans la palette de couleurs, choisir un Gris foncé,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Choisir un style gras et une couleur blanche pour le texte,
• Valider ces réglages en cliquant sur le bouton Ok,
• De retour sur la première boîte de dialogue, valider la règle en cliquant sur le bouton Ok,

De retour sur la feuille, le 1 ^er Janvier est instantanément repéré comme un jour férié dans une mise en forme sans équivoque.

• Répliquer cette règle sur tous les autres mois de l'année grâce au pinceau du ruban Accueil,

Tous les jours fériés sont désormais dynamiquement repérés. Ils viennent renforcer l'interprétation des jours de disponibilité d'un salarié, grâce aux jeux de couleurs judicieusement adaptés.



Dans une prochaine formation, nous aboutirons complètement l'outilde gestion des congés. Grâce au code VBA, nous permettrons au responsable d'archiver de nouveaux congés, par sélections directes des dates dans le calendrier annuel.