«

»

Jours de congés en couleur

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

Aucune programmation n'a été utilisée dans ce classeur.

Téléchargement du fichier

Vous pouvez retrouver le classeur déjà réalisé dans le fichier téléchargeable ci-dessous

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.

planning_1Pour commencer nous allons saisir en cellule A2 la date courante grâce à la fonction AUJOURDHUI.

 

 

planning_2Ensuite, 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

planning_3

Etape 3 : Changer le format des dates

planning_4Laisser 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 : Concaténation des cellules

 

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.

planning_4b

Le résultat n'est pas "esthétique".

En fait, lors de la concaténation, nous ne pouvons pas garder le format de la date (plus d'explication sur le format des dates dans cet article et aussi sur la fonction TEXTE).

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

planning_5

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)

planning_6

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))

planning_7

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)))

planning_8

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.

planning_9

  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.

planning_10

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

planning_11

Le résultat devient ceci

planning_12

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.

planning_13

Articles complémentaires


Voici une liste d'articles qui pourrait également vous intéresser sur le même thème.

Lien Permanent pour cet article : https://www.excel-exercice.com/jours-de-conges-en-couleur/


(1 commentaire)

  1. Vincent Debever

    Bonjour,

    Merci pour ce tuto. Vous montrez qu'en ajoutant une date et un salarié dans le tableau 1 permet de mettre en rouge l'intersection correspondante dans le planning. Mais est-il possible de mettre un salarié avec une date de début de congé et une de fin pour mettre en rouge toute la période de congé plutôt que de faire date par date ?

    Merci d'avance,
    Bonnes fêtes de fin d'année

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *