Excel Solver VBA 不会让一个单元格受到两个约束（最小值和最大值）的限制

发布于 2024-12-23 08:42:33 字数 582 浏览 4 评论 0原文

我正在尝试解决一个优化问题，其中输入变量之一必须受到最小和最大约束的限制。因为我最后没有运行 SolverReset，所以事后我可以知道 Solver 忽略了第三个约束 ($F$5 <= 1)。我也能分辨出来，因为有时它会给我一个答案，其中 $F$5 > 。 1.

当我手动使用 Solver 时，我可以添加第三个约束。我尝试录制宏以查看缺少的内容，但我仍然感到困惑。我正在运行 Excel 2007。有什么想法吗？谢谢，

Public Sub SEDMSolver()

SolverReset
SolverAdd CellRef:="$F$5", Relation:=3, FormulaText:="0.1"
SolverAdd CellRef:="$F$4", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$F$5", Relation:=1, FormulaText:="1"
SolverOk SetCell:="$G$8", MaxMinVal:=2, ValueOf:="0", ByChange:="$F$4:$F$5"
SolverSolve userFinish:=True

End Sub

原文

I am trying to solve an optimization problem where one of the input variables must be bounded by a minimum and a maximum constraint. Because I don't run SolverReset at the end, I can tell afterwards that Solver is ignoring the third constraint ($F$5 <= 1). I can also tell because sometimes it gives me an answer where $F$5 > 1.

When I use Solver manually I'm able to add in the third constraint. I've tried recording the macro to see what I'm missing, but I'm still stumped. I'm running Excel 2007. Any ideas? Thanks,

Public Sub SEDMSolver()

SolverReset
SolverAdd CellRef:="$F$5", Relation:=3, FormulaText:="0.1"
SolverAdd CellRef:="$F$4", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$F$5", Relation:=1, FormulaText:="1"
SolverOk SetCell:="$G$8", MaxMinVal:=2, ValueOf:="0", ByChange:="$F$4:$F$5"
SolverSolve userFinish:=True

End Sub

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

红尘作伴 2024-12-30 08:42:33

求解器可能有点侥幸。尝试在第一行上方设置 MaxMinVal=2 行。当我运行解算器宏时，我总是必须将其设置在约束之上。有时它会抛出错误。

我尝试复制您的问题（使用 Excel 2010），但它对我有用。以 MxMinVal 开头和结尾。您可以尝试重新启动计算机并查看问题是否仍然存在。但你的代码确实对我有用，所有三个约束都加载。

我对您的代码的另一个一般建议是添加：

Application.Calculation = xlAutomatic

这会将计算变为自动。我在运行涉及解算器的更复杂的宏时将其关闭。

Solver can be a bit fluky. Try setting your MaxMinVal=2 line above the first. When I run solver macros, I always have to set that above the constraints. Occasionally it will throw errors.

I tried to replicate your issue (using excel 2010), but it works for me. With the MxMinVal at the beginning and end. You could try restarting your machine and excel and see if the issue remains. But your code does work for me, all three constraints load.

Another general suggestion I would have for your code is to add:

Application.Calculation = xlAutomatic

This will turn the calculations to automatic. I have had it get turned off while running more complex macros involving solver.

回复收藏 0 原文

丢了幸福的猪 2024-12-30 08:42:33

将某些单元格（例如 H9）设置为 1 并在代码中使用对此单元格的引用。
SolverAdd CellRef：=“$F$5”，关系：=1，FormulaText：=“$H$9”

回复收藏 0 原文

﹏半生如梦愿梦如真 2024-12-30 08:42:33

根据我使用求解器并尝试使用 VBA 使其自动化的经验，最好的解决方案是不自动化 Excel 的求解器，而是编写自己的迭代来求解目标。
例如

 For j = 1 To 100
 result = someCalculation
        If result > minConstraint Then
           result = j - 1
           Exit For
        Else
        End If
 Next j

From my experience using solver and trying to automate it with VBA, the best solution is to not automate Excel's sorver but write your own iteration to solve for your target.
for example

 For j = 1 To 100
 result = someCalculation
        If result > minConstraint Then
           result = j - 1
           Exit For
        Else
        End If
 Next j

回复收藏 0 原文

~没有更多了~