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

1、实验指导书用Excel进行长期趋势分析（6）一、实验目的通过具体实例的演示和操作实验，帮助学生加深有关长期趋势分析的内容。此实验，要求掌握用 Excel中的“数据分析”等工具进行趋势方程中参数的拟合。二、实验类型（含验证型、设计型或综合型）综合性实验三、实验资料书例，后面的具体步骤中有范例。四、实验原理最小平方法和函数法。五、实验操作步骤用Excel拟合趋势方程有两种方法， 一是利用函数计算，如“INTERCEP函数”和“SLOPE函数”、“LINEST” 函数；另一种是利用“数据分析”功能回归分析宏计算。资料1 根据下表资料，拟合直线趋势方程，并预测 2010年一季度成交量。时间序号t成交量

2、 y2002 一季度158二季度260三季度363四季度4642003 一季度559二季度661三季度765四季度8692004 一季度970二季度1068三季度1173四季度12752005 一季度1376二季度1473三季度1579四季度1680（一）利用“数据分析”功能回归分析宏拟合趋势方程步骤第 1 步：将数据输入Excel 工作表中。第2 步：选择“工具”下拉菜单，在“工具”菜单中选择“数据分析”选项。第3 步：在“数据分析”中选择“回归”。第 4 步：当出现对话框时，在“输入 Y 的区”方框内键入 C2 ： C17 ，在“输入 X 的区”方框内键入B2: B17,在“输出选项”中选

3、择输出区域（这里我们选择“新工作表”）。 ：单击“确定”，即得到下图所示的结果。有关指标的解释： 22上图中回归统计部分给出了判定系数R、调整后的R、估计标准误差等；方差分析表部分给出的显著水平F值表明回归方程是显著的；最下面的一部分是 a=56.1 , b=1.436765 。以及参数a,b的标准差、t检 验的统计量、p-值、下限95%和上限95%给出了参数a，b置信区间。比如，我们有 95%的把握确信，a在 53.52154 和 58.67846 之间，b 在 1.170107 和 1.703432 之间。 一 S、所以，该例题中得到的回归万程为：yc= 56.1+1.436765 t ,

4、回归估计标准误为：yx 2.292502 。预测 2006 年一季度成交量为：yc=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 ）。第三步，接下来，计算斜率b。在图1的对话框中选择“ LINEST”或“SLOPE”，分别入图3、图4所示。输入相应的y、 t 值单元格范围，即可产生计算结果1.436764706 。最后，趋势方程为：yc= 56.1+1.436765 t六、练习1 、上述两类方