基于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 数组来存储逻辑树的数组，建立了“决策树——用户——逻辑树”的关系，让决策树的计算结果以更加直观、简明的形式呈现出来，方便用户选择最佳方案。从实例应用效果来看，本文设计的决策分析系统与传统的单级决策在最佳方案的选择结果上具有一致性，说明该系统可用。下一步，还要继续研究多级决策模式下决策分析系统的应用方式，以便于该系统在更广泛的领域得到使用。