Excel求解运筹学问题

1、用EXCEL求解运筹学问题,主 要 内 容,1. 用Excel Solver 求解线性规划 2. 用Excel Solver Table 进行敏感性分析 3. 用Excel Solver 求解运输问题和指派问题 4. 用Excel Solver求解网络问题 5. 用Excel Solver 做线性回归分析 6.用Excel Solver 进行决策分析,1. 应用Excel 求解线性规划问题,(1)Excel Solver 的安装 Excel工具菜单中选择加载宏,加载宏以后, 在工具菜单中出现规划求解,某企业的产品生产数据如下表,理论模型,(2) 求解如下的线性规划问题,第一步：选择决策变量单元

2、格 决策变量的初始值一般赋0，并用较醒目的颜色表示。,第二步：目标单元格，用函数公式表示 并用较醒目的颜色表示。,第三步：约束条件左边项用函数表示,第四步: 激活规划求解, 确定可变单元格和目标单元格,第五步: 增加约束条件,第六步: 完成求解对话框,第七步:求解方式的选择,第八步: 从求解结果对话框选择所要的报告,求解结果报告灵敏性报告极限报告,求解结果报告,灵敏性报告,极限值报告,2. 用Excel Solver Table 进行敏感性分析,Solver Table宏的安装使用,Be sure that the Solver is installed. If it is, it shoul

3、d appear under the Tools menu. Quit Excel if it is currently running. Save the Solver Table.xla file to the exact same location as the Solver.xla file (C:program filesMicrosoft Office OfficeLibrarySolverSolver.xla) (If it is not, use the Find command to find the Solver.xla file). Launch Excel. Under

4、 the Tools menu, choose the Add-Ins command. Click the Solver Table checkbox to have Solver Table load with Excel every time it is loaded.,应用Solver Table 做敏感性分析,门的单位利润从$100变到$1000，产品组合的变化,(1) 只有一个目标函数系数变动的影响,(2) 有两个目标函数系数同时变动的影响,16,门和窗的利润同时变化时,最优解的变化,3. 用Excel Solver 求解运输问题和指派问题,例 Better Products公司决

5、定利用三个有剩余生产能力的工厂生产四种新产品，,问题： 哪个工厂生产哪种产品？,如果一类产品可以在不同的工厂生产,可以将此问题看成运输问题,G11=sum(C11:F11) C14=sum(C11:C13 G12=sum(C12:F12) G13=sum(C13:F13),I16=sumproduct($C$4:$F$6, $C$11:$F$13),如果一类产品不能在不同的工厂生产,可以将此问题看成指派问题,并且此时，第一、第二个工厂有生产两种产品的能力。,例题: 固特产品公司研究发展部开发了三种新产品，公司有两个工厂可以生产这些新产品，每种产品都可以在两个工厂中的任意一个进行生产，为了防止公

6、司生产线的过度多样化，同时也为了管理上的方便，公司管理层增加了以下限制： (1) 从三种产品种最多选择其中两种进行； (2) 两个工厂中必须选出一个专门生产两种新产品。,三种产品至多生产二种,该问题的混合整数规划模型,该问题的EXCEL电子表格模型,4. 用Excel Solver求解网络问题,例1： 求解如下的一个网络最大流问题，弧旁括号中第一个数字为弧容量，第二个数字为现有流量。,最大流问题,例2： 若下图中弧旁括号中第一个数字为弧容量，第二个数字为单位流量的费用，求该问题的一个最小费用最大流。,该问题分两步做，第一步先不考费用求最大流。,最小费用最大流问题,第二步：将最大流固定，求最小费

7、用。 目标为最小费用,例题3：求下图中的从起点O到终点T的最短路问题，弧旁的数字为两点之间的距离。,最短路线为： OA B E F T,最短路线为： OA B E F T,5. 用Excel Solver 做线性回归分析,线性回归用来反映因变量与自变量之间的线性关系。应用线性回归，首先需要判断变量之间是否具有线性关系，这可以通过画散点图得到。画散点图的方法是：,1）选择画散点图的数据区域； 2）在插入菜单中选择插入“图表” 3）在图表对话框中选择散点图类型。,进一步，可以通过选择图表，在图表菜单条下选择添加趋势线，在添加趋势线对话框中，选择选项，显示公式和R2值,多元线性回归：y=a+b1x1

8、+b2x2,例题：若某个公司的企业销售及有关变量数据如下表所示：,应用Excel中的数据分析工具，得到的多元回归结果如下：,回归方程如下：,预测值和残差,6.用Excel Solver 进行决策分析,例：求解如下简单的决策树,TreePlan的安装: (1)首先从学习光盘中找到TreePlan程序模块; (2)将 TreePlan程序模块拷贝到Program filesMicrosoft office Officelibrary目录下; (3)在 Excel工具菜单中选择加载宏; (4)在加载宏对话框中选中 TreePlan程序; (5)回到Excel工具菜单中查看,是否有Decision T

9、ree菜单条,如果有,则安装成功.,选择工具菜单条中的Decision Tree 点击创建新树,这样就创建了一个如下图所示的决策树,选中每一分枝末端，再一次点击决策树菜单，可以添加决策结点或状态结点。,用决策树做敏感性分析：通过建立电子表格和决策树之间数据的联系，可方便地对决策问题进行敏感性分析。,用数据表系统地进行敏感性分析,(1)首先在电子表格中创建一张表,列出数据单元格的各个尝试值. (2)数据表的第一行,输入公式使之与相关的输出单元格相关联. (3)选择整个数据表,然后选择数据菜单中的模拟运算表菜单条.,a,b,c,f,d,g,e,h,做地震勘探,不做地震勘探,-30,0,不好的结果,

10、好的结果,钻探,出售,有石油,干涸,钻探,出售,有石油,干涸,钻探,出售,有石油,干涸,-100,90,-100,90,0.143,0.857,0.5,0.5,-100,90,0.25,0.75,670,-130,60,670,-130,60,700,-100,90,-15.7,60,123,123,270,270,100,100,0.7,0.3,复杂决策树的求解与分析,复杂决策树的敏感性分析,使用SensIt创建三种类型的敏感性分析图,SensIt的安装:与TreePlan 软件的安装类似 (1)首先从学习光盘中找到SensIt程序模块; (2)将 程序模块SensIt拷贝到Program

11、filesMicrosoft office Officelibrary目录下; (3)在 Excel工具菜单中选择加载宏; (4)在加载宏对话框中选中 SensIt程序; (5)回到Excel工具菜单中查看,是否有SensIt菜单条,如果有,则安装成功.,SensIt给出了三种不同的敏感性分析图选择:单因素图(Plot)、蛛网图(Spider)、旋风图(Tornado),使用SensIt画单因素图,单因素图用来生成一种图形，显示了输出单元格对应于单一数据单元格的不同数值变化。,这个图表明，当先验概率略大于0.15时期望收益开始上升.当先验概率在0.3附近时期望收益开始以更快的速度上升.这就暗示

12、了最优策略会随着先验概率的变化而变化.,最佳策略: 设p=有石油的先验概率 (1)如果p0.168,则出售土地(不进行地震勘探); (2)如果0.169 p 0.308,则进行地震勘探,结果好则钻探,结果不好则出售土地. (3)如果0.309,则钻探石油(不进行地震勘探),用SensIt分析的蛛网图 Spider,假设我们要考察如果单元格V4:V7中的一项成本或收益以加减10%的方式改变时期望收益会发生怎样的变化,蛛网图可用来进行这一类分析.以下蛛网图的对话框和对应的电子表格.,SensIt_敏感性分析_蛛网图,用 SensIt画旋风图,SensIt蛛网图的一个缺陷是它假设每一个数据都以同样的百分比发生变化.例如,我们考虑这样一种情况,成本或收益数据中任一条的变化都可能是上升或下降10%.而其中某些数据可能会比其余的数据更不确定(或者更可