Calendrier Excel avec Week-End et jours fériés

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


Sujets que vous pourriez aussi aimer :


Calendrier Annuel avec Jours fériés

Nous proposons de bâtir une application Excel, sans code VBA, permettant la construction automatique de calendriers annuels, avec repérages des Week-End et des jours fériés. La simple sélection de l'année par le biais d'une liste déroulante doit offrir le calendrier annuel complet mis en page, tel que l'illustre la capture ci-dessous.



Les mois de l'année sont listés en colonnes et pour chacun d'eux, les jours du mois sont énumérés en abrégé. Des formules permettent d'adapter l'énumération en fonction des jours que compte le mois en cours, y compris pour les années bissextiles.

Source et présentation du concept
Nous proposons de débuter les travaux à partir d'un classeur source proposant déjà une certaine structure et offrant la liste des jours fériés pour les prochaines années. Certains changent en effet de date d'une année sur l'autre.

• Télécharger le classeur calendrier-annuel-excel.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de deux feuilles : Calendrier et Jour_feries. Nous reviendrons sur la seconde en temps voulus.



En consultant la première feuille, vous remarquez que nous sommes encore loin du résultat à atteindre. Mais toutes les techniques que nous allons mettre en oeuvre demeurent relativement simples.

La ligne 5 affiche le premier jour pour chaque mois de l'année dans un format abrégé. La colonne B énumère tous les potentiels jours d'un mois, soit jusqu'au 31^ème. Il s'agit de cellules intermédiaires qui serviront d'aide à la construction. En fonction de tests validés par des calculs simples, la mise enforme conditionnelle d'Excel fera réagir automatiquement les cellules pour faire apparaître le calendrier. La cellule C3 doit permettre à l'utilisateur de désigner une année pour la construction du calendrier, à partir d'une liste déroulante.



La feuille Jour_feries recense les années proposées en ligne 4. Elle représente la liste des jours fériés en France jusqu'en 2022.

Choix de l'année
Pour commencer simplement, nous devons donc construire une liste déroulante des années disponibles, en cellule C3 de la feuille Calendrier.

• Sélectionner la cellule C3 de la feuille Calendrier,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Outils de données, cliquer sur le bouton Validation de données,
• Dans la boîte de dialogue qui apparaît, activer l'onglet Options,
• Dans la section Autoriser, choisir Liste,
• Puis cliquer dans la zone de saisie Source pour l'activer,
• Cliquer alors sur l'onglet Jours_feries en bas de la fenêtre Excel pour afficher sa feuille,
• Puis sélectionner la liste des années, soit la plage de cellules C4:H4,
• Valider ces réglages en cliquant sur le bouton Ok de la boîte de dialogue,

De retour sur la feuille, vous constatez la présence d'une petite flèche en cellule C3. Si vous cliquez dessus, la liste des années se déroule. Il ne reste plus qu'à sélectionner l'une d'entre elles pour l'afficher dans la cellule.

La cellule E2 est fusionnée sur une hauteur de 2 lignes et sur la largeur du calendrier, jusqu'en colonne N. Elle doit présenter le titre de la feuille. Mais selon le principe du calendrier, elle ne doit apparaître que si une année est bien définie. Dans le cas contraire, la cellule doit rester vierge. Et si le titre s'affiche, il doit automatiquement être mis en forme. Nous devons commencer par réaliser un calcul conditionnel, avec la fonction Si d'Excel.

• Cliquer sur la cellule E2 pour la désigner et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivie d'une parenthèse, soit Si(,
• Sélectionner la cellule de l'année en cliquant sur C3,
• Puis, saisir le critère suivant pour vérifier qu'elle n'est pas vide : <>'',
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir le titre suivant entre guillemets : 'Calendrier pour l'année ', sans oublier l'espace avant de fermer les guillemets,
• Enfoncer la touche 1 en haut du clavier pour inscrire l'opérateur de concaténation (&),
• Puis, cliquer de nouveau sur la cellule C3 pour assembler l'année avec le texte,
• Taper un nouveau point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour conserver la cellule vide lorsque l'année n'est pas définie,
• Valider le calcul par la touche Entrée du clavier,

Si une année est définie par le biais de la liste déroulante, le titre apparaît. Si vous supprimez son contenu, il disparaît. La formule conditionnelle que nous avons bâtie est la suivante :

=SI(C3<>'';'Calendrier pour l'année '& C3;'')

Ce titre doit apparaître mis en forme pour être en cohérence avec le reste de la présentation à suivre. Cette mise en forme ne peut pas être définie intégralement en amont à cause des bordures qui persisteraient même en cas de cellule vide. Nous devons donc lui appliquer un format dynamique qui se déclenche lorsque le contenu de la cellule est rempli.

• Sélectionner de nouveau la cellule E2,
• Cliquer sur l'onglet Accueil en haut de la fenêtre Excel pour activer son ruban,
• Sur la droite dans la section Styles, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue, choisir le type de règle : Utiliser une formule pour...,
• Cliquer alors dans la zone de saisie située juste en dessous pour l'activer,
• Taper le symbole = puis cliquer sur la cellule E2 de la feuille Calendrier,
• Poursuivre la saisie avec le critère suivant : <>'', pour vérifier qu'elle n'est pas vide,
• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la nouvelle boîte de dialogue qui apparaît, activer l'onglet Remplissage,
• Définir une couleur de fond assez pâle comme un violet léger,
• Activer l'onglet Police et choisir du Gras pour le texte,
• Avec la liste déroulante, choisir une couleur prononcée comme un violet vif,
• Activer l'onglet Bordures et choisir un type de trait continu de la même couleur que le texte,
• Cliquer sur le bouton Contour pour l'appliquer à la cellule.
• Valider par Ok pour revenir sur la première boîte de dialogue,

Un aperçu du format est donné lorsque le critère que nous avons saisi est vérifié. Les manipulations que nous venons de paramétrer consistent à appliquer un fond, une couleur de texte et une bordure de cellule lorsque le titre est présent en E2, soit lorsque l'année est définie en C3 par enchaînement de critères.

De retour sur la feuille, vous constatez que la mise en forme s'applique à la cellule.

• Sélectionner la cellule C3 et enfoncer la touche Suppr pour effacer l'année,

Le titre disparaît et par voie de conséquence, sa mise en forme se neutralise.

• Sélectionner de nouveau une année avec la liste déroulante,

Le titre réapparaît aussitôt, concaténé avec l'année choisie dans sa mise en forme dynamique.



C'est le même principe qui nous permettra de faire réagir les cellules du Calendrier afin de le bâtir au fur et à mesure.

Dates du calendrier par calculs Excel
Il s'agit maintenant de reconstruire toutes les dates du calendrier en fonction du mois sélectionné par l'utilisateur. Le nombre de jours proposés ne doit pas dépasser la limite du mois bien sûr. Et certaines spécificités sont à considérer. Le mois de Février compte 28 jours sauf les années bissextiles pour lesquelles il en compte 29. La fonction Excel Date permet de reconstruire une date complète en fonction de l'année, du mois et du jour, tous trois passés en valeurs numériques, selon la syntaxe suivante :

=Date(Année ; Mois ; Jour)

L'information sur l'année est fournie par la liste déroulante. L'information sur le jour est donnée par la colonne B, judicieusement incrémentée. L'information sur le mois peut être trouvée grâce à la ligne 5 en appliquant la fonction Excel Mois sur la date fournie. La fonction Mois retourne en effet la valeur du mois sous forme d'un nombre entier . Bien entendu, ce calcul ne peut être entrepris que si l'année est définie par l'utilisateur. Nous devons donc l'inclure dans une fonction conditionnelle Si.

• Sélectionner la première cellule du calcul, soit C7 puis taper le symbole =,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Désigner la cellule sur laquelle vérifier le critère, soit C3,
• Enfoncer la touche F4 du clavier pour la figer,
• Saisir le critère suivant : <>'', pour vérifier qu'elle n'est pas vide,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir le nom de la fonction pour reconstruire la date suivi d'une parenthèse, soit Date(,
• Sélectionner l'année en premier argument, soit la cellule C3,
• Enfoncer la touche F4 du clavier pour la figer,
• Taper un point-virgule pour passer dans l'argument du numéro du mois,
• Saisir le nom de la fonction pour convertir une date en numéro de mois, soit Mois(,
• Sélectionner la première date en ligne 5, soit la cellule C5,
• Fermer la parenthèse de la fonction Mois et taper un point-virgule,
• Sélectionner le numéro du jour en cliquant sur la cellule B7,
• Enfoncer la touche F4 du clavier de manière à la figer,
• Fermer la parenthèse de la fonction Date et taper un point-virgule pour passer dans la branche Sinon de la fonction Si,
• Taper deux guillemets pour garder la cellule vide lorsque l'année n'est pas spécifiée, soit '',
• Fermer la parenthèse de la fonction Si et valider le calcul par CTRL + Entrée,

Le raccourci CTRL + Entrée permet de valider une formule tout en conservant la cellule active, afin par exemple de pouvoir le répliquer sans devoir resélectionner la cellule. Nous avons intégralement figé les cellules C3 et B7 de l'année car le calcul que nous allons reproduire sur les colonnes de droite, doivent toujours y faire référence. Elle ne doit donc pas se déplacer. La formule que nous avons construite est la suivante :

=SI($C$3<>''; DATE($C$3; MOIS(C5); $B7);'')

Comme le format de date avait été paramétré en amont, nous obtenons bien le résultat de la date reconstruite sous forme abrégée. Le format numérique des cellules a en effet été personnalisé sous la forme jjj jj, trois J pour l'abréviation et deux pour le numéro.

• Tirer la poignée du calcul sur la droite jusqu'au mois de Décembre, donc jusqu'en N7,

Comme nous avons judicieusement figé les cellules de référence, la formule se réplique sur chaque colonne en adaptant le calcul. Nous obtenons ainsi les premiers jours de chaque mois, reconstitués sous forme de date au format abrégé. Et comme ce calcul se déclenche seulement lorsque le critère est vérifié, si vous supprimez l'information de l'année en C3, toutes les dates disparaissent à l'instar du titre de la feuille.

Avant de poursuivre, nous proposons de construire les entêtes du tableau en ligne 6, soit les noms de chaque mois en toutes lettres. La fonction Excel Texte permet de transformer une donnée en chaîne de caractères selon un format qui lui est passé en argument. Sa syntaxe est la suivante :

=Texte(date ; format_date)

• En cellule C6, taper la formule suivante :

=SI($C$3<>''; NOMPROPRE(TEXTE(C5;'mmmm')); '')

• Puis, la répliquer sur la largeur du calendrier, soit jusqu'en N6,

Tout d'abord et comme précédemment, nous vérifions le critère sur l'année en C3 que nous n'oublions pas de figer afin de répliquer le calcul dans la foulée. La fonction Texte appliquée sur la date en C5 permet de retourner seulement l'information du mois convertie en texte, grâce au format (mmmm) passé en second paramètre. Elle est elle-même incluse dans la fonction NomPropre qui permet de basculer les premières lettres de chaque mot en majuscules.



Il s'agit maintenant de reconstruire toutes les dates pour chaque mois de l'année définie, sans dépasser sa capacité. L'astuce consiste à vérifier que le mois de la date que nous sommes en train de reconstruire est bien identique au mois du premier jour inscrit sur la première ligne, sans oublier de vérifier si l'année est bien définie par l'utilisateur. C'est la technique que nous avions utilisée dans la formation pour créer des plannings horaires automatiquement afin de recréer les jours du mois.

• Pour ce faire, en cellule C8, reproduire la formule suivante :

=SI(C$6<>''; SI(MOIS(DATE($C$3;MOIS(C$5);$B8)) = MOIS(C$7); DATE($C$3;MOIS(C$5);$B8); ''); '')

Deux fonctions Si sont imbriquées car deux critères doivent être validés en cascade. Tout d'abord, pour vérifier la présence de l'année en C3, nous choisissons de poser le critère sur la présence du mois en ligne 6, soit sur la cellule C6. Nous prenons soin de figer cette cellule en ligne mais pas en colonne. C'est la raison pour laquelle le dollar ne figure que devant l'indice de ligne (C$6). Lorsque nous répliquerons le calcul sur la droite, le critère devra bien considérer la cellule du mois correspondant. En revanche, lorsque nous répliquerons le calcul sur les lignes du dessous, le critère devra toujours être vérifié par rapport au mois qui ne bouge pas, sur la ligne 6 donc.

Ensuite, tout en refaisant le calcul de la date pour l'année, le mois en cours et le jour en cours (DATE($C$3;MOIS(C$5);$B8)), nous en comparons le mois avec celui de la date reconstruite sur la ligne du dessus (=MOIS(C$7)). Ce critère, s'il est vérifié dans la seconde fonction Si permettra d'indiquer que la fin du mois n'est pas atteinte. Notez que les cellules C5 et C7 sont figées seulement en ligne pour les mêmes raisons que celles que nous avons évoquées pour la cellule C6. En revanche la cellule B8 du numéro du jour est figée seulement en colonne ($B8). En effet, lorsque le calcul sera reproduit vers le bas, il faudra bien considérer les jours suivants. Elle doit donc se déplacer en ligne. Mais lorsque le calcul sera reproduit en colonnes, pour les autres mois, il faudra bien continuer de considérer la valeur du jour en B8. Elle ne doit donc pas se déplacer en colonne.

Si les mois sont bien équivalents donc, nous reconstruisons la date selon les informations numériques de l'année, du mois et du jour (DATE($C$3;MOIS(C$5);$B8)), comme nous l'avions fait sur la cellule de la ligne du dessus. Dans le cas contraire, nous conservons la cellule vide (''), puisque la fin du mois est atteinte. Puis dans le sinon de la première fonction Si, nous utilisons à nouveau les double-guillemets (''), afin de conserver la cellule vide lorsque l'année n'est pas définie par l'utilisateur.

• Tirer la poignée de ce calcul sur la droite pour l'ensemble des mois, soit jusqu'en N8,
• Puis, la sélection étant toujours active, double cliquer sur la poignée,

Nous reproduisons ainsi le calcul sur l'ensemble des lignes du tableau pour toutes les colonnes, avec une seule action.



Comme vous le constatez en affichant le bas du tableau, nos calculs ont parfaitement détecté les derniers jours de chaque mois de l'année spécifiée. Chaque plage de dates a parfaitement été reconstruite.

• En C3, sélectionner l'année 2020 et afficher de nouveau la fin du tableau,

L'année 2020 est bissextile et nos calculs se sont adaptés puisque désormais le mois de Février est automatiquement prolongé jusqu'au 29^ème jour.

Mise en valeur du calendrier - Construction visuelle
Comme précédemment, si vous supprimez l'information de l'année en C3, toutes les dates, le titre et les entêtes du tableau disparaissent. La construction automatique et dynamique de notre calendrier annuel commence à prendre forme. De fait, nous pouvons exploiter ces résultats afin de déclencher des mises en forme dynamiques pour construire les bordures et couleurs du Calendrier.

Pour commencer simplement, nous pouvons réaliser les bordures globales du calendrier. Ces dernières doivent apparaître sur l'ensemble de la hauteur et de la largeur, dans la mesure où l'année est spécifiée. Donc le critère est très simple, il consiste à vérifier que C3 n'est pas vide.

• Sélectionner toutes les cellules du calendrier, soit la plage C6:N37,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le type Utiliser une formule...,
• Cliquer juste en dessous, dans la zone de saisie du critère pour l'activer,
• Saisir le critère suivant : =$C$3<>'', pour vérifier que la cellule de l'année n'est pas vide,
• Cliquer alors sur le bouton Format et activer l'onglet Bordure dans la boîte de dialogue qui suit,
• Sélectionner un type de trait plein et choisir une couleur violette,
• Puis cliquer sur le bouton Contour pour l'appliquer à toutes les cellules,
• Valider par Ok pour revenir sur la première boîte de dialogue,
• Puis cliquer de nouveau sur Ok pour valider la règle du format dynamique,

Si une année est bien spécifiée en C3, le quadrillage apparaît sur l'ensemble du calendrier. Si vous supprimez l'information en C3, le quadrillage disparaît comme pour les titres et dates.

Certaines cellules pour les dates des fins de mois sont encadrées bien qu'elles ne portent aucune information. Nous choisirons de les mettre en valeur dynamiquement pour indiquer visuellement la fin du mois. Le critère de la mise enforme conditionnelle est très simple là aussi. Il s'agit de vérifier que l'information de la cellule est vide quand dans le même temps l'année est bien définie. Nous devons donc recouper deux critères à l'aide de la fonction Excel ET. Sa syntaxe est la suivante :

ET(Critère_1 ;Critère_2 ; ... ; Critère_n)

• Sélectionner de nouveau l'ensemble du calendrier, soit la plage de cellules C6:N37,
• Cliquer de nouveau sur le bouton Mise en forme conditionnelle et choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le type Utiliser une formule pour...,
• Dans la zone de formule, saisir le double critère suivant :

=ET($C$3<>'';C6='')

La cellule de l'année ne doit pas être vide ($C$3<>''). Comme le format conditionnel parcourt l'ensemble des cases du calendrier, à chaque déplacement il doit s'assurer que cette même cellule n'est pas vide. C'est la raison pour laquelle nous la conservons figée, d'où la présence des dollars. Dans le même temps, la cellule en cours d'analyse doit être vide (C6=''). Comme chaque case du calendrier doit ainsi être étudiée, nous la laissons cette fois se déplacer jusqu'à N37, d'où l'absence des dollars.

• Cliquer sur le bouton Format et activer l'onglet Remplissage de la boîte de dialogue qui suit,
• A l'aide des listes déroulantes, choisir une couleur de motif (un violet léger par exemple), ainsi qu'un style de motif (Des rayures diagonales par exemple),
• Valider par Ok les deux boîtes de dialogue,

Comme vous le remarquez, toutes les parties incomplètes des mois courts sont ainsi mises en valeur pour plus de clarté et lever toute ambiguïté. Grâce au double critère, si l'année en C3 est supprimée, les hachures disparaissent.

Occupons-nous désormais des cellules de titres. Nous souhaitons faire ressortir les informations des mois en entête de colonne sur fond de couleur. Pour que ce remplissage se déclenche, il suffit de vérifier que l'année est définie. Mais comme ces mois eux-mêmes s'inscrivent selon ce critère, il suffit simplement de vérifier que la cellule en cours d'analyse n'est pas vide.

• Sélectionner la ligne de titre du calendrier, soit la plage de cellules C6:N6,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle Règle puis Utiliser une formule dans la boîte de dialogue,
• Dans la zone de saisie de la formule, taper le symbole = pour débuter l'écriture,
• Cliquer la cellule C6 et enfoncer trois fois la touche F4 pour faire disparaître tous les dollars,

Toutes les cellules de la ligne doivent être vérifiées. Nous initialisons le critère sur la première et la laissons se déplacer.

• Puis saisir le critère suivant : <>'', pour vérifier que la cellule n'est pas vide,
• Cliquer alors sur le bouton Format,
• Définir un fond en violet clair avec l'onglet Remplissage,
• Définir une police grasse dans un violet plus intense avec l'onglet Police,
• Valider ces réglages en cliquant sur les boutons Ok des deux boîtes de dialogue,

Comme vous le constatez, les mois en entêtes du calendrier sont effectivement mis en valeur dynamiquement puisqu'ils doivent être présents en cellule pour déclencher le format. Et pourqu'ils soient présents, selon l'enchaînement des critères en cascade, il faut que l'année soit bien spécifiée en C3. Donc comme toujours, si vous supprimez l'information en cellule C3, cette mise en valeur disparaît de la même manière que les précédentes.

Mise en valeur automatique des Week-End
Pour chaque mois, les Samedis et Dimanches doivent ressortir dans des couleurs explicitement différentes. Il s'agit d'exploiter la fonction JourSem d'Excel dans la zone de critère de la mise en forme conditionnelle. Sa syntaxe est la suivante :

=Joursem(date ;2)

En premier paramètre, nous lui passons la date à vérifier. En second paramètre, le chiffre 2 indique que l'énumération des jours de la semaine commence par le Lundi. De fait, si la fonction Joursem renvoie la valeur 6 ou la valeur 7 sur la date testée, elle confirme qu'il s'agit soit d'un Samedi (6^ème jour), soit d'un Dimanche (7^ème jour). Les deux critères ne doivent pas être vérifiés en même temps. Un jour ne peut être à la fois le Samedi ET le Dimanche. Nous devons donc exploiter la fonction OU pour les énumérer et les considérer tous. Sa syntaxe est identique à celle de la fonction ET :

OU(Critère_1 ;Critère_2 ; ... ; Critère_n)

• Sélectionner toutes les cellules des dates du calendrier, soit la plage C7:N37,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle Règle puis Utiliser une formule dans les types proposés,
• Dans la zone de saisie, taper le critère suivant :

=OU(joursem(C7;2)=6; joursem(C7;2)=7)

Le jour de la semaine peut être soit (OU) le 6^ème (Samedi), soit le 7^ème (Dimanche). Le critère est bâti sur la première cellule de la plage sélectionnée. Comme nous l'avons défigée, le format conditionnel vérifiera ainsi toutes les autres cellules de la plage.

• Cliquer sur le bouton Format puis activer l'onglet Remplissage,
• Définir un fond de cellule sur un bleu assez soutenu,
• Cliquer sur l'onglet Police de la boîte de dialogue,
• Définir une police grasse dans un gris très clair,
• Valider la première boîte de dialogue par Ok ainsi que la seconde,

De retour sur la feuille, vous constatez que tous les Week-End du calendrier annuel ressortent de façon évidente. Si vous modifiez l'année en C3, les Week-End changent et la mise en valeur s'adapte automatiquement. Si vous supprimez l'information de l'année en C3, la mise en valeur disparaît puisque les dates disparaissent.

Mise en forme dynamique des jours fériés
Il s'agit sans doute du point le plus délicat dans la réalisation de ce calendrier annuel. Pour que cette mise en valeur se déclenche automatiquement, le critère du format dynamique consiste à vérifier que la date est présente dans le tableau de la feuille Jour_feries, et plus précisément dans la colonne de l'année spécifiée.

Nous proposons de décomposer le processus par des calculs intermédiaires en colonnes J et K de la feuille Jour_feries. Ils consistent à extraire toutes les dates correspondant à l'année désignée depuis la feuille Calendrier. A l'issue, nous masquerons ces colonnes.

• Cliquer sur l'onglet Jour_feries en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule J4 et taper le symbole = pour débuter le calcul,
• Cliquer alors sur l'onglet Calendrier en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule C3 de la date et valider le calcul par la touche Entrée,

Nous venons simplement de réaliser une correspondance entre les deux cellules par le calcul. La cellule J4 de la feuille Jour_feries affichera toujours la même valeur que la cellule C3 de la feuille Calendrier.

En cellule K4, nous devons inscrire le numéro de la colonne dans laquelle est inscrite cette date dans le tableau des jours fériés. Par exemple, l'année 2018 correspond à la colonne 2 tandis que l'année 2020 correspond à la colonne 4. La ligne 5 qui devra être masquée à l'issue, recense tous ces numéros de colonne. Il suffit donc de réaliser une recherche horizontale de la date dans les deux premières lignes pour récupérer le numéro correspondant. La syntaxe de la fonction RechercheH est la suivante :

=RechercheH(date_cherchée ; tableau_de_recherche ; num_ligne ; Faux)

Date_cherchée est donc la cellule J4. Tableau_de_recherche est la plage C4:H5. Num_ligne vaut 2 car l'information sur le numéro de colonne se situe en deuxième ligne du tableau de recherche. Faux est un booléen indiquant à la fonction RechercheH de réaliser une recherche selon une correspondance exacte.

• Sélectionner la cellule K4 et taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction suivi d'une parenthèse, soit rechercheh(,
• Sélectionner la cellule J4 pour la désigner comme valeur à chercher,
• Taper un point-virgule (;) pour passer à l'argument du tableau de recherche,
• Sélectionner alors la plage de cellules C4:H5,
• Taper un point-virgule (;) pour passer à l'argument du numéro de ligne,
• Saisir le chiffre 2, taper un point-virgule, saisir Faux et fermer la parenthèse,
• Puis valider le calcul par la touche Entrée,

La formule que nous avons tapée est la suivante :

=RECHERCHEH(J4;C4:H5;2;FAUX)

Pour l'année 2019 sélectionnée depuis la liste déroulante de la feuille Calendrier, elle indique que les jours fériés correspondants sont situés dans la troisième colonne du tableau. Si aucune date n'est spécifiée, elle retourne un message d'erreur dont nous nous accommoderons puisque ces deux colonnes sont destinées à être masquées.

En colonne J et à partir de la ligne 6, nous devons récupérer tous les jours fériés correspondant à l'année dont le numéro de colonne a précédemment été retourné. La fonction Excel Index permet d'extraire des données depuis des tableaux selon le numéro de ligne et le numéro de colonne de l'information à extraire. Nous connaissons la colonne mais il nous manque la ligne.

• En K6 taper le chiffre 1 et valider par CTRL + Entrée pour conserver la cellule active,
• Tout en maintenant la touche CTRL enfoncée, tirer la poignée de la cellule jusqu'en K16,

Nous forçons ainsi l'incrémentation de la suite numérique sur la hauteur de tableau des jours fériés. Nous connaissons désormais l'indice de ligne de chacun des jours fériés pour l'année spécifiée en colonne J. La syntaxe de la fonction Index est la suivante :

=Index(Tableau_de_recherche ; indice_de_ligne ; indice_de_colonne)

• Sélectionner la cellule J6 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction d'extraction suivi d'une parenthèse, soit Index(,
• Sélectionner les jours fériés, soit la plage de cellules C6:H16,
• Enfoncer la touche F4 du clavier pour la figer,
• Taper un point-virgule et sélectionner la cellule K6 pour l'indice de ligne,
• Taper un point-virgule et sélectionner la cellule K4 pour l'indice de colonne,
• Enfoncer la touche F4 du clavier pour la figer,
• Fermer la parenthèse de la formule et valider le calcul par Entrée,
• Enfin, tirer la poignée du calcul jusqu'en cellule J16,

Nous obtenons tous les jours fériés correspondant à l'année sélectionnée depuis la feuille Calendrier. Nous avons figé la cellule de l'indice de colonne ($K$4) puisque l'information est unique et ne doit pas changer. En revanche, nous n'avons pas figé la cellule de l'indice de ligne (K6) pour prélever toutes les dates correspondant aux indices des lignes du dessous. La formule que nous avons tapée est la suivante :

=INDEX($C$6:$H$16;K6;$K$4)



Pour mettre en valeur les jours fériés dans le calendrier désormais, il suffit de reconnaître si la date fait partie de la liste que nous venons de reconstruire. La fonction Excel RechercheV semble donc dédiée. Il s'agit de rechercher la date en cours d'analyse par le format conditionnel dans cette colonne J et de retourner la valeur de la colonne 1.

• Cliquer sur l'onglet Calendrier en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner toutes les dates du calendrier, soit la plage de cellules C7:N37,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle Règle puis le type Utiliser une formule...,
• Dans la zone de critère, taper la formule suivante :

=RECHERCHEV(C7; Jour_feries!$J$4:$J$16; 1; FAUX)

Nous effectuons la recherche de la date en cours d'analyse par le format conditionnel, en partant de la première cellule de la sélection (C7). Nous la défigeons pour que toutes les cellules du calendrier puissent être ainsi recherchées dans le tableau des jours fériés. Nous réalisons cette recherche dans la colonne des jours fériés reconstitués sur la feuille Jour_feries (Jour_feries!$J$4:$J$16). Nous conservons ce tableau figé pour que la comparaison se réalise toujours entre ces bornes fixes. Nous demandons d'extraire la valeur correspondante depuis la colonne 1, soit la même que la colonne de recherche. Si la date correspond à un jour férié, la fonction RechercheV retourne cette date et la mise en forme conditionnelle considère que le critère est validé. Si la fonction RechercheV retourne une erreur signifiant que la date n'est pas trouvée, le critère du format dynamique n'est pas vérifié. En conséquence, les préférences ne sont pas appliquées.

• Cliquer sur le bouton Format et activer l'onglet Remplissage de la boîte de dialogue,
• Définir un fond vert relativement dense puis activer l'onglet Police,
• Définir une police grasse dans un gris très clair,
• Valider les réglages en cliquant sur le bouton Ok des deux boîtes de dialogue,

Tous les jours fériés apparaissent mis en valeur dynamiquement et viennent se cumuler aux jours de Week-End pour offrir une lecture claire du calendrier. Si vous modifiez l'année, certains jours fériés relatifs à des fêtes religieuses, notamment pour les mois d'Avril et de Mai, s'adaptent et se déplacent. Notre construction dynamique et automatique du calendrier annuel fonctionne donc parfaitement.

Il reste à nettoyer l'environnement en masquant notamment les cellules ayant servi de calculs intermédiaires.

• Cliquer sur l'étiquette de ligne 5 de la feuille Calendrier avec le bouton droit de la souris,
• Dans le menu contextuel, choisir Masquer (ou Cacher selon la version d'Excel),
• Cliquer sur l'étiquette de colonne B avec le bouton droit de la souris,
• Dans le menu contextuel, choisir Masquer,
• Cliquer sur l'onglet Jour_feries en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner les colonnes J et K par leurs étiquettes,
• Cliquer avec le bouton droit de la souris sur la sélection et choisir Masquer,
• Cliquer sur l'étiquette de ligne 5 avec le bouton droit de la souris et choisir Masquer,
• Cliquer sur l'onglet Calendrier en bas de la fenêtre Excel pour activer sa feuille,

Comme les cellules des calculs intermédiaires ont simplement été masquées et non supprimées, toutes les formules permettant la construction dynamique et automatique continuent de fonctionner. La présentation s'en trouve épurée. De même, des réglages de mise en page avec zone d'impression et mise à l'échelle avaient été prédéfinis. C'est la raison pour laquelle, si vous commandez l'aperçu avant impression, vous constatez que le calendrier, quelle que soit l'année, est prêt à l'édition.

• Réaliser le raccourci clavier CTRL + P pour afficher l'aperçu,

Une évolution intéressante consisterait à pouvoir afficher les noms des fêtes correspondant aux jours fériés directement dans la case associée. Nous résoudrons ce point dans une prochaine formation.