实用运筹学-运用Excel建模和解课程设计

图 13-1第十三章 运筹学问题的Excel 建模及求解 学习运筹学的目的在于学会用运筹学的方法解决实践中的管理问题.注重学以致用.很多实际问题利用人工计算要经过长时间的艰苦工作才能完成甚至根本无法求解.但若使用运筹学软件则瞬间就能解决.因此在学习过程中不仅要掌握运筹学的基本理论和计算方法.还要充分利用现代化的手段和技术.微软的电子表格软件（Microsoft Excel ）为展示和分析许多运筹学问题提供了一个功能强大而直观的工具.它现在已经被应用于管理实践中.本章将重点介绍如何建立和求解规划问题的电子表格模型.对于解决大量的中、小规模的实际规划问题.电子表格软件是远远优于传统的代数算法的.第一节 Excel 中的规划求解工具本节中.我们将举例说明如何使用微软Excel 以电子表格的形式建立线性规划模型.并利用Excel 中的规划求解工具对模型求解.一、在Excel 中加载规划求解工具要使用Excel 应首先安装MicrosoftOffice.然后从屏幕左下角的[开始]—[程序]中找到Microsoft Excel 并启动.在Excel 的主菜单中点击[工具]—[加载宏].选择“规划求解”.如图13-1所示.点击[确定]后.在工具菜单中将增加[规划求解]选项. 二、在Excel 中建立线性规划模型我们以例2-1为例说明如何在电子表格中建立该问题的线性规划模型.建立电子表格模型时既可以直接利用问题中所给的数据和信息.也可以利用已建立的代数模型.本例的代数模型为：图 13-2 图 13-3目标函数 21300200x x Z +=max⎪⎪⎪⎩⎪⎪⎪⎨⎧≥≤≤≤+≤+0,124164821222..21212121x x x x x x x x t s图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种设备台时数的总需求图 13-4图 13-5 量不应超过其可用台时数的限制.所以用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（可用选中单元格的方图 13-6图 13-7图 13-8式）.中间选择<=（点开下拉列表进行选择）.右端输入范围G5：G8.如果模型中还包含其他类型的函数约束.则可点击“添加”按钮以弹出一个新的添加约束对话框.根据输出单元格与约束值之间的关系在对话框中间的下拉列表中选择适当的约束类型.以增加新的约束.但本例中已无其他约束了.所以只要点击“确定”按钮返回“规划求解”对话框.如果需要修改或删除已添加的约束.可选中该约束后点击“更改”或“删除” 按钮.到现在为止“规划求解”对话框已根据图13-3的电子表格描述了整个模型（见图13-4）.但在求解模型前还需要进行最后一个程序.点击“选项”按钮弹出图13-6所示的选项对话框.这个对话框中是一些关于如何求解问题的细节的选项.对于决策变量取值非负的线性规划模型.最主要的选项是“采用线性模型”和“假定非负”选项.（见图13-6）.关于其他选项.对小型问题来说接受图中所示的默认值通常比较合适.点击“确定”按钮返回“规划求解”对话框.现在可以点击“规划求解”对话框中的“求解”按钮了.它会在后台开始对问题进行求解.对于一个小型问题.几秒钟之后“规划求解”就会显示运行结果.如图13-7所示.它会显示已经找到了一个最优解.如果模型没有可行解或没有最优解.对话框会显示“规划求解找不到可行解”或“设定的单元格值不能集中”.对话框还显示了产生各种报告的选项.后面将会介绍.选择“保存规划求解结果” 并点击“确定” 按钮.返回电子表格模型.求解模型之后.如图13-8所示.“规划求解”用最优解和最图 13-9优值代替了可变单元格和目标单元格中的初始值.因此.最优解是生产4公斤药品Ⅰ和2公斤药品Ⅱ.最优值为1400元.与图解法的结果一致.图13-9显示的是例2-2的电子表格模型及求解过程.这个问题的电子表格模型建立与求解过程与例2-1描述的基本相同.数据单元格（C5：E8）、（C9：E9）和（H5：H8）分别存放三种原料B 1、B 2、B 3每斤所含四种营养成分的数量、每斤原料的单价以及食品所要求的最低营养成分的含量限制.可变单元格（C10：E10）存放三种原料配比情况（图13-9的左上部分）.输出单元格（F5：F8）给出了食品中实际的营养成分含量.目标单元格（F9）显示了该种食品的总成本（图13-9的左下部分）.图13-9的右下角显示了“规划求解”对话框的主要部分.包括为目标单元格和可变单元格设定的地址.约束条件F5≥H5.F6≥H6.F7≥H7和F8≥H8通过“添加约束”对话框显示在“规划求解” 对话框中.由于目标是最小化总成本.所以选择了“最小值（N ）”.图13-9的右上角显示了点击“规划求解” 对话框的“选项”按钮后所选择的选项.“采用线性模型”先期定义了这个模型是线性规划模型.“假定非负”选项定义了可变单元格必须是非负约束.因为食品的配比不可能出现负值.点击“规划求解” 对话框的求解按钮后.得到了图13-9中电子表格的可变单元格中显示的最优解.即该食品配比为原料B 1 是1.94斤.原料B 3是2.36斤.成本为109.72元.与单纯形法人工求解不同.如果输出单元格、可变单元格或目标单元格结果不是整数.电子表格是以小数而非分数形式显示的.本例结果以四图 13-10舍五入的方式保留了两位小数.第二节 线性规划的应用问题一、合理用料问题这是第二章第五节的第一个问题.由于原料胶管的长度为15分米.而输液管、止血带和听诊器胶管分别长5.7、4.2和3.1分米.所以每根原料胶管最多可截三种材料依次为2根、3根和4根.即总的截法不超过3×4×5 = 60（种）.又由于每种截法的料头不能超过2分米.所以可先通过电子表格进行试算以选择其中可行的几种截法.再利用线性规划的方法找出用料根数最少的方案.如图13-10的左上部分所示.单元格C4至E4显示三种胶管的长度；C5至E5输入不同的方法截出每种胶管的根数；F4为对应C5至E5的不同截法所剩料头的长度. F5通过判断剩余料头的长度是否在0到2之间显示出该种解法是否可行.单元格F4和F5的公式见图13-10的左下部分.不断变换C5至E5的可能取值并选择其中可行的截法（共6种）.在电子表格中建立该问题的线性规划模型.数据单元格为C9：H11、C12：H12和K9：K12.分别显示每种截法截一根原料胶管时得到三种不同材料的数量、每种截法截取一次所用胶管的数量和三种材料的需要量；可变单元格C13：H13显示采用每种截法所截的胶管原料数；输出单元格I9：I12列出了某一截取方案实际获得的三种材料数量.每种材料的数量等于各种截法截得该材料数与对应截法所截原料数的乘积之和.如输液管的数量I9 = SUMPRODUCT(C9:H9,C13:H13)；目标单元格I12图 13-11为总用料数.应等于各种截法所截原料数之和,即I12 = SUM(C13:H13).图13-10的右半部分显示了“规划求解”对话框及“选项”对话框的内容.该问题的目标是所用的胶管原料的总根数最少.因此设置目标单元格为I12等于最小值.由于实际获得的材料数量必须满足需求量的要求.考虑到最优方案（各种截法的某一组合）不一定能使截出的三种材料数量恰好等于需要的数量.而某种材料超过需求量是允许的.故在添加约束时可设置实际截得的数量大于等于需求量.即I9：I12>=K9：K12（本题中.该约束取“>=”和“=”的结果是相同的）；又由于截出的各种材料数量均为整数.因此约束中应包括决策变量取整数的限制.即C13:H13=整数.图13-10的左上部分显示了该问题的最优方案为：分别用第二种、第四种和第五种截法截取原料40、60和10根.共用原料110根.与第二章中用大M 法求解的结果一致.二、放射科的业务安排图13-11显示了第二章问题二的电子表格模型及求解过程.该问题的数据包括：进行三种检查的单位时间（C5：E5）.三种检查设备每月的可用时间（C9：E9）.三项业务每月最多提供量（H6）以及每项业务的单位利润（C10：E10）.可变单元格为C6至E6.给出三项业务每月的实际发生数量.输出单元格为C7至E7和F6.分别表示根据各项业务的实际发生数量产生的设备使用时间及实际的总业务量.目标单元格F10显示由每项业务的单位利润及每月实际发生数量计算的总利润.图13-11的左下部分给出了输出单元格及目标单元格的公式.图13-11右下部分的“规划求解”对话框显示了求解时应注意的问题：求目标单元格的最大值（利润最大）；约束为设备的实际使用时间小于等于设备的可用时间及实际总业务量小于等于总业务提供量的限制.打开“选项”对话框.仍选择“采用线性模型”和“假定非负”.回到“规划求解”并按“求解”按钮.得到问题的最优方案为：每月X 线及CT 检查的业务量分别为1320人次和480人次.磁共振业务量为0.即不必购买该设备；按最优方案安排业务每月可获利55200元.在电子表格上建立线性规划或其它问题模型的方式是非常灵活的.不必拘泥于一种固定的模式.本书仅提供了一种建立模型的思路.读者可根据不同问题的特点以及个人的习惯或喜好建立不同风格的电子表格模型.第三节 线性规划的灵敏度分析前面指出线性规划模型的许多参数.都只是对实际数据的大致估计.而不可能在研究的时候就获得精确的数值.通过灵敏度分析可以得出每一个估计的数据需要精确到何种程度.才能保持解的最优性.回忆例2-1某制药厂的生产计划问题.其求解结果如图13-8所示.即生产4公斤药品Ⅰ和2公斤药品Ⅱ.总利润为1400元.但该最优解是在假设所有的模型参数都准确的前提下做出的.在此基础上.管理层如果进一步考虑下列问题：1．如果在该厂生产的药品中.有一个单位利润的估计值是不准确的.将会发生怎样的情况？2．如果该工厂两种药品的单位利润的估计都是不准确的.又将会怎样？3．如果改变该厂某种设备可用于生产的时间.会对结果产生什么影响？4．如果四种设备可用于生产的时间同时改变.又会对结果产生何种影响？ 在本节中.我们将重点介绍如何利用“规划求解”中的“敏感性报告”对目标函数系数j c 以及约束条件右端值i b 的变动进行灵敏度分析.分析的内容主要是系数在什么范围内变化时.已得到的最优解保持不变.即发现哪些系数不太敏感（由于在较大范围内变化时.最优解保持不变.故可以进行粗略估计）.哪些系数比较敏感（即使微小的改变都会对最优解产生影响.故必须对其精确定义）.图 13-12图 13-13一、目标函数系数变动的灵敏度分析首先介绍目标函数系数的灵敏度分析.回顾一下就可以知道.这些系数表示各种决策对总目标的单位贡献.下面以例2-1某药厂的生产计划问题的目标函数系数变动情况进行讨论.问题1：如果该药厂一种药品的单位利润的估计是不精确的.结果怎样？ 首先看一下.如果药品Ⅱ的单位利润300元的估计是不精确的情况.假设：药品Ⅱ的单位利润 = 电子表格中D9单元格中的数据现在.2c =300元.下面我们来分析一下在保持最优解)2,4(),(21 x x 不变的条件下.2c 可能的最大值与最小值.这样.也就可以看出2c 为300元的这一估计能够在多大程度上偏离实际值而不会改变解的最优性.（一）使用电子表格进行灵敏度分析电子表格的一个强大的优点就是可以方便互动地展开各种形式的灵敏度分析.通过运用规划求解工具来求解最优解.模型参数值的改变所造成的影响一下子就可以显示出来.为了说明这一点.图13-12显示了药品Ⅱ的单位利润从开始的2c =300元降到2c =250元的情况.与图13-8相比.最优解没有丝毫的变化.事实上.该问题唯一的变动是电子表格中C9单元格中的数据从300元降到250元.以及E9单元格总利润减少了100元（因为每单位药品Ⅱ所提供的利润减少了50元）.因为最优解没有变动.我们可以知道在不影响最优解的前提下.药品Ⅱ的单位利润2c =300元的最初估计是较高的.图 13-14那么.如果这一估计值较低又会怎样呢？图13-13表示了将2c =300元增加到2c =350元的情况.同样.最优解没有发生变化.因为.增加或减少最初的2c =300元均不会对最优解产生任何影响.2c 就不是很敏感的系数.也就不需要为了保证最优解不会改变.而花很大力气去得到2c 的更精确的值.但是对2c 的研究至此并没有结束.因为实际值很可能会超出250到350元这一范围.那么在保持最优解不变的条件下.2c 到底可以在什么样的范围内取值呢？当然可以在电子表格中采取试验的方法.不断增加或减少的2c 值.直到最优解发生改变.以找到最优解发生变化时对应的2c 值.但是.这样计算太麻烦了.是否有简便一些的方法呢？答案是肯定的.（二）利用敏感性报告进行目标系数的灵敏度分析如图13-7所示.在求得最优解之后.规划求解工具会给出相应的信息.同时.在其右边列出了它可以提供的三个报告.选择第二项敏感性报告的选项.就可以得到灵敏度的分析报告.它显示在模型的工作表之前.图13-14显示了本例敏感性报告中的一部分.终值一栏表明了问题的最优解.第二栏给出了递减成本.递减成本提供了为使决策变量取正值.相应的目标系数需要减少的数量.对于本例.由于两决策变量的取值均为正数.故递减成本均为零.第三栏表示了目标函数的现值.最后两栏表示为使最优解保持不变.目标系数允许增加与减少的最大值.例如.考虑决策变量X 1的目标系数1c .从图13-14中表示产品Ⅰ的一行中可知.1c 可以减少50.可以增加1E+30.在电子表格中1E+30是1030的缩写.Excel 使用这一极大的数值来表示无穷大.因此.从灵敏度的分析报告中可知：1c 的现值： 2001c 的允许增加值： 无穷大 此时1c 无上限1c 的允许减少值： 50 此时150502001=-≥c1c 的变化范围： 1501≥c因此.只要在上面的变化范围内变动.并且不改变模型的其他任何内容.最优解将始终保持在)2,4(),(21=x x 不变.该药厂的另一药品的单位利润的变化范围也可以用同样的方法得出.2c 是药品Ⅱ的单位利润.表中表示药品Ⅱ的第二行给出了下面关于2c 的信息：2c 的现值： 3002c 的允许增加值： 100 此时4001003002=+≤c 2c 的允许减少值： 300 此时03003002=-≥c 2c 的变化范围： 40002≤≤c 目标函数的两个系数的允许变化范围都很大.因此.尽管药品Ⅰ和药品Ⅱ的单位利润可能仅仅是实际值的一个粗略估计.我们也可以相信.这个估计值对最优解的正确性不会有影响.但在一些线性规划模型中.目标系数微小的变动都可能会影响最优解.这样的系数称为敏感参数.灵敏度的分析报告中会直接显示目标中哪些系数是敏感的.这些系数允许的变化区域很小.因此.必须格外小心.尽量取得这些数据的精确值.在求得模型的最优解之后.目标系数的允许变化范围还有一个很重要的用途.在问题的线性规划分析结束之后.如果外界的环境发生了一定的变化.灵敏度分析可以在无需重新求解的情况下.表明模型参数的变化是否造成了最优解的改变.例如经过一段时间以后.如果药品的单位利润发生了较大的变化.通过其允许变化范围.可以一眼看出原来的最优组合是否依然适用.有了目标系数的允许变化范围.在判断问题时.就不需要重新建模与求解.这一点对线性规划问题的解决是有很大帮助的.特别是在处理一个大型模型时.（三）目标系数的同时变动因为存在许多不确定性因素.目标函数系数的值.如单位利润.通常都只是对图 13-15实际值的估计.上面所讨论的是只有一个系数变动时的情况.这类问题在求解一个系数的允许变化范围时.假设其他所有系数都是正确的.研究的系数是唯一可能与实际值不符的变动的系数.但事实上.所有的系数（至少一个以上）可能同时都是不准确的.如果这样的话.是否可能会导致求得的最优解不正确呢？这是最关键的问题.如果可能对最后的结果产生影响.就必须对这些系数作进一步的分析.另一方面.如果灵敏度分析表明目前的参数估计不会影响最优解的正确性.那么.管理者可以增加对该模型及其所提供的解决方法的信心.以下将介绍如何在不重新求解模型的条件下.确定如果目标函数的几个系数同时变化.可能造成的对最优解的影响．我们仍利用例2-1提出如下问题：问题2：如果该药厂两种药品的单位利润的估计都是不准确的.将会对结果产生怎样的影响？例如.原来药品Ⅰ和药品Ⅱ的单位利润分别为200元和300元.现在由于原料成本的变化.每公斤药品Ⅰ和药品Ⅱ的单位利润分别变为180元和355元.最优解是否发生变化？在分析多个系数同时变动的情况时.仍然要使用敏感性报告中提供的每个系数的允许增加值和减少值数据.下面介绍多个系数同时变动的百分之百法则.首先定义j c 的允许增加（减少）百分比为j c 的增加量（减少量）除以j c 的允许增加量（允许减少量）的值.这样我们可以计算出1c 的允许减少百分比为%4050/)180200(=-.2c 的允许增加百分比为%55100/)300355(=-.2c 的允许减少百分比与2c 的允许增加百分比之和为%95%55%40=+.目标函数系数同时变动的百分之百法则：如果目标函数的系数同时变动.当其所有允许增加百分比和允许减少百分比之和不超过百分之百时.最优解不会改变.如果超过百分之百.则不能确定最优解是否改变.因为本例中1c 的允许减少百分比与2c 的允许增加百分比之和为95%不超过100%.所以当每公斤药品Ⅰ的利润减少为180元.每公斤药品Ⅱ的利润增加为355元时.此线性规划最优解仍然为药品Ⅰ生产4公斤和药品Ⅱ生产2公斤（即2,421==x x ）.此时有最大利润为143071072023554180=+=⨯+⨯（元）.如图13-15所示.这一法则并没有表示出.在变动百分比之和超过百分之百的情况下.可能的结果.这一结果还有赖于系数变动的方向.但是.只要变动百分比之和不超过百分之百.最优解是肯定不会改变的.记住.我们可以让单一的目标函数系数在整个允许范围内变动.但这只有在其他目标函数系数都不变的情况下才有效.如果多个系数同时变动.我们必须研究各个系数的变动百分比.二、约束右端值的灵敏度分析之所以要分析函数约束右端值变动的原因与前面一样.因为在建模时.还不能得到模型的这些参数的精确值.只能对其作粗略的估计.因此.我们希望知道在这些估计不准确的情况下会产生怎样的后果.除此之外一个更主要的理由是因为.这些常数（通常代表资源的可用量）往往不是由外界决定的而是管理层的政策决策.因此管理者希望知道如果改变这些决策是否会提高最终的收益.影子价格分析就是为管理者提供这方面的信息.下面是关于例2-1的第三个问题：问题3：如果改变该厂某设备可用于生产的时间.结果将如何？（一）约束右端值的影子价格分析回忆第二章中关于影子价格的经济含义.我们知道影子价格代表单位资源在最优利用的条件下所产生的经济效果.即在模型获得最优解的情况下.约束条件右端值在一定范围内每增加（减少）一个单位.使目标函数值增加（减少）的量.其中.一定范围是指保持影子价格不变的右端值变化范围.在影子价格分析中.每次分析一个函数约束.可以将该函数约束右端值的常数增加一个单位后重新求解.观察目标函数值增加的量来确定影子价格.也可以利用灵敏度报告中提供的关于每一个函数约束的影子价格数据.从一个约束的影子价格中就可以直接看出.决策改变而引起的约束常数的改变所造成的影响.只要约束常数的变动不大.那么目标函数值的变动就等于约束常数的变动（正或负）乘以影子价格.为了说明影子价格的含义.我们以第二章。

让利益最大化——关于皇氏乳业加工奶制品的生产计划摘要：如今乳制品的市场竞争越来越强，原料成本正在增加，为了提高皇氏乳业的竞争力，提高公司的利润，公司决定开发新产品，原料奶油及中老年奶粉。

先对皇氏乳业的原料成本，生产时间，产品利润等做了一系列调查，建立了线性规划模型，在对模型求解并进行灵敏度分析后，给出具体的对策建议。

关键词：线性规划；生产成本；最优生产计划一、问题的提出经过调查，每一桶牛奶的生产成本和利润如下表：每天至多加工50桶牛奶，机器最多使用480小时，至多加工100kg奶油A1。

（一）如何制定生产计划，使每天获利最大？（二） 35元可以买到一桶牛奶，买吗？若买，每天最多买多少？（三）可聘用临时工人，付出的工资最多是每小时几元？（四）奶油A1的获利增加到30元/公斤，是否改变生产计划？1.问题分析首先，工厂的经济效益主要取决于原料，劳动时间，产品利润等，至于劳动机械磨损，工人熟练程度等，均不予考虑。

所以我们主要研究原料成本，劳动时间，产品利润与工厂经济效益的关系。

2.数据的收集整理对于奶油A1、奶粉A2的产量，询问工厂管理人员得知。

对于加工时间，可以通人力资源管理部门查询。

对于利润，通过近期一个月的销售成绩，综合分析得出。

二、运筹模型1、模型的建立设X1桶牛奶生产奶油A1，X2桶牛奶生产奶粉A2。

Maxz=72X1+64X2St. X1+X2<=5012X1+8X2<=4803X1<=100X1,X2>=02、模型的求解应用EXCEL软件进行求解。

3、灵敏度分析包括对于目标系数（桶数）变化的灵敏度分析结果表和对于约束条件，如原料供应，劳动时间，加工能力等变化的灵敏度分析结果表。

4、结果分析（一）当20桶牛奶生产奶油A1，30桶生产奶粉A2，利润达到3360元，是最大值。

（二）原料增加1单位，利润增加48。

35元<48元，应该买（三）时间增加1单位，利润增加2元，能力增减不影响，所以临时雇用临时工人每小时不超过2元。

运筹学问题的Excel建模及求解图 13-1第十三章 运筹学问题的Excel 建模及求解学习运筹学的目的在于学会用运筹学的方法解决实践中的管理问题，注重学以致用.很多实际问题利用人工计算要经过长时间的艰苦工作才能完成甚至根本无法求解，但若使用运筹学软件则瞬间就能解决.因此在学习过程中不仅要掌握运筹学的基本理论和计算方法，还要充分利用现代化的手段和技术.微软的电子表格软件（Microsoft Excel ）为展示和分析许多运筹学问题提供了一个功能强大而直观的工具，它现在已经被应用于管理实践中.本章将重点介绍如何建立和求解规划问题的电子表格模型，对于解决大量的中、小规模的实际规划问题，电子表格软件是远远优于传统的代数算法的.第一节 Excel 中的规划求解工具本节中，我们将举例说明如何使用微软Excel 以电子表格的形式建立线性规划模型，并利用Excel 中的规划求解工具对模型求解.一、在Excel 中加载规划求解工具要使用Excel 应首先安装MicrosoftOffice ，然后从屏幕左下角的[开始]—[程序]中找到Microsoft Excel 并启动.在Excel 的主菜单中点击[工具]—[加载宏]，选择“规划求解”，如图13-1所示.点击[确定]后，在工具菜单中将增加[规划求解]选项.二、在Excel 中建立线性规划模型 我们以例2-1为例说明如何在电子表格中建立该问题的线性规划模型.建立电子表格模型时既可以直接利用问题中所给的数据和信息，也可以利用已建立的代数模型.本例的代数模型为：图 13-2 图 13-3目标函数 21300200x x Z +=max⎪⎪⎪⎩⎪⎪⎪⎨⎧≥≤≤≤+≤+0,124164821222..21212121x x x x x x x x t s图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种设备台时数的图 13-4图 13-5总需求量不应超过其可用台时数的限制，所以用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（可用选中单元格图 13-6图 13-7的方式），中间选择<=（点开下拉列表进行选择），右端输入范围G5：G8.如果模型中还包含其他类型的函数约束，则可点击“添加”按钮以弹出一个新的添加约束对话框，根据输出单元格与约束值之间的关系在对话框中间的下拉列表中选择适当的约束类型，以增加新的约束.但本例中已无其他约束了，所以只要点击“确定”按钮返回“规划求解”对话框.如果需要修改或删除已添加的约束，可选中该约束后点击“更改”或“删除” 按钮.到现在为止“规划求解”对话框已根据图13-3的电子表格描述了整个模型（见图13-4）.但在求解模型前还需要进行最后一个程序，点击“选项”按钮弹出图13-6所示的选项对话框，这个对话框中是一些关于如何求解问题的细节的选项.对于决策变量取值非负的线性规划模型，最主要的选项是“采用线性模型”和“假定非负”选项，（见图13-6）.关于其他选项，对小型问题来说接受图中所示的默认值通常比较合适，点击“确定”按钮返回“规划求解”对话框.现在可以点击“规划求解”对话框中的“求解”按钮了，它会在后台开始对问题进行求解.对于一个小型问题，几秒钟之后“规划求解”就会显示运行结果.如图13-7所示，它会显示已经找到了一个最优解.如果模型没有可行解或没有最优解，对话框会显示“规划求解找不到可行解”或“设定的单元格值不能集中”.对话框还显示了产生各种报告的选项，后面将会介绍.选择“保存规划求解结果” 并点击“确定” 按钮，返回电子表格模型.求解模型之后，如图13-8图 13-9所示，“规划求解”用最优解和最优值代替了可变单元格和目标单元格中的初始值.因此，最优解是生产4公斤药品Ⅰ和2公斤药品Ⅱ，最优值为1400元，与图解法的结果一致.图13-9显示的是例2-2的电子表格模型及求解过程.这个问题的电子表格模型建立与求解过程与例2-1描述的基本相同，数据单元格（C5：E8）、（C9：E9）和（H5：H8）分别存放三种原料B 1、B 2、B 3每斤所含四种营养成分的数量、每斤原料的单价以及食品所要求的最低营养成分的含量限制，可变单元格（C10：E10）存放三种原料配比情况（图13-9的左上部分）.输出单元格（F5：F8）给出了食品中实际的营养成分含量，目标单元格（F9）显示了该种食品的总成本（图13-9的左下部分）.图13-9的右下角显示了“规划求解”对话框的主要部分，包括为目标单元格和可变单元格设定的地址，约束条件F5≥H5，F6≥H6，F7≥H7和F8≥H8通过“添加约束”对话框显示在“规划求解” 对话框中.由于目标是最小化总成本，所以选择了“最小值（N ）”.图13-9的右上角显示了点击“规划求解” 对话框的“选项”按钮后所选择的选项，“采用线性模型”先期定义了这个模型是线性规划模型，“假定非负”选项定义了可变单元格必须是非负约束，因为食品的配比不可能出现负值.点击“规划求解” 对话框的求解按钮后，得到了图13-9中电子表格的可变单元格中显示的最优解，即该食品配比为原料B 1 是1.94斤，原料B 3是2.36斤，成本为109.72元.与单纯形法人工求解不同，如果输出单元格、可变单元图 13-10格或目标单元格结果不是整数，电子表格是以小数而非分数形式显示的，本例结果以四舍五入的方式保留了两位小数.第二节 线性规划的应用问题一、合理用料问题这是第二章第五节的第一个问题，由于原料胶管的长度为15分米，而输液管、止血带和听诊器胶管分别长5.7、4.2和3.1分米，所以每根原料胶管最多可截三种材料依次为2根、3根和4根，即总的截法不超过3×4×5 = 60（种）.又由于每种截法的料头不能超过2分米，所以可先通过电子表格进行试算以选择其中可行的几种截法，再利用线性规划的方法找出用料根数最少的方案.如图13-10的左上部分所示，单元格C4至E4显示三种胶管的长度；C5至E5输入不同的方法截出每种胶管的根数；F4为对应C5至E5的不同截法所剩料头的长度， F5通过判断剩余料头的长度是否在0到2之间显示出该种解法是否可行，单元格F4和F5的公式见图13-10的左下部分.不断变换C5至E5的可能取值并选择其中可行的截法（共6种），在电子表格中建立该问题的线性规划模型.数据单元格为C9：H11、C12：H12和K9：K12，分别显示每种截法截一根原料胶管时得到三种不同材料的数量、每种截法截取一次所用胶管的数量和三种材料的需要量；可变单元格C13：H13显示采用每种截法所截的胶管原料数；输出单元格I9：I12列出了某一截取方案实际获得的三种材料数量，每种材料的数量等于各种截法截得该材料数与对应截法所截原料数的乘积之和，如输液管的数量I9 = SUMPRODUCT(C9:H9,C13:H13)；目标单元格I12为总用料数，应等于各种截法所截原料数之和,即I12 = SUM(C13:H13).图13-10的右半部分显示了“规划求解”对话框及“选项”对话框的内容.该问题的目标是所用的胶管原料的总根数最少，因此设置目标单元格为I12等于最小值.由于实际获得的材料数量必须满足需求量的要求，考虑到最优方案（各种截法的某一组合）不一定能使截出的三种材料数量恰好等于需要的数量，而某种材料超过需求量是允许的，故在添加约束时可设置实际截得的数量大于等于需求量，即I9：I12>=K9：K12（本题中，该约束取“>=”和“=”的结果是相同的）；又由于截出的各种材料数量均为整数，因此约束中应包括决策变量取整数的限制，即C13:H13=整数.图13-10的左上部分显示了该问题的最优方案为：分别用第二种、第四种和第五种截法截取原料40、60和10根，共用原料110根，与第二章中用大M法求解的结果一致.二、放射科的业务安排图13-11显示了第二章问题二的电子表格模型及求解过程.该问题的数据包括：进行三种检查的单位时间（C5：E5），三种检查设备每月的可用时间（C9：E9），三项业务每月最多提供量（H6）以及每项业务的单位利润（C10：E10）.可变单元格为C6至E6，图 13-11给出三项业务每月的实际发生数量.输出单元格为C7至E7和F6，分别表示根据各项业务的实际发生数量产生的设备使用时间及实际的总业务量.目标单元格F10显示由每项业务的单位利润及每月实际发生数量计算的总利润.图13-11的左下部分给出了输出单元格及目标单元格的公式.图13-11右下部分的“规划求解”对话框显示了求解时应注意的问题：求目标单元格的最大值（利润最大）；约束为设备的实际使用时间小于等于设备的可用时间及实际总业务量小于等于总业务提供量的限制.打开“选项”对话框，仍选择“采用线性模型”和“假定非负”，回到“规划求解”并按“求解”按钮，得到问题的最优方案为：每月X 线及CT 检查的业务量分别为1320人次和480人次，磁共振业务量为0，即不必购买该设备；按最优方案安排业务每月可获利55200元.在电子表格上建立线性规划或其它问题模型的方式是非常灵活的，不必拘泥于一种固定的模式.本书仅提供了一种建立模型的思路，读者可根据不同问题的特点以及个人的习惯或喜好建立不同风格的电子表格模型.第三节 线性规划的灵敏度分析前面指出线性规划模型的许多参数，都只是对实际数据的大致估计，而不可能在研究的时候就获得精确的数值.通过灵敏度分析可以得出每一个估计的数据需要精确到何种程度，才能保持解的最优性.回忆例2-1某制药厂的生产计划问题，其求解结果如图13-8所示，即生产4公斤药品Ⅰ和2公斤药品Ⅱ，总利润为1400元.但该最优解是在假设所有的模型参数都准确的前提下做出的，在此基础上，管理层如果进一步考虑下列问题：1．如果在该厂生产的药品中，有一个单位利润的估计值是不准确的，将会发生怎样的情况？2．如果该工厂两种药品的单位利润的估计都是不准确的，又将会怎样？3．如果改变该厂某种设备可用于生产的时间，会对结果产生什么影响？4．如果四种设备可用于生产的时间同时改变，又会对结果产生何种影响？ 在本节中，我们将重点介绍如何利用“规划求解”中的“敏感性报告”对目标函数系数j c 以及约束条件右端值i b 的变动进行灵敏度分析.分析的内容主要是系数在什么范围内变化时，已得到的最优解保持不变，即发现哪些系数不图 13-12太敏感（由于在较大范围内变化时，最优解保持不变，故可以进行粗略估计），哪些系数比较敏感（即使微小的改变都会对最优解产生影响，故必须对其精确定义）.一、目标函数系数变动的灵敏度分析首先介绍目标函数系数的灵敏度分析，回顾一下就可以知道，这些系数表示各种决策对总目标的单位贡献.下面以例2-1某药厂的生产计划问题的目标函数系数变动情况进行讨论.问题1：如果该药厂一种药品的单位利润的估计是不精确的，结果怎样？ 首先看一下，如果药品Ⅱ的单位利润300元的估计是不精确的情况，假设：药品Ⅱ的单位利润 = 电子表格中D9单元格中的数据现在，2c =300元，下面我们来分析一下在保持最优解)2,4(),(21 x x 不变的条件下，2c 可能的最大值与最小值.这样，也就可以看出2c 为300元的这一估计能够在多大程度上偏离实际值而不会改变解的最优性.（一）使用电子表格进行灵敏度分析电子表格的一个强大的优点就是可以方便互动地展开各种形式的灵敏度分析.通过运用规划求解工具来求解最优解，模型参数值的改变所造成的影响一下子就可以显示出来.为了说明这一点，图13-12显示了药品Ⅱ的单位利润从开始的2c =300元降到2c =250元的情况，与图13-8相比，最优解没有丝毫的变化.事实上，该问题唯一的变动是电子表格中C9单元格中的数据从300元降到250元，以及E 9单元格总利润减少了100元（因为每单位药品Ⅱ所提供的利润减少了50元）.因为最优解没有变动，我们可以知道在不图 13-14 影响最优解的前提下，药品Ⅱ的单位利润2c =300元的最初估计是较高的.那么，如果这一估计值较低又会怎样呢？图13-13表示了将2c =300元增加到2c =350元的情况.同样，最优解没有发生变化.因为，增加或减少最初的2c =300元均不会对最优解产生任何影响，2c 就不是很敏感的系数，也就不需要为了保证最优解不会改变，而花很大力气去得到2c 的更精确的值.但是对2c 的研究至此并没有结束，因为实际值很可能会超出250到350元这一范围，那么在保持最优解不变的条件下，2c 到底可以在什么样的范围内取值呢？当然可以在电子表格中采取试验的方法，不断增加或减少的2c 值，直到最优解发生改变，以找到最优解发生变化时对应的2c 值.但是，这样计算太麻烦了，是否有简便一些的方法呢？答案是肯定的.（二）利用敏感性报告进行目标系数的灵敏度分析如图13-7所示，在求得最优解之后，规划求解工具会给出相应的信息，同时，在其右边列出了它可以提供的三个报告.选择第二项敏感性报告的选项，就可以得到灵敏度的分析报告，它显示在模型的工作表之前.图13-14显示了本例敏感性报告中的一部分.终值一栏表明了问题的最优解，第二栏给出了递减成本，递减成本提供了为使决策变量取正值，相应的目标系数需要减少的数量.对于本例，由于两决策变量的取值均为正数，故递减成本均为零.第三栏表示了目标函数的现值，最后两栏表示为使最优解保持不变，目标系数允许增加与减少的最大值.例如，考虑决策变量X 1的目标系数1c ，从图13-14中表示产品Ⅰ的一行中可知，1c 可以减少50，可以增加1E+30.在电子表格中1E+30是1030的缩写，Excel 使用这一极大的数值来表示无穷大.因此，从灵敏度的分析报告中可知：1c 的现值： 2001c 的允许增加值： 无穷大 此时1c 无上限1c 的允许减少值： 50 此时150502001=-≥c1c 的变化范围： 1501≥c因此，只要在上面的变化范围内变动，并且不改变模型的其他任何内容，最优解将始终保持在)2,4(),(21=x x 不变.该药厂的另一药品的单位利润的变化范围也可以用同样的方法得出，2c 是药品Ⅱ的单位利润，表中表示药品Ⅱ的第二行给出了下面关于2c 的信息：2c 的现值： 3002c 的允许增加值： 100 此时4001003002=+≤c 2c 的允许减少值： 300 此时03003002=-≥c 2c 的变化范围： 40002≤≤c 目标函数的两个系数的允许变化范围都很大，因此，尽管药品Ⅰ和药品Ⅱ的单位利润可能仅仅是实际值的一个粗略估计，我们也可以相信，这个估计值对最优解的正确性不会有影响.但在一些线性规划模型中，目标系数微小的变动都可能会影响最优解.这样的系数称为敏感参数.灵敏度的分析报告中会直接显示目标中哪些系数是敏感的，这些系数允许的变化区域很小，因此，必须格外小心，尽量取得这些数据的精确值.在求得模型的最优解之后，目标系数的允许变化范围还有一个很重要的用途.在问题的线性规划分析结束之后，如果外界的环境发生了一定的变化，灵敏度分析可以在无需重新求解的情况下，表明模型参数的变化是否造成了最优解的改变.例如经过一段时间以后，如果药品的单位利润发生了较大的变化，通过其允许变化范围，可以一眼看出原来的最优组合是否依然适用.有了目标系数的允许变化范围，在判断问题时，就不需要重新建模与求解，这一点对线性规划问题的解决是有很大帮助的，特别是在处理一个大型模型时.（三）目标系数的同时变动因为存在许多不确定性因素，目标函数系数的值，如单位利润，通常都只是对实际值的估计.上面所讨论的是只有一个系数变动时的情况，这类问题在求解一个系数的允许变化范围时，假设其他所有系数都是正确的，研究的系数是唯一可能与实际值不符的变动的系数.但事实上，所有的系数（至少一个以上）可能同时都是不准确的，如果这样的话，是否可能会导致求得的最优解不正确呢？这是最关键的问题.如果可能对最后的结果产生影响，就必须对这些系数作进一步的分析.另一方面，如果灵敏度分析表明目前的参数估计不会影响最优解的正确性，那么，管理者可以增加对该模型及其所提供的解决方法的信心.以下将介绍如何在不重新求解模型的条件下，确定如果目标函数的几个系数同时变化，可能造成的对最优解的影响．我们仍利用例2-1提出如下问题：问题2：如果该药厂两种药品的单位利润的估计都是不准确的，将会对结果产生怎样的影响？例如，原来药品Ⅰ和药品Ⅱ的单位利润分别为200元和300元，现在由于原料成本的变化，每公斤药品Ⅰ和药品Ⅱ的单位利润分别变为180元和355元，最优解是否发生变化？在分析多个系数同时变动的情况时，仍然要使用敏感性报告中提供的每个系数的允许增加值和减少值数据，下面介绍多个系数同时变动的百分之百法则.首先定义j c 的允许增加（减少）百分比为j c 的增加量（减少量）除以j c 的允许增加量（允许减少量）的值.这样我们可以计算出1c 的允许减少百分比为%4050/)180200(=-，2c 的允许增加百分比为%55100/)300355(=-，2c 的允许减少百分比与2c 的允许增加百分比之和为%95%55%40=+.目标函数系数同时变动的百分之百法则：如果目标函数的系数同时变动，当其所有允许增加百分比和允许减少百分比之和不超过百分之百时，最优解不会改变，如果超过百分之百，则不能确定最优解是否改变.因为本例中1c 的允许减少百分比与2c 的允许增加百分比之和为95%不超过100%，所以当每公斤药品Ⅰ的利润减少为180元，每公斤药品Ⅱ的利润增加为355元时，此线性规划最优解仍然为药品Ⅰ生产4公斤和药品Ⅱ生产2公斤（即2,421==x x ），此时有最大利润为143071072023554180=+=⨯+⨯（元），如图13-15所示.这一法则并没有表示出，在变动百分比之和超过百分之百的情况下，可能的结果.这一结果还有赖于系数变动的方向.但是，只要变动百分比之和不超过百分之百，最优解是肯定不会改变的.记住，我们可以让单一的目标函数系数在整个允许范围内变动，但这只有在其他目标函数系数都不变的情况下才有效.如果多个系数同时变动，我们必须研究各个系数的变动百分比.二、约束右端值的灵敏度分析之所以要分析函数约束右端值变动的原因与前面一样，因为在建模时，还不能得到模型的这些参数的精确值，只能对其作粗略的估计.因此，我们希望知道在这些估计不准确的情况下会产生怎样的后果.除此之外一个更主要的理由是因为，这些常数（通常代表资源的可用量）往往不是由外界决定的而是管理层的政策决策.因此管理者希望知道如果改变这些决策是否会提高最终的收益.影子价格分析就是为管理者提供这方面的信息.下面是关于例2-1的第三个问题：问题3：如果改变该厂某设备可用于生产的时间，结果将如何？（一）约束右端值的影子价格分析回忆第二章中关于影子价格的经济含义，我们知道影子价格代表单位资源在最优利用的条件下所产生的经济效果.即在模型获得最优解的情况下，约束条件右端值在一定范围内每增加（减少）一个单位，使目标函数值增加（减少）的量.其中，一定范围是指保持影子价格不变的右端值变化范围.在影子价格分析中，每次分析一个函数约束，可以将该函数约束右端值的常数增加一个单位后重新求解，观察目标函数值增加的量来确定影子价格，也可以利用灵敏度报告中提供的关于每一个函数约束的影子价格数据.从一个约束的影子价格中就可以直接看出，决策改变而引起的约束常数的改变所造成的影响.只要约束常数的变动不大，那么目标函数值的变动就等于约束常数的变动（正或负）乘以影子价格.为了说明影子价格的含义，我们以第二。

运筹学实验报告册（适用于经济管理类专业）学号：姓名：专业：信息管理与信息系统实验一线性规划的Excel求解与软件求解一、实验目的熟悉Excel软件、管理运筹学软件，掌握线性规划的Excel求解和管理运筹学软件求解。

二、实验要求能识别线性规划有关问题并建立相应的线性规划模型，能写出线性规划的标准形式，理解线性规划解的概念，理解单纯形法原理。

三、实验原理及内容依据单纯形法求解原理及步骤，在Excel界面中输入数据，进行求解。

熟悉线性规划模型的建立过程，掌握数据整理与Excel规划求解的操作步骤。

线性规划模型的建立，数据的输入与求解是最基础的要求。

本节实验要求完成以下内容：1、线性规划模型的建立；2、Excel界面内数据的输入；3、利用Excel规划求解进行线性规划模型的求解。

四、实验步骤及结论分析1、某饲养场养动物出售，设每头动物每天至少需700g蛋白质、30g矿物质、100mg维生素。

现有五种饲料可供选用，各种饲料每kg营养成分含量及单价如表示。

饲料蛋白质（g）矿物质（g）维生素（mg）价格（元/kg）1 3 1 0.5 0.22 2 0.5 1.0 0.73 1 0.2 0.2 0.44 6 2 2 0.35 18 0.5 0.8 0.8（1）建立这个问题的线性规划模型Min f=0.2X1+0.7X2+0.4X3+0.3X4+0.8X5约束条件：3X1+2X2+X3+6X4+18X5>=700X1+0.5X2+0.2X3+2X4+0.5X5>=300.5X1+X2+0.2X3+2X4+0.5X5>=100X1,X2,X3,X4,X5>=0（2）对建立的模型进行Excel求解2、福安商场是个中型的百货商场，它对销售人员的需求经过统计分析如下所示：时间所需售货员人数星期日28人星期一15人星期二24人星期三25人星期四19人星期五31人星期六28人的两天是连续的，问应该如何安排售货人员的作息，既满足了工作需要，又是配备的售货人员的人数最少？（用管理运筹学软件求解）实验二 运输问题一、 实验目的熟悉Excel 软件，学会运输问题的Excel 求解与管理运筹学软件求解。

实验三、用Excel求解线性规划模型线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel“规划求解”工具，就能轻而易举地求得结果。

Excel最多可解200个变量、600个约束条件的问题。

下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。

一、实验目的1、掌握如何建立线性规划模型。

2、掌握用Excel求解线性规划模型的方法。

3、掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变化对最优方案产生的影响。

4、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。

二、实验内容1、[工具][规划求解]命令规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。

在安装完成进入Excel后还要用[工具][加载宏]命令选中“规划求解”，以后在[工具]菜单下就增加了一条[规划求解]命令。

使用[规划求解]命令的一般步骤为：第一步：在选取[工具][规划求解]命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。

图1“规划求解参数”对话框选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单元格等于按问题目标进行选择。

如利润问题，选取“最大值”可变单元格决策变量所在各单元格、不含公式，可以有多个区域或单元格约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件选项决定采用线性模型还是非线性模型求解约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。

图2图3第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。

图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。

图 13-1第十三章 运筹学问题的Excel 建模及求解 学习运筹学的目的在于学会用运筹学的方法解决实践中的管理问题，注重学以致用.很多实际问题利用人工计算要经过长时间的艰苦工作才能完成甚至根本无法求解，但若使用运筹学软件则瞬间就能解决.因此在学习过程中不仅要掌握运筹学的基本理论和计算方法，还要充分利用现代化的手段和技术.微软的电子表格软件（Microsoft Excel ）为展示和分析许多运筹学问题提供了一个功能强大而直观的工具，它现在已经被应用于管理实践中.本章将重点介绍如何建立和求解规划问题的电子表格模型，对于解决大量的中、小规模的实际规划问题，电子表格软件是远远优于传统的代数算法的.第一节 Excel 中的规划求解工具本节中，我们将举例说明如何使用微软Excel 以电子表格的形式建立线性规划模型，并利用Excel 中的规划求解工具对模型求解.一、在Excel 中加载规划求解工具要使用Excel 应首先安装MicrosoftOffice ，然后从屏幕左下角的[开始]—[程序]中找到Microsoft Excel 并启动.在Excel 的主菜单中点击[工具]—[加载宏]，选择“规划求解”，如图13-1所示.点击[确定]后，在工具菜单中将增加[规划求解]选项. 二、在Excel 中建立线性规划模型我们以例2-1为例说明如何在电子表格中建立该问题的线性规划模型.建立电子表格模型时既可以直接利用问题中所给的数据和信息，也可以利用已建立的代数模型.本例的代数模型为：图 13-2 图 13-3目标函数 21300200x x Z +=max⎪⎪⎪⎩⎪⎪⎪⎨⎧≥≤≤≤+≤+0,124164821222..21212121x x x x x x x x t s图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种设备台时数的总需求。

《实用运筹学》上机实验指导课程名称：运筹学/Operations Research实验总学时数：60学时一、实验教学目的和要求本实验与运筹学理论教学同步进行。

目的：充分发挥Excel软件这一先进的计算机工具的强大功能，改变传统的教学手段和教学方法，将软件的应用引入到课堂教学，理论与应用相结合。

丰富教学内容，提高学习兴趣。

要求：能用Excel软件中的规划求解功能求解运筹学中常见的数学模型。

二、实验项目名称和学时分配三、单项实验的内容和要求实验一线性规划（－）实验目的：安装Excel软件“规划求解”加载宏，用Excel软件求解线性规划问题。

（二）内容和要求：安装并启动软件，建立新问题，输入模型，求解模型，结果的简单分析。

（三）实例操作：求解习题1.1。

（1）建立电子表格模型：输入数据、给单元格命名、输入公式等；（2）使用Excel软件中的规划求解功能求解模型；（3）结果分析：如五种家具各生产多少？总利润是多少？哪些工序的时间有剩余，并对结果提出你的看法；（4）在Excel或Word文档中写实验报告，包括线性规划模型、电子表格模型和结果分析等。

案例1 生产计划优化研究某柴油机厂年度产品生产计划的优化研究。

某柴油机厂是我国生产中小功率柴油机的重点骨干企业之一。

主要产品有2105柴油机、x2105柴油机、x4105柴油机、x4110柴油机、x6105柴油机、x6110柴油机，产品市场占有率大，覆盖面广。

柴油机生产过程主要分成三大类：热处理、机加工、总装。

与产品生产有关的主要因素有单位产品的产值、生产能力、原材料供应量与生产需求情况等。

每种产品的单位产值如错误!未找到引用源。

所示。

表 C－1 各种产品的单位产值为简化问题，根据一定时期的产量与所需工时，测算了每件产品所需的热处理、机加工、总装工时，如表 C－2所示。

表 C－2 单位产品所需工时同时，全厂所能提供的总工时如表 C－3所示。

表 C－3 各工序所能提供的总工时产品原材料主要是生铁、焦碳、废钢、钢材四大类资源。

第三节使用Excel求解线性规划问题利用单纯形法手工计算线性规划问题是很麻烦的。

office软件是一目前常用的软件，我们可以利用office软件中的Excel工作表来求解本书中的所有线性规划问题。

对于大型线性规划问题，需要应用专业软件，如Matlab，Lindo，lingo等，这些软件的使用这里我们不作介绍，有需要的，自己阅读有关文献资料。

用Excel工作表求解线性规划问题，我们需要先设计一个工作表，将线性规划问题中的有关数据填入该工作表中。

所需的工作表可按下列步骤操作：步骤1 确定目标函数系数存放单元格，并在这些单元格中输入目标函数系数。

步骤2 确定决策变量存放单元格，并任意输入一组数据。

步骤3 确定约束条件中左端项系数存放单元格，并输入约束条件左端项系数。

步骤4 在约束条件左端项系数存放单元格右边的单元格中输入约束条件左端项的计算公式，计算出约束条件左端项对应于目前决策变量的函数值。

步骤5 在步骤4的数据右边输入约束条件中右端项（即常数项）。

步骤6 确定目标函数值存放单元格，并在该单元格中输入目标函数值的计算公式。

例建立如下线性规划问题的Excell工作表：1212121212max1502102310034120..55150,0z x xx xx xs tx xx x=++≤⎧⎪+≤⎪⎨+≤⎪⎪≥⎩解：下表是按照上述步骤建立的线性规划问题的Excell工作表。

其中：D4=B2*B4+C2*C4, D5=B2*B5+C2*C5 , D6=B2*B6+C2*C6, C7= B2*B1+C2*C1 。

建立了Excel工作表后，就可以利用其中的规划求解功能求相应的线性规划问题的解。

求解步骤如下：步骤1单击[工具]菜单中的[规划求解]命令。

步骤2 弹出[规划求解参数]对话框，在其中输入参数。

置目标单元格文本框中输入目标单元格；[等于]框架中选中[最大值＼最小值］单选按钮。

步骤3 设置可变单元格区域，按Ctrl键，用鼠标进行选取，或在每选一个连续区域后，在其后输入逗号“，”。

运筹学利用Excel课程设计一、课程目标知识目标：1. 让学生掌握运筹学基本概念，理解线性规划、整数规划等基本模型；2. 培养学生运用Excel进行数据处理和分析的能力，掌握运用Excel求解线性规划问题的方法；3. 使学生了解运筹学在实际生活中的应用，如资源配置、生产计划等。

技能目标：1. 培养学生运用Excel进行运筹学模型建立、求解和结果分析的能力；2. 培养学生运用运筹学知识解决实际问题的能力，提高解决问题的效率和准确性；3. 提高学生的团队协作能力和沟通能力。

情感态度价值观目标：1. 培养学生对运筹学学科的兴趣，激发学生主动学习的热情；2. 培养学生严谨、认真的学习态度，养成科学研究和解决问题的良好习惯；3. 培养学生关注社会现象，运用所学知识为社会发展和进步贡献力量的意识。

课程性质：本课程属于应用性较强的学科，旨在培养学生运用运筹学知识解决实际问题的能力。

学生特点：学生具备一定的数学基础和计算机操作能力，对实际问题具有较强的探究欲望。

教学要求：结合学生特点，注重理论与实践相结合，提高学生的实际操作能力和解决问题的能力。

在教学过程中，注重引导学生主动参与，培养学生的团队协作能力和沟通能力。

通过课程学习，使学生能够将所学知识应用于实际生活和工作中。

二、教学内容1. 运筹学基本概念：讲解线性规划、整数规划等基本模型，以及相关定义和性质。

教材章节：第一章 运筹学基本概念内容安排：2课时2. Excel基础操作：介绍Excel的基本功能，包括数据录入、公式运用、图表制作等。

教材章节：第二章 Excel基础操作内容安排：2课时3. 线性规划模型建立与求解：讲解如何利用Excel建立线性规划模型，并进行求解。

教材章节：第三章 线性规划内容安排：4课时4. 整数规划模型建立与求解：介绍整数规划的特点，以及如何利用Excel求解整数规划问题。

教材章节：第四章 整数规划内容安排：4课时5. 运筹学在实际生活中的应用：分析资源配置、生产计划等实际问题，并运用Excel进行求解。

实用管理运筹学基于Excel课程设计一、背景介绍运筹学是一门涵盖了数学、统计学和计算机科学的学科，它在管理领域有着广泛的应用。

随着信息技术的发展，Excel已经成为管理运筹学工具中最为常用的软件之一。

对于运筹学专业的学生来说，掌握实用管理运筹学基于Excel的应用技能至关重要。

为此，本课程根据《实用管理运筹学基于Excel》（作者：潘智焱）一书，采用应用教学方法和项目驱动模式，通过实例让学生了解Excel 的基础操作，掌握常见的管理运筹学模型及其求解方法，并通过实验巩固知识和提高实际操作能力。

二、课程设计内容1. Excel基础操作•Excel 2007-2019界面介绍•字体、边框、颜色、对齐方式等基本格式设定•公式、函数的基础介绍2. 线性规划及其应用•线性规划基础（线性规划模型、决策变量、目标函数和约束条件）•使用Excel求解线性规划（单目标优化、成本最小化、收益最大化）•食品生产线优化（线性规划在工业中的应用）3. 整数规划及其应用•整数规划基础（整数规划模型、目标函数和约束条件）•使用Excel求解整数规划（工程切割问题、货车调度问题）•整数规划在物流中的应用（配送路径规划）4. 目标规划及其应用•目标规划基础（目标规划模型、目标函数和约束条件）•使用Excel求解目标规划（单目标优化、多目标规划）•目标规划在选址问题中的应用5. 供应链优化及其应用•供应链优化基础（供应链优化模型、目标函数和约束条件）•使用Excel求解供应链优化（选址问题、库存规划等）•供应链优化在企业运营中的应用（供应链管理、在线销售平台）三、实验操作设计本课程每个章节均附有实验操作环节，帮助学生将理论知识落实于实际操作中。

实验操作建议为每个模块设置一个综合性案例，让学生通过自行分析问题和填写Excel表格，得到正确的答案，巩固所学知识。

四、考核方式为了考察学生对知识的掌握和应用能力，本课程考核设置如下：•期中考试（50%）•实验作业（20%）•期末大作业（30%）期中考试采用笔试形式，主要考察学生对课程内容的理解和应用能力。

附录4 Excel“规划求解”1. 在系统中安装“规划求解”1、启动EXCEL。

打开“工具”菜单。

如果没有“规划求解”，单击“加载宏”。

弹出以下窗口：2、在复选框中选中“规划求解”，单击“确定”后返回Excel。

这时在“工具”菜单中出现“规划求解”。

关闭“工具”菜单2. 在Excel中创建线性规划模型1、输入线性规划模型的约束条件系数、右边常数和目标函数系数。

定义线性规划的变量单元格、约束条件左边单元格和目标函数单元格。

2、定义“设备能力占用”（即约束条件左边）以及“总利润”的计算公式。

首先定义设备A的“能力占用”单元格（G3）的计算公式，界面如下：其次定义设备B的“能力占用”单元格（G4）的计算公式，界面如下：再次定义设备C的“能力占用”单元格（G5）的计算公式，界面如下：最后定义“总利润”单元格（C8）的计算公式，界面如下：3、将光标停留在“总利润”值的单元格（C8）中，打开“工具/规划求解”，弹出以下窗口：4、设置目标函数单元格：检查“设置目标函数单元格”是否在“$C$8”，如不是，单击文本框右侧的图标，重新选定目标函数单元格，也可以直接单击Excel表中的“C8”。

5、设置变量：单击“规划求解窗口”中“可变单元格”文本框，然后在Excel工作表中选定变量单元格（C7、D7、E7和F7），在文本框中出现“$C$7:$F$7”，如下图所示。

6、设置约束单击“添加”，弹出以下窗口：单击“单元格引用位置”文本框空白处，然后单击工作表G3单元格，“单元格引用位置”文本框中出现“$G$3”；打开“单元格引用位置”和“约束值”之间的下拉文本框，选定“<=”；单击“约束值”文本框空白处，然后单击工作表H3单元格。

结果如下图所示。

单击“添加”，完成第一个约束设置。

继续设置第二、第三个约束，最后设置所有变量非负。

约束设置完成以后，单击“确定”，返回“规划求解参数”窗口，如下图所示。

7、设置叠代参数。

单击“选项”，弹出以下窗口：输入“最长运行时间”、“叠代次数”、“精度”、“允许误差”、“收敛度”等叠代参数。

实用运筹学-运用Excel2010建模和求解第二版课程设计背景
运筹学是一门重要的管理科学，它通过建立数学模型，以科学的方法来求解实际问题。

随着信息技术的飞速发展，现代运筹学越来越多地运用计算机进行建模和求解。

其中，Excel作为一种通用的电子表格软件，成为了运筹学建模和求解的重要工具之一。

本课程的设计旨在通过Excel软件的应用来加深学生对运筹学的理解和掌握能力，同时也旨在培养学生运用现代信息技术解决实际问题的能力。

教学内容
课程目标
本课程的目标是让学生掌握Excel软件的基本操作和运用Excel进行运筹学建模和求解的能力。

通过本课程的学习，学生应该能够：
•熟练使用Excel软件进行数据处理和基本操作
•理解线性规划、整数规划、网络优化、动态规划等运筹学模型的建立和求解方法
•运用Excel软件建立和求解运筹学模型
•能够应用所学知识解决实际问题
教学内容
本课程主要分为以下几个部分：
Excel基础
•Excel界面和操作：界面布局、窗口设定、数据输入和编辑、基本公式和函数等
1。

实用管理运筹学基于Excel教学设计前言管理运筹学是管理学的重要分支，其理论和方法能够为管理决策提供科学的参考和支撑。

本文将介绍如何在Excel中应用管理运筹学，通过有针对性的教学设计，使学生能够全面地掌握管理运筹学相关知识与技能。

Excel基础知识的教学在进行管理运筹学的Excel应用教学之前，要进行Excel基础知识的教学。

这包括Excel软件的安装、打开、关闭等基本操作，同时也需要学生了解Excel中常用的快捷键和基本的单元格操作技能，如插入和删除单元格、行、列等操作。

线性规划的教学线性规划是管理运筹学中的重要理论和方法，其通过数学模型来解决实际问题。

在Excel中实现线性规划需要用到“规划求解器”这个内置工具。

本部分的教学可分为以下几个步骤：1. 建立模型在Excel中通过数据表建立线性规划模型，定义目标函数和约束条件，将其实现为Excel中的公式和单元格之间的关系。

2. 设定规划求解器通过“数据”菜单栏中的“求解器”选项来设置规划求解器，将模型转化为线性规划问题，并设置最优解的查找方法，如规定最小值或最大值，以及优化约束条件等。

3. 进行求解和分析点击“求解”按钮进行求解，在弹出的窗口中查看求解结果和分析报告，不断进行反复试验并进行调整，以得到更优的解。

项目管理的教学项目管理是管理运筹学中最具实践性的技能之一，其目的是帮助企业或组织更好地组织和管理项目，提高项目管理效率和成效。

在Excel中实现项目管理需要用到多个工具和模板，教学的流程如下：1. 了解项目管理基本概念首先要让学生了解项目管理的基本概念和流程，如项目需求分析、项目计划与排期、工期控制、预算分析等基本知识。

2. 掌握项目管理工具和模板教学中要让学生熟悉各种项目管理工具和模板的使用，如甘特图、PERT图、项目进度表等，要求学生在Excel中掌握和运用这些工具和模板。

3. 实战演练通过实战演练，让学生将项目管理的知识和技能应用到实际的项目中，完成实际项目管理过程中的需求分析、制定计划和报告、监督进度和预算控制等任务。

excel管理运筹学课程设计一、课程目标知识目标：1. 掌握Excel的基本操作，包括数据录入、编辑、格式设置等。

2. 学习运用Excel进行数据整理、分析、图表制作等运筹学基本技能。

3. 了解运筹学的基本概念和原理，结合Excel实现线性规划、整数规划等问题的求解。

4. 掌握利用Excel求解最优化问题的方法，并能将其应用于实际案例。

技能目标：1. 培养学生运用Excel进行数据处理和分析的能力，提高工作效率。

2. 培养学生运用运筹学方法解决实际问题的能力，提高解决问题的策略思维。

3. 培养学生独立思考和团队协作的能力，通过实际操作和案例分析，提高动手实践和创新能力。

情感态度价值观目标：1. 培养学生对运筹学和管理科学的学习兴趣，激发学生主动探索的热情。

2. 培养学生严谨、务实的科学态度，养成良好的数据分析习惯。

3. 培养学生具备团队协作精神，学会尊重他人意见，提高沟通表达能力。

4. 引导学生认识到Excel在运筹学和管理领域的重要应用价值，增强实际操作能力。

本课程针对高年级学生，结合学科特点和教学要求，旨在通过实际操作和案例分析，使学生掌握Excel在运筹学中的应用，提高学生分析问题和解决问题的能力。

课程目标具体、可衡量，以便学生和教师在教学过程中能够清晰地了解预期成果，并为后续的教学设计和评估提供依据。

二、教学内容1. Excel基本操作：包括数据录入、编辑、格式设置等，涉及教材第一章内容。

- 数据录入与导入- 数据编辑与格式设置- 公式与函数的应用2. 数据整理与分析：学习利用Excel进行数据整理、分析、图表制作等，涉及教材第二章内容。

- 数据排序与筛选- 数据透视表与透视图- 常用图表类型及应用3. 运筹学基本概念与原理：介绍运筹学的基本概念、线性规划、整数规划等，涉及教材第三章内容。

- 运筹学基本概念- 线性规划模型及其求解- 整数规划模型及其求解4. Excel求解最优化问题：结合教材第四章内容，学习利用Excel求解最优化问题。