EXCEL预测趋势功能讲义

E13* E15+F13*F15+ G13* G15”
定性预测法
之 中位值法
•操作步骤
设置“E17”单元格的内容为“=MEDIAN（H3：H10）
将“E17”单元格的内容复制到“F17”和“G17”单元
格
采用比重系数法中对最可能销售量、最低销售量和最
高销售量比重的设置，设置“I17”单元格的内容为
销售量 10
18
25
30.5
35
38
40
39.5
38
曲线趋势预测法
之 二次线性/多项式
模型预测
绘制产品销售量的“XY散点图”。从散点图可以看出，比较
符合的模型有二次曲线和指数曲线模型，但无法确定那一个模
型能更好拟合该曲线。下面以二次型为例进行参数拟合。
应用公式
y''

yt'

y' t 1

yt
t 4
t 4
t 4
4
4
4
分别计算 yt ， t2 ， tyt
t 4
t 4
t 4
4
， (t2 yt ) ， t 4
4
t4
t 4
的值，将结
果代入（1）中，得
274 9 b0 60 b2
214 60 b1
（2）
1614 60 b0 708 b2
季节变动预测法
季节变动预测法又称季节周期法、季节 指数法、季节变动趋势预测法，季节变动预 测法是对包含季节波动的时间序列进行的方 法。要研究这种方法，就要研究时间序列的 变动。
季节变动预测法
之 不变季节指数预测法
•分类
1、自相关系数法
2、方差分析法
3、简单季节预测法
4、Winters指数平滑预测法
公司成立专家小组，并聘请业务经理、市场专家和销售人员等8为专家，预测
全年可能的销售量。8为专家提出个人判断，经过三次反馈得到结果如下表所
示专家。
编号
第一次判断
最低销售量
最可 能销 售量
最高 销售 量
第二次判断
最低销售量
最可 能销 售量
最高 销售 量
第三次判断
最低销售量
最可 能销 售量
最高 销售 量
1
季节变动预测法
之 不变季节指数预测法 •操作步骤 （以“方差分析法”为例） 应用一次线性模型消除时间序列数据中的趋势数据， 即绘制时间序列的“XY散点图”，然后通过添加趋势 线的方式获得一次线性模型的模型方程以及趋势预测 值。
用各期的时间序列值除以其趋势预测值，得到季节指 数的估计值。假定季节长度L=4，因此将估计值分为4 组，每组数据只包含同“季节”的数据。
时间序列预测法
之 移动平均法
•原理 1、一次移动平均法是指将观察期的数据由远而近按
一定跨越期进行一次移动平均，以最后一个移动平均 值为确定预测值的依据的一种预测方法。
2、二次移动平均法，是对一次移动平均数再进行 第二次移动平均，再以一次移动平均值和二次移动平 均值为基础建立预测模型，计算预测值的方法。
在“移动平均”对话框“输入区域”选择原始数据所 在的单元格区域“C2：C13”，“阻尼系数”中输入 “0.3”，表示平滑系数a=0.3，“输,出区域”选择单 元格“E2”，同时选择“图标输出”和“标准误差” 复选框，点击确定按钮。
时间序列预测法
之 指数平滑法
此时，单元格“E13”给出了一次指数平滑的预 测值，单元格区域“F6：F13”给出了预测的标准 差，实际值以及一次指数平滑法预测值同时以图表 形式给出。
定性预测法
•原理 1、平均值法是根据两组分解物质的某种平均值来判
断两物质范围的解题方法。 2、比重系数法是根据各个因素对结果影响的比重
进行加权平均来预测结果的方法。 3、中位值法是以一组数据的中间数据来预测结果
的方法。
定性预测法
某公司研制出一种新兴产品，现在市场上还没有相似产品出现，因此没有历
史数据可以获得。公司需要对可能的销售量做出预测，以决定敞亮。于是该
数据 13 26 32 8
10 27 31 10
季节变动预测法
之 不变季节指数预测法
输入给定的数据，并输入其滞后1期的时间序列。 点 击excel软件菜单中【工具】菜单下面的子菜单【数据
分析】，打开“数据分析”对话框，从“分析工具”列
表中选择“相关系统”，并点击[确定]按钮。
在打开的“相关系数”对话框中，“输入区域”选择 单元格区域，“输出区域”，并指定“分组方式”为 “逐列”，点击[确定]按钮。
•操作步骤 （以“一次指数平滑法”为例） •下表为1980某地区平板玻璃月产量数据，运用一次指 数平滑法对1981年1月份该地区平板玻璃月产量进行预 测（取a=0.3,0.5,0.7），并计算军方误差。选择使均 方误差最小的a进行预测。 单位：t
时间
1980.01 1980.02 1980.03 1980.04 1980.05 1980.06
季节变动预测法
之 不变季节指数预测法
此时已给出时间序列与其滞后1期所形成的时间序列
的相关系数计算值为-0.112998。重复上述操作，计算
该时间序列与其滞后2~8期的时间序列的相关系数值。
绘制时间序列自相关系数的散点图。从图中可以看出， 时间序列与其滞后2期，6期的时间序列相关系数取负值， 且绝对值很大；与滞后4期，8期的时间序列相关系数值 取正值，且值很大。由此可以判断，该时间序列存在季 节变动，且季节长度为4。
曲线趋势预测法
•分类
1、二次线性/多项式模型预测 2、指数曲线模型
曲线趋势预测法
•操作步骤
之 二次线性/多项式 模型预测
•已知某商店某种产品销售量如下表所示。试预测2005
年该产品的销售量。比较符合的模型有二次曲线和指
数曲线模型，但无法确定那一个模型能更好拟合该曲
线。
产品生产表
年份 1996 1997 1998 1999 2000 2001 2002 2003 2004
11 12 28.1 25
点击EXCEL菜单栏中【工具】菜单下的子菜单【数据 分析】；打开“数据分析”对话框；从“分析工具” 列表中选择“移动平均”，点击【确定】按钮。
时间序列预测法
之 移动平均法
在“移动平均”对话框“输入区域”选择原始数据所 在的单元格区域“C2：C13”，“间隔”中输入“3”， “输入区域”选择单元格“E2”，同时选择“图标输 出”和“标准误差”复选框，点击确定按钮。
产量
203.8
214.1
229.9
223.7
220.7
198.4
时间
1980.07 1980.08 1980.09 1980.10 1980.11 1980.12
产量
207.8
228.5
206.5
226.8
247.8
259.5
时间序列预测法
之 指数平滑法
点击EXCEL菜单栏中【工具】菜单下面的子菜单【数 据分析】；打开“数据分析”对话框；从“分析工具” 列表中选择“指数平滑”，点击【确定】按钮。
此时，单元格“E13”给出了一次移动平均的预测值， 单元格区域“F6：F13”给出了预测的标准差，实际值 以及一次移动平均法预测值同时以图表形式给出。
时间序列预测法
之 指数平滑法
•分类
1、一次指数平滑法
2、二次指数平滑法
3、三次指数平滑法
4、霍尔特双参数现行指数平滑法
时间序列预测法
之 指数平滑法
t 4
曲线趋势预测法
之 二次线性/多项式
模型预测
因此，二次线性模型的参数估计公式可以简化为
4

yt 9b0 b2
4
t2
t 4
t 4


4
tyt b1
4
t2
t 4
t 4
4
4
4
(t 2 yt ) b0 t 2 b2 t 4
5、趋势比率法
6、Holt-Winters指数平滑法
季节变动预测法
之 不变季节指数预测法
•操作步骤 （以“自相关系数法”与“方差分析法” 为例）试采用自相关系数判断法判断下表的时间序列 是否存在季节变动的影响。
时间 1
2
3
4
5
6
7
8
数据 11 25 31 7
12 24 30 9
时间 9
10 11 12 13 14 15 16
曲线趋势预测法
曲线趋势预测法是利用曲线趋势模型进行预测的方法, 常用的曲线趋势模型有抛物线和简单指数两种.当时间 序列在长时期内呈现连续的不断增长或减少的变动趋 势,其逐期增长量又大致相同时,使用直线趋势模型进行 预测为宜;如果时间序列的二级增长量大体相同,使用抛 物线趋势模型进行预测为宜;当时间序列的环比发展速 度或环比增长速度大体相同时,使用指数趋势模型进行 预测为宜.
季节变动预测法
之 不变季节指数预测法
从方差分析所得结果可知，组件差异平方和为3.8509， 组内差异平方和为0.0389，总差异平方和为3.8898。 因此得到F统计量计算值为396.2625。而F分布表给出 的F临界值为3.4903。因为计算得到的F统计量大于F统 计量的临界值，所以各组数据的均值有显著差异，即 可认为季节影响存在，季节长度为4。
“=SUMPRODUCT（E15：G15,E17：G17）”，即“= E15*
Biblioteka Baidu
E17+F13*F17+ G15* G17”
定性预测法
时间序列预测法
时间序列是将某种统计指标的数值，按时间 先后顺序排到所形成的数列。
时间序列预测法就是通过编制和分析时间序 列，根据时间序列所反映出来的发展过程、方 向和趋势，进行类推或延伸，借以预测下一段 时间或以后若干年内可能达到的水平。
时间序列预测法
之 移动平均法
•操作步骤 （以“一次移动平均法”为例）
•某商场2005年1~12月份彩电的销售数据如表所示，预
测2006年1月销售额，单位：万元
月份 1 2 3 4 5 6 7 8 9 10 销售额 25.5 28.1 25 27.5 23.5 21.9 23.8 24.5 26 25
500 600
6
300
500 750
300
500 750
300
600 750
7
250
300 400
250
400 500
400
500 600
8
260
300 500
350
400 600
370
410 610
定性预测法
之 平均值法
•操作步骤
【平均值法】
输入给定的数据
比重系数法
设置“E13”单元格的内容为“=AVERAGE（H3:H10）”
定性预测法
移动平均法
时间序列预测法
指数平滑法
曲线趋势预测法
季节变动预测法

不变季节指数预测法
可变、多季节指数预测
回归分析预测法
定性预测法
定性预测是指根据已掌握的历史资料和直观材料， 运用个人的经验和分析判断能力，对事物的未来发展 做出性质和程度上的判断，然后，再通过一定形式综 合各方面的意见，作为预测未来的主要依据。适用于 重大问题或缺乏原式数据的预测。其预测准确程度主 要取决于预测者的经验、理论、业务水平和分析判断 能力。
500
750 900
600
750 900
550
750 900
2
200
450 600
300
500 650
400
500 650
3
400
600 800
500
700 800
500
700 800
4
750
900 1500
600
750 1500
500
600 1250
5
100
200 350
220
400 500
300
2 yt1

yt 2
计算数据的二阶差分，即单
元格“C3”内容为“=B3-B2”，单元格“D4”的内容为“=C4-
C3”
从二阶差分数据来看，多数二阶差分数据为-1和-1.5，由此
判断可以使用二次线性模型进行预测。设定时间值为-4~4，从
而使得 4 t 0 ，4 t3 0。
t 4
将“E13”单元格的内容复制到“F13”和“G13”单元
格。
设置“I13”单元格的内容为“= AVERAGE（E13:
G13）”
【比重系数法】
假设最可能销售量、最低销售量和最高销售量比重
分别是0.5、0.2和0.3，则可以设置“I15”单元格内容
为“=SUMPRODUCT（E13：G13,E15：G15）”，即“=
曲线趋势预测法
之 二次线性/多项式
模型预测
计算公式（2），得到参数：b0 35.0479 ，b1 3.5667 ，
b2 0.6905 。
将参数
b0
， b1
， b2
代入公式
^
yt
b0 b1t b2t2，得到各
年份预测值。
计算预测误差平方。
计算2005年预测数据为35.6186.
季节变动预测法
之 不变季节指数预测法
点击excel软件菜单栏中的【工具】菜单下面的子菜 单【数据分析】。在打开的“数据分析”对话框，选 择“方差分析：单因素方差分析”，点击[确定]按钮。
在“方差分析：单因素方差分析”对话框中，“输入 区域”选择分组后的季节指数值数据区域，并且指定 分组方式为“列”分组。指定输出区域后，点击[确定] 按钮。