Excel环境下指数平滑预测法最优平滑系数的确定

蒋昌军

［摘要］指数平滑是财务预测中使用频率较高的方法，其应用的关键在于选择最优平滑系数。本文对平滑系数的确定方法进行了梳理，指出在Excel环境下进行平滑系数的确定于实际工作中更有意义，在此基础上探讨了Excel环境下运用模拟运算表和规划求解进行最优平滑系数确定的方法。

［关键词］ 指数平滑；平滑系数；Excel

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2012 . 02. 007

［中图分类号］F275［文献标识码］A［文章编号］1673 - 0194（2012）02- 0013- 03

1引言

指数平滑法（Ｅｘｐｏｎｅｎｔｉａｌ Ｓｍｏｏｔｈｉｎｇ）是较为常用的时间序列预测方法，这种预测法认为：在未来一定时期内，预测对象在数量上的演变特征不会脱离该对象过去的发展趋势，即预测对象的发展具有连续性和规律性，因此可以通过对不同时期历史数据赋予不同的权数（通常赋予近期数据较大权数，远期数据较小权数）来推测预测对象未来的发展趋势。指数平滑最早由霍尔特（Ｃ．Ｃ．Ｈｏｌｔ）于１９５７年提出，布朗（Ｂｒｏｗｎ）于１９６２年在其著作中详细论述了这一预测方法。凭借易理解、易操作、计算工作量较小等优势，指数平滑预测法在国民经济各领域得到广泛应用，财务预测中也经常使用这种方法，统计资料显示，指数平滑在预测方法中的使用频率仅次于回归分析，达到１３．１６％。

指数平滑预测法的核心在于平滑初值的确定以及平滑系数的选择。虽然平滑初值和平滑系数都对预测结果产生影响，但理论与实践证明，平滑系数是其中的瓶颈因素。这是因为指数平滑允许通过选取较大的平滑系数来削弱平滑初值对预测结果的影响，因此如何确定最优平滑系数就成为指数平滑预测的关键。国内理论工作者对指数平滑的研究有相当一部分是针对平滑系数如何确定：袁立（１９８５）探讨了分阶段平滑系数的选择，将预测分为初始阶段和一般阶段，并就各阶段分别介绍了平滑系数的确定方法；张绍和 等（１９８９）指出采用最小二乘法确定平滑系数于手工计算不实用，提出了不断用预测误差来修正预测值的季节性指数平滑预测方法；唐炎森（１９９７）探讨了传统方式下平滑系数的确定，并利用最小平方法导出了确定平滑系数的近似公式；徐大江（１９９９）指出合适的平滑系数必须根据实际问题背景及所选预测模型的特性加以选取；熊国强（２０００）对指数平滑预测模型进行了精度分析，建立了估计指数平滑系数的最优化模型。这些研究都是以手工计算为基础研讨平滑系数的确定，而讨论如何借助计算机确定平滑系数的文献却较少。叶海华 等（２００２）提出了用Ｍａｔｌａｂ实现平滑系数和求导系数的精确表达方法，但由于Ｍａｔｌａｂ软件的普及率及操作等原因，适用性并不广泛。在数据处理软件中，微软公司的Ｅｘｃｅｌ是运用最多、安装最为广泛的软件之一，绝大多数计算机使用人员都具备基本的Ｅｘｃｅｌ操作技能，因此探讨在Ｅｘｃｅｌ环境下如何进行平滑系数的选择更具有现实意义。

2最优平滑系数确定原理与思路

Ｅｘｃｅｌ数据分析中的指数平滑预测工具需要事先录入阻尼系数（阻尼系数＝１－平滑系数），并未提供平滑系数测算的参数，因此要确定最优平滑系数，仍需通过公式计算获得。因为计算机数据处理的优势，最优平滑系数确定方法的选择原则应当是使预测结果越精确越好，而不必像手工条件那样顾虑计算是否复杂。这就意味着原来手工计算条件下不实用的方法在计算机条件下仍然适用。

手工条件下平滑系数的确定通常有3种方法。第一种是利用有关方法推导出近似计算公式，这种方法适合手工计算，但推导公式计算值只能是最优平滑系数的近似值。第二种是经验判断法，这种方法又分为3种情形：（１）当时间序列呈现较稳定的水平趋势时，应选较小的平滑系数，一般可在０．０５～０．２０之间取值；（２）当时间序列有波动但长期趋势变化不大时，可选稍大的平滑系数，一般在０．１～０．４之间取值；（３）当时间序列波动很大，长期趋势变化幅度较大且呈现明显而迅速的上升或下降趋势时，宜选择较大的平滑系数，一般在０．６～１之间选值。显然这种方法需要结合经验判断，主观性太强，但可以在Ｅｘｃｅｌ数据预测中作为平滑系数选择范围的参考。第三种方法是误差试算法，即最优平滑系数的选择一般以预测误差作为判断标准，使预测误差达到最小的平滑系数就是最优平滑系数。这种方法最为科学合理，而且借助Ｅｘｃｅｌ中的模拟运算表或数组公式可以方便快捷地计算不同系数情况下的预测值及预测标准误差。使用这种方法需要大致确定一个范围及平滑系数的精度（可通过经验判断法），再结合计算机进行不同系数情况下的计算，根据计算结果比较预测标准误差，选取标准误差最小的系数作为指数平滑的最优系数。

3基于Ｅｘｃｅｌ的最优平滑系数确定模型

本文以某公司销售额预测为例来说明在误差试算法下如何借助Ｅｘｃｅｌ进行最优平滑系数的选择。该公司有关销售额历史观测值见表１。

由于需要进行若干次平滑系数测算，而Ｅｘｃｅｌ指数平滑预测工具不能使用模拟运算表，所以模型中的预测值将直接通过指数平滑公式计算。相关操作步骤如下：

（１）输入已知数据并做好模型布局（包含平滑系数可能的取值），在Ｇ２单元格添加一个窗体微调项控件，该控件用于设定平滑系数的变动范围。微调项控件的控制选项参数设置为：最小值１，最大值３０（假定平滑系数取值范围为０．０１~０．３，不同预测条件下可根据情况设定。理论上平滑系数的取值在０~１之间可以有无数个，但通常对平滑系数的取值精度不会超过0.001，因此事实上在Ｅｘｃｅｌ环境下采用不超过一定取值精度的穷举法设置平滑系数范围也是可行的），步长为１，单元格链接为Ｈ２（因为控件参数的最小值最大值不允许为小数，而平滑系数只能是０~１之间的小数，需通过Ｈ２单元进行数据转换才能作为平滑系数使用）。

（２）在Ｇ２单元格输入公式“＝Ｈ２／１００”，完成从Ｈ２单元到Ｇ２单元的平滑系数小数转换，并建立起平滑系数与微调项之间的数据关联。

（３）在Ｃ４单元格输入公式“＝Ｂ４”，作为平滑初值。

（５）在Ｇ７单元格输入ＭＳＥ计算公式“＝ＳＵＭＸＭＹ２（Ｂ５：Ｂ１９，Ｃ５：Ｃ１９）／ＣＯＵＮＴ（Ｂ５：Ｂ１９）”，如果不使用ＳＵＭＸＭＹ２函数，也可分步骤计算均方误差，但稍显麻烦，工作量也增加许多。

（６）这一步是最优平滑系数确定的关键，需要在平滑系数可能的取值范围内借助Ｅｘｃｅｌ分别对平滑系数进行均方误差的模拟运算。方法是选择Ｆ７：Ｇ３７单元区域，点击“模拟运算表”，在模拟运算表“输入引用列的单元格”参数中输入“Ｇ２”，完成所有平滑系数变动范围内的均方误差计算。如果此步骤不用模拟运算表，也可通过直接输入数组公式计算。

（７）在Ｇ３单元格输入“＝ＭＩＮ（Ｇ８：Ｇ３７）”，通过函数查找均方误差的最小值，此时计算机显示最小ＭＳＥ为３．９１２ ６。

（８）在Ｇ４单元格输入“＝ＩＮＤＥＸ（Ｆ８：Ｆ３７，ＭＡＴＣＨ（Ｇ３，Ｇ８：Ｇ３７，０））”，该公式表示借助最小ＭＳＥ的相对位置在平滑系数所有可能取值中取得对应位置的最优值，至此，最优平滑系数已经找到：０．１３。整个模型的公式及最终计算结果分别见图１、图２。

除了以上介绍的利用模拟运算表确定最优平滑系数方法外，利用Ｅｘｃｅｌ的规划求解也能较快查找到平滑系数，而且与模拟运算表方法相比，规划求解并不需要将平滑系数的可能取值列出，它通过多次迭代计算自动返回最优平滑系数，并且返回的平滑系数精度更高。

该种方式下操作步骤（１）至（５）与前面使用模拟计算表的步骤（1）至（5）相同。在第（６）步，点击“工具”—“规划求解”菜单输入规划求解参数，其中“目标单元格”输入“Ｇ７”，“等于”选择“最小值”，“可变单元格”选择“Ｇ２”，“约束条件”添加“Ｇ２＜＝１，Ｇ２＞＝０”，点击“求解”后系统自动进行计算，并在Ｇ２单元格返回最优平滑系数：０．１３４，这与采用前述方法得到的结果一致，见图３。

4结语

从以上计算结果来看，只要将指数平滑的有关理论应用到Ｅｘｃｅｌ中并设定好相关运算参数的计算公式，Ｅｘｃｅｌ就能轻松地根据要求求解出结果，不但运算速度快，而且运算结果准确、精度也高。ＩＴ技术的进步以及预测的复杂性要求进一步挖掘Ｅｘｃｅｌ的应用功能，本文介绍的操作方法只是抛砖引玉，如何充分利用Ｅｘｃｅｌ建立方便快捷的财务预测解决方案还有待进一步探讨。

主要参考文献

［１］袁立.指数平滑常数的特性及其选择［Ｊ］.预测，１９８５（z１）:１３７－１４２.

［２］叶海华，张录达，吉海彦，齐小明.利用ＭＡＴＬＡＢ实现平滑系数的精确表达［Ｊ］.北京农学院学报，２００２（３）：４６－５０.

［３］金旭星，盛奎川.指数平滑参数与初值的选取研究［Ｊ］.江南大学学报：自然科学版，２００５（３）：３１６－３１９.

［４］徐大江.预测模型参数的指数平滑估计法及其应用的进一步研究［Ｊ］.系统工程理论与实践，１９９９（２）：２５－３０，４３.

［５］唐炎森.确定平滑系数的新方法［Ｊ］.统计与信息论坛，１９９７（３）：１５－１７，２１.

［６］黎锁平，刘坤会.平滑系数自适应的二次指数平滑模型及其应用［Ｊ］.系统工程理论与实践，２００４（２）：９５－９９.