Trouver LA solution parmi toutes celles proposées par Solveur !

[mongril]

Bonsoir,

J'utilise Solveur pour obtenir la valeur de deux cellules A1 et B1 de façon à ce qu'une troisième C1 soit égale à 0.

La cellule C1 est la valeur minimale d'une plage de cellules qui dépendent de A1 et B1.

Le problème ainsi posé ne comporte pas qu'une seule solution et Solveur me donne donc des résultats systématiquement différents.

Le hic c'est que je souhaite obtenir la combinaison A1 + B1 la plus petite possible et j'ignore comment procéder pour rajouter cette contrainte ???

Admettons que les solutions proposées soient A1 = 3 et B1 = 10 (A1 + B1 = 13) ou A1 = 5 et B1 = 6 (A1 + B1 = 11), j'aimerais alors obtenir la seconde solution, 11 étant inférieur à 13.

Est-ce possible ?

Merci infiniment par avance.

Je vous souhaite une excellente soirée.

P.S. Peut être ne suis-je pas assez précis ??

 

[Misange]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Bonjour
Comme tu as certainement lu la charte avant de poster, si tu n'as pas jugé utile de joindre un classeur c'est que tu dois être trop timide ?
c'est toujours plus simple pour les répondeurs de tester avec un classeur, ça leur évite de devoir le faire eux-même pour qu'on leur dise ensuite que ça ne correspond pas à la situation du demandeur...

 

[mongril]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Bonjour
Comme tu as certainement lu la charte avant de poster, si tu n'as pas jugé utile de joindre un classeur c'est que tu dois être trop timide ?
c'est toujours plus simple pour les répondeurs de tester avec un classeur, ça leur évite de devoir le faire eux-même pour qu'on leur dise ensuite que ça ne correspond pas à la situation du demandeur...

Bonjour,

Désolé. Voici un exemple (Cf. classeur joint).

L'idée est de trouver une combinaison C12 + L12 la plus petite possible telle que U18 soit strictement égale à zéro.

Dans le classeur joint les "ressources 2" déterminent le montant "d'emprunt 1" et donc les annuités générées par "l'emprunt 1". Les "ressources 2" et "l'emprunt 1" déterminent ensuite le montant "d'emprunt 2" et donc les annuités générées par "l'emprunt 2".

Ressources 2 => Emprunt 1 => Annuités emprunt 1
Ressources 2 et Emprunt 1 => Emprunt 2 => Annuités emprunt 2

Si je lance le Solveur j'obtiens des résultats différents parce qu'il existe une multitude de combinaisons possibles, ce qui m'intéresse c'est la meilleure combinaison.

Exemple, j'ai lancé trois fois le Solveur et voici les résultats :
* Solution 1 : C12 = 771 900,39 et L12 = 129 649,32 soit C12 + L12 = 901 549,71
* Solution 2 : C12 = 398 017,44 et L12 = 455 562,13 soit C12 + L12 = 853 579,57
* Solution 3 : C12 = 920 632,41 et L12 = 0 soit C12 + L12 = 920 632,41

Dans l'exemple, la meilleure combinaison c'est la 2ème puisque C12 + L12 est la plus petite combinaison, mais il existe sans aucun doute encore une meilleure combinaison.

Merci d'avance.

 

[Misange]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Tu n'as mis aucune contrainte dans le calcul.
C'est à toi de voir ce qui est pertinent d'ajouter mais c'est en jouant sur les contraintes qu'on peut grandement limiter le nombre de solutions proposées.

 

[mongril]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Les contraintes proposées limiteront certainement les résultats mais ne permettent pas d'indiquer que l'on souhaite obtenir la combinaison la plus faible possible parmi toutes celles potentiellement proposées.

Ça ne répond donc pas à l'objectif.

En réalité l'objectif c'est de trouver C12 et L12 de façon à ce que U18 soit au moins égale à zéro et que le C12 + L12 soit le plus faible possible.

 

[eriiic]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Bonjour,

Les contraintes proposées limiteront certainement les résultats mais ne permettent pas d'indiquer que l'on souhaite obtenir la combinaison la plus faible possible parmi toutes celles potentiellement proposées.

Bien sûr que si, mais je pense que tu abordes mal ton problème.

Ton objectif est de minimiser C12+L12.
Il te faut donc une cellule avec cette somme, disons U15, et dans le solveur mettre U15 dans 'objectif à définir' en cochant 'Min'.
Et comme 'contrainte' U18 = 0

Le solveur te trouve instantanément la réponse : C12=0 L12=0 et il a raison...
Nulle part tu n'utilises les autres résultats de ton tableau comme contrainte.
Il faut revoir un peu l'analyse de ton problème non ?

eric

 

[ROGER2327]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Bonjour à tous

Vraiment pas la peine de sortir la grosse artillerie pour ce problème simplissime !
Sous la contrainte U18=0, les valeurs C12 et L12 sont liées par la relation linéaire

L12=802512,921769508-0,871697450897949*C12.​

Par conséquent,

C12+L12=802512,921769508+0,128302549102051*C12,​

ou encore

C12+L12=920632,406281362-0,14718701881012*L12.​

Il est clair que le minimum de C12+L12 est 802512,921769508, obtenu avec C12=0. (I.e. avec L12=802512,921769508 et non L12=0.)

À moins que je n'aie rien compris, ce qui ne m'étonnerait qu'à moitié...​

ROGER2327
#6012

Vendredi 13 Gidouille 139 (SSaint Instintestins, conseillers intimes - fête Suprême Quarte)
9 Messidor An CCXX, 4,3353h - absinthe
2012-W26-3T10:24:17Z

 

[Misange]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

A moi je serais au contraire étonnée que tu n'aies compris qu'à moitié
tu as pris le temps de regarder ce qu'il y avait dans les cellules contrairement à moi.
Comme tu dis, pas besoin de la grosse artillerie !

 

[mongril]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Bonjour,


Bien sûr que si, mais je pense que tu abordes mal ton problème.

Ton objectif est de minimiser C12+L12.
Il te faut donc une cellule avec cette somme, disons U15, et dans le solveur mettre U15 dans 'objectif à définir' en cochant 'Min'.
Et comme 'contrainte' U18 = 0

Le solveur te trouve instantanément la réponse : C12=0 L12=0 et il a raison...
Nulle part tu n'utilises les autres résultats de ton tableau comme contrainte.
Il faut revoir un peu l'analyse de ton problème non ?

eric

Bravo, merci infiniment.

Il fallait voir le problème autrement et ça marche parfaitement. Merci beaucoup, vraiment.

En réalité il y a plusieurs contraintes :
1. La première : il faut que U18 soit égale à zéro ;
2. La deuxième : il faut que C4 (qui est la somme de C12 + C13 + C14) soit égale aux coûts déduction faite de la "ressource 1" soit C7 + C8 + C9 - C11
3. La troisième : il faut que L4 (qui est la somme de L12 + L13 + L14) soit égale aux coûts déduction faite de la "ressource 1" soit L7 + L8 + L9 - L11

Du coup l'objectif c'est effectivement que E12 (somme de C12 + L12) tende vers 0 en trouvant C12 et L 12 en fonction des trois contraintes ci-dessus.

Le résultat qui ressort : C12 = 0 et L12 = 802 512,92

Super, un grand merci encore eriiiic.

 

[eriiic]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Re,

Maintenant que tu as une meilleure vision du tableur je te conseille de lire le post #7de Roger, c'est le maitre Capello des maths ;-) Toujours de bon aloi...

eric

 

[mongril]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Re,

Maintenant que tu as une meilleure vision du tableur je te conseille de lire le post #7de Roger, c'est le maitre Capello des maths ;-) Toujours de bon aloi...

eric

J'ai relu, mais comment arrive-t-il à cette relation linéaire ?

 

[mongril]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Bonjour à tous

Vraiment pas la peine de sortir la grosse artillerie pour ce problème simplissime !
Sous la contrainte U18=0, les valeurs C12 et L12 sont liées par la relation linéaire

L12=802512,921769508-0,871697450897949*C12.​

Par conséquent,

C12+L12=802512,921769508+0,128302549102051*C12,​

ou encore

C12+L12=920632,406281362-0,14718701881012*L12.​

Il est clair que le minimum de C12+L12 est 802512,921769508, obtenu avec C12=0. (I.e. avec L12=802512,921769508 et non L12=0.)

À moins que je n'aie rien compris, ce qui ne m'étonnerait qu'à moitié...​

ROGER2327
#6012

Vendredi 13 Gidouille 139 (SSaint Instintestins, conseillers intimes - fête Suprême Quarte)
9 Messidor An CCXX, 4,3353h - absinthe
2012-W26-3T10:24:17Z

Comment trouves-tu cette relation linéaire ?

 

[ROGER2327]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Re...

Comment trouves-tu cette relation linéaire ?

Grâce à mon pif gras...

Plus sérieusement : par l'observation du tableau de données et des résultats que vous avez calculé. La contrainte fixée en U18 renvoie aux données d'une seule ligne du tableau : celle qui annule S[SUB]n[/SUB] + J[SUB]n[/SUB]. (Vous remarquerez que, sauf erreur de ma part, il s'agit toujours de la ligne 22.)

Par observation des calculs réalisés entre les cellules de cette ligne (des sommes et des produits), on subodore des choses assez linéaires.

Par ailleurs, en voyant les trois couples de solutions que vous mentionnez dans le message #3, et avec un peu de sens des nombres, on est conforté dans cette odeur. Alors on teste : un petit graphique vite fait montre que ces trois couples de solutions sont parfaitement alignés. Ça sent de plus en plus le phénomène linéaire !
Un petit coup de "résoluteur" avec C12 comme seule cellule variable, L12 fixé à 0, puis avec L12 comme seule cellule variable, C12 fixé à 0, fournit deux nouveaux couples de solutions (920632,41... ; 0) et (0 ; 802512,92...) qui s'alignent avec les autres. Plus guère de doute !

Un chouïa de VBA, maintenant :​

Sub test()
Dim m#, i&, f As Worksheet
    m = 920632.406281362
    Set f = ActiveSheet
    With Application: .ScreenUpdating = 0: .EnableEvents = 0: .Calculation = -4135: End With
    Feuil1.Activate
    SolverOk SetCell:="$U$18", MaxMinVal:=3, ValueOf:=0, ByChange:="$L$12", Engine _
        :=1, EngineDesc:="GRG Nonlinear"
    For i = 0 To 99
        Feuil1.Range("$C$12").Value = m * i / 99
        SolverSolve True
        With Feuil2
            .Range("$G$2").Offset(i).Value = Feuil1.Range("$C$12").Value
            .Range("$H$2").Offset(i).Value = Feuil1.Range("$L$12").Value
            .Range("$I$2").Offset(i).Value = Feuil1.Range("$C$12").Value + Feuil1.Range("$L$12").Value
        End With
    Next
    f.Activate
    With Application: .Calculation = -4105: .EnableEvents = 1: .ScreenUpdating = 1: End With
End Sub

On obtient ainsi le Tableau 1 de la feuille Feuil2. Plus de doute possible.

Un dernier calcul des relations explicites qui lient C12 et L12, et l'établissement du tableau de contrôle Tableau 2 de la feuille Feuil2 confirment l'hypothèse... Un graphe de synthèse pour faire beau... Terminé.

C'est plus rapide à faire qu'à raconter. C'eût été encore plus rapide si nous avions eu le modèle mathématique sous-jacent, c'est-à-dire les quelques équations qui ont servi à bâtir le tableau de données...​

ROGER2327
#6013

Samedi 14 Gidouille 139 (Saint Colon, artilleur - fête Suprême Quarte)
10 Messidor An CCXX, 0,1047h - faucille
2012-W26-4T00:15:05Z

 

[mongril]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Bonsoir,

J'ai une question complémentaire.

J'ai un peu modifié mon classeur pour y inclure de nouvelles variables et je souhaiterais qu'une de ces variables soit un multiple de 50 000. Je n'arrive pas à savoir comment rajouter une contrainte de ce type.

Admettons que la variable en question se situe dans la cellule M17, j'ai mis dans la cellule O17 la formule suivante : si(ent(M17/50000)=M17/50000;1;2) et j'ai rajouté dans mon solveur la contrainte suivante O17=1.

Mais ça ne fonctionne pas, pourtant il faut bien que O17 soit égale à 1 si on veut que M17 soit un multiple de 50 000.

Je vous remercie d'avance parce que là je sèche.

Excellente soirée.

 

[eriiic]

Re : Trouver LA solution parmi toutes celles proposées par Solveur !

Bonsoir,

Ahhh, on croyait que tu étais venu dire merci à Roger....

pourtant il faut bien que O17 soit égale à 1 si on veut que M17 soit un multiple de 50 000.
On ne peut que te dire oui.
Ou bien =MOD(M17;50000)=0
Mais le solveur, selon les choix fait, ne va pas forcément travailler avec des entiers ou des valeurs exactes.
Il va essayer d'approcher la solution, mais selon les minimas et maximas il peut passer à coté d'une solution.
Essaie d'ajouter la contrainte O17 ent entier

eric