Un est un calendrier qui pourra être utilisé pour tous les jours de l'année, quelle que soit l'année. Vous n'aurez donc pas à recréer votre calendrier tous les ans et les jours se recalculeront automatiquement à chaque changement d'année. Dans ce tutoriel, nous allons créer ensemble un calendrier perpétuel avec Excel avec un affichage des weekends et jours fériés que vous soyez sur Windows ou Mac.
Création du modèle de calendrier perpétuel sur Excel
Création et renommage des onglets
Ouvrez un nouveau classeur Excel et renommez l'onglet Calendrier par un double-clic dessus. Créez un autre onglet (que nous utiliserons plus tard) en cliquant sur Nouvelle feuille
Redimensionnement des cellules
Afin que ce calendrier tienne sur une seule page A4 et reste lisible, nous allons redimensionner les colonnes et lignes.
Redimensionnez les colonnes A à AI en les sélectionnant, en faisant un clic droit sur les en‑têtes > Largeur de colonne..., en saisissant 2,25 dans la boîte de dialogue et en cliquant sur OK.
Redimensionnez la première ligne par un clic droit dessus > Hauteur de ligne... et indiquez 40. Faites de même pour les autres lignes :
Pour centrer tout le calendrier en une seule fois, faites un Ctrl + A (ou Cmd + A sur Mac) et cliquez sur Aligner au centre
Création de la cellule de l'année à prendre en compte
Fusionnez les cellules A1 à AI1 en les sélectionnant puis en cliquant sur Fusionner et centrer
Ajout de l'image
Fusionnez les cellules A2 à AI2 et insérez-y l'image de votre choix en utilisant le bouton Images
Ajout des jours de la semaine
En B5, saisissez L pour Lundi, puis, en B6, M pour Mardi et ainsi de suite jusqu'en H5 où vous devriez avoir un D. Saisissez la même suite de lettres pour les plages suivantes :
Fusion et coloration des mois
Sélectionnez les cellules A4 à H4 et fusionnez-les avant de leur appliquer une couleur de fond (#4F81BD dans mon exemple) en utilisant le bouton Couleur de remplissage
Pour gagner du temps, utilisez le pinceau pour reproduire la mise en forme
Procédez de la même manière pour les autres colonnes en fusionnant et en appliquant une couleur identique aux cellules J4 à Q4, J13 à Q13 et J22 à Q22 (#C0504D dans mon exemple), puis S4 à Z4, S13 à Z13, S22 à Z22 (#1F497D dans mon exemple) et enfin AB4 à AI4, AB13 à AI13, AB22 à AI22 (#F79646 dans mon exemple).
Coloration des jours et semaines
Pour chaque bloc, sélectionnez la ligne des jours et la colonne qui nous servira pour les numéros de semaine pour appliquer une couleur de fond un peu plus clair que le mois et une couleur de police blanche. Ce qui donnerait, pour le premier mois dans mon exemple, une couleur de fond #95B3D7 pour les cellules A5 à A11 et B5 à H5, puis la même couleur pour les cellules A14 à A20, B14 à H14, A23 à A29 et B23 à H23.
Faites de même pour les autres blocs :
- J5 à J11, K5 à Q5, J14 à J20, K14 à Q14, J23 à J29, K23 à Q23 de couleur #DA9694 ;
- S5 à S11, T5 à Z5, S14 à S20, T14 à Z14, S23 à S29, T23 à Z23 de couleur #538DD5 ;
- AB5 à AB11, AC5 à AI5, AB14 à AB20, AC14 à AI14, AB23 à AB29, AC23 à AI23 de couleur #FABF8F.
Définition des noms des mois
Pour simplifier notre travail lors de la création des formules, nous allons nommer les cellules des mois. Pour cela, sélectionnez le 1 er mois (A4) et, dans la Zone nom, saisissez Mois_1. Puis, pour les autres mois (en vous déplaçant de gauche à droite, puis vers le bas), nommez-les Mois_2, Mois_3, etc. jusqu'à ce que vous arriviez au dernier bloc qui se nommera Mois_12.
Ajout des fonctions Excel pour calculer automatiquement les dates de chaque jour
Ajout des fonctions pour les mois
Pour les noms des mois
Comme les dates des jours vont être basées sur le mois, on ne peut pas se contenter de l'écrire en toutes lettres : nous allons donc utiliser une fonction.
En A4, saisissez =DATE(Annee_choisie;MOIS(1);1). Cela signifie que nous souhaitons obtenir le 1 er jour du 1 er mois de l'année indiquée en A1.
En J4, nous allons nous baser sur la fonction précédente pour l'incrémenter d'un mois. Saisissez =MOIS.DECALER(Mois_1;1) qui ajoute 1 mois à la date précédente. Procédez de la même manière pour les autres mois en remplaçant Mois_1 par Mois_2, puis Mois_3, etc. jusqu'au dernier mois qui obtiendra la fonction =MOIS.DECALER(Mois_1;11).
Pour les numéros des jours
Les jours ne vont pas être calculés de la même façon sur toute l'année. En effet, comme les premières et dernières lignes de chaque mois peuvent être vides, il faut prendre en compte cette possibilité.
En B6, saisissez =SI(JOURSEM(Mois_1)=2;Mois_1;""). Avec cette formule, nous vérifions si le premier jour de l'année est un lundi (2). Si c'est le cas, le premier sera dans cette cellule, sinon rien.
En C6, saisissez =SI(B6<>"";B6+1;SI(JOURSEM(Mois_1)=3;Mois_1;"")). Dans ce cas, c'est la même formule sauf qu'on vérifie si c'est un mardi (3) et, avant, on vérifie si la cellule précédente n'était pas vide (si c'était le cas, on incrémenterait de 1).
Pour les cellules suivantes, on peut se contenter d'utiliser la poignée de recopie et de remplacer 3 par 4, puis 5, 6, 7 et 1.
Pour la 2 e ligne, le mois étant commencé, il suffit d'écrire =H6+1 en B7, puis =B7+1 en C7 et d'utiliser la poignée de recopie pour le reste de la ligne. Faites de même pour les 3 autres lignes.
Pour la dernière ligne du mois, en B11, saisissez =SI(H10<FIN.MOIS(Mois_1;0);H10+1;""). Dans ce cas, avant d'incrémenter, on vérifie si H10 n'est pas le dernier jour du mois de janvier et il en est de même pour les autres cellules de la ligne où l'on vérifie si la date précédente n'est pas le dernier jour du mois, auquel cas la cellule active serait vide.
Pour les autres mois, il y a une astuce qui peut vous faire gagner beaucoup de temps. Copiez la plage B6 à H11, puis cliquez droit sur la cellule K6 et, dans les options de collage, choisissez Formules
Procédez de la même manière pour les autres mois en copiant une plage déjà faite et en remplaçant la plage nommée par celle du mois que vous modifiez.
Pour les numéros des semaines
Pour renvoyer le numéro de la semaine pour chaque ligne, une seule formule sera utilisée (il suffira ensuite de modifier les cellules utilisées ) : =SI(B6<>"";NO.SEMAINE(B6;2);SI(H6<>"";NO.SEMAINE(H6;2);"")).
Comme une ligne aura forcément la première ou dernière cellule qui sera non vide (sinon, on n'indique pas de numéro de semaine), c'est de cette manière que l'on conditionne notre formule de calcul.
Modification du format de cellule
Pour les mois
Pour le moment, les mois sont indiqués sous la forme jj/mm/aaaa, ce qui n'est pas très visuel pour un calendrier : nous allons donc modifier le format de cellule. Sélectionnez chaque cellule de nom de mois en maintenant la touche Ctrl enfoncée (ou Cmd sur Mac) lorsque vous cliquez sur chacun d'eux, puis cliquez sur la flèche
Pour les jours
Les jours sont, quant à eux, affichés sous forme de numéro de série, ce qui n'est pas pratique non plus. Sélectionnez donc toutes les cellules où pourrait se trouver une date (même celles qui sont vides à cet instant, car, d'une année à l'autre, cela pourrait changer) et modifiez le format de cellule comme précédemment en saisissant jj.
Création des bordures
Maintenant que nous n'allons plus utiliser la poignée de recopie, nous pouvons ajouter une bordure pour chaque bloc de mois en les sélectionnant 3 par 3 (avec la touche Ctrl enfoncée ou Cmd sur Mac) de façon verticale et en appliquant une bordure de la même couleur que la couleur de remplissage du mois via le bouton Bordures
Application des mises en forme conditionnelles aux jours non fériés
Création du tableau des jours fériés
Dans l'onglet Jours fériés que nous avons créé au début de ce tutoriel, créez un tableau en A1 en cliquant sur Tableau
Dans ce tableau, nous allons référencer tous les jours qui devront apparaître comme fériés (pour la France) dans le calendrier (vous pouvez aussi y saisir vos congés, mais vous devrez créer une ligne pour chaque jour).
Saisissez, à partir de la cellule B2, les jours fériés suivants :
- Jour de l'an ;
- Lundi de Pâques ;
- Fête du Travail ;
- Victoire des Alliés ;
- Ascension ;
- Lundi de Pentecôte ;
- Fête nationale ;
- Assomption ;
- La Toussaint ;
- Armistice ;
- Noël.
Modifiez le format de cellule des dates de A2 à A12 pour le format Date longue (directement accessible via la liste déroulante du groupe Nombre).
Enfin, nommez cette plage Dates_jours_feries via la Zone nom.
Cas des jours fériés qui tombent tous les ans à la même date
Le Jour de l'an tombant tous les ans à la même date, il suffit d'écrire en A2 : =DATE(Annee_choisie;1;1). Cela signifie que nous souhaitons obtenir le 1 er jour du 1 er mois de l'année définie dans la cellule Annee_choisie.
Faites de même pour les autres dates fixes :
Cas des jours fériés qui ne tombent pas tous les ans à la même date
Certaines fêtes religieuses (lundi de Pâques, Ascension et lundi de Pentecôte) ne se déroulent pas tous les ans à la même date, il faut donc effectuer un calcul qui va se baser sur la date du jour de Pâques.
Selon la définition, Pâques est le dimanche qui suit le 14 e jour de la Lune qui atteint cet âge le 21 mars ou immédiatement après. Pâques tombe donc entre le 22 mars et le 25 avril.
Pour simplifier la création de ces fonctions, nous allons placer le calcul pour Pâques dans une cellule à part, mais vous pourriez très bien l'imbriquer dans les 3 formules qui l'utilisent.
En D1, j'écris Pâques : et, en E1, je saisis : =ARRONDI(DATE(Annee_choisie;4;MOD(234-11*MOD(Annee_choisie;19);30))/7;0)*7-6.
Cette fonction permet de calculer la date de Pâques de façon automatique en y mélangeant des fonctions d'Arrondi, de Date et de Mod (calcul du reste d'une division).
Enfin, je nomme cette cellule Paques via la Zone nom et je lui attribue le format Date courte via la liste déroulante du groupe Nombre.
Pour le lundi de Pâques, il suffit d'écrire dans le tableau =Paques+1 puisqu'il s'agit toujours du lundi qui suit immédiatement Pâques et que cette date tombe toujours un dimanche.
Le calcul de l'Ascension est tout aussi simple. Cette date se déroulant 40 jours après Pâques, dans le tableau, saisissez =Paques+39 (pour tomber sur un jeudi).
Le lundi de Pentecôte se déroulant 50 jours après Pâques, dans le tableau, saisissez =Paques+50 (il doit tomber un lundi).
Création des mises en forme conditionnelles
Pour rendre notre calendrier plus visuel, nous allons le colorer grâce à la mise en forme conditionnelle. Pour les 4 conditions que nous allons créer, nous procéderons toujours de la même manière : cliquer sur Mise en forme conditionnelle
Création de la mise en forme conditionnelle pour les cellules vides
Choisissez le type de règle Appliquer une mise en forme uniquement aux cellules qui contiennent et, dans la première liste, choisissez Cellules vides. Puis, cliquez sur Format... pour appliquer le format de votre choix. Dans mon exemple, il s'agit d'une couleur de police et de remplissage grise (#D9D9D9). Pour terminer, cliquez sur OK.
Création de la mise en forme conditionnelle pour la date du jour
Choisissez le type de règle Appliquer une mise en forme uniquement aux cellules qui contiennent et, dans la première liste, choisissez Dates se produisant et remplacez Hier par Aujourd'hui. Cliquez sur Format... pour appliquer le format de votre choix. Dans mon exemple, il s'agit d'une couleur de police vert foncé (#006100) et d'une couleur de remplissage vert clair (#C6EFCE). Pour terminer, cliquez sur OK.
Création de la mise en forme conditionnelle pour les weekends
Choisissez le type de règle Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué et, dans le champ, saisissez la formule suivante : =OU(JOURSEM(B6)=7;JOURSEM(B6)=1). Cette fonction permet de rechercher les samedis (7) et les dimanches (1). Cliquez sur Format... pour appliquer le format de votre choix. Dans mon exemple, il s'agit d'une couleur de remplissage orange clair (#FDE9D9). Pour terminer, cliquez sur OK.
Création de la mise en forme conditionnelle pour les jours fériés
Choisissez le type de règle Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué et, dans le champ, saisissez la formule suivante : =NB.SI(Dates_jours_feries;B6). Cette fonction permet de vérifier si la valeur de la cellule à laquelle est appliquée la mise en forme se trouve dans le tableau des jours fériés. Cliquez sur Format... pour appliquer le format de votre choix. Dans mon exemple, il s'agit d'une couleur de police bordeaux (#9C0006) et d'une couleur de remplissage rose (#FFC7CE). Pour terminer, cliquez sur OK.
Application des mises en forme conditionnelles à toutes les cellules concernées
Une fois toutes vos mises en forme conditionnelles créées, cliquez sur le bouton Mise en forme conditionnelle
Finalisez votre calendrier perpétuel
Si vous souhaitez imprimer ce calendrier, je vous conseille de cliquer sur la flèche
Vous pouvez masquer le quadrillage si vous le souhaitez en vous rendant dans le menu Affichage et en décochant la case Quadrillage dans le groupe Afficher.
Testez votre calendrier perpétuel
La création de notre calendrier étant à présent terminée, il ne vous reste plus qu'à modifier l'année en A1 pour vérifier que les jours et mises en forme conditionnelles se modifient bien automatiquement et qu'elles sont correctes.
Vous pouvez télécharger le fichier finalisé au bas de l'articlePour bien démarrer l'année 2021, pour toute inscription au Master Club avant le 5 janvier 2021, je vous offre le Kit du bon démarrage avec les formules Excel. .Si vous souhaitez aller plus loin dans l' apprentissage des formules d'Excel, découvrez le Master Club Excel qui, chaque mois, vous permet de maîtriser 5 fonctions via des cours en PDF et vidéo et des exercices avec leurs corrigés.
Jeu de briques
Snake
Pacman
Bubble