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 ;-)

 

[sylvanu]

Bonjour Monster, et bienvenu sur XLD.
Un essai en PJ uniquement par formules.
Suppose pas plus de 4 couples de compensation de deux types différents.
Couvre des portes de 78 à 512 kg.
En espérant avoir tout compris.

 

[Monster92]

Bonjour Sylvanu, merci beaucoup pour votre travail.
je m'attendais juste à un petit tableau d'exemple avec quelques valeurs, vous avez réussi à reprendre toutes mes données et ça à l'air de fonctionner.
En fait ce qui détermine la série de ressort en 200, 220 ou 240 c'est la hauteur de la porte max. mais on peut avoir des portes très larges et donc tout aussi lourdes. j'aurai juste à adapter par rapport aux valeurs des autres formats de boites ressort.
On n'est pas obligé d'avoir des couples différents, on peut avoir 8 boites de ref5 pour 288kg par exemple (8x36kg). Est qu'on peut le modifier pour avoir jusqu'à 8 couples identiques au lieu de 4+4?
un grand merci

 

[Monster92]

Alors ça fonctionne bien mais si je prend l'exemple de 132kg ça me donne cette solution:
-4 ressorts 60x1.2 et 2 ressorts 60x1
alors que le plus simple serait :
-2 ressorts 60x1.3 et 2 ressorts 1.4 (2x29kg)+(2x37kg)
Peut on afficher plusieurs solutions?
merci

 

[sylvanu]

Bonjour,

-2 ressorts 60x1.3 et 2 ressorts 1.4 (2x29kg)+(2x37kg)

J'ai toujours pris le minimum des valeurs données. J'ai donc 2*(29+35) donc 128 et non 132 car vous panachez les min et les max.
Je pense que si la fourchette est de 35 à 37kg, on est obligé de prendre les valeurs min pour garantir que dans le pire cas la force des ressorts soit toujours supérieure au poids de la porte.

Ensuite pour le premier message, doit on toujours avoir un seul type de ressorts ( de 1 à 8 ) ou on peut panacher avec 4+4 ?
Question subsidiaire : Peux t on choisir le type de ressort dans tout le tableau ? soit 15 types possible.

A vous lire.

 

[sylvanu]

Just for the fun,
En Pj un exemple, peut être l'optimum, avec contrainte :
- les 15 type de ressorts utilisables
- de 1 à 8 jeu de ressorts par porte
- tous de même type
On couvre de 32 à 560 kg avec 120 configurations.

 

[Monster92]

merci pour votre réponse,

d’après les abaques on peut utiliser n'importe quelle valeur mini/maxi d'un ressort. le tout est de se rapprocher de la valeur de la porte pour l'équilibrer sans la dépasser non plus. si les ressorts sont trop puissants la porte remontera toute seule. en fait elle ne doit ni descendre ni monter seule en cas par exemple de défaillance moteur. c'est la norme de sécurité pour toutes les portes à automatisme.
Si on obtient la meme valeur de poids avec 4 ou 6 boites ressorts, on va le plus souvent choisir la version 4 boite, pour reduire les couts et le temps de fabrication. Le ressort est une lame enroulée par exemple largeur 60x "epaisseur 1.4) et dont la longeur correspond à la hauteur de porte + marge de fixation.

pour le type de ressort, c'est suivant la boite. 200,220 ou 240 étant le diamètre de boite suivant le diamètre de l'axe. Il est defini en amont suivant la hauteur de porte.
<3500mm =200
<4200mm =220
<5000mm =240
dans mon tableau actuel, je rentre mes dimensions de porte et j'obtient le poids, l'axe et le type de boite ressort.
Reste à choisir donc parmis les 5 valeurs de ressorts la combinaison qui va correspondre au poids de la porte. Normalement une ou deux valeur de ressorts sont utilisées.

En espérant que ça vous aiguille

 

[Monster92]

J'ai séparé du tableau les boites en 200, 220 et 240. idéalement suivant la valeur de la cellule "choix boite R" ça devrait utiliser le tableau correspondant mais je n'y arrive pas; peut etre avec une valeur Si?

 

[sylvanu]

1- Nos messages se sont croisés.
En PJ un essai avec du VBA.
Il suffit de modifier la cellule Cible pour avoir une configuration avec un seul type, et une configuration avec deux types. J'utilise les valeurs min et max.
Il vous faut activer les macros s'il vous le demandent. La macro est :

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("C10")) Is Nothing Then
        On Error GoTo Fin
        Application.ScreenUpdating = False
        Dim T, Cible, Erreur, i%, j%, A, B
        T = [Mat]: Cible = [C10]: Erreur = 9 ^ 9
        For i = 2 To UBound(T)
            If T(i, 7) < Cible And Cible - T(i, 7) > 0 And Cible - T(i, 7) < Erreur Then
                    A = i: Erreur = Cible - T(i, 7)
            End If
        Next i
        [C14] = A - 1
        For i = 2 To UBound(T)
            For j = 2 To UBound(T)
                If T(i, 7) + T(j, 7) < Cible And Cible - T(i, 7) - T(j, 7) > 0 And Cible - T(i, 7) - T(j, 7) < Erreur Then
                    A = i: B = j: Erreur = Cible - T(i, 7) - T(j, 7)
                End If
            Next j
        Next i
        [C21] = A - 1: [D21] = B - 1
    End If
Fin:
End Sub

2- Je regarde votre dernier fichier, mais testez ma PJ pour voir.

 

[sylvanu]

Réponse au #8.
Le plus judicieux est d'avoir un seul tableau dont les valeurs changent en fonction du choix Boite R, que j'ai mis en liste déroulante. ( si j'ai bien compris, sur une porte on utilise qu'un type de ressort défini au départ )
Ce choix remet à jour le tableau E1:E6 et il n'y a plus qu'un tableau de calcul.

 

[Monster92]

si j'ai bien compris, sur une porte on utilise qu'un type de ressort défini au départ )

Pas vraiment, on defini la boite ressort (200, ou 220 ou 240) par rapport à la hauteur de la porte. Donc j'aurai un cellule qui aura cette valeur automatique et votre tableau devrai l'utiliser pour ensuite choisir (a la place la liste déroulante) les ressorts de compensation parmi les 5 modèle 60x1/60x1.1/60x1.2/60x1.3/60x1.4.
On utilise donc une valeur de boite par porte, mais une ou deux valeurs de ressorts pour arriver au poids exact.

Par exemple un eporte de 4500mm de haut doit utiliser des boites de diametre 240
son poids étant de 176kg, en serie 240 on peut avoir 4x 1.4 + 2x 1.2 (4x 33kg + 2x 22kg) =176
Mais aussi: 4x 1.3 + 2x 1.4 (4x 28kg + 2x 32kg) =176

Sur la V4 j'obtient comme solution: 8 ressorts de 1.2=176, ce qui est correct mais pas optimisé.
Si j'ai les différentes solution qui s'affichent je peux choisir visuellement laquelle est la meilleure, pas besoin d’algorithme.

 

[sylvanu]

et sur la V3 (xlsm) ?

 

[sylvanu]

Une V5 car la V3.xlsm a un petit bug.
176 cela peut être aussi ,en diam de 240, 3 60x1.3 qui fait 174kg.

 

[Monster92]

Bonjour Sylvanu,
d'apres les tests que j'ai pu faire celà fonctionne difficilement. La V4 fonctionne mieux que la V5: dans les deux cas on pourrait avoir plusieurs solutions mais la V4 donne une solution =à la charge, ce qui est correct
Pour 176kg en 240, sur la V5 elle retient 174kg.
y a t'il possibilité d'afficher plusieurs solutions quand elles correspondent à la valeur cible?
merci

 

[sylvanu]

Bonjour Monster,
J'ai amélioré l'algo, voir la V6.
Dans l'état difficile de faire mieux, il faudrait tout changer car non prévu au départ, cet algo ne fournit que une solution 1 type et 1 solution deux types.