基于VBA 編程的Excel 中決策分析系統(tǒng)的流程設(shè)計(jì)及算法

何寶海

（蘇北航務(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è)管理層選擇最佳決策提供了必要的參考。

1 決策樹(shù)分析系統(tǒng)的流程設(shè)計(jì)及算法

1.1 決策樹(shù)圖形繪制的流程圖

本文設(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ù)

1.2 決策樹(shù)數(shù)值的計(jì)算

該部分的計(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ì)算流程圖

1.3 決策樹(shù)的分析

在決策樹(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]。

2 節(jié)點(diǎn)期望值的計(jì)算

本文設(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)分析所得的最佳方案。

3 決策分析系統(tǒng)的應(yīng)用實(shí)例

某零件生產(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)是有效的。

4 結(jié)論

本文基于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)域得到使用。