Exercice pratique : Calendrier perpétuel Excel

Vous souhaitez réaliser un calendrier mensuel perpétuel avec Excel qui, une fois créé, ne vous demandera aucune modification de formule pour l'utiliser ? Ce tutoriel est écrit pour vous !

Avec des formules, de la mise en forme conditionnelle, la gestion des jours fériés et des événements quotidiens, sans programmation VBA.

7 commentaires Donner une note à l'article (5) 

Article lu   fois.

L'auteur

Site personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Présentation

I-A. Objectifs

L'objectif du présent tutoriel est de mettre en œuvre différentes techniques pour créer un calendrier mensuel perpétuel avec Excel, sans utiliser VBA.

Cet exercice est proposé aux participants de mes formations pour mettre en œuvre l'utilisation conjointe de fonctions au sein d'algorithmes « complexes », amenant le participant, à partir d'un cahier des charges, à concevoir un classeur Excel dans le respect des bonnes pratiques.

Cet exercice est la conclusion d'un apprentissage plus théorique de fonctions diverses d'Excel et de règles de conception incontournables pour une utilisation professionnelle de l'outil.

I-B. Moyens mis en œuvre

Dans ce tutoriel, nous allons brasser plusieurs techniques d'Excel, telles que :

  • fonctions de gestion des dates ;
  • fonctions de recherche ;
  • concaténation et sauts de lignes ;
  • mise en forme conditionnelle formulée ;
  • création et utilisation d'une plage nommée, éventuellement dynamique ;
  • création et utilisation d'une formule nommée ;
  • utilisation de tableaux structurés Excel (« tables » de données) ;
  • validation de saisie, sur base de liste, de bornes, ou formulée ;

Nous reparlerons également de trois règles Excel :

  • pas de constantes dans nos formules ;
  • dissociation des données et de leur présentation ;
  • une formule et une seule pour traduire une règle « métier ».

I-C. Fonctions mises en œuvre dans ce tutoriel

L'utilisation professionnelle d'Excel amène souvent l'Excellien à manipuler plusieurs fonctions au sein de son classeur, et notamment en se servant du résultat de certaines fonctions comme arguments d'autres fonctions. Tout l'art consiste à utiliser les bons ingrédients, notamment en en fabriquant certains lorsqu'ils manquent à l'appel. En effet, certaines valeurs, numériques ou non, ne sont pas directement récupérables par des fonctions intrinsèques d'Excel, et il faudra alors les combiner pour obtenir le résultat souhaité.

Dans le cadre du présent cours, les fonctions suivantes sont utilisées, souvent voire toujours en les combinant :

  • NB.JOURS.OUVRES.INTL() : calcul du nombre de jours ouvrés entre deux dates ;
  • JOURSEM() : récupération du rang du jour de la semaine d'une date ;
  • MOIS() : récupération du numéro du mois d'une date ;
  • JOUR() : récupération du jour du mois d'une date ;
  • DATE() : renvoi d'une date sur base de ses trois composantes ;
  • LIGNE() : récupération du numéro de ligne d'une référence ;
  • COLONNE() : récupération du numéro de colonne d'une référence ;
  • RECHERCHEV() : récupération de la donnée d'une colonne d'une table en fonction d'une clé spécifiée ;
  • INDEX() : récupération d'une valeur se trouvant à une position donnée dans une liste ;
  • EQUIV() : récupération de la position d'une valeur dans une liste (conjointement à INDEX(), permet une généralisation de RECHERCHEV()) ;
  • ET() : renvoi de la valeur VRAI lorsque tous les arguments passés sont VRAI ;
  • SIERREUR() : permet de contourner une erreur en renvoyant une valeur de remplacement ;
  • CAR() : permet de renvoyer le caractère correspondant à son code.

II. Présentation du calendrier finalisé

Le calendrier finalisé présentera une grille mensuelle dont la première colonne représente les lundis, la dernière représentant les dimanches.

La grille colore en gris les jours fériés et jours de weekend, ou plutôt les jours habituellement non ouvrés dans la semaine.

Dans chaque case, le calendrier reprendra les événements saisis pour le jour considéré, et précisera la raison du jour férié s'il échet.

Voici un calendrier pour le mois d'avril 2017.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-04_195207.png

En modifiant simplement la date en I1, le calendrier s'adapte pour afficher la grille du nouveau mois saisi. La série des 0000011 peut être modifiée en fonction des jours habituellement non ouvrés de la semaine.

Voici une illustration de la grille de mai 2017 pour une personne ne travaillant qu'en 4/5 et ayant congé les mercredis, samedis et dimanches.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-04_194539.png

La simple modification de la date de début ou du code des jours ouvrés dans la semaine modifie la grille, dans laquelle une seule formule est utilisée, sans qu'aucune saisie de donnée n'ait lieu directement dans la grille.

III. Cahier des charges

  1. Le calendrier doit présenter les lundis à gauche et les dimanches à droite.
  2. Les jours fériés auront un fond grisé, ainsi que les jours de weekend et les jours habituellement non ouvrés de la semaine.
  3. Les jours du mois précédent ou suivant présents dans la grille auront une police grisée.
  4. Aucune saisie ne sera réalisée dans la grille (règle Excel).
  5. La même formule sera utilisée dans toutes les cases de la grille.
  6. La raison du jour férié sera affichée en regard du numéro du jour.
  7. Les événements seront affichés sous le numéro du jour.
  8. Le calendrier permettra de choisir d'autres jours non ouvrés par semaine que les samedis et dimanches ;
  9. Il ne peut pas y avoir de jours de fin de mois avant les jours de début de mois pour économiser une ligne ;
  10. Il ne peut y avoir de jour de début du mois après les jours de fin du mois pour économiser une ligne ;
  11. Par conséquent, à partir du premier jour du mois qui se trouve sur la première ligne, les jours sont lus de gauche à droite et de haut en bas ;
  12. Si la dernière ligne n'est pas nécessaire, elle sera masquée ;
  13. Pour permettre une utilisation du calendrier sans devoir activer les macros, tout doit être réalisé en Excel sans lignes de code ;
  14. La grille mensuelle doit tenir sur un A4 en paysage.

IV. Création et automatisation de la grille

IV-A. Dessin de la grille

Il y a sept jours dans une semaine, il nous faut donc sept colonnes. La semaine commençant en Europe le lundi, nous organiserons les colonnes du lundi au dimanche.

Combien faut-il de lignes ? Le mois le plus long ayant 31 jours, il faudra au moins cinq lignes. Mais si un mois de 31 jours débute un samedi ou un dimanche, nous tomberons trop court avec cinq lignes, si nous voulons respecter la règle 9 du cahier des charges. Nous aurons donc besoin de six lignes pour couvrir tous les cas.

IV-B. Mise au format A4 paysage

La mise au format A4 paysage ne pose pas de souci majeur. Il faut un peu chipoter avec les largeurs et hauteurs, mais le rendu final respecte le cahier des charges, comme le montre la copie d'écran de l'aperçu avant impression.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-04_212925.png

IV-C. Création des tableaux de données

IV-C-1. Tableau des jours fériés

Une règle Excel incontournable consiste à ne jamais mélanger les données et leur affichage ou leur analyse. Le point 4 du cahier des charges reprend cette règle.

Excel nous permet de « gérer » les données au sein de tables de données qui ont porté différents noms selon les versions. Excel 2003 parlait de listes (le raccourci CTRL+L provient de cette époque), le ruban parle de tableau, et les options de calcul parlent de tables de données. Pour ma part, dans les formations que je donne, je parle souvent de tableaux structurés.

Quoi qu'il en soit, il s'agit de l'outil indispensable pour le stockage des informations au sein d'un tableau Excel.

Le cahier des charges nous demande d'afficher les jours fériés d'une certaine manière et de reprendre la raison du jour férié en regard du numéro du jour dans le tableau. Nous aurons donc besoin d'un tableau à deux colonnes. Il n'est pas nécessaire qu'il soit trié sur les dates, mais on conviendra aisément que ce sera plus pratique pour la gestion des jours fériés.

IV-C-1-a. Création d'un tableau vide

Créer un tableau vide est très simple. Il suffit de se positionner dans la cellule qui sera celle du coin supérieur gauche du tableau puis de créer le tableau via le ruban ou le raccourci CTRL+L. Par souci de clarté, je ne place en général qu'un tableau par feuille et je le « cale » en A1.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_072624.png

Dans le cas d'un tableau vide, comme ici, il n'est pas nécessaire de cocher la case. Vous remarquerez que je n'ai pas précisé le nombre de colonnes. Excel étend automatiquement le tableau lors de l'ajout de colonnes à droite.

Il est très important de nommer vos tableaux, car il n'est pas rare, dans un classeur un peu costaud, d'avoir une vingtaine de tableaux, et les repérer par tableau1, tableau2,… relève de l'amateurisme.

Pour nommer le tableau, il suffit de modifier son nom d'origine dans le contrôle à l'extrême gauche de l'onglet de gestion des tableaux.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_072803.png

Je nomme systématiquement mes tableaux en les préfixant par t_. Cela me permet de les retrouver facilement lors de la saisie de leur nom ou d'une de leurs colonnes dans une formule Excel.

Pour finaliser la création du tableau, il suffit de renommer la colonne en saisissant son nom en A1. L'ajout de la colonne Raison en colonne B se fait simplement en saisissant l'intitulé de la colonne en B1. Excel étend automatiquement le tableau à cette colonne, en proposant une balise active d'annulation.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_073028.png

IV-C-1-b. Saisie des données dans le tableau

Il suffit, en A2, c'est-à-dire juste sous la ligne d'intitulé, de saisir les données. Ici aussi, le tableau grandit automatiquement à l'ajout de données, puis, par facilité, de trier les données sur la colonne des dates.

Image non disponible

La liste donnée ici est indicative et probablement lacunaire. On pourrait y ajouter les jours fériés additionnels de l'entreprise, par exemple.

Notez que ce tableau est unique dans le classeur. Il contiendra donc, au fur et à mesure de la vie du classeur, les jours fériés de plusieurs années.

IV-C-2. Tableau des événements

On procèdera de façon identique pour le tableau contenant les événements à afficher dans le calendrier. Ce tableau, créé sur une feuille qui lui est propre, contiendra également deux colonnes, la première reprenant la date de l'événement, la seconde permettant la saisie des événements de la journée.

Par souci pratique, on pourra saisir plusieurs événements sur une même journée en les séparant par Alt+Enter (saut de ligne à l'intérieur de la cellule) de façon à ce qu'ils soient présentés l'un en dessous de l'autre dans le calendrier. J'ai nommé ce tableau t_Evénements.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_082250.png

Les deux copies d'écran suivantes illustrent la saisie multiligne dans le tableau et le rendu dans l'agenda.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_082416.png
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_082455.png

Si vous souhaitez mentionner l'heure de vos événements, une solution pourrait consister à saisir les événements dans l'ordre de survenance et à saisir l'heure de façon structurée, soit avant soit après l'événement.
Dans le cadre d'un calendrier sans VBA, je ne vois pas d'autre solution à mettre en place de façon aisée.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_211453.png
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_211521.png

Notons ici que ce calendrier perpétuel n'a pas vocation de remplacer des outils de gestion d'agenda tels Outlook, Google Agenda ou autre, mais de proposer une solution simple pour la gestion de plannings tels que :

  • jours de congés au sein d'une structure (service, bureau, TPE/PME) ;
  • rôles de garde ;
  • gestion d'événements « de journée entière » tels anniversaires, fêtes diverses ;
  • … 

IV-D. Élaboration de la formule de calcul du jour du mois

IV-D-1. Principe de base

L'idée est de partir du lundi qui précède ou qui est le premier jour du mois, et de remplir les cases en incrémentant la date dans le sens de la lecture (de gauche à droite puis de haut en bas). Le problème se décompose donc en deux parties :

  • trouver la date du lundi qui précède ou qui est le premier jour du mois ;
  • incrémenter de 1 en zigzag dans la grille de l'agenda.

IV-D-2. Trouver le lundi qui précède ou qui est le premier jour du mois

Il n'y a pas de fonction dans Excel permettant de trouver le lundi d'une date. Toutefois, Excel expose la fonction JOURSEM(…) permettant de trouver le rang d'une date dans les jours de la semaine.

Cette fonction demande deux arguments :

  • la date dont on veut connaître la position dans la semaine ;
  • le système de calcul utilisé.

Les Américains considèrent que le premier jour de la semaine est le dimanche alors que les Européens considèrent que c'est le lundi qui marque le début d'une nouvelle semaine. Par défaut, c'est le système américain qui prime.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_091337.png
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_091954.png

Son utilisation est assez simple, mais il ne faut pas oublier de bien préciser le système dans lequel on travaille.

Jusqu'à Excel 2003, trois valeurs étaient disponibles pour déterminer le système de calcul :

  • 1 : la semaine commence le dimanche (qui vaut donc 1) et se termine le samedi (qui vaut donc 7) ;
  • 2 : la semaine commence le lundi (qui vaut donc 1) et se termine le dimanche (qui vaut donc 7) ;
  • 3 : La semaine commence le lundi, mais on commence à compter à 0. Lundi vaut donc 0 et dimanche vaut 6. Nous allons voir que c'est intéressant dans notre cas de figure.

Depuis 2007, de nouvelles valeurs sont disponibles pour le second argument. Vous pouvez déterminer n'importe quel jour de la semaine comme étant le premier. Les valeurs disponibles vont de 11 (lundi = 1) à 17 (dimanche = 1).

Un rapide schéma nous permet de comprendre comment utiliser cette fonction pour trouver le lundi de la semaine.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_094651.png

Il suffit donc d'utiliser JOURSEM() avec 3 comme valeur du second argument pour trouver la valeur de décalage qui permettra, d'une date donnée, de trouver le lundi qui précède, soit le premier jour de la semaine d'une date donnée.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_101408.png

IV-D-3. Incrémentation des dates

Pour chaque case, nous devons calculer l'incrémentation à appliquer par rapport à la première case de l'agenda, soit le lundi de la semaine qui contient le premier jour du mois.

On pourrait évidemment utiliser plusieurs formules pour réaliser cela.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_101845.png

Cette façon de procéder, si elle semble pratique au premier abord, ne respecte pas une règle capitale d'Excel qui est de n'avoir qu'une seule formule pour les cellules qui expriment une même réalité. Or, dans notre agenda, toutes les cellules du tableau expriment le même besoin, à savoir afficher le jour du mois, la raison du jour férié éventuel et les événements renseignés pour la journée. Nous ne devons avoir qu'une seule formule en A3 qui sera recopiée sur A3:G7.

Respectez TOUJOURS cette règle d'Excel. Elle impose souvent des formules plus complexes, mais garantit une maintenance aisée et une fiabilité des calculs tout au long de l'existence du classeur.

Dès lors, comment faire ? Plutôt que de calculer le décalage par rapport à la cellule précédente (à gauche ou au-dessus selon les colonnes), essayons de le calculer par rapport à A3. Le tableau des décalages devient alors celui-ci.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_102932.png

Mais, si on doit utiliser le lundi calculé et y ajouter un nombre différent de jours par case, toutes les formules sont différentes !!

Bien sûr. La subtilité va consister ici à convertir ces valeurs en éléments variables pour pouvoir les utiliser au sein d'une même formule. Or, nous avons des groupes de 7 valeurs puisque nous avons 7 jours dans une semaine. Et si nous factorisions 7, qu'est-ce que cela nous donnerait ? L'expression du décalage devient alors la suivante et nous pouvons remarquer que ces décalages obéissent à une règle que nous allons transformer en formule Excel. Nous observons que, par ligne, nous incrémentons le multiplicateur de 7 et que par colonne, nous incrémentons le produit d'une unité.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_103639.png

Le problème est donc de trouver comment exprimer les incréments en ligne et en colonne. À nouveau, Excel va nous aider en mettant à notre disposition deux fonctions, LIGNE(…) et COLONNE() qui permettent de récupérer respectivement la ligne et la colonne de la référence passée en argument. Sans argument, la fonction renverra la ligne/colonne de la cellule qui utilise la fonction. Voici un petit schéma de l'utilisation de ces fonctions. Notons que lorsque nous passons en argument une plage multicellule, c'est la ligne/colonne de la cellule supérieure gauche de la plage qui est renvoyée.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_104454.png

Ne confondez pas LIGNE() ou COLONNE() avec LIGNES() ou COLONNES(). Au pluriel, ces fonctions renvoient le nombre de lignes/colonnes de la plage passée en argument.

Or, en A3, LIGNE() renverra 3 et COLONNE() renverra 1, et nous souhaitons utiliser respectivement (0*7)+0. Nous pouvons dès lors améliorer notre algorithme de décalage en utilisant ces fonctions et utiliser la formule suivante : =((LIGNE()-3)*7)+COLONNE()-1 dans chacune des cellules du tableau. Nous avons bien maintenant une seule formule commune à toutes les cellules du tableau.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_110245.png

IV-D-4. Récupération du jour du mois

Pour récupérer le jour du mois à afficher dans la case, il nous suffit d'ajouter ce décalage au lundi calculé précédemment, et d'utiliser ce résultat comme argument de la fonction JOUR() qui renvoie le jour du mois de la date passée en argument. En décomposant l'opération, on obtient d'abord la date, puis le jour du mois.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_110904.png
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_110954.png

On aurait pu, à ce stade du développement, utiliser le format d'affichage « J » plutôt qu'une fonction, mais nous verrons par la suite que cela nous bloquait dans la récupération des textes attachés à la date de la case.

IV-D-5. Affichage du nom du mois au-dessus de la feuille d'agenda

Pour afficher le nom du mois et l'année au-dessus de l'agenda, il suffit de :

  • fusionner les cellules A1:G1 ;
  • saisir la formule =I1 pour récupérer la date saisie en I1 ;
  • modifier le format de la cellule en MMMM AAAA.
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_111641.png

IV-E. Améliorations simplificatrices

IV-E-1. Suppression des constantes dans la formule de calcul de la date

Une règle capitale d'Excel nous interdit l'utilisation de (fausses) constantes dans nos formules. La raison en est simple : une constante est rarement constante, c'est-à-dire qu'elle peut varier durant la vie du classeur, amenant à devoir remanier l'outil, lorsque l'on s'aperçoit de la variabilité de la « constante ».

Soyez toujours attentif dès lors que vous introduisez une valeur constante dans une formule et demandez-vous toujours si cette valeur ne va pas devoir être modifiée par la suite. Demandez-vous également si une modification structurelle de votre classeur va modifier cette valeur ou pas. Il en va de la pérennité et de la fiabilité de votre classeur !

Dans la formule utilisée pour récupérer le numéro de jour dans chaque case, nous avons utilisé deux constantes, déterminées par la position de la première cellule du tableau qui se situe en A3 (ligne 3 et colonne 1) :

=JOUR($I$1-JOURSEM($I$1;3)+((LIGNE()-3)*7)+COLONNE()-1). Ces constantes ont permis d'élaborer une seule formule de décalage pour l'ensemble du tableau. Ces constantes sont utilisables si l'on est absolument certain que la cellule du premier lundi ne sera jamais déplacée.

Le problème survient dès lors que l'on va insérer des colonnes ou des lignes avant le tableau, la cellule du premier lundi, initialement A3, étant alors déplacée vers la droite et/ou vers le bas, faussant tous nos calculs.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_160709.png

Pour résoudre ce problème, il suffit d'utiliser les numéros de ligne et de colonne de $A$3 pour réaliser nos décalages :

=JOUR($I$1-JOURSEM($I$1;3)+((LIGNE()-LIGNE($A$3))*7)+COLONNE()-COLONNE($A$3))

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_160408.png

Si l'on déplace l'agenda, par exemple en insérant deux colonnes et une ligne, il n'y a plus de problèmes, car les décalages sont adaptés, $A$3 devenant $C$4. Les 3 et 1 du début étant maintenant calculés 4 et 3 grâce aux fonctions LIGNE() et COLONNE().

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_160219.png

Ne me dites pas que ce cas ne risque pas de se poser. Je vous fixe rendez-vous à la fin de ce tutoriel pour vous proposer une amélioration qui, justement, va imposer l'insertion d'une colonne à gauche.

IV-E-2. Utilisation d'une formule nommée

La partie de la formule renvoyant la date, utilisée dans la formule qui affiche le jour dans la cellule va être utilisée dans plusieurs formules, notamment conditionnelles, et parfois aussi plusieurs fois au sein d'une même formule.

Pour rappel, cette partie de formule est la suivante (en gras) :

=JOUR($I$1-JOURSEM($I$1;3)+((LIGNE()-LIGNE($A$3))*7)+COLONNE()-COLONNE($A$3)).

Cela va vite devenir gênant et créer des formules inutilement longues, difficile à déboguer lors de la rédaction, surtout en mise en forme conditionnelle.

Il sera alors utile de créer une formule nommée et d'utiliser ce nom dans la suite de nos calculs. La création d'une formule nommée est simple à réaliser, surtout que la formule que nous allons utiliser ici ne gère aucune référence relative, ce qui compliquerait (très) légèrement la tâche.

Pour créer cette formule nommée, il faut procéder comme suit :

  • copier cette partie de formule (en gras dans la formule vue ci-dessus) ;
  • ouvrir le gestionnaire de noms (onglet Formules\Noms définis) ;
  • créer un nouveau nom ;
  • lui attribuer un nom (ici, JourCalculé) ;
  • dans la zone « fait référence à », saisir le signe = puis coller la formule ;
  • valider.
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_171639.png

Après quoi, il suffit de remplacer cette partie de formule par le nom créé. Avouez que c'est tout de même plus simple si nous devons utiliser plusieurs fois la date calculée dans nos formules.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_171216.png

IV-F. Conclusions de cette partie

À ce stade, nous avons notre calendrier perpétuel. Il suffit de saisir en I1 la date du premier jour de n'importe quel mois de n'importe quelle année pour que les calculs renvoient l'agenda du mois considéré. Le tableau :

  • utilise une seule formule dans toute la plage des numéros de jour ;
  • utilise une formule nommée pour plus de facilité de saisie et de lecture ;
  • peut être déplacé dans le classeur sans dégâts ;
  • n'utilise pas de VBA, juste des fonctions astucieusement combinées au sein de formules.
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_111839.png
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_111909.png

V. Mise en forme conditionnelle

V-A. Introduction

Bien entendu, notre calendrier est un peu terne et omet certaines informations comme le fait qu'un jour soit férié ou qu'un jour soit hors mois.

En effet, puisque nous avons six lignes de sept colonnes, nous aurons dans la plupart des cas des cases précédant le premier jour du mois dans la première ligne ainsi que des cases succédant au dernier jour du mois dans la dernière ligne. Parfois, toute la dernière ligne sera hors mois et il conviendra alors de la masquer pour satisfaire au cahier des charges.

De plus, le cahier des charges demande que les jours de weekend et les jours habituellement non ouvrés de la semaine soient également affichés avec un fond de couleur (pour l'instant, les samedis et dimanches).

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_113035.png

Afin d'automatiser la mise en forme, nous allons bien entendu utiliser la mise en forme conditionnelle d'Excel (MFC dans le jargon excellien).

V-B. Détecter les jours fériés

V-B-1. Algorithme

À nouveau, il n'existe pas de fonction toute faite pour déterminer qu'un jour est férié ou pas. Il existe bien des fonctions d'Excel qui permette de calculer une date à x jours ouvrés d'une date de référence, ainsi qu'une fonction qui permet de calculer le nombre de jours ouvrés entre deux dates, mais point de fonction du type ESTJOUROUVRE(…) ou ESTJOURFERIE(…).

À nouveau, avec un peu de réflexion, nous allons trouver l'algorithme qui permet de savoir si un jour est ouvré ou pas.

Bien entendu, Excel ne « connaît » pas tous les jours fériés utilisés sur la planète. Il va donc falloir utiliser une table des jours fériés, partant du principe que tout jour qui n'est pas explicitement férié est ouvré. C'est pourquoi, plus tôt dans ce cours, nous avons créé un tableau des jours fériés qu'il nous appartient de remplir d'année en année.

Excel met à notre disposition la fonction NB.JOURS.INTL(Début ; Fin ; Weekend ;Fériés) :

  • Début : date à partir de laquelle on commence à compter ;
  • Fin : date jusqu'à laquelle on compte ;
  • Weekend : soit une constante prévue pour la fonction, soit une chaine de 0 et de 1, 0 étant un jour ouvré et 1 étant un jour férié. Dans ce cas, la chaine doit comporter sept signes, le premier représentant le lundi. Par exemple, 0000011 précise que les samedis et dimanches ne sont pas ouvrés, 0100001 précisant que les mardis et dimanches ne sont pas ouvrés. Les chaines 0000000 ou 1111111 sont bien évidemment utilisables… ;
  • Fériés : liste des jours fériés.

Voici les constantes pouvant être utilisées pour représenter les jours non ouvrés de la semaine. Si les propositions ne conviennent pas, on peut utiliser la chaine des 0 et 1 comme expliqué plus haut.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_122310.png

Par rapport au tableau des jours fériés ci-contre, et en considérant les samedis et dimanches comme non ouvrés, on peut utiliser la formule suivante, où l'argument 3 (1) signifie « samedi/dimanche » :

=NB.JOURS.OUVRES.INTL(D2;D3;1;t_Feries[Date])

Pour le même résultat, nous pourrions également utiliser ceci, en renseignant le masque des jours non ouvrés de la semaine (rappel : 0 veut dire ouvré, 1 veut dire non ouvré) :

=NB.JOURS.OUVRES.INTL(D2;D3;"0000011";t_Feries[Date]).

Image non disponible

Dès lors, pour savoir si un jour est ouvré ou pas, il suffit de compter le nombre de jours ouvrés entre ce jour et lui-même. Si la fonction renvoie 1, le jour est ouvré. Si elle renvoie 0, le jour est non ouvré.

Le tableau suivant, renvoyant VRAI lorsque le jour est ouvré et FAUX lorsqu'il ne l'est pas, illustre cet algorithme :

  • le 23/04/2017 est un dimanche ;
  • le 01/05/2017 est un lundi, mais est repris dans les jours fériés ;
  • le 02/05/2017 en colonne F est un mardi ouvré ;
  • le 02/05/2017 en colonne G est un mardi non repris dans les jours fériés, mais la chaine en G1 exprime explicitement que le mardi est non ouvré.
Image non disponible

V-B-2. Mise en forme conditionnelle

Nous avons vu précédemment que nous pouvions utiliser une chaine ressemblant à 0000011 pour déterminer quel jour est habituellement chômé dans la semaine (rappel : 0 = travaillé, 1 = chômé). Il faut donc utiliser une cellule qui déterminera cette situation. Pour faire simple, je la place en $I$2.

Si nous saisissons 0000011 en I2, Excel va supprimer les 0 non significatifs. Il faut donc soit :

  • modifier le format de la cellule en TEXTE ;
  • saisir le masque binaire en le préfixant de ‘.

La mise en forme conditionnelle des jours fériés revient donc à appliquer un format particulier lorsque la formule suivante renvoie VRAI

=NB.JOURS.OUVRES.INTL(JourCalculé;JourCalculé;$I$2;t_Feries[Date])=0

En effet, s'il n'existe aucun jour ouvré entre une date et elle-même, c'est que cette date représente un jour non ouvré.

Ce n'est malheureusement pas aussi simple, et pour deux raisons.

V-B-2-a. Formule nommée

Lors de l'interprétation de la formule par Excel, JourCalculé va être compris comme étant une matrice, et NB.JOURS.OUVRES.INTL() n'accepte pas les matrices, même si elles sont des matrices singleton. L'observation de l'évaluation de la formule par Excel montre en effet que JourCalculé est une matrice, entourée par {}. Et cela nous renvoie une belle erreur #VALEUR! dans les cellules.

J'ai placé la formule dans le tableau à des fins de visualisation et pour bien comprendre comment un jour est déterminé comme férié dans le tableau. Dans l'agenda finalisé, c'est bien la formule renvoyant le jour qui sera utilisée, et améliorée.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_212135.png
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_212336.png

Qu'à cela ne tienne, nous allons simplement multiplier JourCalculé par 1 pour forcer Excel à transformer sa matrice en un nombre représentant la date. Dans l'illustration qui suit, on voit bien que, par rapport au tableau des jours fériés (illustré plus haut dans le cours) et au masque des jours ouvrés en I2, on obtient bien VRAI pour les jours non ouvrés.

La formule utilisée pour déterminer qu'un jour est férié devient donc :

=NB.JOURS.OUVRES.INTL(JourCalculé*1;JourCalculé*1;$I$2;t_Feries[Date])=0

Retenez cette façon de forcer Excel à transformer une valeur en numérique, pour autant que ce soit possible, évidemment. C'est une technique utilisée régulièrement avec Excel.
Bien que l'on pourrait utiliser +0, on rencontre plus souvent *1, par habitude et peut-être aussi à cause de la priorité de la multiplication sur l'addition.

On note ici tout l'intérêt d'avoir créé notre formule nommée pour calculer la date à afficher dans chaque cellule du tableau. Imaginez qu'il aurait fallu, sans la formule nommée, utiliser deux fois $I$1-JOURSEM($I$1;3)+((LIGNE()-LIGNE($A$3))*7)+COLONNE()-COLONNE($A$3)

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_212548.png

V-B-2-b. Mise en forme conditionnelle et références structurées

Les outils de mise en forme conditionnelle ont été considérablement enrichis à partir de XL2007, sauf, malheureusement, pour ce qui concerne les MFC formulées. Or, les outils natifs de MFC ne nous permettent pas d'arriver à nos fins. Nous voulons une MFC formulée pour que le format soit adapté lorsque la formule vue plus haut renvoie VRAI.

Mais les formules de MFC n'acceptent pas les références structurées. Pourtant, les références structurées, et donc l'utilisation des tables de données, permettent de faire grandir automatiquement les plages vers lesquelles elles pointent, ce qui est évidemment indispensable pour nous. Il n'apparaît effectivement pas comme raisonnable de devoir modifier nos MFC chaque fois que nous ajoutons un jour férié dans la liste.

Nous allons donc tromper Excel. Les MFC formulées acceptent les plages nommées. Nous allons donc créer une plage nommée qui s'appuie sur la colonne d'une table de données. Ainsi, si le tableau est modifié (ajout ou retrait de données), la plage nommée, rendue dynamique puisque s'appuyant sur le tableau, va également être redimensionnée, et par ricochet, notre MFC prendra bien la bonne plage en compte, quel que soit l'ajout ou le retrait de données.

Passons à la pratique. Pour créer une plage s'appuyant sur la colonne d'une table de données, il suffit de :

  • sélectionner la plage du tableau ;
  • lui donner un nom.

Simple, n'est-ce pas ?

Attention ! N'oubliez pas ENTER après avoir saisi le nom de la plage.

Image non disponible

La plage nommée peut être utilisée en remplacement de l'adresse de la plage dans toutes les formules qui acceptent une plage de données. Nous voyons ci-dessous que l'on peut utiliser Fériés pour compter le nombre de jours fériés.

Image non disponible

Après l'ajout d'une donnée dans la table, nous voyons que Fériés s'est bien adaptée et compte maintenant une ligne de plus.

Image non disponible

Dans la formule qui détermine qu'une date est un jour férié, on remplacera donc simplement t_Fériés[Date] par Fériés :

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-05_215757.png

La formule est prête à être utilisée dans la MFC. Il vous suffit maintenant de la copier via l'outil de gestion des MFC.

Retenez la technique. Il est souvent beaucoup plus simple d'écrire la formule qui servira pour la MFC dans la feuille Excel. Vous avez ainsi le guide habituel d'Excel pour écrire votre formule. Pensez que, lorsque vous devrez la rédiger au sein de l'outil de la MFC, rien ne vous viendra plus en aide.
Pensez également que les MFC n'acceptent pas les références structurées.

V-B-2-c. Mise en place de la mise en forme conditionnelle

La mise en forme conditionnelle formulée est rédigée par rapport à la cellule active au moment de la validation de la MFC. Cette remarque a toute sa pertinence lorsque votre formule conditionnelle utilise des références relatives.

Puisque vous avez rédigé votre formule dans la feuille de calcul (en A3 sur ma feuille), il vous suffit de la copier dans le presse-papier. Il s'agit bien ici de copier la formule et non la cellule dans laquelle elle se trouve.

Les étapes sont donc les suivantes :

  1. Copier la formule ;
  2. Sélectionner la plage du tableau concernée par la MFC (ici, A3:G8) ;
  3. Ouvrir l'outil de MFC et choisir la mise en forme conditionnelle formulée ;
  4. Coller la formule préalablement copiée ;
  5. Choisir le format à affecter aux cellules de jours fériés ;
  6. Valide la MFC.

Ces étapes sont illustrées par les copies d'écran ci-dessous.

On copie la formule saisie dans la cellule.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-06_165352.png

On sélectionne la plage concernée par la MFC, soit les six lignes de sept colonnes.

images/2017-05-06_165430.png

On crée une nouvelle MFC.

images/2017-05-06_165449.png

On choisit « utiliser une formule pour déterminer… » et on colle la formule.

images/2017-05-06_165511.png

On choisit la mise en forme qui sera appliquée lorsque la formule renvoie VRAI puis on valide les différentes boites de dialogue.

images/2017-05-06_165607.png

La mise en forme conditionnelle est placée. Dès à présent, notre calendrier perpétuel pourra afficher les jours fériés et jours non ouvrés de n'importe quel mois, pour autant bien entendu que des jours fériés soient renseignés pour le mois sélectionné.

images/2017-05-06_165634.png

Ainsi, si l'on choisit avril 2017 et que la semaine est ouvrée les lundi, mercredi, vendredi, samedi et dimanche (voir le masque saisi en I2), le tableau se met à jour immédiatement, tant pour les jours du mois que pour les jours fériés et non ouvrés.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-06_170103.png

V-C. Mise en forme conditionnelle des jours « hors mois »

Dans ce modèle, j'ai décidé de ne pas masquer les jours hors mois, c'est-à-dire les jours du mois précédent en première ligne et les jours du mois suivant sur la dernière ligne (la cinquième ou sixième selon les mois). Par contre, pour les différencier des jours du mois, nous allons les afficher dans une autre couleur.

La formule de la mise en forme conditionnelle est assez simple et peut s'exprimer ainsi :

=MOIS(JourCalculé)<>MOIS($I$1)

Nous ajouterons cette nouvelle mise en forme conditionnelle en suivant les mêmes étapes que pour la première MFC. Ici, j'ai créé une MFC qui met en gris moyen la police du numéro du jour. Le résultat sur la feuille de calcul est conforme à nos attentes.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_074343.png

V-D. Mise en forme des lignes totalement hors mois

Nous avons encore un petit problème de mise en forme. Parfois, la sixième ligne n'est pas nécessaire, car elle ne contient que des jours du mois suivant. C'est le cas pour avril 2017 par exemple, comme l'illustre la copie d'écran ci-dessus.

Et si nous poussons l'analyse un pas plus loin, nous découvrirons que la cinquième ligne peut être inutile, dans le cas où le mois de février d'une année non bissextile commence un lundi.

Le « piège » serait ici d'essayer de travailler sur le fait que l'on se trouve dans le cas d'un mois de février de 28 jours commençant un lundi. La réflexion nous amène à généraliser notre propos en regard des propriétés d'une ligne totalement hors mois et de la dernière ligne du tableau.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_171252.png

Une ligne totalement hors mois est simple à déterminer. Elle commence par un lundi qui est plus grand que le dernier jour du mois considéré. Avec notre formule nommée, il est aisé d'écrire la formule suivante :

=(JourCalculé-JOURSEM(JourCalculé;3))>FIN.MOIS($I$1;0)

Rappel : JourCalculé-JOURSEM(JourCalculé;3) permet de trouver le lundi de la semaine du jour calculé.

Il suffira donc d'appliquer une MFC formulée sur base de cette formule. La MFC spécifiera comme format une police en blanc sur un fond blanc sans bordures.

Nous pourrions appliquer la MFC uniquement sur les deux dernières lignes, mais le cahier des charges (et les bonnes règles de travail avec Excel) impose normalement d'appliquer la formule sur tout le tableau. Rappel : les cellules qui représentent une même réalité dans un tableau doivent utiliser la même formule, et cela vaut également pour les MFC.

Le résultat permet de voir que l'on a bien « masqué » les cellules par MFC.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_173730.png

En testant avec février 2021, on remarque que les deux dernières lignes sont bien masquées.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_173814.png

Le problème réside dans le fait que la bordure inférieure de la dernière ligne est manquante, ce qui n'est pas du plus bel effet.

V-E. Dernière ligne du tableau

Nous allons donc devoir déterminer quand une ligne est la dernière du tableau, et nous pouvons établir la règle suivante : est la dernière la ligne dont le lundi est dans le mois considéré et le dimanche le dernier jour du mois ou plus grand que le dernier jour du mois. La formule à utiliser en MFC sera donc :

=ET(MOIS(JourCalculé-JOURSEM(JourCalculé;3))=MOIS($I$1);(JourCalculé-JOURSEM(JourCalculé;3)+6 )>=FIN.MOIS($I$1;0))

On remarque ici que l'on peut trouver le dimanche de la semaine d'un jour donné en reculant au lundi et en ajoutant 6 (partie italique de la fonction ci-dessus).

La MFC se place comme d'habitude en sélectionnant préalablement les lignes de date du tableau.

À nouveau, on se félicitera ici d'avoir remplacé le calcul du jour par la formule nommée. Notons au passage que l'on aurait pu créer une formule nommée pour le lundi, ce qui aurait permis d'éviter de répéter l'algorithme lorsqu'on le rencontrait plusieurs fois au sein d'une même formule.

V-F. Conclusions de cette partie

Grâce à ces ajouts de MFC, nous avons couvert les trois cas de figure que nous pouvons rencontrer.

En mai 2017, nous avons besoin de cinq lignes.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_175132.png

En février 2021, quatre lignes suffisent.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_175201.png

En janvier 2017, nous avons besoin de six lignes.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_175228.png

Nous voyons que la mise en forme conditionnelle d'Excel permet de faire des choses assez puissantes. Les jours fériés sont grisés, les chiffres des jours hors mois sont grisés, les lignes indésirables sont masquées et la dernière ligne possède la bonne bordure.

Associées aux formules utilisées pour le calcul du jour et l'utilisation d'une formule nommée, nous avons totalement automatisé la présentation de notre calendrier. À ce stade, les seules choses qui doivent être réalisées pour utiliser le calendrier sont :

  • saisie des jours fériés dans le tableau idoine ;
  • sélection du premier jour du mois pour lequel nous souhaitons créer notre tableau.

Toutes ces automatisations ont été réalisées sans lignes de code VBA.

VI. Ajout des données dans les cases du calendrier

VI-A. Jours fériés

Le plus dur est derrière nous. Il nous reste deux choses à réaliser, dont l'ajout de la raison du jour férié, spécifié dans la seconde colonne du tableau des jours fériés. Nous allons coller cette raison à la suite de la formule qui calcule le jour du mois considéré.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_181438.png

Vous comprenez mieux ici pourquoi je n'ai pas affiché le jour dans les cases en adaptant le format, mais en utilisant la fonction MOIS(). Il était ici impossible d'utiliser le format puisque d'autres informations que le jour du mois doivent être affichées dans la formule.

Un simple RECHERCHEV() permet cela, dont nous viendrons coller le résultat à la suite du jour du mois dans chaque cellule.

Si vous n'êtes pas à l'aise avec RECHERCHEV(), consultez mon tutoriel sur le sujet.

Le problème est que si le jour n'est pas férié, la recherche n'aboutit pas et on se prend une erreur #N/A. L'illustration suivante montre que l'on a bien récupéré les jours fériés, mais le problème des #N/A survient pour tous les jours non fériés.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_194501.png

Nous allons simplement capturer l'erreur avec SIERREUR() qui renverra une chaine vide en cas d'erreur et le tour sera joué.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_194659.png

VI-B. Événements

Nous allons évidemment procéder de la même manière pour récupérer les événements. RECHERCHEV() pourrait bien entendu convenir, mais cette fonction limite les possibilités de recherche et contraint l'utilisateur à former son tableau en respectant l'organisation des colonnes. Je l'ai illustrée dans le point précédent, car c'est une fonction qui est rencontrée dans tout classeur professionnel bien construit, mais je lui préfère de très loin la combinaison de INDEX() et EQUIV().

INDEX(Plage ; Position) renvoie l'élément qui se trouve à la position renseignée dans la plage renseignée. EQUIV(Valeur Cherchée ; Plage ; Type de recherche) permet de renseigner la position d'un élément dans une plage. En utilisant le résultat de EQUIV() comme argument Position d'INDEX(), on peut rechercher, par exemple, un élément dans une colonne se trouvant en vis-à-vis d'un autre dans une autre colonne.

osD:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_200116.png

Je ne détaille pas ici les différentes syntaxes d'INDEX() ni les arguments possibles pour EQUIV, pas plus que la liste exhaustive des avantages à utiliser ces fonctions à la place de RECHERCHEV(). Citons simplement deux avantages parmi d'autres :

l'ordre des colonnes n'a plus aucune importance ;

la recherche peut s'effectuer sur des matrices.

Nous allons donc ajouter la partie de formule suivante, en utilisant bien entendu SIERREUR() pour les jours sans événements :

=JOUR(JourCalculé) & SIERREUR(" " & RECHERCHEV(JourCalculé;t_Feries;2;FAUX);"") & SIERREUR(CAR(10) & INDEX(t_Evénements[Evénement];EQUIV(JourCalculé;t_Evénements[Date];0));"")

Remarquons l'utilisation de la fonction CAR(10) qui insère un saut de ligne pour une meilleure présentation

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_200828.png

Pour que tout soit parfait, nous allons améliorer l'affichage en :

  • renvoyant à la ligne automatiquement ;
  • agrandissant un peu la hauteur des cellules ;
  • alignant les cellules à gauche.

VI-C. Conclusions de cette partie

Voilà notre calendrier perpétuel terminé. Tout y est : les jours fériés sont grisés, les numéros des jours hors mois sont en gris également, les lignes superflues sont masquées pas mfc, la raison du jour férié apparaît et les événements de la journée aussi.

Pour utiliser votre calendrier perpétuel, vous n'avez plus besoin de toucher à la structure, de réaliser des copier-coller hasardeux, etc. Il vous suffit d'alimenter des tables de données avec les jours fériés et les événements, puis de saisir la date du premier du mois en $I$1 pour que le calendrier s'affiche avec les bonnes données.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_201238.png

VII. Améliorations possibles

VII-A. Ajout du numéro de semaine

Le numéro de semaine a été, jusqu'à la version 2007 comprise, un casse-tête pour les Européens. Souvenez-vous de la fonction JOURSEM(…) qui démarre par défaut la semaine un dimanche, système américain oblige.

Pour le calcul du numéro des dates, c'est pareil. Les Américains ne calculent pas comme nous. Aux USA, la première semaine de l'année est celle qui contient le 1er janvier. En Europe, la première semaine de l'année est la première à contenir quatre jours de janvier. Pour faire simple et par déduction, c'est celle qui contient le premier jeudi de janvier.

Excel expose depuis longtemps la fonction NO.SEMAINE() qui, jusqu'à la version 2007 comprise, ne prévoyait pas l'exception européenne. Il fallait alors utiliser une formule savante pour calculer le numéro européen de la semaine.En 2010, NO.SEMAINE() a été adaptée et propose de passer un paramètre supplémentaire spécifiant que le calcul suit la norme européenne. En 2013, Excel a enfin exposé la fonction NO.SEMAINE.ISO() qui calcule directement selon la norme européenne.

Pour une date en A3, voici les trois formules possibles :

2007 : =ENT(MOD(ENT((A3-2)/7)+0.6;52+5/28))+1 ;

2010 : =NO.SEMAINE(A3;21) ;

2013 : =NO.SEMAINE.ISO(A3)

Avec la formule 2007, il faut ajouter 1462 à la date pour un calcul correct si vous travaillez avec l'option Excel Calendrier 1904.

Cela mis à part, il sera simple d'ajouter une colonne pour le numéro de semaine. Nous avions déjà prévu l'ajout d'une colonne à la gauche de notre calendrier. Vous comprenez maintenant pourquoi j'ai remplacé les constantes par des variables.

Petit rappel de la fonction pour retrouver le lundi de la ligne sur laquelle on se trouve par rapport au premier jour du calendrier :

=$J$1-JOURSEM($J$1;3)+((LIGNE()-LIGNE($B$3))*7)

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_213811.png

Il suffira d'inclure cette formule dans une de celle vue plus haut en fonction de votre version et vous aurez le numéro de semaine. On finalisera la présentation en encadrant les cellules du numéro de semaine

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_213941.png

Ici, on va devoir ajouter deux mfc spécifiques pour que le numéro de semaine de la dernière ligne du tableau soit encadrée comme les autres colonnes et pour que la cellule soit « masquée » si elle est sur une ligne masquée du tableau.

La formule pour masquer la cellule sera :

=$J$1-JOURSEM($J$1;3)+((LIGNE()-LIGNE($B$3))*7)>FIN.MOIS($J$1;0)

Celle pour déterminer si l'on est sur une dernière ligne est :

=ET(MOIS($J$1-JOURSEM($J$1;3)+((LIGNE()-LIGNE($B$3))*7))=MOIS($J$1);$J$1-JOURSEM($J$1;3)+((LIGNE()-LIGNE($B$3))*7)+6>=FIN.MOIS($J$1;0))

En ajoutant deux MFC avec ces formules pour la plage A3 :A8, notre tableau sera correctement formaté.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_215841.png

En créant une formule nommée LundiCalculé avec la formule suivante :

=$J$1-JOURSEM($J$1;3)+((LIGNE()-LIGNE($B$3))*7)

Il est plus simple de rédiger la formule du numéro de semaine et les formules des MFC :

  • No de semaine : =NO.SEMAINE.ISO(LundiCalculé) ;
  • MFC de masquage de ligne : LundiCalculé>FIN.MOIS($J$1;0) ;
  • MFC de dernière ligne : =ET(MOIS(LundiCalculé)=MOIS($J$1);LundiCalculé+6>=FIN.MOIS($J$1;0))

VII-B. Vérification de la saisie de la date

Un problème surviendra si l'on ne saisit pas une date de la première semaine du mois pour élaborer le calendrier.D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_221342.png

Notre calendrier commence au 22/05, qui est bien le lundi de la semaine du 23/05, mais le calendrier n'affiche plus que deux lignes, à cause de nos MFC qui masquent les lignes hors mois.

Dès lors, on pourrait, avec une simple règle de validation formulée, tester que l'utilisateur a saisi une date correspondant au premier jour d'un mois.

La validation de saisie formulée fonctionne sur le même principe que la mise en forme conditionnelle formulée. La saisie est validée si la formule renvoie VRAI. Pour la date de début de calendrier, qui est maintenant saisie en J1 (si vous avez ajouté la colonne de semaine comme moi), il suffit de la formule suivante pour la validation pour la cellule J1 :

=JOUR(J1)=1

Nous pourrons évidemment modifier le texte d'alerte pour informer l'utilisateur de sa méprise.

Pour placer la validation, il faut :

  • sélectionner J1 ;
  • via Données\Outils de données\Validation des données ;
  • choisir Personnalisé dans la liste déroulante ;
  • saisir la formule ;
  • éventuellement, modifier le texte d'alerte.
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_221912.png
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_222020.png

Attention : ne modifiez pas le style de l'alerte, car l'utilisateur pourrait alors valider une date non conforme.

Lorsque la règle de validation est placée, une saisie erronée est signalée à l'utilisateur qui devra soit annuler sa saisie, soit la corriger.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-07_222144.png

VII-C. Calcul de la date du 1er du mois suite à saisie du mois et de l'année

Il serait également possible d'utiliser une liste de validation dans une cellule, une validation de l'année dans une deuxième et une recomposition de la date dans une troisième cellule.

VII-C-1. Liste de validation du mois

Ce n'est pas bien compliqué. On pourrait évidemment énoncer la liste in extenso dans l'outil de validation, mais ce serait contraire à la règle incontournable d'Excel qui veut que l'on ne mette pas de constante dans nos formules (et outils…). De plus, nous avons besoin de cette liste des mois deux fois, une fois pour la liste de validation, une fois pour déterminer le numéro du mois choisi. Il est donc plus judicieux de saisir cette liste dans un tableau structuré.

Nous allons donc créer un troisième tableau reprenant les noms des mois, nommer une plage qui s'appuie sur cette liste et créer la liste déroulante sur base de cette plage nommée.

Les versions récentes d'Excel permettent de s'appuyer sur une plage d'une autre feuille pour créer la liste de validation, mais pas sur une référence structurée. Notons que lorsque l'on appuie la liste de validation sur une plage d'une autre feuille, la liste n'est pas dynamique et ne s'adapte pas, en taille, aux modifications dans la plage (ajout ou retrait). Pour que la liste s'adapte, il est nécessaire de l'appuyer sur une plage nommée. Dans le cas précis qui nous occupe, il n'y aura pas de suppression ou d'ajout de données tant qu'il y a douze mois dans une année, mais j'ai pris l'habitude d'appuyer toutes mes listes déroulantes sur des plages nommées.

La création du tableau structuré ne pose plus de problèmes :

Saisie des données :

  • conversion en tableau via Insertion\Tableaux\Tableau ;
  • nommage du tableau. Ici, j'ai appelé le tableau t_Mois ;
  • création d'une plage nommée appelée NomsMois sur base de la colonne de données du tableau.
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_053910.png

N'oubliez pas de valider le nom de la plage avec ENTER.

Nous pouvons créer la liste de validation :

  • sélectionnons la cellule qui contiendra cette liste ;
  • ajoutons une validation via Données\Outils de données\Validation des données ;
  • choisissons Liste dans la liste déroulante ;
  • saisissons le nom de notre plage précédé du signe = dans le contrôle Source ;
  • éventuellement, adaptons le message d'erreur.

Pour saisir le nom de la plage dans le contrôle, nous pouvons utiliser F3 qui affiche la liste des noms disponibles. Ce raccourci est valable dans d'autres situations, notamment pour la saisie d'un nom (plage nommée ou formule nommée) dans une formule.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_054629.png

La cellule présente maintenant sa liste de validation et son message d'erreur lors d'une saisie erronée.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_055006.png
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_055020.png

VII-C-2. Validation de l'année

Pour valider l'année, l'outil de validation nous permet de borner la saisie à une plage d'entiers. Les années pouvant aller de 1900 à 9999, nous allons donc très simplement mettre la validation en place, par exemple pour J2.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_055420.png

Il ne nous reste plus qu'à recomposer la date en J3, grâce à la formule suivante :

=DATE(J2;EQUIV(J1;NomsMois;0);1)

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_055710.png

Nous aurions pu ici créer une formule nommée pour stocker le premier jour du mois, mais cela nous aurait imposé de modifier nos formules et nos MFC. Nous aurions pu aussi recomposer la date en B2 (B2:H2 fusionnées) pour afficher la date sous le format MMMM AA.
À ce stade du tutoriel, nous supposons que ces transformations sont comprises et réalisables sans devoir les mettre en œuvre.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_060224.png

VII-D. Gestion des calendriers de plusieurs personnes

À relativement peu de frais, il est possible d'utiliser le même calendrier pour afficher les événements relatifs à une personne spécifique.

Il nous suffit :

  • de créer un tableau structuré des personnes concernées ;
  • d'ajouter une colonne à notre tableau des événements ;
  • d'ajouter une liste de saisie des personnes dans la feuille du calendrier ;
  • de modifier la formule qui récupère la liste des événements dans le calendrier ;
  • d'adapter la formule qui affiche le mois et l'année au-dessus du calendrier pour y ajouter le nom souhaité.

Vu la façon dont nous avons créé le calendrier jusqu'à maintenant en respectant les règles d'Excel, cette évolution va pouvoir être réalisée sans trop de douleurs.

VII-D-1. Création de la liste des noms

On va bien entendu créer un tableau structuré. La petite « astuce » consiste ici à ajouter une colonne qui précisera, par personne, le régime hebdomadaire de travail.

N'oublions pas de nommer la plage de la première colonne du tableau, elle permettra de créer une liste de saisie dans le calendrier

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_063118.png

VII-D-2. Ajout de la colonne dans le tableau des événements

Nous pouvons insérer une colonne dans notre tableau t_Evénements pour permettre la saisie de la personne concernée par l'événement repris sur la ligne. Idéalement, nous créerons une liste de validation qui s'appuiera sur la plage nommée créée précédemment.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_063434.png

Bien entendu, sans VBA, nous allons devoir répéter les événements qui concernent plusieurs personnes.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_063944.png

Nous aurions pu envisager une autre conception de classeur, avec un tableau unique pour les événements communs (un peu comme pour les jours fériés) et un tableau précisant à qui se rattache un événement, puis concaténer les recherches dans les trois tableaux au sein du calendrier. À nouveau, la bonne conception du classeur dès le départ permet ce genre d'adaptations assez facilement. Je pense que vous êtes maintenant armé pour réaliser ces adaptations par vous-même si vous le souhaitez.

VII-D-3. Adaptation du calendrier

Dans le calendrier, nous avons quelques modifications à réaliser :

  • ajout de la cellule de saisie de la personne ;
  • récupération de son régime de travail hebdomadaire ;
  • modification du titre du calendrier ;
  • adaptation de la formule de récupération des événements journaliers.

L'ajout de la cellule de saisie et de la liste de validation ne devrait plus poser de problèmes. Nous avons déjà réalisé cette opération dans le tableau des événements.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_064604.png

La récupération du régime hebdomadaire s'effectue par RECHERCHEV ou INDEX/EQUIV… Nous avons déjà vu cela aussi. Maintenant, le choix d'une personne pour le calendrier adapte automatiquement son régime hebdomadaire dans le calendrier.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_064830.png

La modification du titre ne pose pas de problème non plus.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_065123.png

La récupération des événements va s'avérer un peu plus complexe. En effet, nous avons maintenant deux critères pour récupérer les événements : la date ET la personne concernée. C'est ici que nous allons voir les possibilités accrues de l'utilisation de INDEX/EQUIV par rapport à RECHERCHEV. Deux techniques s'offrent à nous selon que nous voulons une colonne supplémentaire dans notre tableau des événements ou pas.

VII-D-3-a. Colonne supplémentaire dans le tableau des événements

Puisque nous allons devoir rechercher sur la date et la personne, nous allons créer une colonne qui concatène (qui colle) les informations des deux cellules, sans nous préoccuper de l'affichage que cela donne.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_065721.png

Nous pouvons maintenant modifier simplement la formule dans le calendrier pour récupérer les événements (les modifications apparaissent en italique) :

=JOUR(JourCalculé) & SIERREUR(" " & RECHERCHEV(JourCalculé;t_Feries;2;FAUX);"") & SIERREUR(CAR(10) & INDEX(t_Evénements[Evénement];EQUIV(JourCalculé & $J$1 ;t_Evénements[RechercheCalendrier ];0));"")

Notre calendrier est maintenant adapté pour récupérer les événements en fonction de la personne choisie.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_070113.png
D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_070220.png

VII-D-3-b. INDEX/EQUIV sans colonne supplémentaire

INDEX et EQUIV permettent de travailler sur des matrices, a contrario de RECHERCHEV qui ne peut travailler que sur des plages. Dès lors, ces fonctions peuvent être utilisées au sein d'une formule matricielle. Une telle formule travaille en boucle sur les valeurs des matrices en considérant, à chaque boucle, les valeurs qui sont à des positions identiques dans les matrices utilisées.

Une formule matricielle permet d'éviter des colonnes de construction comme celle que nous avons dû construire pour concaténer la date d'un événement avec le prénom associé.

Une formule matricielle doit être validée par les touches CTRL+SHIFT+ENTER. Excel ajoute lui-même les accolades lors de la validation. Il ne faut donc pas les saisir.

L'illustration suivante expose la façon dont la formule est rédigée. C'est au niveau de EQUIV que la modification est effectuée. Nous voyons que nous n'utilisons plus la colonne dans laquelle la date et le prénom ont été concaténés, mais nous concaténons les matrices directement dans EQUIV.

Image non disponible

VII-E. Impression de la feuille du calendrier

Souvent, il sera nécessaire d'imprimer la page du calendrier. L'idée est de le faire tenir sur une page, en portrait ou paysage selon les besoins, et d'ajuster hauteurs de lignes et largeurs de colonnes. Il faudra, visuellement, que le rapport hauteur/largeur soit +/- celui de la hauteur/largeur de votre page selon votre orientation. Vous pourrez alors ajuster l'affichage à une page en hauteur et en largeur.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_214027.png

Il vous suffira alors de définir la zone d'impression après avoir sélectionné la plage entière du calendrier. N'oubliez pas de sélectionner toute la zone du calendrier (même les dernières lignes vides en fonction du mois sélectionné), mais d'exclure de la sélection la colonne de saisie des paramètres.

D:\Article_Dvp\documents\calendrier_perpetuel_excel\images\2017-05-08_214146.png

VII-F. Autres améliorations envisageables

On pourrait, dans le désordre :

  • ajouter le nom du Saint du jour (mais quelle référence prendre ?) ;
  • tenter d'éviter la saisie annuelle des jours fériés fixes en déterminant la liste « une fois pour toutes » ;
  • tenter de calculer les fêtes chrétiennes mobiles ;
  • ajouter les cycles lunaires ;

Mais, d'une part, il faut bien se mettre des limites à un moment donné, et d'autre part, la complexification des formules, puisque nous voulons travailler sans VBA, ne se justifie pas par rapport à la simplicité d'une saisie annuelle des jours fériés dans le tableau idoine, surtout si l'on ajoute à cette simplicité la souplesse des tables de données Excel qui permettent des modifications à la volée en fonction du pays, de l'entreprise, bref du besoin final.

Je rappellerai donc ici, pour justifier mon choix de ne pas aller plus loin dans le développement du calendrier, que les objectifs du tutoriel sont de créer un calendrier perpétuel et surtout, de montrer les techniques à mettre en œuvre et comment les mettre en œuvre pour créer un classeur Excel qui respecte les règles de bonne conception.

VIII. Conclusions

Nous voici à la fin de ce tutoriel. Avant sa lecture, vous auriez peut-être créé vos calendriers avec un fichier par année et douze feuilles dedans, vous « amusant » à saisir les données dans la bonne feuille, et à nettoyer votre calendrier en début d'année (en omettant probablement de passer l'éponge partout) pour préparer vos feuilles mensuelles.

Vous aurez appris dans ce tutoriel à :

  • concevoir votre fichier en fonction des règles de l'art ;
  • utiliser des fonctions de date et les combiner dans des algorithmes pour calculer d'autres dates ;
  • utiliser des tableaux structurés pour organiser vos données ;
  • utiliser la position d'une cellule pour calculer des valeurs liées à cette position ;
  • créer des mises en forme conditionnelles formulées ;

J'espère que vous pourrez transférer ces notions dans la création de vos classeurs personnels et professionnels. N'hésitez pas à me faire part de vos remarques dans la discussion liée à ce tuto.

IX. Fichiers joints

En bonus, je vous propose les fichiers qui ont servi à l'élaboration de ce tuto. Les versions proposées tiennent compte des différences relatives à la gestion du numéro de semaine :

X. Remerciements

Je remercie Jean-Philippe André, User, Laurent Ott, Chtulus et Gaby277 pour leurs conseils techniques.

Merci à Claude Leloup pour la relecture orthographique.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2017 Pierre Fauconnier. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.