| 楼主 gouweicao78 |
相关阅读：如何解决VBA中调用规划求解工具出现子过程或函数未定义错误？ http://www.exceltip.net/thread-7261-1-1.html 掀开神秘面纱后发现美 规划求解工具是一个非常好用的工具，也称作“假设分析”，经常用于查看更改某些单元格中的变量对工作表中公式结果的影响，例如： 1、根据已知结果倒推变量应赋予的初值：利润=销售额-成本-税收；而税收=利润*3%之类，可以用单变量求解、循环引用，也可规划求解。 2、根据已知参数和配比，寻找最佳组合方案：这种应用案例居多，例如“我的测验”版【E马当先】等帖。 有一篇《掀开规划求解神秘的面纱》写的非常不错，大家可以去阅读。 美中不足 除了限制变量个数、求解时间有时候比较长外，一个美中不足的问题是，如果变量不多、算法也不难，但有许多需要求解的值，每一次改变都要重复操作，调出规划求解对话框、求解。 宏代码中的规划求解 反反复复操作的问题，首先，就想到了“录制宏”来解决。经过摸索，对在VBA中调用规划求解工具有了一些认识，不敢暗藏，分享如下： 【示例来源】根据货物数量和纸箱规格优化装货策略 http://www.exceltip.net/thread-7259-1-1.html （为节省资源，此处就不附了） 【模型建立】该帖也已说明。 【代码解释】本人是有名的VBA菜鸟，别笑话我啊
 |
| 2楼 gouweicao78 |
【设置目标单元格和求解模式】 SolverOk SetCell:=Range("I5")，设置目标单元格，此处为I5单元格 MaxMinVal:=2 ，设置求解最值模式，=1是求最大值，=2是求最小值，=3是“目标值”并设置目标值的具体数值ValueOf:="0"（目标值为0，本例可以不用） 【设置可变单元格】 byChange:=Range("B5:F5")，设置可变单元格为B5:F5 【添加约束条件】  Solveradd CellRef:=Range("B5:F5"), Relation:=4, formulaText:="整数" 添加约束条件，其中，Relation:=4 表示约束单元格与约束值的关系，1为<=，2为=，3为>=，4为INT，5为BIN，6为DIF formulatext则是约束的值。 【设置求解模型】 Engine:=1，所使用的“引擎”为1，表示 表示单工 LP 方法（线性规划），2 表示 GRG 非线性方法，或 3 表示进化方法（2010版称“演化”）。 【是否手工确认求解结果】 此时，如果是代码：
 需要用户手工点击“确认”后，才可以结束。这个比较麻烦，因而使用SolverSolve UserFinish:=True 直接代替手工按了“确认”按钮，呵呵。 |
| 3楼 gouweicao78 |
上面是常用的“规划求解”参数设置，此外，规划求解还有一个“选项”按钮，参数一般是默认的，也可以在VBA中通过调用SolverOptions函数来设置。 具体可参考帮助中以下信息： SolverOptions)MaxTime, Iterations, Precision, AssumeLinear, StepThru, Estimates, Derivatives, SearchOption, IntTolerance, Scaling, Convergence, AssumeNonNeg, PopulationSize, RandomSeed, MultiStart, RequireBounds, MutationRate, MaxSubproblems, MaxIntegerSols, SolveWithout, MaxTimeNoImp) [tr]MaxTime Variant 类型，可选。规划求解解决问题所花费的最长时间（以秒为单位）。该值必须为正整数。 [/tr] [tr]Iterations Variant 类型，可选。迭代规划求解解决问题所花费的最长时间。该值必须为正整数。 [/tr] [tr]Precision Variant 类型，可选。一个介于 0（零）和 1 之间的数字，用于指定约束（包括整数约束）必须达到的精度。默认精度为 0.000001。小数位数越少（例如 0.0001），表示精度越低。一般而言，指定的精度越高（该数字越小），规划求解为进行求解而花费的时间越长。[/tr] [tr]AssumeLinear Variant 类型，可选。如果为 True，则规划求解时将假定模型是线性的。这将加快求解速度，但仅应当用于模型中所有的关系均为线性的情况。默认值为 False。[/tr] [tr]StepThru Variant 类型，可选。如果为 True，则在每次试解时都会导致规划求解暂停。使用 SolverSolveShowRef 参数可以在每次暂停时向规划求解传递一个宏运行。如果为 False，则在每次试解时不暂停规划求解。默认值为 False。[/tr] [tr]Estimates Variant 类型，可选。指定用于估计每个一维基本变量初始值的方法：1 表示正切函数估值，而 2 表示二次方程估值。正切函数估值使用正切向量的线性外插法。二次方程估值使用二次外插法；这将有利于求解高度非线性的问题。默认值为 1（正切函数估值）。[/tr] [tr]Derivatives Variant 类型，可选。指定目标函数和约束函数的偏导式估值使用向前差分还是中心差分：1 表示向前差分，2 表示中心差分。中心差分所需的工作表重新计算较多，但如果求解某些问题时出现“规划求解无法改善解”的信息，则使用中心差分将有所帮助。对约束函数在极限值附近快速变化的情况，应使用中心差分。默认值为 1（向前差分）。[/tr] [tr]SearchOption Variant 类型，可选。使用“搜索”选项可指定每次迭代时所使用的搜索算法，该搜索算法将决定搜索的方向：1 表示牛顿搜索方法，2 表示共轭搜索方法。使用准牛顿法的牛顿法是默认的搜索方法。 [/tr] [tr]IntTolerance Variant 类型，可选。一个介于 0（零）和 100 之间的十进制数字，用于指定“整数最优”百分比公差。本参数仅适用于定义了整数约束的情况。它指定规划求解可在以下情况下停止：它找到可行整数解，该解的目标处于真正整数最优解的目标上最佳已知界限的此百分比范围内。较高的百分比公差有助于加快求解过程。[/tr] [tr]Scaling Variant 类型，可选。如果目标或约束之间相差多个数量级，例如，基于数额达百万美元的投资最大程度地提高利润百分比，则将此选项设置为 True，让规划求解在计算期间在内部将目标和约束值重新缩放至类似的数量级。如果此选项为 False，则规划求解将使用原目标和约束值执行计算。默认值为 True。[/tr] [tr]Convergence Variant 类型，可选。一个介于 0（零）和 1 之间的数字，用于指定“GRG 非线性求解”和“进化求解”方法的收敛度公差。对于 GRG 方法，当最后五次迭代中目标单元格值的相对改变量小于此公差时，规划求解将停止。对于“进化”方法，当 99% 或更多总体成员具有“拟合”值，且这些值的相对差值（百分比形式）小于此公差时，规划求解将停止。在这两种情况下，规划求解都将显示消息“规划求解收敛于当前解。满足所有约束。”[/tr] [tr]AssumeNonNeg Variant 类型，可选。如果为 True，则规划求解假设所有在“约束”列表框中没有明确下限的决策变量单元格（这些单元格必须包含非负值）的下限为 0（零）。如果为 False，则规划求解仅使用在“约束”列表框中指定的限制。[/tr] [tr]PopulationSize Variant 类型，可选。如果为 True，则规划求解假设所有在“约束”列表框中没有明确下限的决策变量单元格（这些单元格必须包含非负值）的下限为 0（零）。如果为 False，则规划求解仅使用在“约束”列表框中指定的限制。 [/tr] [tr]RandomSeed Variant 类型，可选。如果值为正整数，则为“进化求解”方法和多启动方法使用的随机数字生成器指定固定基数以实现全局优化。这意味着，规划求解每次在没有变化的模型上运行时都会找到同一解。如果值为零，则指定规划求解应在每次运行时为随机数字生成器使用不同的种子，这样，当它每次在没有变化的模型上运行时，都会生成不同的解。[/tr] [tr]MultiStart Variant 类型，可选。如果为 True，则规划求解会在调用 SolverSolveFalse，则规划求解仅在调用 SolverSolve [tr]RequireBounds Variant 类型，可选。如果为 True，且任何变量都没有定义下限和上限，则“进化求解”方法和多启动方**立即通过调用 SolverSolveFalse，则这两种方**尝试在不限定所有变量的情况下解决问题。[/tr] [tr]MutationRate Variant 类型，可选。一个介于 0（零）和 1 之间的数字，用于指定“进化求解”方法将对现有总体成员进行“突变”的速率。突变率越高，越会增加总体的多样性，并且生成的解越好。[/tr] [tr]MaxSubproblems Variant 类型，可选。规划求解在包含整数约束的问题中以及通过“进化求解”方法解决的问题中浏览的子问题的最大数量。该值必须为正整数。[/tr] [tr]MaxIntegerSols Variant 类型，可选。规划求解在包含整数约束的问题中以及通过“进化求解”方法解决的问题中考虑的可行（或整数可行）解的最大数量。该值必须为正整数。[/tr] [tr]SolveWithout Variant 类型，可选。如果为 True，则规划求解将忽略所有整数约束并解决问题的“缓和”。如果为 False，则规划求解在解决问题时使用整数约束。[/tr] [tr]MaxTimeNoImp Variant 类型，可选。当使用“进化求解”方法时，规划求解在不查找要添加到总体中的明显改进解的情况下继续执行求解的最长时间（以秒为单位）。该值必须为正整数。[/tr] |
| 4楼 kuaiban |
学习了！非常感谢分享，解决了我的问题！ |
| 5楼 zhyou |
谢谢 ，期待提供更多的好东东 |
| 6楼 huangniuniuniu |
我的调用规划求解是这样写的Sub Solve() SolverOk SetCell:="$C$25", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$15:$C$20" SolverSolve UserFinish:=True End Sub Sub Doit() Range("Results").ClearContents For counter = 1 To 30 Range("constant") = 0.1 + counter * 0.1 Solve Application.SendKeys ("{Enter}") Range("Results").Cells(counter, 1) = ActiveSheet.Range("constant") Range("Results").Cells(counter, 2) = ActiveSheet.Range("portfolio_sigma") Range("Results").Cells(counter, 3) = ActiveSheet.Range("portfolio_mean") Range("Results").Cells(counter, 4) = ActiveSheet.Range("x_1") Range("Results").Cells(counter, 5) = ActiveSheet.Range("x_2") Range("Results").Cells(counter, 6) = ActiveSheet.Range("x_3") Range("Results").Cells(counter, 7) = ActiveSheet.Range("x_4") Range("Results").Cells(counter, 8) = ActiveSheet.Range("x_5") Range("Results").Cells(counter, 9) = ActiveSheet.Range("x_6") Next counter End Sub 但是运行后求出来不是我要的这，只是把那个从-o.o4到0.11求出来了是怎么回事 |
| 7楼 短笛 |
按照楼主的方法，出现了如图的问题，求高手解释。谢谢！使用的是office2003.1.JPG |
| 8楼 gouweicao78 |
Excel 2007的规划求解与2003版有较多变动，Engine=1在2007版中表示“非线性”求解，在Excel 2003版中，对应为：
修改代码如下：
|
| 9楼 hhzjxss |
谢谢分享，学习一下！ |
| 10楼 jundao |
正好用到 ，lz 真神人也 |
| 11楼 jundao |
lz 太牛了 excel 真是博大精深 |
| 12楼 ling0924 |
看这个帖子受益匪浅！ 我的程序里面两个SolverAdd条件，但是运行结果发现其中一个条件Relation:=2, FormulaText:=1总是没有满足。请教一下，为什么会这样子？有多个限制条件的时候这个程序是不是有缺陷呢~~我改怎么解决呢？ 先谢谢啦~~希望有好心人能赶紧来解救我一下~~ Sheet5.Activate SolverReset SolverOptions precision:=0.001 SolverOK SetCell:=Range("D22"), _ MaxMinVal:=2, _ ByChange:=Range("E8:E17") SolverAdd CellRef:=Range("E18"), _ Relation:=2, _ FormulaText:=1 SolverAdd CellRef:=Range("E8:E17"), _ Relation:=3, _ FormulaText:=0 SolverSolve UserFinish:=True |
| 13楼 baijianzhong |
有时规划求解运算超时，此时仍会跳出另一个对话框（超时选项对话框），如果我每次都选择“停止”选项，因而不需要该对话框跳出，该如何做？请问各位大侠？谢谢！ |