Valeur maximale possible avant que le reste soit <0

Bonjour,

Je souhaiterais savoir s'il est possible de trouver la valeur maximale que peut accepter ma formule avant que le reste soit égale à 0.

excel question

Par exemple sur cet exemple :

  • dans la cellule bleu je renseigne la hauteur que je souhaite atteindre
  • celles en orange, je souhaite que les valeurs optimales s'affichent afin d'avoir le reste le plus petit possible.

J'ai donc essayé de faire avec une recherche de valeur cible sauf que je ne veux pas la valeur exacte, les éléments disponibles sont en unité, je ne peux pas en avoir un demi.

Merci par avance,

Julien

4question.xlsm (12.62 Ko)

Bonjour,

avec le solveur.

eric

7question.xlsm (62.05 Ko)

Ouahhh merci beaucoup,

j'avais jamais essayé le solveur mais ça a l'air d'être un chef d’œuvre cet outil

Est-il possible de lancer le calcul via un bouton en le faisant pour plusieur hauteur en même temps ou il faut faire une macro qui active les multiples solveurs que je dois paramétrer ?

Un grand merci en tout cas

oui, tu peux le paramétrer et le lancer en vba :

http://support.microsoft.com/kb/843304

http://www.solver.com/content/basic-solver-vba-functions

Il faut avoir chargé le complément Solver dans les options.

eric

PS : et activer Solver dans Outils / Références... de VBE

Salut,

Merci pour tout ça, je vais essayer ça dans les prochains jours.

Je souhaite juste savoir s'il est possible de tenir compte des prix unitaire de chaque élément afin d'atteindre la hauteur souhaitée mais avec un prix minimal.

Merci pour l'aide 😉

Re, encore moi

J'ai réussi à le faire dans une macro (Macro5) puis j'ai essayé d'étendre mon tableau à 2 lignes pour tester et ajouter un bloc à ma macro pour qu'une seule et même macro calcul mes 2 lignes (Solveur_1).

Sub Solveur_1()

    Application.DisplayAlerts = False

     'Ligne a
    SolverOk SetCell:="$H$4", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$4:$G$4", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$C$4:$G$4", Relation:=4, FormulaText:="entier"
    SolverAdd CellRef:="$C$4:$G$4", Relation:=3, FormulaText:="0"
    SolverAdd CellRef:="$H$4", Relation:=3, FormulaText:="$I$4"
    SolverSolve Userfinish:=True

    'Ligne b
    SolverOk SetCell:="$H$5", MaxMinVal:=2, ValueOf:=0, ByChange:="$C$5:$G$5", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$C$5:$G$5", Relation:=4, FormulaText:="entier"
    SolverAdd CellRef:="$C$5:$G$5", Relation:=3, FormulaText:="0"
    SolverAdd CellRef:="$H$5", Relation:=3, FormulaText:="$I$5"
    SolverSolve Userfinish:=True

    Application.DisplayAlerts = True
End Sub

Mais ça ne fonctionne pas, j'ai un message en bas à gauche qui reste : "mise en place du problème..."

De plus je dois le faire pour une trentaine de lignes à terme... je ne suis pas rendu, il faudrait donc que je passe idéalement par une boucle sinon je ne suis pas rendu.

Je ne vois pas du tout comment le faire

Bonne soirée

6probleme.xlsm (17.68 Ko)

Vite fait avant d'aller au lit :

Sub Solveur_1()
    Dim pl As Range
    Application.DisplayAlerts = False
    For lig = 4 To Cells(Rows.Count, "B").End(xlUp).Row
        SolverReset
        With Range("$C$4:$G$4").Offset(lig - 4)
            SolverOk SetCell:=Cells(lig, "H").Address, MaxMinVal:=2, ValueOf:=0, ByChange:=.Address, _
                     Engine:=1, EngineDesc:="Simplex LP"
            SolverAdd CellRef:=.Address, Relation:=4, FormulaText:="entier"
            SolverAdd CellRef:=.Address, Relation:=3, FormulaText:="0"
            SolverAdd CellRef:=Cells(lig, "H").Address, Relation:=3, FormulaText:=Cells(lig, "I").Address
            SolverSolve Userfinish:=True
        End With
    Next lig
    Application.DisplayAlerts = True
End Sub

J'ai mis la méthode de résolution Simplex PL.

Ca demande peut être confirmation (je ne connais pas vraiment les critères de validité) mais elle a l'air de fonctionner sur ton modèle.

Si c'est ok elle est beaucoup plus rapide.

eric

Salut,

ça fonctionne trop bien tu veux dire, merci beaucoup

la ligne 2 correspond à quoi ?

Dim pl As Range

A quoi correspond la méthode du simplex LP ?

Je me suis amusé à ajouter quelques conditions mais il y a une que je ne trouve pas pour l'instant, c'est le fait que la somme d'une plage de cellule (pour la ligne en cour de calcul) soit égale à 1...

SolverAdd CellRef:=Cells(lig, "I:L").Address, Relation:=2, FormulaText:="1"

ça n'a pas l'air d'être trop ça

ça devrait être bon après, merci pour tout

Je me suis débrouillé pour ça en faisant une somme dans une colonne à côté et appliquant ma condition dessus x)

Bonjour,

la ligne 2 correspond à quoi ?

C'est la déclaration et le typage de la variable pl : type Range (cellule ou plage)

Même si c'est facultatif je te conseille fortement le faire systématiquement. Ca t''alertera si tu utilises une variable pour ce qu'elle n'est pas prévue (erreur de variable ?), ou si tu fait une faute de frappe dessus.

Le style d'erreur qu'on passe des heures à résoudre. On a beau lire et relire, le cerveau lit ce qu'il veut lire...

Dans Ouyils / Options cocher Déclaration de variables obligatoires.

Pour les conditions difficiles, mets-toi en enregistrement de macro en paramétrant le solveur.

Plus qu'à ramasser les bouts qui t'intéressent.

eric

Bonjour,

Ha d'accord merci, ça pourrait être quoi comme variables autre que plage et cellule ??

Yep, c'est ce que je fais mais pas toujours simple pour aller plus loin et automatiser plus...

En tout cas tout fonctionne bien.

Juste pour chipoter il faudrait que j'optimise un dernier truc : il me met parfois 6 éléments de 10cm de hauteur alors qu'il y a le même élément dispo avec 60 de hauteur par exemple...

Bonjour,

Fais une recherche sur type variable vba.

Tu ne peux pas diriger le solveur. Il s'arrête dès que les critères sont respectés.

Si tu veux un truc précis il te faut faire une macro vba.

Ou bien lui faire optimiser une note que tu calcules par formule qui varierait selon le nombre d'éléments, à goupiller avec le mini que tu dois atteindre.

Par exemple, si ce qui prime c'est le reste mini :

note= reste*100000+somme(éléments)

Et tu demandes au solveur de minimiser cette note.

Il faudra sans doute aussi compléter ta formule du reste si jamais il passe négatif, le mettre à 9^9 (par exemple) dans ce cas.

eric

Il faudrait que je dise au solveur de minimiser une cellule comprenant la somme de mes éléments à l'intérieur de mon VBA existant ? Oulala

Sauf que si je lui dit de minimiser mon nombre d'éléments il va partir en cacahuètes puisqu'il n'arrivera pas à atteindre parfaitement mon reste 😅

Donc il donnera des chiffres avec des décimales je pense 😭

Je ne vois pas comment le faire en VBA, j'essaierai de bidouiller un truc avec une formule 😑🤔

Bonne journée 😉

Bonjour

Si tu veux, c'est comme un programme de jeu d'échec.

Il ne sait pas jouer, mais on lui fait calculer une note. Plus la position est forte, plus elle est élevée et il choisit la meilleure.

A toi de trouver le bon calcul. Comme départ prend ce que je t'ai indiqué en mettant un fort coefficient au reste de façon qu'il prime devant le nombre d'éléments aussi élevé qu'il puisse être.

eric

Rechercher des sujets similaires à "valeur maximale possible que reste soit"