Excel函数在决策管理中的应用.pptx
第8章用用ExcelExcel函数进行财务管理函数进行财务管理高效办公“职”通车Excel 函数公式范例应用vvExcel的一般线性模型优化决策的一般线性模型优化决策vv用用Excel函数进行运输与指派函数进行运输与指派vv用用Excel函数进行方案选择函数进行方案选择高效办公“职”通车Excel 函数公式范例应用ExcelExcel的一般线性模型优化决策的一般线性模型优化决策vv经济价值最优的生产决策经济价值最优的生产决策vv计算收益最大化的投资组合计算收益最大化的投资组合vv求解酒店最优化的配餐方案求解酒店最优化的配餐方案任何一个企业的管理都必须对如何向不同的活动分配资源的问题作出决策，以便达到组织的目标。线性规划是帮助管理这类决策问题的一个功能非常强大的解决工具。高效办公“职”通车Excel 函数公式范例应用ExcelExcel的一般线性模型优化决策的一般线性模型优化决策某餐馆从相关权威营养学家处获得了如图8-18所示的两种食物的碳水化合物、蛋白质、脂肪含量以及人体每天所需要的摄入量，已知每份牛排的成本为5元，土豆的成本为2元，求每顿向顾客至少提供多少份牛排和土豆才能满足营养且成本最低。（1）建立求解模型，输入已知条件和限制条件，并设定自变量和因变量，如图8-19所示。（2）选中D9单元格，输入公式“=SUMPRODUCT(B9:C9,B$13:C$13)”，按下Enter键后将其填充复制到D10:D11单元格区域，结果如图8-20所示。（3）选中F13单元格，输入公式“=SUMPRODUCT(B13:C13,B12:C12)”，按下Enter键确认输入，结果如图8-21所示。（4）选中F13单元格，单击【数据】【规划求解】命令项，打开【规划求解参数】对话框，然后进行如图8-22所示的设置。（5）单击【求解】按钮，再单击【确定】按钮，即可得到该问题的最优解，如图8-23所示。因此，餐馆向顾客提供1.29份牛排和3.07分土豆即可以最低成本满足顾客的营养要求。vv求解酒店最优化的配餐方案高效办公“职”通车Excel 函数公式范例应用图8-18 已知条件图8-19 建立求解模型 图8-20 计算合计值高效办公“职”通车Excel 函数公式范例应用图8-21 计算目标函数值 图8-22 设置规划求解参数图8-23 求解结果高效办公“职”通车Excel 函数公式范例应用用用ExcelExcel函数进行运输与指派函数进行运输与指派vv运输指派问题概述vv用Excel函数实现运输配送vv用Excel函数实现选址vv用Excel函数实现员工任务指派高效办公“职”通车Excel 函数公式范例应用用用ExcelExcel函数进行运输与指派函数进行运输与指派vv运输指派问题概述运输问题指的是如何确定以最优的方式运输货物，而指派问题则是指如何以最优的方式将任务指派给人员。为使读者更为直观地理解这两个概念，下面继续使用实例向读者介绍Excel在运输和指派问题中的应用。高效办公“职”通车Excel 函数公式范例应用用用ExcelExcel函数进行运输与指派函数进行运输与指派某木材公司是一个拥有3个木材资源区和5个需要供应的市场的木材公司，木材资源区每年所能生产的木材量分别为1550万、2100万、1500万板英尺。每年5个市场能够销售的木材量分别为1000、1300、950、1000、820万板英尺。该公司使用火车运输木材，运输成本如图8-24所示，求成本最小的运输计划。（1）建立求解模型，如图8-25所示。（2）选中B6单元格，输入公式“=SUM(B3:B5)”，按下Enter键后将其填充复制到C6:F6单元格区域，结果如图8-26所示。（3）选中G3单元格，输入公式“=SUM(B3:F3)”，按下Enter键后将其填充复制到G4:G5单元格区域，结果如图8-27所示。（4）选中G11单元格，输入公式“=SUMPRODUCT(B3:F5,B11:F13)”，按下Enter键确认输入，结果如图8-28所示。（5）选中G11单元格，单击【数据】【规划求解】命令项，打开【规划求解参数】对话框，进行如图8-29所示的设置。（6）单击【求解】按钮，在弹出来的【规划求解结果】对话框中单击【确定】按钮，即可得出求解结果，如图8-30所示。此例中的问题属于平衡运输问题，即总生产量和总销售量相等，但在实际工作中，很多运输问题都属于不平衡的运输问题，即运输需求量与供应量相等。在这种情况下，操作与平衡运输问题一样，只是约束条件不同而已，读者可以根据实际情况灵活运用。vv用Excel函数实现运输配送高效办公“职”通车Excel 函数公式范例应用图8-25 建立求解模型图8-26 计算市场销售量图8-27 计算货源的生产量图8-28 计算总运费高效办公“职”通车Excel 函数公式范例应用图8-29【规划求解参数】对话框图8-30 求解结果高效办公“职”通车Excel 函数公式范例应用用用ExcelExcel函数进行方案选择函数进行方案选择vv用Excel函数解决非确定型决策vv决策树概述vv用决策树解决风险型决策方案选择是指决策者在若干可能方案中做出选择的过程。每个方案选择问题都有其特定的目标，决策者在选择方案时借助于自己的经验，通过获取可靠的有用信息，用科学的分析方法进行分析，然后作出符合实际情况的决定，选择最优方案，以便用于指导未来的行动计划。高效办公“职”通车Excel 函数公式范例应用用用ExcelExcel函数进行方案选择函数进行方案选择非确定性决策是指决策者对于拥有多个自然状态的决策问题在未来发生的可能性一无所知的情况下进行的决策活动。尽管为非确定性，但在这种情况下，仍然存在一些可供决策人员参考的决策方法。某农场能够种植的作物有：棉花、玉米、土豆，种植不同的作物的时候如图8-48所示，问到底该种植哪一种作物？1乐观法乐观法乐观法也叫大中取大原则。即计算各决策下每种自然状态的最大收益值，然后取这些最大收益值中的最大值。2悲观法悲观法悲观法也较小中取大原则，即计算各决策中每种自然状态的最小收益值，然后取这些最小收益值中的最大值。3最小后悔值法最小后悔值法最小后悔值法是把每一种自然状态对应的最大收益视为理想目标，而以它与该状态的其他收益值之差作为为达到理想的后悔值，由此得到后悔矩阵，再求每行的最大值，这些值中的最小值对应的策略就是所求策略。vv用Excel函数解决非确定型决策高效办公“职”通车Excel 函数公式范例应用图8-48 收益矩阵高效办公“职”通车Excel 函数公式范例应用用用ExcelExcel函数进行方案选择函数进行方案选择vv决策树概述决策树是一种可以用来帮助决策者进行决策的树状图。它是由决策节点、方案枝、事件节点、概率枝和结果节点按一定关系联结而成的树状图。决策节点一般用小方框代表，表示需要在该处进行决策，从它向后引出的每一分支代表可能选取的一个策略或方案。事件节点用小圆圈表示，从它引出的分支代表其后续状态，分支上标明的数字表示事件在该状态下发生的概率，也被称为概率枝。结果节点为决策树的末梢，用小三角表示，代表决策问题的一个可能结果，旁边的数字为该情况下的益损值。高效办公“职”通车Excel 函数公式范例应用用用ExcelExcel函数进行方案选择函数进行方案选择vv用决策树解决风险型决策在风险型决策问题中经常会采用决策树的方法来进行解决，本小节就将通过实例来介绍决策树在风险型决策问题中的应用。【例例】某公司为生产一种新产品拟定了两种方案，一种是建大厂，一种是建小厂。两者的使用期均为10年，据估计，生产出的产品在此期间销路好的可能性为0.8，这两种方案的年度收益如图8-56所示，问此公司到底是建大厂好还是建小厂好？（1）根据已知条件建立该问题的决策树，如图8-57所示。（2）单击E11单元格，输入公式“=IF(ABS(1-SUM(H6,H11)=0.00001,SUM(H6*I9,H11*I14),NA()”，然后再选中E21单元格，输入公式“=IF(ABS(1-SUM(H16,H21)=0.00001,SUM(H16*I19,H21*I24),NA()”，按下Enter键确认输入，结果如图8-58所示。（3）选中A16单元格，输入公式“=MAX(E11,E21)”，按下Enter键确认输入，结果如图8-59所示。因此该问题的最优方案是建大厂，且10年后的收益为760。高效办公“职”通车Excel 函数公式范例应用图8-56 已知条件图8-57 建立决策树 高效办公“职”通车Excel 函数公式范例应用图8-58 求事件节点的收益值图8-59 求决策节点的收益值高效办公“职”通车Excel 函数公式范例应用高效办公“职”通车Excel 函数公式范例应用
