利用Excel进行指数平滑分析与预测

利用Excel 进行指数平滑分析与预测（1）

【例】以连续10年的灌溉面积为例说明。这个例子并不典型，采用此例仅在说明指数平滑的操作过程。将我的计算过程在Excel 上重复一遍，就会掌握指数平滑法的基本要领；然后利用SPSS 练习几遍，就能学会实用技巧。

第一步，录入数据，设置参数（图1）。

录入数据以后，开始设置参数：

⒈ 设置平滑系数：在一个自己感到方便的位置如C2单元格设定一个参数作为指数平滑系数α，由于α介于0～1之间，不妨从0开始，即首先取α=0。

⒉ 设置迭代计算的初始值S 0’。初始值有多种取法，一般取S 0’=x 1，对于本例，自然是取S 0’=28.6，写于D2单元格，与1971年对应（图1）。

图1 原始数据与参数设置

第二步，指数平滑计算。

按照下式进行

1)1(-'-+='t t t S x S αα

显然当t =1时，我们有

20

11)1(y S x S ='-+='αα 根据公式在D3单元格中输入公式“=$C$2*B2+(1-$C$2)*D2”（图2），回车，得到28.6；然

后用鼠标抓住D3单元格的右下角，下拉（图3），即可得到α=0时的全部数值，其中对应于1981年的数据便是预测值（图4），当然，此时，它们全部都是28.6，即数据被极度修匀。

第三步，复制并保存数据。

将α=0时的计算结果复制到旁边，其中最后一个数据即1981年的预测值可以不必复制；最好在结果的上面注明对应的平滑系数，以便后来识别（图5）。

第四步，计算全部结果。

在C2单元格中，将0改为0.1，立即得到α=0.1时的平滑结果，复制并保存（图6）；重复以上操作，直到得到α在0～1之间的全部数值（图7）。

第五步，均方差(MSE)检验。

首先计算误差平方和（SSE ），公式为

∑∑=-=-'=-=n

t t t n t t t x S x y 2

211

2

)()(SSE

注意这里是S t -1’对应x t ！例如在H2单元格中输入公式“=(B2-G2)^2”，回车，即可得到(x 1-y 1)2的数值（图8）；下拉，得到1971－1980年间的全部结果：

0 86.49 141.61 49 412.09 268.96 0.36 30.25 327.61 77.44

求和，即可得到SSE=1393.8（图9）。

根据下式

∑=--'-==n

t t t x S n n 2

21)(111-SSE MSE 容易算出均方差。根据SSE 或MSE 最小原则取α=0.3（图11，图12），此时预测值为

y 11=y (1981)=38.5。

图2 指数平滑计算示意图

图3 计算的第一步

图4 平滑系数为0时的计算结果图5 复制保存的数据

图6 平滑系数为0.1时的计算结果

图7 全部计算结果

图8 计算误差平方和示意图

图9 平滑系数为0时的误差平方和（SSE）

图10 误差平方和（SSE ）和均方差（MSE ）

SSE

800

9001000110012001300140015000

0.2

0.4

0.6

0.8

1

SSE

图11 SSE 随平滑系数变化的曲线

MSE

100

1101201301401501600

0.2

0.4

0.6

0.8

1

MSE

图12 MSE 随平滑系数变化的曲线

第六步，绘制指数平滑曲线。

将α=0.3时的平滑结果与原数据按顺序排列（图13），然后利用Excel 的绘图功能不难绘制指数平滑曲线图——将原始数据曲线与指数平滑曲线画在统一坐标系，便于比较指数平滑的效果：两条曲线越吻合，表明指数平滑的效果越好，从而预测也就越可靠。从图14可以看出，对于本例而言，指数平滑的效果并不见佳（图14）。

图13 将指数平滑结果与原始数据按顺序排列

101520253035404550551971

1973

1975

1977

1979

灌溉面积(千亩)一次平滑

图14 一次指数平滑曲线图（与原始数据比较）

第七步，二次指数平滑。

二次指数平滑是在一次指数平滑的基础上进行的，其计算过程和检验方法与对原始数据进行指数平滑的步骤完全一样。但是，有一点无需注意：在我们的指数平滑模型中，我们取

t t S y '=+1 而y t 作为计算值对应的是x t ，故x t 实际上对应的是S t -1’。若以1971年为第t =1个时点，则1972年才对应S 1’（1971年对应于S 0’）。二次指数平滑的公式为

1)1(-''-+'=''t t t S S S αα

显然，我们的计算的起点是从S 1’开始的，亦即从1972年开始的，否则会有t -1=-1的现象，而我们的时间序号不取负值。

根据习惯方法，取S 0’’=S 1’=x 1=28.6，平滑系数不妨仍然从0开始，以C3单元格表示新的平滑系数α＝0所在，在E3单元格输入28.6表示S 0’’，在E4单元格中建立公式“= $C$3*D3+ (1-$C$3)*E3”（参见图15。注意这里是D3表示S 1’，E3表示S 0’’，后面的三次指数平滑要考虑这个问题），回车，得到S 1’’ =28.6；下拉至1982年，给出α＝0时全部的二次平滑结果；1971年对应的年份空着，取28.6。复制，保存（可以只保存1971－1980年间的结果）。

图15 二次指数平滑的初始值的计算（α＝0）

图16 平滑系数α＝0时的全部二次指数平滑结果

在C3单元格中改变α值，以0.1为步长，分别取α＝0.1、α＝0.2、…、α＝1，给出、复制、保存基于不同平滑系数的计算结果，最后比较发现，当α＝1时，误差平方和SSE 从均方差MSE 最小，此时SSE=82.592，MSE=82.592/(10-1)=9.177（图17）。

我们知道，当α＝1时，原始数据实际上未经过任何修匀，但由于计算起点相对于一次指数平滑下移一步，故本例所谓的二次指数平滑实际上是对一次指数平滑的结果进行一次平移，