Plannings hebdomadaires des salles de réunion réservées

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 salles de réunion

Dans la formation Excel précédente, nous avons développé l'outil permettant de construire un calendrier hebdomadaire, en fonction d'un numéro de semaine stipulé. Ce calendrier doit désormais servir de support afin de renseigner sur la disponibilité des salles de réunion.



Dans cette partie, nous proposons de résoudre le problème par calculs Excel, sans l'intervention du code VBA. Les réservations sont en effet toutes archivées dans une feuille annexe du même classeur.

Source et présentation de la problématique
Pour poursuivre le développement, nous devons tout d'abord réceptionner les travaux engagés au cours de la formation précédente.

• Télécharger le classeur planning-reservation.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é,

La feuille Archives est active par défaut. Elle recense les réservations des salles en fonction du numéro de semaine, de la date et de l'heure. Grâce à ces indications, nous pourrons réaliser un calcul de repérage selon la semaine et la salle choisie depuis la feuille Planning. Ce calcul servira alors d'extraction des réservations concordantes. C'est pourquoi il existe une feuille nommée Extraction.



Il offre la même structure que le tableau de la feuille Archives. Il doit s'enrichir dynamiquement, selon les réglages de la feuille Planning et grâce au calcul intermédiaire à produire. C'est ainsi que nous isolerons les réservations à restituer dans le calendrier hebdomadaire.

Cette liste des réservations est relativement courte. Elle est suggérée à titre d'exemple. Seules les salles Oméga et Multimédia ainsi que les semaines 36 et 37 sont concernées. Pour les besoins des travaux, nous devons donc caler certaines indications de la feuille Planning.

• En bas de la fenêtre Excel, cliquer sur l'onglet Planning pour activer sa feuille,
• A l'aide de la première liste déroulante, choisir l'année 2019,
• Avec la deuxième liste déroulante, choisir la salle Oméga,
• Avec la dernière liste déroulante, désigner la semaine 36,

Remarque : Une correction a été apportée en cellule B8 de la feuille Planning :

=SI(C7<>'';'09:00'*1;'')

Pour les besoins de correspondances et de normalisation des horaires, un zéro a été ajouté en préfixe de la première heure.

Repérer les réservations concordantes
L'astuce à développer est désormais bien connue. Nous l'avons exploitée à maintes reprises, à commencer par la formation montrant comment extraire des données selon des critères recoupés. Elle consiste à inscrire un numéro incrémenté en regard de chaque ligne pour lesquelles la semaine, la salle et l'année correspondent.

Il s'agit donc de vérifier un triple critère. Lorsque la condition globale est satisfaite, un numéro doit s'inscrire dans la colonne du calcul. Et pour qu'il puisse grandir, nous allons exploiter la fonction Excel Max sur une plage qui croît en même temps que la formule est répliquée.

• En bas de la fenêtre Excel, cliquer sur l'onglet Archives pour sélectionner sa feuille,
• Sélectionner ensuite la cellule G3,

Nous choisissons donc de bâtir ce calcul de repérage en regard du tableau des archives.

• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Taper la fonction pour énumérer les critères, suivie d'une parenthèse, soit : Et(,
• Cliquer sur le premier numéro de semaine, soit la cellule B3,
• Taper le symbole égal (=) pour la première condition à vérifier,
• En bas de la fenêtre Excel, cliquer sur l'onglet Planning pour activer sa feuille,
• Puis, sélectionner le numéro de semaine désigné, soit la cellule G5,
• Enfoncer alors la touche F4 du clavier pour la figer, ce qui donne : Planning!$G$5,

Ce calcul est en effet destiné à être reproduit sur les lignes du dessous de la feuille Archives. Or, chaque numéro de semaine énuméré doit être comparé à cette cellule de référence. Elle ne doit donc pas bouger.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• En bas de la fenêtre Excel, cliquer sur l'onglet Archives pour revenir sur sa feuille,
• Sélectionner alors la première salle, soit la cellule C3,
• Taper le symbole égal (=) pour la deuxième condition à satisfaire,
• En bas de la fenêtre Excel, cliquer sur l'onglet Planning pour activer sa feuille,
• Sélectionner la salle désignée ou taper ses références, soit E5,
• Enfoncer la touche F4 du clavier pour les mêmes raisons que précédemment (Planning!$E$5),
• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Saisir la fonction pour l'année d'une date suivie d'une parenthèse, soit : Annee(,

En effet, nous devons comparer l'année de la date recensée dans les archives avec l'année sélectionnée sur le planning.

• Sur la feuille Archives, sélectionner la première date, soit la cellule D3,
• Fermer la parenthèse de la fonction Annee,
• Taper le symbole égal (=) pour la condition à honorer,
• Sur la feuille Planning, sélectionner l'année choisie, soit la cellule C5,
• Puis, enfoncer la touche F4 du clavier pour la figer,
• Fermer la parenthèse de la fonction Et,
• Puis, taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Taper la fonction pour la plus grande valeur, suivie d'une parenthèse, soit : Max(,
• Sur la feuille Archives, cliquer la cellule au-dessus du calcul, soit G2,
• Taper le symbole deux points (:) pour générer la plage : G2:G2,
• Dans la barre de formule, cliquer sur la première des deux références,
• Puis, enfoncer la touche F4 du clavier pour figer seulement la borne supérieure, soit : $G$2:G2,
• Cliquer alors à la fin de la syntaxe pour y replacer le point d'insertion,
• Fermer la parenthèse de la fonction Max,
• Puis, ajouter une unité à ce calcul, soit : +1,

Grâce à cette technique, dès qu'une réservation concordante est trouvée, un numéro est inscrit en regard de l'enregistrement. Comme la fonction Max agit sur une plage de cellules qui va progresser en même temps que le calcul est répliqué sur la ligne du dessous, chaque dernier numéro sera incrémenté. C'est ainsi que nous allons fabriquer une suite logique de repérage à des fins d'extraction.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Puis, taper deux guillemets ('') pour garder la cellule vide en cas de non-correspondance,
• Fermer la parenthèse de la fonction Si,
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette technique permet de conserver active la cellule du résultat. Nous allons donc pouvoir l'exploiter dans l'enchaînement.

• Double cliquer sur la poignée du résultat pour répliquer la formule sur la hauteur du tableau,

Comme vous pouvez le voir, des numéros incrémentés repèrent aussitôt la salle choisie sur le planning, en fonction de l'année et de la semaine désignée.

La formule que nous avons bâtie est la suivante :

=SI(ET(B3=Planning!$G$5; C3=Planning!$E$5; ANNEE(D3)=Planning!$C$5); MAX($G$2:G2)+1;'')

Nous avons volontairement supprimé le nom de la feuille Archives en préfixe des cellules. Excel l'impose au changement de feuille. Mais elle n'est pas nécessaire. Sa référence est implicite puisque le calcul est porté par cette même feuille.

Extraire les salles réservées
Les données résultantes doivent donc être isolées dans la feuille Extraction. Le calcul doit chercher chaque numéro incrémenté pour extraire les données de l'enregistrement concordant. La fonction Excel Index permet d'extraire une donnée située au croisement d'une ligne et d'une colonne. Pour chaque champ, la colonne est connue. Elle est donc fixe. La ligne est variable en revanche. Nous devons donc exploiter la fonction Excel Equiv pour la repérer. Sa recherche doit se baser sur ces numéros incrémentés que nous pouvons simuler grâce à la fonction Excel Ligne. Cette dernière retourne en effet l'indice de ligne d'une cellule passée en paramètre. Non figée, elle permettra à la fonction ligne de produire tous les numéros en même temps que la formule est répliquée. Enfin, pour éviter tout retour d'erreur, nous devons englober le tout dans une fonction de gestion d'erreur (SiErreur).

• En bas de la fenêtre Excel, cliquer sur l'onglet Extraction pour activer sa feuille,
• Sélectionner la première semaine à extraire, soit la cellule B3,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Taper la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• En bas de la fenêtre Excel, cliquer sur l'onglet Archives pour activer sa feuille,
• Puis, saisir les références suivantes : B3:B1000,

Nous désignons ainsi le tableau de recherche pour la semaine, qui n'est autre que la première colonne. Nous prévoyons suffisamment de lignes en cas de calendrier chargé.

• Enfoncer deux fois la touche F4 du clavier, ce qui donne : Archives!B$3:B$1000,

De cette manière, le tableau est figé en hauteur. Ainsi la recherche débutera toujours à partir de la ligne 3. En libérant la colonne, nous prévoyons de répliquer le calcul sur les autres rangées qui respectent en effet le même ordre.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction retournant l'emplacement suivie d'une parenthèse, soit : Equiv(,
• Saisir la fonction donnant le numéro de ligne suivie d'une parenthèse, soit : Ligne(,
• Cliquer sur une cellule de la première ligne, par exemple A1,

De cette façon, la première recherche sera bien réalisée sur le chiffre 1.

• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sur la feuille Archives, désigner la colonne G des numéros, soit : G3:G1000,
• Enfoncer la touche F4 du clavier pour figer intégralement cette plage,

Quelle que soit l'information à retourner, la recherche des numéros incrémentés doit toujours se faire dans cette colonne. Elle ne doit donc pas varier.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper un point-virgule pour passer dans l'argument de la colonne pour la fonction Index,
• Saisir le chiffre 1 et fermer la parenthèse de la fonction Index,

En effet, l'élément à restituer, le numéro de semaine, est situé dans la première colonne du tableau mentionné en premier argument. De plus, ce tableau n'est représenté que par une seule colonne, tel que nous l'avons inscrit.

• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,
• Tirer la poignée du résultat sur la droite jusqu'en colonne F,
• Puis, tirer la poignée de la sélection sur quelques dizaines de lignes vers le bas,

Comme nous avons judicieusement figé certaines plages et libéré d'autres, le calcul se réplique sur l'ensemble du tableau offrant le détail de l'extraction, pour chaque réservation concordante.

Des défauts de mise en forme sont à noter. Ils pénalisent la lecture mais pas la suite des travaux. Idéalement, il convient d'aligner les textes sur la gauche, de formater les dates en date et les heures en heure.

• En bas de la fenêtre Excel, cliquer sur l'onglet Planning pour afficher sa feuille,
• Avec la deuxième liste déroulante, remplacer la salle Oméga par la salle Multimédia,
• Puis, revenir sur la feuille Extraction,

Comme vous pouvez le voir, la liste des réservations s'est aussitôt actualisée. En effet, le calcul de réparage intermédiaire tient compte des réglages de la feuille Planning. Et cette extraction est produite sur ces numéros incrémentés. La formule que nous avons bâtie et répliquée est la suivante :

=SIERREUR(INDEX(Archives!B$3:B$1000; EQUIV(LIGNE(A1); Archives!$G$3:$G$1000; 0); 1);'')

Ce sont donc ces informations désormais isolées que nous devons diffuser dans le calendrier hebdomadaire.

Remplir le planning de réservation
La tâche semble simple a priori. Il suffit d'établir la correspondance des paires Date/Heure entre les cellules du calendrier et le tableau d'extraction. Mais ce défi doit être relevé par une unique formule. Et c'est un calcul matriciel qui permet d'analyser des critères multiples sur des plages de données.

Nous avons déjà entrepris un tel raisonnement dans une formation passée. En premier paramètre de la fonction Equiv, nous allons transmettre le chiffre 1. Il fait figure de test booléen pour les matrices avec critères à passer en second argument. Le tout doit être englobé dans la fonction d'extraction Index, sans oublier la gestion d'erreur.

• En bas de la fenêtre Excel, cliquer sur l'onglet Planning pour revenir sur sa feuille,
• Sélectionner la première case du calendrier, soit la cellule C8,
• Taper le symbole égal (=) pour initier la formule,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Taper la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• En bas de la fenêtre Excel, cliquer sur l'onglet Extraction pour activer sa feuille,
• Désigner ou saisir les références de la colonne Objet, par exemple la plage F3:F100,

Ce sont en effet les causes des réservations que nous souhaitons inscrire dans les cases du planning.

• Enfoncer la touche F4 du clavier pour figer cette plage, ce qui donne : Extraction!$F$3:$F$100,

Les informations à importer sont situées dans cette plage immuable. Nous allons en effet répliquer le calcul par la suite, sur les autres cases du calendrier hebdomadaire.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Taper le chiffre 1 en guise de test booléen,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la rangée de recherche,

C'est ici que nous devons émettre les critères sur les plages à recouper. Un calcul matriciel raisonne rangée par rangée. Dès que l'une d'entre elles satisfera la condition sur l'heure et la date, elle retournera l'indice de ligne correspondant, grâce au test booléen passé en premier paramètre. Et grâce à cette extraction préliminaire, nous savons que l'année, la semaine et la salle sont déjà concordante. Elle permet donc de limiter l'énumération des conditions à satisfaire dans cette formule matricielle.

• Ouvrir la parenthèse pour la première plage et son critère,
• Sur la feuille Extraction, désigner la plage des heures, soit par exemple : E3:E100,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : Extraction!$E$3:$E$100,
• Taper alors le critère suivant : =B8,

Nous cherchons ainsi à établir la correspondance entre l'heure en cours dans le planning et les heures archivées dans la feuille Extraction.

• Enfoncer trois fois la touche F4 du clavier pour figer seulement la colonne, soit : $B8,

Chaque heure du calendrier doit en effet être comparée aux archives extraites. C'est pourquoi, nous libérons la cellule en ligne. Mais pour chaque ligne du planning, l'heure est inscrite dans la même colonne, que nous figeons donc.

• Fermer la parenthèse de ce premier critère matriciel,
• Taper le symbole étoile (*) pour symboliser la seconde plage conditionnelle à recouper,
• Ouvrir de nouveau une parenthèse,
• Sur la feuille Extraction, désigner la colonne des dates, soit par exemple : D3:D100,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : Extraction!$D$3:$D$100,
• Taper alors le critère suivant : =C7,
• Puis, enfoncer deux fois la touche F4 pour figer seulement la ligne, soit : C$7,

Les dates sont toutes situées sur la même ligne du planning. Il est donc nécessaire de figer cette dernière. Mais pour chaque colonne, elles varient. Pour établir la correspondance, nous devons donc libérer cet indice.

• Fermer la parenthèse de la seconde matrice conditionnelle,
• Puis, saisir un point-virgule suivi du chiffre zéro, soit : ;0, pour une correspondance exacte,
• Fermer la parenthèse de la fonction Equiv,
• Taper alors un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,
• Saisir le chiffre 1 pour indiquer l'emplacement de l'extraction,

En effet et comme précédemment, nous avons indiqué un tableau de recherche d'une seule rangée en premier argument de la fonction Index.

• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule pour passer dans le second argument de la fonction SiErreur,
• Saisir deux guillemets ('') pour conserver la case vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

Comme vous pouvez le voir, le premier résultat tombe. La réservation de la salle pour la première heure du premier jour de la semaine a parfaitement été importée.

• Cliquer et glisser la poignée du résultat à l'horizontale jusqu'en colonne G,
• En bas à droite de la sélection, cliquer sur l'icône carrée de la balise active qui se propose,
• Dans la liste, choisir Recopier les valeurs sans la mise en forme,

Cette technique est surtout importante pour la réplication à suivre à la verticale, du fait de la présence des lignes alternées.

• Cliquer et glisser la poignée de la sélection à la verticale jusqu'en ligne 18,
• Cliquer sur l'icône de la balise active et choisir Recopier les valeurs sans la mise en forme,

Toutes les réservations sont parfaitement importées comme vous pouvez le constater. Si vous modifiez l'année, le calendrier est totalement purgé du fait de l'absence de correspondances recoupées.

Si pour l'année 2019, vous choisissez la semaine 37, le planning de réservation se met automatiquement à jour en corrélation avec les données inscrites dans la feuille Archives et extraites dans la feuille Extraction. La formule matricielle que nous avons construite est la suivante :

{=SIERREUR(INDEX(Extraction!$F$3:$F$100; EQUIV(1; (Extraction!$E$3:$E$100 = $B8)*(Extraction!$D$3:$D$100 = C$7);0);1);'')}

Les crochets encadrent la syntaxe du fait de la validation par ce raccourci clavier particulier. Ils indiquent qu'il s'agit d'une formule matricielle. Les lignes des matrices sont étudiées et recoupées ensemble.

Mise en valeur des jours réservés
Pour parachever l'outil, nous proposons de réaliser un réglage de mise en forme conditionnelle afin de faire ressortir les réservations sur un fond légèrement hachuré. Toutes les données du calendrier doivent préalablement être sélectionnées.

• Sur la feuille Planning, sélectionner la plage de cellules C8:G18,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans 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 juste en-dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Sélectionner la première case du calendrier, soit la cellule C8,
• Enfoncer trois fois la touche F4 du clavier pour la libérer complètement,

En effet, c'est l'analyse du contenu de chaque cellule qui doit être passé en revue à la recherche d'une réservation à mettre en valeur.

• Taper alors le critère suivant : <> '' ,

Nous cherchons donc à repérer chaque case dont le contenu n'est pas vide, donc chaque cellule portant une réservation de salle.

• Cliquer ensuite sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Avec la première liste déroulante, choisir un gris moyen,
• A l'aide de la seconde liste déroulante, choisir le motif à points,

Avec une teinte allégée, ce remplissage ne confondra pas le texte au premier plan.

• Cliquer sur le bouton Ok de la boîte de dialogue,
• De retour sur la première boîte de dialogue, cliquer de nouveau sur Ok pour valider la règle,

De retour sur la feuille, vous notez que les réservations sont mises en valeur, certes de façon subtile.

Nous avons donc bâti l'application de réservation des salles de réunion uniquement par les formules Excel. Cependant et en l'état, chaque nouvelle réservation doit être inscrite manuellement dans la feuille Archives. Nous pourrions certes prévoir des outils avec des listes déroulantes pour simplifier l'implémentation. Mais la solution ne serait pas pleinement satisfaisante.

Pour plus d'ergonomie, nous proposons de permettre cette inscription directement depuis le planning de réservation, à l'aide du code VBA. Ce développement fera l'objet d'une prochaine formation.