§9.6 Excel软件“规划求解”的使用
用Excel软件的“规划求解”功能可以方便地求解线性规划、整数规划和非线性规划问题。但如果安装Office 97时采用的是典型安装方法，则【工具】菜单中是无“规划求解”功能项的。可参照§2.8中介绍的方法将未安装的组件安装完整。
下面以第八章例8.1为例介绍用Excel求解线性规划的操作步骤和运行输出结果的分析。
求解线性规划的操作过程
1．输入数据、公式和说明文字
（1）在工作表中按图9.7所示格式输入必要的说明文字（图中粗体字部分）和LP模型的原始数据（图中虚线框所示单元格内，注意并不需要化为标准型）；图中F4是放置目标函数的单元格，B5:D5是放置决策变量X1、X2、X3（既“可变单元格”）的区域。
图 9.7
（2）在F4单元格内输入目标函数X0的计算公式：
=B4*B5+C4*C5+D4*D5
或 =SUMPRODUCT（B4:D4,B5:D5）
其中SUMPRODUCT（）函数返回两个或多个区域（即数组）中对应单元格乘积之和的值。该函数可在Excel的“数学和三角函数”中找到。
在E8单元格中输入第一个约束条件左端的计算公式：
=B8*$B$5+ C8*$C$5+D8*$D$5
或 = SUMPRODUCT（B8:D8,$B$5:$D$5）
然后拖曳E8的填充柄将公式复制到E9、E10单元格（注意公式中的B5、C5、D5或B5:D5要使用绝对引用）。
当模型中的变量数较多时，使用SUMPRODUCT（）函数可大大加快以上两个公式的输入速度。
说明：图中粗线框是表示要输入公式的单元格。用Excel求解线性规划的数据输入格式可由用户自行设计，但以上介绍的格式不仅与我们所熟悉的LP模型相似，便于理解和使用；而且便于在对话框中输入约束条件。按以上格式输入说明文字后，还可以使系统所输出的三个运行结果报告更具可读性。
2．选【工具】→“规划求解”，“打开规划求解参数”对话框，见图9.8。
图 9.8
（1）在“设置目标单元格”文本框中输入目标单元格（建议用鼠标选定的方法输入，下同），并选系统默认的“最大值”单选纽；
（2）在“可变单元格”文本框中输入B5:D5（既指定决策变量所在的单元格）；
3．单击“约束”框中的〈添加〉按纽，打开“添加约束”对话框，见图9.9。
图 9.9
（1）在“单元格引用位置”文本框中输入E8:E10；打开约束类型下拉列表框，选“〈=”；在“约束值”文本框中输入F8:F10；
（2）单击〈添加〉按纽，再输入非负性约束 B5:D5>=0（方法同上）；
说明：对有整数约束要求的决策变量，应再将相应的可变单元格设为“int”约束（即求解纯整数规划或混合整数规划问题）；对模型中的0-1变量，则应再将相应的可变单元格设为“bin”约束（即二进制变量）。
（3）单击〈确定〉，返回“规划求解参数”对话框，见图9.10。
图 9.10
单击〈选项〉按纽，打开“规划求解选项”对话框，见图9.11。
图 9.11
选“采用线性模型” 复选框（选择该选项后，系统将使用单纯形法求解，否则就使用非线性规划的算法求解，不仅速度慢，精度低，而且输出的敏感性报告内容也少）。
单击〈确定〉，系统运算结束后打开“规划求解结果”对话框，见图9.12。
图 9.12
4．在“报告”列表框中选定所有3个报告，并单击〈确定〉，系统在工作表中显示运行结果并生成3个输出报告的工作表，见图9.13。
图9.13
二．输出结果分析
由图9.13，可知本问题的最优解为：
X1*=0，X2*=100，X3*=230，X0*=1350
即每天生产乙产品100件，丙产品230件，不生产甲产品，每天总利润为1350元。在以上最优生产方案下，工序1和2的能力都已用完，而工序3则每天还有20分钟的剩余能力（仅用去400分钟）。
系统输出的“运算结果报告”、“敏感性报告”和“极限值报告”三张工作表分别见图9.14、图9.15、图9.16。
图 9.14
图9.14所示“运算结果报告”中三道工序约束的“型数值”给出的是最优解中松弛变量的值，即三道工序时间的富裕量。
图 9.15
图9.15所示“敏感性报告”中“可变单元格”下的“递减成本”给出的是最优单纯形迭代中各决策变量检验数的负值；“允许的增量”和“允许的减量”则给出了在不影响当前最优基的条件下各决策变量目标函数系数cj的可变动范围；而“约束”下的“阴影价格”则给出了各种资源的“影子价格”，其含义将将在第十章中介绍。“允许的增量”和“允许的减量”则给出了在不影响当前最优基的条件下各有限资源数量（约束条件右端常数）的可变动范围。以上敏感性分析的内容将在第十一章中详细介绍。
图 9.16
图9.16所示“极限值报告”给出了各决策变量分别取下限和上限值时对目标函数的影响。
三．关于“规划求解选项”对话框中各可选项的说明：
“最长运算时间”——可输入的最大值为32767（秒），默认值100（秒）可以满足大多数小型规划求解要求。
“迭代次数”——设定求解过程中迭代运算的次数。可输入的最大值为 32767，默认值100次，可满足大多数小型规划求解要求。
“精度”——输入用于控制求解精度的数字，以确定约束条件单元格中的数值是否满足目标值或上下限。默认值为 0.000001 。
“允许误差”——输入满足整数约束条件的目标单元格求解结果与最佳结果间的允许百分偏差。此选项只应用于具有整数约束条件的问题。默认值为5%。
“收敛度”——输入收敛度数值，当最近五次迭代时，目标单元格中数值的变化小于“收敛度”编辑框中设置的数值时，“规划求解”停止运行。收敛度只应用于非线性规划问题，默认值为0.001
“采用线性模型”——当模型中的所有关系都是线性的，并且希望解决线性优化问题或对非线性问题进行线性逼近时，选中此复选框可加速求解进程。
“显示迭代结果”——如果选中此复选框，每进行一次迭代后都将中断“规划求解”，并显示当前的迭代结果。
“自动按比例缩放”——当输入和输出值数量差别很大时，可以使用此功能。
“假定非负”——对于在“添加约束”对话框的“约束值”编辑框中没有设置下限的可变单元格，假定其下限为 0（零）。
“正切函数”、“二次方程”、“向前差分”、“中心差分”、“牛顿法”、“共轭法”是应用于求解非线性规划问题的各选项，在此不作介绍。
〈装入模型〉——将打开“装入模型”对话框，输入对所要调入模型的引用。
〈保存模型〉——将打开“保存模型”对话框，输入模型的保存位置。只有当需要在工作表上保存多个模型时，单击此命令。第一个模型会自动存储。
