规划求解工具

yiyiyicz

规划求解工具，也称作“假设分析”。工作表中公式计算结果，在变动单元格中的变量后，结果出现变化，例如：
1、根据已知结果倒推变量应赋予的初值：利润=销售额-成本-税收；而税收=利润*3%之类，可以用单变量求解、循环引用，也可规划求解。
2、根据已知参数和配比，寻找最佳组合方案：这种应用案例居多

http://club.excelhome.net/thread-296815-1-1.html

yiyiyicz

【例】

1. 
   
2. Sub tt()
   
3.    Dim i As Integer
   
4.    For i = 3 To 100
   
5.     SolverReset
   
6.     SolverOk SetCell:="$L$" & i, MaxMinVal:=3, ValueOf:="0", byChange:="$G$" & i & ":$J$" & i
   
7.     SolverAdd CellRef:="$G$" & i & ":$J$" & i, Relation:=4, formulaText:="整数"
   
8.     SolverAdd CellRef:="$G$" & i & ":$J$" & i, Relation:=1, formulaText:="10"
   
9.     SolverAdd CellRef:="$G$" & i & ":$J$" & i, Relation:=3, formulaText:="0"
   
10.     SolverOk SetCell:="$L$" & i, MaxMinVal:=3, ValueOf:="0", byChange:="$G$" & i & ":$J$" & i
    
11.     SolverSolve Userfinish = False
    
12.     Next i
    
13. End Sub
    
14. 
    

复制代码

前面加 SolverReset 设置重置
最后 SolverSolve Userfinish = False
byChange:="$G$" & i & ":$J$" & i  可以改用 cells( )

yiyiyicz

Q：已经在“加载项”加载了“规划求解”，为何在VBA中调用规划求解工具还是出现“子过程或函数未定义”错误？ A：必须建立对规划求解加载宏的引用，操作方法如下： 在VBE窗口中，单击“工具”→“引用，从“可使用的引用”列表框中选择“Solver.xla”或“Solver”复选框。 【注意】 1、菜单中的“引用”是灰色的？——可能是执行代码出错，先停止代码运行。 2、Excel 2003 则把Office 14改为Office 11 3、是引用Solver.xla，不是.dll

yiyiyicz

【例】
根据货物数量和纸箱规格优化装货

有一批货物需要用纸箱包装后运走，目前有5种规格的纸箱，分别可以装下24、16、12、8和5个货品。如何根据货品的数量来决定装箱的方案，以便尽量装满纸箱和尽量少用纸箱？

2010-4-19 14:43:16 上传
下载附件 (25.65 KB)




【分析】
这是一个典型的优化方案求解类型，使用规划求解功能求解最小值。
【模型构建】
如上图，
G5单元格公式：
=SUM(B5:F5)
H5单元格公式：
=SUMPRODUCT(B$4:F$4,B5:F5)-A5
分别求得所用纸箱数量和空位（比如拿规格为5的箱子装了3个货品，则空位为2）
I5单元格输入：
=G5+H5*1000
为何H5*1000——这只是一个意思，表示H5比G5重要，也就是说，能够刚好都装满箱子没有空位是前提，然后再是尽量少用箱子。

【规划求解】设置：



即：I5作为目标单元格，求“最小值”
B5：F5作为可变单元格，添加整数、>=0的约束；H5添加>=0的约束求解

yiyiyicz

根据货物数量和纸箱规格优化装货（续）
每次在A5单元格输入货品数量，再调用规划求解工具，这个操作很繁琐。因此，做了个按钮，在VBA中调用规划求解工具：
代码如下：

• Sub 求解()
• Range("I5").Formula = "=G5+H5*1000"
• SolverReset
•     SolverOk SetCell:="I5", MaxMinVal:=2, ValueOf:="0", byChange:="B5:F5"
•     SolverAdd CellRef:="B5:F5", Relation:=4, formulaText:="整数"
•     SolverAdd CellRef:="B5:F5", Relation:=3, formulaText:="0"
•     SolverAdd CellRef:="H5", Relation:=3, formulaText:="0"
•     SolverOptions AssumeLinear:=False
•     SolverSolve UserFinish:=True
•     SolverFinish KeepFinal:=1
• End Sub
  

Excel 2007的规划求解与2003版有较多变动，Engine=1在2007版中表示“非线性”求解，
在Excel 2003版中，对应为：SolverOptions AssumeLinear:=False

yiyiyicz

根据货物数量和纸箱规格优化装货（续）

【设置目标单元格和求解模式】
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版称“演化”）。

yiyiyicz

根据货物数量和纸箱规格优化装货（续）

【是否手工确认求解结果】
此时，如果是代码：

• Sub 求解()
• Range("I5").Formula = "=G5+H5*1000"
• SolverReset
•     SolverOk SetCell:=Range("I5"), MaxMinVal:=2, ValueOf:="0", byChange:=Range("B5:F5"), Engine:=1
•     Solveradd CellRef:=Range("B5:F5"), Relation:=4, formulaText:="整数"
•     Solveradd CellRef:=Range("B5:F5"), Relation:=3, formulaText:="0"
•     Solveradd CellRef:=Range("H5"), Relation:=3, formulaText:="0"
•     SolverSolve    '相当于按“求解”按钮
• End Sub
  

用SolverSolve作为结束，则会弹出“规划求解”完成后的确认对话框

需要用户手工点击“确认”后，才可以结束。这个比较麻烦，因而使用SolverSolve UserFinish:=True    直接代替手工按了“确认”按钮。

yiyiyicz

在VBA中通过调用SolverOptions函数来设置“规划求解”参数

《参见帮助》
MaxTime   Variant 类型，可选。规划求解解决问题所花费的最长时间（以秒为单位）。该值必须为正整数。
Iterations   Variant 类型，可选。迭代规划求解解决问题所花费的最长时间。该值必须为正整数。
Precision   Variant 类型，可选。一个介于 0（零）和 1 之间的数字，用于指定约束（包括整数约束）必须达到的精度。默认精度为 0.000001。小数位数越少（例如 0.0001），表示精度越低。一般而言，指定的精度越高（该数字越小），规划求解为进行求解而花费的时间越长。
AssumeLinear   Variant 类型，可选。如果为 True，则规划求解时将假定模型是线性的。这将加快求解速度，但仅应当用于模型中所有的关系均为线性的情况。默认值为 False。
StepThru   Variant 类型，可选。如果为 True，则在每次试解时都会导致规划求解暂停。使用 SolverSolve 函数的 ShowRef 参数可以在每次暂停时向规划求解传递一个宏运行。如果为 False，则在每次试解时不暂停规划求解。默认值为 False。
Estimates   Variant 类型，可选。指定用于估计每个一维基本变量初始值的方法：1 表示正切函数估值，而 2 表示二次方程估值。正切函数估值使用正切向量的线性外插法。二次方程估值使用二次外插法；这将有利于求解高度非线性的问题。默认值为 1（正切函数估值）。
Derivatives   Variant 类型，可选。指定目标函数和约束函数的偏导式估值使用向前差分还是中心差分：1 表示向前差分，2 表示中心差分。中心差分所需的工作表重新计算较多，但如果求解某些问题时出现“规划求解无法改善解”的信息，则使用中心差分将有所帮助。对约束函数在极限值附近快速变化的情况，应使用中心差分。默认值为 1（向前差分）。
SearchOption   Variant 类型，可选。使用“搜索”选项可指定每次迭代时所使用的搜索算法，该搜索算法将决定搜索的方向：1 表示牛顿搜索方法，2 表示共轭搜索方法。使用准牛顿法的牛顿法是默认的搜索方法。
IntTolerance   Variant 类型，可选。一个介于 0（零）和 100 之间的十进制数字，用于指定“整数最优”百分比公差。本参数仅适用于定义了整数约束的情况。它指定规划求解可在以下情况下停止：它找到可行整数解，该解的目标处于真正整数最优解的目标上最佳已知界限的此百分比范围内。较高的百分比公差有助于加快求解过程。
Scaling   Variant 类型，可选。如果目标或约束之间相差多个数量级，例如，基于数额达百万美元的投资最大程度地提高利润百分比，则将此选项设置为 True，让规划求解在计算期间在内部将目标和约束值重新缩放至类似的数量级。如果此选项为 False，则规划求解将使用原目标和约束值执行计算。默认值为 True。
Convergence   Variant 类型，可选。一个介于 0（零）和 1 之间的数字，用于指定“GRG 非线性求解”和“进化求解”方法的收敛度公差。对于 GRG 方法，当最后五次迭代中目标单元格值的相对改变量小于此公差时，规划求解将停止。对于“进化”方法，当 99% 或更多总体成员具有“拟合”值，且这些值的相对差值（百分比形式）小于此公差时，规划求解将停止。在这两种情况下，规划求解都将显示消息“规划求解收敛于当前解。满足所有约束。”
AssumeNonNeg   Variant 类型，可选。如果为 True，则规划求解假设所有在“约束”列表框中没有明确下限的决策变量单元格（这些单元格必须包含非负值）的下限为 0（零）。如果为 False，则规划求解仅使用在“约束”列表框中指定的限制。
PopulationSize    Variant 类型，可选。如果为 True，则规划求解假设所有在“约束”列表框中没有明确下限的决策变量单元格（这些单元格必须包含非负值）的下限为 0（零）。如果为 False，则规划求解仅使用在“约束”列表框中指定的限制。
RandomSeed    Variant 类型，可选。如果值为正整数，则为“进化求解”方法和多启动方法使用的随机数字生成器指定固定基数以实现全局优化。这意味着，规划求解每次在没有变化的模型上运行时都会找到同一解。如果值为零，则指定规划求解应在每次运行时为随机数字生成器使用不同的种子，这样，当它每次在没有变化的模型上运行时，都会生成不同的解。
MultiStart    Variant 类型，可选。如果为 True，则规划求解会在调用 SolverSolve 时针对“GRG 线性求解”方法使用多启动方法来实现全局优化。如果为 False，则规划求解仅在调用 SolverSolve 时调用一次“GRG 求解”方法，而不使用多启动。
RequireBounds    Variant 类型，可选。如果为 True，且任何变量都没有定义下限和上限，则“进化求解”方法和多启动方**立即通过调用 SolverSolve 来返回值 18。如果为 False，则这两种方**尝试在不限定所有变量的情况下解决问题。
MutationRate    Variant 类型，可选。一个介于 0（零）和 1 之间的数字，用于指定“进化求解”方法将对现有总体成员进行“突变”的速率。突变率越高，越会增加总体的多样性，并且生成的解越好。
MaxSubproblems    Variant 类型，可选。规划求解在包含整数约束的问题中以及通过“进化求解”方法解决的问题中浏览的子问题的最大数量。该值必须为正整数。MaxIntegerSols    Variant 类型，可选。规划求解在包含整数约束的问题中以及通过“进化求解”方法解决的问题中考虑的可行（或整数可行）解的最大数量。该值必须为正整数。
SolveWithout    Variant 类型，可选。如果为 True，则规划求解将忽略所有整数约束并解决问题的“缓和”。如果为 False，则规划求解在解决问题时使用整数约束。
MaxTimeNoImp    Variant 类型，可选。当使用“进化求解”方法时，规划求解在不查找要添加到总体中的明显改进解的情况下继续执行求解的最长时间（以秒为单位）。该值必须为正整数

yiyiyicz

【例】
程序里面两个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

有时规划求解运算超时，此时仍会跳出另一个对话框（超时选项对话框）

gjj136138139

好贴，多谢楼主。