Excel在多目标规划求解和灵敏度分析中的应月.pdf

2007年9月 第IO卷第9期 中国管理信息化 Sep.,2007 Vol. 10,No.9 China Management Inationization Excel在多目标规划求解和灵敏度分析中的应月 郑蕉，涂传清 （江西农业大学计算机与信息工程学院，南昌330045 ［摘要］多目标规划可以利用Excel中的l工具］［规划求解］命令进行求解；但由于在建模过程中引入了优先级和权 系数，利用Excel求解多目标规划模型比求解线性规划更复杂，特别是用Excel求解多目标规划模型时输出的敏感 性报告没有实际的经济意义。本文运用Excel中的菜单［工具］［方案］命令，轻松地实现了多目标规划问题的灵敏度分 析，使管理者可以随时检验各种可能的方案，从而为其做出科学决策提供支持。 ［关键词]Excel; 多目标规划；灵敏度分析；方案 ［中图分类号]F270.7;F224.3 [文献标识码]A [文章编号]1673-0194200709-0045-03 多目标规划被认为是一种较之线性规划更接近千实 际决策过程的决策工具。多目标规划由线性规划发展演变 而来，因此，同线性规划一样，多目标规划在建立模型后也 可以利用Excel中的［工具］［规划求解］命令进行求解。然 而由于在建立多目标规划模型过程中引入了优先级和权 系数， 使得利用Excel求解多目标规划模型比求解线性规 划更复杂，特别是用Excel求解多目标规划模型时输出的 敏感性报告没有实际意义。因为各决策变量在目标函数中 的系数P，仅是一种符号，表示优先权等级，在用Excel求解 ［收稿日期]2007-01-02 分析功能。 首先介绍OFFSET函数。 该函数属千Excel函数库中 的 “查找与引用“类别，其工作原理是以初始单元格区域 为参照，通过偏移设定的行数或者列数，返回一个指定行 数与列数的新单元格区域的引用。函数的语法是OFFSET reference, rows, cols, [height], [width], 其中reference 参数代表初始单元格区域，rows与cols分别代表行与列的 偏移量（正数代表向下或向右偏移，负数代表向上或向左 偏移），height与width则分别代表新单元格区域的行数与 列数（这两个参数可以省略，默认为与reference大小相同 的区域）。 接着进行操作讲解。 如图5所示，首先把表示每一套 销售方案的单元格区域按照统一的形式编排于Al2J41.o 具体实现上， 可以先在A12J19设置好方案1的公式并进 行字体、颜色、边框等格式化操作，然后复制此区域到下方 并填充各套销售方案的数据。必须注意的是，操作中要确 保每套方案的行间隔数相等。 然后，再次复制A12Jl9到工 作表的最上方，清除B5J8的内容，并修改Al和Bl单元 格，以利用这片单元格区域呈现所选定的销售方案。 接下来的问题即是找到一种方法，使B5J8的数据随 着Bl单元格填入的销售方案编号而变。仔细分析目前的 时有两种处理方法，一是将目标函数表示为各偏差变量和 的形式，然后根据实际需要赋予P，具体的数字，但必须满 足PiPltl;二是将目标规划转化为多步线性规划问题来求 解，Pl的值全部设为1。 不论按哪种方法处理，Pl的值都是 为计算方便而人为设定的，因此，用Excel求解的结果中分 析P，变化范围的敏感性报告没有实际的经济意义。 另外， 由于在各约束条件中引入了偏差变量，而目标函数为求偏 差变量的最小值，因此，用Excel求解的结果中分析b, 变化 范围的敏感性报告也没有实际的经济意义。但在现实中， 灵敏度分析又十分重要，因为它可以回答管理者诸多“假如 ，那么”式的问题，以帮助管理者甄别多个决策方案。 工作表，细心的读者不难发现笔者如此排列备选方案的用 意在呈现选定方案的数据区域B5J8中，每个单元格与备 选方案数据区域对应单元格的行号之差均为11 的倍数， 而倍数值即为选定方案的编号。 这样的 设 计为随后 OFFSET函数的应用奠定了基础。 紧接着在B5单元格中输入公式“OFFSETB5,11* B1 ,O“, 然后通过选择性粘贴把该公式复制到B5J8区 域的其他单元格，即可实现如图6所示的最终效果。 三、总结 该方法具有3个优点 1.方便切换方案。 采用此方法，进行方案切换时只须 在B2单元格中输入目标方案的编号，操作十分方便。 2. 方便修改或增加方案。每套方案的数据都存储在工 作表上，呈现千用户可以直接接触的前台界面，方便用户 查看、对比与修改。用户需要增加方案时，也仅须按照统一 的格式（此例中为确保间隔相同的行数）编排新方案并添 加数据，同样非常方便和高效。 3.适用面广、可扩展性强。通过OFFSET函数进行多 变量假设分析的方法具有很强的扩展性。该方法既没有变 量数目的限制，也不限定方案的结构 ，适用面广、易千扩 展，读者完全可以借鉴此方法构建更复杂的模型。 CHINA MANAGEMENT INATIONIZA TION / 45 企业 管理信 息化 有关如何运用 E x c e l 求解多 目标规划问题 已经有不 少文献资料 ， 无需赘述 ； 本文的重点是探讨如何利用 E x c e l 对多 目标规划 问题进行灵敏度 分析 。笔 者尝试 着运用 E x c e l 中的菜单 [ 工具 ] [ 方案 ] 命令 ， 轻松实现多 目 标规划 问题的灵敏度分析 。 一 、多目标模型 多 目标模型的数学模型的一般形式如下 f 『 K 1 1 m in ∑ ，／ I 2 “ ，L } ∑ 丐 ， 1 2一 ，K 』 1 1 ≤ ， ≥ b ， ／ 1 ， 2 ， ⋯， m ，1 丐 t0 ， 户1 ， 2 ， ⋯ ， n ， ≥0， k 1 ， 2， ⋯ ， K 模 型中， ， 分别为正负偏差 变量 ； 为第 k个 目标 约束的预期 目标值 ， 和 为优先因子 对应各 目 标 的 权系数 。 二、 应用实例 某化工厂生产两种用于轮船上的黏合剂 A和 B 。 这两 种黏合剂 的强度不同 。 所需的加工时间也不 同， 生产 1 升 的 A需要 2 0 分钟。 生产 1 升 的 B 需要 2 5 分钟 。这两种黏 合剂都以一种树脂作为原料， 1 升树脂可以制造 1 升 A， 或 者 1 升 B 。树脂的保质期是 2 周 ，目前树脂的库存为 3 0 0 升。已经正常工作下每周有 5 个工作 日，每个工作 日有 8 个工时． 工厂期望在未来两周达到以下 目标 目标 1 保持工厂满负荷运转 ； 目标 2 加班时间控制在 2 0工时以内； 目标 3 至少生产 1 0 0 升 A 目标 4 至少生产 1 2 0 升 B ； 目标 5 使用完所有的树脂 。 假设 目标 1 和 目标 2的优先权为 P l ，且重要程度相 等； 目标 3 和目标4的优先权为尸 2 ， 且重要程度相等； 目标 5的优先权为 ， 建立 目 标规划模型并求解。 如果设在未来两周 A 、 B两种黏合剂的生产数量分别 为 ， 和 ，则对上述问题我们可以建立如下 目标规划模 型 m i n { ，尸 2 ， } 公 式1 一 ‘ 2 0 x 1 2 5 x 2 -- dI 8 0 0 一 ‘ 2 0 x l 2 5 x 2 6 0 0 0 s． { 蝎 1 0 0 - ‘ 1 2 0 1 x 2 _ 3 0 0 一 ‘ 1 ， ， ， t0 ／ 1 ， ⋯ ， 5 公式 2 为了便于用 E x c e l 中的[ 工具] [ 规划求解] 对上面的目 标规划问题求解 ， 我们采用 了第一种处理方法 ， 将 目 标 函 数表示为各偏差变量和的形式 m i n 尸 2 公 式3 4 6|C HI N A MAN A G E ME NTI N F O R MAT I ON I Z AT I O N 并赋予 P l 1 0 0 0 ， 尸 1 1 0 0 ， 尸 1 1 ，以满足 P l P 1 P 1 。 利用 E x c e l 中的[ 工具 ] [ 规划求解 ] 命令 ， 我们可以求得如 图 1 所示的计算结果。必须注意的是 ， 单元格 B 7 M7 为各 变量在 目标函数 中的系数 ； 另外 。 在单元格 N 2 N 6中输入 表示约束条件左边项的公式 。在单元格 N 7 输入表示 目标 函数的公式 ．输入方式为 先在单元格 N 2中输入公式“ S U M P R O D U C T B 2 M2 ， B 8 M 8 ” ， 然后选定单元格 N 2 ， 用 鼠标拖曳填充柄至 N 7即可。 从图 1 中可以看 出各变量的值为 1 5 0 ， x 2 1 2 0 ， d l 一 十 一 十 一 十 一 十 0 ， d I 1 2 0 0 ， 0 ， 0 ， 0 ， ％-- 5 o ， 吐 0 ， 吐 0 ， 4 3 0 ， 40。 即为尽可能达到问题 中所列的 目标． 工厂在未来两周 应该生产黏合剂 A和 B分别为 1 5 0升和 1 2 0升。 这一方案 ； A B C D E F j G H ， X L I N 一 蒹 x 1 2 d卜d l 监 一 412 ,93 一 a3 d 4一 批 一 左 边 童 式 ∞2 5 1 一 t 0 0 O 0 0 0 0 0 4翘 2 O 2 5 0 0 l 一 1 O 0 O 0 0 O -A 1 0 0 唾 { 1 0 0 0 0 0 t l 0 0 0 O 1 0 0 一 0 1 O 0 O 0 0 O 1 1 0 0 l ∞ j 1 i 0 0 0 0 O 0 0 O 1 1 3 o o 3 。 O 奄边常量 5 “ 0 0 e 咐 1 0 3 1 7 O 3 ∞ 图1 利用E x c e l 中的【 工具Ⅱ 规划求解】 命令求解的结果 图 2 方案管理器对话框 满足了目标 1 、 目标 2 、 目标 3 和 目 标 4 ； 但未满足目标 5 。 因 此 ， 将会有 3 0 升的树脂无法在保质期内加工 。 考虑到上述 3 0 升库存树脂无法在保质期 内加工的事 实 ．工厂领导决定调整 目 标 的优先权 。以保证库存的 3 0 0 升树脂在两周内全部用完。调整后的优先权为 目标 5 的 优先权为 P l ， 目标 3 和 目标4的优先权为尸 2 ， 且重要程度 相等； 目标 1 和目标2的优先权为 ， 且重要程度相等， 重 新建立 目 标规划模型并求解 。 工厂领导希望通过调整 目标优先权次序的方案来解 决 3 0 升库存树脂无 法在保质期 内加工的问题是 否可行 呢可以通过对前面已经建立的 目标规划模型进行灵敏度 分析来 回答这一问题。 调整 目标优先权次序相当于对各偏 差变量在 目标函数 中的系数进行灵敏度分析。 对于工厂领 导调整 目标优先权 。 我们可以用如下 目 标函数来表示 m i n z - P I } { } 公式4 而约束条件不变。 仍为公式 2 。 我们可以借助于E x c e l 中的[ 工具] [ 方案] 来完成调整 目标优先权后的模型求解。具体操作步骤如下 第一步 ， 在如 图 1 所示结果 的界面中 。 选择 菜单 『 工 具] [ 方案 ] ， 将出现如 图 2 所示对话框 ， 点击对话框中的“ 添 加 A ” 按钮 ， 出现如图 3 所示对话框。 在本例中， 我们将工 厂的初始方案命名为‘ ‘ A 1 ” ， 因此 ， 在 “ 方案名 N ” 文本框 企业 管理信息化 图6 方案管理器对话框 i A 墨 、要 ～ I 随 l 玎蔓 L j [ 』 工 ～ } -量 一 } L I N } 0 一量 i 变蠢 名称 x l x 2 d l ～ d l 姥 一 拉 ,1 3 一d 3 d { 一d ‘ 龉 一 墨 左边公式右边常 萋 妻 i约秉条件 2 。 i 1 0 0 0 0 0 0 0 0 4 8 ∞ 4 8 ∞ ∞2 S 0 0 l-1 0 0 0 0 0 0 6 O O O 6 ∞ 4 1 1 0 0 o 0 0 I 1 0 0 0 0 l O 0 I 啦 一 0 I O 0 o 0 0 0 I l 0 0 2 O I 2 0 b l i 0 0 0 0 0 0 0 0 l 一 】 3 ∞3 0 0 饵标函数0 0 l o 0 I 1 0 0 0 一 1 0 0 0I 0 0 0 f 舅一 j 捷 麓燕量 1 8 0 l 2 0 0 l 8 0 0 0 6 0 0 0 8 0 0 0 0 0 图5 调整目标的优先权后利用 E x c e l 中的【 工具I 规划求解】 命令求解的结果 中输入 A1 ； 在“ 可变单元格 C ” 文本框 中输入 “ B 8 M 8 ， D 钾 ， G 7 ， 【 7 ” 。需要说明的是 ， 这里单元格 B 8 M 8 是 目 标规划问题中待求的决策变量和偏差变量 ． 应该作 为可变单元格来处理 ， 而单元格“ D 7 ， G 7 ， 【 7 ” 是 目标 函数中某些偏差变量的系数． 因为它们会随着工厂领导调 整目标的优先权而发生改变 ． 所以也被作为可变单元格来 处理。然后 ． 单击图 3 中的“ 确定” 按钮， 出现如图4所示对 话框 ， 由于系统默认的值就是我们要输入的每个可变单元 格的值 ， 所以只需按 “ 确定” 即可 ． 这样工厂的初始方案被 作为方案 A 1 保存起来了。 第二步， 调整 目 标的优先权后 ， 目标函数变为 rai n z ； 图8方案摘要 d ， ，J 2 吐 P 3 ， 这 意味着 目标函数 中某些偏差 变量的系数发生变化 ．所 以． 我们必须 调整图 1 中单元格 D 7 ， C 7 和 L 7的值 ， 因 为这些单元格所代表 的偏差 变 量在 目标 函数 中系数 由于 调 整 目标 的优先权而发生 了 变化。调整后 D 7 1 ， C 7 1 ， I 新 1 0 0 0 。再次用 [ 工具] [ 规划求解] 命令求解 ， 所得结 果如图 5所示 从 图 5可 以看 出各变量 的值为 1 8 0 ， x 2 1 2 0 ， 1 3 ， 1 8 0 0 ， o ， 6 o o ， 吐 o ， 吐 - - 8 0 ， 4 0 ， 4 0 ， 4 0 ， 4 0 。 即调整 目标的优先权后 ， 工厂在未来两周应该生产黏 合剂 A和 B分别为 1 8 0升和 1 2 0升 ． 这样将保证所有库存 的树脂全部在保质期 内加工 完 ， 但工人的加班时数将增加 1 0工时。 第三步 ． 按照与第一步相 同的操作 ． 将调整 目 标 的优先 权后 所建模 型的计算 结果命 名为方案 “ A 2 ” 。利用上述方 法 ， 我们还可 以很容易地检验 目标 优先权调 整后 的其他方 案。 第 四步 ， 显示方案摘要 ． 以便于管理者比较 目标优先 权变化时． 工厂各有关 目 标实现 隋况 ． 从而做出最符合企业 实际的决策。 显示方案摘要的具体操作如下 当我们在图 2 所示的“ 方案管理器对话框” 中添加方案后 ． 会 出现如图 6 所示对话框 ， 点击 图 6中的“ 摘要 U ” 按钮 ， 会 出现如图 7 所示对话框 ， 在结果类型复选框中选择“ 方案摘要 S ” ， 在 结果单元格 R 文本框 中输入 N 7 ， 再点击“ 确定” 按钮， 可 以得到如图 8 所示方案摘要。方案摘要将展示不同方案中 各变量的取值 ， 有利于管理者比较不同方案的结果。 三、 结束语 由此可见 ， 用户不但可以利用 E x c e l 中的[ 工具] [ 规划 求解 1 命令对 目标规划进行求解 ． 而且可 以运用 E x c e l 的 [ 工具 ] [ 方案 ] 命令来保存计算结果 ， 利用 E x c e l 提供的这 一 功能用户可以完成对 目标规划的灵敏度分析 ． 因而轻易 地获得由于调整 目标优先权 、 资源约束 6 或者资源消 耗系数 而产生 的新方案 ， 从而帮助管理者甄别各种不 同方案 ， 为其做出科学决策提供支持。 主要参考文献 [ 1 ] 韩 伯棠． 管理运筹学 [ M] ． 第 2版． 北京 高等教育出版社 ， 2 0 0 5 ． [ 2 ]胡运权． 运筹学教程 [ M] ． 第 2版． 北京 清华大学出版社 ， 2 0 0 3 ． [ 3 ]周德镇． E x c e l 在现代管理中的应用 [ M] ． 北京 电子工业 出版社 1 9 9 7 ． [ 4 ]刘珊 等．多 目标 规划 的 E x c e l求解 方 法 [ J ] ． 科技 广场 ， 2 0 0 5 1 0 9 1 9 2 ． C HI N A MA N AG E ME NTI N F O R MAT I ON I Z AT I ON | 4 7