Il est très fréquent dans les entreprises de devoir faire des plannings hebdomadaires. Dans cet article je vais vous montrer comment transformer les jours de congés posés par les salariés en couleur dans votre calendrier.

Aucune programmation n'a été utilisée pour concevoir ce classeur mais sa construction n'est pas simple et prend beaucoup de temps. 😧

Etape 1 : Calculer la date du prochain lundi

La première chose à faire pour construire notre planning, c'est de déterminer la valeur du prochain lundi. Pour commencer nous allons saisir en cellule A2 la date courante grâce à la fonction AUJOURDHUI.

Ensuite, sur la base de cette information et de la fonction JOURSEM, nous allons récupérer le prochain lundi grâce à la formule suivante

=A2+8-JOURSEM(A2;2)

Etape 2 : Remplir les autres jours de la semaine

Une fois que le lundi est calculé, il est facile de rajouter les dates suivantes. Il suffit de rajouter 1 à la date précédente.

Etape 3 : Changer le format des dates

• Laisser la date au format jour/mois/année n'est pas très lisible.
• Le plus simple c'est de changer le format d'affichage des dates grâce au code personnalisé pour ajouter le jour en lettres

=jjj jj mmm

Etape 4 : Numéro de semaine

• Le numéro de semaine se déduit du premier lundi grâce à la fonction NO.SEMAINE.ISO.
• Attention à ne pas confondre les fonctions NO.SEMAINE et NO.SEMAINE.ISO

=NO.SEMAINE.ISO(B4)

Etape 5 : Création d'une clé unique

Nous devons maintenant effectuer une recherche entre notre feuille hebdomadaire et la table qui contient les jours posés. Le calendrier est construit sur la base de dates (en ligne) et de noms de salariés (en colonne).

Pour pouvoir retrouver dans la table des jours d'absence des salariés, le jour d'absence d'un salarié à une date précise nous n'avons pas d'autres alternatives que de créer une colonne supplémentaire qui va concaténer le nom du salarié et la date d'absence.

Le résultat n'est pas "esthétique". Mais cela n'a pas d'importance pour notre test. L'important c'est d'être capable de déterminer de façon unique, dans une seule cellule, le jour où un salarié est absent.

Etape 6 : Référence mixte

Maintenant, nous devons utiliser la fonction RECHERCHEV qui va être capable de "regarder" si l'association Salarié + Date existe dans la table des jours d'absence. Mais, avant cela, nous allons écrire une référence mixte qui va récupérer le nom des salariés associés aux dates.

=$A5&B$4

Comme vous le voyez, à chaque intersection, nous récupérons le croisement de la date et du nom du salarié.

Etape 7 : Création de la fonction RECHERCHEV

L'étape précédente peut vous paraître anodine mais c'est la clé de tout ce classeur.

Nous venons de créer une clé unique qui associe le nom des salariées et les dates. Il est facile dès lors de créer la fonction RECHERCHEV qui va aller chercher les informations sur les absences des salariés dans la feuille.

=RECHERCHEV($A5&B$4;Tableau1[Code];1;0)

Quand la fonction RECHERCHEV retourne #N/A (Not Applicable), cela signifie que la recherche n'a pas abouti. Mais cela n'est pas forcément une erreur comme cela a été vu dans l'article sur la comparaison entre 2 colonnes.

Nous sommes maintenant très proche de la fin de la construction de notre planning.

Etape 8 : Afficher VRAI pour les jours de congé dans le calendrier

A l'étape précédente, nous avons été capable de créer une formule qui détecte si un salarié a posé une journée d'absence (la fonction RECHERCHEV renvoie une valeur) ou pas (la fonction RECHERCHEV renvoie #N/A).

Maintenant, nous devons transformer ce résultat en test VRAI/FAUX (indispensable pour une mise en forme conditionnelle). Pour convertir le résultat de la fonction RECHERCHEV en résultat VRAI/FAUX, il faut tout simplement englober l'écriture de la fonction RECHERCHEV dans la fonction ESTNA.

=ESTNA(RECHERCHEV($A5&B$4;Tableau1[Code];1;0))

Seulement, pour la réalisation de notre mise en forme conditionnelle, c'est le résultat inverse que nous voulons. C'est à dire que la formule doit nous retourner VRAI quand la recherche a abouti et FAUX quand la recherche n'abouti pas.

C'est pourquoi nous devons englober le précédent test dans la fonction NON qui va inverser le test (astucieux 😉👍). Les jours de congés affichent VRAI dans le calendrier.

=NON(ESTNA(RECHERCHEV($A5&B$4;Tableau1[Code];1;0)))

Etape 9 : Mise en forme conditionnelle

Nous venons de créer une formule qui nous retourne VRAI ou FAUX correctement quand un salarié a posé un jour de congé pour l'afficher dans le calendrier. Nous allons maintenant intégrer cette formule dans une mise en forme conditionnelle pour changer la couleur des cellules quand le salarié est absent.

1. Copiez la formule précédente
2. Ouvrez le menu Accueil>Mise en forme conditionnelle>Nouvelle règle
3. Dans la boîte de dialogue, sélectionnez Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué
4. Là, vous copiez dans la zone de texte votre formule
5. Cliquez sur le bouton Format
6. Puis dans l'onglet Remplissage, vous choisissez une couleur qui va définir votre trame de fond quand la règle sera VRAI.

Et ....... Ca ne marche pas 😮😡

Etape 10 : Rendre les références du Tableau visible

En fait, quand on met la référence d'un Tableau dans une règle, la mise en forme conditionnelle est incapable de l'interpréter. Sauf, si vous intégrez la référence du Tableau dans une fonction INDIRECT.

INDIRECT("Tableau[Code]")

Maintenant, il ne reste plus qu'à appliquer cette règle à l'ensemble de vos cellules

1. Ouvrez le menu Accueil>Mise en forme conditionnelle>Gérer les règles
2. Veillez à bien sélectionner l'option Cette feuille de calcul dans le menu déroulant Afficher les règles de mise en forme pour
3. Ensuite dans la zone S'applique à Sélectionner la zone des cellules B5:H8

Le résultat devient ceci. Chaque jour de congés est maintenant en rouge dans le calendrier.

Partout où le test est VRAI, la cellule est coloriée en rouge.

Vous pouvez maintenant effacer les formules qui nous ont permis de créer la règle conditionnelle ; elles ne sont plus utiles.

Etape 11 : Bordures

Améliorer la présentation est toujours conseillé pour tout ceux qui vont travailler avec votre document. Si vous perdez trop de temps à tracer vos bordures, vous pouvez voir la vidéo dans cet article pour améliorer votre technique.