Microsoft 365 Tableau de suivi de mise à disposition de matériel

[LAETI-TOINOU]

Bonjour à toutes et à tous,

Je vous présente, en tout premier lieux, tous mes meilleurs voeux en ce début de nouvelle année.

Je reviens une nouvelle fois vers vous pour que vous m'aidiez à mettre au point un tableau de suivi de mise à disposition de matériel de réception.

En effet, présidente d'une asso, j'aimerais pouvoir suivre les demandes de mises à dispo de notre matériel.

A cet effet, j'ai commencé un tableau en m'inspirant grandement pour la partie date, de la structure qu'Alain m'a mis en forme sur un précédent fil de discussion.

J'aimerais sur ce tableau du 1er au dernier jour de l'année (joint en PJ) pouvoir avoir :

les samedis et dimanches en vert
les jours fériés en rouge
le nom de l'emprunteur et ses coordonnées,
la liste du matériel mis à dispo,
le matériel restant encore disponible (ça aurait pu être facile, si tout le matériel n'était qu'en une seule unité),
les samedis et dimanches en vert
les jours fériés en rouge

Et c'est surtout sur ce dernier point que je sèche, surtout que je ne sais pas utiliser les fonctions VBA.

Je souhaiterais aussi que mon tableau soit évolutif, tant dans le temps (dans ce cas, je sauvegarde un fichier / année et ça me va bien), et que je puisse ajouter ou retirer du matériel si besoin, tout en évitant les fonctions VBA, de manière à ce que je puisse être autonome, une fois le fichier construit.

Merci d'avance pour tout l'intérêt et le temps que vous voudrez bien accorder à ma demande.


Laëtitia

 

[JM27]

Bonsoir
A tester
je ne sais pas si cela peut faire l'affaire ( en adaptant à ton cas : c'est possible)

 

[Hasco]

Bonjour,

voici qui est fait pour votre mise en forme conditionnelle sur les Fériés.
Les MFC sont cumulatives dans leur ordre d'apparition dans la fenêtre du gestionnaire.
Vous pouvez les faire monter ou descendre avec les flèches à gauche de 'supprimer la règle'
Servez vous des cases à cocher 'interrompre si vrai'. Excel s'arrêtera d'appliquer les règles à la première cochée si la condition (vrai) s'applique à la valeur de la cellule en cours de test.
Par exemple votre règle des mfc des samedis et dimanche est en conflit avec la règle des Fériés.
Laquelle doit avoir la priorité ? (une cellule ne peut avoir un fond vert et un fond rouge en même temps)
Si c'est la règle des week-end, montez là et cochez la case 'interrompre si vrai'.

Cordialement

 

[LAETI-TOINOU]

Bonsoir
A tester
je ne sais pas si cela peut faire l'affaire ( en adaptant à ton cas : c'est possible)

Bonsoir Jean-Marcel,

Merci pour ton message,

L'idée de base est bonne, par contre, comment planifier une sortie de matériel, dans les jours à venir ? J'ai compris, en parcours le fichier, que la sortie se faisait au moment de la saisie, et comment ajouter un numéro de téléphone par exemple ?

 

[LAETI-TOINOU]

Bonjour,

voici qui est fait pour votre mise en forme conditionnelle sur les Fériés.
Les MFC sont cumulatives dans leur ordre d'apparition dans la fenêtre du gestionnaire.
Vous pouvez les faire monter ou descendre avec les flèches à gauche de 'supprimer la règle'
Servez vous des cases à cocher 'interrompre si vrai'. Excel s'arrêtera d'appliquer les règles à la première cochée si la condition (vrai) s'applique à la valeur de la cellule en cours de test.
Par exemple votre règle des mfc des samedis et dimanche est en conflit avec la règle des Fériés.
Laquelle doit avoir la priorité ? (une cellule ne peut avoir un fond vert et un fond rouge en même temps)
Si c'est la règle des week-end, montez là et cochez la case 'interrompre si vrai'.
Regarde la pièce jointe 1126410

Cordialement

Super, merci, c'est déjà un point de réglé. J'avoue que je ne connaissais pas ces subtilités.

 

[AtTheOne]

Bonsoir Laëtitia (@LAETI-TOINOU ), bonsoir à tous

Comme tu préfères éviter le VBA, je ne suis pas parti de la proposition de Jean-Marcel (@JM27).

Je me suis dit : Quelqu'un vient te voir pour réserver des tables et des bancs pour une période à venir.
Tu regardes ton planning sur l'onglet "Suivi" pour voir s'il y a du matériel disponible à ces dates.
Si non "désolée je ne peux rien pour vous, peut-être pour une autre période ?" (tu as les disponibilités sous les yeux)
Si oui, tu fais une réservation, là, tu vas sur l'onglet "BdD prêts" et tu enregistres 2 lignes, une pour les tables, une pour les bancs avec les qtés demandées.
Ton planning est automatiquement mis à jour (Formules matricielles dynamiques)

Le planning affiche, jour par jour et matériel par matériel les Qtés disponibles, un format conditionnel met en blanc sur fond noir les Qtés nulles (les indisponibilités)

J'ai revu les mises en forme conditionnelles, il y avait des petits écarts lorsque tu utilisais des formules avec adresses relatives : c’est un peu piégeux car ta formule dépend alors de la cellule active au moment où tu la tapes.

Le planning s'adapte à la liste du matériel que tu peux faire évoluer (en plus et en moins), mais j'ai collé un peu de VBA pour adapter la plage de mise en forme conditionnelle : fais un clic droit sur l'onglet "Suivi" et choisis "Visualiser le code". Si tu n'en veux pas supprime le, lorsque tu ajouteras ou supprimeras du matériel il faudra réajuster la plage de définition des formats conditionnels.

Private Sub Worksheet_Change(ByVal Target As Range)
   
   [B][COLOR=rgb(97, 189, 109)][B] 'Target est la plage qui a initié l'événement Change de la feuille[/B][/COLOR][/B]
   
'Seulement s'il s'agit d'une action sur le tableau
     If Intersect(Target, Me.[_Tb_Mat].EntireColumn) Is Nothing Or Target.Row < Me.[_Tb_Mat].Row Then Exit Sub
   
     Application.ScreenUpdating = False                               'Désactiver la mise à jour de l'affichage
     Application.EnableEvents = False                                    'Désactiver le gestionnaire d'événement
   
     With Me.[_Planning#]
   
          'Effacer les formats sous la 1ère ligne du planning
          .Offset(1).Resize(Me.Rows.Count - .Row).ClearFormats
          'Effacer les formats à gauche de la 1ère cellule du planning
          .Offset(0, 1).Resize(, .Columns.Count - 1).ClearFormats
          'Copier le format de la 1ère cellule du planning
          .Cells(1).Copy
          'Coller ce format sur l'ensemble du planning
          .PasteSpecial Paste:=xlPasteFormats
          'Desactiver le copiage
          Application.CutCopyMode = False

     End With
     'Retour sur la cellule qui a déclencher l'événement
     Application.Goto Target
   
     Application.EnableEvents = True                                     'Réactiver le gestionnaire d'événement
     Application.ScreenUpdating = True                                'Réactiver la mise à jour de l'affichage

End Sub

J'ai créé un onglet mémo qui détaille les principales formules utilisées.

Voilà, dis moi si ça te convient et si tu as des questions sur le fonctionnement.

Amicalement
Alain

 

[LAETI-TOINOU]

Bonsoir Laëtitia (@LAETI-TOINOU ), bonsoir à tous

Comme tu préfères éviter le VBA, je ne suis pas parti de la proposition de Jean-Marcel (@JM27).

Je me suis dit : Quelqu'un vient te voir pour réserver des tables et des bancs pour une période à venir.
Tu regardes ton planning sur l'onglet "Suivi" pour voir s'il y a du matériel disponible à ces dates.
Si non "désolée je ne peux rien pour vous, peut-être pour une autre période ?" (tu as les disponibilités sous les yeux)
Si oui, tu fais une réservation, là, tu vas sur l'onglet "BdD prêts" et tu enregistres 2 lignes, une pour les tables, une pour les bancs avec les qtés demandées.
Ton planning est automatiquement mis à jour (Formules matricielles dynamiques)

Le planning affiche, jour par jour et matériel par matériel les Qtés disponibles, un format conditionnel met en blanc sur fond noir les Qtés nulles (les indisponibilités)

J'ai revu les mises en forme conditionnelles, il y avait des petits écarts lorsque tu utilisais des formules avec adresses relatives : c’est un peu piégeux car ta formule dépend alors de la cellule active au moment où tu la tapes.

Le planning s'adapte à la liste du matériel que tu peux faire évoluer (en plus et en moins), mais j'ai collé un peu de VBA pour adapter la plage de mise en forme conditionnelle : fais un clic droit sur l'onglet "Suivi" et choisis "Visualiser le code". Si tu n'en veux pas supprime le, lorsque tu ajouteras ou supprimeras du matériel il faudra réajuster la plage de définition des formats conditionnels.

Private Sub Worksheet_Change(ByVal Target As Range)
  
   [B][COLOR=rgb(97, 189, 109)][B] 'Target est la plage qui a initié l'événement Change de la feuille[/B][/COLOR][/B]
  
'Seulement s'il s'agit d'une action sur le tableau
     If Intersect(Target, Me.[_Tb_Mat].EntireColumn) Is Nothing Or Target.Row < Me.[_Tb_Mat].Row Then Exit Sub
  
     Application.ScreenUpdating = False                               'Désactiver la mise à jour de l'affichage
     Application.EnableEvents = False                                    'Désactiver le gestionnaire d'événement
  
     With Me.[_Planning#]
  
          'Effacer les formats sous la 1ère ligne du planning
          .Offset(1).Resize(Me.Rows.Count - .Row).ClearFormats
          'Effacer les formats à gauche de la 1ère cellule du planning
          .Offset(0, 1).Resize(, .Columns.Count - 1).ClearFormats
          'Copier le format de la 1ère cellule du planning
          .Cells(1).Copy
          'Coller ce format sur l'ensemble du planning
          .PasteSpecial Paste:=xlPasteFormats
          'Desactiver le copiage
          Application.CutCopyMode = False

     End With
     'Retour sur la cellule qui a déclencher l'événement
     Application.Goto Target
  
     Application.EnableEvents = True                                     'Réactiver le gestionnaire d'événement
     Application.ScreenUpdating = True                                'Réactiver la mise à jour de l'affichage

End Sub

J'ai créé un onglet mémo qui détaille les principales formules utilisées.

Voilà, dis moi si ça te convient et si tu as des questions sur le fonctionnement.

Amicalement
Alain

Vraiment merci Alain, tu es génial.

Tu as eu le bon raisonnement, quant à la présentation du document. Il est effectivement plus facile de savoir ce qui reste, plutôt que ce qui est déjà réservé. J'avoue que je n'avais pas eu cette logique, qui est nettement plus pratique.

Ton planning est automatiquement mis à jour (Formules matricielles dynamiques)

Je ne sais pas ce que c'est, en langage plus simple, ça correspond à quoi ?

Sinon, concernant :

L'onglet Mémo
-> C'est vraiment super

L'onglet Tables
-> Je n'ai fait que reprendre ce que tu m'avais proposé sur mon précédent sujet. Donc nickel

L'onglet Tb prêts
-> Je n'ai pas compris à quelle condition la ligne passait en ORANGE.
-> C'est la colonne RENTRE qui met à jour le tableau de suivi ?
Si le matériel est réservé, il faut indiquer FAUX et le planning diminue d'autant le stock, et inversement si on indique VRAI ?
-> Est-il possible de mettre un visuel, si par exemple je réserve 5 tables tables pour Monsieur XXX, alors qu'il n'en reste pas autant ?

L'onglet Suivi
-> Peut-on mettre un petit visuel comme pour les semaines avec la fonction AUJOURD'HUI, en ligne 8, par exemple, ou m'expliquer comment faire ?
-> Colonne Equipement et quantité, si j'ai bien compris, je n'aurais qu'à ajouter / supprimer des matériel ou modifier des quantité -> Et surtout penser à modifier mes validations de données dans l'onglet Tb prêts

Sinon, j'adhèèèèèère.

Petite précision, ce document est-il compatible avec une version antérieure d'EXCEL, car je suis sur la version 365, mais si je ne suis certaine que la 2ème personne qui serait amenée à suivre le matériel soit sous cette version ?

Vraiment du bon boulot.

Merci Alain.

Bonne soirée.


Laëtitia

 

[AtTheOne]

e-bonsoir Laëtitia (@LAETI-TOINOU )
Merci de ta réponse, ça motive !

L'onglet Tb prêts
-> Je n'ai pas compris à quelle condition la ligne passait en ORANGE.

J'ai rebaptisé cet onglet "BdD Prêts", j'ai enlevé les "FAUX" et limité la saisie possible à "VRAI" pour indiquer que le matériel est rentré.
Si la case reste vide et que la date de retour attendue est dépassée ( < AUJOURDHUI() ) la ligne passe à l'orange (un petit coup de Tél pour savoir ce qui se passe peut-être ...). Quand le matériel rentre il faut sélectionner "VRAI" pour éviter l'alerte

-> C'est la colonne RENTRE qui met à jour le tableau de suivi ?

Non, comme expliqué ci-dessus c'est pour avoir une alerte en cas de retard pour le retour (regarde la formule du Format conditionnel).
Le planning de suivi se met à jour quand tu renseignes le matériel emprunté, la Qté, et les deux dates. il n'y a pas de macro, c'est la formule du planning qui fait le boulot.

Je ne sais pas ce que c'est, en langage plus simple, ça correspond à quoi ?

Les fonctions matricielles dynamiques sont des fonctions qui occupent plus d'une cellule et qui s’ajustent automatiquement. La formule est unique, saisie dans le coin supérieur Gauche de la zone que va occuper son résultat, et ce résultat s'étend ou se réduit en fonction du calcul effectuée par la formule.
Par exemple pour la liste des dates, la formule est en D11 (baptisée _J0) est le résultat s'étend en fonction du nombre de mois choisi.

-> Est-il possible de mettre un visuel, si par exemple je réserve 5 tables tables pour Monsieur XXX, alors qu'il n'en reste pas autant ?

Heu ... tu fais ça toi ? Je ne sais pas il faut y réfléchir, et savoir où placer ce signal.

-> Peut-on mettre un petit visuel comme pour les semaines avec la fonction AUJOURD'HUI, en ligne 8, par exemple, ou m'expliquer comment faire ?

Oui avec un format conditionnel, j'explique :

• Dans la zone de nom tape _J0# (la plage contenant les dates), ne change pas la sélection

• Accueil, Mise en forme conditionnelle, gérer les règles, nouvelle règle, avec une formule, dans la formule tape =D$11=AUJOURDHUI(), choisis le format à ton goût, assure-toi que cette règle se place en premier dans la liste des formats pour cette sélection (avant les jours fériés et les week-ends
• N'oublie pas de valider
  
  • ​
• Dans la zone de nom tape _Planning# (la plage contenant les disponibilités)
• Même démarche que pour _J0# , mais en plaçant la règle en 2ème position, sous la format texte blanc sur fond noir.

-> Colonne Equipement et quantité, si j'ai bien compris, je n'aurais qu'à ajouter / supprimer des matériel ou modifier des quantité -> Et surtout penser à modifier mes validations de données dans l'onglet Tb prêts

Si tu laisses la macro de de la feuille "Suivi" pas la peine de modifier les formats conditionnels, elle s'en charge lorsque qu'une modification intervient sous la ligne de titre du tableau (Equipement - Qté).
Pas besoin d'intervenir dans l'onglet BdD Prêts. les validations de données vont suivre directement tes modifications.

Petite précision, ce document est-il compatible avec une version antérieure d'EXCEL, car je suis sur la version 365, mais si je ne suis certaine que la 2ème personne qui serait amenée à suivre le matériel soit sous cette version ?

Oups ! Non car les fonctions matricielles dynamiques ne sont apparues qu'avec Excel 365 et 2021 (la version que j'utilise)
Pour faire un tel projet compatible avec des versions antérieures, il y a un sacré boulot en perspective.
J'ai acheté OFFICE PRO PLUS 2021 (5 installations) pour un prix très raisonnable sur le net.

Bon courage
Amicalement
Alain

 

[LAETI-TOINOU]

Bonjour Alain, bonjour tout le monde,

Mon document a sacrément avancé en quelques heures.

Alain, je n'en serais jamais arrivée à ce stade sans ta précieuse aide.

Merci de ta réponse, ça motive !

Je crois que je suis plus motivée que toi, grâce à ta collaboration.

Oui avec un format conditionnel, j'explique :

• Dans la zone de nom tape _J0# (la plage contenant les dates), ne change pas la sélection

Ca a fonctionné.

Si je comprends bien :
_JO correspond à une cellule
_JO# à une sélection démarrant depuis la cellule nommée _JO. Par contre, le nom avec le # se fait tout seul ou est-ce toi qui le définis, car je ne sais pas le retrouver en regardant dans tous les noms que tu as donnés aux cellules ?

Comment fais-tu pour mettre ces flèches ?
Car je trouve cette présentation excellente pour expliquer une formule.

Oups ! Non car les fonctions matricielles dynamiques ne sont apparues qu'avec Excel 365 et 2021 (la version que j'utilise)
Pour faire un tel projet compatible avec des versions antérieures, il y a un sacré boulot en perspective.
J'ai acheté OFFICE PRO PLUS 2021 (5 installations) pour un prix très raisonnable sur le net.

Dans la mesure où j'ai un compte OFFICE 365 FAMILLE, je vais créer une adresse pour que mes collègues puissent accéder aux documents, quelle que soient leurs versions.

Merci beaucoup.

Laëtitia

 

[erwinus_lupulus]

Bonjour à toutes et tous,

Merci pour tout ce qui a déjà été fait sur ce fichier dont je me suis inspiré.

J'aurais aimé savoir s'il était possible dans mon cas (1 seule unité par matériel) d'avoir un message d'alerte quand on essaie de réserver alors que le matériel n'est pas disponible?

Pour l'instant, on peut aller en stock négatif ce qui n'a aucun intérêt.

Merci d'avance !

Belle journée !

 

[AtTheOne]

Bonsoir à toutes & à tous,
bonsoir @erwinus_lupulus
Dans l'idée de départ, on me demande un matériel pour une période donnée, on regarde si pour la période voulue le matériel est disponible : il fallait afficher la période (par exemple mars et 2 mois), lorsque le matériel n'est pas disponible la valeur est à 0 et la case est rouge.
Cela ne suffit-il pas ?
Amicalement
Alain

Edit : autre remarque, dans l'idée de départ il n'y avait qu'une feuille de planning, on peut afficher la période que l'on veut (mois de début, nombre de mois)

 

[AtTheOne]

Bonne nuit à toutes & à tous,
Bonne nuit @erwinus_lupulus

Remarque : l'idéal aurait été que tu crées un nouveau fil pour que tu puisses le marquer comme résolu lorsque ce sera le cas, là seule Laëtitia (@LAETI-TOINOU ) pourras le faire.

J'ai essayé quand même de répondre à ta demande :

J'ai repris ton fichier, mais avec une seule feuille de suivi : choisir le mois pour un aperçu des emprunts pour ce mois, plutôt que de multiplier les feuilles, les tableaux, les formules et les mises en forme conditionnelles.

J'ai ajouté au tableau de la feuille "BdD Prêts" une colonne Alerte dans la laquelle s'affiche "Matériel déjà sorti !" si 2 périodes de prêt pour un même matériel se chevauchent (affichage dans les 2 lignes concernées lorsque l'on a renseigné les dates de début et de fin).

J'ai modifié les MFC du planning de la feuille Suivi pour faire apparaître "!!!" sur fond noir lorsque deux périodes se chevauchent malgré l'alerte précédemment décrite.

J'ai modifié la formule du planning de Suivi pour tenir compte d'un retard dans le retour d'un équipement. Un format conditionnel Orange met en évidence cet équipement non rentré.

Voilà en espérant avoir répondu à ta demande.



Amicalement
Alain

 

[erwinus_lupulus]

Bonjour Alain,

Merci pour ta réponse! Je découvre le forum à vrai dire donc, comme tu dis, j'aurais dû créé un nouveau fil. Je l'ai fait hier mais sans penser à effacer celui-ci. Désolé!

En fait, le fait de pouvoir consulter le planning devrait en effet suffire à la plupart d'entre nous mais nous avons une équipe de commerciaux qui font des réservations au départ de leur smartphone quand ils sont en route et ils ont parfois un peu de mal à vérifier la disponibilité sur un écran de taille limitée. En plus, l'alerte permet d'éviter des erreurs qu'on ne verrait qu'à la prochaine consultation.

Merci beaucoup pour ton aide!

Bonne journée!

Erwin

 

[AtTheOne]

Bonjour à toutes & à tous
Bonjour @erwinus_lupulus
J'ai renvoyé ton autre fil (post2) vers celui-ci.
Amicalement
Alain

 

[erwinus_lupulus]

J'ai ajouté au tableau de la feuille "BdD Prêts" une colonne Alerte dans la laquelle s'affiche "Matériel déjà sorti !" si 2 périodes de prêt pour un même matériel se chevauchent (affichage dans les 2 lignes concernées lorsque l'on a renseigné les dates de début et de fin).

Re-bonjour Alain,

J'ai dû faire une fausse manœuvre! J'ai modifié la liste de matériel et l'alerte n'apparaît plus. Peux-tu me guider pour la remettre en place svp ?

Merci d'avance !

Cordialement,

Erwin