Site icon Excel Exercice

Afficher les jours de congés dans un calendrier

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 d'absence posés par les salariés en couleur dans votre planning.

Aucune programmation n'a été utilisée pour concevoir ce classeur mais sa construction n'est pas simple et assez longue.

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.

=B4+1

Etape 3 : Changer le format des dates

Laisser la date au format jour/mois/année n'est pas très pratique. Le plus simple c'est de changer le format d'affichage des dates grâce au code personnalisé pour afficher la première date de la façon suivante :

=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. Ici, on prend la formule NO.SEMAINE.ISO car la fonction NO.SEMAINE ne retourne pas nécessairement la bonne valeur, comme cela a été précisé dans cet article.

=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 colonne, 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

Les dates sont au format Standard

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 : Transformer le test pour retourner Vrai ou Faux

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 une instruction 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 dès lors englober le précédent test dans la fonction NON qui va inverser le test.

=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 (Youpi ! ????) quand un salarié a posé un jour de congé. Il nous suffit d'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
  1. Dans la boîte de dialogue, sélectionnez Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué
  2. Là, vous copiez dans la zone de texte votre formule
  3. Cliquez sur le bouton Format
  4. 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

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.

Quitter la version mobile