运筹学实验指导书

Excel中规划求解宏模块的使用

Excel自带的宏模块“规划求解”可用于求解线性规划、非线性规划、整数规划的最优解。

规划求解宏模块在Excel普通运行状况下一般不会启动，当需要调用时，可以从工具菜单条中加载宏来启动，其基本步骤如下。

（1） 在工具菜单中选择“加载宏”选型。

（2） 在加载宏对话框中选择“规划求解”选型。

图0-1加载“规划求解”宏

（3） 如果成功加载，则在工具菜单条中会出现“规划求解”选型。 由此，可以运用规划求解宏模块求解任何一个线性规划问题、整数规划问题、非线性规划问题，分别举例说明如下。

例1 营养配餐问题 根据生物营养学理论，一个成年人每天要维持人体正常的生理健康需求，需要从食物中获取3000卡路里热量、55g蛋白质和800mg钙。假定市场上可供选择的食品有猪肉、鸡蛋、大米和白菜，这些食品每千克所含热量和营养成分以及市场价格如表1-1所示。如何选购才能在满足营养的前提下，使购买食品的总费用最小？

表0-1 营养配餐问题数据表

序号 1 2 3 4 食品名称 猪肉 鸡蛋 大米 白菜 热量/卡路里 1200 800 900 200 蛋白质/g 50 60 20 10 钙/mg 400 200 300 500 价格/元/kg 20 8 4 2

1

解，建立该问题的线性规划模型如下：

假设xj（j=1,2,3,4）分别为猪肉、鸡蛋、大米和白菜每天的购买量，则其线性规划模型为：

minz?20x1?8x2?4x3?2x4?1200x1?800x2?900x3?200x4?3000?50x?60x?20x?10x?55 ?1234s.t.??400x1?200x2?300x3?500x4?800?xj?0(j?1,2,3,4)?第一步：需要在Excel中建立该问题的电子表格模型，如图0-2所示。

图0-2 营养配餐问题的Excel表模型

其中单元格B10：E10设置为决策变量单元格，F12设置为目标单元格，F4：F6设置为三个约束条件的左边项，即表示实际获得的营养。目标单元格和约束条件左边项的函数如图0-3所示

图0-3营养配餐问题中的公式设置

函数sumproduct(区域1，区域2)为Excel的常用函数，表示将区域1中对应元素与区域2中对应元素相乘后再相加。

第二步：调用Excel中的“规划求解”宏，并设置目标单元格、可变单元格（即决策变量）、约束条件地址参数，如图0-4所示。

2

图0-4 规划求解参数设置

“规划求解参数”对话框的作用就是让计算机知道模型的各个组成部分放在电子表格中的什么地方，可以通过键入单元格（或单元区域）的地址或用鼠标在电子表格相应的单元格（或单元区域）单击或拖动的方法将有关信息加入对话框相应的位置。具体步骤如下。

（1） 设置目标单元格

在“规划求解参数”对话框中指定目标函数所在单元格的引用位置，此目标单元格经求解后获得某一特定数值、最大值、最小值。

（2） 设置可变单元格

可变单元格指定决策变量所在的各单元格，不含公式，可以有多个单元格或区域，当单元格或区域不连成一片时，各区域之间用逗号隔开。求解是，可变单元格中的数据不断地调整，直到满足约束条件，并使“设置目标单元格”编辑框中指定的单元格达到目标值。可变单元格必须直接或间接与目标单元格相联系。

（3） 添加约束

在“规划求解参数”对话框中单击“添加”按钮就会显示“添加约束”对话框。

图0-5 规划求解添加约束

在添加约束对话框中有3个选项需填写，其中：

1）单元格引用位置，指定需要约束其中数据的单元格或单元格区域，一般在此处添加约束函数不等式左侧的函数表达式的单元格或单元格区域。

2）运算符，对于不同类型的约束条件，可以选定相应的关系运算符来表示约束关系。其中int表示决策变量为整数，bin表示决策变量为二进制变量。 3）约束值，表示约束条件右边的限制值，在此编辑框中输入数值、右边限制值单元格引用或区域引用。

4）添加，单击此按钮可以在不返回“规划求解参数”对话框的情况下继续添加其他约束条件。当已经把所有约束条件都一一添加了，只需单击“确定”按

3

钮，返回“规划求解参数”对话框，“约束”栏中已经显示了刚添加的约束。 第三步，单击选项按钮，弹出“规划求解选项”对话框，图0-6所示。

图0-6“规划求解选项”对话框

规划求解选项对话框中的选项可以对求解运算的一些高级属性进行设定，这些高级属性选项如下。

（1） 最长运算时间

在此设定求解过程的时间，可输入的最大值为32767，默认值为100，可以满足大多数小型规划求解的需要，此选择项一般在求解非线性规划时才设置。

（2） 迭代次数

在此设定求解过程中迭代运算的次数，限制求解过程所花费的时间。可输入的最大值为32767，默认值为100，可以满足大多数小型规划求解的需要，此选择项一般在求解非线性规划时才设置。

（3） 精度

在此输入用于控制求解精度的数字，以确定约束条件单元格中的数值是否满足目标值的上下限。精度一般运用于非线性规划问题，并且必须由一个0-1之间的小数表示。设置的数值越少，精度越低。

（4） 收敛度

在此输入收敛度数值，当最近五次迭代时，目标单元格中数值的变化小于“收敛度”编辑框中设置的数值时，“规划求解”停止运算。收敛度只运用于非线性规划问题，并且必须由一个0-1之间的小数表示。设置的数值越小，收敛度就越高。

（5） 采用线性模型

当模型中所有的关系都是线性的并希望解决线性优化问题时，选中复选框可加速求解进程。

（6） 显示迭代结果

如果选中此复选框，每进行一次迭代后都将中断“规划求解”过程，并显示当前的迭代结果。

（7） 假定非负

对于在“添加约束”对话框的“约束值”编辑框中没有设置下限的可变单元

4

格，假定其下限为0。规划问题一般要求决策变量非负，所以一般都需要选择此选择项。

在本例中，只要选中“采用线性模型”，“假定非负”即可。单击“确定”按钮回到规划求解参数对话框。

第四步：单击规划求解参数对话框中的“求解”按钮，弹出“规划求解结果”

对话框。

图0-7“规划求解结果”对话框

当规划求解得到答案时，“规划求解结果”对话框中会给出下面两条求解结果信息。

（1）“规划求解”找到一个解，可满足所有的约束及最优化要求。这表明按“规划求解选项”对话框中设置的精度，所有的约束条件都已经满足，并且目标单元格达到极大值或极小值，表示已经求出了问题的最优解。

（2）“规划求解”收敛于当前结果，并满足所有的约束条件。这表明目标单元格中的数值在最近5次求解过程中的变化量小于“规划求解选项”对话框中“收敛度”设置的值。“收敛度”中设置的值越小，“规划求解”在计算时就会越精细，但求解过程将花费更多的时间。

当规划求解不能得到最佳结果时，在“规划求解结果”对话框中就会显示下述信息：

（1） 满足所有约束条件，“规划求解”不能进一步优化结果。这表明仅

得到近似值，迭代过程无法得到比显示结果更精确的数值，或是无法进一步提高精度，或是精度值设置得太小，请在“规划求解选型”对话框中试着设置较大的精度值，再运行一次。

（2） 求解达到最长运算时间后停止。这表明在达到最长运算时间限制

时，没有得到满意的结果，如果保存当前结果并节省下次计算的时间，单击“保存规划求解”或“保存方案”选项即可。

（3） 求解达到最大迭代次数后停止。这表明在达到最大迭代次数时，

仍没有得到满意的结果，增加迭代次数也许有用，但是应该先检查结果确定问题的原因。如果要保存当前结果并节省下次计算的时间，单击“保存规划求解” “保存方案”选项即可。

（4） 目标单元格中数值不收敛。这表明即使满足全部约束条件，目标

单元格数值也只是有增有减但不收敛。这可能是在设置问题时忽略了一项或多项约束条件。请检查工作表中的当前值，确定目标发散的原因，并检查约束条件，然后再次求解。

（5） 规划求解未找到合适的结果。这表明在满足全部约束条件和精度

要求的条件下，“规划求解”无法得到合理的结果，这可能是约束条件不一致所致。请检查约束条件公式或类型选择是否有误。

（6） 规划求解在目标或约束条件单元格中发现错误值。这表明在最近

5

搜索“diyifanwen.net”或“第一范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，第一范文网，提供最新资格考试认证运筹学实验指导书 全文阅读和word下载服务。