物流EXCEL建模例9.10(思考一,考虑不周全模型1)(模板)

Excel规划求解在运输规划中的应用专题规划求解是Excel的一个非常有用的工具，不仅可以解决运筹学、线性规划等问题，还可以用来求解线性方程。

【例】某公司拥有两个处于不同地理位置的生产工厂和五个位于不同地理位置的客户，现在需要将产品从两个工厂运往五个客户。

已知两个工厂的最大产量均为60000，五个客户的需求总量分别为30000、23000、15000、32000、16000，从各工厂到各客户的单位产品运输成本如表1所示，请用EXCEL的规划求解功能计算出使总成本最小的运输方案。

表1 单位产品运输成本客户1 客户2 客户3 客户4 客户5工厂1 1.75 2.25 1.50 2.00 1.50工厂2 2.00 2.50 2.50 1.50 1.00 问题分析：一个公司需要将产品从两个不同的工厂运往五个不同的客户，每个工厂只有有限的供给，而且每个客户有确定需求量，那么如何运输才能使运输总成本最小？解答过程：（一）、根据已知条件建立EXCEL表格模型，如图1所示：图1说明：1.B9:F10单元格区域是变量区域（可变单元格），变量区域的初始值全部设置为2.B11单元格为客户1收到的来自两个工厂的产品数合计，公式为“=SUM(B9:B10)”3.G9单元格为工厂1出货的合计，公式为“=SUM(B9:F9)”4.C13单元格为运输总成本，公式为“=SUMPRODUCT(B3:F4,B9:F10)”（二）、加载宏工具→加载宏→规划求解→确定注意：如果Office软件没有安装完整，是不能加载和使用规划求解宏的，这种情况下建议卸载现有版本，重新完全安装Office。

（三）、设定规划求解参数和求解1.工具→规划求解2.设置求解参数，如图2所示：图2【目标单元格】：即放置运输总成本运算结果的单元格【等于】：这里求最小值【可变单元格】：可变单元格是电子表格中我们可以进行更改或调整以优化目标单元格的单元格，即变量区域，本例中各工厂到各客户的运量为变量。

运筹学excel运输问题实验报告(一)运筹学Excel运输问题实验报告实验目的通过运用Excel软件解决运输问题，加深对运输问题的理解和应用。

实验内容本实验以四个工厂向四个销售点的运输为例，运用Excel软件求解运输问题，主要步骤如下：1.构建运输问题表格，包括工厂、销售点、单位运输成本、每个工厂的供应量、每个销售点的需求量等内容。

2.使用Excel软件的线性规划求解工具求解该运输问题，确定每条路径上的运输量和总运输成本。

3.对结果进行分析和解释，得出优化方案。

实验步骤1.构建运输问题表格工厂/销售点 A B C D 供应量1 4元/吨8元/吨10元/吨11元/吨35吨2 3元/吨7元/吨9元/吨10元/吨50吨3 5元/吨6元/吨11元/吨8元/吨25吨4 8元/吨7元/吨6元/吨9元/吨30吨需求量45吨35吨25吨40吨2.使用Excel软件的线性规划求解工具求解该运输问题在Excel软件中选择solver，按照下列步骤完成求解：1.添加目标函数：Total Cost=4AB+8AC+10AD+11AE+3BA+7BC+9BD+10BE+5CA+6CB+11CD+8CE+8DA+7DB+6DC+9DE2.添加约束条件：•A供应量: A1+A2+A3+A4=35•B供应量: B1+B2+B3+B4=50•C供应量: C1+C2+C3+C4=25•D供应量: D1+D2+D3+D4=30•A销售量: A1+B1+C1+D1=45•B销售量: A2+B2+C2+D2=35•C销售量: A3+B3+C3+D3=25•D销售量: A4+B4+C4+D4=403.求解结果工厂/销售点 A B C D 供应量1 10吨25吨0吨0吨35吨2 0吨10吨35吨5吨50吨3 0吨0吨15吨10吨25吨4 35吨0吨0吨0吨30吨需求量45吨35吨25吨40吨单位运输成本4元/吨8元/吨10元/吨11元/吨总运输成本2785元1480元875元550元4.结果分析和解释通过求解结果可知，工厂1最终向A销售10吨、向B销售25吨；工厂2最终向B销售10吨、向C销售35吨、向D销售5吨；工厂3最终向C销售15吨、向D销售10吨；工厂4最终向A销售35吨。

图 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：如果改变该厂某设备可用于生产的时间.结果将如何？（一）约束右端值的影子价格分析回忆第二章中关于影子价格的经济含义.我们知道影子价格代表单位资源在最优利用的条件下所产生的经济效果.即在模型获得最优解的情况下.约束条件右端值在一定范围内每增加（减少）一个单位.使目标函数值增加（减少）的量.其中.一定范围是指保持影子价格不变的右端值变化范围.在影子价格分析中.每次分析一个函数约束.可以将该函数约束右端值的常数增加一个单位后重新求解.观察目标函数值增加的量来确定影子价格.也可以利用灵敏度报告中提供的关于每一个函数约束的影子价格数据.从一个约束的影子价格中就可以直接看出.决策改变而引起的约束常数的改变所造成的影响.只要约束常数的变动不大.那么目标函数值的变动就等于约束常数的变动（正或负）乘以影子价格.为了说明影子价格的含义.我们以第二章。

（二）运输问题的Excel建模求解实验目的:掌握在Excel中建立运输问题模型和求解的方法实验内容:利用Excel“规划求解”求解下述运输问题。

（如果“工具”菜单没有显示“规划求解”子菜单，可到“加载宏”下加载。

在本实验室选择安装路径：D:\tool_bak\office2003\PRO11.MSI ）某食品公司从三两个加工厂A1、A2 、A3将其生产的糖果运往四个门市部B1、B2、B3、B4销售，各加工厂每天的生产量、各门市部每天的销售量和各加工厂运往各门市部每吨糖果的运价如下表所示，问：该食品公司应如何调运可使总运输费用最小？解：设xij 为从产地Ai运往销地Bj的运输量，得到下列运输量表：该问题的数学模型如下：Min Z = 3x11+ 11x12+ 3x13 + 10x14 + x21+ 9x22+ 2x23 +8 x24 +7x31+ 4x32+ 10x33 +5 x34 s.t. x11+ x12 + x13 +x14 = 7x21 + x22+ x23 +x24 = 4x31 + x32+ x33 +x34 = 9x11 + x21+ x31=3x12 + x22 +x32= 6x13 + x23 +x33= 5x14 + x24 +x34= 6x ij ≥0 ( i = 1 , ..., 3；j = 1, (4)实验步骤第一步建模依次在相应的单元格内输入数据和公式，建模如图1注：Sumproduct()函数：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

图1运输的Excel模型第二步设置规划求解参数如图2和图3，其中，“选项”中选取“假定非负”和“采用线性模型”，其它采用默认选项，如图图2 规划求解参数设置图3 选项设置第三步求解设置完毕后，单击图2中“求解”按钮，出现如图4规划求解结果对话框图4 规划求解结果对话框如图4所示，共提供3类报告，选择你想要的报告，单击确定按钮，完成运算，最后计算结果如图5图5 计算结果思考：为什么用WINQSB最小元素法求解出来的最优解同Excel计算出来的不同，而最优值相同？（因为终表中非基变量检验数有等于0 的，说明有无穷多最优解）。

运筹学问题的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：如果改变该厂某设备可用于生产的时间，结果将如何？（一）约束右端值的影子价格分析回忆第二章中关于影子价格的经济含义，我们知道影子价格代表单位资源在最优利用的条件下所产生的经济效果.即在模型获得最优解的情况下，约束条件右端值在一定范围内每增加（减少）一个单位，使目标函数值增加（减少）的量.其中，一定范围是指保持影子价格不变的右端值变化范围.在影子价格分析中，每次分析一个函数约束，可以将该函数约束右端值的常数增加一个单位后重新求解，观察目标函数值增加的量来确定影子价格，也可以利用灵敏度报告中提供的关于每一个函数约束的影子价格数据.从一个约束的影子价格中就可以直接看出，决策改变而引起的约束常数的改变所造成的影响.只要约束常数的变动不大，那么目标函数值的变动就等于约束常数的变动（正或负）乘以影子价格.为了说明影子价格的含义，我们以第二。

一、实验目的和要求(一)知识目标1.掌握excel常用键的使用以及快捷方式2.掌握常用基本函数以及物流管理函数的使用3.掌握如何制作表格以及表格的美化4.掌握excel在运输管理、配送与仓储、采购管理等方面的使用(二)能力目标1.能运用excel制作常见的物流管理相关表格2.能运用excel进行物流预测、分析和决策3.能通过excel解决物流中常用的普通数学、数学分析、概率和运筹学等计算难题及物流管理中的繁琐的统计计算、财务管理计算等（三）素质目标1、能自主学习会计信息化新知识、新技术2、具有较强的语言表达、会计职业沟通和协调能力3、具有踏实肯干的工作作风和主动、热情、耐心的服务意识4、具有良好的心理素质、诚信品格和社会责任感教学重点：（1）Excel 在运输管理中的应用；（2）Excel 在配送与仓储中的应用；（3）Excel 在采购管理中的应用。

二、实验内容1、Excel 在运输管理中的应用本章内容利用Excel作为工具解决最小成本问题、运输模式的选择问题、运输线路决策问题和网络最大流问题。

2、Excel 在配送与仓储中的应用本章内容利用Excel作为工具解决配送中心选址问题。

3、Excel 在采购管理中的应用本章内容利用Excel作为工具解决采购的利润杠杆效应模拟、采供控制模型和供应商的选择与评估问题。

三、实验报告填写要求1）学生自己找一个实例，根据实例数据建立合理的模型。

2）实验设计过程进行描述（包括实验目的、实验意义、实验步骤、技术关键点与难点等解决途径），不少于六百字；3）实验设计结果；•（1）输入已知条件•讲问题的已知条件转换为一个矩阵，在Excel中输入。

•C列表示酒店A的事；•D列表示酒店B的事；•E列表示酒店C的事；•第4行表示仓库1的事；•第5行表示仓库2的事；•于是，可以在【C4:E5】这个范围输入单位运输成本。

•-----------•F列表示库存量的事；于是【F4】表示仓库1的库存、【F5】表示仓库2的库存•-----------•第6行表示需求量的事；于是【C6】表示酒店A的需求量、【D6】表示酒店B的需求量、【E6】表示酒店C的需求量.•••（2）建立模型•***复制区域【B2：E5】，选中单元格【B9】，粘贴。

如何用Excel进行物流数据分析与优化在当今竞争激烈的物流行业中，数据分析与优化成为了企业提高效率、降低成本、提升服务质量的关键手段。

Excel 作为一款广泛使用的电子表格软件，拥有强大的数据分析和处理功能，能够帮助物流从业者有效地进行数据分析与优化。

下面将详细介绍如何利用 Excel 进行物流数据分析与优化。

一、数据收集与整理在进行物流数据分析之前，首先需要收集相关的数据。

这些数据可能包括货物的运输量、运输路线、运输时间、运输成本、库存水平等。

数据的来源可以是企业内部的物流管理系统、财务报表、运输单据，也可以是外部的市场调研数据、行业报告等。

收集到的数据往往是杂乱无章的，需要进行整理和清洗。

在 Excel 中，可以使用“数据”选项卡中的“排序”和“筛选”功能，对数据进行排序和筛选，去除重复数据和异常值。

同时，还可以使用“数据工具”中的“分列”功能，将一列中的数据按照特定的分隔符拆分成多列，以便于后续的分析。

二、数据分析方法1、描述性统计分析使用 Excel 的“数据分析”工具（如果没有该工具，需要在“选项”中加载）中的“描述统计”功能，可以快速计算出数据的均值、中位数、众数、标准差、方差等统计指标，从而对数据的集中趋势和离散程度有一个初步的了解。

2、相关性分析通过 Excel 的“数据分析”工具中的“相关系数”功能，可以分析两个或多个变量之间的相关性。

例如，可以分析运输量与运输成本之间的相关性，以便确定是否可以通过增加运输量来降低单位运输成本。

3、趋势分析使用 Excel 的折线图、柱状图等图表功能，可以直观地展示数据的趋势。

例如，可以绘制运输量随时间的变化趋势图，以便发现季节性或周期性的规律。

4、成本分析物流成本是物流企业关注的重点之一。

在 Excel 中，可以使用公式和函数来计算各项成本，如运输成本、仓储成本、装卸成本等。

然后，通过数据透视表功能，可以对成本进行分类汇总和比较分析，找出成本的主要构成部分和可优化的环节。