何寶海
(蘇北航務(wù)管理處淮安航務(wù)中心,江蘇 淮安223002)
VBA 是一款帶有圖形界面的強(qiáng)大開(kāi)發(fā)工具,除了自帶的函數(shù)功能和數(shù)據(jù)分析處理功能外,還提供了豐富的圖表制作功能,是軟件開(kāi)發(fā)領(lǐng)域常用的工具之一。將VBA語(yǔ)言和Excel 相結(jié)合,在電子數(shù)據(jù)表中建立決策樹(shù),可以使用戶直觀地認(rèn)識(shí)都哪一項(xiàng)決策最優(yōu),哪一項(xiàng)決策存在風(fēng)險(xiǎn)。同時(shí),用戶只需要點(diǎn)選相應(yīng)的單元格,既可以輕松地創(chuàng)建圖表,并基于決策分析模型展開(kāi)計(jì)算,為風(fēng)險(xiǎn)決策分析提供了技術(shù)支持。本文基于VBA 編程,在Office Excel 中建立了決策分析系統(tǒng),并將該系統(tǒng)應(yīng)用于某企業(yè)的一項(xiàng)投資決策中,通過(guò)定量分析的方式計(jì)算出不同生產(chǎn)方案的期望損益值,為企業(yè)管理層選擇最佳決策提供了必要的參考。
本文設(shè)計(jì)的決策分析系統(tǒng),按照“從左至右”的順序生成節(jié)點(diǎn)與分枝,得到相應(yīng)的決策樹(shù)。將決策樹(shù)最左側(cè)的節(jié)點(diǎn)作為根節(jié)點(diǎn),用戶可通過(guò)系統(tǒng)界面左上角工具欄中的“開(kāi)始樹(shù)”選項(xiàng),在工作表任意位置繪制根節(jié)點(diǎn)。系統(tǒng)將根節(jié)點(diǎn)的類型默認(rèn)為“結(jié)束節(jié)點(diǎn)”。雙擊根節(jié)點(diǎn)左側(cè)標(biāo)簽,可快速調(diào)出“UserForm1”對(duì)話框,用于設(shè)置決策樹(shù)的名稱、效用函數(shù)、結(jié)果計(jì)算方式等相關(guān)參數(shù)。雙擊根節(jié)點(diǎn),會(huì)彈出“UserForm2”對(duì)話框,用于設(shè)置節(jié)點(diǎn)名稱、分枝數(shù)目、節(jié)點(diǎn)類型等。設(shè)置完畢后,點(diǎn)擊確定即可生成相應(yīng)的決策樹(shù)[1]。決策樹(shù)圖形的繪制流程見(jiàn)圖1 所示。
圖1 決策樹(shù)圖形繪制的流程圖
對(duì)于系統(tǒng)生成的決策樹(shù),用戶可根據(jù)需要改變其節(jié)點(diǎn)類型,例如將決策節(jié)點(diǎn)更改為機(jī)會(huì)節(jié)點(diǎn),或者刪除節(jié)點(diǎn)。這里以三級(jí)決策樹(shù)為例,添加“Tree”后,生成一個(gè)對(duì)應(yīng)的決策節(jié)點(diǎn)。并由決策節(jié)點(diǎn)引出“方案枝”,從決策節(jié)點(diǎn)引出的每一條直線均代表一種備選方案。在方案枝上,用“True”或“False”表示該方案是否可行。從狀態(tài)節(jié)點(diǎn)引出“概率枝”,表示此種狀態(tài)出現(xiàn)的概率,可以由系統(tǒng)根據(jù)歷史數(shù)據(jù)自動(dòng)得出,也可由用戶自定義,見(jiàn)圖2。
圖2 系統(tǒng)生成的決策樹(shù)
該部分的計(jì)算對(duì)象主要包括決策節(jié)點(diǎn)、狀態(tài)節(jié)點(diǎn)的期望值,結(jié)束節(jié)點(diǎn)的損益值與概率,以及用戶改變決策樹(shù)后系統(tǒng)重新計(jì)算各個(gè)節(jié)點(diǎn)的值。由于VBA 的局限性(無(wú)指針結(jié)構(gòu)),無(wú)法在決策樹(shù)上直接計(jì)算出上述各項(xiàng)數(shù)據(jù),因此在系統(tǒng)設(shè)計(jì)中引入了“邏輯樹(shù)”的概念,通過(guò)在邏輯樹(shù)、用戶、決策樹(shù)之間建立映射關(guān)系,使用戶的所有操作都能反映到邏輯樹(shù)上,從而十分方便地計(jì)算出結(jié)果[2]。邏輯樹(shù)、用戶與決策樹(shù)之間的對(duì)應(yīng)關(guān)系見(jiàn)圖3。
圖3 用戶、決策樹(shù)和邏輯樹(shù)之間的關(guān)系
邏輯樹(shù)作為存儲(chǔ)決策樹(shù)信息的數(shù)組,數(shù)組中每個(gè)元素均采用結(jié)構(gòu)體形式存在。以定義決策樹(shù)分枝的結(jié)構(gòu)體為例,表示方式如下:
這里以計(jì)算決策樹(shù)上各個(gè)節(jié)點(diǎn)的期望值為例,其計(jì)算方式見(jiàn)圖4。
圖4 決策樹(shù)數(shù)值計(jì)算流程圖
在決策樹(shù)模型建成后,系統(tǒng)可基于用戶需求以剪枝圖、散點(diǎn)圖、統(tǒng)計(jì)報(bào)表等形式顯示計(jì)算和分析結(jié)果。在設(shè)計(jì)決策樹(shù)的分析部分時(shí),可通過(guò)系統(tǒng)界面上方工具欄選擇“結(jié)果顯示”選項(xiàng),在彈出的對(duì)話框中勾選結(jié)果的顯示類型,如散點(diǎn)圖、柱狀圖、統(tǒng)計(jì)報(bào)表;除此之外,還可以選擇結(jié)果的存儲(chǔ)位置,如新的工作簿、當(dāng)前工作簿。以“剪枝圖”為例,根據(jù)分析結(jié)果可生成對(duì)應(yīng)的剪枝圖,用戶可一目了然地找出最佳決策方案。在決策分析模塊的設(shè)計(jì)中,設(shè)計(jì)了一個(gè)新的數(shù)組“Cutarray”,可以將邏輯樹(shù)中生成的內(nèi)容存儲(chǔ)到該數(shù)組中[3]。仍然以“剪枝圖”為例,在決策分析系統(tǒng)生成剪枝圖以后,會(huì)將分析結(jié)果存儲(chǔ)到Cutarray 數(shù)組中?;跊Q策分析系統(tǒng)的分析流程如下:
(1) 建立新的數(shù)組Cutarray 后,將邏輯數(shù)組中的所有數(shù)據(jù)復(fù)制到該數(shù)組中,同時(shí)執(zhí)行一個(gè)判斷程序“是否添加新的工作簿?”。
(2) 如果判斷為“是”,則添加新的工作簿,然后在新的工作簿中繼續(xù)執(zhí)行下一步操作;如果判斷為“否”,則直接在當(dāng)前的工作簿執(zhí)行下一步操作。
(3) 在工作簿中執(zhí)行一個(gè)判斷程序“是否繪制剪枝圖?”,如果判斷結(jié)果為“是”,則在工作簿中繪制剪枝圖;如果判斷結(jié)果為“否”,在繼續(xù)執(zhí)行下一個(gè)判斷程序“是否繪制報(bào)表?”,按照同樣的方式,依次判斷是否繪制柱狀圖、散點(diǎn)圖。如果判斷為“是”,則繪制相應(yīng)類型的圖像。
(4) 所有判斷程序執(zhí)行完畢后,以柱狀圖、剪枝圖等形式呈現(xiàn)決策分析結(jié)果,結(jié)束當(dāng)前分析程序[4]。
本文設(shè)計(jì)的決策分析系統(tǒng),最終的計(jì)算結(jié)果是基于決策樹(shù)上所有節(jié)點(diǎn)的期望值求得的。結(jié)合決策樹(shù)期望值的運(yùn)算規(guī)則,以及邏輯樹(shù)的存儲(chǔ)特征,對(duì)節(jié)點(diǎn)期望值和概率值的算法作如下設(shè)計(jì):對(duì)于決策樹(shù)中的結(jié)束節(jié)點(diǎn),其downdata 是該分枝上所有節(jié)點(diǎn)投入值或收入值的累加;而狀態(tài)節(jié)點(diǎn)的downdata,即當(dāng)前節(jié)點(diǎn)的期望值,可通過(guò)下一節(jié)點(diǎn)的downdata 與概率值相乘得到。由此可知,決策節(jié)點(diǎn)的downdata 是決策樹(shù)中所有節(jié)點(diǎn)downdata 中的最大值。同時(shí),選擇每個(gè)節(jié)點(diǎn)的數(shù)據(jù)項(xiàng)jdrate,記錄該節(jié)點(diǎn)的層次,各個(gè)節(jié)點(diǎn)的層次都可以通過(guò)上一節(jié)點(diǎn)“層次+1”得到。將決策樹(shù)中節(jié)點(diǎn)的最大層次記錄在變量jbmaxrate 中,作為決策樹(shù)的層次。根據(jù)該算法,決策樹(shù)首先根據(jù)最右端節(jié)點(diǎn)的損益值和概率值,求得當(dāng)前分枝的總概率。將總概率乘以所有節(jié)點(diǎn)的損益值之和,即可得到該方案下的期望損益值。按照同樣的方法,計(jì)算出決策樹(shù)上所有方案的期望損益值,然后對(duì)比大小,期望收益值最大或期望損失值最小的方案,即為決策分析系統(tǒng)分析所得的最佳方案。
某零件生產(chǎn)廠現(xiàn)有2 種生產(chǎn)方案,方案一是沿用原來(lái)的生產(chǎn)線繼續(xù)生產(chǎn)手動(dòng)型老產(chǎn)品;方案二是改進(jìn)生產(chǎn)線生產(chǎn)全自動(dòng)新產(chǎn)品。通過(guò)市場(chǎng)需求分析,在消費(fèi)需求較大的賣方市場(chǎng)下,生產(chǎn)老產(chǎn)品每月可實(shí)現(xiàn)盈利30 萬(wàn)元,而生產(chǎn)新產(chǎn)品每月可實(shí)現(xiàn)盈利50 萬(wàn)元;在消費(fèi)需求較小的買方市場(chǎng)下,生產(chǎn)老產(chǎn)品每月可實(shí)現(xiàn)盈利10 萬(wàn)元,而生產(chǎn)新產(chǎn)品每月虧損5 萬(wàn)元。根據(jù)市場(chǎng)調(diào)研結(jié)果,市場(chǎng)需求量較大的概率為0.8,需求量小的概率為0.2?;谏鲜鲂畔?,分析哪種方案能夠使該企業(yè)獲得更多利潤(rùn)。采用常規(guī)的單級(jí)決策方法,分析流程如下:
(1) 畫(huà)出決策樹(shù),見(jiàn)圖5。
圖5 一級(jí)決策圖
(2) 求出各節(jié)點(diǎn)的期望損益值,按照從右至左的順序以此計(jì)算。節(jié)點(diǎn)2:30×0.8+10×0.2=26(萬(wàn)元);節(jié)點(diǎn)3:50×0.8+(-5)×0.2=39(萬(wàn)元);則決策節(jié)點(diǎn)1的期望損益之為Max{26,39}=39(萬(wàn)元)。
(3) 剪枝。已知決策節(jié)點(diǎn)1 的期望損益值是39萬(wàn)元,為了達(dá)到新產(chǎn)品方案的期望損益值,需要剪掉老產(chǎn)品生產(chǎn)線這一分枝[5]。故選擇新產(chǎn)品生產(chǎn)方案可以實(shí)現(xiàn)企業(yè)利潤(rùn)最大化。按照本文設(shè)計(jì)的決策分析系統(tǒng)對(duì)上述案例展開(kāi)分析,建立的決策樹(shù)模型見(jiàn)圖6。
圖6 基于決策分析系統(tǒng)建立的決策樹(shù)圖
對(duì)比來(lái)看,在Excel 中建立的決策分析系統(tǒng),與常規(guī)的單級(jí)決策系統(tǒng)在決策分析結(jié)果上是一致的,均認(rèn)為生產(chǎn)新產(chǎn)品可以為企業(yè)創(chuàng)造更大的利潤(rùn)。由此可見(jiàn),本文設(shè)計(jì)的決策分析系統(tǒng)是有效的。
本文基于VBA 編程開(kāi)發(fā)的決策分析系統(tǒng),能夠與電子數(shù)據(jù)表Office Excel 相結(jié)合,具有直觀易學(xué)、操作簡(jiǎn)便等特點(diǎn)。該系統(tǒng)的核心部分為決策樹(shù),但是考慮到VBA 語(yǔ)言無(wú)指針類型的局限性,在設(shè)計(jì)時(shí)引入了邏輯樹(shù),并利用Cutarray 數(shù)組、Sensitarray 數(shù)組來(lái)存儲(chǔ)邏輯樹(shù)的數(shù)組,建立了“決策樹(shù)——用戶——邏輯樹(shù)”的關(guān)系,讓決策樹(shù)的計(jì)算結(jié)果以更加直觀、簡(jiǎn)明的形式呈現(xiàn)出來(lái),方便用戶選擇最佳方案。從實(shí)例應(yīng)用效果來(lái)看,本文設(shè)計(jì)的決策分析系統(tǒng)與傳統(tǒng)的單級(jí)決策在最佳方案的選擇結(jié)果上具有一致性,說(shuō)明該系統(tǒng)可用。下一步,還要繼續(xù)研究多級(jí)決策模式下決策分析系統(tǒng)的應(yīng)用方式,以便于該系統(tǒng)在更廣泛的領(lǐng)域得到使用。