Excel在多目标规划求解和灵敏度分析中的应用
２００７年９月第１０卷第９期
中国管理信息化
ＣｈｉｎａＭａｎａｇｅｍｅｎｔＩｎｆｏｒｍａｔｉｏｎｉｚａｔｉｏｎ
Ｓｅｐ．，２００７Ｖｏｌ．１０，Ｎｏ．９
Ｅｘｃｅｌ在多目标规划求解和灵敏度分析中的应用
郑蕉，涂传清
（江西农业大学计算机与信息工程学院，南昌３３００４５）
［摘
要］多目标规划可以利用Ｅｘｃｅｌ中的［工具］［规划求解］命令进行求解；但由于在建模过程中引入了优先级和权
系数，利用Ｅｘｃｅｌ求解多目标规划模型比求解线性规划更复杂，特别是用Ｅｘｃｅｌ求解多目标规划模型时输出的敏感性报告没有实际的经济意义。本文运用Ｅｘｃｅｌ中的菜单［工具］［方案］命令，轻松地实现了多目标规划问题的灵敏度分析，使管理者可以随时检验各种可能的方案，从而为其做出科学决策提供支持。［关键词］Ｅｘｃｅｌ；多目标规划；灵敏度分析；方案［中图分类号］Ｆ２７０．７；Ｆ２２４．３
［文献标识码］Ａ
［文章编号］１６７３－０１９４（２００７）０９－００４５－０３
多目标规划被认为是一种较之线性规划更接近于实际决策过程的决策工具。多目标规划由线性规划发展演变而来，因此，同线性规划一样，多目标规划在建立模型后也可以利用Ｅｘｃｅｌ中的［工具］［规划求解］命令进行求解。然而由于在建立多目标规划模型过程中引入了优先级和权系数，使得利用Ｅｘｃｅｌ求解多目标规划模型比求解线性规划更复杂，特别是用Ｅｘｃｅｌ求解多目标规划模型时输出的敏感性报告没有实际意义。因为各决策变量在目标函数中的系数Ｐｌ仅是一种符号，表示优先权等级，在用Ｅｘｃｅｌ求解
［收稿日期］２００７－０１－０２
时有两种处理方法，一是将目标函数表示为各偏差变量和的形式，然后根据实际需要赋予Ｐｌ具体的数字，但必须满足Ｐｌ＞＞Ｐｌ＋１；二是将目标规划转化为多步线性规划问题来求解，Ｐｌ的值全部设为１。不论按哪种方法处理，Ｐｌ的值都是为计算方便而人为设定的，因此，用Ｅｘｃｅｌ求解的结果中分析Ｐｌ变化范围的敏感性报告没有实际的经济意义。另外，由于在各约束条件中引入了偏差变量，而目标函数为求偏差变量的最小值，因此，用Ｅｘｃｅｌ求解的结果中分析ｂｉ变化范围的敏感性报告也没有实际的经济意义。但在现实中，灵敏度分析又十分重要，因为它可以回答管理者诸多“假如…，那么…”式的问题，以帮助管理者甄别多个决策方案。
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
分析功能。工作表，细心的读者不难发现笔者如此排列备选方案的用
首先介绍ＯＦＦＳＥＴ函数。该函数属于Ｅｘｃｅｌ函数库中意：在呈现选定方案的数据区域Ｂ５∶Ｊ８中，每个单元格与备的“查找与引用”类别，其工作原理是：以初始单元格区域选方案数据区域对应单元格的行号之差均为１１的倍数，为参照，通过偏移设定的行数或者列数，返回一个指定行而倍数值即为选定方案的编号。这样的设计为随后数与列数的新单元格区域的引用。函数的语法是：ＯＦＦＳＥＴＯＦＦＳＥＴ函数的应用奠定了基础。（ｒｅｆｅｒｅｎｃｅ，ｒｏｗｓ，ｃｏｌｓ，［ｈｅｉｇｈｔ］，［ｗｉｄｔｈ］），其中ｒｅｆｅｒｅｎｃｅ紧接着在Ｂ５单元格中输入公式“＝ＯＦＦＳＥＴ（Ｂ５，１１＊参数代表初始单元格区域，ｒｏｗｓ与ｃｏｌｓ分别代表行与列的，然后通过选择性粘贴把该公式复制到Ｂ５∶￥Ｂ￥１，０）”Ｊ８区偏移量（正数代表向下或向右偏移，负数代表向上或向左域的其他单元格，即可实现如图６所示的最终效果。偏移），ｈｅｉｇｈｔ与ｗｉｄｔｈ则分别代表新单元格区域的行数与三、总结列数（这两个参数可以省略，默认为与ｒｅｆｅｒｅｎｃｅ大小相同该方法具有３个优点：的区域）。１．方便切换方案。采用此方法，进行方案切换时只须
接着进行操作讲解。如图５所示，首先把表示每一套在Ｂ２单元格中输入目标方案的编号，操作十分方便。销售方案的单元格区域按照统一的形式编排于Ａ１２∶Ｊ４１．。每套方案的数据都存储在工２．方便修改或增加方案。具体实现上，可以先在Ａ１２∶Ｊ１９设置好方案１的公式并进作表上，呈现于用户可以直接接触的前台界面，方便用户行字体、颜色、边框等格式化操作，然后复制此区域到下方查看、对比与修改。用户需要增加方案时，也仅须按照统一并填充各套销售方案的数据。必须注意的是，操作中要确的格式（此例中为确保间隔相同的行数）编排新方案并添保每套方案的行间隔数相等。然后，再次复制Ａ１２∶Ｊ１９到工加数据，同样非常方便和高效。作表的最上方，清除Ｂ５∶Ｊ８的内容，并修改Ａ１和Ｂ１单元可扩展性强。通过ＯＦＦＳＥＴ函数进行多３．适用面广、格，以利用这片单元格区域呈现所选定的销售方案。变量假设分析的方法具有很强的扩展性。该方法既没有变
接下来的问题即是找到一种方法，使Ｂ５∶Ｊ８的数据随量数目的限制，也不限定方案的结构，适用面广、易于扩着Ｂ１单元格填入的销售方案编号而变。仔细分析目前的展，读者完全可以借鉴此方法构建更复杂的模型。
ＣＨＩＮＡＭＡＮＡＧＥＭＥＮＴＩＮＦＯＲＭＡＴＩＯＮＩＺＡＴＩＯＮ
／４５
企业管理信息化
有关如何运用Ｅｘｃｅｌ求解多目标规划问题已经有不少文献资料，无需赘述；本文的重点是探讨如何利用Ｅｘｃｅｌ对多目标规划问题进行灵敏度分析。笔者尝试着运用Ｅｘｃｅｌ中的菜单［工具］［方案］命令，轻松实现多目标规划问题的灵敏度分析。
并赋予Ｐ１＝１０００，Ｐ２＝１００，Ｐ３＝１，以满足Ｐ１＞＞Ｐ２＞＞Ｐ３。利用Ｅｘｃｅｌ中的［工具］［规划求解］命令，我们可以求得如图１所示的计算结果。必须注意的是，单元格Ｂ７～Ｍ７为各变量在目标函数中的系数；另外，在单元格Ｎ２～Ｎ６中输入表示约束条件左边项的公式，在单元格Ｎ７输入表示目标函数的公式，输入方式为：先在单元格Ｎ２中输入公式“＝
，然后选定单元格Ｎ２，用ＳＵＭＰＲＯＤＵＣＴ（Ｂ２∶Ｍ２，Ｂ￥８∶Ｍ￥８）”
鼠标拖曳填充柄至Ｎ７即可。
从图１中可以看出各变量的值为：ｘ１＝１５０，ｘ２＝１２０，ｄ１
－
一、多目标模型
多目标模型的数学模型的一般形式如下：
ｍｉｎＰｌ
*
,,,,,ｊ,,,,+,,ｊ,,,,,,,-
&$!
Ｋｋ＝１
Ｗｌｋｄｋ＋Ｗｌｋｄｋ
－
＋ｋｋ
ｋ
－－＋＋
#，ｌ＝１，２，…，Ｌ
%’
!Ｃｘ＋ｄ－ｄ＝ｇ，ｋ＝１，２，…，Ｋ
＝１
ｋｊｊ
ｎ
＝０，ｄ１＝１２００，ｄ２＝０，ｄ２＝０，ｄ３＝０，ｄ３＝５０，ｄ４＝０，ｄ４＝０，ｄ５＝３０，ｄ５＝０。即为尽可能达到问题中所列的目标，工厂在未来两周
应该生产黏合剂Ａ和Ｂ分别为１５０升和１２０升，这一方案
＋－＋－＋－＋－＋
ｓ．ｔ．
!ａｘ≤（＝，≥）ｂ，ｉ＝１，２，…，ｍ
＝１
ｉｊｊ
ｉ
－
＋
ｎ
ｘｊ≥０，ｊ＝１，２，…，ｎｄｋ，ｄｋ≥０，ｋ＝１，２，…，Ｋ
－
＋
－
＋
模型中，ｄｋ，ｄｋ分别为正负偏差变量；ｇｋ为第ｋ个目标约束的预期目标值，Ｗｌｋ和Ｗｌｋ为优先因子Ｐｌ对应各目标的权系数。
图１
利用Ｅｘｃｅｌ中的［工具］［规划求解］
命令求解的结果
二、应用实例
某化工厂生产两种用于轮船上的黏合剂Ａ和Ｂ。这两种黏合剂的强度不同，所需的加工时间也不同，生产１升的Ａ需要２０分钟，生产１升的Ｂ需要２５分钟。这两种黏合剂都以一种树脂作为原料，１升树脂可以制造１升Ａ，或者１升Ｂ。树脂的保质期是２周，目前树脂的库存为３００升。已经正常工作下每周有５个工作日，每个工作日有８个工时，工厂期望在未来两周达到以下目标：
目标１：保持工厂满负荷运转；
目标２：加班时间控制在２０工时以内；目标３：至少生产１００升Ａ；目标４：至少生产１２０升Ｂ；目标５：使用完所有的树脂。
假设目标１和目标２的优先权为Ｐ１，且重要程度相等；目标３和目标４的优先权为Ｐ２，且重要程度相等；目标５的优先权为Ｐ３，建立目标规划模型并求解。
如果设在未来两周Ａ、Ｂ两种黏合剂的生产数量分别为ｘ１和ｘ２，则对上述问题我们可以建立如下目标规划模型：
图２
方案管理器对话框
ｍｉｎＰ１ｄ１＋ｄ２，Ｐ２ｄ３＋ｄ４，Ｐ３ｄ５
*,,,,,,,,,,,,,+,,,,,,,,,,,,,-
&.#.#’
－
＋
－
－
－
（公式１）
２０ｘ１＋２５ｘ２＋ｄ１－ｄ１＝４８００
－
＋
－－
＋＋
－＋
２０ｘ１＋２５ｘ２＋ｄ２－ｄ２＝６０００
（公式２）
ｓ．ｔ．ｘ１＋ｄ３－ｄ３＝１００
ｘ２＋ｄ４－ｄ４＝１２０ｘ１＋ｘ２＋ｄ５－ｄ５＝３００
－
＋－
＋
满足了目标１、目标２、目标３和目标４；但未满足目标５，因此，将会有３０升的树脂无法在保质期内加工。
考虑到上述３０升库存树脂无法在保质期内加工的事实，工厂领导决定调整目标的优先权，以保证库存的３００升树脂在两周内全部用完。调整后的优先权为：目标５的优先权为Ｐ１，目标３和目标４的优先权为Ｐ２，且重要程度相等；目标１和目标２的优先权为Ｐ３，且重要程度相等，重新建立目标规划模型并求解。
工厂领导希望通过调整目标优先权次序的方案来解决３０升库存树脂无法在保质期内加工的问题是否可行呢？可以通过对前面已经建立的目标规划模型进行灵敏度分析来回答这一问题。调整目标优先权次序相当于对各偏差变量在目标函数中的系数进行灵敏度分析。对于工厂领导调整目标优先权，我们可以用如下目标函数来表示：
（公式４）ｍｉｎｚ＝Ｐ１ｄ５＋Ｐ２ｄ３＋ｄ４＋Ｐ３ｄ１＋ｄ２
而约束条件不变，仍为公式２。
我们可以借助于Ｅｘｃｅｌ中的［工具］［方案］来完成调整目标优先权后的模型求解。具体操作步骤如下：
第一步，在如图１所示结果的界面中，选择菜单［工
添具］［方案］，将出现如图２所示对话框，点击对话框中的“
加（Ａ）”按钮，出现如图３所示对话框。在本例中，我们将工
，因此，在“方案名（Ｎ）”文本框厂的初始方案命名为“Ａ１”
－
.#.#
－
－
－
＋
ｘ１，ｘ２，ｄｉ，ｄｉ≥０ｉ＝１，…，５
为了便于用Ｅｘｃｅｌ中的［工具］［规划求解］对上面的目标规划问题求解，我们采用了第一种处理方法，将目标函数表示为各偏差变量和的形式：
ｍｉｎｚ＝Ｐ１ｄ１＋ｄ２＋Ｐ２ｄ３＋ｄ４＋Ｐ３ｄ５
.#.#
－
＋
－
＋
－
（公式３）
４６／
ＣＨＩＮＡＭＡＮＡＧＥＭＥＮＴＩＮＦＯＲＭＡＴＩＯＮＩＺＡＴＩＯＮ
企业管理信息化
ｄ５＋Ｐ２ｄ３＋ｄ４＋Ｐ３ｄ１＋ｄ２，这意味着目标函数中某些偏差变量的系数发生变化，所以，我们必须调整图１中单元格￥Ｄ￥７，￥Ｇ￥７和￥Ｌ￥７的值，因为这些单元格所代表的偏差变量在目标函数中系数由于调整目标的优先权而发生了变化。调整后：￥Ｄ￥７＝１，￥Ｇ￥７＝１，￥Ｌ￥７＝１０００。再次用［工具］［规划求解］命令求解，所得结果如图５所示。
从图５可以看出各变量的值为：ｘ１＝１８０，ｘ２＝１２０，ｄ１＝０，
－
－
!
－
－
－
＋
图４
图３
添加方案对话框
方案变量值输入对话框
ｄ１＝１８００，ｄ２＝０，ｄ２＝６００，ｄ３＝０，ｄ３＝８０，ｄ４＝０，ｄ４＝０，ｄ５＝０，ｄ５＝０。
－
＋
－
＋
＋－＋－＋
图６
方案管理器对话框
图８
方案摘要
即调整目标的优先权后，工厂在未来两周应该生产黏合剂Ａ和Ｂ分别为１８０升和１２０升，这样将保证所有库存的树脂全部在保质期内加工完，但工人的加班时数将增加１０工时。
第三步，按照与第一步相同的操作，将调整目标的优先权后所建模型的计算结果命名为方案“。利用上述方Ａ２”
法，我们还可以很容易地检验目标优先权调整后的其他方
案。
第四步，显示方案摘要，以便于管理者比较目标优先权变化时，工厂各有关目标实现情况，从而做出最符合企业实际的决策。显示方案摘要的具体操作如下：当我们在图２所示的“方案管理器对话框”中添加方案后，会出现如图６所示对话框，点击图６中的“摘要（Ｕ）”按钮，会出现如图７所示对话框，在结果类型复选框中选择“方案摘要（Ｓ）”，在结果单元格（Ｒ）文本框中输入：￥Ｎ￥７，再点击“确定”按钮，可以得到如图８所示方案摘要。方案摘要将展示不同方案中各变量的取值，有利于管理者比较不同方案的结果。
图７
方案摘要对话框
三、结束语
图５
调整目标的优先权后利用Ｅｘｃｅｌ中的［工具］［规划求解］
命令求解的结果
中输入Ａ１；在“可变单元格（Ｃ）”文本框中输入“￥Ｂ￥８∶￥Ｍ
。需要说明的是，这里单元格￥Ｂ￥８∶￥８，￥Ｄ￥７，￥Ｇ￥７，￥Ｌ￥７”￥Ｍ
￥８是目标规划问题中待求的决策变量和偏差变量，应该作为可变单元格来处理，而单元格“是目标￥Ｄ￥７，￥Ｇ￥７，￥Ｌ￥７”
函数中某些偏差变量的系数，因为它们会随着工厂领导调整目标的优先权而发生改变，所以也被作为可变单元格来处理。然后，单击图３中的“确定”按钮，出现如图４所示对话框，由于系统默认的值就是我们要输入的每个可变单元
确定”即可，这样工厂的初始方案被格的值，所以只需按“
作为方案Ａ１保存起来了。
第二步，调整目标的优先权后，目标函数变为ｍｉｎｚ＝Ｐ１
由此可见，用户不但可以利用Ｅｘｃｅｌ中的［工具］［规划求解］命令对目标规划进行求解，而且可以运用Ｅｘｃｅｌ的［工具］［方案］命令来保存计算结果，利用Ｅｘｃｅｌ提供的这一功能用户可以完成对目标规划的灵敏度分析，因而轻易地获得由于调整目标优先权（Ｐｌ）、资源约束（ｂｉ）或者资源消耗系数（ａｉｊ）而产生的新方案，从而帮助管理者甄别各种不同方案，为其做出科学决策提供支持。
主要参考文献
［１］韩伯棠．管理运筹学［Ｍ］．第２版．北京：高等教育出版社，２００５．［２］胡运权．运筹学教程［Ｍ］．第２版．北京：清华大学出版社，２００３．［３］周德镇．Ｅｘｃｅｌ在现代管理中的应用［Ｍ］．北京：电子工业出版社，
１９９７．
［４］刘珊等．多目标规划的Ｅｘｃｅｌ求解方法［Ｊ］．科技广场，２００５，
（１０）：９１－９２．
ＣＨＩＮＡＭＡＮＡＧＥＭＥＮＴＩＮＦＯＲＭＡＴＩＯＮＩＺＡＴＩＯＮ
／４７