Calendriers automatiques des vacances scolaires

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


Sujets que vous pourriez aussi aimer :


Calendrier des vacances scolaires

Sur la base des travaux permettant de repérer les absences d'un salarié dans un calendrier annuel, cette formation propose de décliner le principe. Il y est question, toujours sur un calendrier annuel, de faire ressortir dynamiquement les périodes de congés scolaires. Ces dernières doivent s'adapter au choix de l'année et de la zone.



Mais il doit aussi être possible de réunir toutes les vacances sur une même vue. Dès lors, les périodes se chevauchant doivent réagir dynamiquement, selon une couleur définie par la légende. Vous l'avez remarqué, ce calendrier est adapté au cycle scolaire. Il débute à partir du mois de Septembre et non du mois de Janvier.

Source et présentation de la problématique
Pour réaliser ces travaux, nous proposons de réceptionner un classeur offrant les données et la construction automatisée du calendrier scolaire annuel.

• Télécharger le classeur calendrier-vacances-scolaires.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Sur ce classeur constitué de plusieurs feuilles, c'est la feuille Calendrier qui est active par défaut. On y retrouve la représentation des mois, à cheval sur deux années, pour respecter le cycle scolaire. L'année 2019 étant sélectionnée par défaut, le calendrier offre la représentation des mois de Septembre à Décembre 2019 et de Janvier à Août 2020. C'est sur ces mois que nous devons bâtir des règles de mise en forme conditionnelle. Elles doivent faire réagir les périodes en fonction de l'année désignée en BD8 et de la zone choisie dans la seconde liste déroulante en BD11.

• En bas de la fenêtre Excel, cliquer sur l'onglet Vacances pour activer sa feuille,

Elle livre un tableau archivant toutes les dates de vacances scolaires pour les trois zones et pour les périodes 2019-2020 et 2020-2021. Les règles de mise en forme conditionnelle doivent donc s'y référer pour établir la correspondance avec les dates du calendrier de l'année scolaire choisie.



Repérer les jours fériés
De la même façon, si vous activez la feuille Jour_feries du classeur, vous notez que ces journées particulières sont énumérées de l'année 2019 à l'année 2024. Ces jours fériés sont déjà repérés dynamiquement pour l'année scolaire choisie, sur un fond noir. Nous proposons néanmoins de remplacer les règles en vigueur par une nouvelle. La méthode que nous suggérons dans cette formation est une astuce. Et cette astuce simplifie la syntaxe et la compréhension de la règle. Nous pourrons dès lors la décliner pour la mise en valeur des périodes de vacances scolaires.

Cette astuce consiste à compter le nombre de fois où la date est trouvée dans la base de données plutôt que de la rechercher. Nous allons donc pouvoir exploiter la fonction Excel Nb.Si à la place de la fonction RechercheV. Cette fonction de dénombrement requiert deux paramètres :

=NB.SI(Plage_du_critère; Critère)

Les cellules vides du calendrier doivent être ignorées. Pour énoncer deux conditions à recouper, nous devons donc exploiter la fonction ET dans la syntaxe de la règle.

• En bas de la fenêtre Excel, cliquer sur l'onglet Calendrier pour revenir sur sa feuille,
• Sélectionner toutes les cases du mois de Septembre, soit la plage de cellules B7:H12,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir l'option Gérer les règles,
• Dans la boîte de dialogue, sélectionner la première règle sur fond noir,
• Puis, cliquer sur le bouton Supprimer la règle,
• Ensuite, réitérer le processus pour supprimer la seconde règle sur fond Noir,
• Enfin, cliquer sur le bouton Ok,

Pour le premier mois de l'année scolaire, nous avons donc supprimé les règles de mise en valeur des jours fériés afin de les remplacer par une seule règle plus simple. Nous devons répliquer cette modification sur tous les autres mois de l'année scolaire.

• La plage de cellules du mois de Septembre doit toujours être sélectionnée, soit B7:H12,
• Tout à fait à gauche du ruban Accueil, double cliquer sur le pinceau (Reproduire la mise...),
• Puis, sélectionner tour à tour, l'ensemble des cases des autres mois,

Au fur et à mesure, les jours fériés repérés disparaissent. Cette technique du pinceau permet de répliquer les attributs de mise en forme en cascade. A l'issue, pour abandonner le pinceau, il convient d'enfoncer la touche Echap ou encore de recliquer sur son icône dans le ruban Accueil.

Avant de débuter et pour simplifier la syntaxe de la règle, nous proposons d'attribuer un nom au tableau des jours fériés.

• En bas de la fenêtre Excel, cliquer sur l'onglet Jour_feries pour activer sa feuille,
• Sélectionner l'une des dates du tableau, par exemple la cellule C6,
• Puis, réaliser le raccourci clavier CTRL + A pour sélectionner toute la base de données,
• Dans la zone Nom en haut à gauche de la feuille, saisir l'intitulé : Feries,
• Puis, valider nécessairement ce nom par la touche Entrée du clavier,

C'est désormais grâce à cet intitulé que nous allons pouvoir faire référence à la base de données pour l'écriture des règles de mise en forme conditionnelle. Grâce à lui, nous n'aurons pas besoin de changer de feuille pour désigner le tableau.

• En bas de la fenêtre Excel, cliquer sur l'onglet Calendrier pour revenir sur sa feuille,
• Sélectionner de nouveau toutes les cases du mois de Septembre, soit la plage B7:H12,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie juste en-dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Saisir la fonction recoupant les conditions, suivie d'une parenthèse, soit : Et(,
• Sélectionner la première case du mois de Septembre, soit la cellule B7,
• Puis, enfoncer trois fois de suite la touche F4 du clavier pour la libérer complètement,

Ainsi, les dollars qui encadraient les coordonnées de la cellule disparaissent. Elles doivent en effet toutes être passées en revue. La cellule doit donc se déplacer en même temps que l'analyse de la mise en forme conditionnelle.

• Taper le symbole inférieur suivi du symbole supérieur, soit : <>,
• Taper alors deux guillemets ('') pour l'inégalité à respecter,

En effet et comme nous l'évoquions, nous devons tout d'abord nous assurer que la cellule porte bien une date, donc qu'elle n'est pas vide.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Saisir la fonction conditionnelle de dénombrement, suivie d'une parenthèse, soit : Nb.Si(,
• Saisir ensuite le nom de la base de données : Feries, sans accents,
• Taper un point-virgule (;) pour passer dans l'argument du critère à compter,
• Sélectionner de nouveau la première date, soit la cellule B7,
• Et comme précédemment, enfoncer trois fois la touche F4 pour la libérer complètement,
• Fermer la parenthèse de la fonction Nb.Si,
• Taper l'inégalité : >0,
• Puis, fermer la parenthèse de la fonction Et,
• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Activer l'onglet Remplissage de la boîte de dialogue qui suit,
• Dans la palette de couleurs, choisir un gris foncé,
• Activer alors l'onglet Police de la boîte de dialogue,
• Choisir un style Gras et le blanc pour la couleur de texte,
• Valider ces réglages en cliquant sur le bouton Ok de cette seconde boîte de dialogue,

Nous sommes ainsi de retour sur la première boîte de dialogue. Elle propose un résumé du comportement dynamique à adopter, sur la plage de cellules désignée. Le raisonnement est le suivant : Si la date en cours d'analyse existe bien et qu'elle est référencée dans la base de données des jours fériés, alors sa cellule doit réagir sur fond noir avec un texte transformé en blanc.

• Valider cette règle en cliquant sur le bouton Ok de la boîte de dialogue,

Comme le mois de Septembre ne propose aucun jour férié, aucune mise en valeur dynamique ne surgit pour l'instant. Cette règle doit être répliquée sur tous les autres mois de l'année scolaire.

• Avec le pinceau, reproduire les attributs du mois de Septembre sur tous les autres mois,

Comme vous pouvez le voir, au fur et à mesure de la réplication, les jours fériés sont automatiquement repérés.



Et si vous changez l'année 2019 par l'année 2020 en cellule BD8, vous constatez le déplacement de certaines dates repérées, notamment pour les mois d'Avril et de Mai. Nous avons donc réussi à remplacer deux règles de mise en forme conditionnelle par une seule, qui plus est avec une syntaxe allégée.

Repérer les vacances scolaires
En cellule BD11, une liste déroulante propose de choisir la zone pour laquelle les dates de vacances doivent être identifiées dynamiquement dans le calendrier scolaire.

• En bas de la fenêtre Excel, cliquer sur l'onglet Vacances pour activer sa feuille,

Comme vous le remarquez, chaque titre de colonne de dates est préfixé du nom de sa zone. Il est ensuite concaténé avec les années de la période scolaire. Et l'année de départ, nous la choisissons grâce à la première liste déroulante de la feuille Calendrier. Il paraît donc judicieux de nommer chaque colonne en fonction de son titre. Dans la syntaxe de la règle, nous n'aurons aucune difficulté à désigner ce nom dynamiquement par concaténation, en fonction des choix émis par l'utilisateur. Nous proposons d'exploiter une fonctionnalité automatique pour créer ces noms de colonne.

• Sélectionner l'une des dates de la base de données, par exemple la cellule B3,
• Réaliser le raccourci clavier CTRL + A pour sélectionner intégralement le tableau,
• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Depuis sélection,

Une boîte de dialogue apparaît. Elle propose d'attribuer des noms automatiquement en fonction des intitulés situés aux extrémités.

• Décocher la case Colonne de gauche et conserver la case Ligne du haut,

De cette manière, nous indiquons à Excel de nommer chaque colonne du tableau en fonction des titres situés en haut des rangées respectives.

• Valider ce choix en cliquant sur le bouton Ok de la boîte de dialogue,
• En haut à gauche de la feuille Excel, déployer la liste déroulante de la zone Nom,

Comme vous pouvez le voir, les noms ont parfaitement été attribués en fonction des titres de chaque colonne.



Et si vous cliquez sur l'un d'entre eux, sa plage est automatiquement sélectionnée. Ce sont ces noms que nous devons dynamiquement reconstruire, en fonction des choix de l'utilisateur, dans les règles à bâtir.

• Sélectionner de nouveau toute la base de données des vacances,
• Dans la zone Nom, lui attribuer l'intitulé Vacances,

Ce nom désigne donc toutes les périodes réunies. Il sera utile pour des recherches globales.

• En bas de la fenêtre Excel, cliquer sur l'onglet Calendrier pour revenir sur sa feuille,
• Sélectionner toutes les cases du premier mois, soit la plage de cellules B7:H12,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, taper le symbole égal (=) pour initier la syntaxe de la règle,
• Saisir la fonction pour énumérer les conditions, suivie d'une parenthèse, soit : Et(,
• Saisir la fonction de dénombrement conditionnel, suivie d'une parenthèse, soit : Nb.Si(,
• Saisir la fonction pour interpréter une donnée comme une référence, suivie d'une parenthèse, soit : Indirect(,

En effet, nous allons reconstruire par concaténation, le nom de la colonne sur laquelle l'analyse doit être faite. Comme cette concaténation va conduire à une chaîne de texte, l'emploi de la fonction Indirect est nécessaire pour l'interpréter comme une référence de plage de cellules.

• Construire alors l'expression suivante : 'Zone_A_' & $BD$8 & '_' & $BD$8+1,

Pour cette première règle, nous agissons sur la zone A (Zone_A_). Il s'agit du préfixe du nom de la colonne sur laquelle la date doit être cherchée. Nous le concaténons (&) avec l'information sur l'année ($BD$8) mais aussi sur l'année suivante ('_' & $BD$8+1). Il en résulte une chaîne strictement identique au nom de la colonne ciblée, interprété comme tel grâce à la fonction Indirect.

• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument du critère à compter,
• Sélectionner la première date du mois de Septembre, soit la cellule B7,
• Enfoncer trois fois la touche F4 du clavier pour la libérer complètement,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis taper le critère suivant : >0,

En effet, si la date en cours d'analyse est comptabilisée au moins une fois dans la colonne correspondant à la zone et l'année scolaire, il en résulte qu'il s'agit d'un jour de vacances.

• Taper un point-virgule (;) pour poursuivre l'énumération des conditions,
• Ajouter alors le critère suivant : $BD$11='Zone_A',

Si la zone sélectionnée est bien la zone A, nous savons que le remplissage doit se faire avec une couleur orange.

• Fermer la parenthèse de la fonction Et,
• Cliquer sur le bouton Format, en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un jaune fidèle à celui de la légende,
• Valider ce choix en cliquant sur le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle indique explicitement que si la date est trouvée dans la zone A, pour l'année scolaire désignée, son arrière-plan se remplira d'orange. Il est conseillé à ce stade de sélectionner la syntaxe de la règle et de la copier. Nous pourrons ainsi la décliner plus facilement pour les zones B et C.

• Valider la création de cette règle en cliquant sur le bouton Ok,

Pour le mois de Septembre, rien ne réagit pour l'instant. Il s'agit en effet du mois de la rentrée des classes. Nous répliquerons ces attributs lorsque toutes les règles seront bâties.

• De la même façon, créer les deux nouvelles règles suivantes :

Sur fond vert pour la zone B :
=ET(NB.SI(INDIRECT('Zone_B_' & $BD$8 & '_' & $BD$8+1);B7)>0; $BD$11='Zone_B')
Sur un fond orange pour la zone C :
=ET(NB.SI(INDIRECT('Zone_C_' & $BD$8 & '_' & $BD$8+1);B7)>0; $BD$11='Zone_C')

Repérer les périodes communes de vacances
Avant de reproduire l'ensemble des règles sur l'ensemble des mois de l'année scolaire, il est préférable de les avoir toutes construites. Et nous souhaitons, comme l'indique la dernière couleur de la légende, faire ressortir les dates communes sur un fond de couleur explicite. Ce fond doit surgir lorsque l'utilisateur choisit la zone Toutes dans la liste déroulante en BD11. De fait, les périodes indépendantes doivent continuer de réagir dans leur couleur propre. Quatre règles quasiment identiques sont encore nécessaires.

• Toujours sur la plage de cellules B7:H12, créer les quatre règles dans l'ordre suivant :

Sur fond jaune :
=ET(NB.SI(INDIRECT('Zone_A_' & $BD$8 & '_' & $BD$8+1);B7)>0; $BD$11='Toutes'),
Sur fond vert :
=ET(NB.SI(INDIRECT('Zone_B_' & $BD$8 & '_' & $BD$8+1);B7)>0; $BD$11='Toutes'),
Sur fond orange :
=ET(NB.SI(INDIRECT('Zone_C_' & $BD$8 & '_' & $BD$8+1);B7)>0; $BD$11='Toutes'),
Sur fond rouge avec un texte blanc et gras :
=ET($BD$11='Toutes';NB.SI(Vacances;B7)>1).

Avant de répliquer toutes ces règles sur l'ensemble du calendrier scolaire, il est important de comprendre l'importance de la hiérarchie de ces dernières. La plage de cellules B7:H12 doit toujours être sélectionnée.

• Cliquer une fois sur l'outil pinceau à gauche dans le ruban Accueil,
• Puis, sélectionner toutes les cellules du mois de Décembre, soit la plage AU7:BA12,

Aussitôt, les dates de vacances pour la zone A sont repérées dynamiquement. Mais comme vous pouvez le voir, la mise en valeur du jour férié le 25 Décembre est neutralisée. Cette journée est commune à deux règles. Et celle qui est posée en dernier est jugée prioritaire.

• Annuler cette réplication de mise en forme (CTRL + Z),

La plage de cellules B7:H12 doit toujours être sélectionnée.

• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir la commande Gérer les règles,

Une boîte de dialogue apparaît. Il s'agit du gestionnaire des règles de mise en forme conditionnelle pour la plage de cellules sélectionnée. Nous retrouvons donc toutes celles que nous avons construites. Plus une règle est placée haut dans la liste, plus son importance est grande dans la hiérarchie. C'est elle qui aura le dernier mot sur des plages communes.

• En bas de la liste, sélectionner la règle sur fond noir, celle des jours fériés,
• Cliquer sur la flèche orientée vers le haut jusqu'à remonter la règle en tête de liste,

Ainsi positionnée, elle doit permettre d'identifier un jour férié de façon explicitement différente, y compris dans une période conjointe de vacances scolaires.



De plus, cet ordre est censé être répliqué à l'identique, au même titre que les formats et syntaxes des règles.

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider cette hiérarchie,
• Répliquer les attributs de format dynamique du premier mois sur tous les autres grâce au pinceau,

Au fur et à mesure de la réplication, vous voyez surgir la mise en forme dynamique des vacances pour la période sélectionnée. Dans le même temps, les jours fériés gardent cette fois le dessus, grâce à la hiérarchie imposée. Si vous changez de zone, les périodes de vacances scolaires s'adaptent automatiquement. Et si vous sélectionnez la zone Toutes, les périodes communes sont mises en évidence explicitement. Dans le même temps, les autres ressortent dans leur propre couleur.



Nos travaux sont terminés. Nous aurions pu développer une fonctionnalité supplémentaire consistant à repérer les jours d'absence des professeurs par matière. Nous aurions de même pu entrevoir d'identifier les potentiels jours de grève. Mais il s'agit de techniques similaires à celles que nous avons mises en place dans la formation sur les jours de congés des salariés.