Excel(时间序列预测)操作
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1
时间序列分析预测 EXCEL 操作
一、长期趋势(T)的测定预测方法
线性趋势→：: 用回归法 非线性趋势中的“指数曲线” ：用指数函数 LOGEST、增长函数 GROWTH（针对指数曲线） 多阶曲线(多项式)：用回归法 （一）回归模型法-------长期趋势（线性或非线性）模型法： 具体操作过程：在 EXCEL 中点击“工具” “数据分析” “回归” 分别在“Y 值输入 区域”和“X 值输入区域”输人数据和列序号的单元格区域一选择需要的输出项目，如“线性拟合图” 。 回归分析工具的输出解释：
1
2
第四列：根据原假设 Ho：a=b=0 计算的样本 统计量 t 的值。 第五列：各个回归系数的 p 值(双侧) 第六列：a 和 b 95%的置信区间的上下限。 （二）使用指数函数 LOGEST 和增长函数 GROWTH 进行非线性预测 在 Excel 中，有一个专用于指数曲线回归分析的 LOGEST 函数，其线性化的全部计算过程都是自 动完成的。如果因变量随自变量的增加而相应增加，且增加的幅度逐渐加大；或者因变量随自变量的增 加而相应减少，且减少的幅度逐渐缩小，就可以断定其为指数曲线类型。 具体操作过程： 1．使用 LOGEST 函数 计算回归统计量 ①打开“第 3 章 时间数列分析与预测.xls”工作簿，选择“增长曲线”工作表如下图所示。
3
4
Fra Baidu bibliotek确的预测。 Excel 操作过程： ①打开“第 3 章 时间数列分析与预测.XLS”工作簿，选择“长期趋势剔除”工作表，如下图所示 。
②在单元格 E1 中，点击“工具”菜单中选择“数据分析”选项，打开“移动平均”对话框，对 D 列“销售额” 进行 4 项移动平均。 ③在单元格 F3 中，点击“工具”菜单中选择“数据分析”选项，打开“移动平均”对话框，对 E 列进行 2 项 移动平均。 ④把单元格 E4:F4 中的公式复制到 E19:F19，调整其小数部分使显示 1 位小数，结果如下图所示。
4
5
分别计算第三、第四季度的季节比率。
5
6
6
函数输出结果
2
3
ˆ 504.9204 1.168757 ④根据单元格 E2，F2 中的计算结果可以写出如下估计方程： y ⑤可以根据上面的指数曲线方程计算预测值：在 D2 单元格中输入公式“=$F$2*$E$2^B2” ，将 D2 单元 格中的公式复制到 D3:D20 元格， 各年的预测值便一目了然了。 但使用增长函数可以直接得到预测结果。 2. 使用增长函数 GROWTH 计算预测值 ①选择 D2:D20 单元格，在工具栏单击“粘贴函数”快捷键，弹出“粘贴函数”对话框，在“函数分类”中选 择“统计”，在“函数名”中选择“GROWTH”函数，打开 GROWTH 对话框，如下图所示。
x
②在 Known_y’s、Known_x’s、 New_x’s 和 Const 后分别输入 C2:C16、 B2:B16、 B2:B20 和 1。 按住 Ctrl+Shift 组合键，同时按回车键或“确定”按钮，计算结果如下图所示。 3. 绘制实际值与预测值的折线图
季节变动的测定与预测分析
季节变动的趋势—循环剔除法 长期趋势剔除法是在移动平均法的基础上，以乘法模型（Y=T×S × C × I ）为理论基础的 测定季节变动的方法，它能避免长期趋势与周期波动的影响，净化季节变动的规律性，从而实现较为准
计算结果共分为三个模块： 1）回归统计表： Multiple R（复相关系数 R） ：R2 的平方根，又称为相关系数，它用来衡量变量 xy 之间相关程度的大小。 R Square(复测定系数 R2 )：用来说明用自变量解释因变量变差的程度，以测量同因变量 y 的拟合效果。 Adjusted R Square (调整复测定系数 R2)：仅用于多元回归才有意义，它用于衡量加入独立变量后模型的 拟合程度。当有新的独立变量加入后，即使这一变量同因变量之间不相关，未经修正的 R2 也要增大， 修正的 R2 仅用于比较含有同一个因变量的各种模型。 标准误差：又称为标准回归误差或叫估计标准误差，它用来衡量拟合程度的大小，也用于计算与回归有 关的其他统计量，此值越小，说明拟合程度越好。 2）方差分析表：方差分析表的主要作用是通过 F 检验来判断回归模型的回归效果。 3） 回归参数：回归参数表是表中最后一个部分： Intercept：截距 a 第二、三行：a (截距) 和 b (斜率)的各项指标。 第二列：回归系数 a (截距)和 b (斜率)的值。 第三列：回归系数的标准误差
②选择 E2:F6 区域，单击工具栏中的“粘贴函数”快捷键，弹出“粘贴函数”对话框，在“函数分类”中选择 “统计”，在“函数名”中选择“LOGEST”函数，则打开 LOGEST 对话框，如下图 11.20 所示。 ③在 Known_y’s、 Known_x’s 和 Stats 后分别输入 C2:C16、B2:B16 和 1。按住 Ctrl+Shift 组合键，再按 回车键或“确定”按钮，得到计算结果如下图中 E2:F6 单元格所示。
⑤在单元格 G4 中输入公式“＝D4／F4”，并把它复制到 G5:G19。 ⑥在单元格 H2 中输入公式“＝AVERAGE(H6,H10,H14,H18)”, 并把它复制到单元格 H3 中。 分别计算第一、第二季度的季节比率。 ⑦在单元格 H4 中输入公式“＝AVERAGE(H4，H8，H12，H16)”， 并把它复制到单元格 H5 中。
时间序列分析预测 EXCEL 操作
一、长期趋势(T)的测定预测方法
线性趋势→：: 用回归法 非线性趋势中的“指数曲线” ：用指数函数 LOGEST、增长函数 GROWTH（针对指数曲线） 多阶曲线(多项式)：用回归法 （一）回归模型法-------长期趋势（线性或非线性）模型法： 具体操作过程：在 EXCEL 中点击“工具” “数据分析” “回归” 分别在“Y 值输入 区域”和“X 值输入区域”输人数据和列序号的单元格区域一选择需要的输出项目，如“线性拟合图” 。 回归分析工具的输出解释：
1
2
第四列：根据原假设 Ho：a=b=0 计算的样本 统计量 t 的值。 第五列：各个回归系数的 p 值(双侧) 第六列：a 和 b 95%的置信区间的上下限。 （二）使用指数函数 LOGEST 和增长函数 GROWTH 进行非线性预测 在 Excel 中，有一个专用于指数曲线回归分析的 LOGEST 函数，其线性化的全部计算过程都是自 动完成的。如果因变量随自变量的增加而相应增加，且增加的幅度逐渐加大；或者因变量随自变量的增 加而相应减少，且减少的幅度逐渐缩小，就可以断定其为指数曲线类型。 具体操作过程： 1．使用 LOGEST 函数 计算回归统计量 ①打开“第 3 章 时间数列分析与预测.xls”工作簿，选择“增长曲线”工作表如下图所示。
3
4
Fra Baidu bibliotek确的预测。 Excel 操作过程： ①打开“第 3 章 时间数列分析与预测.XLS”工作簿，选择“长期趋势剔除”工作表，如下图所示 。
②在单元格 E1 中，点击“工具”菜单中选择“数据分析”选项，打开“移动平均”对话框，对 D 列“销售额” 进行 4 项移动平均。 ③在单元格 F3 中，点击“工具”菜单中选择“数据分析”选项，打开“移动平均”对话框，对 E 列进行 2 项 移动平均。 ④把单元格 E4:F4 中的公式复制到 E19:F19，调整其小数部分使显示 1 位小数，结果如下图所示。
4
5
分别计算第三、第四季度的季节比率。
5
6
6
函数输出结果
2
3
ˆ 504.9204 1.168757 ④根据单元格 E2，F2 中的计算结果可以写出如下估计方程： y ⑤可以根据上面的指数曲线方程计算预测值：在 D2 单元格中输入公式“=$F$2*$E$2^B2” ，将 D2 单元 格中的公式复制到 D3:D20 元格， 各年的预测值便一目了然了。 但使用增长函数可以直接得到预测结果。 2. 使用增长函数 GROWTH 计算预测值 ①选择 D2:D20 单元格，在工具栏单击“粘贴函数”快捷键，弹出“粘贴函数”对话框，在“函数分类”中选 择“统计”，在“函数名”中选择“GROWTH”函数，打开 GROWTH 对话框，如下图所示。
x
②在 Known_y’s、Known_x’s、 New_x’s 和 Const 后分别输入 C2:C16、 B2:B16、 B2:B20 和 1。 按住 Ctrl+Shift 组合键，同时按回车键或“确定”按钮，计算结果如下图所示。 3. 绘制实际值与预测值的折线图
季节变动的测定与预测分析
季节变动的趋势—循环剔除法 长期趋势剔除法是在移动平均法的基础上，以乘法模型（Y=T×S × C × I ）为理论基础的 测定季节变动的方法，它能避免长期趋势与周期波动的影响，净化季节变动的规律性，从而实现较为准
计算结果共分为三个模块： 1）回归统计表： Multiple R（复相关系数 R） ：R2 的平方根，又称为相关系数，它用来衡量变量 xy 之间相关程度的大小。 R Square(复测定系数 R2 )：用来说明用自变量解释因变量变差的程度，以测量同因变量 y 的拟合效果。 Adjusted R Square (调整复测定系数 R2)：仅用于多元回归才有意义，它用于衡量加入独立变量后模型的 拟合程度。当有新的独立变量加入后，即使这一变量同因变量之间不相关，未经修正的 R2 也要增大， 修正的 R2 仅用于比较含有同一个因变量的各种模型。 标准误差：又称为标准回归误差或叫估计标准误差，它用来衡量拟合程度的大小，也用于计算与回归有 关的其他统计量，此值越小，说明拟合程度越好。 2）方差分析表：方差分析表的主要作用是通过 F 检验来判断回归模型的回归效果。 3） 回归参数：回归参数表是表中最后一个部分： Intercept：截距 a 第二、三行：a (截距) 和 b (斜率)的各项指标。 第二列：回归系数 a (截距)和 b (斜率)的值。 第三列：回归系数的标准误差
②选择 E2:F6 区域，单击工具栏中的“粘贴函数”快捷键，弹出“粘贴函数”对话框，在“函数分类”中选择 “统计”，在“函数名”中选择“LOGEST”函数，则打开 LOGEST 对话框，如下图 11.20 所示。 ③在 Known_y’s、 Known_x’s 和 Stats 后分别输入 C2:C16、B2:B16 和 1。按住 Ctrl+Shift 组合键，再按 回车键或“确定”按钮，得到计算结果如下图中 E2:F6 单元格所示。
⑤在单元格 G4 中输入公式“＝D4／F4”，并把它复制到 G5:G19。 ⑥在单元格 H2 中输入公式“＝AVERAGE(H6,H10,H14,H18)”, 并把它复制到单元格 H3 中。 分别计算第一、第二季度的季节比率。 ⑦在单元格 H4 中输入公式“＝AVERAGE(H4，H8，H12，H16)”， 并把它复制到单元格 H5 中。