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

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

一、实验目的

通过具体实例的演示和操作实验，帮助学生加深有关长期趋势分析的内容。

此实验，要求掌握用Excel中的“数据分析”等工具进行趋势方程中参数的拟合。

二、实验类型（含验证型、设计型或综合型）

综合性实验

三、实验资料

书例，后面的具体步骤中有范例。

四、实验原理

最小平方法和函数法。

-

五、实验操作步骤

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

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

时间序号t成交量y

2002 一季度

二季度

三季度

四季度[

1

2

3

4

58

60

63

64

2003 一季度

二季度

}

三季度

四季度5

6

7

8

59

61

65

69

<

2004 一季度

二季度

三季度

四季度9

10

11

12

70

68

【

73

75

2005 一季度

二季度

三季度

四季度13

14

15

16

%

76

73

79

80

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

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

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

&

第3步：在“数据分析”中选择“回归”。

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

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

有关指标的解释：

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

R、调整后的2R、估计标准误差等；方差分析表部分给出的显著

水平F值表明回归方程是显著的；最下面的一部分是a=，b=。以及参数

b

a,的标准差、t检验的统计量、p-

值、下限95%和上限95%给出了参数

b

a,置信区间。比如，我们有95%的把握确信，a在和之间，b在和之

间。

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

yx

S

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

*

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

上例数据。

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

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

图1 图2

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

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

六、练习

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

t），大家不妨一试。

2、教材P245页，13题。