《医学统计学》课程教学资源：第九章（9-6）Excel规划求解的使用

§9.6 Excel软件“规划求解”的使用 用 Excel软件的“规划求解”功能可以方便地求解线性规划、整数规划和非线 性规划问题。但如果安装 Office97时采用的是典型安装方法,则【工具】菜单中是 无“规划求解”功能项的。可参照§2.8中介绍的方法将未安装的组件安装完整。 下面以第八章例8.1为例介绍用 Excel求解线性规划的操作步骤和运行输出结 果的分析。 求解线性规划的操作过程 1.输入数据、公式和说明文字 (1)在工作表中按图97所示格式输入必要的说明文字(图中粗体字部分)和 LP模型的原始数据(图中虚线框所示单元格内,注意并不需要化为标准型);图中 F4是放置目标函数的单元格,B5:D5是放置决策变量X1、X2、X3(既“可变单元 格”)的区域 XlicrosoftErcel-定量法上机案例x1s ]文件②)编据)视图Q)插入g)格式Q)工具)数据①)窗口() 帮助0D 性规划的一般求解方法 3 产品甲x产品乙产品丙x3 4单位利润 引利润总额 5产量 a工序能力 8工序1 9工序2 460 10工序3 图9.7 (2)在F4单元格内输入目标函数X0的计算公式: B4*B5+C4*C5+D4*D5 或 =SUMPRODUCT(B4: D4.B5: D5) 其中 SUMPRODUCT()函数返回两个或多个区域(即数组)中对应单元格乘 积之和的值。该函数可在 Excel的“数学和三角函数”中找到 (1)在E8单元格中输入第一个约束条件左端的计算公式: B8*$BS5+C8*$CS5+D8*$D$5

§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 的“数学和三角函数”中找到。 （1）在 E8 单元格中输入第一个约束条件左端的计算公式： =B8*$B$5+ C8*$C$5+D8*$D$5

或 SUMPRODUCT (B8: D8SB$5: SD$5) 然后拖曳E8的填充柄将公式复制到E9、E10单元格(注意公式中的B5、C5、D5 或B5D5要使用绝对引用)。 当模型中的变量数较多时,使用 SUMPRODUCT()函数可大大加快以上两个 公式的输入速度 说明:图中粗线框是表示要输入公式的单元格。用 Excel求解线性规划的数据 输入格式可由用户自行设计,但以上介绍的格式不仅与我们所熟悉的LP模型相似, 便于理解和使用;而且便于在对话框中输入约束条件。按以上格式输入说明文字后, 还可以使系统所输出的三个运行结果报告更具可读性。 2.选【工具】→“规划求解”,“打开规划求解参数”对话框,见图98, 设置目标单元格):4 匚求解() 等于:G最大值思)C最小值)C值为ol 可变单元格① 压Bg5:55 推测G 「更改c 全部重设」 册除 图98 (1)在“设置目标单元格”文本框中输入目标单元格(建议用鼠标选定的方法 输入,下同),并选系统默认的“最大值”单选纽; (2)在“可变单元格”文本框中输入B5D5(既指定决策变量所在的单元格); 3.单击“约束”框中的〈添加〉按纽,打开“添加约束”对话框,见图99。 添加约束 单元格引用位置 勺束值c E3:E3105 F8:$F1 匚确定「取消」添加()」帮助00 图99 (1)在“单元格引用位置”文本框中输入E8E10:打开约束类型下拉列表框 选“〈=”;在“约束值”文本框中输入F8F10;

或 = 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)单击〈添加〉按纽,再输入非负性约束B5D5>=0(方法同上); 说明:对有整数约束要求的决策变量,应再将相应的可变单元格设为“int”约 束(即求解纯整数规划或混合整数规划问题):对模型中的0-1变量,则应再将相应 的可变单元格设为“bin”约束(即二进制变量)。 (3)单击〈确定),返回“规划求解参数”对话框,见图9.10 设置目标单元格):4 匚求解 等于:最大值思)C最小值)c值为同 可变单元格① SBS5: SD$5 推测G 逸项 约束Q 单击〈选项〉按纽,打开“规划求解选项”对话框,见图9.11。 最长运算时间 00秒 确定 取消 精度 装入模型) 保存模型() 采用线性模型 厂自动按比例缩放D 厂假定非负G 显示迭代结果) 估计 G正切函数(A 向前差分 G牛顿法 C二次方程 C中心差分c C共轭法 选“采用线性模型”复选框(选择该选项后,系统将使用单纯形法求解,否则 就使用非线性规划的算法求解,不仅速度慢,精度低,而且输出的敏感性报告内容 也少)。 单击〈确定〉,系统运算结束后打开“规划求解结果”对话框,见图9.12

（2）单击〈添加〉按纽，再输入非负性约束 B5:D5>=0（方法同上）； 说明：对有整数约束要求的决策变量，应再将相应的可变单元格设为“int”约 束（即求解纯整数规划或混合整数规划问题）；对模型中的 0-1 变量，则应再将相应 的可变单元格设为“bin”约束（即二进制变量）。 （3）单击〈确定〉，返回“规划求解参数”对话框，见图 9.10。 图 9.10 1．单击〈选项〉按纽，打开“规划求解选项”对话框，见图 9.11。 图 9.11 选“采用线性模型” 复选框（选择该选项后，系统将使用单纯形法求解，否则 就使用非线性规划的算法求解，不仅速度慢，精度低，而且输出的敏感性报告内容 也少）。 单击〈确定〉，系统运算结束后打开“规划求解结果”对话框，见图 9.12

规划求解结果 求解找到解,可满足所有的约束及最优 报告0 φ保存规戈求解结果 C恢复为原值① 确定取消保存方案()」_帮助00 图912 4.在“报告”列表框中选定所有3个报告,并单击〈确定),系统在工作表中 显示运行结果并生成3个输出报告的工作表,见图9.13。 MIcrosoft Reel-定量法上机案例x1s 文件⑦)编指)视图插入)格式)工具①)数据)窗口)帮助0B 例81:线性规划的一般求解方法 产品甲x1产品乙x2产品丙x2 4单位利润 5利润总额 5产量 工序能力b 8工序1:12 9工序2 3 0 10工序 2 )/敏感性报告1/极限值报告1)倒8,1运| 图913 输出结果分析 由图9.13,可知本问题的最优解为 X1*=0,Ⅹ2*=100,X3*=230,X0*=1350 即每天生产乙产品100件,丙产品230件,不生产甲产品,每天总利润为1350元 在以上最优生产方案下,工序1和2的能力都已用完,而工序3则每天还有20分钟 的剩余能力(仅用去400分钟)。 系统输出的“运算结果报告”、“敏感性报告”和“极限值报告”三张工作表分 别见图9.14、图915、图9.16

图 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

Xlierosoft Excel-定量法上机案例x1x 文件卫)编辑)视图插入①)格式@)工具①)数据①)窗口)帮助0= 6目标单元格(最大值) 单元格名字初值终值 10可变单元格 初值终值 17单元格名字单元格值公式 态型数值 430辑8=0未到跟制值230 泛算结果报告1人敏感性报告1极值报 图914 图9.14所示“运算结果报告”中三道工序约束的“型数值”给出的是最优解中 松弛变量的值,即三道工序时间的富裕量 microsoft Excel 量法上机案例.x1s 口区 ]文件)编辑)视图)插入〔)格式@)工具℃)数据①)窗口) 帮助① 6可变单元 终递减目标式允许的允许的 8单元格名字值成本系数增量减量 10多C$5 11 SDS5 3量 1E+30 乙x2100028 1E+302.6666 13约束 15」单元格名字值价格限制值增量减量 1658工序1:∑ai430141010200 17sE工序2:2ai1x460245040 18亚10工序3:2ai140004201+3020 4≯人运算结果报告1)感性报告1/极限 图915 图915所示“敏感性报告”中“可变单元格”下的“递减成本”给出的是最优 单纯形迭代中各决策变量检验数的负值:“允许的增量”和“允许的减量”则给出了 在不影响当前最优基的条件下各决策变量目标函数系数c的可变动范围:而“约束

图 9.14 图 9.14 所示“运算结果报告”中三道工序约束的“型数值”给出的是最优解中 松弛变量的值，即三道工序时间的富裕量。 图 9.15 图 9.15 所示“敏感性报告”中“可变单元格”下的“递减成本”给出的是最优 单纯形迭代中各决策变量检验数的负值；“允许的增量”和“允许的减量”则给出了 在不影响当前最优基的条件下各决策变量目标函数系数 cj 的可变动范围；而“约束

下的“阴影价格”则给出了各种资源的“影子价格”,其含义将将在第十章中介绍 “允许的增量”和“允许的减量”则给出了在不影响当前最优基的条件下各有限资 源数量(约束条件右端常数)的可变动范围。以上敏感性分析的内容将在第十一章 中详细介绍。 rosoft Reel-定量法上机案例x1s 口区 文件()编辑C)视图Q插入①)格式Q)工具〔)数据q 窗囗)帮助0D 回x GH 目标式 7单元格名字值 8F4 变量 下限目标式上限目标式 11单元格 值极限结果极限结果 12B5 0 145Ds5 230 0200 2301350 人敏感性报告1极限值报告1k例 图9.16 图916所示“极限值报告”给出了各决策变量分别取下限和上限值时对目标函 数的影响。 三.关于“规划求解选项”对话框中各可选项的说明: 最长运算时间”一一可输入的最大值为32767(秒),默认值100(秒)可以 满足大多数小型规划求解要求。 “迭代次数”一一设定求解过程中迭代运算的次数。可输入的最大值为32767, 默认值100次,可满足大多数小型规划求解要求 精度”—一输入用于控制求解精度的数字,以确定约束条件单元格中的数值 是否满足目标值或上下限。默认值为0000001。 “允许误差”—一输入满足整数约束条件的目标单元格求解结果与最佳结果间 的允许百分偏差。此选项只应用于具有整数约束条件的问题。默认值为5% “收敛度”一一输入收敛度数值,当最近五次迭代时,目标单元格中数值的变 化小于“收敛度”编辑框中设置的数值时,“规划求解”停止运行。收敛度只应用于

下的“阴影价格”则给出了各种资源的“影子价格”，其含义将将在第十章中介绍。 “允许的增量”和“允许的减量”则给出了在不影响当前最优基的条件下各有限资 源数量（约束条件右端常数）的可变动范围。以上敏感性分析的内容将在第十一章 中详细介绍。 图 9.16 图 9.16 所示“极限值报告”给出了各决策变量分别取下限和上限值时对目标函 数的影响。 三．关于“规划求解选项”对话框中各可选项的说明： “最长运算时间”——可输入的最大值为 32767（秒），默认值 100（秒）可以 满足大多数小型规划求解要求。 “迭代次数”——设定求解过程中迭代运算的次数。可输入的最大值为 32767， 默认值 100 次，可满足大多数小型规划求解要求。 “精度”——输入用于控制求解精度的数字，以确定约束条件单元格中的数值 是否满足目标值或上下限。默认值为 0.000001 。 “允许误差”——输入满足整数约束条件的目标单元格求解结果与最佳结果间 的允许百分偏差。此选项只应用于具有整数约束条件的问题。默认值为 5%。 “收敛度”——输入收敛度数值，当最近五次迭代时，目标单元格中数值的变 化小于“收敛度”编辑框中设置的数值时，“规划求解”停止运行。收敛度只应用于

非线性规划问题,默认值为0001 “采用线性模型”一一当模型中的所有关系都是线性的,并且希望解决线性优 化问题或对非线性问题进行线性逼近时,选中此复选框可加速求解进程 “显示迭代结果”一一如果选中此复选框,每进行一次迭代后都将中断“规划 求解”,并显示当前的迭代结果 “自动按比例缩放”一一当输入和输出值数量差别很大时,可以使用此功能 “假定非负”一一对于在“添加约束”对话框的“约束值”编辑框中没有设置 下限的可变单元格,假定其下限为0(零)。 “正切函数”、“二次方程”、“向前差分”、“中心差分”、“牛顿法”、“共轭法” 是应用于求解非线性规划问题的各选项,在此不作介绍 〈装入模型〉—一将打开“装入模型”对话框,输入对所要调入模型的引用。 〈保存模型〉—一将打开“保存模型”对话框,输入模型的保存位置。只有当 需要在工作表上保存多个模型时,单击此命令。第一个模型会自动存储

非线性规划问题，默认值为 0.001 “采用线性模型”——当模型中的所有关系都是线性的，并且希望解决线性优 化问题或对非线性问题进行线性逼近时，选中此复选框可加速求解进程。 “显示迭代结果”——如果选中此复选框，每进行一次迭代后都将中断“规划 求解”，并显示当前的迭代结果。 “自动按比例缩放”——当输入和输出值数量差别很大时，可以使用此功能。 “假定非负”——对于在“添加约束”对话框的“约束值”编辑框中没有设置 下限的可变单元格，假定其下限为 0（零）。 “正切函数”、“二次方程”、“向前差分”、“中心差分”、“牛顿法”、“共轭法” 是应用于求解非线性规划问题的各选项，在此不作介绍。 〈装入模型〉——将打开“装入模型”对话框，输入对所要调入模型的引用。 〈保存模型〉——将打开“保存模型”对话框，输入模型的保存位置。只有当 需要在工作表上保存多个模型时，单击此命令。第一个模型会自动存储