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.
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.
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).
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.
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.
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 :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
