用Excel进行统计趋势预测分析

用Excel进行统计趋势预测分析

在统计工作中运用电脑技术，不仅仅需要使用专门的统计软件，还应当使用一些其他软件为我们的统计工作服务，excel以强大的处理表格、图表和数据的功能被广泛地应用于统计领域。预测分析是统计数据分析工作中的重要组成部分之一，Excel 中不仅可以用函数，也可以用“趋势线”来进行趋势预测分析。下面介绍一下具体使用方法。

一、函数法

1、简单平均法

简单平均法非常简单,以往若干时期的简单平均数就是对未来的预测数。

例如，某企业今年1－6月份的各月实际销售额资料如图1。在c9中输入公式av erage（b3：b8）即可预测出7月份的销售额。

图1

2、简单移动平均法

简单移动平均法预测所用的历史资料要随预测期的推移而顺延。仍用上例，我们假设预测时用前面3个月的资料，我们可以用两种方法实现用该法预测销售额：一是

在d6输入公式average(b3:b5)，拖曳d6到d9，这样就可以预测出4－7月的销售额；二是运用excel的数据分析功能，选取工具菜单中的数据分析项（如没有此项，则选择加载宏来加载此项），然后选择移动平均，在输入区域输入b3：b8，输出区域输入d4：d9，也可以得到相同的结果。

3、加权移动平均法

加权移动平均法在简单移动平均法的基础上对所用的资料分别确定一定的权数，算出加权平均数即为预测数。还是用上例，在e6输入公式sum(b3＊1＋b4＊2＋b5＊3) /6，把e6拖曳到e9即可预测出4－7月的销售额。

4、指数平滑法

指数平滑法是通过导入平滑系数对本期的实际数和本期的预测数进行加权平均计算后作为下期预测数的一种方法。仍用上例（b2，f3的数据都为1月份的预测销售额），假设平滑系数为0.3，我们也可以用两种方法实现。用该法预测销售额：一是在f4输入公式0.3＊b3＋0.7＊f3，把f4拖曳到f9即可；二是运用数据分析功能，在工具菜单中选取数据分析项后，选择指数平滑，在输入区域输入b2：b9，阻尼系数输入0.7，输出区域输入f2：f11，也可得到2－7月份的预测销售额。

5、直线回归分析法

直线回归分析法就是运用直线回归方程来进行预测。手工情况下进行直线回归分析需要进行大量的计算，而利用excel中的forecast函数能很快地计算出预测数。我们还是用上面的例子，在g9输入公式forecast(a9,b3:b8,a3:a8)，就可得到7月份的预测销售额。

6、曲线回归分析法

曲线回归分析法就是运用二次或二次以上的回归方程所进行的预测，如抛物线、指数曲线、双曲线等曲线形式。本文仅以指数曲线为例来说明预测的过程。

例如，某企业近5年的销售额资料如图2所示。我们首先可用折线图反映实际值如图2，从折线图中可看出，该企业的销售额呈现超常规的指数增长，可以选用指数模型来拟合该增长类型。在c7中输入公式growth(b2:b6,a2:a6,a7)，即可得到第6年的预测销售额。

图2

二、“趋势线”法

Excel图表中的“趋势线”是一种直观的预测分析工具，通过这个工具，用户可以很方便地直接从图表中获取预测数据信息。

“趋势线”法的主要类型有线性、对数、多项式、乘幂、指数和移动平均等。选择合适的趋势线类型是提升真趋势线的拟合程度、提高预测分析的准确性的关键。

１、线性趋势线：适合增长或降低的速率比较稳定的数据情况，数据点表现近似于一直线，如某企业产量与用电量数据。

２、对数趋势线：适合增长或降低幅度一开始比较快逐渐趋于平缓的数据财政部，如年龄与身高数据。

３、多项式趋势线：适合增长或降低幅度波动较多的数据，如CPI指数波动情况。

４、乘幂趋势线：适合增长或降低速度持续增加且增加幅度比较恒定的数据情况，如自由落体的时间与高度数据。

５、指数趋势线：适合增长或降低速度持续增加且增加幅度越来越大数据情况，如某地不同高度的大气压数据。

具体操作步骤如下：

选定数据区域→图表向导→选择“XY散点图”→在子图表类型中选择“平滑

线散点图”→完成后进行适当格式设置→在图表中选中数据系列→右键单击

数据系列→选择“添加趋势线”→在“类型”选项卡中选择相应相应趋势线

（如对数、指数、线性、多项式、移动平均、乘幂等）→在“选项”选项卡

中勾选“显示公式”→确定。如下图3所示：

图3