Construire un calendrier d’activités dans Excel avec la mise en forme conditionnelle

Publié le 21 septembre 2020
par Sophie Marchand M.Sc., CPA, CGA, MVP
Calendrier avec mise en forme conditionnelle dans Excel

Construire un calendrier d’activités dans Excel avec la mise en forme conditionnelle

Si vous devez effectuer de la gestion de projets, nul doute que vous avez déjà travaillé avec les graphiques de Gantt. Ces graphiques permettent de visualiser rapidement la cédule de déploiement de certaines activités. On peut ensuite venir moduler le tout en filtrant la visualisation par individu, par département, par priorité ou tout autre filtre pertinent. Dans le présent article, je souhaite partager avec vous une technique plutôt simple dans Excel, pour créer des calendriers d’activités, inspirés de graphiques de Gantt, à partir de règles de mise en forme conditionnelle.

 

Construire un calendrier avec la mise en forme conditionnelle d’Excel

Dans les prochaines sections de cet article, nous allons construire le calendrier suivant dans Excel. En gris pâle, on aperçoit les week-ends et en vert, les journées associées à des tâches dont les dates de début et de fin sont inscrites dans les colonnes B et C. À noter que si l’on fournit de nouvelles valeurs dans ces cellules, le calendrier se remettra à jour automatiquement.

Construire un calendrier d'activités dans Excel sous forme de graphique de Gantt

 

Bien préparer son fichier Excel

Il y aurait de multiples façons de construire un calendrier de tâches dans Excel à partir de la mise en forme conditionnelle mais nous allons ici nous concentrer sur un seul scénario, que vous pourrez évidemment adapter à votre réalité, par la suite. La première étape est donc de bien préparer le fichier pour l’insertion des dates de début et de fin de chacune des tâches, mais aussi pour les dates du calendrier lui-même. Ici, pour simplifier au maximum nos règles de mise en forme conditionnelle, nous avons pris soin d’insérer une ligne ANNÉE et une ligne MOIS. Remarquez que la dernière colonne visible est en fait le jour 1 du mois d’octobre. C’est pour vous montrer que nos règles s’appliquent au-delà d’un mois. Nous pourrons ensuite grouper les lignes 6 et 7 pour ne pas alourdir le calendrier inutilement et améliorer le visuel.

Calendrier mise en forme conditionnelle

 

Bien comprendre la fonction Excel JOURSEM

Pour simplifier au maximum les formules utilisées dans nos règles de mise en forme conditionnelle, nous allons exploiter la fonction JOURSEM à notre avantage. De base, la fonction JOURSEM rapporte la journée de la semaine associée à une date. Par exemple, la fonction =JOURSEM(B17), dans l’image ci-dessous, retourne 2 et signifie que le 21 septembre 2020 est un lundi. La semaine dite de base dans Excel démarre avec un dimanche (1) et se termine avec un samedi (7).

Fonction Excel JourSem

 

Par contre, il est possible de spécifier un type de retour dans la fonction JOURSEM. Ainsi, le type 2 fera plutôt démarrer la semaine un lundi (1), ce qui fait en sorte qu’un samedi se verra attribuer le chiffre 6 et un dimanche le chiffre 7. Par exemple, la fonction =JOURSEM(B18;2) retourne la valeur 1. Finalement, il est utile de savoir qu’on peut aussi appliquer la fonction JOURSEM sur un nombre. Ainsi, la fonction =JOURSEM(C17;2) retourne aussi la valeur 1.

 

Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel.

 

Insérer des règles de mise en forme conditionnelles pour définir un calendrier

Pour créer notre calendrier de tâches dynamiques, nous utiliserons 2 règles de mise en forme conditionnelle.

 

Définir les journées de week-ends

Pour définir les journées de week-ends, nous avons utilisé la formule suivante :

=JOURSEM(DATE(D$6,D$7,D$9),2)>5

Celle-ci recrée d’abord une date à partir de l’année de la ligne 6, du mois de la ligne 7 et du jour de la ligne 9. Elle vérifie ensuite, selon le mode de retour 2 de la fonction JOURSEM expliqué précédemment, si le jour est supérieur à 5 ou si vous préférez, si le numéro du jour est égal à 6 ou 7, soit samedi ou dimanche. Si c’est le cas, on demande d’afficher la cellule en gris pâle.

 

Définir les journées d’activités

Pour définir les journées d’activités, nous avons utilisé la formule suivante :

=ET($B10<=DATE(D$6,D$7,D$9),$C10>=DATE(D$6,D$7,D$9))

On reconstitue à nouveau la date de la même façon et on s’assure ensuite de la comparer aux dates des colonnes B et C, i.e. les dates de début et de fin de l’activité. Si la date de début est inférieure ou égale à la date de la case du calendrier et si la date de fin est supérieure ou égale à la date de la case du calendrier, Excel affiche la cellule en vert. À noter que la condition ET est utilisée pour s’assurer que les deux conditions soient rencontrées.

 

Pour développer vos compétences avec les graphiques et la mise en forme conditionnelle dans Excel, nous vous suggérons fortement de jeter un oeil à nos formations portant sur les Tableaux de bord dans Excel.

 

Bien fixer l’ordre des conditions de mise en forme conditionnelle

Pour que tout fonctionne proprement, il faut s’assurer d’appliquer d’abord une mise en forme conditionnelle sur les journées de week-ends et ensuite une règle sur les journées de tâches. Pour cela, il faut s’assurer que la règle des week-ends soit placée en premier dans le gestionnaire des règles de mise en forme conditionnelle. Si ce n’est pas le cas, il faudra utiliser les flèches dans le coin supérieur droit, pour corriger l’ordre. Autrement, des journées de week-ends pourraient ressortir en vert.

Ordre des règles de mise en forme conditionnelle dans Excel

 

Définir un calendrier de tâches dans Power BI avec la mise en forme conditionnelle

Si vous vous demandez s’il est possible de construire le même genre de calendrier dans Power BI alors oui, c’est tout à fait possible mais avec une technique un peu différente. Pour savoir comment procéder, je vous invite à consulter l’article suivant : Power BI: Convertir un visuel de matrice en calendrier utile.

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Afin d’approfondir vos connaissances avec Excel, dont celles touchant la mise en forme conditionnelle, nous vous recommandons notre formation Excel – Tableaux de bord (niveau 2).

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Jean-François Dostie
Écrit il y a 1 an
excellent

ça rend évident que plusieurs PME sont tout simplement non-performante par rapport à la gestion de leurs données

Bernard Faucher
Écrit il y a 2 ans
Hâte de mettre en application!

Je suis bien content d'avoir suivi cette formation et J'ai hâte de retourner au travail demain pour commencer à la mettre en application 🙂

Louise Simard
Écrit il y a 2 ans
Très intéressée par la suite des formations

Agréablement surprise de la quantité d'information apprise et applicable dans mon quotidien.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top