Excel Solver.docx

Excel Solver.docx

《Excel Solver.docx》由会员分享，可在线阅读，更多相关《Excel Solver.docx（17页珍藏版）》请在冰豆网上搜索。

ExcelSolver

ExcelSolver（3）：

求解步骤和数据组织

作者：

XLFinance   来源：

XLFinance     打印  邮寄  返回

求解的一般步骤

使用Excel规划求解工具解决优化问题的基本步骤包括：

1） 识别问题，明确优化方程模型

2） 组织数据

3） 确定模型决策变量所在单元格

oSolver在求解问题过程中会不断调整决策变量的取值

o可变单元格的数量不能超过200个

o使用引用单元格、命名或区域识别可变单元格

o必须位于活动的工作表上

4） 确定包含Excel公式（代表依赖于决策变量的目标函数）的目标单元格：

o目标单元格必须是引用单元格或一个区域名称

o目标单元格在多数情况下应包含公式

o必须指定求解的目标为最大值、最小值或一个指定的数值

5） 确定模型约束条件

6） 通过规划求解工具实施

正如上文对局部最优和全局最优的解释，不同的初始值可能导致不同的求解结果。

复杂的非线性问题中，在识别问题和组织数据阶段可能还需要对问题的预分析，确定适当的决策变量初始值。

运行过程和结果报告求解

运行Solver后，Solver会处于求解状态直至：

o找到一个方案；

o确定无法找到解决方案；

o运行时间超出最高限制；

找到最优解

如找到最优解，则Solver将返回对话框，提示用户选择下一步的操作。

可选择的操作包括：

将Solver的求解结果写入可变单元格区域或放弃Solver求解结果，将可变单元格恢复至原始值状态。

Solver返回的最优解结果可能包括以下不同的类型：

o可行解：

满足所有的约束，但如有可能，Solver会继续求解更优的解；

o局部最优：

在初始值的“邻近地带”没有找到更好的解决方案；

o全局最优：

不存在更好的解决方案；

无法找到解决方案

有多个原因可能导致Solver无法求解，其中部分是计算原因，部分则是操作原因，如为后者则应根据提示错误信息进行相应调整。

结果报告

在规划求解结果对话框中可以选择输出的报告类型，其中包括：

求解报告、敏感性报告和极值报告三种。

Solver会自动添加相关工作表并命名。

求解报告是对求解结果的综述，包括目标单元格和可变单元格的初始值和最终值、约束及相关信息。

敏感性分析给出优化结果对输入参数微小变动的敏感度测量，不适用于整型约束模型。

极值报告给出目标和可变单元格的上下极值和目标值，不适用于整型约束模型。

模型识别、变量界定和数据组织

模型识别是使用Solver求解之前的工作，多数情况下，清晰和准确的模型识别是顺利实施规划求解的条件，在某些复杂问题中，也是获得可靠结果的重要前提。

本例通过一个投资决策案例说明如何根据目标、决策和约束分析框架，进行模型的识别和分析。

假设给出6种公司债券的预期回报、剩余期限和评级基本信息，以及投资方案的基本约束条件：

首先是界定决策变量，此例中的决策变量可以是各债券的投资比例，也可以是投资金额。

其次界定目标函数，在以投资金额为决策变量的前提下，目标函数是各债券的预期回报和投资数量的乘积之和的最大化。

最后是将文字描述的约束条件转换为数学表达式。

将界定清楚的模型展现在工作表中，并在此基础上启动规划求解，数据和逻辑关系的良好组织有助于理解和快速准确的实施模型。

本例最终的数据组织如下：

ExcelSolver（4）：

应用案例

作者：

XLFinance   来源：

XLFinance     打印  邮寄  返回

案例分析：

极值

假设一个简单的极值问题：

考虑方程f=x^2-x+2，求在[1,5]区间内使f最小化的x值。

该问题在更专业的数值分析软件中可能很容易得到解决，例如在Matlab中只需在命令行状态下输入“fminbnd（x.^2-x+2,-1,5）”即可求出最优值。

在Excel中则需通过上文中的各个步骤完成求解。

目标函数：

f（x）最小化

决策变量：

x

约束条件：

x>=-1,x<=5

在Excel中组织数据如下，同时画出了在不同X取值情形下的Y值，以方便同规划求解结果的验证。

在目标单元格、可变单元格和约束区域分别引用输入工作表中设置好的对应单元格：

案例分析：

最优分配

延续上文中用于说明规划求解的模型识别和数据组织的债券投资案例。

此问题的目标是在给定的资金约束和投资约束下，确定一个预期回报最大的投资方案。

决策变量即投资金额位于C列，初始值为0

D列为投资限额，计算公式为单个约束比例成乘以投资总额，列E是给定的债券预期回报，列F是给定的剩余期限，列G利用逻辑判断函数IF将剩余期限数据转换为逻辑参数：

剩余期限在10年或以上则为长期债券，否则为非长期债券。

列H是给定的评级参数，列I利用逻辑判断函数IF将评级数据转换为逻辑参数：

评级大于2的归为高风险债券。

第12行是中间计算参数，包括实际的投资总额（C12）、根据投资额和各自期望回报计算出的投资回报总额（E12）、长期债投资额（G12）、高风险债券投资额（I12），其中E12即为本例的目标单元格：

投资回报的最大化。

第13行是约束参数，包括对投资总额、长期债投资额和高风险债券投资额的约束。

整个模型的数据组织区分了不同的输入参数类型，包括给定的参数、中间参数、决策变量、目标变量和约束变量。

并通过函数和公式将给定参数转换为可用于工作表计算的中间参数。

案例分析：

整型约束和选择变量约束

规划求解的目标单元格必须是包含公式的单个单元格，决策变量来自工作表的区域，约束变量则一般需界定为完整的表达式，规划求解提供了不同的表达式关系运算符，其中整型约束和选择变量约束是比较是两种特殊类型的优化约束。

本例考虑一个项目选择的规划求解案例，在多个具有不同现金流结构的项目中进行取舍，决策变量的最终取值只能是1或0，分别代表选择或放弃。

正如下图所示，在6个不同的项目中，C列给出了决策变量的初始值0，之后各列是项目的净现值和每年的资本需求，第17行利用函数SumProduct计算各项目在第1至5年的资本需求合计，第18列是可用资本的最高约束。

本例的目标单元格位于D20，项目的净现值合计，同样利用SumProduct函数计算，如项目未被选择，则C列为零，和其净现值的乘积仍然为零。

由于决策变量只能选择0和1，因此在约束条建设之中需要指定C11:

C16所在的决策变量为二进制：

在关系符号中选择“bin”后确定即可。

案例分析：

非线性最小二乘

对于线性问题，使用Excel的回归分析工具可以返回详尽的统计信息，包括估算的参数值和包括标准差等在内的回归统计值，而对于非线性问题，虽可通过Solver求解，但Solver并不返回具体的统计值。

考虑一个非线性方程：

Y=a+b[1-exp（cx）]，其中a、b和c为待估参数，由于存在参数非线性，因此该方程无法通过回归分析工具求解，但可以借助Solver解出。

在工作表中输入给定的x和y数据样本，而后假定决策变量即a,b和c均为1，将其代入方程计算拟和的y’，进一步计算y和y’的残差平方和，数据组织如下所示：

显然，目标单元格为D14即残差平方和的最小化，决策变量为B15:

B17，本例没有约束条件。

规划求解返回了一个结果，将原始数据样本和拟合后的方程作图如下：

从效果看，规划求解返回的结果不能令人满意，这和前文提到的局部最优和全局最优问题有关。

ExcelSolver（5）：

多目标优化和初始值选择

作者：

XLFinance   来源：

XLFinance     打印  邮寄  返回

补充问题：

多目标优化

ExcelSolver的目标单元格只能为一个，意味着只能对单个目标进行优化，如利润的最大化或成本的最小化。

在存在多个优化目标的问题中，不能直接使用Solver求解，但通过模型的分析和转换，仍可以使用Solver求解。

考虑一个生产案例，存在的多个目标包括生产成本最小化、污染最小和事故最小化。

在使用Solver求解之前，首先可以进行极值目标转换，例如生产成本的最小化转换为生产成本约为X1，污水产量最低转换为污水产量约为X2，事故最小化转换为事故发生数约为X3。

继续利用Solver对上述三个目标分别进行优化，求出最优的X1,X2和X3值。

而后将目标函数重新定义为最优目标值即上一步中解出的X1、X2和X3和待解的实际目标值的百分比偏差之和最小化。

由于X1、X2和X3已知，新的决策变量标记为X1’、X2’和X3’，则目标函数为二者间的百分比偏差之和。

如上述三个目标存在优先次序关系，可进一步对此百分比偏差赋予不同的权重，以反映不同目标的不同优先级别，而后通过Solver求解出X1’、X2’和X3’。

补充问题：

选择初始值

如果数据样本干扰较大，或选择的决策变量初始值偏离最优估计值较远，则优化结果很可能陷入局部最优而非全局最优。

多数情况下，通过对数据样本的绘图有助于识别局部或全局最优，例如拟合效果较差可能表明局部最优解或者模型不准确。

另外可以借助于估计参数的标准差加以判断，一般而言，如果解出的是局部最优，标准差一般较大。

例如在前面的非线性最小二乘案例中，给定三个决策变量的初始值为1，通过Solver工具返回了一个最优解，其中：

a=15.25，标准差为9.27，9.27/15.25=61%

b=14.43，标准差为9.17，9.17/14.43=64%

c=-0.5371，标准差为0.284，0.5371/0.284=53%

而在全局最优解下，上述比率分别为1.1%，3.9%和14%。

在运行规划求解之前分析数据样本并选择合理的初始值，将有助于发现全局最优解。

延续上例，如将决策变量初始值设定为25.5，5.5和-0.12，规划求解返回不同的结果，通过数据绘图可以发现拟合效果大大改善：

在初始值选择的问题上，对不同复杂程度的问题有不同的方法，在本例中，通过绘图和简单的数学计算就可以找到适当的初始值。

给定本例的方程形式，可以看出当x=0时，y=a，因此a的初始值为25.5，进一步的当x值很大时，y收敛于a+b即31，因此可以设定b的初始值为5.5，为了确定c的初始值，可以对于方程取对数得：

转换后的是一个以c为斜率的线性方程形式，很容易可以求出c值为-0.12。

ExcelSolver（6）：

Solver和VBA开发

作者：

Microsoft   来源：

Microsoft     打印  邮寄  返回

ExcelSolver除了可用于界面形式的调用和操作之外，同时提供了VBA函数接口，可以在程序中使用Solver求解各类问题，实现优化工作的自动化。

1、如何在VBA宏中使用ExcelSolver函数

在VBA宏代码中使用ExcelSolver之前，首先需要在VBA工程中设定对Solver的引用，引用步骤如下：

1） 进入VBA编辑器

2） 在工具菜单中选择“引用”

3） 在可用引用列表中，找到并选中“Solver”后确定

如果在列表中未发现Solver，可以通过“浏览”，手工定位至Solver所在目录，例如：

C:

ProgramFilesMicrosoftOfficeOfficeLibrarySolver，选择Solver.xla后确定即便可。

2、在VBA宏中创建和求解ExcelSolver模型

ExcelSolver提供了许多函数接口，其中最基本的是：

SolverOk，SolverSolve和SolverFinish。

3、SolverOK函数

SolverOK函数界定一个基本的ExcelSolver模型，通常是构建规划求解模型时所需要使用的第一个函数，使用SolverOK函数等同于在Excel中点击“工具”菜单中的“规划求解”，而后在对话框中设置规划求解参数。

SolverOK的语法形式如下：

SolverOK（SetCell,MaxMinVal,ValueOf,ByChang），其中：

SetCell指定目标单元格地址；

MaxMinVal指定目标单元格的优化类型，1为最大值，2为最小值，3为指定值；

ValueOf指定目标单元格的目标值，该参数仅在MaxMinVal参数设定为3时使用，如MaxMinVal指定为1或2，则可忽略此参数；

ByChang指定可变单元格即决策变量。

以上参数和规划求解界面中的选项对应如下：

4、SolverSolve函数

SolverSolve函数根据SolverOK指定的参数求解模型。

执行SolverSolve函数等同于点击规划求解界面中的“求解”按钮。

SolverSolve的函数语法如下：

SolverSolve（UserFinish,ShowRef），其中：

UserFinish指定是否希望提示用户完成模型求解，将此参数设定为TRUE则规划求解将不返回求解结果对话框，反之则返回该对话框；

ShowRef参数识别在ExcelSolver返回中间结果时所需调用的宏。

5、SolverFinish函数

SolverFinish函数指定如何处理规划求解结果以及生成的求解结果报告类型。

SolverFinish的函数语法如下：

SolverFinish（KeepFinal,ReportArray）

KeepFinal指定保留或放弃规划求解结果，如参数值为1则保留结果，参数值为2则恢复最初值；

ReportArray指定返回的报告类型，参数值为1则创建结果报告，参数值为2则创建敏感性分析报告，参数值为3则创建极值报告。

SolverFinish的以上参数和界面对应如下：

6、实例

假定一个简单的平方根模型y=x^2，x为决策变量，假定初始值为2，在单元格A1中输入2，在A2中输入公式：

“=A1^2”。

现要求A2所在的目标单元格为50，求可变单元格A1的值。

以下是求解代码：

SubFind_Square_Root（）

    SolverOKSetCell:

=Range（A2）,MaxMinVal:

=3,ValueOf:

=50,_ByChange:

=Range（A1） 

    SolverSolveUserFinish:

=True 

    SolverFinishKeepFinal:

=1

EndSub

其中SetCell确定目标单元格在A2，优化类型为3，同时通过参数ValueOf设定目标值为50，ByChange设定可变单元格为A1，之后使用SolverSolve和SolverFinish函数，设定不返回对话框并直接保存求解结果。

如需由用户指定目标值，只需稍微修改代码，将ValueOf参数的值改变为用户输入即可，如：

val=Application.InputBox（prompt:

=输入目标值:

Type:

=1）

SolverOKSetCell:

=Range（A2）,MaxMinVal:

=3,ValueOf:

=val,_ByChange:

=Range（A1）

7、如何创建结果报告

第一部分已经介绍，ExcelSolver提供三种不同的输出报告供选择，这些报告按单独的工作表创建，在VBA代码中通过SolverFinish函数指定输出报告的类型，使用方法是指定函数的第2个参数ReportArray。

例如，如需创建极值报告，则将SolverFinish函数语法修改为：

SolverFinishKeepFinal:

=1,ReportArray:

=Array（3）

如需返回多个报告，则修改为如下形式：

SolverFinishKeepFinal:

=1,ReportArray:

=Array（1,2）

8、如何在循环语句中使用ExcelSolver函数

某些情况下可能需要在循环语句中重复调用Solver，如需要对目标单元格求解不同的数值时，一个典型的案例是投资组合的有效边界，指定不同的期望回报或标准差水平并重复优化。

以下是修改过的循环调用代码：

Setw=Worksheets.Add

w.Range（C1）.Value=2

w.Range（C2）.Formula==C1^2

Fori=1To10

    SolverOkSetCell:

=Range（C2）,ByChange:

=Range（C1）,_MaxMinVal:

=3,ValueOf:

=i

    SolverSolveUserFinish:

=True

    w.Cells（i,1）=i

    w.Cells（i,2）=Range（C1）

    SolverFinishKeepFinal:

=2

Next

w.Range（C1:

C2）.Clear