《利用Excel进行指数平滑分析报告与预测》由会员分享，可在线阅读，更多相关《利用Excel进行指数平滑分析报告与预测（12页珍藏版）》请在装配图网上搜索。
1、标准实用利用 Excel 进行指数平滑分析与预测（1 ）【例】以连续10 年的灌溉面积为例说明。这个例子并不典型，采用此例仅在说明指数平滑的操作过程。将我的计算过程在 Excel 上重复一遍，就会掌握指数平滑法的基本要领；然后利用 SPSS 练习几遍，就能学会实用技巧。第一步，录入数据，设置参数（图 1）。录入数据以后，开始设置参数： 设置平滑系数：在一个自己感到方便的位置如C2 单元格设定一个参数作为指数平滑系数，由于 介于 0 1 之间，不妨从0 开始，即首先取=0 。 设置迭代计算的初始值S0 。初始值有多种取法，一般取S0= x1，对于本例，自然是取 S0=28.6 ，写于 D2 单元
2、格，与1971 年对应（ 图 1 ）。图 1原始数据与参数设置第二步，指数平滑计算。按照下式进行Stxt(1) St1显然当 t=1时，我们有S1x1(1)S0y2根据公式在D3 单元格中输入公式“=$C$2*B2+(1-$C$2)*D2”（ 图2 ），回车，得到28.6 ；然后用鼠标抓住D3 单元格的右下角，下拉（图 3 ），即可得到 =0时的全部数值，其中对应于 1981 年的数据便是预测值（图 4 ），当然，此时，它们全部都是28.6 ，即数据被极度修匀。文案大全标准实用第三步，复制并保存数据。将时的计算结果复制到旁边，其中最后一个数据即1981 年的预测值可以不必复制；=0最好在结果的
3、上面注明对应的平滑系数，以便后来识别（图 5）。第四步，计算全部结果。在 C2 单元格中，将0 改为 0.1 ，立即得到 =0.1时的平滑结果，复制并保存（图 6 ）；重复以上操作，直到得到在0 1 之间的全部数值（图 7）。第五步，均方差 (MSE) 检验。首先计算误差平方和（ SSE），公式为nnSSE( yt xt ) 2(St 1 xt ) 2t 1t 2注意这里是 St -1 对应 xt！例如在H2 单元格中输入公式“ =(B2-G2)2 ”，回车，即可得到(x1 - y1) 2 的数值（ 图 8 ）；下拉，得到1971 1980年间的全部结果：086.49141.6149412.0
4、9268.960.3630.25327.6177.44求和，即可得到 SSE=1393.8（图 9）。根据下式SSE1nxt ) 2MSE( St 1n - 1n 1 t 2容易算出均方差。根据SSE 或 MSE最小原则取 =0.3 （图11 ，图12 ），此时预测值为y11 = y(1981)= 38.5 。图 2 指数平滑计算示意图文案大全标准实用图 3 计算的第一步图 4 平滑系数为0 时的计算结果文案大全标准实用图 5 复制保存的数据图 6 平滑系数为0.1 时的计算结果图 7 全部计算结果图 8 计算误差平方和示意图文案大全标准实用图 9 平滑系数为0 时的误差平方和（SSE）图 1
5、0 误差平方和（SSE）和均方差（MSE ）SSE1500140013001200SSE1100100090080000.20.40.60.81图 11 SSE 随平滑系数变化的曲线文案大全标准实用MSE160150140130MSE12011010000.20.40.60.81图 12 MSE随平滑系数变化的曲线第六步，绘制指数平滑曲线。将=0.3时的平滑结果与原数据按顺序排列（图 13 ），然后利用Excel 的绘图功能不难绘制指数平滑曲线图将原始数据曲线与指数平滑曲线画在统一坐标系，便于比较指数平滑的效果：两条曲线越吻合，表明指数平滑的效果越好，从而预测也就越可靠。从图14可以看出，对于
6、本例而言，指数平滑的效果并不见佳（图 14）。图 13 将指数平滑结果与原始数据按顺序排列文案大全标准实用5550454035灌溉面积 ( 千亩)30一次平滑2520151019711973197519771979图 14一次指数平滑曲线图（与原始数据比较）第七步，二次指数平滑。二次指数平滑是在一次指数平滑的基础上进行的，其计算过程和检验方法与对原始数据进行指数平滑的步骤完全一样。但是，有一点无需注意：在我们的指数平滑模型中，我们取yt 1St而 y t 作为计算值对应的是xt ，故 xt 实际上对应的是St -1 。若以 1971年为第 t=1 个时点，则 1972 年才对应 S1 （ 19
7、71 年对应于 S0）。二次指数平滑的公式为StSt(1) St 1显然，我们的计算的起点是从S1开始的，亦即从1972年开始的，否则会有t-1=-1的现象，而我们的时间序号不取负值。根据习惯方法，取 S0 = S1 = x1 =28.6，平滑系数不妨仍然从 0开始，以 C3单元格表示新的平滑系数 0 所在，在 E3 单元格输入 28.6 表示 S0 ，在E4 单元格中建立公式“= $C$3*D3+ (1-$C$3)*E3”（参见 图 15 。注意这里是D3 表示 S1，E3 表示 S0 ，后面的三次指数平滑要考虑这个问题），回车，得到S1=28.6 ；下拉至 1982年，给出 0 时全部的二
8、次平滑结果；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 时，原始数据实际上未经过任何修匀，但由于计算起点相对于一次指数平滑下移
9、一步，故本例所谓的二次指数平滑实际上是对一次指数平滑的结果进行一次平移，这一点从二次指数平滑坐标图可以看出。根据图 17 中的数据容易画出两次指数平滑曲线，二次指数平滑曲线（蓝色）实际上是对一次指数平滑曲线（绿色）的右向平移（图 18 ）。因此，对于本例而言，二次指数平滑没有必要，当然三次指数平滑更是多余。不过，为了说明三次指数平滑的方法，我们还是计算三次指数平滑的结果。文案大全标准实用图 17二次平滑系数 1 时的计算结果及其误差、误差平方和5550454035灌溉面积 ( 千亩)一次平滑30二次平滑2520151019711973197519771979图 18 两次指数平滑曲线图在理论上
10、，我们可以建立指数平滑预测的线性模型yt TatbtT式中at2StStbt( StSt )1文案大全标准实用运用模型时原则上要求MSE最小条件下的两次指数平滑系数没有差别，但是，对于本例，二次指数平滑系数不仅与第一次不同，而且不能代入上面的公式，因为 1时，必有b t= ，从而模型无意义。未来说明基于指数平滑结果的线性模型创建方法，姑取 0.3 ，即假定 SSE最小时的平滑系数没有变化。在 F2 单元格中输入公式“ =2*D2-E2”，回车得 a1=28.6 ，下拉至 1981 年，得到全部的at ；在 G2 单元格中，输入公式“=0.3/(1-0.3)*(D2-E2)”，回车得b1 =0
11、，下拉至 1982年，得到全部的b t （图 19 ）。取T=1 ，根据模型yt = at+ b tT，在 H3单元格中输入公式“=F2+G2*1 ”，得到 y 2=28.6 ，下拉至1982 年，得到全部的预测值。显然，根据1980 年对应的参数 a10 =42.281 ， b10 =1.421 ，可以建立线性预测模型y10 T42.2811.421T根据 1981 年对应的参数可以建立预测模型y11 T38.0270.201T利用误差序列Err 计算自相关系数进行检验只要自相关系数的绝对值不大于1.96/ n ，就认为模型可以接受。从预测曲线与原始曲线的关系可以看出，预测效果不太理想（图
12、20）。图 19 基于两次指数平滑结果的线性模型参数及其预测结果文案大全标准实用50454035灌溉面积 ( 千亩)30yt2520151970197519801985图 20线性预测曲线与原始数据曲线的关系（二者并不吻合）第八步，三次指数平滑。三次指数平滑与二次指数平滑一样，是在平滑的结果上进行的，起点则是从1973年开始，原因与二次指数平滑从1972 年开始一样。取0= 1=28.6 ，平滑系数仍然从S S0 开始，以 C4 单元格表示新的平滑系数 0 所在，在 F4单元格输入28.6 表示 0，S 在 F5 单元格中建立公式“ = $C$4*E4+ (1-$C$4)*F4 ”，回车，得
13、S1 = 28.6 ，下拉至1983 年，得到全部的三次平滑结果；在1971 、 1972 年对应 F 单元格中取28.6 、 28.6 。改变值，可得不同平滑系数条件下的三次平滑结果（图 21 ）。正如所料，当 1 ，误差平方和最小，此时SSE=71.606 ，MSE=7.956（图 22 ）。根据图22 中的数据，可以画出三次指数平滑曲线图（图 23 ）。进而可以建立二次抛物线性预测模型，由于对于本例而言抛物线模型没有实质意义，在此从略。图 23 三次平滑系数 1 时的计算结果及其误差、误差平方和文案大全标准实用55504540灌溉面积 ( 千亩)35一次平滑30二次平滑25三次平滑20151019711973197519771979图 24三次指数平滑曲线图文案大全
利用Excel进行指数平滑分析报告与预测
