L’objectif de cet article est de vous guider dans la réalisation d’un calendrier d’horaires journaliers, en partie dynamique, dans Excel.
Cela fait suite à une question qui m’a été posée, et dont la problématique principale était d’attribuer de manière récurrente une allocation horaire journalière en fonction du jour de la semaine, et ce chaque mois. J’y apporte donc ici une solution possible permettant d’éviter la répétition de travail manuel, et donc de gagner en productivité grâce à l’utilisation de formules Excel principalement avec les fonctions de date, et aussi INDEX et EQUIV.
Ce cas d’utilisation particulier peut bien évidemment être dérivé pour d’autres problématiques similaires.
Si vous avez une question, n’hésitez pas à la poser sur le forum !
Composants du Calendrier
L’aperçu ci-dessous nous donne une première idée de la composition du calendrier que nous allons réaliser étape par étape.
On y trouve :
- Une Cellule « Année » dans laquelle on pourra entrer l’année souhaitée pour le calendrier, ce qui influera sur les jours de la semaine affichés dans notre calendrier.
- Une Table [Jour Semaine / Numéro Jour Semaine / Allocation Heure] – il s’agit de notre table statique dans laquelle nous définirons l’allocation d’heures en fonction du jour de la semaine. La colonne [Numéro Jour Semaine] permettra de faire le lien avec la fonction « JOURSEM » qui renvoie le numéro du jour de la semaine.
- La Partie Dynamique du calendrier qui s’affichera selon les paramètres entrés. On peut considérer ici la partie des colonnes F à H comme étant répétée sur J à L, N à P, avec une simple variation du paramètre entré dans la ligne 2 (et correspondant au numéro du mois de l’année).
Les points (1) et (2) sont statiques et ne contiennent aucune formule. Vous pouvez donc y adapter vos données. Nous allons donc nous attarder sur la partie des colonnes F à G, qui peut être ensuite répétée 12 fois sur les colonnes suivantes pour chaque mois de l’année.
Implémentation de la Partie Dynamique du Calendrier
Nous allons décomposer la partie dynamique (colonnes F à G) en 5 parties.
- L’entrée du numéro de mois : qui permettra ensuite d’adapter dynamiquement le nombre de jours dans le mois, ainsi que le jour de la semaine en fonction du mois et de l’année.
- L’affichage du mois et de l’année en question : un affichage visuel pour l’ensemble des colonnes en question, et également une dépendance pour l’affichage du nombre de jours pour le mois concerné.
- La colonne [Jour Du Mois] qui, en fonction du mois et de l’année, affichera uniquement le nombre de jours pour ce mois. la première ligne étant statique et fixée à 1.
- La colonne [Jour Semaine] qui, en fonction du jour de la ligne et du mois/année de la colonne, définira la date entière (jour/mois/année) et en affichera uniquement la partie « jour de la semaine » grâce aux options de format de cellule.
- Et pour finir la colonne [Heures Journalières] qui, en fonction du [Jour Semaine] ira chercher dans la table statique du point (2), et à l’aide des fonctions INDEX et EQUIV, l’allocation d’heure correspondante.
L’Entrée du Numéro de Mois
Commençons donc par le premier point (sur la capture d’écran, F2 à H2). Il s’agit simplement d’une cellule d’entrée dont la valeur définira le mois affiché dans la colonne. On entrera donc ici une valeur entre 1 et 12 selon le mois de l’année souhaité. Cette entrée aura pour avantage de construire rapidement chaque table de mois en ayant uniquement une valeur à changer.
Tous les points suivants seront composées de formules qui une fois implémentées n’auront plus besoin d’être modifiées et afficheront dynamiquement les valeurs en fonction des paramètres entrés.
L’Affichage du Mois et de l’Année
Deuxième point, l’affichage du mois et de l’année, cette cellule permet principalement d’identifier le mois pour lequel la table est en place et sera utilisée en référence pour les formules. On y entrera la formule suivante :
=DATE($C$2;F$2;1)La fonction DATE composera une date Excel selon l’année, le mois et le jour donnés. On y entrera donc notre cellule année, notre cellule vue dans le point précédent pour le mois, et statiquement le chiffre 1. Par ailleurs on appliquera un format de cellule personnalisé « mmmm aaaa » pour n’afficher que le mois et l’année.
La Colonne « Jour du Mois »
Troisième point, il s’agit de la première colonne de notre table : « Jour Du Mois ». La première ligne (ici dans la capture d’écran la cellule F5) sera statique et toujours « 1 ». Les 30 lignes suivantes (pour les jours potentiellement jusque 31) auront une formule permettant de vérifier le nombre de jours dans le mois pour lequel la table est paramétrée. On entrera dans la première cellule (ici sur la capture d’écran cellule F6) la formule suivante que l’on étendra sur les cellules suivantes.
=SI(F5="";"";SI(F5+1<=JOURS(FIN.MOIS(F$3;0);F$3-1);F5+1;""))Si la cellule précédente est vide, alors afficher vide aussi : c’est le cas si l’on a déjà dépassé le nombre de jours maximum dans le mois.
Sinon, si le jour précédent auquel on ajoute 1 est inférieur ou égal au nombre de jours maximum dans le mois, on affiche ce nombre, sinon vide.
Le nombre de jours maximum dans le mois est calculé avec la fonction JOURS qui donne le nombre de jours entre deux dates données : on indique donc en date de fin la date du dernier jour du mois (grâce à la fonction FIN.MOIS) et en date de début le dernier jour du mois précédent – c’est ici donc que nous utilisons en référence le mois défini au point 2.
La Colonne « Jour de la Semaine »
Quatrième point : la colonne « Jour Semaine » dans laquelle on construira entièrement la date grâce au Jour, au Mois et à l’Année que nous passerons dans la fonction DATE. Visuellement on affichera au format de cellule « jjjj » pour ne voir que le jour de la semaine. Sur la première ligne (sur la capture d’écran en G5) on entrera la formule suivante que l’on étendra ensuite sur les lignes suivantes.
=SI(F5="";"";DATE($C$2;F$2;F5))La première partie de la formule permet de ne rien afficher si le Jour du mois est vide. Sinon la deuxième partie compose la date.
La Colonne « Heures Journalières »
Finalement, cinquième et dernier point, l’affichage des heures journalières en fonction du jour de la semaine en allant chercher la valeur dans notre table statique. On utilisera simplement une combinaison de INDEX avec EQUIV pour récupérer la valeur cherchée dans notre table statique. Dans la première ligne (ici dans la capture d’écran en H5) on entrera donc la formule suivante que l’on étendra sur les lignes suivantes :
=SI(F5="";"";INDEX($D$8:$D$14;EQUIV(JOURSEM(G5;2);$C$8:$C$14;0)))Ici encore, la première partie de la formule permet de ne rien afficher si le Jour du mois est vide. Sinon on utilise notre combinaison INDEX EQUIV pour récupérer la colonne « Allocation Heure » si le Jour de la semaine correspond à la colonne « Numéro Jour Semaine » de notre table statique.
La transformation de notre date « Jour Semaine » en chiffre se fait à l’aide de la fonction JOURSEM avec en deuxième argument « 2 » (qui permet de définir Lundi = 1 à Dimanche = 7).
Notre table est maintenant composée avec les formules et paramétrage du mois, on peut alors copier les 3 colonnes qui la composent et la coller 11 fois sur d’autres colonnes, changer le chiffre du mois vu au point 1 pour chaque table pour obtenir les mois de 1 à 12. Et voilà!
On peut aussi ensuite répliquer la feuille de calcul et changer le paramètre « Année » pour créer rapidement et dynamiquement une nouvelle année sans avoir à tout reconstruire.
Pour Aller Plus Loin : Plusieurs Attributions Horaires
Pour aller plus loin dans l’optimisation, imaginons que nous avons plusieurs employés, pour lesquels l’attribution horaire journalière est différente. On pourrait créer plusieurs feuilles de calcul pour chaque employé dans lesquelles on maintiendra une table d’allocation statique en fonction de l’employé.
Mais on peut également se contenter de deux feuilles de calcul uniquement ! Une feuille de calcul qui contiendra toute la partie statique pour chaque employé sous forme de table croisée avec en ligne le jour de semaine et en colonne l’employé. Et une seconde feuille de calcul qui correspond à notre calendrier dynamique avec une liste déroulante qui permet de sélectionner l’employé pour lequel on souhaite afficher le calendrier horaire journalier.
Notre table statique « Jour Semaine » / « Numéro Jour Semaine » / « Allocation Heure » devient donc dynamique en fonction de l’employé sélectionné dans une nouvelle cellule de référence (qui peut même contenir une liste déroulante) qui permettra là encore avec la combinaison INDEX EQUIV d’aller récupérer les allocations horaires dans une nouvelle table statique, qui elle contient la liste de employés et leur allocations horaires en fonction du jour de la semaine.