Aller au contenu
Accueil » Votre propre planning automatique Excel

Votre propre planning automatique Excel

    Dans votre vie professionnelle, on vous a surement déjà demandé de réaliser un planning. Ou alors, on vous en a déjà forcément parlé. De plus, ne vous êtes jamais vous dit que ce serait bien que vous vous fassiez un planning ? Que ce soit pour une activité professionnelle ou personnelle ? Seulement, cela serait beaucoup plus simple que votre outil soit quelque peu automatique. Il existe bien des solutions payantes bien entendu. Nous allons voir ensemble comment utiliser la puissance des formules Excel et de VBA pour créer un planning automatique.

    Comment créer un planning automatique avec Excel et VBA

    Il est vrai qu’un planning, si il est suivi correctement, vous permet de maitriser le bon déroulement de votre projet afin d’atteindre son objectif. Cependant, il faut être régulier dans la mise à jour de celui-ci pour que cet outil vous serve complètement. Si vous avez un jalon de défini, vous pourrez observer les décalages engendrés par les différents retards et surtout comment arbitrer pour essayer de limiter l’impact sur la date finale.

    Nous allons voir au travers de cet article les différentes solutions qui s’offrent à vous et comment nous allons construire un outil de plus en plus automatisé. Je tiens à rappeler que le but n’est pas de créer un logiciel complet mais de voir comment nous pouvons utiliser les fonctionnalités Excel et VBA pour nous aider à générer des plannings de manière intuitive et optimisée.

    De plus, je n’ai pas tout créé tout seul. Je me suis largement inspiré de fichiers existants ou de ressources disponibles sur internet pour ensuite peaufiner le sujet à ma sauce. Ca ne sert à rien de réinventer la roue à chaque fois.

    Présentation d’un planning simple

    La manière la plus simple de créer un planning est d’utiliser Excel pour créer un tableau. Chaque ligne correspondra à une tâche et les colonnes seront le calendrier. Chaque colonne pourra correspondre à un jour de travail ou une semaine voire un mois selon vos besoins. Ensuite, suivant la durée, les colonnes correspondantes seront colorées pour visualiser la tâche dans le temps. C’est la manière qui est utilisée dans tous les outils de planning aujourd’hui.

    Votre planning ressemble à ça ?

    Si ce que vous faites ressemble à l’image précédente, c’est un très bon début. Néanmoins, cette technique à plusieurs défauts :

    • à chaque mise à jour de date, vous devez corriger manuellement
    • les dépendances ne sont gérées
    • si une date bouge et qu’il y a une dépendance sur celle-ci, la mise à jour n’est pas automatique
    • les trois points précédents font que les sources d’erreur sont multiples

    Comme évoqué dans l’introduction, il existe des outils sur le marché mais qui sont payants. Si vous avez un ordinateur au travail, vous aurez certainement accès à la suite Office et donc Excel. Il n’y pas besoin d’avoir une licence supplémentaire. Dans cet article, nous allons voir comment utiliser les formules d’Excel pour obtenir un planning automatique.

    Dans un premier temps, nous allons voir comment automatiser la visualisation du planning.

    Comment automatiser l’affichage des tâches dans un calendrier

    Calculer la date de fin

    Pour automatiser l’affichage du planning en fonction des dates rentrées, nous allons utiliser la fonctionnalité « Mise en forme conditionnelle » d’Excel. A cette fonctionnalité, nous allons la coupler avec des formules permettant d’avoir un outil flexible et évolutif.

    Voici ce que vous pouvez obtenir de manière totalement automatisée.

    Visualisation automatique d'un planning avec Excel
    La visualisation se fait automatiquement dans le calendrier

    Comme indiqué dans la légende de l’illustration précédente, l’utilisateur n’a plus qu’à renseigner la date de début ainsi que la durée en jours la tâche. Ensuite, par le biais de formule, les barres horizontales s’affichent automatiquement.

    Tout d’abord, pour calculer la date de fin, on utilise les fonctions « SERIE.JOUR.OUVRE » et « JOURSEM » à partir de la date de début et de la durée. Notez toutefois que l’on pourrait tout à fait paramétrer le fichier pour renseigner la date de début et la date de fin ; la durée se calculerait automatiquement. Si vous souhaitez avoir une liste des fonctions utiles sur Excel, voici un article à ce sujet.

    Voici la formule utilisée :

    =SERIE.JOUR.OUVRE(SI(JOURSEM(J10;1)=7;J10+2;SI(JOURSEM(J10;1)=1;J10+1;J10));K10-1;Jours_Feries)

    La fonction SERIE.JOUR.OUVRE permet de calculer une date de fin à partir de la date de départ et un nombre de jours en tenant compte des jours ouvrés (du lundi au vendredi inclus).

    Le premier argument est la date de départ. Une fonction SI est utilisée pour vérifier si celle-ci tombe un samedi ou dimanche auquel cas, on rajoute 1 ou 2 jours suivant la configuration. Le deuxième argument est le nombre de jours. Celui-ci est récupéré directement dans la cellule du tableur, à savoir K10 dans la formule. Enfin, la fonction SERIE.JOUR.OUVRE donne la possibilité de fournir une liste de jours considérés comme fériés. Pour se faire, il faut renseigner une plage de cellule. Danos notre exemple c’est une plage qui a été renommée Jours_Feries.

    La fonction JOURSEM permet de renvoyer une valeur en fonction de la date. Le premier argument est la date de départ. Le second argument peut être omis. La valeur 1 est la valeur par défaut. Elle pilote le comportement de la fonction. Ainsi paramétrée, elle renverra 7 pour un samedi et 1 pour un dimanche. Vous pouvez parfaitement changer ce paramètre, pour cela je vous invite à lire la documentation sur cette fonction dans l’aide en ligne dont voici le lien.

    Vous pouvez réutiliser la formule telle qu’elle est écrite, en modifiant les cellules bien sûr 🙂

    Insérer une mise en forme conditionnelle

    Nous savons maintenant calculer la date de fin de notre tâche en fonction de la date de départ et sa durée tout en prenant en compte les jours ouvrés ainsi que les jours fériés. Nous allons maintenant appliquer une mise en forme conditionnelle sur les cellules de notre calendrier. Ainsi, la couleur de fond des cellules changera en fonction de la date associée à la colonne de la cellule et les dates de début et fin de la tâche. Nous devons donc appliquer les conditions suivantes :

    • Si la date de la cellule concernée est supérieure ou égale à la date de début, on applique la couleur
    • Si la date de la cellule concernée est inférieure ou égale à la date de fin, on applique la couleur
    • La cellule de la date de début ne doit pas être vide

    Ce dernier critère est un cas spécial à gérer pour ne pas colorier la ligne entière. Voyons maintenant comment appliquer une mise en forme conditionnelle dans Excel :

    Insertion d'une mise en forme conditionnelle pour mettre à jour son planning de manière automatique

    Le menu de la mise en forme conditionnelle offre des préréglages qui sont souvent utilisés. Cela permet de gagner du temps dans la définition de la condition de mise en forme. Dans notre cas nous allons définir une nouvelle règle en utilisant une formule, nous devons donc cliquer sur « nouvelle règle ». Une fenêtre va s’ouvrir avec plusieurs choix possibles, c’est le dernier qui nous intéresse. Ainsi, nous devrons rentrer la formule qui répond aux critères cités précédemment et choisir la format à appliquer dans la cellule. Nous mettrons un remplissage simple.

    Application de la formule Excel pour piloter la mise en forme automatique du planning

    Pour appliquer le format, le détail n’est pas présenté ici mais la manipulation est très simple. Dans l’onglet « remplissage », il suffit de sélectionner la couleur désirée.

    Concernant la formule, vous observerez l’utilisation du caractère « $ ». Il permet de figer une cellule lorsqu’on glisse la formule. Un « $ » devant la lettre fige la colonne et un « $ » devant le nombre fige la ligne. Dans notre fichier Excel, la date correspondant à la colonne est stockée dans la cellule Q7. La ligne est donc figée pour que toute la colonne et la mise en forme conditionne fasse référence à la même cellule. La colonne n’est pas figée car la date change à chaque colonne.

    Le même raisonnement est appliqué pour les dates de début et de fin des tâches. Ces paramètres ne changent pas quand on change de colonne (la tâche est sur toute la ligne). Par contre, quand on descend d’une ligne, c’est une nouvelle tâche donc les dates changent. Il faut donc figer la colonne et pas la ligne d’où le « $ » devant la lettre.

    Des idées à rajouter pour optimiser la visualisation de votre planning automatiquement

    Vous savez maintenant comment appliquer une mise en forme conditionnelle en fonction de la date de début et fin. Il est tout à fait possible de rajouter des conditions qui vont se superposer. Ainsi vous aurez plusieurs couches de mise en forme qui vont venir vous donner des indications supplémentaires sur votre planning. Voici quelques idées.

    Afficher l’avancement de la tâche

    Dans votre tableau avec vos informations, vous pouvez rajouter une colonne où vous indiquez l’état d’avancement de votre tâche. Cela peut être soit un pourcentage soit le nombre d’heures ou de journées passées sur la tâche. Ainsi, avec ce pourcentage d’avancement vous pouvez appliquer une mise en forme conditionnelle pour changer la couleur si la date de la colonne dépasse X% de la durée de celle-ci.

    Afficher le jour actuel

    Le fait d’afficher le jour en cours permet d’avoir un repère visuel pour détecter les tâches qui auraient dû être terminées. Cela peut orienter vos priorités. Pour réaliser la mise en formation conditionnelle, la fonction « AUJOURDHUI() » qui permettra de comparer le jour de la colonne du calendrier à la date d’aujourd’hui. Si les deux valeurs correspondent, la mise en forme sera alors appliquée.

    Affichage du jour courant sur le planning automatique avec Excel

    Afficher la date prévue ainsi que la date réelle

    Pour aller plus loin, votre planning peut vous comparer la date initialement prévue et la date réelle, revue au fur et à mesure du projet. Il faudra donc rajouter une colonne avec cette information. Ensuite, à vous de rajouter une mise en forme conditionnelle avec la formule qui utilise cette nouvelle information.

    Il est possible d’aller très loin dans la personnalisation de votre planning. Cependant j’attire votre attention sur le fait de rester simple. Trop d’informations tue l’information. Il faut que votre planning reste lisible et utilisable.

    Un curseur pour piloter l’affichage du calendrier

    Pour rendre la navigation dans le calendrier plus convivial, l’utilisateur d’un curseur via une barre de défilement permet de piloter les semaines du calendrier affichées. Le curseur va en réalité piloter une valeur dans une cellule. Cette valeur sera utilisée pour calculer les dates correspondantes.

    La première chose à faire est de définir le nombre de semaine à afficher. 6 ou 8 semaines semblent être un bon ordre de grandeur.

    La seconde étape consiste à insérer une barre de défilement. Pour réaliser cette action il vous faut au préalable activer l’onglet « Développeur » pour avoir accès aux outils. Pour l’activer, aller dans « Fichier » puis « Options » et suivre les étapes décrites dans l’image ci-dessous.

    Activer l'onglet développeur dans Excel

    Maintenant que l’onglet développeur est actif, il faut créer une barre de défilement.

    Insérer une barre de défilement dans Excel

    Cliquer dans la feuille pour insérer la barre de défilement. Vous pourrez ensuite la redimensionner pour la positionner parfaitement au dessus de votre calendrier. Il reste maintenant l’étape de la configuration de votre barre. Faites un clic droit sur la barre et sélectionner « Format de contrôle ».

    Format de contrôle de la barre de défilement
    Contrôle de la barre de défilement du planning automatique dans Excel

    Les paramètres à rentrer sont les suivantes :

    • La valeur minimale : dans notre cas ce sera 1 pour la première semaine
    • la valeur maximale : vous pouvez vous limiter à un an donc 52 ou laisser 100
    • changement de pas : on laisse 1 car on veut incrémenter de semaine en semaine
    • changement de page : on laisse la valeur par défaut, soit 10
    • Cellule liée : c’est ici que vous allez renseigner la cellule pilotée par cette barre de défilement. Dans mon cas c’est la cellule Q5

    La cellule liée changera de valeur à chaque fois que vous ferez défiler la barre. En changeant de valeur, cette cellule influencera les formules de calcul des dates dans le calendrier. L’étape suivante consiste donc à construire la formule permettant de calculer le premier jour de votre calendrier. Tous les jours suivants seront obtenus en faisant un +1.

    Voici la formule utilisée pour calculer le premier jour avec une illustration pour mieux comprendre :

    Calcul du premier jour du calendrier à afficher

    =E2-JOURSEM(E2;3)+7*(Q5-1)

    La cellule E2 correspond au premier jour de votre planning. Pour faire simple, j’aime bien faire mon planning par année civile mais comme un projet ne commence par forcément un 1er janvier, il y a la possibilité de décaler ce début.

    La première partie de la formule permet de recaler le début du calcul au lundi. En effet, le 1er janvier n’est pas toujours un lundi. On prend donc la date de départ sur laquelle on retrouve le jour de la semaine avec la fonction JOURSEM. L’option 3 de la fonction JOURSEM renvoie un chiffre entre 0 et 6, 0 correspondant au lundi et 6 au dimanche. Voici trois exemples pour mieux comprendre :

    • Le premier jour du planning est un lundi => la fonction JOURSEM renvoie donc 0. Il n’y a donc pas de décalage
    • Le premier jour tombe un mercredi. La fonction JOURSEM renvoie donc 2. La formule retranche alors deux jours à la date de départ ce qui nous fait tomber un lundi (mercredi – 2 = lundi)
    • le premier jour tombe un dimanche. La fonction JOURSEM renvoie donc 6. Dimanche – 6 = Lundi

    Le début de la formule recale sur le lundi de la première semaine du projet. La partie restante permet de rajouter le nombre de semaine de décalage pour afficher le calendrier mobile. On trouve donc dans la formule 7*(Q5-1). Le terme Q5-1 correspond au nombre de semaine pour le décalage. En effet, si on souhaite afficher la première semaine, Q5 vaut 1, donc Q5-1 vaut 0, il n’y a donc pas de décalage. Si la première semaine à afficher est la troisième, il faudra décaler de deux semaines (Q5 vaut 3, 3-1 = 2 donc 2 semaines de décalage).

    Ensuite, le facteur 7 vient bien entendu du fait qu’il y ait 7 jours dans une semaine. Voilà donc la formule mise en place. La cellule Q5 sera donc notre point de départ. Pour toutes les semaines adjacentes, nous ferons +1 pour avoir le jour suivant.

    Un dernier point concerne le format de la cellule. Nous avons besoin que cette cellule soit une date pour nos calculs mais nous souhaitons afficher uniquement le numéro du jour. On modifie donc le format d’affichage.

    Appliquer un format personnalisé dans Excel
    Voici la démarche pour appliquer un format personnalisé

    Voici un aperçu de ce que vous pourriez avoir pour les semaines 1 et 3.

    Aperçu d'un planning automatique avec Excel
    Aperçu du calendrier pour la semaine 1
    Mise à jour automatique de la visualisation de votre planning avec seulement des formules Excel
    Aperçu du calendrier pour la semaine 3

    Vous pouvez soit déplacer le curseur pour atteindre la semaine 3, soit taper 3 dans la cellule concernée, à savoir Q5 dans notre cas. Ainsi, le planning Excel va se mettre à jour automatiquement. Les dates seront recalculées automatiquement et par suite, l’affichage des tâches également.

    Rajouter de l’automatisation avec VBA

    Le planning ainsi créé permet de générer une visualisation automatique en utilisant les formules Excel et la mise en forme conditionnelle. Dans un prochain article, nous aborderons comment automatiser encore plus en utilisant du code VBA. Les deux points suivants seront abordés :

    1. Générer un planning macro

    Pour définir les budgets et les jalons d’un projet, on utilise souvent un planning macroscopique. Cependant, de mon point de vue, pour créer ce planning macroscopique, il est bien nécessaire de lister toutes les tâches et actions à réaliser pour calculer le temps requis sur chaque étape du projet. Dit autrement, il faudra donc réaliser le planning détaillé afin de créer le planning macroscopique. Les deux sont alors liés. L’idée est alors de ne faire que le planning détaillé et de générer automatiquement le planning grossier à partir du premier. Nous passerons par du code VBA pour réaliser cette tâche.

    Gérer les dépendances entre les tâches

    Ce point est particulièrement intéressant, de mon point de vue. En effet, dans un planning, les tâches se succèdent, s’enchaînent, se superposent. Certaines peuvent être faites en parallèles mais d’autres sont dépendantes d’une précédente.

    Avec un fichier simple comme nous venons de faire, vous pouvez gérer cela en insérant des formules au lieu de renseigner les dates. Par exemple, la date de début de la tâche 2 sera égale à la date de fin de la tâche 1 + 1 jour. En reprenant les illustrations précédentes, on aurait dans la cellule J11 la formule suivante :

    « =L10+1 »

    Cependant, en faisant cela, vous risquez de faire des erreurs de liens dans le cas où vous rajouteriez des lignes par exemple. L’idée est de rajouter deux colonnes. La première servira à identifier les tâches par un identifiant unique. La seconde sera pour indiquer si la tâche possède un ou plusieurs prédécesseurs. Si elle n’en a pas, la case reste vide. Si la tâche doit attendre la fin d’autre, on pourra l’indiquer en remplissant son numéro d’identification dans cette colonne. Le fichier Excel détectera automatiquement la tâche en amont et viendra mettre à jour automatiquement la date de début.

    A vous de jouer !

    De mon point de vue, un planning doit vous apporter les bénéfices suivants :

    • Anticiper les dérives
    • Prioriser les tâches à réaliser
    • Vous alerter si une tâche dépasse la date limite ou le budget
    • Vous donner une visualisation macroscopique de votre projet qui soit partageable avec votre équipe

    Dans des projets d’automatisation comme la création d’un planning automatique avec Excel, il est important de décomposer votre travail en plusieurs tâches pour atteindre l’objectif. Si une des tâches est encore conséquente, il ne faut pas hésiter à découper à nouveau en plusieurs petites tâches. L’idée est de découper le travail en tâches qui ne semblent pas insurmontable.

    Une fois cette décomposition réalisée, il faut maintenant commencer à réaliser chaque étape. Grâce à cette méthode, vous verrez les progrès que vous faites en réalisant des petites tâches les unes après les autres. Surtout, vous vous rendrez compte que vous vous approchez petit à petit de votre objectif, qui pouvait sembler inatteignable au début.

    Il est rare d’utiliser un logiciel à 100% de ses capacités. On trouve dans différents articles qu’un utilisateur n’utilise que réellement 10% du potentiel d’un logiciel. Sans vouloir révolutionner le monde informatique ou avoir la prétention de vouloir recréer un logiciel complet, ‘implémenter soi-même les fonctionnalités qui nous intéresse permet de se focaliser sur les fonctionnalités importantes. De plus, cela évite également de devoir payer un logiciel qui sera sous-utilisé.

    Cet article vous a présenté les quelques fonctionnalités qui peuvent être implémentées afin de transformer votre tableau Excel en un outil de planning flexible, facile d’utilisation et surtout automatique. Si ce projet vous parle, que vous souhaitez plus de détail sur un point particulier, n’hésitez pas à laisser un commentaire, j’y répondrai avec plaisir.

    Merci de m’avoir lu,
    A bientôt,
    Benjamin

    Si vous avez aimé l'article, vous êtes libres de le partager ! :)

    Laisser un commentaire