一应用Excel求解不确定型决策-东北大学秦皇岛分校

1、决策学实验指导书吕世斌编写东北大学秦皇岛分校二。一四年九月“决策学”是行政管理专业的专业基础课，为了帮助学生更好地学习决策学这门课程， 编写了本实验指导书。使用软件主要是Excel，实验一的任务可以在EXCEL中完成。实验 二除了在Excel中操作外，还需要加载TreePlan程序。两个实验分别是：应用Excel求解不 确定型决策、应用Excel求解风险型决策问题。指导书中实验内容用于演示实验过程，给出 实验步骤，帮助学生上机操作。由于作者水平有限，书中难免有不足与错误之处，敬请批评 指正。实验一应用Excel求解不确定型决策4实验二 应用Excel求解风险型决策问题18实验一 应用Excel

2、求解不确定型决策实验目的学会在Excel中使用乐观决策准则、悲观决策准则、乐观系数准则、后悔值准则和等概 率准则来解决不确定型决策问题。实验内容将教材中例2.1模型化，转化为损益矩阵，如图1.1所示。利用乐观决策准则、悲观决 策准则、乐观系数准则、后悔值准则和等概率准则求解图1.1损益矩阵中的最优方案。实验步骤1、打开 Excel从“开始”菜单中打开Excel。2、创建一个项目单击File (文件)菜单中的New (新建)选项，新建一个空白工作簿。3、输入数据在EXCEL的单元格中输入原始数据，其中自然状态设为S1S5，方案设为ala5，各 个收益值见图1.1。4、使用乐观决策准则进行求解在G

3、列求各个行动方案下的最大收益值，将G3命名为Max。选择单元格G4，在 “插入”菜单下选择“函数”对话框，如图1.2所示。 在“选择函数”栏下，找到函数Max”(最大值)，单击确定，弹出“函数参数” 对话框，如图1.3所示。在参数栏“Number1”里输入a1方案下的各个收益值所在位置“B4： F4”，单击 确定，即在G4单元格得到a1方案的最大损益值。拖动填充手柄至G8，可得到各个行动方 案的最大损益值，如图1.4所示。图1.1打开Excel输入数据图1.3 Max函数对话框在H列求出具有最大最大(MaxMax)收益值的行动方案。选择单元格G9,在“插入”菜单下选择“函数”对话框，仍然选择函

4、数“Max”，但 这次在参数栏“Numberl”里输入的是不同行动方案下的各个最大收益值所在位置“G4:G8”， 单击“确定”，在G9单元格显示的就是乐观准则下的最大最大(MaxMax)收益值，如图 1.5所示。为了更清楚地在Excel表中表示出方案，可以在H4到H8列插入IF函数，如在 单元格H4中插入=IF(G4=MAX(Max), ”-Maxmum”，”)”，就可以用箭头指出哪 个方案是最优的。如图1.6所示，可以看到最大最大收益值80所对应的方案a5为最优方案。图1.5乐观决策准则下的最大最大收益值图1.6乐观决策准则下的最优决策方案5、使用悲观决策准则进行求解（1）在EXCEL单元格

5、中输入原始数据。（2）在G列求各个行动方案下的最小收益值，将G3命名为Min。选择单元格G4，在 “插入”菜单下选择“函数”对话框，弹出“插入函数”对话框，找到函数“Min”（最小值），单击确定，弹出“函数参数”对话框，如图1.7所示。（3）在参数栏“Numberl”里输入al方案下的各个收益值所在位置“B4: F4”，单击 确定，即在G4单元格得到a1方案的最小损益值。拖动填充手柄至G8，可得到各个行动方 案的最小损益值，如图1.8所示。（4）在第9行求出具有最大最小损益值的行动方案，将A9命名为MaxMin。在单元格G9选择函数“Max”，在参数栏“Number1”里输入的是不同行动方案下

6、的 各个收益值所在位置“G4:G8”，单击“确定”，在G4单元格显示的就是悲观准则下的最 大最小（MaxMin）收益值，如图1.9所示。为了更清楚地在EXCEL表中表示出方案，可以 在H4到H8列插入IF函数，如在单元格H4中插入“=IF（G4=MAX（Min）, ”- Maxmum”，”）”，就可以用箭头指出哪个方案是最优的。如图1.10所示，可以看到最 大最小损益值0所对应的方案a1为最优方案。剧|文件史）编辑 视图但）插入隹）格式Q）工具住）数据皿 窗口也）刮助如键入需要帝助的问题-5 x适如临算随矿p |b / -I拿言号国|，|章|阻皿 X J园=IIN（B4:F4）ABcDEFGH

7、iJKLM12悲观准则3SIS2S3S4S5020Min4al0000：B4:F4)5a2-102020206a3-20104040407a4-3003060608a5-40-10205080910函教参数11WINN*Q Q Q 012jniberl 堰理13umber2 |玉司=14=01516Numb er 1:numb er 1, number2.准备从中求取最小值的1到30个数值、空单元格、谡辑值或文本数值17181920计算结果=021有关该函数的帮助00I确定 I 取消2223242526272829H 4 H | Sheetl Sheet2 I Sheets /J iI篷图十自

8、选图形十今，兰，三W三J卒:g英,z1，野夬策学课程实验指导.e3 Microsoft Excel - Bookl707:56图1.7 Min函数对话框图1.9悲观决策准则下的最大最小收益值在EXCEL单元格中输入原始数据。首先在G列和H列分别用函数Max和Min求出各个行动方案下的最大损益值和最小损 益值，如图1.11所示。在第I列计算在乐观系数a= 7下各个行动方案的加权收益值，在I3输入乐观系 数 a= 0.7。选择单元格14,手动输入公式“=G4*I3+H4*(1-I3)”，按回车键即得a= 0.7条件下的加 权平均收益，拖动填充手柄至18,得到各个行动方案的加权收益值，如图1.12所

9、示。在第9行求出具有最大期望损益值的行动方案，将A9命名为Max。在单元格I9选择函数“Max”，在参数栏Number1”里输入不同行动方案在乐观系数 a= 0.7下的加权损益值所在位置“I4:I8”，单击确定，就可以很容易看到最大加权损益值 44所对应的的方案a5为最优行动方案。运用类似的方法，我们还可以求出在不同乐观系数水平下的最优行动方案，如图1.13 所示。图1.11各个方案下的最大最小收益值回 Microsoft Excel - Bookl1 D 回曹 文件 编辑您）视图世）插入座）110A格式Co）J，工具（X）数据但）窗口S3i地）帮助但）宋体 12勰人需要帮助的问题B Z U

10、拿号号圉|等孝田加，-5 XABCDEFGHIJKLM12乐观系数隹则乐观系数3SIS2S3S4S5MaxMin0. 74al000000005a2-102020202020-10116a3-201040404040-20227a4-30030606060-30338a5-40-1020508080-404491011121314151617181920212223242526272829T14 4 1一 1图1.12各个行动方案在a = 0.7下的加权损益值（1）在Excel单元格中输入原始数据。在第9行用“Max”函数求出每种自然状态下 的最大收益值。如图1.14所示。（2）由决策矩阵计算

11、后悔值矩阵。例如单元格B4对应的后悔值=B9-B4”，计入单 元格B13。可类似计算其余单元格，得到的后悔值矩阵如图1.15所示。（3）在G列求出各个行动方案的最大后悔值，将G12命名为Max。选择单元格G13,在“插入”菜单下选择“函数”对话框，找到函数“Max”（最大值）， 单击确定，在参数栏“Number1”里输入a1方案下的各个后悔值所在位置“B13:F13”，单 击确定，即在G13单元格得到a1方案的最大后悔值。拖动填充手柄至G17，得到各个行动 方案的最大后悔值，如图1.16所示。（4）在H列求出具有最小最大后悔值的行动方案。在单元格G18选择函数“Min”，在参数栏“Number

12、1 ”里输入的是不同行动方案下的 各个后悔值所在位置“G13:G17”，单击“确定”，在G18单元格显示的就是后悔值准则下 的最小最大（MinMax）后悔值，如图1.17所示。我们可以看到最小最大后悔值30所对应 的方案a4为最优方案。同样，可以在H13到H17列插入IF函数，如在单元格H13中插入二IF（G13=MIN（Max）, ”- Minimum ,”）”，就可以用箭头指出哪个方案是最优的。图1.14原始数据及每种自然状态下的最大值图1.15决策矩阵和后悔值矩阵图1.16各个行动方案下的最大后悔值图1.17后悔值准则下的最有决策7、使用等概率准则求解在等概率准则的操作中，由于假定了每种

13、自然状态出现的概率是相等的，所以求每种行 动方案的期望收益值的过程实际上就是求其收益值算数平均数的过程。我们可以利用Excel 中的函数“Average”来实现这一操作，具体操作步骤如下：（1）在Excel的单元格中输入数据。（2）在G列求各个行动方案下的期望损益值，将G3命名为Average。选择单元格G4,在“插入”菜单下选择“函数”对话框，弹出“插入函数”对话框， 找到函数“Average”（平均值），单击确定，弹出“函数参数”对话框，如图1.18所示。（3）在参数栏“Number1 ”里输入a1方案下的各个收益值所在位置“B4:F4”，单击 确定，即在G4单元格得到a1方案的期望收益值

14、。拖动填充手柄至G8，得到各个行动方案 的期望收益值，如图1.19所示。（4）在第9行求出具有最大期望损益值的行动方案，将A9命名为Max。在单元格G9选择函数“Max”，在参数栏“Number1”里输入的是不同行动方案下的 各个收益值所在位置“G4:G8”，单击“确定”，在G9单元格显示的就是等概率准则下的 最大期望收益值，如图1.20所示。我们可以看到最大期望收益值24所对应的方案a4为最 优方案。同样，可以在H4到H8列插入IF函数，如在单元格H4中插入=IF（G4=MAX （Average）, ” Maxmum”，”）”，就可以用箭头指出哪个方案是最优的。为文件 编辑鱼）视图 插入H）

15、格式廷）工具 数据 窗口地）帮助如禅入需要帮助的问题 - I? X11 A-IftlttilZ3EE3I B 1 ui 言言耋圉1毁1 拿芸average x二AVERAGE（B4:F4）函数参数AVERAGENumber1Number2 匚返回其参数的算术平均值；参数可以是数值或包含数值的名称、数组或引用Numberl:numberl, numb er 2用于计算平均值的1到30个数值参数计算结果有关该函数的帮助M图1.20等概率决策准则下的最优决策问题讨论本实验重点在于熟悉决策分析的操作环境，掌握乐观决策准则、悲观决策准则、乐观系 数准则、后悔值准则和等概率准则，解决不确定型决策问题。总结

16、在使用实验环境所出现的 问题及解决方法。实验二应用Excel求解风险型决策问题实验目的学会在Excel中使用决策树分析法来解决风险型决策问题。实验内容使用决策树分析教材中例3.5 “生物影像公司的发展战略问题”。实验步骤1、需要加载由麦克尔密德堂教授开发的Excel的加载宏TreePlan程序在电子表格上进 行构建和分析决策树。2、运行Excel,“工具一加载宏”弹出如图2.1所示的对话框。单击“浏览”，找到需要 的加载宏“TreePlan”，选中之后单击“确定”，如图2.2所示，就可以在Excel中建立自 己的决策树了。3、加载“TreePlan”之后，重新打开Excel，点击“工具一Dec

17、ision tree”，选择工具菜 单中的“创建树”，这样就建立了一个默认的如图2.3所示的决策树，它只有一个方形节点 和两条树枝。4、如果还需要其他的节点或树枝，在TreePlan中对节点做改变是很容易的。只要选择包 含节点的单元格，再从工具菜单里选择决策树选项。这样就弹出了一个对话框，允许你改变 节点的类型或者添加更多的树枝。5、要在决策树的末尾创建一个节点，选择包含结束节点的单元格，在选择工具菜单里的决 策树选项。这样就可以将结束节点转化含有所希望的树枝数目（15条）的决策节点或事 件节点。如图2.4所示。6、最初，每一条树枝都会有一个自动产生的默认值为0的净现金流量（在树枝标签下面出 现的数字），同样，两条树枝的任何一条都伸向事件节点，显示为事件发生的默认的50% 概率。根据贝叶斯规则TreePlan总是能够找到现有决策树的最优策略。问题讨论总结调试程序所出现的错误种类和相应的解决方法。S Microsoft Excel -新建 Microsoft Excel 工作表曹 文件 编辑田）视图但）插入）格式 工具 数据 窗口地）帮助0） 岌地：宋体!;131415161718192021222324可用加载宏:|F|Internet Assistant VBA四EreePlan Decisi