Excel求解运筹学问题课件

用用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工具菜单中选择加载宏加载宏以后加载宏以后,在工具菜单中出现规划求解在工具菜单中出现规划求解某企业的产品生产数据如下表某企业的产品生产数据如下表分工厂分工厂单位产品生产时间单位产品生产时间每周可利用时间每周可利用时间门门窗窗11小时小时04小时小时202小时小时12小时小时33小时小时2小时小时18小时小时单位利润单位利润$300500理论模型理论模型(2)求解如下的线性规划问题求解如下的线性规划问题第一步：选择决策变量单元格第一步：选择决策变量单元格决策变量的初始值一般赋决策变量的初始值一般赋0，并用较醒目的颜色表示。，并用较醒目的颜色表示。第二步：目标单元格，用函数公式表示第二步：目标单元格，用函数公式表示 并用较醒目的颜色表示。并用较醒目的颜色表示。第三步：约束条件左边项用函数表示第三步：约束条件左边项用函数表示第四步第四步:激活规划求解激活规划求解,确定可变单元格和目标单元格确定可变单元格和目标单元格第五步第五步:增加约束条件增加约束条件第六步第六步:完成求解对话框完成求解对话框第七步第七步:求解方式的选择求解方式的选择第八步第八步:从求从求解解结果对话框选择所要的报告结果对话框选择所要的报告求解求解结果报告结果报告灵敏性报告灵敏性报告极限报告极限报告 求求解解结果报告结果报告 灵敏性报告灵敏性报告 极限值报告极限值报告2.用用Excel Solver Table 进行敏感性分析进行敏感性分析Solver Table宏的安装使用Be sure that the Solver is installed.If it is,it should 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 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)有两个目标函数系数同时变动的影响有两个目标函数系数同时变动的影响Select these cells(C17:H20),before choosing the Solver Table.16门和窗的门和窗的利润同时变化时利润同时变化时,最优解的变化最优解的变化3.用用Excel Solver 求解运输问题和指派问题求解运输问题和指派问题例 Better Products公司决定利用三个有剩余生产能力的工厂生产四种新产品，问题：问题：哪个工厂生产哪种产品？哪个工厂生产哪种产品？40303020需要的产量4521273037375232940275$24$28$27$411工厂工厂剩余生剩余生产能力产能力4321产品产品单位成本单位成本如果一类产品可以在不同的工厂生产如果一类产品可以在不同的工厂生产,可以将此问题看成运输问题可以将此问题看成运输问题G11=sum(C11:F11)C14=sum(C11:C13G12=sum(C12:F12)G13=sum(C13:F13)I16=sumproduct($C$4:$F$6,$C$11:$F$13)如果一类产品不能在不同的工厂生产如果一类产品不能在不同的工厂生产,可以将此问题看成指派问题可以将此问题看成指派问题,并且此时，第一、第二个工厂有生产两种产品的能力。并且此时，第一、第二个工厂有生产两种产品的能力。例题例题:固特产品公司研究发展部开发了三种新产品，公司有两个工固特产品公司研究发展部开发了三种新产品，公司有两个工厂可以生产这些新产品，每种产品都可以在两个工厂中的任意一个厂可以生产这些新产品，每种产品都可以在两个工厂中的任意一个进行生产，为了防止公司生产线的过度多样化进行生产，为了防止公司生产线的过度多样化，同时也为了管理上同时也为了管理上的方便，公司管理层增加了以下限制：的方便，公司管理层增加了以下限制：(1)从从三种三种产品种最多选择其中两种进行；产品种最多选择其中两种进行；(2)两个工厂中必须选出一个专门生产两种新产品。两个工厂中必须选出一个专门生产两种新产品。每每单位产品生产时间单位产品生产时间(小时小时)每周可利用生产每周可利用生产时间时间(小时小时)工厂工厂产品产品1产品产品2产品产品3 134230246240单位利润573(千美元)可销售数量759(每周)三种产品至三种产品至多生产二种多生产二种两个两个工厂中选工厂中选一个生产新产一个生产新产品品该该问题的混合整数规划模型问题的混合整数规划模型该该问题的问题的EXCEL电子表格模型电子表格模型4.用用Excel Solver求解网络问题求解网络问题例例1：求解如下的一个网络最大流问题，弧旁括号中第一个数字为求解如下的一个网络最大流问题，弧旁括号中第一个数字为弧容量，第二个数字为现有流量。弧容量，第二个数字为现有流量。s2134t(8,3)(6,6)(7,6)(2,0)(3,0)(10,4)(8,4)(3,3)(7,7)最大流问题最大流问题例例2：若下图中弧旁括号中第一个数字为弧容量，第二个数字为单若下图中弧旁括号中第一个数字为弧容量，第二个数字为单位流量的费用，求该问题的一个最小费用最大流。位流量的费用，求该问题的一个最小费用最大流。s2134t(8,3)(6,6)(7,6)(2,1)(3,1)(10,4)(8,4)(3,3)(7,7)该问题分两步做，第一步该问题分两步做，第一步先不考费用求最大流。先不考费用求最大流。最小费用最大流问题最小费用最大流问题第二步：将最大流固定，求最小费用。第二步：将最大流固定，求最小费用。目标为最小费用目标为最小费用例题例题3：求下图中的从起点：求下图中的从起点O到终点到终点T的最短路问题，弧的最短路问题，弧旁的数字为两点之间的距离。旁的数字为两点之间的距离。最短路线为：最短路线为：OA B E F T最短路线为：最短路线为：OA B E F T5.用用Excel Solver 做线性回归分析做线性回归分析 线性回归用来反映因变量与自变量之间的线性关系。应用线性线性回归用来反映因变量与自变量之间的线性关系。应用线性回归，首先需要判断变量之间是否具有线性关系，这可以通过画散回归，首先需要判断变量之间是否具有线性关系，这可以通过画散点图得到。画散点图的方法是点图得到。画散点图的方法是：1）选择画散点图的数据区域；）选择画散点图的数据区域；2）在插入菜单中选择插入）在插入菜单中选择插入“图表图表”3）在图表对话框中选择散点图类型。）在图表对话框中选择散点图类型。进一步，可以通过选择图表，在图表菜单条下选择添加趋进一步，可以通过选择图表，在图表菜单条下选择添加趋势线，在添加趋势线对话框中，选择选项，显示公式和势线，在添加趋势线对话框中，选择选项，显示公式和R2值值多元多元线性回归：线性回归：y=a+b1x1+b2x2例题：若某个公司的企业销售及有关变量数据如下表所示：例题：若某个公司的企业销售及有关变量数据如下表所示：应用应用Excel中的数据分析工具，得到的多元回归结果如下：中的数据分析工具，得到的多元回归结果如下：回归方程如下：回归方程如下：预测值和残差预测值和残差6.用用Excel Solver 进行决策分析进行决策分析A出售钻探干涸(0.75)有石油(0.25)-10070090B100100例：求解如下简单的决策树例：求解如下简单的决策树TreePlan的安装的安装:(1)首先从学习光盘中找到首先从学习光盘中找到TreePlan程序模块程序模块;(2)将将 TreePlan程序模块拷贝到程序模块拷贝到Program filesMicrosoft office Officelibrary目录下目录下;(3)在在 Excel工具菜单中选择加载宏工具菜单中选择加载宏;(4)在加载宏对话框中选中在加载宏对话框中选中 TreePlan程序程序;(5)回到回到Excel工具菜单中查看工具菜单中查看,是否有是否有Decision Tree菜单条菜单条,如果有如果有,则安装成功则安装成功.选择工具菜单条中的选择工具菜单条中的Decision Tree点击创建新树点击创建新树,这样就创建了一个如下图所示的决策树这样就创建了一个如下图所示的决策树选中每一分枝末端，再一次点击决策树菜单，可以添加决策结选中每一分枝末端，再一次点击决策树菜单，可以添加决策结点或状态结点。点或状态结点。用用决策树做敏感性分析：通过建立电子表格和决策树之间决策树做敏感性分析：通过建立电子表格和决策树之间数据的联系，可方便地对决策问题进行敏感性分析。数据的联系，可方便地对决策问题进行敏感性分析。用数据表系统地进行敏感性分析用数据表系统地进行敏感性分析(1)首先在电子表格中创建一张表首先在电子表格中创建一张表,列出数据单元格的各个尝试值列出数据单元格的各个尝试值.(2)数据表的第一行数据表的第一行,输入公式使之与相关的输出单元格相关联输入公式使之与相关的输出单元格相关联.(3)选择整个数据表选择整个数据表,然后选择数据菜单中的模拟运算表菜单条然后选择数据菜单中的模拟运算表菜单条.abcfdgeh做地震勘探不做地震勘探-300不好的结果好的结果钻探出售有石油干涸钻探出售有石油干涸钻探出售有石油干涸-10090-100900.1430.8570.50.5-100900.250.75670-13060670-13060700-10090-15.7601231232702701001000.70.3复杂决策树的求解与分析复杂决策树的求解与分析复杂决策树的敏感性分析复杂决策树的敏感性分析使用使用SensIt创建三种类型的敏感性分析图创建三种类型的敏感性分析图SensIt的安装的安装:与与TreePlan 软件的安装类似软件的安装类似(1)首先从学习光盘中找到首先从学习光盘中找到SensIt程序模块程序模块;(2)将将 程序模块程序模块SensIt拷贝到拷贝到Program filesMicrosoft office Officelibrary目录下目录下;(3)在在 Excel工具菜单中选择加载宏工具菜单中选择加载宏;(4)在加载宏对话框中选中在加载宏对话框中选中 SensIt程序程序;(5)回到回到Excel工具菜单中查看工具菜单中查看,是否有是否有SensIt菜单条菜单条,如果有如果有,则安装成功则安装成功.SensIt给出了三种不同的敏感性分析图选择给出了三种不同的敏感性分析图选择:单因素图单因素图(Plot)、蛛网图蛛网图(Spider)、旋风图旋风图(Tornado)使用使用SensIt画单因素图画单因素图 单因素图用来生成一种图形，显示了输出单元格对应于单一数单因素图用来生成一种图形，显示了输出单元格对应于单一数据单元格的不同数值变化。据单元格的不同数值变化。这个图表明，当先验概率略大于这个图表明，当先验概率略大于0.15时期望收益开始上升时期望收益开始上升.当先当先验概率在验概率在0.3附近时期望收益开始以更快的速度上升附近时期望收益开始以更快的速度上升.这就暗示了最这就暗示了最优策略会随着先验概率的变化而变化优策略会随着先验概率的变化而变化.最佳策略最佳策略:设设p=有石油的先验概率有石油的先验概率(1)如果如果p0.168,则出售土地则出售土地(不进行地震勘探不进行地震勘探);(2)如果如果0.169 p 0.308,则进行地震勘探则进行地震勘探,结果好则钻探结果好则钻探,结果不好则结果不好则出售土地出售土地.(3)如果如果0.309,则钻探石油则钻探石油(不进行地震勘探不进行地震勘探)用用SensIt分析的蛛网图分析的蛛网图 Spider 假设我们要考察如果单元格假设我们要考察如果单元格V4:V7中的一项成本或收益以加减中的一项成本或收益以加减10%的方式改变时期望收益会发生怎样的变化的方式改变时期望收益会发生怎样的变化,蛛网图可用来进行蛛网图可用来进行这一类分析这一类分析.以下蛛网图的对话框和对应的电子表格以下蛛网图的对话框和对应的电子表格.SensIt_敏感性分析敏感性分析_蛛网图蛛网图用用 SensIt画旋风图画旋风图 SensIt蛛网图的一个缺陷是它假设每一个数据都以同样的百分蛛网图的一个缺陷是它假设每一个数据都以同样的百分比发生变化比发生变化.例如例如,我们考虑这样一种情况我们考虑这样一种情况,成本或收益数据中任一成本或收益数据中任一条的变化都可能是上升或下降条的变化都可能是上升或下降10%.而其中某些数据可能会比其余的而其中某些数据可能会比其余的数据更不确定数据更不确定(或者更可靠或者更可靠).旋风图可以克服这个缺陷旋风图可以克服这个缺陷.不过不过,它需它需要每一个数据单元格的最小值、基数、和最大值要每一个数据单元格的最小值、基数、和最大值.SensIt敏感性分析敏感性分析 旋风图旋风图 图中的每一条横杠表示相应的成本或者收益在横杠两端所标数图中的每一条横杠表示相应的成本或者收益在横杠两端所标数字之间变化时的期望收益的变化范围。每条横杠的宽度衡量了期望字之间变化时的期望收益的变化范围。每条横杠的宽度衡量了期望收益对横杠的成本或者收益的敏感性。此图再次说明，有石油时的收益对横杠的成本或者收益的敏感性。此图再次说明，有石油时的收益比其他成本或收益会期望引起更大的敏感性。收益比其他成本或收益会期望引起更大的敏感性。The End of LectureThanks