第十三章 运学问题的Excel建模及求解.docVIP

第十三章 运筹学问题的Excel建模及求解 学习运筹学的目的在于学会用运筹学的方法解决实践中的管理问题，注重学以致用.很多实际问题利用人工计算要经过长时间的艰苦工作才能完成甚至根本无法求解，但若使用运筹学软件则瞬间就能解决.因此在学习过程中不仅要掌握运筹学的基本理论和计算方法，还要充分利用现代化的手段和技术. 微软的电子表格软件（Microsoft Excel）为展示和分析许多运筹学问题提供了一个功能强大而直观的工具，它现在已经被应用于管理实践中. 本章将重点介绍如何建立和求解规划问题的电子表格模型，对于解决大量的中、小规模的实际规划问题，电子表格软件是远远优于传统的代数算法的. 第一节 Excel中的规划求解工具 本节中，我们将举例说明如何使用微软Excel以电子表格的形式建立线性规划模型，并利用Excel中的规划求解工具对模型求解. 一、在Excel中加载规划求解工具 要使用Excel应首先安装Microsoft Office，然后从屏幕左下角的[开始]—[程序]中找到Microsoft Excel并启动.在Excel的主菜单中点击[工具]—[加载宏]，选择“规划求解”，如图13-1所示.点击[确定]后，在工具菜单中将增加[规划求解]选项. 二、在Excel中建立线性规划模型 我们以例2-1为例说明如何在电子表格中建立该问题的线性规划模型.建立电子表格模型时既可以直接利用问题中所给的数据和信息，也可以利用已建立的代数模型.本例的代数模型为： 目标函数 图13-2显示了将该例的数据转送到电子表格中后所建立的电子表格数学模型（本例是一个线性规划模型）.其中显示数据的单元格称为数据单元格，包括生产每单位药品Ⅰ和Ⅱ所需要的4种设备的台时数（单元格C5：D8），药品Ⅰ和Ⅱ的单位利润（单元格C9：D9），4种设备可用的台时数（单元格G5：G8）. 我们要做的决策是两种药品各生产多少；对这一决策的约束条件是生产两种药品所需的4种设备台时的限制；判断这些决策的优劣程度的指标是生产这两种药品所获得的总利润（决策目标）. 如图13-3所示，将决策变量（药品Ⅰ、Ⅱ的产量）分别放入单元格C10和D10，正好在两种药品所在列的数据单元格的下面.由于不知道这些产量会是多少，故在图13-3中均设为零（空白的单元格默认取值为零.实际上，除负值外的任何一个试验解都可以）.以后在寻找产量最佳组合时这些数值会被改变.因此，含有需要做出决策的单元格称为可变单元格. 两种药品所需的4种设备台时总数分别放入单元格E5至E8，正好在对应数据单元格的右边.由于所需的各种设备台时总数取决两种药品的实际产量，如：E5=C5×C10+D5×D10（可直接将公式写入E5，也可利用SUMPRODUCT 函数，E5=SUMPRODUCT（C5：D5，C10：D10），此函数可以计算若干维数相同的数组的彼此对应元素乘积之和），因此当产量为零时所需各种设备台时的总数也为零.由于E5至E8单元格每个都给出了依赖于可变单元格（C10和D10）的输出结果，它们因此被称为输出单元格.作为输出单元格的结果，4种设备台时数的总需求量不应超过其可用台时数的限制，所以用F列中的来表示. 两种药品的总利润作为决策目标进入单元格E9，正好位于用来帮助计算总利润的数据单元格的右边.类似于E列的其他输出单元格，E9 = C9×C10+D9×D10或E9 = SUMPRODUCT（C9：D9，C10：D10）.由于它是在对产量做出决策时目标值定为尽可能大的特殊单元格，所以被称为目标单元格. 根据对上述建模过程的总结，在电子表格中建立线性规划模型的步骤可归纳如下： 1．收集问题的数据，并将数据输入电子表格的数据单元格； 2．确定需要做出的决策，并且指定可变单元格显示这些决策； 3．确定对这些决策的限制（约束条件），并将以数据和决策表示的被限制的结果放入输出单元格； 4．选择要输入目标单元格的以数据和决策表示的决策目标. 三、应用电子表格求解线性规划模型 上例的求解过程可通过在Excel的工具菜单中选择“规划求解”开始.“规划求解”对话框如图13-4所示. “规划求解”开始前，可通过键入单元格地址或选中单元格的方式确定模型的每个组成部分设置在电子表格的何处（单击暂时隐藏对话框，再从工作表中选定单元格，然后再次单击）.如目标单元格地址为E9，可变单元格地址范围为C10：D10，并选中最大值（M）表示要最大化目标单元格. 约束条件的设定可通过点击对话框中的“添加”按钮，弹出图13-5所示的添加约束对话框.由于各种设备台时的总需求量均不应超过可用台时数的限制，故单元格E5到E8必须小于或等于对应的单元格G5到G8.即在添加约束对话框的左端输入范围E5：E8（可用选中单元格的方式），中间选