Création outil optimisation
Bonjour à tous,
Je sollicite votre aide car je travaille actuellement sur la création d'un outil et je bloque ...
Pour que vous puissiez m'aider, je vous ai créé un fichier dans lequel vous trouverez (dans l'onglet BDD) un premier tableau dans lequel se trouve une liste de contenants avec leurs ID respectifs, leur capacité en litres, le coût de leur location mensuelle et une matrice contenant/ville avec un 1 si le contenant x est disponible pour la ville y ou un 0 s'il ne l'est pas.
Dans le deuxième tableau, vous trouverez une liste de liquides, leur coût de traitement en fonction des différentes villes (certains ont le même prix quelque soit la ville) et une matrice liquide/contenant indiquant de la même manière que dans le tableau précédent si le liquide x est compatible avec le contenant y.
Et enfin, un dernier tableau dans lequel on trouve en fonction du type de liquide, de la ville et du nombre de contenant enlevé un coût d'enlèvement par contenant (le prix unitaire est applicable à tous les contenants à partir du moment où l'on se trouve dans la tranche concernée).
Ce que je souhaiterais svp, c'est que je puisse entrer, dans l'onglet outil, une ville, des types de liquides et leurs volumes et qu'Excel me calcule la solution optimale (celle qui génère le coût le plus faible) donc qu'il me dise le nombre et le type de contenants dont j'aurai besoin et le nombre de rotations que je devrai faire en tenant compte bien sûr de tous les paramètres (coût de location et de rotation des contenants, coût de traitement des liquides et qu'il tienne compte des compatibilités entre contenants/villes et contenants/liquides).
Si une solution qui ne nécessite pas de VBA existe alors ce serait top car je ne suis pas à l'aise avec VBA et donc j'aurai des difficultés à ajuster par la suite mon outil.
J'espère avoir été le plus clair possible, si ce n'est pas le cas, n'hésitez pas à me solliciter !
Je vous remercie pour votre aide.
Bonjour,
1) Peux-tu m'éclairer sur la manière de calculer les coûts totaux pour ces différentes configurations :
Pour Evian à Nantes 100 L
80 litres en cuve 80L et 2 x 10 litres en bidons de 10 L
ou
100 litres en bouteilles de 1 L
2) Comment intervient la dimension temps dans ce calcul pour la location par mois ?
3) Comment déterminer le nombre de rotations ?
bonjour tyler_durden, h2so4,
just pour savoir si j'ai tout bien compris. Cette solution se calcule correcte ? Donc 20 B1's pour le multifruit, 2 C1's pour l'evian et 65 A6's pour le Schweppes. Les coûts pour location et le traitement, okay, mais 7.000€ pour les coûts d'enlèvement ??? Pour gazeuse 7à10 = 84€, pour 65 unités 65*84€ ?
Bonsoir h2so4,
Merci pour ta réponse,
Pour le premier point, c'est justement le choix que j'aimerai que Excel fasse en calculant toutes les possibilités comme tu l'as fait de par tes deux exemples et qu'il me propose celle qui sera la moins coûteuse.
Idem pour le nombre de rotations, admettons qu'il ait retenu un contenant de 20 litres et qu'il y ait 40 litres de liquide alors il faudra deux rotations et j'aimerai qu'il prenne aussi cela en compte dans son calcul afin de choisir la solution adéquate entre un petit contenant avec plus de rotations ou un grand avec moins de rotation.
En ce qui concerne le facteur temps en revanche je me suis posé la même question et je n'arrive pas à savoir comment je pourrai faire pour intégrer ce paramètre, peut-être une autre table qui indiquerait pour une combinaison contenant/liquide, un temps moyen de remplissage ? Mais je préfère ignorer ce facteur pour l'instant car s'il est possible d'avoir une première version de l'outil sans prendre en compte ce paramètre ce serait déjà top. Par la suite j'essaierai de voir un moyen d'intégrer ce problème.
En espérant que j'ai pu te répondre.
Bonsoir BsAlv,
Merci pour ta réponse,
Ne t'en fais pas si les montants paraissent incohérents, j'ai modifiés les prix pour pouvoir vous joindre un fichier sans données réelles. Tu as trouvé ces résultats en calculant manuellement ?
re,
pour le moment, c'est un modèle "Solveur" mais avec des corrections manuelles. On aura certainement besoin de VBA. Mais pour le moment, c'est encore un prototype. Je ne comprends pas encore complètement la situation.
à peu près la même question que h2so4, ces rotations, si pour un produit, on choissit 7 rotations, un autro produit peut avoir un autre nombre ou un multiple ou c'est un chiffre pour tous les produits ?
Le résultat ici dessous, cela vous semble réaliste ?
Bonsoir à tous,
Pour le premier point, c'est justement le choix que j'aimerai que Excel fasse en calculant toutes les possibilités comme tu l'as fait de par tes deux exemples et qu'il me propose celle qui sera la moins coûteuse.
ici il n'y a pas de choix qui soit fait, je cherche juste à comprendre comment calculer les coûts. On a besoin de connaitre la façon correcte de calculer les coûts pour choisir la solution optimale. Donc si tu pouvais donner les étapes du calcul pour ces 2 cas, ça m'aiderait à comprendre.
Re BsAlv,
Merci beaucoup pour ce fichier, il est top et me permet déjà d'avoir une idée de la mise en forme nécessaire, je pense en plus que les résultats sont les bons donc c'est top merci beaucoup. Juste pour comprendre, à quoi est-ce que ce tableau te sert stp ?
+ à quelle niveau est-ce que tes corrections manuelles interviennent et à quel problème répondent-elles ?
Merci beaucoup pour ton aide.
Bonjour h2so4,
Excuse-moi je n'avais pas compris ta question,
Pour le premier cas alors il y aura le coût de location pour les contenants en question, c'est-à-dire 50 pour la cuve + 2*10 pour les bidons.
Ensuite viendra le coût de traitement donc 100(nombre de litres)*0.9(coût du litre)
Et enfin le coût d'enlèvement qui sera ici de 85*3(le nombre de contenants).
L'idée serait que l'outil fasse ce travail pour toutes les possibilités qui existent (ton deuxième cas devra être testé) et qu'à l'issu de ces calculs, la solution la plus avantageuse ressorte.
J'espère avoir pu répondre à ta question cette fois,
Merci pour ton aide.
bonjour,
merci pour ta réponse.
Et enfin le coût d'enlèvement qui sera ici de 85*3(le nombre de contenants).
donc si on fait le calcul avec 100 bouteilles de 1 L ce sera 70*10 ? J'avais du mal à concevoir que pour transporter 100 bouteilles de 1 L il fallait faire 10 rotations.
rebonjour,
une proposition sur base de ma compréhension du problème, utilise le solveur tout comme dans la proposition de Bsalv
re; et salut h2so4,
ce sont tous des tableaux maintenant, plus facile à manipuler
Dans la feuille "outil", vous choississez vos villes et boissons et puis dans la feuille "Solveur" vous poussez le bouton.
Il faut ajouter la référence vers "solver" dans la liste des références de l'éditeur VBA.
Si vous demandez 749 litres et on prend par exemple un contenant de 1.000 litres & 1 rotation comme meilleure solution, le coût de traitement, c'est alors sur 749 litres ou sur 1.000 litres ?
PS. Solveur est un Add-in d'excel (https://support.microsoft.com/fr-fr/office/d%C3%A9finir-et-r%C3%A9soudre-un-probl%C3%A8me-%C3%A0-l-a...)
h2so4, BsAlv, bonjour,
Un grand merci à vous deux pour votre aide !
Re BsAlv,
Dans ton cas, le coût de traitement sera alors sur les 749 litres.
Je ne peux pas pour l'instant pleinement me plonger dans ton fichier mais je pense le faire la semaine prochaine, ça ne te dérange pas si je reviens vers toi à ce moment car je pense que j'aurai des interrogations notamment sur ce que tu as fais en VBA ?
Bonjour BsAlv,
petite question,
J'ai du mal à comprendre à quoi servent ces deux tableaux :
Ils ressemblent à des matrices de compatibilité mais je retrouve pas les mêmes valeurs en les comparant aux deux matrices de l'onglet BDD.
Aussi, pourquoi certaines cellules sont à 1000 et d'autres à 1 ?
Et pourquoi est-ce qu'on trouve des valeurs isolées tout en haut de la page ?
Merci à toi.
Re BsAlv,
En fait j'ai l'impression que c'est sur la deuxième ligne que tu utilises le solveur et que c'est ensuite grâce à ta macro que tu fais pour toutes les autres lignes ce que le solveur t'a permis de faire sur la deuxième. Est-ce que c'est à peu près ça ?
re,
oui, je copie & colle ligne par ligne du tableau dans "Outil" vers la ligne 2 de "Solveur" et j'ajoute "gazeux/plat" + le prix de traitement.
F2:P2 = copie & colle des 0 et 1 à droite du tableau (à partir de la colonne AA) dans "BDD"
Q1:AA1 = avec d formules on recherche les 0/1 dans le tableau (à partire de B2) dans "BDD"
Q2:AA2 = produit de F2:P2 et Q1:AA1 et 1000 (effectivement il y avait une erreur dans les premières cellules de cette plage, faute dans la macro). On suppose que 1000 est le max de contenants nécessaire pour cette commande (donc valeur exagérée).
Puis AB2:AL2, ce sont les cellules que solveur utilise pour cette optimalisation. (ce sont des valeurs integer)
AM2 = somme du nombre de contenants que solveur a choissi.
AN2 = vous ne le voyez pas, mais dans un boucle le nombre de rotations est changé de 1 à 10 et on mémorise la rotation avec le meilleur résultat de AT2.
AP2:AU2 sont des formules.
Quand solveur a choissi la meilleure solution des rotations toute la ligne 2 est copie et collé dans le tableau à partir de la ligne
excusez-moi,
il y a un bug avec solveur, il met la calculation à manuel et puis se trompe.
Donc ignorez cette solution pour le moment
Okay merci pour tes explications