Calendrier annuel et perpétuel avec Excel

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


Sujets que vous pourriez aussi aimer :


Calendriers perpétuels et numéros de semaine

Nous avons déjà bâti ensemble quelques calendriers automatiques et dynamiques avec Excel. Celui que propose de concevoir cette formation se dote des numéros de semaine. Ce renseignement, à afficher en regard des jours de chaque mois, est une indication précieuse pour de nombreux professionnels.



L'exemple illustré par la capture ci-dessus offre une vue partielle de la solution à atteindre. Un phénomène loin d'être anecdotique saute rapidement aux yeux. Les trois premiers jours du premier mois de l'année 2021 sélectionnée, correspondent à la semaine 53 et non à la première semaine de l'année. La raison est simple. Il s'agit de la fin de la dernière semaine de l'année précédente.

Source et présentation du concept
Un classeur offrant certaines données et une ébauche de structure nous attend pour concevoir cette étude. Il s'agit donc de le réceptionner dans un premier temps.

• Télécharger le classeur calendriers-perpetuels-numeros-semaines.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é,

Comme vous pouvez le voir, le classeur réceptionné est muni de trois feuilles nommées respectivement : Calendriers, Source et Jour_feries.

La feuille Source énumère les numéros de semaines et d'années servant à remplir les deux listes déroulantes de la feuille Calendriers, en cellules respectives D5 et H5. La feuille Jour_feries comme son nom l'indique, recense les jours fériés sur quelques années. Elle est proposée à titre de modèle et il convient de l'actualiser. Elle doit permettre de repérer distinctement ces dates particulières dans le calendrier annuel à construire.

• Sur la feuille Calendriers, sélectionner la cellule B8,

Il s'agit du premier mois retranscrit en fonction de l'année choisie avec la liste déroulante en D5. Ce mois est le résultat d'une formule comme le confirme le contenu de sa barre de formule.

=SI($D$5<> ''; NOMPROPRE(TEXTE(DATE($D$5; COLONNE(D1)/4; 1); 'mmmm')); '')

Certes, nous aurions très bien pu réaliser la mise en page manuellement. Mais cette formule livre une astuce que nous allons répliquer afin de produire une unique formule sur le calendrier annuel. Elle permettra de fournir tous les jours d'un mois et ce, quel que soit le mois considéré. Cette astuce exploite la fonction Date :

DATE($D$5; COLONNE(D1)/4; 1).

L'année figée ($D$5) lui est passée en premier argument. Le jour est toujours fixé sur le premier du mois, passé avec le chiffre 1 en troisième paramètre. La donnée mouvante concerne le mois. Elle dépend de la position de la formule dans la feuille. Les débuts de chaque mois sont séparés de quatre colonnes. Pour obtenir les mois suivants par réplication, nous exploitons la fonction Colonne :

COLONNE(D1)/4

Celle-ci retourne l'indice de la rangée désignée. En le divisant par 4, nous obtenons par réplication le mois suivant jusqu'au dernier.

Enfin l'affichage abrégé de la date sous forme de mois, est imposé par la fonction Texte. Elle est elle-même encapsulée dans la fonction NomPropre pour convertir le résultat en majuscule.

Construire les jours des mois
A partir de la cellule D11 pour le premier mois, nous devons créer la série incrémentée des jours du mois désigné en fonction de l'année choisie. Les données des colonnes précédentes (Jour et N°) s'en déduiront. Cette formule de la série incrémentée ne doit être écrite qu'une fois. Elle doit se répliquer naturellement pour tous les autres mois de l'année.

Il convient tout d'abord de gérer les erreurs lorsque la fin de la série sera atteinte.

• Sélectionner la cellule D11 et débuter la syntaxe comme suit : =SIERREUR(,

En premier argument de cette fonction nous allons tenter le calcul. Si une anomalie est détectée, nous la gèrerons grâce à son deuxième argument. Ce calcul offre des conditions multiples. Il doit s'adapter au contexte. Si la cellule du dessus porte la mention N° J, nous savons que nous devons procéder à l'inscription du premier jour pour le mois en cours.

• A la suite de la syntaxe, ajouter la partie conditionnelle suivante :

SI(D10='N° J'; DATE($D$5; COLONNE(D1)/4; LIGNE(D1));

Lorsque la condition est effectivement remplie, nous utilisons la fonction Date pour créer le premier jour du mois. Nous lui indiquons l'année en premier paramètre. Sa cellule est figée pour que chaque calcul puisse se référer à cette cellule de référence. En deuxième paramètre, nous stipulons le numéro de mois sous forme dynamique. Nous passons une cellule de la quatrième rangée à la fonction Colonne (D1). Divisée par quatre, elle retournera bien le premier mois, puis le deuxième, le troisième etc.., pour les tableaux suivants. Bien entendu, en dernier paramètre, nous indiquons de calculer la première date du mois mais de façon dynamique. Pour cela, nous exploitons la fonction Ligne sur une cellule de la première rangée (D1). Elle retournera donc le premier jour, puis par réplication sur les lignes du dessous, les jours suivants.

Dans le cas contraire, nous devons incrémenter les dates d'une journée. Mais pour cela, nous devons vérifier que la date à suivre appartient bien au même mois que la précédente. L'enjeu est de stopper l'incrémentation au dernier jour du mois.

• A la suite de la syntaxe, donc après le point-virgule, ajouter la nouvelle condition suivante :

SI(MOIS(D10)=MOIS(DATE($D$5; COLONNE(D1)/4; LIGNE(D1))); DATE($D$5; COLONNE(D1)/4; LIGNE(D1));

Nous vérifions l'égalité entre la date de la cellule précédente (D10) et la date à suivre (MOIS(DATE($D$5; COLONNE(D1)/4; LIGNE(D1)))). Rappelez-vous, l'incrémentation des jours sera naturelle grâce à l'emploi de la fonction Ligne dans le dernier argument de la fonction Date. Si ce critère est satisfait, alors nous inscrivons cette date : DATE($D$5;COLONNE(D1)/4;LIGNE(D1)).

Dans le cas restant, nous comprenons qu'il ne s'agit ni de la première date du mois, ni d'une date dans l'enchaînement du même mois. Nous devons donc conserver la cellule vide pour opérer la rupture en fin de mois.

• A la suite de la formule, après le point-virgule, ajouter la syntaxe suivante :

'')); '')

Les deux guillemets gardent la cellule vierge. Nous faisons de même dans le deuxième argument de la fonction SiErreur en cas d'anomalie.

• Valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous conservons active la cellule du résultat pour l'exploiter dans la foulée. A ce stade, elle retourne effectivement le premier jour du premier mois pour l'année désignée. Pour ne conserver que le numéro du jour, nous règlerons son format.

• Cliquer et glisser la poignée de la cellule jusqu'en ligne 41,

En effet, un mois ne peut pas compter plus de 31 jours.



Comme la plage toute entière est encore active, nous allons en profiter pour ajuster le format de ses dates.

• Dans la section Nombre du ruban Accueil, déployer la liste déroulante des formats,
• Tout en bas de la liste, choisir l'option Autres formats numériques,
• Dans la boîte de dialogue qui suit, sélectionner la catégorie Personnalisée,
• Dans la zone Type au centre de la boîte de dialogue, taper le code suivant : jj,

Ainsi, nous stipulons que nous souhaitons conserver l'information que sur le numéro du jour.

• Valider ces réglages en cliquant sur le bouton Ok de la boîte de dialogue,

Les dates sont toujours présentes mais désormais retranscrites dans leur plus simple expression.

• Dans la section Alignement du ruban Accueil, cliquer sur le bouton Aligner à droite,
• Faire de même pour le titre de la colonne (N° J),
• Sélectionner la cellule C11 et taper la syntaxe suivante : =D11,
• Valider le calcul par CTRL + Entrée puis double cliquer sur la poignée du résultat,

De fait, nous répliquons toutes les dates précédemment calculées à l'identique. Il s'agit maintenant de ne conserver que l'information textuelle sur le jour de la semaine.

• Dans la section Nombre du ruban Accueil, déployer la liste déroulante des formats,
• En bas des propositions, choisir l'option Autres formats numériques,
• Dans la boîte de dialogue qui suit, sélectionner la catégorie Personnalisée,
• Dans la zone Type, saisir le code suivant : jjjj,

Avec une précision supplémentaire, nous conservons l'information textuelle en lieu et place de l'information numérique.

• Valider ce réglage de format en cliquant sur le bouton Ok de la boîte de dialogue,

De retour sur la feuille, nous obtenons bien l'information sur les jours de la semaine. Ils enrichissent désormais l'indication sur les numéros de jours du mois.

• Sélectionner la cellule B11 et valider la formule suivante:

=SIERREUR(NO.SEMAINE.ISO(D11); '')

• Double cliquer sur la poignée du résultat pour produire tous les numéros de semaines,

La fonction Excel NO.SEMAINE.ISO retourne le numéro de semaine d'une date passée en paramètre (D11). Contrairement à la fonction NO.SEMAINE, elle a la capacité de retranscrire le numéro d'une semaine à cheval sur deux années. C'est la raison pour laquelle nous obtenons le numéro 53 pour les derniers jours de la semaine qui avait déjà débuté, avant que l'énumération ne reparte du chiffre 1.

• Sélectionner la plage de cellules B11:D41,
• La copier avec le raccourci clavier CTRL + C,
• Sélectionner la première cellule du tableau pour le mois de Février, soit la cellule F11,
• Réaliser le raccourci clavier CTRL + V pour coller la précédente sélection,

Nous obtenons bien les jours ainsi que les numéros de semaine. Et comme nous le remarquez, la série poursuit parfaitement son incrémentation. Son calcul est basé sur le deuxième mois de l'année, grâce à l'astuce employée dans notre calcul. Et pour preuve, l'énumération se stoppe au 28 Février.

• De la même façon, coller ce calcul sur tous les autres tableaux des mois restants,

Pour une présentation ajustée, il est opportun de réduire la largeur des colonnes N° J.

Identifier les jours remarquables
Pour permettre de localiser rapidement des périodes précises dans le calendrier annuel, il convient de faire réagir certaines cellules en fonction des dates qu'elles portent. Les jours de Week-End ne sont pas travaillés dans la majorité des cas. En les affublant d'une mise en forme différente, nous fractionnerons les semaines et simplifierons la lecture et le repérage.

Les jours fériés peuvent aussi être identifiés avec des attributs différents. Il peut paraître opportun de faire ressortir chaque début de semaine, soit chaque lundi.

Pour appliquer des règles de mise en forme conditionnelle, il convient tout d'abord de sélectionner les cellules concernées. Et nous proposons de débuter avec le premier tableau, soit le premier mois.

• Sélectionner toutes les données du premier mois, soit la plage de cellules B11:D41,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas des propositions, choisir l'option Nouvelle Règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie juste en dessous, taper la syntaxe suivante :

=OU(JOURSEM($D11;2)=6; JOURSEM($D11;2)=7)

La fonction Excel JourSem retourne le numéro du jour de la semaine pour la date passée en paramètre. Par défaut, cette fonction raisonne sur le calendrier américain. Et dans ce calendrier, le dimanche n'est pas le dernier jour de la semaine (7) mais le premier. Pour que le Dimanche soit bien considéré comme le dernier jour, nous lui passons la valeur 2 en second paramètre. Grâce à la fonction OU, nous cherchons à repérer tous les samedis et les dimanches. Lorsqu'ils sont repérés, ils doivent être explicitement mis en forme.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert pâle,
• Activer ensuite l'onglet Police,
• Grâce à la liste déroulante, choisir un vert foncé pour le texte,
• Valider ces attributs de format par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle permet de visualiser la mise en forme qui sera appliquée à chaque fois que la règle sera satisfaite, sur les dates sélectionnées.

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

Comme vous pouvez le voir, les Samedis et Dimanches sont désormais clairement identifiés pour le premier mois.

• A l'aide de la liste déroulante en cellule D5, choisir une autre année,

Instantanément, en même temps que les dates et semaines du calendrier se recomposent, le repérage dynamique s'ajuste sur les jours de Week-End déplacés.

• Sélectionner de nouveau la plage de cellules B11:D41,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas des propositions, choisir l'option Nouvelle Règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie juste en dessous, taper la syntaxe suivante :

=NB.SI(Jour_feries!$C$6:$H$16; $D11)>0

Grâce à la fonction Nb.Si, nous analysons la plage des jours fériés sur sa feuille, à la recherche de chacune des dates du mois en cours. Dès qu'une date coïncide, la fonction Nb.Si répond par une valeur entière et positive (>0). Nous en déduisons qu'il s'agit d'un jour férié à repérer dynamiquement.

• 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 bleu pâle,
• Activer l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un bleu foncé pour la couleur du texte,
• Valider ces attributs de format en cliquant sur le bouton Ok,
• Valider la création de la règle en cliquant de nouveau sur le bouton Ok,

Les jours fériés sont nécessairement moins nombreux que les Week End. Mais le premier d'entre eux, le premier Janvier, réagit automatiquement.

• Sélectionner de nouveau la plage de cellules B11:D41,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas des propositions, choisir l'option Nouvelle Règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie juste en dessous, taper la syntaxe suivante :

=JOURSEM($D11;2)=1

Cette règle est triviale. Grâce à la fonction JouSem, nous cherchons à repérer tous les Lundis pour identifier clairement chaque commencement de semaine.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Activer l'onglet Police de la boîte de dialogue qui suit,
• Choisir un style gras et un bleu foncé pour le texte,
• Valider ces attributs de format avec le bouton Ok,
• Puis, valider la création de la règle de mise en forme conditionnelle,

Repérer une semaine désignée
Pour offrir plus d'ergonomie à l'utilisateur dans l'exploitation du calendrier annuel, nous souhaitons désormais repérer dynamiquement la période correspondant à une semaine choisie. Ce numéro de semaine peut se définir grâce à la liste déroulante en cellule H5. Cette fois, l'identification de la règle doit porter sur la première colonne du tableau, celle des numéros de semaine.

• Sélectionner de nouveau la plage de cellules B11:D41,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas des propositions, choisir l'option Nouvelle Règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie juste en dessous, taper la syntaxe suivante :

=$B11=$H$5

Nous cherchons à vérifier l'égalité de tous les numéros de semaine passés en revue ($B11), avec celui choisi dans la liste déroulante et utilisé comme référence ($H$5).

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans l'onglet Remplissage, choisir un fond orange clair,
• Dans l'onglet Police, choisir un rouge foncé,
• Valider les attributs de format puis valider la création de la règle,

Ensuite, il convient de répliquer l'ensemble de ces règles sur tous les autres mois de l'année. Mais à cause des contraintes placées sur les colonnes ($B11 et $D11), le pinceau ne peut pas être exploité efficacement. Il reproduit les règles à l'identique en tenant compte des dates du premier mois. Certes la procédure est de fait un peu fastidieuse. Elle consiste, pour chaque mois et chaque règle, à adapter ces deux contraintes dans la syntaxe. Mais une fois le processus terminé, le calendrier annuel et perpétuel se génère dynamiquement, quelle que soit l'année et autorise le repérage dynamique de la semaine voulue.