EXCEL规划求解宏包的使用简介.pdf

EXCEL 规划求解宏包的使用简介规划求解宏包的使用简介 王王 緌緌 四川大学锦城学院工商管理系四川大学锦城学院工商管理系 EXCEL 规划求解宏包是 EXCEL 软件上附带的一款非常好用的解决线性系统优化问题 的工具 也是国内外学习运筹学必备的工具之一 下面 结合教材 将简单地为大家介绍其 用法 1 加载 规划求解 宏包 打开 EXCEL 选择菜单中 工具 加载宏 图 1 图 1 选择 规划求解 宏包 图 2 点击 确定 即可完成加载 图 2 图 3 加载后 再打开工作簿窗口上方菜单中的 工具 选项 则可见其中出现了 规划求解 项 见图 3 如果一些同学安装的 OFFICE 系统 不能正确加载此宏包 则可以在打开 EXCEL 后 找 到硬盘上 OFFICE 安装位置中的 SOLVER XLA 程序 双击打开它 即可实现在 EXCEL 中的加载 此程序文件一般的路径为 C Program Files Microsoft Office OFFICE11 Library SOLVER 2 利用 规划求解 宏包求解线性规划问题 下面将结合例 1 说明 例 1 max z 3x1 2x2 2x1 3x2 14 x1 0 5x2 4 5 x1 x2 0 a 基本术语 价值向量 c 变量在目标函数中的系数的总称 本例中 c 3 2 资源向量 b 资源约束不等式右端的常数项的总称 它反映了资源总量的限制 本 例中 b 14 4 5 即第一 二种资源的总量限制分别为 14 和 4 5 系数矩阵 A 资源约束不等式左端的函数部分中变量系数的总称 本例中 A 23 10 5 资源的实际耗用 资源约束不等式左端的函数部分 本例有两个资源约束 资源约 束 的左端函数部分为 2x1 3x2 此即第一种资源的实际耗用 资源约束 的左端函数部分 为 x1 0 5x2 此即第二种资源的实际耗用 关系 指资源约束中不等式左端函数部分与不等式右端的常数项之间的关系 即资 源的实际耗用与该资源总量之间的关系 可用关系符 或 或 来描述 本例 中资源约束 的左端函数部分 即第一 二种资源的实际耗用 均被要求小于等于其各 自的资源总量 故均用 来表示 约束条件 其资源约束形态一般有三种 资源实际耗用 资源总量 每个约束包括三个组成部分 资源实际耗用 关系 资源总量 本例两个资源约束条件 均为类型 资源实际耗用 资源总量 单元格 EXCEL 表上的每个空格被称为单元格 并且分别用 A B C D Z AA AB AC AZ 等字母和 1 2 3 4 5 等数字来标示单元格所在的行和列的位置 如 C5 就 表示该单元格在 C 列 5 行上 见图 4 图 4 b 模板设计 将系数矩阵 A 价值向量 c 资源实际耗用 资源实际耗用与资源总量限制间的关系 资源向量 b 等栏目分别在 EXCEL 表上选用不同列的单元格放置 除此 还要选择一些单元格来盛放模型求解后变量与目标函数的值 这些单元格分别被 称为变量单元格和目标单元格 例 1 的求解模板设计如图 5 其中 可看出系数矩阵 A 价值向量 c 资源实际耗用 资 源实际耗用与资源总量限制间的关系 资源向量 b 等栏目分别被置于 EXCEL 表上的 B4 C5 B6 C6 D4 D5 E4 E5 F4 F5 等单元格 而单元格 H4 H5 被设计为变量单元格 它们用来盛放例 1 中两个 x1 x2的解 单元格 H6 被设计为目标单元格 c 数据录入 将例 1 的系数矩阵 A 价值向量 c 资源实际耗用与资源总量限制间的关系 资 源向量 b 按图 5 方式分别输入 EXCEL 表中的 B4 C5 B6 C6 E4 E5 F4 F5 等单元格 其中 A 23 10 5 c 3 2 b 14 4 5 图图 5 例例 1 的模板设计的模板设计 d 函数输入 将模型基础数据录入后 就可以将每种资源的实际耗用 即每个资源约束左端的函数部 分以及模型的目标函数输入到预先规划好的单元格中 记住 例1中两个变量x记住 例1中两个变量x1 1 x x2 2的单元格被预先设计在H4和H5 所以在下面函数的输入中 将分别用 H4 H5 代替函数中的变量符号 x 的单元格被预先设计在H4和H5 所以在下面函数的输入中 将分别用 H4 H5 代替函数中的变量符号 x1 1 x x2 2 目标函数的输入 例 1 模型中目标函数为 3x1 2x2 则在目标单元格 H6 上操作如下 鼠标点击单元格 H6 输入 此时 自动出现在上面菜单中的 fx 栏上 在 后接着输入 3 H4 2 H5 点击旁边的 即可完成输入 目标单元格 H6 出现了 值 0 或者 value 图图 6 例例 1 目标函数的输入目标函数的输入 变量单元格 目标单元格 实际耗用的输入 例 1 模型中两种资源的实际耗用分别为 2x1 3x2和 x1 0 5x2 即资源约束 左端 的函数部分 下面 只须将这两个函数赋予给它们在模板设计时被预先规划好的位置 单元 格 D4 D5 中即可 方法与目标单元格的函数输入一致 鼠标点击单元格 D4 输入 此时 自动 出现在上面菜单中的 fx 栏上 在 后接着输入 2 H4 3 H5 点击旁边的 即 可完成输入 单元格 D4 出现了值 0 或者 value 将函数 H4 0 5 H5 输入单元格 D5 的过程同理 不再累述 实际耗用单元格和目标单元格完成相应的函数输入后的形态见图 7 图图 7 实际耗用函数和目标函数输入完成后的形态实际耗用函数和目标函数输入完成后的形态 e 模型求解 先鼠标点击目标单元格 H6 调用规划求解宏包 打开菜单 工具 规划求解 见图 8 图图 8 调用规划求解宏包调用规划求解宏包 设置规划求解参数 规划求解宏包选择后 会出现规划求解参数设置菜单 见图 9 图图 9 规划求解参数菜单规划求解参数菜单 据模型 逐一设置图 9 中的各项规划求解所需参数 过程如下 a 目标单元格设置 选项 设置目标单元格 中会自动出现例 1 的目标单元格的值 H 6 所以不必管它 b 目标类型设置 EXCEL 默认为求 最大值 而例 1 正好是 MAX 问题 所以不用改变此选项 若模型是 MIN 问题 则只需点击 最小值 选项 即可调整 c 可变单元格设置 其作用是告知 EXCEL 模型变量单元格的位置 由于例 1 中变量 x1 x2的单元格被规定在 H4 H5 上 所以按此输入 H 4 H 5 即可 此处 的意思为 H4 至 H5 的意思为相应单元格的值 打开 EXCEL 的 HELP 可寻找到该符号的详细解 释 另一种常用的可变单元格设置方式为 先将鼠标在该空白选项栏上点击一下 然后鼠标移动回工作簿上 左键点击放置变量的第一个单元格 H4 然后按住不放 拖动鼠标到放置变量的最后一个单元格 H5 松开鼠标 此时规划求解 参数菜单中可变单元格栏同样会出现 H 4 H 5 d 约束条件设置 此步骤作用为输入模型中的约束条件 由前面知道 约束条件有三 个部分 资源实际耗用 关系 和 资源总量 将它们分别输入到相应参数 选项中 即可完成约束条件的输入 做法为 先将鼠标在 约束 选项下的空白栏处点击一下 然后点击旁边的 添加 项 出现 添加约束 菜单 见图 10 图图 10 调出 添加约束 菜单调出 添加约束 菜单 继而 将例 1 中约束条件的三个组成部分 资源实际耗用 和 资源总量 所在单元格 分别填入 添加约束 菜单中的 单元格引用位置 和 约束值 选项中 同时点击该菜单 中间的按钮 选择资源耗用和资源总量间的关系类型即可 EXCEL 一般默认关系为 即 由于例 1 中资源实际耗用单元格为 D4 D5 资源总量单元格为 F4 F5 二者间关系均为 故可按图 11 中左图所示填入 图 11 中右图描述了关系类型的多种选择 除此 也可采用点击工作簿相应位置的单元格来完成约束条件中资源实际耗用和资源 总量的输入 点击 添加约束 菜单中 单元格引用位置 选项 然后移动鼠标回工作簿 左 键点击放置第一种资源实际耗用函数的第一个单元格 D4 然后按住不放 拖动鼠标到放置 第二种资源实际耗用函数的单元格 D5 松开鼠标 此时 添加约束 菜单中 单元格引用位 置 选项栏会出现 D 4 D 5 同理 点击 添加约束 菜单中 约束值 选项 然后移动鼠标回工作簿 左键点击放 置第一种资源总量的第一个单元格 F4 然后按住不放 拖动鼠标到放置第二种资源总量的 单元格 F5 松开鼠标 此时 添加约束 菜单中 单元格引用位置 选项栏会出现 F 4 F 5 图图 11 添加约束添加约束 当还有约束条件需输入时 可点击 添加约束 菜单中的 添加 选项 重复上面的操 作 当所有约束均完成输入后 点击 添加约束 菜单中的 确定 选项 回到前面的 规 划求解参数 菜单 点击其上的 选项 见图 12 图图 12 求解参数设置完成后的形态求解参数设置完成后的形态 e 规划求解选项的设置 点击选择 选项 菜单中的 采用线性模型 和 假定非负 点击 确定 回到 规 划求解参数 菜单 见图 13 图图 13 规划求解选项的设置规划求解选项的设置 求解结果 点击 规划求解参数 菜单上的 求解 S 见图 14 出现 规划求解结果 菜单 图 15 根据需要点击选择 报告 栏下面的 运算结果报告 敏感性报告 和 极限值报 告 最后点击 确定 获得求解结果 工作簿中 两个变量的优解值 目标函数最优值 两种资源的实际耗用值被展示在原来 设置的单元格 H4 H5 H6 D4 D5 中 见图 16 图图 14 点击求解按钮点击求解按钮 图图 15 选择求解报告类型选择求解报告类型 图图 16 工作簿中的求解结果工作簿中的求解结果 同时 EXCEL 上涌现了一些新工作簿放置运算结果 敏感性和极限值报告 3 报告阅读 a 运算结果报告 图图 17 运算结果报告运算结果报告 最优目标函数值 最优解 两种资源的实际耗用值 即资源约束左端函数 b 敏感性报告 图图 18 敏感性报告敏感性报告 价值向量 c 的灵敏度分析 图 18 中方框显示 变量 x1在目标函数中系数 3 在 3 1 666666667 3 1 范围内变化时 即 4 3 4 现最优 解 x 3 25 2 5 T不变 变量 x2在目标函数中系数 2 在 2 0 5 2 2 5 范围内变化时 即 1 5 4 5 现最优解 x 3 25 2 5 T不变 x1 x2的递减成本均为 0 两种资源的影子价格分别为 0 25 2 5 资源向量灵敏度分析 图 18 中椭圆显示 第一种资源总量现有 14 它在 14 5 14 13 即 9 27 范围内变化时 现最优基不变 现在的影子价格不变 仍为 0 25 第二种资源总量现有 4 5 它在 4 5 2 1666666667 4 5 2 5 即 7 3 7 范围内变化时 现 最优基不变 现在的影子价格不变 仍为 2 5 关于影子价格和递减成本的经济含义和应用 可参见 LINDO 简介 4 高级技巧 EXCEL 规划求解宏包的使用过程中 在实际耗用函数和目标函数的输入中还有一些更 简便的方法 不过需要用到 EXCEL 本身自带的一些函数功能 a 目标函数的多种输入方式 方式1 见图19 在目标单元格H6上直接输入 3 H4 2 H5 目标函数中变量系数 3 2 是直接输入的 图图 19 目标函数中变量系数目标函数中变量系数 3 2 是直接输入的情况是直接输入的情况 最优解 影子价格 递减成本 方式 2 见图 20 由于目标函数中变量系数 3 2 已预先输入单元格 B6 C6 中 故在 目标单元格 H6 上可输入 B6 H4 C6 H5 这样 在输入目标函数过程中 可通过 直接点 击 B6 再输入 号 再点击 H4 单元格 再输入 号 再点击 C6 单元格 再输入 再点击 H5 最后完成输入 即目标函数中变量系数可通过点击系数所在单元格完成系数的输入 图图 20 目标函数中变量系数可通过点击系数所在单元格完成系数的输入目标函数中变量系数可通过点击系数所在单元格完成系数的输入 方式 3 见图 21 EXCEL 提供了大量函数可供更方便的计算 由于例 1 目标函数 3x1 2x2可写成向量矩阵乘积的形式 3 2 1 2 x x 故可用EXCEL中MMULT这个函数 当使 用 MMULT A B 命令时 就可完成矩阵乘积运算 An m Bm q 输入过程 点击目标单元格 H6 输入 如图 21 点击打开函数菜单 选择其中的 MMULT 出现函数参数菜单 见图 22 鼠标点击 Array1 空白栏 然后移动回工作簿 价值向量所在单元格 左键点击 B6 按住并拖动鼠标至 C6 松开鼠标 就完成了价值向量 c 3 2 的输入 鼠标点击 Array2 空白栏 然后移动回工作簿变量所在单元格 左键点击 H4 按住并拖动鼠标至 H5 松开鼠标 就完成了变量向量 1 2 x x 的输入 最后 点击 MMULT 的 函数参数 菜单中的 确定 项 这样就完成了目标函数在单元格 H6 上的输入 图图 21 在函数库中寻找矩阵乘法函数在函数库中寻找矩阵乘法函数 MMULT 图图 22 MMULT 函数参数菜单函数参数菜单 b 资源耗用函数的多种输入方式 方式 1 见图 第一种资源实际耗用函数 2x1 3x2中变量系数 2 3 是直接输入的情 况 图图 23 第一种资源实际耗用函数中变量系数第一种资源实际耗用函数中变量系数 2 3 是直接输入的情况是直接输入的情况 方式2 见图24 第一种资源实际耗用函数2x1 3x2在输入时利用了变量系数2 3所 在的单元格 B4 C4 输入形式为 B4 H 4 C4 H 5 其中 H 4 H 5 分别表示 H4 H5 单 元格中的数值 它们不会因为鼠标的拖动而发生数值指针的位移 单元格D4中的公式输入 完成后 就可以通过拖动鼠标到D5 而轻松完成第二种资源实际耗用函数在 D5 单元格上的 输入 见图 25 图图 24 第一种资源实际耗用函数中的系数可通过点击系数所在单元格完成系数的输入第一种资源实际耗用函数中的系数可通过点击系数所在单元格完成系数的输入 拖动 D4 完成 D5 单元格的输入过程为