Site icon Excel Exercice

Création d’un calendrier automatique avec Excel

Frédéric LE GUEN
Calendrier Excel couverture

Comment créer un calendrier avec Excel où les couleurs des week-ends et des jours fériés changent de façon automatique pour chaque mois. Cet article va vous détailler pas-à-pas comment le construire.

Si vous souhaitez un calendrier qui enregistre les données d'un mois sur l'autre, reportez-vous à cet article ou télécharger le fichier.

Télécharger le calendrier avec enregistrement des données

Étape 1 : Ajouter les noms des employés

En colonne A, vous allez écrire les noms de vos employés.

Étape 2 : Ajouter d'un menu déroulant sous forme d'objet.

Il est très facile de créer des menus déroulants dans Excel, mais avec cette technique, il n'est pas possible d'y attacher une macro. Dans ce classeur, la macro va nous servir à masquer les jours selon le nombre de jours dans le mois.

Assurez-vous d'avoir le menu Développer d'affiché dans votre ruban. Si tel n'est pas le cas, allez dans le menu Fichier > Options > Personnaliser le ruban, puis cliquez sur le menu Développeur.

Étape 3 : Insertion d'un objet Menu déroulant

Étape 4 : Création de la liste des mois

Maintenant, nous allons créer la liste des mois quelque part dans notre classeur (dans une colonne assez éloignée).

Ne perdez pas de temps à écrire les mois les uns à la suite des autres, la poignée de recopie le fait pour vous.

Étape 5 : Lier le menu déroulant à une cellule

Ensuite, il faut lier l'objet "Menu déroulant" à une cellule du classeur pour récupérer la valeur sélectionnée.

  1. Sélectionnez votre objet Menu déroulant
  2. Faites un clic-droit
  3. Sélectionnez Format de contrôle

La boîte de dialogue suivante s'ouvre

Dans l'onglet Contrôle

Maintenant, si vous sélectionnez le mois de Mai, la cellule liée contiendra la valeur 5. Si vous sélectionnez Septembre, la valeur dans la cellule liée sera 9 et ainsi de suite.

Pourquoi avoir choisi spécifiquement la cellule A1 ? Tout simplement parce que le menu déroulant va masquer le résultat de la la cellule liée à vos utilisateurs

Étape 6 : Menu déroulant pour les années

Reproduisez les mêmes manipulations pour les avoir un menu déroulant pour les années

Étape 7 : Créer la date en fonction du mois et de l'année sélectionné

Nous allons maintenant créer une formule qui va récupérer les cellules liées A1 (pour les mois) et A2 (pour les années)  pour retourner le premier jour du mois.

Pour faire cela, nous allons utiliser la fonction DATE avec le contenu des cellules liées.

La formule est donc

=DATE(A2+2014;A1;1)

Étape 8 : Création des autres jours du mois

Pour calculer les autres jours, la formule est plus facile. Il suffit de rajouter 1 à la cellule précédente et de recopier cette formule sur la plage de données C6 jusqu'à AF6

=B6+1

Étape 9 : Changer le format de la date

Dans cette étape, nous allons changer le format des dates pour faire apparaître le jour en lettre et en chiffres

Étape 10 : Changer l'orientation du texte

Maintenant, nous allons changer l'orientation des dates pour les afficher verticalement.

Et pour finir le travail, ajuster la taille des colonnes de B à AF

  1. Sélectionnez les colonnes de B:AF
  2. Faites un clic-droit sur l'en-tête des colonnes
  3. Choisissez l'option Largeur de colonnes
  4. Indiquez une largeur de 2.5

Le calendrier avance très bien. En changeant les valeurs dans les menus déroulants, vous voyez les jours du mois sélectionné.

Étape 11 : Mise en forme de votre calendrier

Ajoutez des bordures ainsi que de la couleur à votre calendrier.

Etape 12 : Ajoutez un titre dynamique

Nous allons maintenant créer un titre qui va s'adapter au mois et à l'année sélectionnée.

Première et dernière date du mois

La formule pour la premier jour est :

=DATE(A2+2014;A1;1)

La formule pour la date du dernier jour du mois est :

=DATE(A2+2014;A1+1;1)-1

Formule pour le titre dynamique

Il ne nous reste plus qu'à insérer ces 2 fonctions dans une fonction TEXTE

="Période du "&TEXTE(DATE(A2+2014;A1;1);"jj mmmm aaaa")&" au "&TEXTE(DATE(A2+2014;A1+1;1)-1;"jj mmmm aaaa")

Et le résultat devient :

A chaque fois que vous changez le mois ou l'année, votre titre se modifiera

Etape 13 : Changer la couleur des week-ends et des jours fériés

Pour changer les couleurs des jours féries, nous utiliserons les mises en forme conditionnelles et surtout les méthodes avec des formules personnalisées

Pour que notre calendrier soit effectivement automatique, nous devons créer 2 règles pour la mise en forme conditionnelle

Règle pour les week-ends

  1. Sélectionnez votre plage de données B6:AF13
  2. Créer une nouvelle règle de mise en forme (Accueil>Mise en forme conditionnelle>Nouvelle règle)
  3. Sélectionnez l'option Utilisez une formule pour déterminer sur quelles cellules le format sera appliqué
  4. Ecrivez la formule =JOURSEM(B$6;2)>5
  5. Changez la couleur de remplissage (ici de l'orange)

Règle pour les jours fériés

Ici, nous devons intégrer à notre classeur la liste des jours fériés de votre pays. En cliquant sur ce lien, vous trouverez les formules pour calculer les jours fériés français.

La formule pour cette règle est différente du calcul précédent. Ici, nous utiliserons la fonction NB.SI.ENS

  1. Sélectionnez votre plage de données B6:AF13
  2. Créez une nouvelle règle de mise en forme (Accueil>Mise en forme conditionnelle>Nouvelle règle)
  3. Sélectionnez l'option Utilisez une formule pour déterminer pour quelles cellules le format sera appliqué
  4. Ecrivez la formule =NB.SI.ENS(Feuil2!$B$3:$B$15;B$6)>0
  5. Changez la couleur de remplissage en rouge

Vérifier vos règles

Ouvrez le manager de règles conditionnelles (Accueil>Mise en forme conditionnelle>Gérer les règles), vous pouvez visualiser les 2 règles créées dans votre feuille de calculs.

N'oubliez pas de sélectionner l'option "Cette feuille de calcul" dans le premier menu déroulant

Etape 14 : Masquer les dernières colonnes par Macro

Comme tous les mois n'ont pas le même nombre de jours, il faut créer un programme pour qu'Excel masque les colonnes pour les jours au-delà du mois sélectionné.

Toutes les explications sur le code vous sont données dans cet article

Le programme va lire les colonnes AD, AE et AF pour vérifier que la valeur du mois dans ces cellules est égale à la valeur contenue en A1 (valeur du mois sélectionné). Si ce n'est pas le cas, la colonne est masquée.

  1. Appuyez sur les touches Alt + F11 pour ouvrir le Visual Basic Editor
  2. Depuis la barre de menu, sélectionnez Insertion>Module
  3. Copiez le code suivant
Sub Masquer_Jour()
Dim Num_Col As Long
For Num_Col = 30 To 32 ' Boucle sur les cellules des jours 29, 30 et 31
  If Month(Cells(6, Num_Col)) >= Cells(1, 1) Then
    Columns(Num_Col).Hidden = True
  Else
    Columns(Num_Col).Hidden = False
  End If
Next
Range("B6:AF13").ClearContents 'Supprime le contenu dans les cellules
End Sub

Etape 15 : Lier les menus déroulants à la macro

Pour finir le travail, il nous reste à lier les 2 menus déroulants avec la macro. De cette façon, à chaque nouvelle sélection, la macro sera lancée.

  1. Sélectionnez l'un de vos menus déroulants
  2. Faites un clic-droit
  3. Sélectionnez Assignez une macro
  4. Sélectionnez le nom de votre macro
  5. Validez
  6. Refaites la même manipulation pour le second menu déroulant

Maintenant, à chaque fois que vous sélectionnez un mois ou une année dans les menus déroulants, votre calendrier se mettra à jour et les colonnes se masqueront (ou s'afficheront) automatiquement

Vidéo explicative

Quitter la version mobile