Exercice pratique : Calendrier perpétuel Excel

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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é.

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.

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.

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.

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.

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.

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.

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

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

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.

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.

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.

À 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.

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

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é.

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.

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.

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)

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.

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.

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.

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 :

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.

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

On crée une nouvelle MFC.

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

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

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é.

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.

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.

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.

La cellule présente maintenant sa liste de validation et son message d'erreur lors d'une saisie erroné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.

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

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

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.

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

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.

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

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.

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.

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.

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

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.

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.

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.