Créer des calendriers et plannings automatiques avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Planning et calendrier automatiques avec Excel

Excel gère parfaitement les dates et leurs subtilités. Le tableur connaît les nombres de jours constituant les mois de l'année, y compris pour les années bissextiles. Il sait précisément le jour de la semaine correspondant à une date spécifiée, qu'elle appartienne au présent, à un passé lointain ou à un futur éloigné. C'est pourquoi nous proposons de mettre en pratique les connaissances acquises au travers de différentes formations pour automatiser la création de calendriers et plannings avec Excel.



Nous choisissons de résoudre un cas pratique précis pour structurer le concept et le raisonnement. Mais les méthodes pourront facilement s'adapter à tout type de besoin d'entreprise. Nous développerons cette application sans recourir au code VBA Excel.

Sources Excel et présentation du concept
Une entreprise doit gérer le planning de chacun de ses salariés pour chaque mois de l'année. Sur une période horaire s'étalant de 6h30 à 22h30, trois affectations sont possibles. Il y a les postes du matin, ceux de la journée et ceux du soir. Chaque mois ne propose pas le même nombre de jours. Pour chaque mois, les jours ouvrés changent. Ainsi l'application propose à l'administrateur de définir l'année et le mois dans un premier temps, à l'aide de listes déroulantes. Le calendrier se dessine automatiquement avec la représentation des jours de la semaine, des heures de travail, de la mise en valeur des Week-End, dans la limite des jours autorisés par le mois en cours.

L'utilisateur sélectionne alors l'un des salariés, toujours par le biais d'une liste déroulante et le planning devient nominatif. Enfin, le dernier choix se réalise sur l'affectation du poste, et la mise en valeur conditionnelle des plages horaires finit de construire le planning. Des boutons permettent néanmoins de réaliser des affectations spécifiques pour adapter certains horaires aux besoins. Mais comme la structure globale s'est construite automatiquement, le gain de temps est considérable. Il ne reste plus qu'à l'imprimer, d'autant qu'une mise en page préalable soignée a permis de parfaitement ajuster le planning, pour un rendu efficace.

• Télécharger le classeur creer-planning.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification,

Le classeur propose deux feuilles. La feuille Calendrier offre la structure de base pour le développement de la construction automatisée du planning. La feuille Sources quant à elle, propose toutes les petites tables de données pour la fabrication des listes déroulantes. C'est du choix réalisé dans ces listes que vont dépendre tous les calculs et le format dynamique conduisant à la création automatique du calendrier. Dans la feuille Calendrier, quatre listes déroulantes doivent être construites en C2, C3, C4 et C5, respectivement pour l'année, le mois, le salarié et l'affectation horaire.



Automatiser le choix des paramètres
Nous allons commencer par construire les listes déroulantes permettant de simplifier la sélection des éléments clés du planning. Et pour cela, nous allons reproduire les techniques enseignées par le support sur la création des listes dans Excel. Nous allons procéder méthodiquement en créant dans l'ordre une liste pour le choix des années, une autre pour les mois, une suivante pour les salariés et une dernière pour les affectations.

• Activer la feuille Calendrier, en cliquant sur son onglet en bas de la fenêtre Excel,
• Sélectionner la cellule des années, soit la cellule C2,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Outils de données, cliquer sur le bouton Validation de données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue qui suit, choisir Liste,
• Puis, cliquer dans la zone Source située juste en dessous pour l'activer,
• Cliquer alors sur l'onglet de la feuille Sources en bas de la fenêtre Excel,
• Sélectionner les cellules des années sans l'entête, soit la plage F3:F14,
• Valider par ok pour confirmer la création de la liste sur la base de ces valeurs,

La liste de choix apparaît instantanément dans la cellule C2 de la feuille Calendrier. Elle propose toutes les années répertoriées dans la source de données ainsi désignée. Nous devons réitérer ce processus pour créer les trois autres listes déroulantes.

• Sélectionner la cellule C3 de la feuille Calendrier pour construire la liste des mois,
• Cliquer sur le bouton Validation de données du ruban Données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue qui suit, choisir Liste,
• Puis, cliquer dans la zone Source située juste en dessous pour l'activer,
• Cliquer alors sur l'onglet de la feuille Sources en bas de la fenêtre Excel,
• Sélectionner la liste des mois, soit la plage de cellules C3:C14 et valider par Ok,
• Sélectionner la cellule C4 de la feuille Calendrier pour construire la liste des salariés,
• Cliquer sur le bouton Validation de données du ruban Données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue qui suit, choisir Liste,
• Puis, cliquer dans la zone Source située juste en dessous pour l'activer,
• Cliquer alors sur l'onglet de la feuille Sources en bas de la fenêtre Excel,
• Sélectionner la liste des salariés, soit la plage de cellules H3:H27 et valider par Ok,
• Sélectionner la cellule C5 de la feuille Calendrier pour construire la liste des affectations,
• Cliquer sur le bouton Validation de données du ruban Données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue qui suit, choisir Liste,
• Puis, cliquer dans la zone Source située juste en dessous pour l'activer,
• Cliquer alors sur l'onglet de la feuille Sources en bas de la fenêtre Excel,
• Sélectionner la liste des tranches, soit la plage de cellules L3:L5 et valider par Ok,

Comme l'illustre la capture ci-dessus, nous obtenons les quatre listes déroulantes. Elles permettent ainsi de formuler les quatre critères en cascades, nécessaires à la construction du planning automatisé par briques d'indices.

Reconstruire les dates des jours selon le mois et l'année
La ligne 8 de la feuille Calendrier est prévue pour réaliser le calcul intermédiaire des dates, du premier au dernier jour du mois, selon les éléments fournis par les deux premières listes déroulantes. Ce calcul intermédiaire sera exploité dans un deuxième temps, pour créer la ligne d'entête du planning, celle des jours de la semaine. A l'issue, la ligne 8 pourra être masquée pour un résultat épuré.

La fonction Excel Date permet de reconstruire une date complète selon trois paramètres. Il s'agit dans l'ordre de l'année, du mois et du jour. Les deux premières listes fournissent les deux premiers éléments. Concernant le dernier, la ligne 7 énumère tous les potentiels jours d'un mois de l'année, du 1^er au 31^ème. Dans certains cas bien sûr, il s'agira d'être en mesure de tronquer la restitution à 30 voire à 29 ou 28 pour les mois de Février. Les paramètres à fournir doivent nécessairement être numériques. Or nous disposons de l'information du mois en texte, par le biais de la liste déroulante. Pour pallier la contrainte, nous proposons de réaliser une recherche du mois dans la table de la feuille Sources afin d'extraire son numéro correspondant. La fonction RechercheV est dédiée à la recherche et à l'extraction de données. Et cette fonction doit être imbriquée en deuxième paramètre de la fonction Excel Date.

• Sélectionner la cellule D8 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit date(,
• Sélectionner la cellule C2 pour fournir le renseignement sur l'année,
• Enfoncer la touche F4 du clavier pour la figer,

En effet, nous allons reproduire ce calcul sur la droite. Et bien que nous changions de colonne, la formule doit toujours faire référence à cette cellule située au même emplacement. Pour la figer dans le calcul, nous devons enfoncer la touche F4 du clavier comme l'enseigne la formation pour reproduire un calcul avec une seule formule.

Le paramètre suivant concerne l'information numérique à transmettre pour le mois. C'est donc ici qu'intervient la fonction RechercheV.

• Taper un point-virgule (;) pour passer au paramètre suivant,
• Saisir le nom de la fonction de recherche suivi d'une parenthèse, soit RechercheV(,
• Cliquer la cellule C3 pour transmettre l'élément à rechercher,
• Enfoncer la touche F4 pour la figer,
• Taper un point-virgule (;) pour passer à l'argument du tableau de recherche,
• Cliquer sur l'onglet Sources en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner tout le tableau des mois avec leur numéro, soit la plage de cellules C2:D14,
• Enfoncer la touche F4 du clavier de manière à figer cette plage,
• Taper un point-virgule pour passer à l'argument du numéro de colonne à retourner,
• Saisir le chiffre 2 car le numéro du mois se situe dans la deuxième colonne de la sélection,
• Taper un point-virgule et saisir le texte Faux afin de demander une correspondance exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Taper un point-virgule pour passer à l'argument des jours pour la fonction Date,
• Cliquer sur l'onglet Calendrier en bas de la fenêtre Excel pour revenir sur sa feuille,
• Sélectionner le premier numéro de l'incrémentation, soit la cellule D7,
• Fermer la parenthèse de la fonction Date,
• Valider la formule par le raccourci clavier CTRL + Entrée afin de garder la cellule active,

Cette astuce comme tant d'autres est enseignée par la formation sur les raccourcis claviers Excel. La validation par la touche Entrée aurait activé la cellule du dessous nous obligeant à remonter dans celle du résultat pour exploiter son calcul. Vous remarquez que nous n'avons pas figé la dernière référence, soit la cellule D7. En effet, cette fois nous devons la laisser se déplacer en même temps que le calcul. Le jour doit varier en même temps que la formule change de colonne.

• Tirer la poignée du calcul jusqu'à la cellule AH8 pour le répliquer,
• Si nécessaire, ajuster la hauteur de la ligne 8 pour visualiser l'intégralité des dates,
• A l'aide de la liste déroulante en C2, sélectionner l'année 2018,
• A l'aide de la liste déroulante en C3, sélectionner le mois de Février,

Comme vous avez pu le remarquer, dans les deux cas, la suite logique des dates s'est mise à jour en ligne 8. Dans le premier cas, bien que le mois comporte encore 31 jours, les jours de la semaine changent et s'actualisent donc. Dans le second cas, le mois de Février est plus court et les jours de la semaine s'actualisent aussi.



Un souci qui ne vous a pas échappé, intervient en bout de ligne 8, après le vingt huitième jour du mois de Février. Nous aurions souhaité générer une erreur plus simple à exploiter pour définir la borne supérieure du mois. Mais la fonction Date interprète et s'adapte. Malgré les informations erronées qui lui sont passées en bout de piste, elle reprend la suite logique de l'énumération à partir du mois suivant.

Ligne d'entête du planning - Les jours de semaine
Pour pallier le problème que nous venons d'identifier afin de tronquer l'énumération lorsque la fin du mois est atteinte, ce sont les calculs Excel qui vont offrir la solution. Il s'agira de vérifier que le mois de la date proposée en ligne 8 est bien le même que celui de la toute première cellule de la même ligne. Le calcul pour la création de cette ligne d'entête ne doit se déclencher que si deux conditions sont vérifiées ensemble. L'année et le mois doivent être définis. En d'autres termes, les cellules C2 et C3 ne doivent pas être vides. Pour recouper ces conditions dans la zone de critère de la fonction Excel Si, nous devons donc exploiter la fonction ET permettant de les énumérer. Commençons par le calcul de référence, le seul qui ne sera pas à répliquer. Il s'agit d'extraire le jour de semaine de la date. La fonction Excel Texte permet de transformer une date en texte selon un format qui lui est passé en second paramètre.

• Sélectionner la cellule D9 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse ouvrante, soit Si(,
• Saisir le nom de la fonction pour recouper les critères et ouvrir la parenthèse, soit Et(,
• Taper le critère suivant en figeant la cellule : $C$2<>'' pour vérifier que l'année n'est pas vide,
• Taper un point-virgule (;) pour passer au critère suivant,
• Taper le critère vérifiant que le mois n'est pas vide, soit $C$3<>'', en figeant la cellule,
• Fermer la parenthèse de la fonction et taper un point-virgule pour passer dans la branche Alors de la fonction Si,
• Saisir le nom de la fonction permettant la conversion suivi d'une parenthèse, soit Texte(,
• Sélectionner la date à convertir, soit la cellule D8 et taper un point-virgule,
• Entre guillemets, saisir le format permettant de restituer le jour en lettres, soit 'jjjj',
• Fermer la parenthèse de la fonction Texte,
• Concaténer avec un espace entre guillemets, soit & '',
• Concaténer avec la fonction Texte pour extraire le numéro de la date, soit texte(D8;'jj'),
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets pour garder la cellule vide lorsque les conditions ne sont pas vérifiées,
• Fermer la parenthèse de la fonction Si et valider,

Ce qui donne :

=SI(ET($C$2<>''; $C$3<>''); TEXTE(D8;'jjjj') & ' ' & TEXTE(D8;'jj'); '')

Le jour de la semaine en texte et en chiffre apparaît dans la cellule. Si nous supprimons l'une des deux informations, le mois et/ou l'année, l'inscription disparaît comme demandé par le recoupement de critères, grâce à la fonction Excel ET.

En E9, il s'agit d'extraire le jour de la date en texte et en chiffre de la cellule du dessus, soit E8 cette fois. Bien sûr, la double condition précédente reste vraie. Mais en plus, nous devons vérifier que le mois de cette date (en E8) est identique à la première (En D8, à figer donc), afin de pouvoir reproduire le calcul jusqu'au potentiel 31 ème jour.

• En conséquence, en E9, taper la formule suivante :

=SI(ET($C$2<>''; $C$3<>''); SI(MOIS(E8)=MOIS($D$8); TEXTE(E8;'jjjj') & ' ' & TEXTE(E8;'jj');'');'')

Littéralement, nous exprimons le raisonnement suivant : Si l'année et le mois sont définis, alors si le mois de la date en cours est identique au mois de la première date de la ligne, nous affichons le jour de la semaine en texte et en chiffre, sinon nous n'affichons rien.

• Tirer la poignée de la cellule E9 jusqu'au dernier jour potentiel du mois, soit AH9,

Comme vous le remarquez sur votre feuille de calcul et comme l'illustre la capture ci-dessus, l'inscription des dates du mois sélectionné, s'adapte parfaitement au contexte. Dans notre cas, le mois de Février 2018 ne compte que 28 jours. Ainsi les trois dernières cases restent vides. Si nous sélectionnons le mois de Janvier, l'énumération se prolonge jusqu'au 31^ème jour. Si nous choisissons le mois d'Avril, elle se coupe au 30^ème. La construction automatisée de notre calendrier commence à prendre forme.

Inscription conditionnelle des heures du planning
La plage permise dans notre cas varie de 6H30 à 22H30, à saisir au format heure, soit 6:30 et 22:30. L'inscription des horaires peut se déclencher si celle des jours de la semaine est apparue. En basant ainsi le critère sur la précédente plage, nous simplifions les conditions.

• Sélectionner la première cellule pour l'inscription des horaires, soit C10,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse, soit Si(,
• Cliquer sur la cellule D9 pour la désigner et enfoncer la touche F4 pour la figer,
• Saisir alors le critère de comparaison suivant : <>'',

En langage Excel, le symbole inférieur suivi du symbole supérieur signifie Différent de. Dans l'expression, nous cherchons à vérifier que la cellule D9 n'est pas vide (''). Selon les conditions vérifiées précédemment, si elle n'est pas vide, cela signifie que le mois et l'année sont définis. Donc nous pouvons inscrire les plages horaires pour poursuivre la construction automatisée du planning.

• Taper alors un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Cliquer sur l'onglet Sources en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner le premier horaire du tableau des heures, soit la cellule J3,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour garder la cellule vide lorsque la condition n'est pas satisfaite,
• Fermer la parenthèse de la fonction et valider le calcul par CTRL + Entrée pour garder la cellule active,
• Tirer la poignée de la cellule jusqu'en C26 pour incrémenter la plage des horaires,

La formule que nous avons construite est la suivante :

=SI($D$9<>''; Sources!J3; '')



Nous obtenons bien la reproduction des horaires selon la logique de la plage issue de la feuille Sources. Et surtout vous remarquez que si l'année et/ou le mois n'est pas défini, les inscriptions des horaires disparaissent, tout comme pour la ligne de titre du tableau. La construction automatisée du planning continue donc de se mettre en place au fur et à mesure.

Rendre le planning nominatif
Il existe une cellule fusionnée sur la partie supérieure de la feuille. Elle est prévue pour accueillir le titre du planning désignant le salarié concerné. Comme toujours, cette inscription doit se déclencher automatiquement. Il faut vérifier que le mois et l'année (C3 et C2) ont bien été renseignés et en même temps que le nom du salarié (C4) est bien défini. C'est une fois de plus la fonction Excel Et qui va nous permettre d'énumérer les conditions à vérifier, dans la zone de critère de la fonction Si pour décider ou non, de l'inscription du titre.

• En cellule F2, saisir la formule suivante :

=SI(ET(C2<>''; C3<>''; C4<>''); 'Planning horaires de ' & C4 & ' pour le mois de ' & C3;'')

Si les trois critères énumérés par la fonction Et sont vérifiés, nous reconstruisons le titre par concaténation dans la branche Alors de la fonction Si, afin de l'associer à des données variables, comme le nom du salarié et le mois du planning. Dans le cas où le critère global n'est pas satisfait, comme souvent nous choisissons de ne rien inscrire grâce aux deux guillemets (''). Si l'une des trois informations (Mois, année ou salarié) n'est pas définie, le titre disparaît. Il s'agit donc bien, une fois encore, d'une construction automatique.

Il s'agit désormais de mettre en valeur ce titre. Mais cette mise en forme ne peut pas se faire manuellement car elle demeurerait même quand le titre disparaît, faute d'éléments suffisants pour le construire. Nous devons bâtir une mise en forme conditionnelle capable de formater cette cellule si et seulement si son contenu n'est pas vide. La formation sur les synoptiques Excel notamment, nous avait appris à faire réagir l'aspect des cellules selon des règles plus ou moins sophistiquées.

• Sélectionner le titre, soit la cellule F2,
• Cliquer sur la flèche du bouton Mise en forme conditionnelle dans le ruban Accueil,
• Dans la partie inférieure de la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le type Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie du dessous et sélectionner la cellule F2,
• Saisir alors l'expression suivante pour le critère : <>'',
• Puis cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la nouvelle boîte de dialogue qui suit, définir des attributs comme un fond orangé clair, une bordure de cellule et un texte gras et rouge foncé grâce aux onglets remplissage, Bordure et Police,

• Valider ces réglages une première fois par Ok puis une seconde pour la première boîte de dialogue,

De retour sur la feuille, le titre apparaît instantanément mis en valeur, selon les réglages que nous venons de définir. Le critère déclencheur (=$F$2<>'') consiste simplement à vérifier que le titre n'est pas vide. Mais si le contenu de l'un des paramètres vient à disparaître (En C2, C3 ou C4), le titre s'effaçant, la mise en valeur redevient invisible.

Mise en forme automatique du planning
Pour parfaire la construction dynamique de notre planning, nous devons travailler sa présentation. Mais une fois encore, il s'agit d'une présentation qui n'a lieu d'être que si le planning est construit. Nous pourrions parler de présentation conditionnelle. Nous proposons de débuter par les bordures qui consistent en un quadrillage fin sur l'ensemble des cellules, sauf pour la première à l'intersection, la cellule C9 qui doit être exclue. La condition permettant d'enclencher le dessin des bordures des horaires consiste simplement à vérifier que la cellule D9 n'est pas vide ($D$9<>''). Pour le reste du tableau en revanche, pour que la bordure existe sur la colonne, il faut que le jour correspondant en ligne 9 existe. Pour un mois de Février par exemple ne comptant que 28 jours, les trois dernières colonnes ne doivent pas être quadrillées. Donc le critère consiste à vérifier que la cellule correspondante de la ligne 9 n'est pas vide.

• Sélectionner tout d'abord les horaires, soit la plage de cellules de C10 à C26,
• Puis, cliquer sur la flèche du bouton Mise en forme conditionnelle et choisir Nouvelle Règle,
• Dans la boîte de dialogue qui suit, choisir le type Utiliser une formule comme précédemment,
• Cliquer dans la zone de saisie juste en dessous et cliquer la cellule D9 sur la feuille,
• A la suite, saisir le critère vérifiant que la cellule n'est pas vide, soit <>'',
• Cliquer alors le bouton Format et activer l'onglet Bordure de la boîte de dialogue qui suit,
• Choisir un style de trait plein et cliquer sur le bouton Contour pour l'appliquer,
• Valider ces réglages par Ok et cliquer de nouveau sur Ok pour confirmer le format conditionnel,

Le quadrillage apparaît sur la plage des heures de la journée. Si l'une des conditions consistant à construire le planning n'est plus vérifiée (Mois et/ou Année), comme les jours de la semaine n'apparaissent plus, le quadrillage ne se dessine pas. Les critères de la mise en forme se déclenchent en cascade. Soucions-nous maintenant du quadrillage du reste du planning dont l'étendue dépend du nombre de jours que compte le mois.

• Sélectionner la plage de cellules D9:AH26,
• Cliquer sur la flèche du bouton Mise en forme conditionnelle et choisir Nouvelle Règle,
• Dans la liste des types proposés, choisir Utiliser une formule...,
• Cliquer la première cellule pour vérifier que le jour de la semaine n'est pas vide, soit D9,
• Enfoncer la touche F4 de manière à ne conserver le dollar que devant l'indice de ligne,
• Saisir le critère vérifiant que la cellule n'est pas vide, soit <>'',
• Puis, cliquer sur le bouton Format et définir les mêmes bordures que précédemment,
• Valider ensuite ces réglages pour constater les effets sur la feuille,

Comme vous le remarquez, le quadrillage prend effet sur l'ensemble du planning. Il s'agit toujours de bordures conditionnelles dans la philosophie de sa construction automatisée. Dans le critère que nous avons écrit et rappelé par la capture ci-dessous (=D$9<>''), la cellule est figée en ligne mais pas en colonne. Excel va vérifier la présence du jour correspondant sur chaque colonne. Si le mois ne compte que 28 jours par exemple, les cellules suivantes étant vides, le critère impose de ne pas réaliser de bordures sur les colonnes concernées. Et comme ces jours ne peuvent être définis que si le mois et l'année ont été spécifiés, ce sont encore une fois des conditions enclenchées en cascade qui permettent peu à peu, de construire le planning automatiquement.



Mise en évidence des Week End
Les Samedi et Dimanche étant vaqués pour cette entreprise, nous souhaitons qu'ils ressortent automatiquement hachurés. Les conditions pour un tel format dynamique sont multiples. Non seulement, la cellule D9 ne doit pas être vide, indiquant que le planning peut se construire, mais en même temps, le jour de semaine de la colonne doit être soit un Samedi, soit un Dimanche. La fonction Joursem appliquée sur une date, avec le chiffre 2 en second paramètre, retourne 6 pour un Samedi et 7 pour un Dimanche. En résumé si la cellule D9 n'est pas vide et que le jour de semaine de la date active dans la colonne vaut 6 ou 7, alors nous pouvons hachurer l'intégralité des cellules de la colonne.

• Sélectionner la plage de cellules D10:AH26,
• Cliquer sur la flèche du bouton Mise en forme conditionnelle et choisir Nouvelle règle,
• Dans la liste des types, choisir Utiliser une formule...,
• Puis, cliquer dans la zone de saisie située juste en-dessous,
• Saisir le critère suivant :

=ET(D$9<>''; OU(JOURSEM(D$8;2)=6; JOURSEM(D$8;2)=7))

Nous imbriquons une fonction Ou dans une fonction Et. La première condition qui doit être vérifiée pour justifier la création du planning est la présence d'une date en ligne 9. Nous la libérons en colonne pour que chaque comparaison puisse se faire avec le jour en cours, correspondant à la colonne analysée. Et en même temps, le jour de la semaine doit être soit un Samedi, soit un Dimanche. C'est la raison pour laquelle nous poursuivons l'énumération avec la fonction Ou. Nous passons la cellule D8 figée seulement en ligne à la fonction Joursem. Ainsi, la condition sera vérifiée pour chaque colonne et donc pour chaque jour de la semaine. Si la valeur retournée vaut 6 ou 7, nous savons qu'il s'agit d'un jour du Week-End que nous devons hachurer.

• Cliquer alors sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la liste Style de motif, choisir un graphisme puis valider par Ok,
• Valider de nouveau par Ok pour confirmer la mise en forme conditionnelle,

Les Week-End apparaissent instantanément hachurés. Si vous changez de mois ou d'année, les Week-End changent et les hachures s'adaptent grâce à la fonction Joursem. Si vous supprimez l'information sur le mois et/ou l'année, comme toujours, le planning disparaissant, les hachures n'ont plus lieu d'être et s'effacent.

Mise en valeur des jours du planning
La ligne 9 étant une ligne de titre, il s'agit de la faire ressortir en tant que telle. Nous proposons de lui appliquer un fond orange clair dégradé avec un texte rouge foncé et gras, comme pour les autres cellules importantes de la feuille. Le critère justifiant une telle mise en valeur consiste simplement à vérifier que la cellule de titre n'est pas vide. Si le jour de la semaine est inscrit, alors il est mis en forme dynamiquement. Pour les parcourir tous, nous devons là aussi défiger la colonne.

• Sélectionner toutes les lignes de titre, soit la plage de cellules D9:AH9,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil et choisir Nouvelle Règle,
• Dans la boîte de dialogue qui suit, choisir le type Utiliser une formule...,
• Puis, cliquer dans la zone de saisie située juste en dessous,
• Cliquer la première cellule des jours de semaine, soit D9,
• Enfoncer la touche F4 pour libérer son déplacement en colonne,
• Taper alors le critère : <>'', pour vérifier qu'elle n'est pas vide,
• Cliquer sur le bouton Format et activer l'onglet Remplissage de la boîte de dialogue qui suit,
• Cliquer sur le bouton Motifs et textures et définir un dégradé blanc vers un orange clair,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Définir un texte gras rouge foncé et valider par Ok,
• De retour sur la première boîte de dialogue, valider de nouveau par Ok pour confirmer,

Grâce à ce réglage dynamique, les titres du planning apparaissent parfaitement mis en forme, sans déborder après le dernier jour du mois. Comme toujours, les critères des formats se déclenchent en cascade. Si le mois et/ou l'année n'est pas défini, le jour de la semaine ne s'inscrit pas en ligne 9 et par voie de conséquence, la cellule n'est pas mise en forme.

Faire ressortir dynamiquement les plages horaires
Pour parfaire la construction dynamique du planning, nous devons proposer l'affectation automatique des horaires de travail, selon le choix effectué dans la dernière liste déroulante. Les plages horaires sont les suivantes :

• Matin : 6h30 à 9h30 et 11h30 à 15h30,
• Journée : 9h30 à 12h30 et 14h30 à 18h30,
• Soir : 13h30 à 16h30 et 18h30 à 22h30.

Pour construire ces formats dynamiques permettant de mettre en valeur ces plages de cellules respectives, nous devons les sélectionner précisément tour à tour et recouper de nombreux critères. Tout d'abord, le salarié concerné doit être désigné, ce qui peut se traduire par $F$2<>''. Le critère peut aussi être posé sur la cellule C4. Le jour correspondant ne doit pas être vide, ce qui se traduit par : D$9<>''. Il faut bien penser à défiger la cellule en colonne (Touche F4) pour que la comparaison puisse se déplacer sur la largeur du tableau. Enfin et dans le même temps, ce jour de la semaine ne doit être ni un Samedi, ni un Dimanche, ce qui se traduit par les critères : JOURSEM(D$8;2)<>6 et JOURSEM(D$8;2)<>7. Comme toutes ces conditions doivent être vérifiées ensemble, elles doivent être énumérées dans une fonction ET. Commençons par la mise en valeur des horaires du matin.

• Sélectionner la plage de cellules D10:AH13,
• Puis, tout en maintenant la touche CTRL enfoncée, sélectionner la plage D15:AH19,
• Cliquer sur le bouton Mise en forme conditionnelle et choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le type Utiliser une formule...,
• Dans la zone de saisie du dessous, reproduire strictement la formule suivante :

=ET($F$2<>''; D$9<>''; $C$5='Matin'; JOURSEM(D$8;2)<>6; JOURSEM(D$8;2)<>7)

• A l'aide du bouton Format, définir ensuite un remplissage en dégradé de blanc et vert,

Après validation, comme vous le constatez, pour l'affectation du matin, les plages définies sont mises en valeur en ignorant les jours de Week-End ainsi que les cellules dépassant le dernier jour du mois. Si le mois ou l'année ou le salarié ou l'affectation n'est pas définie, la plage horaire ne ressort pas.

Le même procédé doit être répliqué sur les autres plages correspondant aux autres tranches horaires, en adaptant le critère d'affectation sur la cellule C5.

• Sélectionner ensemble les plages de cellules D13:AH16 et D18:AH22,
• Puis bâtir le critère de mise en forme conditionnelle suivant :

=ET($F$2<>''; D$9<>''; $C$5='Journée'; JOURSEM(D$8;2)<>6; JOURSEM(D$8;2)<>7)

• Ensuite appliquer un dégradé de couleurs blanc et vert comme précédemment et valider,
• Sélectionner ensemble les plages de cellules D17:AH20 et D22:AH26,
• Saisir le critère de mise en forme conditionnelle suivant :

=ET($F$2<>''; D$9<>''; $C$5='Soir'; JOURSEM(D$8;2)<>6; JOURSEM(D$8;2)<>7)

• Appliquer un dégradé de couleurs blanc et vert comme précédemment et valider,

Désormais, à la sélection de l'une des affectations, les plages horaires se mettent en valeur pour finir de construire le planning, dès lors que les éléments nécessaires comme le mois, l'année et le salarié ont été désignés. Pour parfaire la présentation, les lignes intermédiaires de calculs doivent être masquées.

• Sélectionner l'intégralité des lignes 7 et 8 par leurs étiquettes sur la gauche de la fenêtre,
• Cliquer avec le bouton droit de la souris sur la sélection,
• Dans le menu contextuel, choisir Masquer ou Cacher selon les versions,

Avant de parfaire le tout grâce à des boutons de macros, nous proposons de réaliser une petite simulation pour apprécier la construction automatique et dynamique du planning.

• Supprimer le contenu des cellules C2 à C5,
• En C2, choisir l'année 2018 avec la liste déroulante,
• En C3, choisir le mois d'Avril avec la deuxième liste,

Le calendrier du mois d'Avril 2018 apparaît instantanément du dimanche 1 au lundi 30. Les bordures sont tracées et les titres sont mis en valeur. Enfin, les Week-End apparaissent parfaitement hachurés.

• En C4, sélectionner un salarié avec la troisième liste déroulante,

Le titre personnalisé du planning se construit automatiquement dans la cellule fusionnée prévue à cet effet.

• En C5, sélectionner l'affectation de journée avec la dernière liste déroulante,

Les plages horaires correspondantes, pour le salarié désigné et pour le mois et l'année sélectionnés, se mettent instantanément en évidence, en excluant les jours des Week-End.

Boutons de macros pour adaptations spécifiques
Nous proposons d'offrir à l'administrateur du confort et de l'efficacité par la construction automatique des plannings. Mais en même temps, nous proposons de lui offrir la possibilité de réaliser des ajustements spécifiques sur les plages horaires. Pour cela, nous allons disposer deux boutons exécutant chacun une macro spécifique. La première se chargera d'appliquer un remplissage en dégradé vert sur la sélection, afin de spécifier de nouvelles plages horaires. La seconde devra permettre de retourner à un affichage neutre. Elle devra donc supprimer la mise en forme conditionnelle sur la sélection et redéfinir les bordures.

Pour créer ces macros, nous devons simuler ces actions pendant qu'Excel les enregistre. Ensuite nous les rattacherons à des boutons. Si le ruban Développeur n'est pas présent dans vos onglets en haut de la fenêtre Excel, vous pouvez vous référer à la formation pour débuter la programmation en VBA Excel.

• Sélectionner deux cellules vides pour la simulation, par exemple AL10 et AL11,
• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Tout à fait à gauche, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue qui suit, taper le nom mef et valider par Ok,
• Cliquer sur l'onglet Accueil pour activer son ruban,
• Cliquer sur le petit bouton d'options situé en bas à droite de la section Police du ruban,
• Dans la boîte de dialogue, activer l'onglet Remplissage et cliquer sur le bouton Motifs et textures,
• Définir un dégradé à base de blanc et de vert à l'aide des listes déroulantes,
• Valider les réglages depuis la boîte de dialogue,
• De retour sur la feuille, activer de nouveau le ruban Développeur,
• Cliquer cette fois sur le bouton Arrêter l'enregistrement,

La macro existe mais elle n'est pas encore matérialisée. Elle est censée reproduire à l'identique les actions que nous avons simulées à savoir, appliquer un fond en dégradé.

• Au milieu du ruban Développeur, cliquer sur le bouton Insérer,
• Dans la liste des contrôles proposés, choisir le premier soit le bouton,
• Le tracer à hauteur de la cellule B9,
• Dans la boîte de dialogue qui se déclenche, sélectionner la macro mef pour les relier,
• Cliquer sur Ok pour valider,

Le bouton peut être atteint en mode création par clic droit afin de modifier son texte.

• Sélectionner la cellule AL12 et cliquer sur le bouton,

Comme vous le remarquez, cette dernière est instantanément affectée du dégradé que nous avons défini pendant l'enregistrement. Pour l'instant nous choisissons de ne pas l'appliquer sur le planning, tant que nous n'en avons pas créé un modèle en tous cas.

• Sélectionner de nouveau les cellules AL10 et AL11,
• Cliquer de nouveau sur le bouton Enregistrer une macro du ruban Développeur,
• Dans la boîte de dialogue, la nommer Effacer et valider,
• Dans la section Edition à droite du ruban Accueil, cliquer sur le bouton Effacer matérialisé par une gomme,
• Dans la liste, choisir Effacer la mise en forme (Effacer les formats selon la version),
• Cliquer ensuite sur le bouton d'options de la section Police du ruban Accueil,
• Activer l'onglet Bordure de la boîte de dialogue qui suit,
• Choisir un style de trait plein et cliquer sur les boutons Contour et Intérieur pour l'appliquer,
• Valider par Ok,
• De retour sur la feuille, cliquer sur l'onglet Développeur pour activer son ruban,
• Cliquer sur le bouton Arrêter l'enregistrement,
• Au milieu du ruban Développeur, cliquer sur le bouton Insérer,
• Dans la liste des contrôles proposés, choisir le premier soit le bouton,
• Le tracer à hauteur de la cellule B9 sous le précédent,
• Dans la boîte de dialogue qui se déclenche, sélectionner la macro effacer,
• Cliquer sur Ok pour valider,

Les deux boutons déclenchent désormais les actions que nous venons d'enregistrer, matérialisées par les macros. Nous les testerons lorsque nous aurons créé un modèle à partir de notre planning pour ne pas dégrader les réglages de mise en forme conditionnelle par le bouton Effacer.

Mise en page du planning - Réglages d'impression
Pour que l'application de création automatisée du planning soit pleinement productive, nous devons proposer au responsable de pouvoir imprimer le résultat, sans la moindre action supplémentaire. Or à ce stade, ce n'est pas le cas. Le planning se créée parfaitement et nous avons pu le vérifier. Mais quand il s'agit de l'imprimer, c'est un désastre, il reste beaucoup trop de travail à accomplir.

• Pour preuve, réaliser le raccourci clavier CTRL + P (ou Fichier/Imprimer),

Comme vous le constatez, une grande partie du planning est masquée. Il n'est donc pas directement exploitable en l'état. Nous proposons de réaliser des réglages de pages qui seront enregistrés avec le modèle de manière à ce que, à chaque nouvelle création de planning, la sortie papier soit déjà parfaitement calibrée. Le gain de temps sera considérable dans l'enchaînement des actions. Nous allons répliquer quelques techniques que nous avait enseignées la formation sur les réglages de pages pour les tableaux Excel.

• A l'aide de la quatrième liste déroulante sur la gauche de l'aperçu, basculer de l'orientation Portrait à une orientation Paysage,

Avec une feuille présentée à l'horizontale, nous gagnons de la place en largeur mais ce n'est pas suffisant. Pour que les colonnes vides soient ignorées, nous proposons de réaliser une zone d'impression capable d'accueillir le planning le plus volumineux, soit pour les mois comptant 31 jours.

• Revenir sur la feuille en cliquant sur la flèche de retour ou sur le ruban Accueil, selon les versions d'Excel,
• Sélectionner la plage de cellules allant de B2 à AH26, soit la plage correspondant au planning comportant le plus grand nombre de jours,
• Cliquer sur l'onglet Mise en page pour activer son ruban,
• Puis, cliquer sur le bouton ZoneImpr et sur Définir dans la liste qui apparaît,
• Réaliser de nouveau le raccourci clavier CTRL + P,

Tel que le prouve l'aperçu avant impression, nous avons gagné un peu de place puisque la première colonne, la colonne A, est exclue de la zone d'impression. C'est plus juste mais ce n'est pas encore suffisant.

• Cliquer sur la dernière liste déroulante en bas à gauche de l'aperçu et remplacer le réglage Pas de mise à l'échelle par l'option Ajuster toutes les colonnes à une page,
• Dans la foulée, cliquer sur le lien Mise en page situé juste en dessous,
• Dans la boîte de dialogue qui apparaît, activer l'onglet Marges,
• Dans la section Centrer sur la page, cocher la case Centrer horizontalement,
• Puis, valider ces réglages en cliquant sur le bouton Ok,

Cette fois le planning automatisé et personnalisé est parfaitement calibré dans la foulée de sa création. Il tient dans une seule page, il est joliment présenté, parfaitement lisible et apparaît centré sur la largeur de la feuille. Il ne reste plus qu'à l'imprimer pour le produire et le transmettre.

Créer un modèle de planning automatisé
Pour que la production de plannings personnalisés puisse se faire sans risquer d'endommager la source, nous devons créer un modèle à partir de ce classeur. A chaque exploitation du modèle, nous récupèrerons tous les paramétrages permettant de créer dynamiquement un nouveau planning horaires prêt à l'impression, en quelques clics seulement. Nous allons répliquer ce que nous avions mis en oeuvre pour créer un modèle de facturation automatisé.

• Supprimer les informations des cellules C2 à C5 pour vider le planning,
• Cliquer sur l'onglet Fichier en haut de la fenêtre Excel,
• Dans la liste, cliquer sur Enregistrer sous puis sur Parcourir,
• Dans la zone Type de la boîte de dialogue qui apparaît, choisir Modèle Excel (Prenant en charge les macros),

Comme vous le constatez, Excel change l'adresse de destination pour désigner l'emplacement centralisé de ses modèles.

• Dans la zone Nom de fichier, saisir modele-planning puis cliquer sur Enregistrer,
• De retour sur la feuille, réaliser le raccourci CTRL + W (Ou Fichier/Fermer) pour fermer le classeur,
• Puis cliquer de nouveau sur l'onglet Fichier
• Et choisir Nouveau dans la liste sur la gauche,

Comme l'illustre la capture ci-dessus, Excel propose par défaut ses propres modèles dans la catégorie proposés. Les modèles personnalisés sont tous centralisés dans la section Personnel.

• Cliquer sur le lien Personnel situé à droite du lien Proposés,
• Dans la liste des modèles, cliquer sur modele-planning,

Comme vous le constatez, nous récupérons bien la feuille de l'application vraisemblablement fidèlement à ce que nous avons créé. Mais ce classeur est désormais délié du modèle, comme en atteste le nom modele-planning1 qui apparaît dans la barre de titre, en haut de la fenêtre Excel. Ainsi nous préservons le modèle d'origine sans risquer de l'endommager. A chaque fois qu'un nouveau planning doit être crée, il suffit d'ouvrir une nouvelle copie de ce modèle, comme nous l'avons fait. Réalisons une simulation complète de création.

• A l'aide de la première liste, choisir l'année 2018,
• A l'aide de la deuxième liste, choisir le mois de Juin pour déclencher la création du Calendrier,
• A l'aide de la troisième liste, choisir un salarié,
• A l'aide de la dernière liste, choisir l'affectation du soir,

Le planning du salarié est parfaitement construit avec la mise en valeur des plages horaires du soir. Mais nous souhaitons créer quelques ajustements avant production. Pour le vendredi premier, nous souhaitons lui attribuer les horaires du matin.

• Sélectionner la plage de cellules D17:D26,
• Cliquer sur le bouton de macro Effacer,
• Puis sélectionner les plages D10:D13 et D15:D19 ensemble (CTRL),
• Cliquer sur le bouton de macro Affecter,

La plage horaire est ainsi réaffectée spécifiquement à la volée, en respectant les spécificités de mise en forme.

• Réaliser un CTRL + P pour visualiser le résultat à l'impression,

En quelques clics, nous avons bâti un planning personnalisé parfaitement calibré pour l'impression, donc prêt à la production et à la livraison. Aucun code VBA Excel n'a été nécessaire. L'architecture a été articulée autour des formules et de la mise en forme conditionnelle, pour des créations automatisées répondant aux critères. Enfin, deux petites macros automatiques ont permis de parfaire l'application afin d'offrir de la souplesse pour des ajustements personnalisés. Le tout étant déjà réglé pour l'impression, le responsable peut produire les plannings de chacun sans perte de temps.