Fiche pratique

Créer un agenda automatique sous Excel

Vous n'êtes pas satisfaits de votre agenda électronique ? Vous aimeriez en avoir un personnalisé ? Exportable ? Imprimable ? Vous pouvez le réaliser vous-même sous Excel ! La méthode pas à pas nous est présentée par notre partenaire ExcelPlus.

Nous allons voir comment utiliser quelques formules de dates et les formats conditionnels pour obtenir un calendrier personnalisé et automatique en fonction des années sélectionnées. Nous travaillerons la base, sachant qu'il sera encore possible de personnaliser votre outil par la suite.

Création de la base de l'agenda

Nous allons travailler sur un seul onglet pour un mois, puis nous le dupliquerons 11 fois.
Nous allons construire un agenda en deux parties : à gauche, une synthèse du mois par semaine ; et sur la droite, les jours pour intégrer vos rendez-vous.

Ouvrez un classeur Excel.

a) Sélectionnez l'ensemble des cellules de l'onglet en cliquant sur le triangle en haut à gauche et choisissez un remplissage blanc des cellules. Placez-vous ensuite en B3, et commencez les jours de la semaine. Nous mettrons les numéros des jours en dessous grâce à des formules. Réduisez la taille des colonnes de manière à obtenir un petit tableau compact. 

jours

b) Placer les champs dynamiques : en A1, vous saisissez 1 (correspond au champ du mois, donc ici janvier). Puis dans le 1er onglet qui servira d'onglet de paramètres, nous allons ajouter un champ pour l'année (à customiser plus tard selon vos envies). Placez 2013 en C3.

Paramètrage des dates pour qu'elles se modifient automatiquement

Voici la méthode pour que les dates se modifient automatiquement en fonction du mois, de l'année, mais surtout du jour. Nous avons choisi ici de faire commencer les semaines le lundi. Pour cela, nous allons combiner les fonctions DATE et JOURSEM.

1/ Placez-vous en B4, sous le jour du lundi et saisissez la formule suivante :
=DATE(Paramètres !$C$3 ;$A$1 ;1)-JOURSEM(DATE(Paramètres !$C$3 ;$A$1 ;1) ;2)+1

Découpons-la pour la décrypter :
- La première partie, avec la fonction date, correspond à la date du 1er jour du mois et de l'année saisie dans la cellule A1 et dans l'onglet Paramètres, donc ici le 01/01/2013.
- La seconde partie nous donne le jour de la semaine correspondant à ce jour - vous constatez qu'il s'agit exactement de la même formule date que le début de formule - avec un complément qui est 2. Le 2 indique que la semaine commence un lundi. Le résultat de cette fonction est 2, soit le mardi, le 01/01/2013 étant un mardi.

Pour résumer, nous avons le 01/01/2013 auquel on retire 2 (le mardi, résultat du jour de la semaine) soit le 30/12/2012, auquel nous rajoutons 1 car notre semaine commence un lundi et non un dimanche. Le résultat sera le 31/12/2013 pour le lundi de la semaine du 01/01/2013.
Essayez en saisissant une date différente de la semaine, en remplaçant le 1 de la fonction DATE, et vous verrez que cela fonctionne de la même manière.

2/ En C4, introduisez la formule =B4+1
Recopiez cette formule sur toute la ligne jusqu'au dimanche. Et dans la colonne du lundi, en seconde ligne, soit en B5, tapez =H4+1
Et recommencez comme en C4, en ajoutant 1 à la cellule d'avant.
Recopiez ensuite cette seconde ligne 4 fois de suite.
Le premier mois est maintenant rempli de manière automatique.
Essayez de modifier le 1 en A par un autre mois de l'année, et vous verrez que les jours changent seuls.

debut_planning
 

3/ Ajoutez le mois au-dessus de ce tableau.
Pour cela, fusionnez l'ensemble des cellules de B2 à H2 et saisissez la formule suivante :
=DATE(Paramètres !C3;A1 ;1)
En clair, nous demandons la date du 1er jour du mois de l'année en paramètres, et du mois de la feuille (A1).
Pour modifier le format d'affichage afin qu'il apparaisse sous forme de mois et non de date, allez  dans le format de la cellule en faisant un clic droit puis Format et dans la sélection personnalisée, tapez "mmmm"

format_mois

Mise en forme et ajout des éléments journaliers

a) Mettre les jours correspondants aux mois précédents en gris clair 
Sélectionnez d'abord la première ligne de dates, de B4 à H4, puis dans l'onglet Accueil et Mise en forme conditionnelle, cliquez sur Nouvelle règle puis Appliquer une mise en forme uniquement aux cellules qui contiennent. 
Nous allons demander à ce que les dates inférieures à la date du premier jour du mois de l'onglet soit mises en gris et italique.
Dans Modifier la description de la règle, sélectionnez "Valeur de la cellule", puis "Inférieur ou égal à" avec la formule "=DATE(Paramètres!$C$3;$A$1;1)" et paramétrez le texte en gris et italique dans l'onglet Format.

regle1
b) Mettre les jours correspondants aux mois suivants en gris clair
Sélectionnez d'abord la première ligne de dates, de B8 à H8, puis dans l'onglet Accueil et Mise en forme conditionnelle, cliquez sur Nouvelle règle puis Appliquer une mise en forme uniquement aux cellules qui contiennent.
Nous allons demander à ce que les dates supérieurs ou égales à la date du premier jour du mois de l'onglet soit mises en gris et italique.
Dans Modifier la description de la règle, sélectionnez "Valeur de la cellule", puis "Supérieur ou égal à" avec la formule "=DATE(Paramètres!$C$3;$A$1;1)" et paramétrez le texte en gris et italique dans l'onglet Format.

c) Masquer le dernier jour du mois
L'idée est de masquer la date si le dernier jour du mois se trouve dans la ligne du dessus, et de lui appliquer les mêmes formats si le dernier jour se trouve dans cette ligne. Sélectionnez la dernière ligne du tableau puis dans l'onglet Accueil et Mise en forme conditionnelle, cliquez sur Nouvelle règlepuis Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
Dans la partie Modifier la description de la règle, tapez la formule =MOIS($B$9)>$A$1 et paramétrez la couleur blanche dans Format.
En clair, nous indiquons ici que si la première cellule de la ligne contient une date dont le mois est supérieur à celui de la feuille, le texte sera donc de couleur blanche (donc blanc sur blanc = invisible).

regle3
d) Mettre la date d'aujourd'hui en rouge sur fond rose
Sélectionnez l'ensemble du tableau puis dans l'onglet Accueil et Mise en forme conditionnelle, cliquez sur Nouvelle règle puis Appliquer une mise en forme uniquement aux cellules qui contiennent.
Dans Modifier la description de la règle, sélectionnez "Valeur de la cellule", puis "égale à" avec la formule "=AUJOURD'HUI()" et paramétrez le texte en rouge et le fond en rose dans l'onglet Format.

regle5
Vous devriez ensuite obtenir ceci :

rendu1


Comme vous le constatez, la dernière ligne du tableau (de B9 à H9) est invisible car le dernier jour du mois s'arrête ligne 8.

> Création du tableau des jours du mois

Maintenant créez, à partir de la cellule J2, le tableau des jours du mois avec les horaires de la journée, afin de pouvoir y noter vos rendez-vous. Voici un modèle très simple que vous pourrez personnaliser à votre goût par la suite.

rendu2
Pour rendre votre agenda automatique, vous devez maintenant lui demander d'indiquer les noms des jours en fonction de la date.
Pour cela, placez-vous en K2 et saisissez la formule suivante :
=JOURSEM(DATE(Paramètres !$C$3 ;$A$1 ;K3);1)
Ici, vous reprendrez la même base de formule utilisée dans le tableau de gauche, mais adaptée pour donner le numéro du jour de la semaine. Et comme, cette formule ne donne que le numéro du jour, vous irez dans le format de cellule pour lui demander un affichage du nom du jour. 

format_jour
Recopiez maintenant cette formule jusqu'à la fin de votre ligne et le tableau est désormais prêt !
Il ne vous reste plus qu'à dupliquer cet onglet encore 11 fois, de renommer les onglets selon les mois, puis de saisir sur chaque cellule A1 le numéro du mois correspondant. 
À vous désormais de personnaliser votre nouvel agenda, en y ajoutant logos, couleurs, et autres formats qui vous conviennent. Faîtes fonctionner votre imagination !

Une dernière astuce, si cet agenda est destiné à être diffuser et proposer à d'autres utilisateurs, protégez vos formules et la structure de votre classeur, cela pourra vous éviter des désagréments...

En savoir plus

Cet article a été rédigé en partenariat avec Excel Plus.
Plus d'informations : Thierry Courtot - Cette adresse email est protégée contre les robots des spammeurs, vous devez activer Javascript pour la voir.

 

Note (23 votes)

Notez cet article (Cliquez sur les étoiles)

Ajouter un commentaire

1000 caractères max

Rédiger un commentaire

Pour publier votre commentaire, veuillez-vous identifier.

Se connecter

Vous n’avez pas encore de compte Kalligo ?
Inscrivez-vous dès maintenant en quelques clics :

S'inscrire gratuitement

  • Le 09/11/2014 à 18h06 par Mickael11678

    il y a une erreur dans la premiere partie, comment mettre 2013 en c3 alors qu'il y a deja les jours de la semaine dedans?<br /> Cordialement

  • Le 06/08/2014 à 08h08 par agnes11542

    bonjour, <br /> impossible de créer cet agenda qui me paraît pourtant bien pratique, j'ai beau recopier la formule je me retrouve avec un avertissement de formule incohérente, pas moyen de coriger

  • Le 14/11/2013 à 11h11 par fadime15706

    Très utile, merci à vous !

Publicité

Newsletter

Recevez chaque semaine nos infos et astuces par email !

Témoignage
de Nathalie

Attachée de Direction - CBM Franchiseur

J'attends toujours le mardi avec impatience car je trouve que vos informations sont toujours utiles, concrètes et claires. Je garde tous vos mails et parfois quand cela m'arrive d'hésiter sur un document, je les consulte pour voir si il n'y a pas des astuces que vous ayez déjà données sur le sujet ! En tout cas, j'adooooree..........toutes les rubriques !