基于VBA 編程的Excel 中決策分析系統的流程設計及算法

何寶海

（蘇北航務管理處淮安航務中心，江蘇 淮安223002）

VBA 是一款帶有圖形界面的強大開發工具，除了自帶的函數功能和數據分析處理功能外，還提供了豐富的圖表制作功能，是軟件開發領域常用的工具之一。將VBA語言和Excel 相結合，在電子數據表中建立決策樹，可以使用戶直觀地認識都哪一項決策最優，哪一項決策存在風險。同時，用戶只需要點選相應的單元格，既可以輕松地創建圖表，并基于決策分析模型展開計算，為風險決策分析提供了技術支持。本文基于VBA 編程，在Office Excel 中建立了決策分析系統，并將該系統應用于某企業的一項投資決策中，通過定量分析的方式計算出不同生產方案的期望損益值，為企業管理層選擇最佳決策提供了必要的參考。

1 決策樹分析系統的流程設計及算法

1.1 決策樹圖形繪制的流程圖

本文設計的決策分析系統，按照“從左至右”的順序生成節點與分枝，得到相應的決策樹。將決策樹最左側的節點作為根節點，用戶可通過系統界面左上角工具欄中的“開始樹”選項，在工作表任意位置繪制根節點。系統將根節點的類型默認為“結束節點”。雙擊根節點左側標簽，可快速調出“UserForm1”對話框，用于設置決策樹的名稱、效用函數、結果計算方式等相關參數。雙擊根節點，會彈出“UserForm2”對話框，用于設置節點名稱、分枝數目、節點類型等。設置完畢后，點擊確定即可生成相應的決策樹[1]。決策樹圖形的繪制流程見圖1 所示。

圖1 決策樹圖形繪制的流程圖

對于系統生成的決策樹，用戶可根據需要改變其節點類型，例如將決策節點更改為機會節點，或者刪除節點。這里以三級決策樹為例，添加“Tree”后，生成一個對應的決策節點。并由決策節點引出“方案枝”，從決策節點引出的每一條直線均代表一種備選方案。在方案枝上，用“True”或“False”表示該方案是否可行。從狀態節點引出“概率枝”，表示此種狀態出現的概率，可以由系統根據歷史數據自動得出，也可由用戶自定義，見圖2。

圖2 系統生成的決策樹

1.2 決策樹數值的計算

該部分的計算對象主要包括決策節點、狀態節點的期望值，結束節點的損益值與概率，以及用戶改變決策樹后系統重新計算各個節點的值。由于VBA 的局限性（無指針結構），無法在決策樹上直接計算出上述各項數據，因此在系統設計中引入了“邏輯樹”的概念，通過在邏輯樹、用戶、決策樹之間建立映射關系，使用戶的所有操作都能反映到邏輯樹上，從而十分方便地計算出結果[2]。邏輯樹、用戶與決策樹之間的對應關系見圖3。

圖3 用戶、決策樹和邏輯樹之間的關系

邏輯樹作為存儲決策樹信息的數組，數組中每個元素均采用結構體形式存在。以定義決策樹分枝的結構體為例，表示方式如下：

這里以計算決策樹上各個節點的期望值為例，其計算方式見圖4。

圖4 決策樹數值計算流程圖

1.3 決策樹的分析

在決策樹模型建成后，系統可基于用戶需求以剪枝圖、散點圖、統計報表等形式顯示計算和分析結果。在設計決策樹的分析部分時，可通過系統界面上方工具欄選擇“結果顯示”選項，在彈出的對話框中勾選結果的顯示類型，如散點圖、柱狀圖、統計報表；除此之外，還可以選擇結果的存儲位置，如新的工作簿、當前工作簿。以“剪枝圖”為例，根據分析結果可生成對應的剪枝圖，用戶可一目了然地找出最佳決策方案。在決策分析模塊的設計中，設計了一個新的數組“Cutarray”，可以將邏輯樹中生成的內容存儲到該數組中[3]。仍然以“剪枝圖”為例，在決策分析系統生成剪枝圖以后，會將分析結果存儲到Cutarray 數組中。基于決策分析系統的分析流程如下：

（1） 建立新的數組Cutarray 后，將邏輯數組中的所有數據復制到該數組中，同時執行一個判斷程序“是否添加新的工作簿？”。

（2） 如果判斷為“是”，則添加新的工作簿，然后在新的工作簿中繼續執行下一步操作；如果判斷為“否”，則直接在當前的工作簿執行下一步操作。

（3） 在工作簿中執行一個判斷程序“是否繪制剪枝圖？”，如果判斷結果為“是”，則在工作簿中繪制剪枝圖；如果判斷結果為“否”，在繼續執行下一個判斷程序“是否繪制報表？”，按照同樣的方式，依次判斷是否繪制柱狀圖、散點圖。如果判斷為“是”，則繪制相應類型的圖像。

（4） 所有判斷程序執行完畢后，以柱狀圖、剪枝圖等形式呈現決策分析結果，結束當前分析程序[4]。

2 節點期望值的計算

本文設計的決策分析系統，最終的計算結果是基于決策樹上所有節點的期望值求得的。結合決策樹期望值的運算規則，以及邏輯樹的存儲特征，對節點期望值和概率值的算法作如下設計：對于決策樹中的結束節點，其downdata 是該分枝上所有節點投入值或收入值的累加；而狀態節點的downdata，即當前節點的期望值，可通過下一節點的downdata 與概率值相乘得到。由此可知，決策節點的downdata 是決策樹中所有節點downdata 中的最大值。同時，選擇每個節點的數據項jdrate，記錄該節點的層次，各個節點的層次都可以通過上一節點“層次+1”得到。將決策樹中節點的最大層次記錄在變量jbmaxrate 中，作為決策樹的層次。根據該算法，決策樹首先根據最右端節點的損益值和概率值，求得當前分枝的總概率。將總概率乘以所有節點的損益值之和，即可得到該方案下的期望損益值。按照同樣的方法，計算出決策樹上所有方案的期望損益值，然后對比大小，期望收益值最大或期望損失值最小的方案，即為決策分析系統分析所得的最佳方案。

3 決策分析系統的應用實例

某零件生產廠現有2 種生產方案，方案一是沿用原來的生產線繼續生產手動型老產品；方案二是改進生產線生產全自動新產品。通過市場需求分析，在消費需求較大的賣方市場下，生產老產品每月可實現盈利30 萬元，而生產新產品每月可實現盈利50 萬元；在消費需求較小的買方市場下，生產老產品每月可實現盈利10 萬元，而生產新產品每月虧損5 萬元。根據市場調研結果，市場需求量較大的概率為0.8，需求量小的概率為0.2。基于上述信息，分析哪種方案能夠使該企業獲得更多利潤。采用常規的單級決策方法，分析流程如下：

（1） 畫出決策樹，見圖5。

圖5 一級決策圖

（2） 求出各節點的期望損益值，按照從右至左的順序以此計算。節點2:30×0.8+10×0.2=26（萬元）；節點3:50×0.8+（-5）×0.2=39（萬元）；則決策節點1的期望損益之為Max{26，39}=39（萬元）。

（3） 剪枝。已知決策節點1 的期望損益值是39萬元，為了達到新產品方案的期望損益值，需要剪掉老產品生產線這一分枝[5]。故選擇新產品生產方案可以實現企業利潤最大化。按照本文設計的決策分析系統對上述案例展開分析，建立的決策樹模型見圖6。

圖6 基于決策分析系統建立的決策樹圖

對比來看，在Excel 中建立的決策分析系統，與常規的單級決策系統在決策分析結果上是一致的，均認為生產新產品可以為企業創造更大的利潤。由此可見，本文設計的決策分析系統是有效的。

4 結論

本文基于VBA 編程開發的決策分析系統，能夠與電子數據表Office Excel 相結合，具有直觀易學、操作簡便等特點。該系統的核心部分為決策樹，但是考慮到VBA 語言無指針類型的局限性，在設計時引入了邏輯樹，并利用Cutarray 數組、Sensitarray 數組來存儲邏輯樹的數組，建立了“決策樹——用戶——邏輯樹”的關系，讓決策樹的計算結果以更加直觀、簡明的形式呈現出來，方便用戶選擇最佳方案。從實例應用效果來看，本文設計的決策分析系統與傳統的單級決策在最佳方案的選擇結果上具有一致性，說明該系統可用。下一步，還要繼續研究多級決策模式下決策分析系統的應用方式，以便于該系統在更廣泛的領域得到使用。