Contraintes du solveur excel

[Résolu/Fermé]
Signaler
Messages postés
9
Date d'inscription
mardi 6 mars 2018
Statut
Membre
Dernière intervention
7 mars 2018
-
Messages postés
13127
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
25 octobre 2021
-
Bonjour,

je rencontre un problème dans l'utilisation du solveur d'excel et plus particulièrement sur les contraintes des variables.
Je m'explique: j'ai une formule assez pénible qui doit etre égale à une valeur donnée (Perte de Charge souhaitée).

Cette formule est fonction de longeurs et de diamètres, c'est à dire de la forme PdC=f(D1,L1)+f(D2,L2)+....
Je voudrais donc utiliser le solveur pour atteindre ma valeur en modifiant ces diamètres et longeurs.

Le hic: les longeurs sont encadrées (par exemple 150mm<L1<170mm), je peux donc les contraidres sans problèmes mais les diamètre possibles sont des valeurs précises, à savoir 2,4,6,7,8,10,12,14,16,18,20.

Un premier pas serait de demander à avoir des valeurs de diamètres entières, mais ne résout pas mon problème.

Comment puis je faire pour qu'excel me trouve une solution à cette équation en utilisant qu'un de ces diamètre?

Je précise que faire autant de solveur différent que de configurations possibles de diamètres m'est compliqué car j'arrive à un très grand nombre de calcul.

Par avance, je vous remercie beaucoup.

ps: si vous avez d'autres moyens de résoudre cela dites moi; je suis preneur! :)

4 réponses

Messages postés
13127
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
25 octobre 2021
2 270
Bonjour à tous

Une possibilité ;
Lister les diamètres possibles par ex dans la plage A12:A22
Mettre dans une cellule, par exemple G2 la formule =NB.SI(A12:A22;A2)
Mettre comme contrainte pour les diamètres dans le solveur $G$2=1 cette condition permettant de retenir que les cas où la valeur diamètre testée est dans liste

Cdlmnt
Via

1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 41713 internautes nous ont dit merci ce mois-ci

Messages postés
9
Date d'inscription
mardi 6 mars 2018
Statut
Membre
Dernière intervention
7 mars 2018

Bonjour.

Cette utilisation de la fonction COUNTIF ne fonctionne pas comme contrainte pour le solveur dans mon cas. La valeur de diamètre utilisée par le solveur n'est pas égale à une des valeurs rentrée dans "NB.SI". la convergence du solveur serait elle une cause possible?

Cordialement
Messages postés
13127
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
25 octobre 2021
2 270
Re

C'est vrai ma solution ne fonctionne pas apparemment, je n'avais pas fait assez d'essais différents
Voir les autres solutions proposées si elles conviennent mieux

Cdlmnt
Messages postés
24205
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
24 octobre 2021
6 937
Sans plus de restriction sur L presque tous les diamètres peuvent convenir avec :
L =PI()*(diamètre)^(0.5)/(valeur visée)

La plus petite longueur (0.444288294) correspond au plus petit diamètre (2)
Avec le diamètre 20 on obtient L=1.404962946
A mon avis il manque qq chose non ?
eric

En essayant continuellement, on finit par réussir. 
Donc plus ça rate, plus on a de chances que ça marche.(les Shadoks)
En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 41713 internautes nous ont dit merci ce mois-ci

Messages postés
26242
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
26 octobre 2021
6 092
Bonjour
Difficile de tout comprendre avec ce message, vu qu'on ne connaît pas le sujet!
pouvez vous déposer un modèle
http://mon-partage.fr
et revenez coller le lien créé sur le site
Ajoutez dans le modèle toutes explications utiles
à vous lire
crdlmnt
Messages postés
9
Date d'inscription
mardi 6 mars 2018
Statut
Membre
Dernière intervention
7 mars 2018

Bonjour, je vais tenter de réexpliquer mon problème.

Dans le solveur d'excel, on peut appliquer des contraintes aux variables comme majorer ou minorer par exemple.

Est il possible de demander par exemple qu'une variable valle uniquement 2 ou 5 ou 9 ou 45?

c'est à dire qu'elle puisse prendre uniquement une des valeurs données?

Je posterai plus tard un modèle :)

merci d'avance
Messages postés
9
Date d'inscription
mardi 6 mars 2018
Statut
Membre
Dernière intervention
7 mars 2018

Voici un modèle.

https://mon-partage.fr/f/prkISoSm/

La formule à résoudre dans le fichier n'a pas d'importance, je voudrais juste obtenir une solution par le solver qui réponde à la contrainte de variable (c'est à dire un diamètre qui soit uniquement une des valeurs désirées comme 7,10 ou 12 par exemple)
Messages postés
24205
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
24 octobre 2021
6 937
Bonjour,

j'établirai un tableau de correspondance de 1 à 11 avec en vis à vis tes diamètres 2,4,6,7,8,10,12,14,16,18,20.
Le solveur variant de 1 à 11, tu récupères le diamètre avec un recherchev() (ou bien directement dans ta fonction f() )
eric

En essayant continuellement, on finit par réussir. 
Donc plus ça rate, plus on a de chances que ça marche.(les Shadoks)
En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
Messages postés
26242
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
26 octobre 2021
6 092
Re
tant qu'on n'aura pas votre modèle, il y a peu de chance pour que nous puissions fournir des explications plausibles et utilisables
à vous lire?
crdlmnt
Messages postés
9
Date d'inscription
mardi 6 mars 2018
Statut
Membre
Dernière intervention
7 mars 2018

J'ai déposé un modèle plus haut sur cette page, je le remets ici.

https://mon-partage.fr/f/prkISoSm/

La formule à résoudre dans le fichier n'a pas d'importance, je voudrais juste obtenir une solution par le solver qui réponde à la contrainte de variable (c'est à dire un diamètre qui soit uniquement une des valeurs désirées comme 7,10 ou 12 par exemple)

en vous remerciant

Jean
Messages postés
26242
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
26 octobre 2021
6 092
Ok je n'avais pas vu le précédent message
à défaut de savoir ce qui permet de définir le diamètre, voyez ici ce qui est possible avec un tableau de référence (3 possibilités)
https://mon-partage.fr/f/vrM8EEoH/

note:le tableau des valeurs disponibles doit être classé en ordre croissant
revenez si ça ne convient pas mais dites nous pourquoi!
crdlmnt
Messages postés
9
Date d'inscription
mardi 6 mars 2018
Statut
Membre
Dernière intervention
7 mars 2018

le diametre entrée et la longueur sont à trouver grace à cette formule. Le but est de trouver une longeur et un diamètre qui permettent d'atteindre la valeur visée, avec un diamètre quelquonque appartenant à une liste définie et la plus petite longeur possible (dans l'encadrement que l'on donne pour la longueur).

Je ne comprends pas ce que vous avez fait avec le tableau de référence et le tableau des disponibles G1:G20, pouvez m'expliquer?

En vous remerciant d'avance pour vos explications
Messages postés
26242
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
26 octobre 2021
6 092
Il suffit de voir les formules qui "transforment" les valeurs exemple entrées au clavier en A2,A3,A4

vous avez trois propositions de solutions basées sur le code:
INDEX(champ;ligne;colonne) qui va chercher la valeur proche dans la liste (à considérer comme base de données) en G1;G20

principe INDEX dans ce cas:
=INDEX(champ;ligne) (puisque le champ n'a qu'une colonne)

principe EQUIV
ressort le n° de ligne dans le champ où se trouve la valeur directement inférieure à la valeur cherchée
je vous ai proposé trois cas selon vos besoins:

directement inférieure de I2 à I4:
=INDEX($G$1:$G$20;EQUIV(A2;$G$1:$G$20))
EQUIV cherche la ligne de la valeur la plus proche au dessous de A1 dans la liste et INDEX ressort la valeur de cette ligne

directement supérieure de I13 àI16:
=INDEX($G$1:$G$20;EQUIV(A2;$G$1:$G$20)+1)
en rajoutant +1 au code EQYUIV on passe à la ligne qui suit la valeur précédente, donc la plus proche supérieure

valeur la plus proche de I8 à I10
=INDEX($G$1:$G$20;EQUIV(A2;$G$1:$G$20)+SI(A2-INDEX($G$1:$G$20;EQUIV(A2;$G$1:$G$20))<INDEX($G$1:$G$20;EQUIV(A2;$G$1:$G$20)+1)-A2;0;1))
la formule fait la différence entre la valeur réelle , la valeur inférieure et la valeur supérieur et selon les écarts, rajoute 1 si le plus proche est a supérieur, 0 si c'est l'inférieur

Pour appliquer ça à votre cas, après choix d'une option ci dessus, deux solutions
la plus simple:
calculer D exact dans une colonne et utiliser la solution choisie pour renvoyer le diamètre utile dans une autre colonne

la plus compliquée:
,...dépend de l'option que vous retenez dans les trois solutions, et de la complexité de votre formule:
soit donc remplacer A2 dans la formule par l'item qui calcule le diamètre exact

Pour comprendre les formules, le mieux est de les tester:
écrivez quelque part dans une cellule:
=INDEX(G2:G20;4) changez le 4 et voyez ce qui ressort
et aussi:
=EQUIV(A2;G1:G20) et voyez le N° de ligne qui ressort

et si ça ne va toujours pas, soumettez un modèle plus complet avec votre calcul de D exact

crdlmnt