Une formule simple pour un calendrier annuel perpétuel

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 une seule formule

Nous avons déjà construit ensemble de nombreux calendriers et plannings annuels automatisés. Mais cette fois, il n'est plus question de le bâtir à l'aide de formules intermédiaires permettant enfin d'aboutir au résultat escompté. Un seul calcul matriciel doit produire l'ensemble des dates de chaque mois pour le calendrier annuel perpétuel.



Source et présentation
Pour débuter la construction, nous proposons tout d'abord de récupérer un classeur offrant certains réglages.

• Télécharger le fichier calendrier-annuel-matriciel.rar en cliquant sur ce lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous pouvez le voir, le classeur Excel est accompagné d'un fichier de type texte. Il livre des syntaxes de matrices virtuelles que nous exploiterons. Nous y reviendrons en temps voulu.

• Double cliquer sur le fichier du classeur pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons une feuille proposant une certaine structure. En cellule B4, une liste déroulante permet de désigner une année. C'est sur la base de ce choix que doit s'opérer la construction automatique du calendrier annuel. Cette liste de choix se nourrit des informations renseignées en colonne O. Cette dernière peut donc être masquée.

En ligne 6 sont énumérés tous les mois de l'année dans leur version textuelle. A partir de la ligne 7, et donc pour chacun des mois, nous devons entreprendre la construction automatisée de chacun des jours qui les composent.

Formule pour construire le calendrier
La fonction Excel Date est nécessaire.

=Date(Année; Mois; Jour)

Selon l'année passée en premier paramètre, le numéro du mois inscrit en second argument et le numéro du jour fourni en troisième paramètre, elle livre une date précise. Mais cette date doit évoluer au gré des mois et des jours du mois. Seul un calcul matriciel raisonnant sur l'ensemble des données peut prétendre à la construction automatique et directe du calendrier.

• A la racine du dossier de décompression, double cliquer sur le fichier texte pour l'ouvrir,

Celui-ci renferme deux matrices virtuelles :

{1.2.3.4.5.6.7.8.9.10.11.12}
{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30}

La première désigne les douze mois de l'année sous forme de tableau horizontal, respectant la progression de notre calendrier. Dans la syntaxe, ce sont les points utilisés comme délimiteurs qui définissent une telle matrice. La seconde énumère les 30 jours potentiels de chaque mois à ajouter au premier jour construit, pour obtenir la suite logique dans chaque colonne. Dans la syntaxe, ce sont les points-virgules qui définissent une telle matrice d'énumération.

• Revenir sur la feuille Excel et sélectionner la plage B7:M37,

Ainsi, pour chaque colonne, nous désignons une rangée composée de 31 lignes pour la construction des mois les plus riches.

• Taper le symbole égal (=) pour débuter la formule matricielle,
• Inscrire la fonction pour calculer une date, suivie d'une parenthèse, soit : Date(,
• Cliquer sur la cellule B4 pour fournir l'information dynamique sur l'année,
• Taper un point-virgule (;) pour passer dans l'argument du mois,

Dans ce raisonnement matriciel, en guise de mois, ce sont tous les mois de l'année à répartir que nous devons désigner sous forme de tableau horizontal.

• Dans le fichier texte, sélectionner et copier (CTRL + C) la première matrice virtuelle,
• De retour sur Excel, à la suite de la syntaxe, la coller (CTRL + V), ce qui donne :

{1.2.3.4.5.6.7.8.9.10.11.12}

• Taper un point-virgule (;) pour passer dans l'argument du jour,
• Taper le chiffre 1 pour désigner chaque premier jour de mois,

Ensuite, nous devons transmettre l'énumération de tous les jours à ajouter à chacun de ces premiers jours du mois.

• Fermer la parenthèse de la fonction Date,
• Taper le symbole plus (+) pour annoncer l'addition matricielle,
• Dans le fichier texte, sélectionner et copier la seconde matrice virtuelle,
• De retour sur Excel, la coller à la fin de la syntaxe, ce qui donne :

{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30}

• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

A première vue, le résultat obtenu est pour le moins surprenant. Tout est simplement une question de formatage. Nous devons effectuer un réglage imposant à Excel de livrer ces données sous forme de date. Et nous allons profiter du fait que la plage soit toujours intégralement sélectionnée.

• Dans la section Nombre du ruban Accueil, déployer la liste déroulante des formats,
• Tout en bas de la liste, choisir 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 : jjj jj,

Nous connaissons déjà le mois et l'année. Pour ne pas alourdir la présentation du calendrier annuel, seule la précision sur le jour nous intéresse. Avec une succession de trois lettres j, nous demandons l'affichage textuel du jour sous forme abrégée. Après un espace et avec une succession de deux lettres j, nous additionnons la précision sur le numéro du jour dans le mois.

• Cliquer sur le bouton Ok pour valider ce format personnalisé,

De retour sur la feuille, vous constatez que notre calendrier est effectivement enrichi automatiquement des dates composant chaque mois. Et pour cela, nous avons bâti une unique formule se nourrissant de l'information dynamique sur l'année :

{=DATE(B4; {1.2.3.4.5.6.7.8.9.10.11.12}; 1) + {0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30}}

Cependant, une anomalie fort logique s'impose.



Si vous atteignez les derniers jours du calendrier, vous constatez que l'énumération reprend sur le mois suivant lorsqu'elle déborde.

Limiter l'énumération aux jours du mois
C'est une règle de mise en forme conditionnelle qui permet de corriger l'anomalie. L'idée consiste à comparer pour chaque colonne, le mois de la date construite avec le mois du premier jour. S'ils coïncident, la date doit être conservée. Dans le cas contraire, la date ne doit plus être affichée. L'astuce consiste à lui appliquer dynamiquement un texte blanc sur un fond blanc.

• Sélectionner de nouveau toutes les dates du calendrier, soit la plage de cellules B7:M37,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Tout en bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone Source située juste en-dessous,
• Et taper la syntaxe suivante pour la règle à honorer : =Mois(B7)<>Mois(B$7),

L'analyse d'une mise en forme conditionnelle est chronologique. Elle doit donc débuter à partir de la première date. C'est ce que nous faisons en désignant la cellule B7. Mais la première des deux est complètement libérée tandis que la seconde est conservée figée en ligne (B$7). Ainsi, au fil de la progression de l'analyse, les dates des lignes du dessous seront comparées avec la date de la première ligne pour le mois en cours. Et plus précisément, ce sont les mois qui sont étudiés grâce à la fonction du même nom. S'ils diffèrent, nous en concluons que le calcul déborde sur le mois suivant. Dans ces conditions, son résultat doit être neutralisé par une mise en forme dynamique.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la liste déroulante, choisir le blanc pour la couleur du texte,
• Valider ces réglages en cliquant sur le bouton Ok,

• Valider la création de la règle de mise en forme conditionnelle par le bouton Ok,

Si vous affichez la fin du calendrier, vous constatez que les limites des mois sont désormais respectées.

Mise en valeur des Week-End
Ensuite, pour repérer les jours de Week-End et les faire ressortir dynamiquement, nous devons exploiter la fonction Excel Joursem. Elle renseigne sur le numéro du jour dans la semaine pour une date qui lui est passée en paramètre. Ces dates sont toutes celles du calendrier. Par défaut, elle fonctionne sur le mécanisme américain. Le Dimanche correspond au premier jour de la semaine tandis que le Samedi correspond au septième jour. Nous devons vérifier ces deux égalités conjointement grâce à la fonction OU. Dès lors que l'une des deux est avérée, une mise en forme dynamique spécifique doit se déclencher.

• Sélectionner de nouveau toutes les dates du calendrier, soit la plage de cellules B7:M37,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Tout en bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Cliquer dans la zone Source située juste en-dessous,
• Puis, construire la syntaxe suivante pour la règle à honorer :

=Ou(Joursem(B7)=1; Joursem(B7)=7)

L'analyse est donc bien réalisée par rapport à la première date pour respecter la chronologie. Notez que sa cellule B7 est totalement défigée pour que toutes les dates soient analysées tour à tour. Donc lorsque qu'il s'agit soit d'un Dimanche (1), soit d'un Samedi (7), l'apparence des cellules concernées doit s'adapter 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 pour le fond de la cellule,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Définir un style gras et un texte bleu foncé,
• Valider ces attributs de format par le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle par le bouton Ok,

De retour sur la feuille, la vision est plus évidente, tous les Week-End sont dynamiquement mis en forme.



Et bien sûr, si vous modifiez l'année à l'aide de la liste déroulante en B4, les dates du calendrier annuel se réorganisent automatiquement tandis que les repérages des jours de Week-End se déplacent pour s'adapter.