Calendrier Excel facile et rapide

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


Sujets que vous pourriez aussi aimer :


Calendrier automatique et facile

Nous avons déjà découvert ensemble maintes techniques différentes pour construire des calendriers et plannings avec Excel. Cette nouvelle astuce montre avec quelle simplicité il est possible de reconstituer les mois d'une année choisie, sur la base d'une unique formule qui plus est, fort simple.



Sur l'exemple illustré par la capture, l'utilisateur choisit d'abord une année à l'aide d'une liste déroulante, placée sur la droite du tableau. Puis, il définit un mois de cette année avec une seconde liste déroulante située en dessous. Et aussitôt, les jours du mois se recomposent dans le tableau au centre de l'écran.

Vous notez l'organisation symétrique du Calendrier. L'énumération des jours débute nécessairement par un Lundi. Et pour chaque semaine, ce sont les Samedis et Dimanches qui clôturent chaque ligne. De fait, une règle de mise en forme se charge de mettre en retrait les jours en dehors du mois. En effet et fort logiquement, un mois ne commence pas nécessairement par un Lundi au même titre qu'il ne se termine pas forcément par un Dimanche.

Classeur source et présentation
Pour la démonstration de cette nouvelle astuce, nous proposons d'appuyer l'étude sur un classeur offrant déjà une certaine organisation et ces listes déroulantes de dates.

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

Deux listes déroulantes sont effectivement disponibles en cellules J4 et J6 pour l'année et pour le mois. Elles se nourrissent des informations des colonnes annexes N et O. Sur la base de ces deux choix, le premier jour du mois est calculé en cellule J9 grâce à la fonction Date:

=SI(ET(J4<>"";J6<>""); DATE(J4; RECHERCHEV(J6;O1:P12; 2; FAUX); 1); "")

L'année lui est passée en premier paramètre et le jour (Le 1^er) en troisième. Le numéro du mois en second paramètre est trouvé grâce à la fonction RechercheV dans la colonne P sur la recherche du nom du mois dans la colonne O. C'est sur ce premier jour du mois que repose en effet toute la construction du calendrier. La structure de ce dernier est prête sur sept colonnes du Lundi au Dimanche et sur six lignes pour une capacité de six semaines. En effet, un mois peut très bien débuter à cheval sur la première semaine et se terminer à cheval sur la sixième semaine.

Lundi avant le premier jour du mois
Nous proposons de dérouler la construction de l'unique formule à répliquer par étapes. Nous simplifierons ainsi la compréhension. Le premier enjeu consiste à trouver la date du Lundi intervenant avant le premier jour du mois choisi. C'est en effet cette première donnée qui entame la construction du calendrier à partir de la cellule B4. L'astuce consiste à retrancher le numéro du jour de semaine à la date en cellule J9, à une unité près, pour retomber sur le Lundi précédent.

• Sélectionner la case de la première date à trouver en cliquant sur la cellule B4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Cliquer sur la cellule du premier jour du mois en J9,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $J$9,

En effet, cette formule, lorsqu'elle sera aboutie, est destinée à être répercutée sur toutes les cellules du calendrier. Et chaque calcul doit faire référence à ce premier jour. Sa cellule ne doit donc pas bouger.

• Taper le symbole du moins (-) pour annoncer la soustraction à suivre,
• Inscrire la fonction du jour de semaine, suivie d'une parenthèse, soit : JourSem(,
• Cliquer de nouveau sur la cellule J9 pour lui passer la date du premier jour du mois,
• Comme précédemment, enfoncer la touche F4 du clavier pour la figer,
• Puis, taper un point-virgule suivi du chiffre 2 : ;2 pour raisonner sur le calendrier français,

Par défaut, sans autre indication, cette fonction JourSem raisonne sur le calendrier américain. Et dans ce calendrier, c'est le dimanche qui est considéré comme le premier jour de la semaine. Avec le chiffre 2 en second paramètre, c'est bien le Lundi qui représente le premier jour de la semaine.

• Fermer la parenthèse de la fonction JourSem,

Nous venons de retrancher à la date de ce premier jour du mois, le nombre de jours correspondant à son numéro de semaine. Le 1 er Mai 2021 est par exemple tombé un Samedi. Il s'agit du sixième jour de la semaine. En retranchant six unités à cette date, nous remontons au Dimanche précédent et non au Lundi. Nous devons donc ajuster ce calcul en ajoutant une unité.

• En conséquence, ajouter une unité : +1 et valider la formule par la touche Entrée,

Nous obtenons bien la date du Lundi précédent la date du premier jour du mois choisi. C'est sur cette base que nous allons pouvoir naturellement déduire tous ceux qui suivent, rappelons-le, avec une seule formule.

Les jours suivants dans le mois
Maintenant pour pouvoir répliquer la formule sur les colonnes de droite, nous devons être en mesure de déduire les jours suivants dans la même semaine. C'est une incrémentation naturelle qui doit être opérée sur cette première date au gré de la progression de la formule répliquée sur la droite. Pour cela, rien de plus simple, il suffit d'employer la fonction Colonne en la faisant agir sur une cellule de la première colonne, par exemple sur la cellule A1. Comme vous le savez, cette fonction retourne l'indice de colonne d'une cellule. Au gré de la réplication de la formule sur les autres colonnes, elle retournera un numéro grandissant permettant d'incrémenter naturellement d'une unité supplémentaire cette première date du calendrier.

• Sélectionner de nouveau la cellule B4,
• Dans sa barre de formule, cliquer à la toute fin de la syntaxe pour y placer le point d'insertion,
• Taper le symbole plus (+) pour annoncer les unités à incrémenter,
• Inscrire la fonction pour l'indice de colonne suivie d'une parenthèse, soit : Colonne(,
• Désigner une cellule de la première colonne en cliquant par exemple sur A1,
• Puis, fermer la parenthèse de la fonction Colonne,

Pour son premier calcul, cette fonction retourne la valeur 1 puis 2, 3 etc... au gré de la réplication sur la droite. Or, cette première date ne doit pas être incrémentée. Ce sont les suivantes qui doivent l'être. Pour réajuster ce décalage, nous devons retrancher une unité à son résultat.

• Retrancher une unité à ce score, soit : -1,
• Puis valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, cliquer et glisser la poignée de la cellule à l'horizontale jusqu'en colonne H,

Cette recopie reproduit bien la logique de la formule mais réplique également les attributs de format, annihilant la mise en valeur différente pour les jours de Week-End. C'est la raison pour laquelle il est opportun dans l'enchaînement de cliquer sur la balise active qui se déclenche en bas à droite de la sélection. Dès lors, il ne reste plus qu'à opter pour l'option : Recopier les valeurs sans la mise en forme.

Et dès lors, vous avez le plaisir de constater qu'une seule formule permet de calculer le premier Lundi et les jours suivants dans la semaine, sur la base d'un choix sur l'année et d'un autre sur le mois.

Bien entendu à ce stade, si vous répliquez la sélection sur les lignes du dessous, c'est la même série de dates pour la première semaine qui est répliquée. Nous venons de fonder l'incrémentation des jours en colonne. Il nous reste à trouver le subterfuge pour produire le même effet afin d'incrémenter les dates en lignes sur les semaines suivantes.

Les jours des semaines suivantes dans le mois
Le principe est finalement assez similaire. Cette fois nous devons exploiter la fonction Ligne à exercer au départ sur une cellule de la première ligne, par exemple A1. Pour passer à la semaine suivante, le pas n'est plus d'une unité mais de sept (Ligne(A1)*7). Mais la première date ne doit pas bouger. Nous devons donc lui soustraire sept unités (Ligne(A1)*7 -7). Pour les suivantes en revanche, nous aurons bien l'incrémentation souhaitée (Ligne(A2)*7-7=2*7-7=14-7=7), soit une semaine de plus.

• Sélectionner de nouveau la date du premier Lundi en cliquant sur la cellule B4,
• Dans la barre de formule, cliquer à la fin de la syntaxe pour y placer le point d'insertion,
• Taper le symbole plus (+) pour annoncer l'incrémentation à suivre,
• Inscrire la fonction pour l'indice de ligne suivie d'une parenthèse, soit : Ligne(,
• Désigner une cellule de la première ligne en cliquant par exemple sur A1,
• Fermer la parenthèse de la fonction Ligne,
• Multiplier ce résultat par sept unités, soit : *7,
• Puis, retrancher sept unités à ce score, soit : -7,
• Dès lors, valider la formule par le raccourci clavier CTRL + Entrée,
• La répliquer sur la droite avec la poignée jusqu'en colonne H,
• Utiliser la balise active pour conserver l'intégrité de la mise en forme,
• Puis, glisser la poignée de la sélection vers le bas jusqu'en ligne 9,
• Là encore, exploiter la balise active pour préserver les attributs de mise en forme,

Comme vous pouvez l'apprécier, nous sommes parvenus à recomposer toutes les dates pour le mois et l'année choisis sur la base d'une seule formule.



Et bien entendu, si vous changez de mois et/ou d'année, toutes les dates se recomposent parfaitement pour reconstruire le calendrier demandé en cohérence.

Atténuer les dates hors du mois
Pour parfaire la solution, nous proposons de bâtir une règle de mise en forme conditionnelle capable de détecter les dates situées avant et après le mois choisi pour minorer leur mise en valeur. En les rendant plus discrètes, nous obtiendrons une vue claire du calendrier avec ses bornes.

• Sélectionner toutes les dates du calendrier, soit la plage de cellules B4:H9,
• 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 ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Puis, saisir la syntaxe suivante : =Mois(B4)<>Mois($J$9),

L'analyse porte sur la comparaison du mois de la première date du calendrier avec celui de la date utilisée pour confectionner ce calendrier. Notez que la cellule B4 est totalement défigée pour respecter l'analyse chronologique. Ainsi, tous les mois des dates du calendrier vont être comparés avec le mois de la date de référence qui elle reste naturellement figée. Si ce critère d'inégalité est vérifié, il indique que la date en cours d'analyse ne fait pas partie du mois en cours. Sa mise en forme doit donc être retouchée conditionnellement pour atténuer son aspect.

• Pour cela, cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un gris atténué pour la couleur du texte,
• Puis, valider cet attribut de format avec le bouton Ok,
• De retour sur la première boîte de dialogue, cliquer sur Ok pour valider la règle,

De retour sur la feuille, dès lors que vous changez de période, non seulement le calendrier se recompose automatiquement, mais dans le même temps, les plages variables des dates hors propos sont automatiquement atténuées pour un rendu explicite.

Pour rappel, la syntaxe de l'unique formule que nous avons bâtie pour construire ce calendrier dynamique est la suivante

=$J$9-JOURSEM($J$9;2) + 1 + COLONNE(A1) - 1 + LIGNE(A1)*7-7