【Excel系列】Excel数据分析：时间序列预测

移动平均

18.1 移动平均工具的功能

“移动平均”分析工具可以基于特定的过去某段时期中变量的平均值，对未来值进行预测。移动平均值提供了由所有历史数据的简单的平均值所代表的趋势信息。使用此工具适用于变化较均匀的销售量、库存或其他趋势的预测。预测值的计算公式如下：

18.2 移动平均工具的使用

例：对图中的数据按时间跨度为3进行移动平均预测。

表 18-1 观测值数据

操作步骤：

（1）建立EXCEL数据清单（图图 182:B列）

（2）“数据｜分析｜数据分析｜移动平均”弹出移动平均对话框，并设置如下：

图 18-1 移动平均对话框

数据源区域：在此输入待分析数据区域的单元格引用。该区域必须由包含四个或四个以上的数据单元格的单列组成。

标志位于第一行：如果数据源区域的第一行中包含标志项，请选中此复选框。

间隔：在此输入需要在移动平均计算中包含的数值个数。默认间隔为 3。

输出区域：在此输入对输出表左上角单元格的引用。如果选中了“标准误差”复选框，Excel 将生成一个两列的输出表，其中右边的一列为标准误差值。如果没有足够的历史数据来进行预测或计算标准误差值，Excel 会返回错误值 #N/A。

输出区域必须与数据源区域中使用的数据位于同一张工作表中。因此，“新工作表”和“新工作簿”选项均不可用。

图表输出：选中此选项可在输出表中生成一个嵌入直方图。

标准误差：如果选中此复选框，则在在输出表的一列中包含标准误差值。

（3）单击“确定”得到移动平均预测结果

图 18-2 移动平均预测结果

图 18-3 移动平均预测结果（公式显示模式）

18.3 移动平均工具的缺点与改进

移动平均工具在设置对话框时，要求输入时间间隔，该间隔不能用单元格的引用，因此不能由优化工具（规划求解）来求得最优时间间隔；故建议直接利用公式。操作如下：

（1）在C2单元格输入如图所示公式，并复制到C3:C13单元格区域。则当改变F3单元格的间隔值时，其平均预测值将改变。

图 18-4 移动平均预测公式

（2）在F3单元格输入如图 184所示公式，求得均方误。

=SUMXMY2(OFFSET(B2,F2,0,12-F2,1),OFFSET(C2,F2,0,12-F2,1))/(12-F2)

（3）在E4:E11单元格输入不同间隔，利用数据表求得均方误。

（4）选中E3:F11单元格区域，“数据｜假设分析｜数据表”，弹出模拟运算表对话框，并设置如下：

图 18-5 数据表设置

（5）单击“确定”，得最优时间跨度。

图 18-6 数据表运算结果

由图可见，时间跨度为5时，均方误最小。

指数平滑

19.1 指数平滑法简介

指数平滑法（Exponential Smoothing，ES）是布朗(Robert G..Brown)所提出，布朗、认为时间序列的态势具有稳定性或规则性，所以时间序列可被合理地顺势推延；他认为最近的过去态势，在某种程度上会持续的未来，所以将较大的权数放在最近的资料。即：

19.2 EXCEL指数平滑工具的使用

指数平滑常数取值至关重要。平滑常数决定了平滑水平以及对预测值与实际结果之间差异的响应速度。平滑常数a越接近于1，远期实际值对本期平滑值的下降越迅速；平滑常数a越接近于 0，远期实际值对本期平滑值影响程度的下降越缓慢。由此，当时间数列相对平稳时，可取较大的a。

例：对如下12周的数据，利用EXCEL指数平滑工具求各期平滑值。

（1）在EXCEL中输入数据。

图 19-1 数据资料

（2）从“数据”选项卡选择“数据分析”，选择“指数平滑”，单击“确定”弹出对话框如下：

图 19-2 指数平滑对话框

（3）单击“确定”得到指数平滑结果（图 193,公式显示模型图 194）

图 19-3 指数平滑结果

图 19-4 指数平滑结果（公式显示模式）

图中C列为平滑值，D列的标准误差。此标准误差为近3期的平均标准误。

19.3 指数平滑工具的缺点与改进

指数平滑工具在设置对话框时，要求输入阻尼系数，因此对于求得的平滑结果有两个问题：一是不能由优化工具（规划求解）来求得最优平滑系数；二是对于近期的平均标准误不能人为地改变时间跨度。因此不建议使用指数平滑工具，而建议直接利用公式。操作如下：

（1）根据公式（4）在C2单元格输入“＝B2”，确定Y的初值，在C3单元格输入如图5所示公式，其中平滑系数引用F2单元格的值，以便利用“规划求解”工具进行优化。将C3单元格的公式复制到C3:C13单元格区域，得指数平滑值。

（2）在F3单元格输入如图 195所示公式，求得误差平方和，该值与标准误同时达到最小。

图 19-5 指数平滑模型的建立（公式显示模式）

图 19-6 指数平滑模型的建立（普通显示模式）

（3）利用“规划求解”工具求得最优平滑系数。

从“数据”选项卡选择“规划求解”，调出规划求解参数对话框，并设置如图 197其约束添加如下：单击“添加”，弹出图 198所示添加约束对话框，并设置如图 198.单击“确定”，返回规划求解参数对话框。

图 19-7 规划求解参数对话框的设置

图 19-8 添加约束

（4）在图 197所示对话框中单击“选项”，设置为“假定非负”，单击“确定”返回规划求解参数对话框。

图 19-9 规划求解选项设置“假定非负”

在图7所示对话框中单击“求解”，得最优平滑系数如图 1910所示。即最优平滑系数为0.2843。

图 19-10 规划求解结果

傅利叶分析

20.1 傅利叶分析基本知识简介

傅利叶分析Fourier analysis 是分析学中18世纪逐渐形成的一个重要分支，主要研究函数的傅利叶变换及其性质。Excel中的傅立叶分析是求解离散型快速傅立叶变换和逆变换。

快速傅利叶变换（Fast Fourier Transform, FFT），是离散傅利叶变换的快速算法，也可用于计算离散傅利叶变换的逆变换。快速傅利叶变换有广泛的应用，如数字信号处理、计算大整数乘法、求解偏微分方程等等。在经济管理中可用于判断时间序列周期性。

20.2 傅利叶工具时间序列频谱分析中的应用

对于时间序列，可以展开成傅利叶级数，进行频谱分析。对于时间序列xt其傅立叶级数展开式为展开成傅立叶级数：

20.3 傅利叶分析工具应用操作

步骤

（1）输入数据并中心化：时间、时间序号t、观测值xt、中心化(减x平均值）、求频率fi(=i/N)。

（2）由傅立叶分析工具求中心化数据序列的傅立叶变换。

（3）IMREAL和IMAGINARY提取实部和虚部，按公式5计算频率强度（或由IMCONJUGATE求得共轭复数，再由IMPRODUCT求得两共轭复数乘积，得频率强度。

（4）以频率为横坐标、频率强度为纵坐标，绘制频率强度图。

（5）分析周期性。由频率强度最大的所对应的频率倒数即得周期。

例：某时间序列如图 20-1

图 20-1 时间序列观测值及其图形

由图可见，序列显现周期性变化，在整个时期范围内，周期为4.下面利用傅立叶分析工具进行频谱分析。

（1）在B18单元格输入“=AVERAGE(B2:B17)”求得观测值的平均值；在C2单元格输入“=B2/B$18”，将观测值中心化（均值为0,并仍保持原序列的方差），并复制到C3:C17

图 20-2 傅立叶变换及频率强度计算过程

（2）从“数据”选项卡选择“数据分析”｜选择“傅利叶分析”弹出对话框并设置如图 20-3：

图 20-3 傅利叶分析对话框

（2）单击“确定”生成傅立叶变换序列（图 20-2 D列）。

（3）在E2单元格输入“=IMCONJUGATE(D2)”求得傅利叶变换值的共轭复数，并复制到E3:E17；在F3至F17输入1至15,列出周期序列；在G3单元格输入“=F3/16”求得频率，并复制到G4:G17；在H3单元格输入“=IMPRODUCT(D3:E3)*8”（即根据公式5）求得频率强度，并复制到H4:H17。（见图3）

（4）以G3:H17为源数据，插入散点图，得图 204所示频率强度频谱图。

图 20-4 频率强度频谱图

由图可见，图形完全对称，通常只取左半部分。频率强度最大的所对应的频率为0.25,其倒数为4,即周期为4。