Optimisation production de cailloux
Bonjour à tous les professionnels Excel
Je vous sollicite aujourd’hui pour un soucis bien particulier !
Le contexte le voici :
Je travaille dans les granulats, parmit les granulats il y a ce qu’on appelle des coupures qui correspondent aux différentes dimensions de granulats (0/20= de 0 à 20mm, 20/40= de 20 à 40mm…). Ces coupures sont produites sur une installation de traitement grâce à des scalpeurs, des concasseurs… Il existe différentes manières de régler l’installation en fonction de ce que l’on veut produire (c’est ce que j’appelle « configuration »). Une configuration peut produire plusieurs coupures avec des débits bien spécifiques à chaque fois. Une même coupure peut être produite par différentes configurations. Il y a aussi ce qu’on appelle « sous-produits » ou « excédent », c’est ce qui est produit par l’installation mais qu’on ne veut pas.
Mon objectif est de trouver la combinaison de différentes configurations afin de :
- Répondre aux besoins (en tonne)
- Avoir le moins d’excédents (en tonne)
- Avoir le moins d’heure de fonctionnement (en fonction des débits)
Je vais moi-même paramétrer les différentes configurations (ce qu’elles produisent comme coupure, les débits associés pour chaque coupure…). Il peut en avoir une dizaine.
Mon idée de base est de, choisir une première configuration qui posséde le plus de coupure de production en commun avec ce qui est indiqué dans « besoin » ET qui possède le moins d’excédent (coupures autres que celles indiquées dans besoin). Une fois cette première configuration trouvée, je regarde le temps de fonctionnement nécessaire pour arriver à terme d’une coupure dans besoin. (Exemple dans fichier ci joint, le débit le plus élevé dans les paramètres de la config 6, c’est 0/25 avec un débit de 200 t/h, avec un besoin de 10 000 tonnes, il faut donc 50h de fonctionnement pour produire la totalité du besoin). On va donc avoir en premier lieu 50h de config 6. Le 0/25 n’étant pas la seule coupure produite dans cette configuration, en 50h et avec les débits il y a aura 1400 tonnes de 0/2, 1250 t de 4/6 et 1200 tonnes de 6/10 (parmi les coupures indiquées dans besoin) mais il y a aura aussi d’autres excédents (7500 tonnes de 0/180…).
Ensuite, une fois qu’une des coupures a été totalement produites, on remet à jour les besoins (reste à produire) en fonction de ce qui a été produit grâce à la config 6. Et on réitère la boucle afin de chercher la config qui correspond aux mieux aux besoins mis à jour « reste à produire » en reprenant les objectifs principaux (trouver la config avec le plus de coupures souhaitée et le moins d’excédent).
Et ce jusqu’à ce que les besoins arrivent à 0. On aura donc plusieurs configurations avec des heures de fonctionnements pour chacune !
J’espère que vous m’avez compris
Voici le fichier exemple ci-joint
Si vous avez d’autres idées n’hésitez pas !
Merci bcp vous ètes au top,
M
bonjour Mswin,
une solution avec l'Add in "Solveur"
Bonjour Bart,
Ca à l'air super, merci beaucoup!
Est ce que je peux avoir quelques explications ? Pour que j'essaye de comprendre
Oups, je viens de voir votre lien
J'aurais juste quelques questions:
- Pourquoi c'est la cellule E20 qui est l'objectif à définir
- J'ai bien compris que la contrainte indiquée permettait d'indiquer qu'on souhaité subvenir aux besoins
Mais quand est-il des contraintes de minimisation des excédents et de temps ?
Merci par avance
bonjour,
Pourquoi c'est la cellule E20 qui est l'objectif à définir
Lorsque tu ouvres le dialogue de configuration du solveur, la cellule "objectif" est la cellule active. tu peux la modifier, pour moi si on veut minimiser le nombre d'heures tout en produisant toutes les quantités demandées, il faut définir N19 comme cellule "objectif".
On peut imaginer une formule pour minimiser le nombre d'heures et la quantité d'excédents (ratio, somme ou produit, ...)
re,
comme h2so4 la dit, les 2 cellules cible sont N19 (min temps) et O19 (min excedent).
Quand on compare les 2 solutions, la solution "min temps" a 19.9 heures de moins, mais 3.794 ton en plus, donc une heure en plus = -191 ton en moins.
Solveur ne sait pas laquelle des 2 vous préférez, si vous voulez optimaliser les 2 en même temps, il faut ajouter une sorte de pondération pour avoir la même unité.
Bonjour,
Après on peut se poser aussi d'autres questions...
Par exemple : est-il opportun de minimiser le nombre d'heures et le surplus en faisant tourner 5 config alors que 2 y suffiraient, au prix il est vrai d'une certaine surproduction mais les cailloux ne sont pas des denrées périssables...
Cela dépend bien sur de la difficulté de mise en œuvre de chaque config... S'il suffit d'appuyer sur un bouton le solveur fera l'affaire, par contre si les changements de config sont non négligeables il convient de le préciser.
A+
Bart et H2SO4 , super merci j'ai bien tout compris!
Maintenant il me reste à trouver une façon, comme vous dites, de pondérer l'excédent et le temps afin de de déterminer une seule combinaison optimale. Je pensais convertir ces 2 paramètres en coûts pour essayer de trouver le juste milieux, peut être grâce à un 2ème solveur ?
Merci à vous 2 en tout cas !
rebonjour,
Je pensais convertir ces 2 paramètres en coûts pour essayer de trouver le juste milieux, peut être grâce à un 2ème solveur ?
non, avoir une formule qui détermine le cout total (heures, matières premières, excédents, ...) et utiliser le solveur pour minimiser ce cout total.
re,
il y a un alternative pour "Solveur", c'est "OpenSolver" qui est plus convivial, dispose de plus d'algorithmes et plus vite.
Il est aussi facile à combiner avec VBA
Donc cela dépend un petit peu du goût ....
Re,
C'est bon à savoir, je connais pas du tout Opensolveur, je vais me renseigner.
Il faut le télécharger?
Bonne journée
re,
oui, dans le lien de hier oubien https://opensolver.org/installing-opensolver/ tout est bien expliqué et puis tout est très similaire mais plus puissant. Un problème créé dans Solveur peut facilement être transféré vers OpenSolver (mais pas l'inverse).
Bonjour je reviens vers vous pour une demande particulière par rapport au fonctionnement du solveur.
Est-il possible d'identifier quelle est la coupure qui genère le plus d'excédent ?
Pour produire certaines coupures il est systématique de produire d'autres coupures secondaires. J'aimerais identifier (et même classer si possible) les coupures qui générent le plus de coupures secondaires. Est-ce possible ?
Merciii
re,
quand le solveur a fini, vous recevez un écran dans laquelle vous pouvez demander plusieurs rapports intéressants (par exemple sensitivity) mais ces rapports ne servent pas à resoudre votre question actuelle.
J'ai ajouté les lignes 28-30, pour les volumes de produits secondaires, produits utiles et total. Puis la ligne 32 = total / utile. Config5 avec son 400% veut dire qu'il y a 3 fois plus de produits secondaires que du produit utile. Mais solveur l'a choisi !!! S'on ignore config5, (vous changez la cellule orange I21 de 999 à 0 !!! et lancez le solveur), le résultat sera 269.9 heures (+33) et les produits secondaires 106.150 (+6.200), donc cette nouvelle solution, sans config5, est pire. Ici, le choix sera vite fait, le changement de config de votre installation, cela prend normallement combien de temps ? Un quart d'heure, une demi-heure, une heure (???) mais vous gagnez 33 heures et 6.200 ton, même avec ce rapport de 400%. Les pires configs sont encore les meilleurs des mauvaises solutions ...
Re,
Le changement de configuration est très rapide il suffit parfois juste d'ouvrir ou de fermer des vannes...
En effet le résultat est frappant
J'avais pensé à autre chose, je ne sais pas si c'est faisable. Serait-il possible de faire varier les besoins de 10% de + ou de - s'il s'avère que ça produise moins d'excédents ?
Faire en sorte que le solveur puisse ajuster entre ces +10% et -10% afin d'avoir le moins d'excédents ? J'en demande beaucoup je suis désolé
Merci vous êtes au top, j'en apprends tous les jours ici !!!
re,
Hier je parlais de plusieurs rapports après l'optimalisation. Donc, au moment où Solveur a trouvé une solution, vous recevez un écran dans lequel, à la côté droit, vous pouvez cocher 3 types de rapport. Vous cochez le 2ième "Sensitivité" (je pense, mon excel n'est pas francophone) et vous clicquez sur okay. Excel ajoutera une nouvelle feuille comme la mienne "Gevoeligheidsrapport 1". Les chiffres qu'on verra, ils sont valable à ce point, donc si vous changez quelque paramètres vous pouvez voir d'autres chiffres.
ligne 9-17 : sensitivité des configs
Cellule E11, le config 3 n'es pas choisi par solveur, pendant une heure, ce config produit 753 ton (cellule F11) ce qui est 204.7 ton de trop pour être choisi !!! Donc, comme exemple , je supprime ce 150 de G4 et 55 des 75 de G6 (75-55 = 20 dans G6), le config produira 548 ton par heure et avec ces chiffres, config3 entre la solution (et un ou plusieurs des autres configs changent aussi).
ligne 22-37 : sensitivité des produits
cellule E22 = 1.98 pour le produit "0/25", donc si vous ajoutez 1 ton à la demande de "0/25", vous aurez 1.98 ton produits en plus (1 ton 0/25 et 0.98 ton secondaire) et cela est valable dans l'intervalle 10.000-9.829 (H22) = 170 ton à 10.000+8.190 (G22) = 18.190 ton. Donc, ce n'est pas intéressant d'augmenter le produit "0/25".
Cellule E35 = 0.084 pour produit "6/10", si vous ajoutez 1 ton de "6/10" le tonnage n'augment qu'avec 0.084 ton (=génial) et cela est valable dans l'intervalle 6.000 (F35) - 5.205 (H35) = 795 ton à 6.000 (F35) + 8.971 (G35) =
Donc ma réponse à votre question, choississez les produit avec une valeur <1 dans la plage E22:E37, CàD "4/6" et "6/10" et certainement pas "0/60"
Maintenant, ce sont les configs 1-2-5-8-9 qui sont actives, si vous modifiez les quantités, ces configs restent active, mais leur partage mutuel changera. Si vous sortez de l'intervalle, un des configs sera supprimé et/ou un nouveau config sera ajouté.
(je n'ai pas vérifié tout ce que j'ai écrit ici dessus)
Bonjour,
J'avais essayé le rapport de sensibilité sans comprendre, c'est vrai que c'est formidable par contre je crois que je n'ai pas bien compris à quoi servent les marges sup et inf pour les cellules variables et les contraintes.
Pour les cellules variables j'ai bien compris que l'objectif coeff représente le débit. Si j'ai bien compris marge sup + obj coeff représente le débit maximal pour lequel une configuration pourra être choisie ? exemple: Si la config qui a été choisie à 250 de débit et que marge sup c'est 3, la config sera choisie si son débit est max de 253, au dela elle sera pas choisie ? Et même principe pour marge inf ?
Si oui j'ai essayé de faire varier les débits de config par rapport à ces marges et pourtant ma théorie est fausse.
Pour contraintes, même principe, j'imagine que la valeur marginal reste identique si contrainte à droite n'augmente que de "mar sup" ou diminue de "marge inf" ?
Dans ce cas la j'imagique que tant qu'on reste dans entre ces 2 marges au niveau des besoins en t, le choix des configurations restera identiques ? Seuls leurs temps variera ?
Beaucoup de questions désolé
En tout très intéressant ce solveur et merci !!!
re,
en PJ une simulation avec le produit 6/10 (=ligne verte dans "gevoeligheidsrapport") et comme je disais qu'entre les valeurs 795 et 14.792 pour ce produit tout change d'une manière linéaire. Si on sort de cette intervalle, il se passe quelque chose, un ou plusieurs configs entrent ou sortent la solution.
Bon, pour prouver cette théorie, avec une macro, on change vite la cellule B15 de feuille1 et demande la solution à Solveur et copie/colle les résultats dans le tableau de la feuille "6_10 produit" et avec ces chiffres on crée 2 graphiques, un pour le tonnage des produits et l'autre avec les heures de chaque config.
La macro est assez facile à comprendre, je suppose,
Bon, si on regarde aux graphiques et le tableau, on voit directement que les tonnages des produits et les heures des configs changent bien lineaire entre les 2 valeurs 795-14.972 mais à ces 2 points, il y a des choses supplémentaires.
De 0 A 795, on n'a que config 1-5-8-9 qui sont dans la solution et qui sont horizontal (ne changent pas de valeur), à 795, le config 2 entre la solution et les 5 autres configs changent linéaire jusqu'à 14..792, au moment où config9 devient 0 et quite la solution. A ce moments certaintes pentes des lignes des tonnages dans le première graphique changent.
Je pense que les graphiques vous expliquent plus que moi
on peut faire la même chose avec les configs et config8 est peut-être un bon exemple. Il est dans la solution avec 39 heures et un tonnage de 365 T/h. Mais ces limites sont assez étroit. Si vous savez modifier son tonnage avec 17 en moins ou 57 en plus, il peut se passer des changements dans les résultats. Je ne sais pas vous dire quoi pour le moment, il faut simuler cela avec la macro et étudier les graphiques ...
Mais je pense que c'est mieux de calculer avec des coûts au lieu de ces tonnages, le solde de la vente des produits primaires et les coûts de stockage et d'enlèvements des produits secondaires + le coût de l'installation.
Un autre calcul à faire, vous voyez la ligne 9-10 de "6_10 produit", donc 6.000 et 8.000 T de produit "6/10", si vous divisez l'écart entre ces lignes par 2.000, vous savez pour une demande de 6.000T "6/10" les heures nécessaires des configs et l' "output" des autre produits. Donc vous êtes capables à calculer un coût marginal (en termes économiques = la pente des lignes dans les 2 graphiques)
Bonjour,
Merci grâce à vos graphes je comprends beaucoup mieux.
La seule chose (après j'arrête de vous solliciter) c'est la macro, elle fait changer le besoin initiale du 6/10 qui passe de 6000t à 19000t est-ce normal ?