formateur informatique

Gérer les disponibilités des salariés avec Excel

Accueil  >  Bureautique  >  Excel  >  Excel Avancé  >  Gérer les disponibilités des salariés avec Excel
Livres à télécharger


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

Abonnez-vous gratuitement à la chaîne Youtube Rossetti Stéphane .
Sujets et formations similaires :


Gérer la disponibilité des salariés

Dans cette formation Excel, nous allons construire un outil simple, permettant de repérer instantanément la disponibilité des salariés, dans un calendrier.

Calendrier annuel Excel avec repères visuels sur les absences et congés des salariés



En fonction des semaines surlignées, le Team Manager saura sur qui il peut compter et comment construire ses équipes.

Classeur source et présentation du concept
Nous proposons de bâtir cet outil sur une application existante et déjà très fonctionnelle. Nous réceptionnons un classeur constitué de trois feuilles nommées respectivement : Calendrier, Jour_feries et Conges.

Cette première feuille offre un outil puissant et abouti. Il permet de construire automatiquement le calendrier annuel d'une année à définir. Il gère bien entendu le nombre de jours qui composent chaque mois, et tient compte des années bissextiles.

Mais de surcroît, il repère en vert les jours fériés et en bleu, les jours de Week-End. Cela va de soi, tout cela est entièrement automatisé et dynamique.
  • Sur la feuille Calendrier, cliquer sur la cellule C3 pour la sélectionner,
Vous notez qu'elle est dotée d'une petite liste déroulante.
  • Cliquer sur la flèche pour déployer cette liste,
  • Puis, sélectionner par exemple l'année 2022,
Aussitôt, le calendrier se recompose. Les Week-End se déplacent et les jours fériés s'adaptent. Ces jours fériés sont recensés pour les années proposées dans la feuille Jour_feries. C'est ainsi que la correspondance dynamique s'établit.

Outil Excel pour construire automatiquement calendriers annuels avec Week-End, jours fériés et gestion des salariés

Nous avions abouti ce calendrier automatique à l'occasion d'une formation. Vous pouvez donc la consulter à loisir pour comprendre sa construction.

Sur la droite de cette liste déroulante, vous notez la présence de deux cases destinées à désigner deux des salariés. Plus précisément, il s'agit des cellules E3 et G3. En cliquant sur l'une ou l'autre, une liste déroulante se propose. Elle énumère les salariés sous la responsabilité du Team Manager. C'est donc, au choix de l'un d'entre eux, que les indisponibilités doivent être mises en valeur automatiquement, par des jeux de couleurs. Dans le cas où deux employés sont désignés, nous souhaitons repérer, dans une mise en forme explicitement différente, les indisponibilités communes.

Vous l'avez compris, la mise en forme conditionnelle d'Excel est la solution pour aboutir cet outil.
  • En bas de la fenêtre Excel, cliquer sur l'onglet Conges, pour afficher sa feuille,
Base de données Excel archivant les congés des employés pour gérer les indisponibilités dans le calendrier

Nous y trouvons un tableau. Et ce dernier, énumère pour chaque salarié, l'ensemble des dates posées. Il est à noter la distinction faite entre les congés payés (CP), les jours de récupération (RTT) et les absences inopinées (ABS). Nous veillerons à différencier subtilement ces indisponibilités dans le calendrier.
  • En haut à gauche de la feuille Excel, cliquer sur la flèche de la zone Nom,
Noms de plages Excel pour pointer sur les colonnes des congés des salariés dans les formules de repérage

Comme vous pouvez le voir, de nombreuses plages de cellules sont recensées. Elles portent les noms des salariés respectifs. Elles bornent les dates et la nature des congés pour chacun. Ces noms ont été construits automatiquement avec la fonctionnalité Depuis sélection d'Excel. Et ces noms sont primordiaux pour la résolution du problème.



Repérer dynamiquement les indisponibilités
Dans la feuille Calendrier, un salarié est désigné par son nom. Et comme nous venons de le constater, ce nom borne les dates de congés de l'employé choisi. Nous devons l'exploiter dans les formules régissant les règles de mise en forme conditionnelle. L'idée consiste à passer en revue chaque date du calendrier, dans la plage associée au salarié pour trouver une correspondance. Si ce lien est établi, nous savons que la date est posée. En conséquence, la cellule doit réagir dynamiquement. Et pour des interprétations plus fines, nous souhaitons que les couleurs diffèrent légèrement selon la nature de l'absence.
  • En bas de la fenêtre Excel, cliquer sur l'onglet Calendrier pour revenir sur sa feuille,
La cellule E3 est remplie d'un fond jaune tandis que la cellule G3 est remplie d'un fond orange. Nous proposons d'appliquer des variantes à ces deux couleurs. Ainsi, en même temps que nous distinguerons automatiquement les dates des deux salariés, nous connaîtrons la nature des congés posés, pour l'un et l'autre. Souvenez-vous, en cas de recoupement, nous souhaitons en alerter explicitement le chef d'équipe. Bref, nous avons du pain sur la planche.

Ces règles de mise en forme conditionnelle doivent réagir selon des critères. Si une date du calendrier figure dans le listing du salarié, elle doit être repérée. Nous devons donc les rechercher toutes, mais à l'aide d'une seule formule. La fonction Excel dédiée est la fonction RechercheV:

=RechercheV(Valeur_cherchée; Tableau_de_recherche ; Colonne_de_retour ; Mode_de_recherche)

La valeur cherchée correspond à chaque date du calendrier. Elle doit être passée en premier argument de la fonction. Le tableau de recherche est la plage de cellules pointée par le nom du salarié désigné. Mais attention, inscrites en cellules E3 et G3, Excel les considère comme des textes et non des plages. Nous devons exploiter la fonction Excel Indirect sur ces cellules pour qu'il les interprète comme telles. En troisième argument, nous devons lui spécifier l'élément que nous souhaitons récupérer en retour de la recherche fructueuse. Il s'agit de la nature du congé pour faire varier les couleurs. Et ce type est défini en colonne 2 de chaque plage. Le mode de recherche doit être exact, ce que nous spécifierons en quatrième et dernier argument de la fonction RechercheV. Et cette construction doit intervenir dans une règle de mise enforme conditionnelle appliquée à l'intégralité du calendrier.
  • Cliquer sur la première date du calendrier pour sélectionner sa cellule C7,
  • Tout en maintenant la touche MAJ (Shift) enfoncée, cliquer sur la dernière, soit N37,
Cette technique nous permet d'englober toutes les dates dans la sélection. Une mise en forme conditionnelle s'applique en effet sur une plage explicitement définie.
  • Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
  • En bas de la liste, choisir 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 qui se propose juste en-dessous, pour l'activer,
C'est elle qui doit recevoir la syntaxe du critère permettant de faire réagir dynamiquement les cellules du calendrier. Dans une mise en forme conditionnelle, la condition est implicitement exprimée. Nous ne devons donc pas employer la fonction Excel Si.
  • Taper le symbole égal (=) pour initier la formule,
  • Saisir la fonction de recherche suivie d'une parenthèse, soit : RechercheV(,
  • Désigner la première date cherchée en cliquant sur la cellule C7, ce qui donne : $C$7,
  • Enfoncer trois fois de suite la touche F4 du clavier pour éliminer les dollars,
En effet, cette cellule ne doit pas être figée. Une mise en forme conditionnelle raisonne chronologiquement, de la première à la dernière cellule sélectionnée. Toutes les dates doivent être comparées aux congés. Elles doivent donc toutes être passées en revue.
  • Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
  • Saisir la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
  • Cliquer sur le nom du premier salarié, soit la cellule E3, ce qui donne : $E$3,
En effet, son nom désigne la plage de ses congés. Grâce à la fonction Indirect, nous pointons sur son tableau, situé dans la feuille Conges. Et cette fois, nous devons conserver les dollars. Chaque date du calendrier doit être recherchée dans le tableau désigné par cette cellule. Elle ne doit donc pas bouger.
  • Fermer la parenthèse de la fonction Indirect,
  • Taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,
  • Saisir le chiffre 2 pour obtenir la nature de l'indisponibilité correspondant à la date trouvée,
  • Taper un point-virgule suivi du texte Faux, soit : ;Faux, pour une recherche exacte,
  • Fermer la parenthèse de la fonction RechercheV,
  • Puis, taper le symbole égal (=) pour l'égalité du critère à vérifier,
  • Saisir la mention CP entre guillemets, soit : 'CP',
La syntaxe de notre critère est donc la suivante :

=RechercheV(C7;Indirect($E$3);2;Faux)='CP'

Nous recherchons la date en cours d'analyse dans le calendrier. Si elle est trouvée et que la mention en regard est CP, nous savons que le salarié est absent, plus précisément en congés payés. Dans ces conditions, nous souhaitons faire ressortir la cellule dans un jaune soutenu. Il indiquera visuellement la nature de l'absence. En l'occurrence, un congé payé peut difficilement être remis en cause et déplacé par le chef d'équipe.
  • En bas de la boîte de dialogue, cliquer sur le bouton Format,
  • Dans la nouvelle boîte qui apparaît, activer l'onglet Remplissage,
  • Dans la palette de couleurs, choisir un jaune soutenu,
  • Puis, cliquer sur le bouton Ok pour valider cette mise en forme,
Règle de mise en forme conditionnelle Excel pour surligner dynamiquement les dates de congés payés dans le calendrier annuel

Nous sommes de retour sur la première boîte de dialogue qui résume parfaitement le contexte. A chaque fois que le salarié désigné est repéré en congés payés, les dates correspondantes doivent se parer d'un fond jaune.
  • Valider une nouvelle fois en cliquant sur le bouton Ok de la boîte de dialogue,
A notre grande surprise, aucune date ne semble réagir. Pourtant, les absences du salarié sont clairement identifiées dans la feuille Conges. Mais souvenez-vous, en début de formation, nous avions choisi l'année 2022 pour la construction automatique du calendrier. Les congés quant à eux sont posés sur l'année 2020. En conséquence, aucune correspondance ne peut être trouvée par la fonction Excel RechercheV.
  • Cliquer sur la cellule C3 pour la sélectionner,
  • Déployer la liste déroulante,
  • Puis, choisir l'année 2020,
Instantanément, les congés payés du salarié, repérés par la mention CP, sont identifiés et mis en valeur. Le Team Manager connaît donc ses indisponibilités, mais pas toutes encore. Nous devons, selon le même procédé, repérer les RTT et les absences inopinées.

Identifications visuelles dynamiques des congés du salarié dans calendrier annuel Excel

Pour ce premier salarié, nous devons créer deux règles quasiment identiques. Seule la valeur de retour à observer change : RTT dans le premier cas et ABS dans le second.

En prenant soin de sélectionner préalablement l'intégralité des dates du calendrier, les deux règles suivantes doivent être ajoutées pour le premier salarié.

=RECHERCHEV(C7;INDIRECT($E$3);2;FAUX)='RTT'

Pour les RTT, nous choisissons un jaune pâle. Ces absences peuvent se moduler facilement. Le chef d'équipe en aura l'indication visuelle instantanée.

=RECHERCHEV(C7;INDIRECT($E$3);2;FAUX)='ABS'

Pour repérer les absences, nous préférons un jaune foncé. Si elle a été posée en dernière minute, cela signifie qu'il s'agit d'un impératif.

Nous devons ensuite réitérer le même procédé pour créer trois règles de mise en forme conditionnelle, associées au second salarié. La cellule concernée n'est donc plus E3 pour le tableau de recherche, mais G3.

=RECHERCHEV(C7;INDIRECT($G$3);2;FAUX)='CP'

Cette première règle doit être associée à un orange soutenu.

=RECHERCHEV(C7;INDIRECT($G$3);2;FAUX)='RTT'

Cette deuxième règle doit être associée à un orange clair.

=RECHERCHEV(C7;INDIRECT($G$3);2;FAUX)='ABS'

Cette dernière règle doit être associée à un orange foncé.

Désormais, en désignant deux salariés à l'aide des listes déroulantes respectives, les absences de chacun sont repérées dynamiquement par des nuances de couleurs qui lui appartiennent. Cette identification visuelle instantanée est donc très précieuse pour le Team Manager qui souhaite construire ses équipes de production par anticipation.

Repérer dynamiquement les congés croisés des employés sur un calendrier annuel Excel

Dans le contexte actuel en revanche, il existe une hiérarchie dans les règles posées. Si d'aventure des périodes se chevauchent, c'est la dernière règle posée qui prend le pas sur les autres. En d'autres termes, l'un des deux salariés n'est plus identifié dans sa couleur.



Repérer les absences conjointes
Pour pallier le problème, nous proposons donc de créer une dernière règle. Elle consiste à vérifier si dans le même temps, la date du calendrier est identifiée dans les absences du premier et du second salarié. Il faut donc recouper deux recherches. C'est la fonction Excel ET qui permet de combiner les conditions.
  • Sélectionner l'intégralité du calendrier, soit la plage de cellules C7:N37,
  • Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
  • En bas de la liste, choisir 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 qui se propose juste en-dessous pour l'activer,
  • Taper le symbole égal (=) pour initier la syntaxe de la règle,
  • Saisir la fonction pour recouper les conditions, suivie d'une parenthèse, soit : Et(,
  • Taper la fonction de recherche, suivie d'une parenthèse, soit : RechercheV(,
  • Désigner la première date du calendrier, soit C7,
  • Enfoncer trois fois la touche F4 du clavier pour éliminer les dollars,
  • Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
  • Saisir la fonction d'interprétation, suivie d'une parenthèse, soit : Indirect(,
  • Désigner le premier salarié pointant sur sa plage, soit la cellule $E$3,
  • Fermer la parenthèse de la fonction Indirect,
  • Taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,
  • Saisir le chiffre 2 pour désigner la colonne de la nature des absences,
  • Terminer par : ;Faux, pour réaliser une recherche exacte,
  • Fermer la parenthèse de la fonction RechercheV,
  • Taper le symbole inférieur suivi du symbole supérieur, suivi de deux guillemets, soit : <>'',
Grâce à ce critère, nous cherchons simplement à savoir si la date est trouvée, peu importe la nature de l'absence.
  • Taper un point-virgule (;) pour poursuivre l'énumération des critères de la fonction Et,
  • Saisir alors la syntaxe quasiment identique suivante :
RechercheV(C7;Indirect($G$3);2;Faux)<>''

C'est la plage de recherche qui est adaptée, désignée par la cellule du salarié choisi.
  • Fermer la parenthèse de la fonction Et,
  • En bas de la boîte de dialogue, cliquer sur le bouton Format,
  • Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
  • Dans la palette de couleurs, choisir un gris très foncé puis, activer l'onglet Police,
  • Dans la zone Couleur, choisir un gris très clair et valider ces réglages par le bouton Ok,
Règle de mise en forme conditionnelle Excel pour repérer les absences de deux salariés en même temps

Comme précédemment, nous sommes de retour sur la première boîte de dialogue. Elle résume parfaitement le contexte. Lorsque les dates d'indisponibilité des deux salariés sont communes, les cellules doivent se parer d'un fond noir et d'un texte clair.

Identification visuelle dynamique des absences croisées de deux salariés sur un calendrier annuel Excel

Désormais, en croisant deux salariés, en plus des repérages individuels, les dates communes des absences sont explicitement mises en valeur. Le chef d'équipe n'a plus qu'à recueillir l'information.

 
Sur Facebook
Sur G+
Sur Youtube
Les livres
Contact
Mentions légales



Abonnement à la chaîne Youtube
Partager la formation
Partager sur Facebook
Partager sur Google+
Partager sur Twitter
Partager sur LinkedIn