一應(yīng)用Excel求解不確定型決策-東北大學(xué)秦皇島分校

1、決策學(xué)實(shí)驗(yàn)指導(dǎo)書呂世斌編寫東北大學(xué)秦皇島分校二。一四年九月“決策學(xué)”是行政管理專業(yè)的專業(yè)基礎(chǔ)課，為了幫助學(xué)生更好地學(xué)習(xí)決策學(xué)這門課程， 編寫了本實(shí)驗(yàn)指導(dǎo)書。使用軟件主要是Excel，實(shí)驗(yàn)一的任務(wù)可以在EXCEL中完成。實(shí)驗(yàn) 二除了在Excel中操作外，還需要加載TreePlan程序。兩個(gè)實(shí)驗(yàn)分別是：應(yīng)用Excel求解不 確定型決策、應(yīng)用Excel求解風(fēng)險(xiǎn)型決策問題。指導(dǎo)書中實(shí)驗(yàn)內(nèi)容用于演示實(shí)驗(yàn)過程，給出 實(shí)驗(yàn)步驟，幫助學(xué)生上機(jī)操作。由于作者水平有限，書中難免有不足與錯(cuò)誤之處，敬請(qǐng)批評(píng) 指正。實(shí)驗(yàn)一應(yīng)用Excel求解不確定型決策4實(shí)驗(yàn)二 應(yīng)用Excel求解風(fēng)險(xiǎn)型決策問題18實(shí)驗(yàn)一 應(yīng)用Excel

2、求解不確定型決策實(shí)驗(yàn)?zāi)康膶W(xué)會(huì)在Excel中使用樂觀決策準(zhǔn)則、悲觀決策準(zhǔn)則、樂觀系數(shù)準(zhǔn)則、后悔值準(zhǔn)則和等概 率準(zhǔn)則來解決不確定型決策問題。實(shí)驗(yàn)內(nèi)容將教材中例2.1模型化，轉(zhuǎn)化為損益矩陣，如圖1.1所示。利用樂觀決策準(zhǔn)則、悲觀決 策準(zhǔn)則、樂觀系數(shù)準(zhǔn)則、后悔值準(zhǔn)則和等概率準(zhǔn)則求解圖1.1損益矩陣中的最優(yōu)方案。實(shí)驗(yàn)步驟1、打開 Excel從“開始”菜單中打開Excel。2、創(chuàng)建一個(gè)項(xiàng)目單擊File (文件)菜單中的New (新建)選項(xiàng)，新建一個(gè)空白工作簿。3、輸入數(shù)據(jù)在EXCEL的單元格中輸入原始數(shù)據(jù)，其中自然狀態(tài)設(shè)為S1S5，方案設(shè)為ala5，各 個(gè)收益值見圖1.1。4、使用樂觀決策準(zhǔn)則進(jìn)行求解在G

3、列求各個(gè)行動(dòng)方案下的最大收益值，將G3命名為Max。選擇單元格G4，在 “插入”菜單下選擇“函數(shù)”對(duì)話框，如圖1.2所示。 在“選擇函數(shù)”欄下，找到函數(shù)Max”(最大值)，單擊確定，彈出“函數(shù)參數(shù)” 對(duì)話框，如圖1.3所示。在參數(shù)欄“Number1”里輸入a1方案下的各個(gè)收益值所在位置“B4： F4”，單擊 確定，即在G4單元格得到a1方案的最大損益值。拖動(dòng)填充手柄至G8，可得到各個(gè)行動(dòng)方 案的最大損益值，如圖1.4所示。圖1.1打開Excel輸入數(shù)據(jù)圖1.3 Max函數(shù)對(duì)話框在H列求出具有最大最大(MaxMax)收益值的行動(dòng)方案。選擇單元格G9,在“插入”菜單下選擇“函數(shù)”對(duì)話框，仍然選擇函

4、數(shù)“Max”，但 這次在參數(shù)欄“Numberl”里輸入的是不同行動(dòng)方案下的各個(gè)最大收益值所在位置“G4:G8”， 單擊“確定”，在G9單元格顯示的就是樂觀準(zhǔn)則下的最大最大(MaxMax)收益值，如圖 1.5所示。為了更清楚地在Excel表中表示出方案，可以在H4到H8列插入IF函數(shù)，如在 單元格H4中插入=IF(G4=MAX(Max), ”-Maxmum”，”)”，就可以用箭頭指出哪 個(gè)方案是最優(yōu)的。如圖1.6所示，可以看到最大最大收益值80所對(duì)應(yīng)的方案a5為最優(yōu)方案。圖1.5樂觀決策準(zhǔn)則下的最大最大收益值圖1.6樂觀決策準(zhǔn)則下的最優(yōu)決策方案5、使用悲觀決策準(zhǔn)則進(jìn)行求解（1）在EXCEL單元格

5、中輸入原始數(shù)據(jù)。（2）在G列求各個(gè)行動(dòng)方案下的最小收益值，將G3命名為Min。選擇單元格G4，在 “插入”菜單下選擇“函數(shù)”對(duì)話框，彈出“插入函數(shù)”對(duì)話框，找到函數(shù)“Min”（最小值），單擊確定，彈出“函數(shù)參數(shù)”對(duì)話框，如圖1.7所示。（3）在參數(shù)欄“Numberl”里輸入al方案下的各個(gè)收益值所在位置“B4: F4”，單擊 確定，即在G4單元格得到a1方案的最小損益值。拖動(dòng)填充手柄至G8，可得到各個(gè)行動(dòng)方 案的最小損益值，如圖1.8所示。（4）在第9行求出具有最大最小損益值的行動(dòng)方案，將A9命名為MaxMin。在單元格G9選擇函數(shù)“Max”，在參數(shù)欄“Number1”里輸入的是不同行動(dòng)方案下

6、的 各個(gè)收益值所在位置“G4:G8”，單擊“確定”，在G4單元格顯示的就是悲觀準(zhǔn)則下的最 大最小（MaxMin）收益值，如圖1.9所示。為了更清楚地在EXCEL表中表示出方案，可以 在H4到H8列插入IF函數(shù)，如在單元格H4中插入“=IF（G4=MAX（Min）, ”- Maxmum”，”）”，就可以用箭頭指出哪個(gè)方案是最優(yōu)的。如圖1.10所示，可以看到最 大最小損益值0所對(duì)應(yīng)的方案a1為最優(yōu)方案。劇|文件史）編輯 視圖但）插入隹）格式Q）工具?。?shù)據(jù)皿 窗口也）刮助如鍵入需要帝助的問題-5 x適如臨算隨礦p |b / -I拿言號(hào)國|，|章|阻皿 X J園=IIN（B4:F4）ABcDEFGH

7、iJKLM12悲觀準(zhǔn)則3SIS2S3S4S5020Min4al0000：B4:F4)5a2-102020206a3-20104040407a4-3003060608a5-40-10205080910函教參數(shù)11WINN*Q Q Q 012jniberl 堰理13umber2 |玉司=14=01516Numb er 1:numb er 1, number2.準(zhǔn)備從中求取最小值的1到30個(gè)數(shù)值、空單元格、謖輯值或文本數(shù)值17181920計(jì)算結(jié)果=021有關(guān)該函數(shù)的幫助00I確定 I 取消2223242526272829H 4 H | Sheetl Sheet2 I Sheets /J iI篷圖十自

8、選圖形十今，蘭，三W三J卒:g英,z1，野夬策學(xué)課程實(shí)驗(yàn)指導(dǎo).e3 Microsoft Excel - Bookl707:56圖1.7 Min函數(shù)對(duì)話框圖1.9悲觀決策準(zhǔn)則下的最大最小收益值在EXCEL單元格中輸入原始數(shù)據(jù)。首先在G列和H列分別用函數(shù)Max和Min求出各個(gè)行動(dòng)方案下的最大損益值和最小損 益值，如圖1.11所示。在第I列計(jì)算在樂觀系數(shù)a= 7下各個(gè)行動(dòng)方案的加權(quán)收益值，在I3輸入樂觀系 數(shù) a= 0.7。選擇單元格14,手動(dòng)輸入公式“=G4*I3+H4*(1-I3)”，按回車鍵即得a= 0.7條件下的加 權(quán)平均收益，拖動(dòng)填充手柄至18,得到各個(gè)行動(dòng)方案的加權(quán)收益值，如圖1.12所

9、示。在第9行求出具有最大期望損益值的行動(dòng)方案，將A9命名為Max。在單元格I9選擇函數(shù)“Max”，在參數(shù)欄Number1”里輸入不同行動(dòng)方案在樂觀系數(shù) a= 0.7下的加權(quán)損益值所在位置“I4:I8”，單擊確定，就可以很容易看到最大加權(quán)損益值 44所對(duì)應(yīng)的的方案a5為最優(yōu)行動(dòng)方案。運(yùn)用類似的方法，我們還可以求出在不同樂觀系數(shù)水平下的最優(yōu)行動(dòng)方案，如圖1.13 所示。圖1.11各個(gè)方案下的最大最小收益值回 Microsoft Excel - Bookl1 D 回曹 文件 編輯您）視圖世）插入座）110A格式Co）J，工具（X）數(shù)據(jù)但）窗口S3i地）幫助但）宋體 12勰人需要幫助的問題B Z U

10、拿號(hào)號(hào)圉|等孝田加，-5 XABCDEFGHIJKLM12樂觀系數(shù)隹則樂觀系數(shù)3SIS2S3S4S5MaxMin0. 74al000000005a2-102020202020-10116a3-201040404040-20227a4-30030606060-30338a5-40-1020508080-404491011121314151617181920212223242526272829T14 4 1一 1圖1.12各個(gè)行動(dòng)方案在a = 0.7下的加權(quán)損益值（1）在Excel單元格中輸入原始數(shù)據(jù)。在第9行用“Max”函數(shù)求出每種自然狀態(tài)下 的最大收益值。如圖1.14所示。（2）由決策矩陣計(jì)算

11、后悔值矩陣。例如單元格B4對(duì)應(yīng)的后悔值=B9-B4”，計(jì)入單 元格B13?？深愃朴?jì)算其余單元格，得到的后悔值矩陣如圖1.15所示。（3）在G列求出各個(gè)行動(dòng)方案的最大后悔值，將G12命名為Max。選擇單元格G13,在“插入”菜單下選擇“函數(shù)”對(duì)話框，找到函數(shù)“Max”（最大值）， 單擊確定，在參數(shù)欄“Number1”里輸入a1方案下的各個(gè)后悔值所在位置“B13:F13”，單 擊確定，即在G13單元格得到a1方案的最大后悔值。拖動(dòng)填充手柄至G17，得到各個(gè)行動(dòng) 方案的最大后悔值，如圖1.16所示。（4）在H列求出具有最小最大后悔值的行動(dòng)方案。在單元格G18選擇函數(shù)“Min”，在參數(shù)欄“Number

12、1 ”里輸入的是不同行動(dòng)方案下的 各個(gè)后悔值所在位置“G13:G17”，單擊“確定”，在G18單元格顯示的就是后悔值準(zhǔn)則下 的最小最大（MinMax）后悔值，如圖1.17所示。我們可以看到最小最大后悔值30所對(duì)應(yīng) 的方案a4為最優(yōu)方案。同樣，可以在H13到H17列插入IF函數(shù)，如在單元格H13中插入二IF（G13=MIN（Max）, ”- Minimum ,”）”，就可以用箭頭指出哪個(gè)方案是最優(yōu)的。圖1.14原始數(shù)據(jù)及每種自然狀態(tài)下的最大值圖1.15決策矩陣和后悔值矩陣圖1.16各個(gè)行動(dòng)方案下的最大后悔值圖1.17后悔值準(zhǔn)則下的最有決策7、使用等概率準(zhǔn)則求解在等概率準(zhǔn)則的操作中，由于假定了每種

13、自然狀態(tài)出現(xiàn)的概率是相等的，所以求每種行 動(dòng)方案的期望收益值的過程實(shí)際上就是求其收益值算數(shù)平均數(shù)的過程。我們可以利用Excel 中的函數(shù)“Average”來實(shí)現(xiàn)這一操作，具體操作步驟如下：（1）在Excel的單元格中輸入數(shù)據(jù)。（2）在G列求各個(gè)行動(dòng)方案下的期望損益值，將G3命名為Average。選擇單元格G4,在“插入”菜單下選擇“函數(shù)”對(duì)話框，彈出“插入函數(shù)”對(duì)話框， 找到函數(shù)“Average”（平均值），單擊確定，彈出“函數(shù)參數(shù)”對(duì)話框，如圖1.18所示。（3）在參數(shù)欄“Number1 ”里輸入a1方案下的各個(gè)收益值所在位置“B4:F4”，單擊 確定，即在G4單元格得到a1方案的期望收益值

14、。拖動(dòng)填充手柄至G8，得到各個(gè)行動(dòng)方案 的期望收益值，如圖1.19所示。（4）在第9行求出具有最大期望損益值的行動(dòng)方案，將A9命名為Max。在單元格G9選擇函數(shù)“Max”，在參數(shù)欄“Number1”里輸入的是不同行動(dòng)方案下的 各個(gè)收益值所在位置“G4:G8”，單擊“確定”，在G9單元格顯示的就是等概率準(zhǔn)則下的 最大期望收益值，如圖1.20所示。我們可以看到最大期望收益值24所對(duì)應(yīng)的方案a4為最 優(yōu)方案。同樣，可以在H4到H8列插入IF函數(shù)，如在單元格H4中插入=IF（G4=MAX （Average）, ” Maxmum”，”）”，就可以用箭頭指出哪個(gè)方案是最優(yōu)的。為文件 編輯魚）視圖 插入H）

15、格式廷）工具 數(shù)據(jù) 窗口地）幫助如禪入需要幫助的問題 - I? X11 A-IftlttilZ3EE3I B 1 ui 言言耋圉1毀1 拿蕓average x二AVERAGE（B4:F4）函數(shù)參數(shù)AVERAGENumber1Number2 匚返回其參數(shù)的算術(shù)平均值；參數(shù)可以是數(shù)值或包含數(shù)值的名稱、數(shù)組或引用Numberl:numberl, numb er 2用于計(jì)算平均值的1到30個(gè)數(shù)值參數(shù)計(jì)算結(jié)果有關(guān)該函數(shù)的幫助M圖1.20等概率決策準(zhǔn)則下的最優(yōu)決策問題討論本實(shí)驗(yàn)重點(diǎn)在于熟悉決策分析的操作環(huán)境，掌握樂觀決策準(zhǔn)則、悲觀決策準(zhǔn)則、樂觀系 數(shù)準(zhǔn)則、后悔值準(zhǔn)則和等概率準(zhǔn)則，解決不確定型決策問題?？偨Y(jié)

16、在使用實(shí)驗(yàn)環(huán)境所出現(xiàn)的 問題及解決方法。實(shí)驗(yàn)二應(yīng)用Excel求解風(fēng)險(xiǎn)型決策問題實(shí)驗(yàn)?zāi)康膶W(xué)會(huì)在Excel中使用決策樹分析法來解決風(fēng)險(xiǎn)型決策問題。實(shí)驗(yàn)內(nèi)容使用決策樹分析教材中例3.5 “生物影像公司的發(fā)展戰(zhàn)略問題”。實(shí)驗(yàn)步驟1、需要加載由麥克爾密德堂教授開發(fā)的Excel的加載宏TreePlan程序在電子表格上進(jìn) 行構(gòu)建和分析決策樹。2、運(yùn)行Excel,“工具一加載宏”彈出如圖2.1所示的對(duì)話框。單擊“瀏覽”，找到需要 的加載宏“TreePlan”，選中之后單擊“確定”，如圖2.2所示，就可以在Excel中建立自 己的決策樹了。3、加載“TreePlan”之后，重新打開Excel，點(diǎn)擊“工具一Dec

17、ision tree”，選擇工具菜 單中的“創(chuàng)建樹”，這樣就建立了一個(gè)默認(rèn)的如圖2.3所示的決策樹，它只有一個(gè)方形節(jié)點(diǎn) 和兩條樹枝。4、如果還需要其他的節(jié)點(diǎn)或樹枝，在TreePlan中對(duì)節(jié)點(diǎn)做改變是很容易的。只要選擇包 含節(jié)點(diǎn)的單元格，再從工具菜單里選擇決策樹選項(xiàng)。這樣就彈出了一個(gè)對(duì)話框，允許你改變 節(jié)點(diǎn)的類型或者添加更多的樹枝。5、要在決策樹的末尾創(chuàng)建一個(gè)節(jié)點(diǎn)，選擇包含結(jié)束節(jié)點(diǎn)的單元格，在選擇工具菜單里的決 策樹選項(xiàng)。這樣就可以將結(jié)束節(jié)點(diǎn)轉(zhuǎn)化含有所希望的樹枝數(shù)目（15條）的決策節(jié)點(diǎn)或事 件節(jié)點(diǎn)。如圖2.4所示。6、最初，每一條樹枝都會(huì)有一個(gè)自動(dòng)產(chǎn)生的默認(rèn)值為0的凈現(xiàn)金流量（在樹枝標(biāo)簽下面出 現(xiàn)的數(shù)字），同樣，兩條樹枝的任何一條都伸向事件節(jié)點(diǎn)，顯示為事件發(fā)生的默認(rèn)的50% 概率。根據(jù)貝葉斯規(guī)則TreePlan總是能夠找到現(xiàn)有決策樹的最優(yōu)策略。問題討論總結(jié)調(diào)試程序所出現(xiàn)的錯(cuò)誤種類和相應(yīng)的解決方法。S Microsoft Excel -新建 Microsoft Excel 工作表曹 文件 編輯田）視圖但）插入）格式 工具 數(shù)據(jù) 窗口地）幫助0） 岌地：宋體!;131415161718192021222324可用加載宏:|F|Internet Assistant VBA四EreePlan Decisi