Microsoft 365 Planning de réservation de matériel (avec gestion des stocks) - Formules utiles ?

[Tytoyo]

Bonjour,

J'ai parcouru pas mal le site et j'en profite pour remercier tous les experts en herbes ou aguerris que vous êtes pour toutes ces précieuses informations.
Je n'ai néanmoins pu trouvé de solution répondant à tous mes critères...

Je me permets donc de créer ce post à la recherche des formules "magiques" pour solutionner mes soucis et interrogations.
(Je compte m'initier aux macros prochainement mais, pour le moment je pense que les formules peuvent être efficaces pour tous ces questionnements).

Contexte :
Afin de faciliter l'organisation des réservations de matériels que nous proposons via notre association, je souhaiterais créer un fichier Excel qui soit, suffisament simple à utiliser / alimenter pour un utilisateur non initié mais fournissant néanmoins le max d'infos et surtout une vue globale de l'état des stocks selon la période de l'année.

Objectif :
Appliquer un code couleur en fonction du stock réservé (le plus foncé étant "Plus de stock disponible")
Mais cela, en fonction du nombre d'article réservé par commande et par date
Je dois donc prendre en compte les doublons de dates au sein des périodes de réservation (chevauchement) en tenant compte du type et du nombre d'articles de ces mêmes résas.
J'ai donc tenté via SOMMEPROD mais je ne parviens pas au résultat souhaité
Je pense que j'essai d'aller trop vite et grille des étapes... et je me permets donc de vous soumettre mon problème pour m'aider à clarifier tout ça.

Objectif bonus :
Au remplissage du tableau de résa, avoir une alerte si doublon de date fait que le stock n'est plus dispo.

Existant :
J'ai donc commencé à créer un fichier avec 4 onglets :

• Le 1er (Planning_2022') est un planning qui reprend toutes les infos. L'idée est juste d'avoir un aperçu global de toutes les résas du mois sans que l'utilisateur puisse modifier quoi que ce soit sur cet onglet - CONSULTATION
• Le 2nd ('Réservations') est l'onglet que l'utilisateur va alimenter via la création d'une nouvelle résa avec matériel réservé, date, nombre, tarifs et infos perso... - MODIFICATION
• Le 3ème ('Stocks & Tarifs') sert de table de référence. Il peut être mis à jour ou alimenter de nouvelles infos (nouveau matériel) - INFORMATIONS
• le 4ème ('Listes') répertorie simplement les listes de validation pour certains critères. Cela permets d'éviter des erreurs de saisies - ONGLET MASQUÉ

J'ai commencé à alimenter quelques résa fictives pour pouvoir vérifier un peu tous les scénarios (cf. colonne 'commentaires') mais si l'un d'entre vous peut déjà me confirmer que partir sur une SOMMEPROD est la bonne idée ?
Ensuite, dois-je créer une colonne résultat sur laquelle je me base pour mettre en forme l'onglet Planning ou puis-je directement l'intégrer dans une Mise en Forme Conditionnelle ?

Merci d'avance à ceux qui oseront s'y plonger.

Bonne journée à tous !

 

[vgendron]

Hello

j'ai regardé un peu ton fichier
et apporté quelques modifs
1) tous les tableaux sont maintenant des tableaux structurés avec des noms explicites "Tab_...")

2) ta formule à base de Sommeprod me semble très bien=> l'utilisation de table structurée modifie l'écriture de la formule, mais c'est la meme::> au lieu de faire référence à Debut, Fin, Matériel, elle fait référence directement aux noms des colonnes

3) ta MFC doit etre modifiée pour colorer toutes les réservations: exemple d'une résa sur deux créneaux "superposés") le sommeprod renvoie 2 (ou 3 ou plus)
il faut donc que la MFC soit avec "valeur>=1"

 

[vgendron]

Petite explication sur la formule

=SOMMEPROD((C$8>=Tab_Resa[[#Tout];[Date départ]:[Date départ]])*(C$8<=Tab_Resa[[#Tout];[Date retour]:[Date retour]])*($B10=Tab_Resa[[#Tout];[Matériel]:[Matériel]]))

tu avais

=SOMMEPROD((C$8>=debut)*(C$8<=fin)*($B10=materiel))

Debut, Fin et matériel sont définies de manière "statique" dans le gestionnaire de noms
==> cad qu'à chaque fois que tu ajoutes des lignes dans le tableau, il faut ajuster les plages debut, fin matériel

à moins de les définir de façon dynamique avec une fonction "Decaler"

mais comme on utilise une table structurée qui a pour principal avantage de s'adapter automatiquement aux données (avec recopie des formules si il y en ), autant utiliser les références associées

ce qui dans un premier temps donnerait une formule telle que:

=SOMMEPROD((C$8>=Tab_Resa[[#Tout];[Date départ]])*(C$8<=Tab_Resa[[#Tout];[Date retour]])*($B10=Tab_Resa[[#Tout];[Matériel]]))

Sauf que quand tu tires cette formule vers la droite, et bah.. la formule s'adapte et change de colonne,==> ce n'est pas ce qu'on veut : on souhaite que les colonnes soit des références absolues (avec un $)
THE probleme avec les tables structurées.. le $ n'existe pas.et pour fixer une colonne.. il faut "doubler" la référence
[Date départ] devient [Date départ]:[Date départ]

ce qui alourdit la formule.. et quand on connait pas. bah.. c'est le bordel.. et c'est illisible..
et oui. les gars d'excel ont de bonnes idées (table structurée).. mais ils oublient des notions de base..(formule illisible)

 

[chris]

Bonjour à tous

Une autre approche

Tableaux structurés pour Réservations et Stocks et construction de planning par PowerQuery (intégré à Excel) avec les MFC

Il suffit d'actualiser quand les réservations changent

J’ai supprimé la liaison et fait le ménage dans les noms

EDIT : j'ai fait une version légèrement modifiée qui récupère bien les % en numérique.
Cela ne change rien mais cela me paraît plus cohérent
Mais le demandeur n'étant pas revenu , je garde sous le coude

 

[Tytoyo]

Merci à tous les 2 pour ces approches et surtout, pour vos précisions !!
Désolé pour ma faible réactivité, je n'ai pas encore eu le temps de m'y replonger mais j'analyse ça à tête reposée ...quand j'aurais un moment.
En tout cas, vos explications me donnent vraiment envie de m'y pencher (et de me perfectionner).
Je vous tiens au courant, merci encore !

 

[Tytoyo]

Bonjour à tous

Une autre approche

Tableaux structurés pour Réservations et Stocks et construction de planning par PowerQuery (intégré à Excel) avec les MFC

Il suffit d'actualiser quand les réservations changent

J’ai supprimé la liaison et fait le ménage dans les noms

EDIT : j'ai fait une version légèrement modifiée qui récupère bien les % en numérique.
Cela ne change rien mais cela me paraît plus cohérent
Mais le demandeur n'étant pas revenu , je garde sous le coude

"PowerQuery existe depuis plus de 10 ans, est totalement intégré à Excel 2016 & +. Utilisez-le !" Effectivement le nom me parle mais je pense ne jamais encore l'avoir utilisé ... j'y remédie rapidement !!

 

[bugg]

Bonsoir,
Votre planning m'intéresse beaucoup, car notre asso prête des bancs et tables, et ça nous aiderait bien.
Quelle feuille faut-il modifier pour mettre le planning de 2023, sans tout casser, et quelle est la meilleure méthode
Merci d'avance pour votre aide !

 

[vgendron]

Hello Bugg
Voir en PJ le fichier que j'ai légèrement modifié avec des formules pour "construire" le planning de l'année choisie dans la cellule en jaune en haut

si tu as d'autres questions, je te suggère de créer ton propre post