6、用Excel进行长期趋势分析

实验指导书-------用Excel 进行长期趋势分析（6）

一、实验目的

通过具体实例的演示和操作实验，帮助学生加深有关长期趋势分析的内容。 此实验，要求掌握用Excel 中的“数据分析”等工具进行趋势方程中参数的拟合。 二、实验类型（含验证型、设计型或综合型）

综合性实验 三、实验资料

书例，后面的具体步骤中有范例。 四、实验原理

最小平方法和函数法。

五、实验操作步骤

用Excel 拟合趋势方程有两种方法，一是利用函数计算，如“INTERCEP 函数”和“SLOPE 函数”、“LINEST ”函数；另一种是利用“数据分析”功能回归分析宏计算。

［资料］ 根据下表资料，拟合直线趋势方程，并预测2010年一季度成交量。

（一） 利用“数据分析”功能回归分析宏拟合趋势方程 ［步骤］

第1步：将数据输入Excel 工作表中。

第2步：选择“工具”下拉菜单，在“工具”菜单中选择“数据分析”选项。 第3步：在“数据分析”中选择“回归”。

第4步：当出现对话框时，在“输入Y 的区域”方框内键入C2：C17，在“输入X 的区域” 方框内键入B2：B17，在“输出选项”中选择输出区域(这里我们选择“新工作表”)。

［结果］：单击“确定”，即得到下图所示的结果。

有关指标的解释：

上图中回归统计部分给出了判定系数2

R 、调整后的2

R 、估计标准误差等；方差分析表部分给出的显著水平F 值表明回归方程是显著的；最下面的一部分是a=56.1，b=1.436765。以及参数b a ,的标准差、t 检验的统计量、p-值、下限95%和上限95%给出了参数b a ,置信区间。比如，我们有95%的把握确信，a 在53.52154和58.67846之间，b 在1.170107和1.703432之间。

所以，该例题中得到的回归方程为：y c = 56.1+1.436765 t ，回归估计标准误为：

yx S 2.292502。预测

2006年一季度成交量为：y c=56.1+1.436765*17=80.55（万kg）。

（二）利用Excel中的函数功能求趋势方程参数

上例数据。

第一步，单击“插入”下拉菜单，选择“函数”选项，如下图1。.在“或选择类别”中选择“统计”。

第二步，在“选择函数”对话框中选中“INTERCEPT”，出现图2所示对话框。在“Known_y’s”中输入y（实际值）的单元格范围：c2:c17；在“Known_x’s”（x此处即t）输入t值单元格范围b2:b17。对话框中即产生了计算结果56.1（即截距a=56.1）。

图1 图2

第三步，接下来，计算斜率b。在图1的对话框中选择“LINEST”或“SLOPE”，分别入图3、图4所示。输入相应的y、t 值单元格范围，即可产生计算结果1.436764706。

图3 图4

最后，趋势方程为：y c= 56.1+1.436765 t

六、练习

1、上述两类方法中都还可以根据简捷法计算（赋t值使∑=0

t），大家不妨一试。

2、教材P245页，13题。