XL 2013 Combinaisons somme de valeurs pour obtenir une valeur cible

[Monster92]

Bonjour,

Je cherche à trouver a partir dune liste de références les combinaisons de valeurs pour obtenir une valeur cible, et chaque référence peut être réutilisée plusieurs fois.
j'essaye tant bien que mal à créer un tableur pour gagner du temps, mais ma solution en utilisant le solver n'est pas des plus pratique à utiliser car je dois lancer le solver a chaque fois et entrer la valeur cible manuellement alors qu'elle est calculée dans une cellule.

Pour fabriquer une porte enroulable, je doit ajouter des ressorts de compensation suivant le poids total de cette porte, et toujours par paire. mais je peux utiliser des paires différentes pour arriver au poids.
chaque ressort a une plage de valeur en kg, par exemple 18 à 20kg, 21 à 23kg etc
Il y a 5 ressorts différents par serie, il y a 3 series independantes suivant le diametre de l'axe utilisé.

pour la serie1:
ref1: 18-20
ref2: 21-23
ref3: 24-26
ref4: 29-32
ref5: 35-37

en gros pour une porte de 166kg, je vais utiliser 4 ressorts de 29kg (ref4) et 2 ressorts de 25kg (ref3)

le solver ne prend pas une plage de valeurs, donc j'ai detaillé mon tableau avec les trois valeurs de chaque ref: 18 19 20 pour la ref1 par exemple. Mais il me donnera en resultat 4x18 + 2x19 alors que ce sont les meme ressorts.

comme je n'arrive pas a mettre une contrainte de paire dans le solver, j'ai du doubler les valeurs en kg pour simuler une paire, mais rediviser pour la lecture des resultats avec les references.
Il peut y avoir plusieurs solutions.

j'ai trouver des exemples qui fonctionnent sur le forum mais orientés compta, avec beaucoup trop de valeurs, et avec lesquels on ne peut pas utiliser plusieurs fois les valeurs d'entrée.

Si quelqu'un aurai une base de combinaison que je pourrait adapter ça serait sympa.
merci ;-)

 

[Monster92]

Bonsoir Sylvanu,
je comprend. Je regarde votre mise a jour et je vous dis.
Merci
cordialement

 

[sylvanu]

Bonjour Monster,
Ca m'agaçait de ne pas comprendre pourquoi on n'arrivait pas à converger.
Le problème vient de cette phrase :

d’après les abaques on peut utiliser n'importe quelle valeur mini/maxi d'un ressort.

D'un point de vue XL elle ne veut rien dire, on choisit la valeur min ou max "comme cela nous arrange".
J'ai donc tenté de simuler le "comme cela nous arrange".
En V7 j'ai fait 5 calculs. Pour deux type de ressorts, je prends une fois les min, une fois les max, puis ensuite les min et max, puis les max et min, puis enfin les moyennes.
Ca ne fait que 1600 combinaisons.
Je fait tous les calculs puis je tri sur l'erreur min et sur le nombre minimum de ressorts utilisés.
A noter que ce n'est pas exhaustif, on pourrait avoir deux ressorts du même type mais un min et un max. Mais cela augmente de façon exponentielle le nombre de calculs.
A tester pour voir. Il suffit de modifier une des deux cellules jaunes le reste est automatique.
( macros à autoriser bien sur )

 

[Monster92]

Bonjour Sylvanu,
j'ai du m’absenter pour des urgences familiales; désolé pour ma réponse tardive.
Je viens de tester et ça me parait bien fonctionner, en tout cas les différentes combinaisons sont exploitables.
J'ai essayé de changer les cellule D10 et D12 pour qu'elle soient appelées par la valeur de D9 et D11 qui seront définies automatiquement sur une autre feuille, par rapport au dimensions de la porte.
ça fonctionne bien pour tous les poids, et pour les ressorts en diam 240 mais pas 220 ou 200. Il doit y avoir un paramètre que je ne sais pas gérer.
quand vous aurez le temps de jeter un coup d'oeil

merci encore

 

[sylvanu]

Bonjour Monster,
C'est normal puisque la macro Worksheet_Change ne se déclenche que quand on valide une modification de valeur d'une des cellules D10 ou D12 par "Entrée".
Si ces valeurs viennent d'une autre feuille, alors en PJ j'utilise la macro Worksheet_Activate.
On choisit les paramètres dans "Autre feuille" ( c'est original ) et les calculs se font automatiquement lorsqu'on sélectionne la feuille Compensation.
A vous d'adapter Autre feuille par votre contexte, il faudra évidemment changer les deux formules en D10 et D12 pour cibler les bonnes valeurs.