Planning Excel en fonction du numéro de semaine

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


Sujets que vous pourriez aussi aimer :


Plannings des numéros de semaine

Dans l'optique de créer des plannings de réservation de salles, cette formation Excel démontre comment il est possible de construire automatiquement des plannings hebdomadaires, sur la base d'un numéro de semaine désigné.



Le choix de l'année et du numéro de semaine, par le biais de deux listes déroulantes, doit suffire à construire et mettre en forme automatiquement le planning de la semaine.

Source et présentation de la problématique
Pour la réalisation de ces travaux, nous proposons tout d'abord de réceptionner un classeur offrant des données à manipuler.

• Télécharger le classeur planning-numero-semaine.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons un classeur composé de trois feuilles nommées respectivement : Planning, Archives et Sources.



La feuille Sources porte bien son nom. Elle propose les données permettant de remplir les listes déroulantes de la feuille Planning.

La feuille Archives n'est pas utile à cette formation. Dans une prochaine étape, elle servira à mémoriser les dates et causes des réservation des salles de réunion de l'entreprise.

Enfin, la feuille Planning est le support de nos travaux.



Au choix de l'année dans la première liste déroulante et du numéro de semaine dans la seconde liste déroulante, le planning de la semaine doit se construire automatiquement à partir de la ligne 7.

Premier jour d'une semaine
Le principal défi de cette formation consiste à identifier le premier jour d'une semaine désignée par son numéro. Le planning de réservation de la salle désignée doit en effet s'étendre du Lundi au Vendredi.

L'énumération de ces dates doit donc débuter à partir de la cellule C7, à la place de la mention Debut J. Puis, elle doit se poursuivre jusqu'en cellule G7 pour restituer chaque jour de la semaine choisie. Mais ce calcul ne doit se déclencher que si l'année et la semaine sont définies. La fonction Excel Si est donc nécessaire. Comme deux conditions sont à vérifier, l'emploi de la fonction Excel ET est indispensable pour les énumérer.

• En cellule C7, construire la formule suivante :

=SI(ET(C5<>'';G5<>'');7*G5+DATE(C5;1;1)-JOURSEM(DATE(C5;1;1))-5;'')

Le résultat obtenu, si une date et une semaine dont définies, est numérique car le format de cellule n'est pas adapté.

La fonction Si permet donc tout d'abord de tester la double condition sur la cellule de l'année (C5) et de la semaine (G5). Pour retrouver le premier jour de la semaine désignée, nous commençons par multiplier ce numéro par sept (7*G5). Une semaine est effectivement composée de sept jours. Nous en déduisons donc le nombre de jours écoulés depuis le début de l'année. Nous lui ajoutons le premier jour de l'année (DATE(C5;1;1)). Cette technique permet de transformer le résultat en une date. Et plus précisément, il s'agit de la date du dernier jour pour la semaine en cours, mais a priori seulement.

En effet, cette déduction est approximative pour l'instant. Rien n'indique que le premier jour de l'année était un Lundi. Et dans ce cas, la première semaine est forcément tronquée car incomplète. Pour corriger cet écart, nous soustrayons à cette date, le numéro du premier jour de la première semaine de l'année (JOURSEM(DATE(C5;1;1))).

Mais, le fait de multiplier chaque semaine par son nombre de jours (7), conduit au dernier jour de la semaine qui est un samedi dans le calendrier américain. C'est la raison pour laquelle nous déduisons enfin 5 unités, pour retomber sur le premier jour de la semaine désignée, soit le Lundi.

Avant de songer au format de cellule, sur la base de cette première date, nous devons construire tous les jours ouvrés de la semaine désignée. Bien entendu, le calcul ne doit être enclenché que si la première date est fournie.

• Sélectionner la cellule suivante de la ligne 7, soit la cellule D7,
• Taper la formule suivante : =SI(C7<>'';C7+1;''),

Si le premier jour de la semaine est trouvé, nous incrémentons la date d'une journée. Comme la cellule C7 n'est pas figée dans le calcul, la logique répliquée raisonnera à chaque reprise sur la précédente date, permettant l'incrémentation jusqu'au Vendredi.

• Cliquer et glisser la poignée du résultat à l'horizontale jusqu'en colonne G,

Tous les numéros de série respectent ainsi une suite logique incrémentée.

• Sélectionner l'ensemble des résultats, soit la plage de cellules C7:G7,
• Dans la section Nombre du ruban Accueil, déployer la liste des formats,
• Tout en bas des propositions, choisir Autres formats numériques,

Nous souhaitons afficher la date avec l'information textuelle sur le jour et le mois. La précision sur l'année n'est pas nécessaire. Elle est en effet rappelée par la première liste déroulante. Nous allons donc construire un format de date personnalisé.

• Dans la boîte de dialogue, sélectionner la catégorie Personnalisée,
• Dans la zone Type, taper le code suivant : jjjj jj mmmm,

La première séquence (jjjj) permet d'obtenir l'indication textuelle sur le jour de la semaine. La seconde (jj) permet de conserver l'information numérique. La dernière (mmmm) transcrit le numéro du mois dans son équivalent textuel. Comme l'information sur l'année n'est pas précisée, elle ne sera pas affichée, mais elle sera bien prise en compte par Excel. D'ailleurs, la zone Exemple de la boîte de dialogue livre un aperçu probant construit à partir de la première date de la sélection.

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider ce format,

De retour sur la feuille, vous constatez que les résultats ont parfaitement été formatés en dates, vraisemblablement cohérentes.

Si vous choisissez par exemple la semaine 26, correspondant approximativement au milieu de l'année, vous obtenez bien les jours de la semaine pour la fin du mois de Juin.



De plus, l'énumération débute bien à partir du Lundi pour se terminer le Vendredi. Bref, nous sommes en train de construire l'outil permettant de dresser les plannings en fonction d'un numéro de semaine désigné. Grâce aux conditions posées, vous constatez qu'en supprimant l'indication sur la semaine et/ou sur l'année, les résultats disparaissent automatiquement.

Construire le planning horaire
Pour penser à la conception future de la réservation des salles de réunion, nous devons construire les tranches horaires, de 9h00 à 19h00. Ces plages doivent apparaître en colonne B, à partir de la cellule B8 portant pour l'instant la mention Debut H. Comme précédemment, cette construction automatique ne doit être déclenchée que dans la mesure ou l'année et la semaine sont fournies. Par raisonnement en cascade, il est plus simple de poser un unique test sur l'existence de la première date en C7.

• Sélectionner la cellule B8 et remplacer la mention Debut H par le calcul suivant :

=SI(C7<>'';'9:00';'')

Si la date du début de la semaine à été calculée, alors nous inscrivons la première heure à partir de laquelle il sera possible de réserver une salle de réunion. A validation, grâce à cette inscription (9:00), la première valeur est fidèlement retranscrite au format heure. Les suivantes doivent toutes être incrémentées d'une unité jusqu'à atteindre 19h00.

• En cellule B9, taper la formule suivante : =SI(B8<>'';B8+'1:00';''),

A validation, le résultat est pour le moins surprenant. Nous obtenons une valeur numérique décimale. Tout est une fois de plus une question de format de cellule. Si l'horaire précédent est inscrit, nous incrémentons le suivant. Nous devons donc répliquer la logique de ce calcul jusqu'à atteindre la fin de journée.

• Cliquer et glisser la poignée de la cellule B9 à la verticale jusqu'en ligne 18,
• Sélectionner l'ensemble de ces résultats, soit la plage de cellules B8:B18,
• Puis, dans la section Nombre du ruban Accueil, déployer la liste des formats,
• Tout en bas des propositions, choisir Autres formats numériques,
• Dans la boîte de dialogue qui suit, sélectionner la catégorie Personnalisée,
• Dans la zone Type, saisir le code suivant : hh:mm,

Ainsi, nous demandons à Excel d'afficher les cellules sélectionnées au format heure, avec deux unités pour les heures et deux unités pour les minutes.

• Valider ce réglage en cliquant sur le bouton Ok de la boîte de dialogue,
• De retour sur la feuille, cliquer sur le bouton Aligner à droite dans le ruban Accueil,

Nous obtenons bien la plage horaire souhaitée. Une fois encore, grâce à cette construction en cascade, si vous supprimez l'indication sur la semaine et/ou l'année, toutes les informations calculées disparaissent.

• Sélectionner de nouveau tous les horaires, soit la plage de cellules B8:B18,
• Tout en maintenant la touche CTRL enfoncée, sélectionner les dates, soit la plage C7:G7,

Pour optimiser les opérations de mise en forme, nous regroupons les données dans une même sélection.

• Formater la sélection en gras et appliquer une couleur bleue soutenue pour la police,

Ces réglages disparaissent avec les contenus. Il est donc judicieux de les prévoir en amont. Les remplissages quant à eux, devront réagir en fonction des données, si elles existent.

• Sélectionner désormais toutes les cellules à l'intérieur du planning, soit la plage C8:G18,
• Formater la sélection en gras et appliquer une couleur gris soutenu pour la police,

Ces réglages concernent l'implémentation du planning. Ils prendront effet lorsque nous aurons finalisé l'outil de réservation des salles de réunion.

Présentation dynamique du planning automatique
Nous proposons de présenter la ligne de titre sur un fond en dégradé de bleu. A l'intérieur du planning, nous souhaitons une bordure inférieure de séparation, en bas de chaque ligne donc. Ces réglages sont conditionnels car ils ne disparaissent pas avec le contenu. En d'autres termes, ces formats doivent s'enclencher dynamiquement lorsque le contenu des cellules n'est pas vide. Vous l'avez compris, nous devons exploiter des règles de mise en forme conditionnelle.

• Sélectionner tout d'abord la ligne de titre, soit la plage de cellules C7:G7,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Tout en bas de la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Taper alors le critère suivant : =C7<>'',

L'analyse débute donc sur la première date potentielle. Pour qu'elle se poursuivre sur les autres dates de la même ligne, la cellule C7 doit être libérée. En d'autres termes, les dollars proposés pour figer la cellule, doivent disparaître.

• Cliquer alors sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Puis, cliquer sur le bouton Motifs et textures,
• Avec la première liste déroulante, choisir un bleu assez soutenu,
• A l'aide de la seconde liste déroulante, définir un bleu pâle,

La couleur doit ainsi se dégrader du haut vers le bas de la cellule non vide.

• Cliquer sur le bouton Ok pour revenir sur la deuxième boîte de dialogue,
• Cliquer sur le bouton Ok pour revenir sur la première boîte de dialogue,

La règle ainsi définie consiste à appliquer un remplissage dégradé aux cellules portant effectivement du contenu, soit une date.

• Cliquer une dernière fois sur le bouton Ok pour revenir sur la feuille Excel,

Aussitôt, la ligne de titre se pare en effet du remplissage en dégradé de bleus. Et bien sûr, si vous supprimez l'une ou les deux indications sur l'année et la semaine, le format dynamique disparaît en même temps que les dates. Notre outil de construction automatique des plannings de la semaine prend forme.

• Sélectionner l'intégralité du planning de réservation, soit la plage de cellules B7:G18,
• Cliquer sur le bouton Mise en forme conditionnelle dans le ruban Accueil,
• En bas de la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Taper alors la règle suivante : =$B7<>'',

L'analyse débute à partir de la première cellule du planning, soit la cellule B7. Nous la conservons figée seulement en colonne. Ainsi, pour chaque ligne, dès lors que l'horaire est renseigné en en-tête, un format dynamique doit se déclencher sur l'intégralité de la ligne. Et ce format consiste à souligner ces dernières par une bordure inférieure.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Bordure,
• Dans la zone Style, choisir le trait le plus épais,
• Dans la zone Couleur, choisir un bleu assez foncé,
• Puis, cliquer sur le petit bouton permettant d'appliquer ce trait à la bordure inférieure,
• Cliquer alors sur le bouton Ok en bas de la boîte de dialogue pour revenir sur la première,
• Cliquer de nouveau sur le bouton Ok pour valider cette règle de mise en forme conditionnelle,

De retour sur la feuille Excel, vous notez que toutes les plages horaires du planning de réservation, sont explicitement délimitées. Comme toujours, si l'une ou les deux indications sur l'année et la semaine ne sont pas fournies, ces bordures disparaissent en même temps que le reste.

Alterner les couleurs de lignes
Pour des raisons de clarté et de repérage, nous souhaitons désormais alterner les couleurs de fond du planning. Bien sûr, ce format demeure dynamique. Il ne doit intervenir que lorsque la construction du calendrier hebdomadaire est avérée.

La règle à construire est spécifique. Elle doit valider deux conditions. De fait, l'emploi de la fonction Excel ET est nécessaire pour les recouper. La première est semblable à la précédente. Elle doit s'assurer que la plage horaire est définie en en-tête de ligne. La seconde doit vérifier que l'indice de la ligne en cours est impair. De cette manière, toutes les lignes paires seront sautées et l'alternance sera créée. Pour cela, nous devons exploiter la fonction Excel Ligne. Comme son nom l'indique, elle retourne le numéro de ligne de la cellule qui lui est passée en paramètre. Et puis, nous devons l'imbriquer dans la fonction Excel Mod pour calculer le reste de la division par 2. Si ce reste existe, nous saurons qu'il ne s'agit pas d'une ligne paire. En conséquence, nous pourrons appliquer la couleur de fond.

• Sélectionner la plage de cellules C8:G18,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, construire la règle suivante : =ET($B8<>'';MOD(LIGNE($B8);2)=1),

La cellule B8 du premier horaire est utilisée pour l'analyse des deux conditions. Comme précédemment, afin qu'elle se poursuive sur les lignes du dessous, nous ne la conservons figée qu'en colonne. Dans la mesure où l'horaire est bien défini ($B8<>'') et qu'il s'agit d'une ligne impaire (MOD(LIGNE($B8);2)=1), nous pouvons déclencher la couleur de fond pour l'alternance.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un bleu pâle puis valider par Ok,
• De retour sur la première boîte de dialogue, valider la règle en cliquant sur le bouton Ok,

De retour sur le planning, vous notez en effet l'apparition de ce format dynamique alterné précieux. Comme toujours, si l'indication sur la semaine et/ou l'année est manquante, l'alternance de couleur disparaît.

Pour parachevez la construction automatique du planning, il convient d'ajouter une bordure de séparation verticale entre les heures et le reste du calendrier sur la droite.

La règle doit être appliquée sur la plage de cellules B8:B18 : =B8<>''.



Grâce à ces travaux, nous pourrons finaliser l'outil par le code VBA. Son rôle doit consister à permettre la réservation des salles de réunion.