EXCEL预测趋势功能

1、利用EXCEL预测趋势 1 定性预测法时间序列预测法曲线趋势预测法季节变动预测法回归分析预测法 移动平均法指数平滑法 不变季节指数预测法可变 多季节指数预测 目录 2 定性预测法 简介 定性预测是指根据已掌握的历史资料和直观材料 运用个人的经验和分析判断能力 对事物的未来发展做出性质和程度上的判断 然后 再通过一定形式综合各方面的意见 作为预测未来的主要依据 适用于重大问题或缺乏原式数据的预测 其预测准确程度主要取决于预测者的经验 理论 业务水平和分析判断能力 3 定性预测法 原理1 平均值法是根据两组分解物质的某种平均值来判断两物质范围的解题方法 2 比重系数法是根据各个因素对结果影响的比重进行加权平均来预测结果的方法 3 中位值法是以一组数据的中间数据来预测结果的方法 4 定性预测法 某公司研制出一种新兴产品 现在市场上还没有相似产品出现 因此没有历史数据可以获得 公司需要对可能的销售量做出预测 以决定敞亮 于是该公司成立专家小组 并聘请业务经理 市场专家和销售人员等8为专家 预测全年可能的销售量 8为专家提出个人判断 经过三次反馈得到结果如下表所示 5 定性预测法 操作步骤 平均

2、值法 输入给定的数据设置 E13 单元格的内容为 AVERAGE H3 H10 将 E13 单元格的内容复制到 F13 和 G13 单元格 设置 I13 单元格的内容为 AVERAGE E13 G13 比重系数法 假设最可能销售量 最低销售量和最高销售量比重分别是0 5 0 2和0 3 则可以设置 I15 单元格内容为 SUMPRODUCT E13 G13 E15 G15 即 E13 E15 F13 F15 G13 G15 之平均值法比重系数法 6 定性预测法 操作步骤设置 E17 单元格的内容为 MEDIAN H3 H10 将 E17 单元格的内容复制到 F17 和 G17 单元格采用比重系数法中对最可能销售量 最低销售量和最高销售量比重的设置 设置 I17 单元格的内容为 SUMPRODUCT E15 G15 E17 G17 即 E15 E17 F13 F17 G15 G17 之中位值法 7 定性预测法 8 时间序列预测法 时间序列是将某种统计指标的数值 按时间先后顺序排到所形成的数列 时间序列预测法就是通过编制和分析时间序列 根据时间序列所反映出来的发展过程 方向和趋势 进行类推

3、或延伸 借以预测下一段时间或以后若干年内可能达到的水平 简介 9 之移动平均法 原理1 一次移动平均法是指将观察期的数据由远而近按一定跨越期进行一次移动平均 以最后一个移动平均值为确定预测值的依据的一种预测方法 2 二次移动平均法 是对一次移动平均数再进行第二次移动平均 再以一次移动平均值和二次移动平均值为基础建立预测模型 计算预测值的方法 时间序列预测法 10 之移动平均法 操作步骤 以 一次移动平均法 为例 某商场2005年1 12月份彩电的销售数据如表所示 预测2006年1月销售额 单位 万元点击EXCEL菜单栏中 工具 菜单下的子菜单 数据分析 打开 数据分析 对话框 从 分析工具 列表中选择 移动平均 点击 确定 按钮 时间序列预测法 11 之移动平均法 在 移动平均 对话框 输入区域 选择原始数据所在的单元格区域 C2 C13 间隔 中输入 3 输入区域 选择单元格 E2 同时选择 图标输出 和 标准误差 复选框 点击确定按钮 此时 单元格 E13 给出了一次移动平均的预测值 单元格区域 F6 F13 给出了预测的标准差 实际值以及一次移动平均法预测值同时以图表形式给出 时

4、间序列预测法 12 之指数平滑法 分类1 一次指数平滑法2 二次指数平滑法3 三次指数平滑法4 霍尔特双参数现行指数平滑法 时间序列预测法 13 之指数平滑法 操作步骤 以 一次指数平滑法 为例 下表为1980某地区平板玻璃月产量数据 运用一次指数平滑法对1981年1月份该地区平板玻璃月产量进行预测 取a 0 3 0 5 0 7 并计算军方误差 选择使均方误差最小的a进行预测 单位 t 时间序列预测法 14 之指数平滑法 点击EXCEL菜单栏中 工具 菜单下面的子菜单 数据分析 打开 数据分析 对话框 从 分析工具 列表中选择 指数平滑 点击 确定 按钮 在 移动平均 对话框 输入区域 选择原始数据所在的单元格区域 C2 C13 阻尼系数 中输入 0 3 表示平滑系数a 0 3 输 出区域 选择单元格 E2 同时选择 图标输出 和 标准误差 复选框 点击确定按钮 时间序列预测法 15 之指数平滑法 时间序列预测法 此时 单元格 E13 给出了一次指数平滑的预测值 单元格区域 F6 F13 给出了预测的标准差 实际值以及一次指数平滑法预测值同时以图表形式给出 16 曲线趋势预测法 简介

5、曲线趋势预测法是利用曲线趋势模型进行预测的方法 常用的曲线趋势模型有抛物线和简单指数两种 当时间序列在长时期内呈现连续的不断增长或减少的变动趋势 其逐期增长量又大致相同时 使用直线趋势模型进行预测为宜 如果时间序列的二级增长量大体相同 使用抛物线趋势模型进行预测为宜 当时间序列的环比发展速度或环比增长速度大体相同时 使用指数趋势模型进行预测为宜 17 曲线趋势预测法 分类1 二次线性 多项式模型预测2 指数曲线模型 18 曲线趋势预测法 之二次线性 多项式模型预测 操作步骤已知某商店某种产品销售量如下表所示 试预测2005年该产品的销售量 比较符合的模型有二次曲线和指数曲线模型 但无法确定那一个模型能更好拟合该曲线 产品生产表 19 曲线趋势预测法 绘制产品销售量的 XY散点图 从散点图可以看出 比较符合的模型有二次曲线和指数曲线模型 但无法确定那一个模型能更好拟合该曲线 下面以二次型为例进行参数拟合 应用公式计算数据的二阶差分 即单元格 C3 内容为 B3 B2 单元格 D4 的内容为 C4 C3 从二阶差分数据来看 多数二阶差分数据为 1和 1 5 由此判断可以使用二次线性模型进行

6、预测 设定时间值为 4 4 从而使得 之二次线性 多项式模型预测 20 曲线趋势预测法 因此 二次线性模型的参数估计公式可以简化为分别计算 的值 将结果代入 1 中 得 2 之二次线性 多项式模型预测 21 曲线趋势预测法 计算公式 2 得到参数 将参数 代入公式 得到各年份预测值 计算预测误差平方 计算2005年预测数据为35 6186 之二次线性 多项式模型预测 22 季节变动预测法 简介 季节变动预测法又称季节周期法 季节指数法 季节变动趋势预测法 季节变动预测法是对包含季节波动的时间序列进行的方法 要研究这种方法 就要研究时间序列的变动 23 季节变动预测法 之不变季节指数预测法 分类1 自相关系数法2 方差分析法3 简单季节预测法4 Winters指数平滑预测法5 趋势比率法6 Holt Winters指数平滑法 24 季节变动预测法 之不变季节指数预测法 操作步骤 以 自相关系数法 与 方差分析法 为例 试采用自相关系数判断法判断下表的时间序列是否存在季节变动的影响 25 季节变动预测法 之不变季节指数预测法 输入给定的数据 并输入其滞后1期的时间序列 点击excel软件菜

7、单中 工具 菜单下面的子菜单 数据分析 打开 数据分析 对话框 从 分析工具 列表中选择 相关系统 并点击 确定 按钮 在打开的 相关系数 对话框中 输入区域 选择单元格区域 输出区域 并指定 分组方式 为 逐列 点击 确定 按钮 26 季节变动预测法 之不变季节指数预测法 此时已给出时间序列与其滞后1期所形成的时间序列的相关系数计算值为 0 112998 重复上述操作 计算该时间序列与其滞后2 8期的时间序列的相关系数值 绘制时间序列自相关系数的散点图 从图中可以看出 时间序列与其滞后2期 6期的时间序列相关系数取负值 且绝对值很大 与滞后4期 8期的时间序列相关系数值取正值 且值很大 由此可以判断 该时间序列存在季节变动 且季节长度为4 27 季节变动预测法 之不变季节指数预测法 操作步骤 以 方差分析法 为例 应用一次线性模型消除时间序列数据中的趋势数据 即绘制时间序列的 XY散点图 然后通过添加趋势线的方式获得一次线性模型的模型方程以及趋势预测值 用各期的时间序列值除以其趋势预测值 得到季节指数的估计值 假定季节长度L 4 因此将估计值分为4组 每组数据只包含同 季节 的数据

8、28 季节变动预测法 之不变季节指数预测法 点击excel软件菜单栏中的 工具 菜单下面的子菜单 数据分析 在打开的 数据分析 对话框 选择 方差分析 单因素方差分析 点击 确定 按钮 在 方差分析 单因素方差分析 对话框中 输入区域 选择分组后的季节指数值数据区域 并且指定分组方式为 列 分组 指定输出区域后 点击 确定 按钮 29 季节变动预测法 之不变季节指数预测法 从方差分析所得结果可知 组件差异平方和为3 8509 组内差异平方和为0 0389 总差异平方和为3 8898 因此得到F统计量计算值为396 2625 而F分布表给出的F临界值为3 4903 因为计算得到的F统计量大于F统计量的临界值 所以各组数据的均值有显著差异 即可认为季节影响存在 季节长度为4 30 季节变动预测法 之不变季节指数预测法 1 计算时间序列值的平均值作为趋势的估计值 2 剔除趋势 即用各期时间序列值除以平均值 得出季节指数值 注意 此处需要采用绝对引用的方式 3 由于表6 1所示数据具有季节长度为4的季节变动 因此 对同季节的季节指数值求平均值 4 应用公式计算未来一年的数据 如第一季度的预测值

9、为 F3 B 19 如下图所示 简单季节预测法 31 季节变动预测法 之不变季节指数预测法 32 季节变动预测法 之不变季节指数预测法 Winters指数平滑预测法 1 首先 应用公式计算Winters指数平滑预测法的初始值 2 应用公式计算Winters指数平滑预测法的初始值 3 应用公式计算Winters指数平滑预测法的初始值 4 应用公式计算Winters指数平滑预测法的初始值 33 季节变动预测法 之不变季节指数预测法 5 应用公式计算预测值 6 复制单元格内容 得到各时间点上的平滑值 7 复制单元格内容 得到未来一个周期内的预测值 8 根据公式 将 E9 单元格中的内容设置为 C 9 D6 并复制到单元格 E10 E13 依此类推 得到后续的预测值 9 计算百分比误差 得到预测精度指标MAPE值 10 复制单元格内容 得到未来一个周期的预测值 34 季节变动预测法 之不变季节指数预测法 趋势比率法 1 绘制时间序列的 XY散点图 并通过添加趋线的方式得到趋势线方程 2 根据趋势线方程 得到各时间点的趋势值 3 剔除趋势 即用各期时间序列值除以趋势值 4 初步估计季节指数值 对

10、同季节的季节指数值求平均值 5 最终估计季节指数 即用公式计算最终季节指数 35 季节变动预测法 之不变季节指数预测法 6 根据趋势季节月模型公式 得到各期的预测值 7 计算相对百分比误差 从而得到预测精度指标MAPE值 8 利用趋势线方程得到利润额的趋势值 9 根据季节预测模型得到利润额的预测值 36 季节变动预测法 之不变季节指数预测法 Holt Winters指数平滑法 1 利用前两个周期的数据获得初始值 设变量 表示第一个周期和第二个周期各数据的平均值 即 2 初始值可以利用公式得到 3 初始值可以利用公式得到 4 初始值可以利用公式得到 5 应用公式计算得到下一期的值 37 季节变动预测法 之不变季节指数预测法 6 应用公式计算得到下一期的的值 7 应用公式计算得到下一期的的值 8 复制单元格内容 得到逐期 值 9 应用公式得到各期的预测值 38 季节变动预测法 之可变多季节季节指数预测 分类1 可变季节指数预测法2 双季节指数预测法 39 季节变动预测法 之可变多季节季节指数预测 操作步骤 以 可变季节指数预测法 为例 根据下表中的数据 试采用可变季节指数预测法预测2006

《EXCEL预测趋势功能》由会员日度分享，可在线阅读，更多相关《EXCEL预测趋势功能》请在金锄头文库上搜索。