Automatiser le solveur excel
Tan
-
michel_m Messages postés 18903 Date d'inscription Statut Contributeur Dernière intervention -
michel_m Messages postés 18903 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour,
Comme il est dit dans le sujet je souhaite automatiser le solveur d'excel (2007 je précise la version au cas ou). Je m'explique:
Je dois résoudre un équation du type : f(a,b,c,T)=1.2 (je vous grâce de la formule exacte c'est un truc a coucher dehors). a,b et c sont fixes pour la résolution, Donc je cherche T de sorte que mon équation soit résolue.
Vous allez me dire : utilise le solveur, pof pof t'auras ta réponse ^^,
en effet !
Sauf que j ai un échantillon de 1000 triplets de valeur pour (a,b,c), donc vous comprendrez qu'étant un peu feignant et n'ayant pas le temps d'utiliser 1000 fois le solveur, j'aimerais savoir s'il est possible d'automatiser la chose pour le faire en une fois, parce que le solveur n'accepte pas de résoudre les 1000 cas de figure en même temps (on ne peut mettre qu'une cellule cible a la fois)
Voili voulou, en espérant avoir été suffisamment clair, merci d avance pour vos réponses.
P.S
Je précise que mes données sont organisées en colonnes:
a | b | c | f(x) | T(valeur recherchée)
1 | 0.4 | 14 | 1.2 | 6.67
1.2 | 0.7 | 10 | 1.2 | etc.
Comme il est dit dans le sujet je souhaite automatiser le solveur d'excel (2007 je précise la version au cas ou). Je m'explique:
Je dois résoudre un équation du type : f(a,b,c,T)=1.2 (je vous grâce de la formule exacte c'est un truc a coucher dehors). a,b et c sont fixes pour la résolution, Donc je cherche T de sorte que mon équation soit résolue.
Vous allez me dire : utilise le solveur, pof pof t'auras ta réponse ^^,
en effet !
Sauf que j ai un échantillon de 1000 triplets de valeur pour (a,b,c), donc vous comprendrez qu'étant un peu feignant et n'ayant pas le temps d'utiliser 1000 fois le solveur, j'aimerais savoir s'il est possible d'automatiser la chose pour le faire en une fois, parce que le solveur n'accepte pas de résoudre les 1000 cas de figure en même temps (on ne peut mettre qu'une cellule cible a la fois)
Voili voulou, en espérant avoir été suffisamment clair, merci d avance pour vos réponses.
P.S
Je précise que mes données sont organisées en colonnes:
a | b | c | f(x) | T(valeur recherchée)
1 | 0.4 | 14 | 1.2 | 6.67
1.2 | 0.7 | 10 | 1.2 | etc.
A voir également:
- Solveur excel en ligne
- Partager photos en ligne - Guide
- Liste déroulante excel - Guide
- Mètre en ligne - Guide
- Word et excel gratuit - Guide
- Formule somme excel ligne - Guide
15 réponses
Bonjour Michel_m,
en effet ce que tu decris est une des manips' qui me manquait. J ai pu reconstitue le tout a partir de l'aide de Microsoft (une fois n'est pas coutume) mais ça reste perfectible (j'expliquerai a la fin)
Tout d'abord le code de ma macro:
Sub solver_automatique()
'
'
While ActiveCell <> "fin" 'debut de la boucle : ca implique que vous avez ecrit fin a la premiere cellule vide sans quoi on tourne a l'infini...
SolverReset
AdresseCelluleCible = ActiveCell.Address 'la ca implique que la cellule selectionne est celle ou vous commencez l'execution de la macro
ActiveCell.Offset(0, -1).Select 'deplacement vers la colonne T
AdresseCelluleACalculer = ActiveCell.Address 'affectation de la variable
ActiveCell.Offset(0, 1).Select 'retour sur colonne f
SolverOk SetCell:=AdresseCelluleCible, MaxMinVal:=3, ValueOf:="1.2", ByChange:=AdresseCelluleACalculer
SolverSolve
ActiveCell.Offset(1, 0).Select 'passage a la ligne suivante
Wend 'fin de la boucle
End Sub
Je rappelle que mes données sont organisées en colonnes comme suit:
a|b|c|T|f
1|5|2|13|1.2
etc. (remarque ce qui ont suivi la conversation depuis le debut auront remarque que T et f ont permutes. Je l'ai fait pour pouvoir vérifier plus facilement ma formule. )
Pour répondre a Eriiic: j'ai effectivement eu le problème du SolverOk inconnu. Et Michel_m (merci a lui) a donne la réponse : pour activer le solveur (et donc qu'il reconnaisse la fonction dans vba), il faut aller dans outils/références et la tu coches solver. S'il n'est pas dans le menu, tu fais parcourir->programs files/Microsoft office/office12/library/solver.xlam (normalement c'est bien cette extension). Et après ça marche ^^
Pourquoi ça reste perfectible ? ben parce que la macro, avec le code actuel renvoi la fenêtre de confirmation du solver autant de fois qu'il est exécute (c est pas drôle et ça ralentit bcp d'avoir a confirmer pour autant de valeurs, mais quand bcp moins que de tout faire manuellement, pour 3000 valeur j ai mis dans les 20 min)
Peut être que Michel_m a la réponse (ou quelqu'un d'autre, on fait comment svp)
Cela dit, enlever la fenêtre ne prévient pas des erreurs (par exemple j'ai eu le droit a quelque valeurs negative, qd on parle d'un temps ça fait quelque désordre)
On pourrait donc compléter pour mettre en valeur les cellules qui marche pas pour les retraiter mais ca c'est un e autre histoire...
Enfin voila, merci a tous ceux qui m'ont conseille, et si quelqu'un sait comment empecher la fenetre du solveur d'apparaitre s'il vous plait, ce serait bienvenue ^^,
et pour ceux que ça intéresse la page de Microsoft qui m'a permis de compléter la macro :
https://support.microsoft.com/fr-fr/help/153442
Bonne journée !
en effet ce que tu decris est une des manips' qui me manquait. J ai pu reconstitue le tout a partir de l'aide de Microsoft (une fois n'est pas coutume) mais ça reste perfectible (j'expliquerai a la fin)
Tout d'abord le code de ma macro:
Sub solver_automatique()
'
'
While ActiveCell <> "fin" 'debut de la boucle : ca implique que vous avez ecrit fin a la premiere cellule vide sans quoi on tourne a l'infini...
SolverReset
AdresseCelluleCible = ActiveCell.Address 'la ca implique que la cellule selectionne est celle ou vous commencez l'execution de la macro
ActiveCell.Offset(0, -1).Select 'deplacement vers la colonne T
AdresseCelluleACalculer = ActiveCell.Address 'affectation de la variable
ActiveCell.Offset(0, 1).Select 'retour sur colonne f
SolverOk SetCell:=AdresseCelluleCible, MaxMinVal:=3, ValueOf:="1.2", ByChange:=AdresseCelluleACalculer
SolverSolve
ActiveCell.Offset(1, 0).Select 'passage a la ligne suivante
Wend 'fin de la boucle
End Sub
Je rappelle que mes données sont organisées en colonnes comme suit:
a|b|c|T|f
1|5|2|13|1.2
etc. (remarque ce qui ont suivi la conversation depuis le debut auront remarque que T et f ont permutes. Je l'ai fait pour pouvoir vérifier plus facilement ma formule. )
Pour répondre a Eriiic: j'ai effectivement eu le problème du SolverOk inconnu. Et Michel_m (merci a lui) a donne la réponse : pour activer le solveur (et donc qu'il reconnaisse la fonction dans vba), il faut aller dans outils/références et la tu coches solver. S'il n'est pas dans le menu, tu fais parcourir->programs files/Microsoft office/office12/library/solver.xlam (normalement c'est bien cette extension). Et après ça marche ^^
Pourquoi ça reste perfectible ? ben parce que la macro, avec le code actuel renvoi la fenêtre de confirmation du solver autant de fois qu'il est exécute (c est pas drôle et ça ralentit bcp d'avoir a confirmer pour autant de valeurs, mais quand bcp moins que de tout faire manuellement, pour 3000 valeur j ai mis dans les 20 min)
Peut être que Michel_m a la réponse (ou quelqu'un d'autre, on fait comment svp)
Cela dit, enlever la fenêtre ne prévient pas des erreurs (par exemple j'ai eu le droit a quelque valeurs negative, qd on parle d'un temps ça fait quelque désordre)
On pourrait donc compléter pour mettre en valeur les cellules qui marche pas pour les retraiter mais ca c'est un e autre histoire...
Enfin voila, merci a tous ceux qui m'ont conseille, et si quelqu'un sait comment empecher la fenetre du solveur d'apparaitre s'il vous plait, ce serait bienvenue ^^,
et pour ceux que ça intéresse la page de Microsoft qui m'a permis de compléter la macro :
https://support.microsoft.com/fr-fr/help/153442
Bonne journée !
Tan
Et pendant que j'y suis je m'excuse pour les fautes d'orthographe, je ferai attention en me relisant la prochaine fois ^^"
j ai une debut de reflexion en faisant une boucle, le probleme c'est que je ne sais pas si :
la syntaxe est correcte
si c'est complet
voila le code:
While ActiveCell <> "fin" 'donc ca suppose que j'ai ecris fin a la premiere cellule vide
SolverReset
AdresseCelluleCible = ActiveCell.Address 'Sous-entendu donc que j'ai selectionne la cellule ou je dois executer la macro donc colonne f
ActiveCell.Offset(0, 1).Select 'se deplace sur la colonne d'a cote donc T
AdresseCelluleACalculer = ActiveCell.Address
ActiveCell.Offset(0, -1).Select 'reviens a la cellule de depart
SolverOk SetCell AdresseCelluleCible , MaxMinVal:=3, ValueOf:="1,2",
ByChange:=AdresseCelluleACalculer
SolverSolve 'resolution du solveur
ActiveCell.Offset(1, 0).Select 'incrementation
End while 'fin de boucle
vous sauriez comment l'améliorer SVP ?
@gryzzly: non, je ne peux pas mettre T en fonction de f et des paramètres car il n'y a pas de solutions analytique.
Pourquoi ? parce ma fonction f fait appel a la fonction erreur (erf), et il ne m'est pas possible de la "triturer" pour en sortir T.
la syntaxe est correcte
si c'est complet
voila le code:
While ActiveCell <> "fin" 'donc ca suppose que j'ai ecris fin a la premiere cellule vide
SolverReset
AdresseCelluleCible = ActiveCell.Address 'Sous-entendu donc que j'ai selectionne la cellule ou je dois executer la macro donc colonne f
ActiveCell.Offset(0, 1).Select 'se deplace sur la colonne d'a cote donc T
AdresseCelluleACalculer = ActiveCell.Address
ActiveCell.Offset(0, -1).Select 'reviens a la cellule de depart
SolverOk SetCell AdresseCelluleCible , MaxMinVal:=3, ValueOf:="1,2",
ByChange:=AdresseCelluleACalculer
SolverSolve 'resolution du solveur
ActiveCell.Offset(1, 0).Select 'incrementation
End while 'fin de boucle
vous sauriez comment l'améliorer SVP ?
@gryzzly: non, je ne peux pas mettre T en fonction de f et des paramètres car il n'y a pas de solutions analytique.
Pourquoi ? parce ma fonction f fait appel a la fonction erreur (erf), et il ne m'est pas possible de la "triturer" pour en sortir T.
tu crer unr formule mathematique qui te fera le calcul poure la premiere ligne tu verifie le resultat ensuite tu la copie ds toute la colonne
temps 1 minute.
temps 1 minute.
Je comprends le principe mais l'équation en question n'est pas solvable de façon littérale. C est pour ça que j ai recours au solveur. A moins que ce soit quand même possible. Dans tous les cas, si il y a besoin d une macro 8ce qui est très probable je dois avouer que je suis un débutant complet dans le domaine.
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
comment est ce que tu résolves ton equation ?
Pour trouver T, tu te tapes pas toutes les valeurs possibles de T à la main ... il y a donc forcément un calcul, aussi ocmplexe soit il, qui permet de passer de f (abcT) = 1.2 à T = fonction de tes paramètres
Pour trouver T, tu te tapes pas toutes les valeurs possibles de T à la main ... il y a donc forcément un calcul, aussi ocmplexe soit il, qui permet de passer de f (abcT) = 1.2 à T = fonction de tes paramètres
A vrai dire, j ai pas encore essaye mais le truc c est que je ne pense pas que le code soit complet.
Je vous tiens au courant.
Je vous tiens au courant.
Bonjour tout le monde,
Le solveur provient d' Excel4; tu n'as pas besoin de répéter les paramètres dans la macro car ils sont conservés par l'assistant.
Dans outils-références de l'éditeur VBE, coches solver.xls
ci dessous un exemple de résolution linéaire (détermination de la marge maxi suivant les essais de volumes d''assemblage de cépages pour un viticulteur)
Range("volume").ClearContents
SolverOk SetCell:="marge", MaxMinVal:=1, ValueOf:="0", ByChange:="volume"
SolverSolve (True)
En espèrant que...
Michel
Le solveur provient d' Excel4; tu n'as pas besoin de répéter les paramètres dans la macro car ils sont conservés par l'assistant.
Dans outils-références de l'éditeur VBE, coches solver.xls
ci dessous un exemple de résolution linéaire (détermination de la marge maxi suivant les essais de volumes d''assemblage de cépages pour un viticulteur)
Range("volume").ClearContents
SolverOk SetCell:="marge", MaxMinVal:=1, ValueOf:="0", ByChange:="volume"
SolverSolve (True)
En espèrant que...
Michel
Bonjour,
merci à michel pour l'info sur l'activation et à toi pour le complément (effectivement je ne l'avais pas dans la liste).
Pour les confirmations ajoute
application.DisplayAlerts = false avant.
N'oublie de le remettre à true après
eric
merci à michel pour l'info sur l'activation et à toi pour le complément (effectivement je ne l'avais pas dans la liste).
Pour les confirmations ajoute
application.DisplayAlerts = false avant.
N'oublie de le remettre à true après
eric
Re,
1/pour éviter la fen^tre,
en début de macro tu marques
Application.screenupdating= False
qui fige le défilement de l'écran
Si tu ne rends pas la main au système, tu dois écrire cette instruction avec False. Sinon, tu peux t'en dispenser.
2/A solversolve je rajouterai (true)
3/ pas convaincu par solverreset (source Microsoft ? car cela réinitialise les paramêtres du solveur et apparemment,il faut recommencer les définitions de ta 1° macro)
4/Il ya pas mal de trucs à faire pour optimiser ta macro (les select-sélection sont d'une lenteur extrèmee et occupent de la RAM). Pour cela, il faudrait que tu nous dises à quelle ligne tu commences dans ta colonne F;
petite précision: F est le nom de la colonne ou l'étiquette en entete de ton tableau? il nous faut le nom de la colonne et la ligne de départ.
Dans l'attente
Michel
Pour les fautes d'orthographe, t'inquiètes pas! on a, hélas, plus l'habitude de lire du SMS des ados boutonneux sur ce site...
edit: Salut, Eric, je n'avais pas rafraichi.
petite remarque: ne trouves tu pas que displayalert est dangereux car empêchant l'alerte sur dysfonctionnement?
1/pour éviter la fen^tre,
en début de macro tu marques
Application.screenupdating= False
qui fige le défilement de l'écran
Si tu ne rends pas la main au système, tu dois écrire cette instruction avec False. Sinon, tu peux t'en dispenser.
2/A solversolve je rajouterai (true)
3/ pas convaincu par solverreset (source Microsoft ? car cela réinitialise les paramêtres du solveur et apparemment,il faut recommencer les définitions de ta 1° macro)
4/Il ya pas mal de trucs à faire pour optimiser ta macro (les select-sélection sont d'une lenteur extrèmee et occupent de la RAM). Pour cela, il faudrait que tu nous dises à quelle ligne tu commences dans ta colonne F;
petite précision: F est le nom de la colonne ou l'étiquette en entete de ton tableau? il nous faut le nom de la colonne et la ligne de départ.
Dans l'attente
Michel
Pour les fautes d'orthographe, t'inquiètes pas! on a, hélas, plus l'habitude de lire du SMS des ados boutonneux sur ce site...
edit: Salut, Eric, je n'avais pas rafraichi.
petite remarque: ne trouves tu pas que displayalert est dangereux car empêchant l'alerte sur dysfonctionnement?
Re Michel,
Je prends note pour figer l'écran merci !
2/je comprends pas ce que ca change me mettre true a SolverSolve, quelle est la différence ?
3/f: ce n'est pas la colonne (cf. mon premier post), c est le nom de ma fonction
4/D ailleurs la colonne ou ma fonction est écrite est : AG et mon échantillon commence a la ligne 16
J ai pris un peu de place car j ai aussi des fait des stats sur les autres paramètres ^^
Voili voilou ^^
Je prends note pour figer l'écran merci !
2/je comprends pas ce que ca change me mettre true a SolverSolve, quelle est la différence ?
3/f: ce n'est pas la colonne (cf. mon premier post), c est le nom de ma fonction
4/D ailleurs la colonne ou ma fonction est écrite est : AG et mon échantillon commence a la ligne 16
J ai pris un peu de place car j ai aussi des fait des stats sur les autres paramètres ^^
Voili voilou ^^
Coucou.
J'ai eu un problème assez similaire. J'ai fait un petit script qui fonctionne, chez moi, je vous le donne, vous verrez ce que vous pouvez en faire...
' Macro3 Macro
' Macro enregistrée le 16/10/2008 par hameje
'
Sheets("Feuil2").Select '(ma feuille de calcul était la seconde...)
SolverReset
SolverOptions MaxTime:=500 '(soyons généreux)
SolverOptions Precision:=0.00001 '(et pas trop imprécis)
For rwIndex = 5 To 504 '(j'avais un solveur à faire tourner sur deux plages de valeurs, la première étant de la ligne 5 à 504. la seconde est parrallèle à celle ci, elle n'est pas présentée ici...)
SolverOk SetCell:=Cells(rwIndex, 9), MaxMinVal:=2, ValueOf:="0", ByChange:=Cells(rwIndex, 5) '(ça, ça veux dire que la cellule à laquelle on s'interresse est la cellule I5 , puis I6, puis... puis I504, et que la cellule dont on veut changer le paramètre est la cellule E5, puis E6, puis... etc)
SolverAdd cellRef:=Cells(rwIndex, 5), relation:=3, FormulaText:=0 '(quelques conditions, ça peut etre utile)
SolverSolve UserFinish:=True '(et pas trop d'informations, j'aime pas cliquer "oui" pour rien)
Next rwIndex '(pour passer à la ligne suivante)
Sheets("Feuil1").Select '(et à la fin, retours à la première page, plus présentable que l'affreux tableau à milles lignes et milles colonnes...)
Ceci dit, c'est aussi un peu (beaucoup) grace à vous tous que j'ai réussi à faire marcher ça, alors merci à tous, et bon courage!
J'ai eu un problème assez similaire. J'ai fait un petit script qui fonctionne, chez moi, je vous le donne, vous verrez ce que vous pouvez en faire...
' Macro3 Macro
' Macro enregistrée le 16/10/2008 par hameje
'
Sheets("Feuil2").Select '(ma feuille de calcul était la seconde...)
SolverReset
SolverOptions MaxTime:=500 '(soyons généreux)
SolverOptions Precision:=0.00001 '(et pas trop imprécis)
For rwIndex = 5 To 504 '(j'avais un solveur à faire tourner sur deux plages de valeurs, la première étant de la ligne 5 à 504. la seconde est parrallèle à celle ci, elle n'est pas présentée ici...)
SolverOk SetCell:=Cells(rwIndex, 9), MaxMinVal:=2, ValueOf:="0", ByChange:=Cells(rwIndex, 5) '(ça, ça veux dire que la cellule à laquelle on s'interresse est la cellule I5 , puis I6, puis... puis I504, et que la cellule dont on veut changer le paramètre est la cellule E5, puis E6, puis... etc)
SolverAdd cellRef:=Cells(rwIndex, 5), relation:=3, FormulaText:=0 '(quelques conditions, ça peut etre utile)
SolverSolve UserFinish:=True '(et pas trop d'informations, j'aime pas cliquer "oui" pour rien)
Next rwIndex '(pour passer à la ligne suivante)
Sheets("Feuil1").Select '(et à la fin, retours à la première page, plus présentable que l'affreux tableau à milles lignes et milles colonnes...)
Ceci dit, c'est aussi un peu (beaucoup) grace à vous tous que j'ai réussi à faire marcher ça, alors merci à tous, et bon courage!
Bonjour,
Sur 2003 tout au moins impossible de faire appel au solver par macro.
Si l'enregistrement de la macro se fait bien, l'appel aux fonctions est protégé.
eric
Sur 2003 tout au moins impossible de faire appel au solver par macro.
Si l'enregistrement de la macro se fait bien, l'appel aux fonctions est protégé.
eric
et ça ne te met aucune erreur sur SolverReset, SolverOk etc ????
Ce ne sont pas des sub ou fonctions inconnues chez toi ?
Ah...
Ce ne sont pas des sub ou fonctions inconnues chez toi ?
Ah...
OK,
pour solversolve(true):
ton truc qui réapparait...
copier-coller de l'aide:
SolverSolve(UserFinish, ShowRef)
UserFinish Argument de type Variant facultatif. Affectez-lui la valeur True pour renvoyer les résultats sans afficher la boîte de dialogue Résultats du Solveur
Je regarde le reste tout à l'heure à moins que qqn d'autre s'y colle (ca m'arrangerait)
Michel
pour solversolve(true):
ton truc qui réapparait...
copier-coller de l'aide:
SolverSolve(UserFinish, ShowRef)
UserFinish Argument de type Variant facultatif. Affectez-lui la valeur True pour renvoyer les résultats sans afficher la boîte de dialogue Résultats du Solveur
Je regarde le reste tout à l'heure à moins que qqn d'autre s'y colle (ca m'arrangerait)
Michel
ci dessous proposition non testée (au besoin, envoie un extrait expurgé de ton classeur sur www.cjoint.com)
Je confirme qu'il ne faut pas utiliser solverreset
dans l'attente (ça m'étonnerait que ça marche du 1°coup!) ;-)
Michel
Sub solver_automatique()
Dim col As Byte, lig As Long, derlig As Long
Dim AdresseCelluleCible As String, AdresseCelluleACalculer As String
'parametre départ
col = 33 'colonne AG
derlig = Range("AG65536").End(xlUp).Row 'fin échantillon
fige l'écran
Application.ScreenUpdating = False
'parcours de l'échantillon
For lig = 16 To derlig
AdresseCelluleCible = Cells(lig, col).Address
AdresseCelluleACalculer = Cells(lig, col - 1).Address
' solveur XL4
SolverOk SetCell:=AdresseCelluleCible, MaxMinVal:=3, ValueOf:="1.2", ByChange:=AdresseCelluleACalculer
SolverSolve (True)
Next
End Sub
Je confirme qu'il ne faut pas utiliser solverreset
dans l'attente (ça m'étonnerait que ça marche du 1°coup!) ;-)
Michel
Remonte petit topic ^^!
Non plus serieusement, je n'ai pes encore eu le temps de tester le code que vous avez poste Michel, car j'entre dans une periode cruciale car je dois soutenir une recherche (qui n'a aucun je precise avec l'etude pour laquelle j avais besoin de la macro) a la fin du mois. J'ai juste teste sSolversolve(true) pour un faible echantillon de valeurs (10) et je dois dire que je comprends maintenant ce que vous entendiez par la lenteur d'execution de mon code tel qu'il est actuellement.
Je vous tiens au courant, en ne manquant pas de vous remercier pour la precision de vos reponses (faut que je familliarise un peu plus avec la syntaxe)
Cordialement,
Tan
Non plus serieusement, je n'ai pes encore eu le temps de tester le code que vous avez poste Michel, car j'entre dans une periode cruciale car je dois soutenir une recherche (qui n'a aucun je precise avec l'etude pour laquelle j avais besoin de la macro) a la fin du mois. J'ai juste teste sSolversolve(true) pour un faible echantillon de valeurs (10) et je dois dire que je comprends maintenant ce que vous entendiez par la lenteur d'execution de mon code tel qu'il est actuellement.
Je vous tiens au courant, en ne manquant pas de vous remercier pour la precision de vos reponses (faut que je familliarise un peu plus avec la syntaxe)
Cordialement,
Tan
