Planning automatique des semaines feuille à feuille

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


Sujets que vous pourriez aussi aimer :


Planning des semaines feuille à feuille

C'est une nouvelle astuce Excel que nous proposons de découvrir pour construire automatiquement le calendrier des semaines de l'année. Et pour plus de clarté, nous souhaitons placer les plannings hebdomadaires sur des feuilles différentes. Nous allons le voir, c'est une fois encore la fonction Indirect qui est à l'honneur.

Classeur source
Pour la mise en place de cette solution, nous devons récupérer un classeur avec des réglages avancés.

• Télécharger le classeur semaines-feuille-a-feuille.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué d'une seule feuille pour l'instant. Elle se nomme Semaine_01. Elle doit donc héberger la première semaine de l'année. Les autres feuilles devront pouvoir se construire par simple duplication. Les formules seront en mesure de s'adapter pour livrer les dates des semaines correspondantes.



Une mise en forme conditionnelle est en place(=$C$3=""). Vous pouvez le constater en sélectionnant premièrement l'une des cellules du planning. Ensuite, vous devez cliquer sur le bouton Mise en forme conditionnelle dans la section Styles du ruban Accueil, puis en choisissant la commande Gérer les règles. Elle met en noir (Bordures, fond et texte) la plage du calendrier (B5:G30) pour la semaine en cours. De fait, si aucune date de construction n'est sélectionnée, la feuille est vidée et plus aucun planning n'apparaît. Vous pouvez le constater en supprimant l'année en cellule C3.

La semaine en cours est rappelée en cellule E3. Et c'est une astuce qui permet de la reconstruire automatiquement en fonction du nom de la feuille. Vous pouvez consulter la syntaxe dans sa barre de formule :

=SUBSTITUE(STXT(CELLULE("nomfichier"; A1); TROUVE("]"; CELLULE("nomfichier"; A1))+1; 99); "_"; " ")

Nous avons abordé cette fonction Cellule dans les volets précédents. Elle renvoie le chemin d'accès complet jusqu'à la feuille. C'est la raison pour laquelle, nous employons les fonctions Substitue, Stxt et Trouve afin de purger cette chaîne et de ne conserver que l'intitulé de l'onglet, tout en remplaçant l'Underscore (_) par un espace. Là aussi, il s'agit de techniques que nous avons démontrées dans des sujets récents.

La liste déroulante en cellule C3 permet donc de choisir une année. En guise d'année, il s'agit en réalité du premier Janvier de chaque nouvelle année. Vous pouvez le confirmer en consultant sa barre de formule (01/01/2021). Ces dates sont énumérées en colonne J. C'est un format personnalisé qui permet de limiter leur affichage à l'année (aaaa).

Planning de la première semaine
Les calculs automatiques sont attendus en ligne 5 entre les colonnes C et G. Ils doivent livrer les dates pour la semaine rappelée par le nom de la feuille et fonction du choix de l'année. Il est question premièrement de calculer le premier Lundi de l'année choisie. Les dates suivantes en découleront.

• En cellule C5, bâtir et valider la formule suivante :

=C3-1-JOURSEM(C3-1-1; 2)+7

Cette construction est issue de l'astuce précédente. Nous rabattons d'abord le premier jour de l'année (C3) sur le dernier jour de l'année précédente (C3-1) pour être sûr de calculer le premier Lundi et non le deuxième. Nous lui soustrayons l'écart existant entre le Lundi et son jour de semaine (-JOURSEM(C3-1-1;2)) <==> (-JOURSEM(C3-2;2)). Donc, nous tombons sur le dernier Lundi de l'année précédente. Admettons par exemple que le dernier jour de l'année (C3-1) tombe un Vendredi. Pour revenir au Lundi précédent, nous devons lui amputer 4 jours (5-1, soit C3-1-1). Ensuite, nous lui ajoutons 7 unités (+7) pour tomber sur le premier Lundi de l'année suivante, soit de l'année choisie.



Désormais, si vous choisissez une année avec la liste déroulante, vous avez le plaisir de constater que le premier Lundi de l'année est parfaitement calculé. Et naturellement, si vous changez d'année, cette borne de départ pour le planning de la première semaine, s'ajuste automatiquement.

Obtenir les jours restants pour la semaine en cours est un jeu d'enfants. Il suffit de bâtir le premier calcul dans la cellule d'à côté. Il doit simplement incrémenter d'un jour le Lundi calculé. Par réplication de la formule sur la droite, nous obtiendrons tous les jours suivants jusqu'au Vendredi.

• En cellule D5, taper la formule suivante : =C5+1,
• Puis, la valider par le raccourci clavier CTRL + Entrée,

Nous obtenons bien le mardi suivant.

• Tirer la poignée du résultat sur la droite jusqu'en cellule G5,

Nous obtenons bien les jours ouvrés restants pour cette première semaine de l'année.

Plannings des semaines suivantes
Nous devons maintenant construire les plannings pour les semaines qui suivent la première. Du travail nous attend pour la semaine 2. Elle doit réagir en fonction de la semaine 1. Ensuite, pour les autres semaines, toutes les répercussions se feront automatiquement en cascade.

• En bas de la fenêtre Excel, cliquer droit sur l'onglet de la feuille,
• Dans le menu contextuel, choisir la commande Déplacer ou copier,
• Dans la boîte de dialogue qui suit, cocher la case Créer une copie,
• Puis, cliquer sur la ligne (en dernier) pour placer la copie après la première feuille,

• Cliquer sur le bouton Ok pour valider la création de la copie,
• Double cliquer sur l'onglet de la feuille copiée pour activer sa saisie,
• Puis, la renommer Semaine_02 et valider avec la touche Entrée,

Comme vous pouvez le voir, grâce à la formule existante en cellule E3, l'intitulé s'est automatiquement adapté pour cette deuxième semaine.

Le choix de l'année ne doit être émis qu'à partir de la première feuille. C'est en fonction du calcul de la première semaine que les autres doivent se déduire.

• Sélectionner les cellules B3 et C3,
• Enfoncer la touche Suppr du clavier pour vider les contenus,

Comme vous pouvez le voir, le planning de la deuxième semaine disparaît complètement. Cette règle de mise en forme conditionnelle doit être recalée pour pointer sur la première feuille. Tant qu'une année est choisie, les calendriers doivent être dessinés. Cette règle sera ainsi portable pour toutes les autres duplications. Mais avant cela, nous allons réaliser quelques adaptations.

• Fusionner les deux cellules B3 et C3 encore sélectionnées,
• Cliquer ensuite sur la cellule E3 pour la sélectionner,
• Dans sa barre de formule, sélectionner toute la syntaxe et la copier (CTRL + C),
• Valider par la touche Entrée pour sortir correctement de la barre de formule,
• Cliquer sur la cellule B3 désormais fusionnée,
• Dans la barre de formule, coller la syntaxe (CTRL + V) précédemment copiée,
• Puis, valider par le raccourci clavier CTRL + Entrée,
• Dans la section Police du ruban Accueil, choisir un vert clair pour la couleur du texte,
• Puis, augmenter sa taille à 16 pt,

Ensuite, il convient de supprimer le contenu de la cellule E3 et d'effacer ses attributs de format. Pour cela, le plus simple consiste encore à prélever les attributs de la cellule voisine D3 avec le Pinceau de reproduction de mise en forme et de les appliquer à la cellule E3.

Comme nous l'annoncions, il est maintenant temps de réajuster définitivement la règle de mise en forme conditionnelle.

• Sélectionner la plage de cellules B5:G30,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Gérer les règles,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Modifier la règle,
• Dans la zone de saisie, adapter la syntaxe comme suit : =Semaine_01!$C$3="" ,

Nous ajoutons simplement le nom de la première feuille en préfixe. Attention néanmoins de bien respecter le point d'exclamation qui suit.

De cette manière, tous les plannings des semaines suivantes réagiront si l'année est bien définie sur la feuille de la première semaine.

• Valider cette modification en cliquant sur le bouton Ok de la boîte de dialogue,
• De retour sur le gestionnaire, cliquer de nouveau sur le bouton Ok,

Comme vous le constatez, le planning réapparaît aussitôt sur cette deuxième feuille. Si vous supprimez l'année en cellule C3 de la première feuille, les plannings des deux feuilles disparaissent aussitôt. Pour la suite des opérations, il convient de conserver une année définie en cellule C3 de la première feuille et de revenir sur la deuxième.

Calcul de la nouvelle semaine
Désormais, nous devons ajuster les calculs de cette deuxième feuille pour afficher les dates de la semaine en corrélation. En réalité, seule la formule du point de départ de la semaine en cellule C5 doit être modifiée. Il s'agit de prélever la date de départ de la feuille précédente pour l'incrémenter de sept jours. Et comme vous le savez, pour pointer sur une cellule d'une autre feuille, nous devons la préfixer du nom de cette dernière suivi d'un point d'exclamation. Pour la bonne compréhension, nous proposons de procéder par étapes.

• En cellule C5 de la deuxième feuille, remplacer la formule par la suivante :

="Semaine_" & TEXTE(DROITE(B3; 2)-1; "00")

La fonction droite permet de prélever les deux derniers chiffres du nom de la feuille précédemment recomposé en cellule B3. Nous retirons une unité pour passer au numéro précédent, soit le chiffre 1 ici. Mais pour que le zéro reste en préfixe, nous forçons le format avec la fonction Texte ("00"). Puis, nous concaténons le tout avec le texte Semaine_ en préfixe. Il en résulte le nom de la feuille précédente sur laquelle il s'agit de pointer.

Mais comme vous le savez, pour que ce texte reconstruit désignant la feuille précédente soit réellement considéré comme une feuille du classeur, nous devons l'interpréter avec la fonction Excel Indirect.

• En cellule C5, adapter la précédente syntaxe comme suit :

=INDIRECT("Semaine_" & TEXTE(DROITE(B3; 2)-1; "00") & "!C5") + 7

A validation et comme vous pouvez le voir, toutes les dates s'ajustent parfaitement et automatiquement pour cette deuxième semaine. Grâce à la fonction Indirect, nous pointons effectivement sur la feuille précédente et plus précisément sur la cellule C5 de cette dernière que nous concaténons avec un point d'exclamation. Nous prélevons donc sa date que nous incrémentons de sept jours pour poursuivre l'énumération du planning sur la nouvelle semaine. Et grâce aux calculs déjà en place sur les cases suivantes de la ligne 5, toutes les dates des jours restants se recomposent automatiquement en fonction de ce nouveau point de départ.

Si vous changez d'année dans la première feuille, le planning de la première semaine se reconstruit et dans le même temps, sa suite logique se recompose dans la deuxième.

Pour une démonstration aboutie et maintenant que le mécanisme est en place, il convient de dupliquer cette deuxième feuille à plusieurs reprises tout en adaptant les noms.

• Dupliquer la feuille Semaine_02 pour la placer en dernière position,

Il s'agit d'utiliser la même technique que précédemment par clic droit sur l'onglet de la feuille.

• Renommer l'onglet de cette copie avec l'intitulé Semaine_03,

A validation et comme vous pouvez l'apprécier, la troisième semaine de l'année choisie se construit automatiquement.



Pour un planning complet, il convient de produire les feuilles des semaines suivantes selon le même mécanisme de duplication et de renommage. Et désormais, à chaque changement de date avec la liste déroulante sur la première feuille, c'est l'intégralité du planning qui se reconstruit semaine à semaine et feuille à feuille.