Dans cet article, nous allons vous montrer comment créer un calendrier changeant dynamiquement dans Excel.
Télécharger le classeur
Remplir les variables
La première étape consiste à créer les variables à utiliser dans les formules. Ces variables doivent fournir les informations actuelles afin que le calendrier puisse se mettre à jour automatiquement. Voici la liste des variables:
- Date actuelle
- Le numéro du jour actuel du mois
- Le numéro du mois actuel
- Année actuelle
- La date du premier jour du mois actuel
- Le numéro du jour actuel de la semaine
- Le numéro de la semaine actuelle de l’année
Nous utiliserons les fonctions AUJOURD’hui, JOUR, MOIS, ANNÉE, DATE, JOUR DE LA SEMAINE et WEEKNUM. Brièvement, alors que la fonction TODAY renvoie la date du jour présent, les fonctions DAY, MONTH, YEAR, WEEKDAY et WEEKNUM analysent les valeurs de date respectives. La fonction DATE renvoie la valeur de date de l’année, du mois et du jour donnés.
Dans la capture d’écran suivante, vous pouvez voir les noms que nous avons donnés et les formules générées le 20 novembre 2020.
Nous avons nommé chaque variable pour faciliter notre travail lors de la création de formules. L’étape suivante pour créer un calendrier dans Excel consiste à créer un contour pour l’interface de calendrier.
Contour du calendrier dans Excel
Avant de créer les formules réelles qui génèrent les jours du mois, nous devons d’abord placer un contour pour aider à créer des formules et fournir également une référence visuelle.
Les jours seront placés sur un tableau de 7 colonnes et 6 lignes. Nous devons également augmenter ces nombres de 2 pour les titres et les cellules auxiliaires. Alors que les titres des colonnes et des lignes incluent les jours de la semaine pour les colonnes et les numéros de semaine par lignes, les cellules auxiliaires seront des nombres consécutifs à partir de 1.
Le calendrier peut ressembler à ci-dessous.
Utilisez les bordures ou les couleurs d’arrière-plan comme vous le souhaitez. Le point important est d’utiliser un numéro de semaine dynamique dans le titre. Référencez la fonction WEEKNUM pour la première semaine et ajoutez 1 pour chaque semaine consécutive.
| D | |
| 4 | = Numéro de semaine |
| 5 | = F4+1 |
| 6 | = D5+1 |
| 7 | = F6+1 |
| 8 | = F7+1 |
| 9 | = D8+1 |
Continuons à créer un calendrier dans Excel en remplissant les numéros de jour.
Jours de génération
Si vous utilisez une mise en page similaire et que vous ne souhaitez pas vous soucier des détails de la formule, n’hésitez pas à copier la formule ci-dessous. Collez-le ensuite dans la cellule en haut à gauche de la zone jour de votre calendrier et remplissez les cellules restantes. Les cellules G2 et E4 font référence aux premières cellules des colonnes auxiliaires. Ainsi, vous devrez peut-être ajuster ces références, sauf si la première cellule est G4.
Maintenant, regardons de plus près la formule. Tout d’abord, la formule utilise la fonction DATE, qui renvoie une date par année, mois et jour donnés. Les 2 premiers arguments sont fournis par les variables YearNumber et MonthNumber qui ont déjà été calculées.
La partie jour de la formule vise à générer des nombres de jours sur une base de 7, car il y a 7 jours dans une semaine. 7 * ($E4-1) une partie de la formule vérifie le numéro de semaine dans la colonne d’aide E, et génère une base, commençant par 0 et augmentant de 7 à chaque fois.
| Semaine | Base |
| 1 | 0 |
| 2 | 7 |
| 3 | 14 |
| 4 | 21 |
| 5 | 28 |
| 6 | 35 |
Nous pouvons augmenter ou diminuer ce nombre autant que la différence entre le jour de la semaine du premier jour du mois par le numéro de la semaine du jour. En d’autres termes, nous obtenons le premier jour du mois en cours comme point d’ancrage. Par exemple, c’est le 7e jour de la semaine (en base du lundi au dimanche) pour le 1er novembre 2020.
Si la semaine de notre calendrier suppose que le lundi est le premier jour de la semaine, le lundi devrait être 6 jours avant (= 1-7) le premier jour du mois. Ainsi, nous obtenons des chiffres comme ci-dessous.
| Lun | Mar | Mer | Jeu | Ven | Sam | Dim |
| -6 | -5 | -4 | -3 | -2 | -1 | 0 |
Ajoutez ces valeurs à 1 pour décaler les nombres. Ainsi, le dimanche peut devenir le premier jour comme dans l’exemple suivant.
| Lun | Mar | Mer | Jeu | Ven | Sam | Dim |
| -5 | -4 | -3 | -2 | -1 | 0 | 1 |
En conséquence, nous avons 2 tableaux pour 2 dimensions. Pour créer une table, nous devons ajouter les deux tableaux ensemble.
Ces chiffres représentent le jour du mois en cours. Il y a des nombres inférieurs à 1 ou supérieurs à 31, qui ne peuvent évidemment pas être des jours du mois.
La fonction DATE gère ces types de nombres en générant une date du mois précédent ou du mois suivant. Par exemple, -2 devient deux jours avant le premier jour du mois donné.
Enfin, si vous utilisez ces chiffres dans la fonction DATE, vous pouvez obtenir les dates réelles du mois en cours.
Formatage
Nombres de jours
Maintenant, remplissons les jours. Vous pouvez modifier la mise en forme des valeurs sans modifier la valeur réelle. Cette fonctionnalité est appelée Formatage des nombres. Suivez les étapes pour ajuster la mise en forme des nombres pour afficher uniquement les jours.
- Sélectionnez les dates dans la zone jour
- Appuyez sur Ctrl + 1 pour ouvrir la boîte de dialogue Format des cellules
- Activez l’onglet Numéro s’il n’est pas déjà ouvert
- Sélectionnez Personnalisé dans la liste des catégories
- Tapez la lettre d dans la zone Type
- Cliquez sur OK pour enregistrer
Maintenant, la mise en page du calendrier est terminée.
Si vous souhaitez supprimer le remplissage pour les dates à un chiffre, vous pouvez utiliser dd au lieu de d unique. Pour en savoir plus sur le formatage des nombres: Formatage des nombres dans Excel – Tout ce que vous devez savoir
Mise en évidence du jour présent
Enfin, nous utiliserons le formatage conditionnel pour définir différentes couleurs pour les jours qui ne sont pas dans le mois actuel et mettre en évidence le jour présent.
Pour ajouter ces options de mise en forme conditionnelle;
- Sélectionnez la plage de jours
- Cliquez sur l’icône de Mise en forme conditionnelle sous l’onglet Accueil du Ruban
- Sélectionnez une nouvelle règle
- Sélectionnez Utiliser une formule pour déterminer les cellules à formater
- Entrez une formule pour définir la règle de mise en évidence du jour en cours
= G4= TODAY()
G4: La cellule en haut à gauche de la plage comprend les jours. Utilisez une référence relative (n’utilisez pas $). - Cliquez sur le bouton Formater dans la fenêtre d’aperçu de la boîte de dialogue Formater les cellules
- Dans la boîte de dialogue, sélectionnez les options de formatage que vous souhaitez voir dans la cellule actuelle
Nous avons utilisé un fond orange dans cet exemple. - Cliquez sur OK pour appliquer
- La zone d’aperçu affichera vos préférences
- Cliquez sur OK pour appliquer une mise en forme conditionnelle
Voici à quoi ça ressemble:
La formule renvoie une valeur booléenne. Si la valeur de la cellule (G4) est égale au résultat de la fonction TODAY, qui renvoie le jour présent dans un format de date, la formule renvoie TRUE. Sinon, FAUX. Si le résultat est VRAI, Excel applique le formatage dans la cellule.
Il y a 2 points importants ici:
- Vous devez utiliser une référence relative, par exemple G4, sauf si vous ne souhaitez pas qu’Excel remplisse la référence le long de la plage. Considérez comment les formules changent lorsque vous les copiez ailleurs. Excel applique également la même chose pour les formules de mise en forme conditionnelle. Pour plus d’informations, voir: Comment créer une référence absolue Excel et une référence relative
- Les cellules jour doivent renvoyer les valeurs de date réelles. C’est pourquoi nous utilisons le formatage des nombres pour afficher les portions de jour.