实验 用EXCEL进行相关与回归分析

《实验 用EXCEL进行相关与回归分析》由会员分享，可在线阅读，更多相关《实验 用EXCEL进行相关与回归分析（15页珍藏版）》请在金锄头文库上搜索。

1、实验二实验二 用用 EXCEL 进行相关与回归分析进行相关与回归分析实验目的：用 EXCEL 进行相关与回归分析实验内容：1、 相关分析2、回归分析实验步骤：采用下面的例子进行相关和回归分析。【例例 1】 10 个学生身高和体重的情况如下表，要求对身高和体重作相关和回归分析。学生身高（公分）体重（公斤）12345678910171167177154169175163152172160535664495566524758501. 用用 EXCEL 进行相关分析进行相关分析首先把有关数据输入 EXCEL 的单元格中，如图 1-20 所示。用 EXCEL 进行相关分析有散点图、计算相关系数，另一种是

2、利用相关分析宏。图图 1-20 EXCEL 数据集数据集（1）作散点图（2）利用函数计算相关系数在 EXCEL 中，提供了两个计算两个变量之间相关系数的方法，CORREL 函数和PERSON 函数，这两个函数是等价的，这里介绍用 CORREL 函数计算相关系数：第一步：单击任一个空白单元格，单击插入菜单，选择函数选项，打开粘贴函数对话框，在函数分类中选择统计，在函数名中选择 CORREL，单击确定后，出现 CORREL 对话框。第二步：在 array1 中输入 B2：B11，在 array2 中输入 C2：C11，即可在对话框下方显示出计算结果为 0.896，如图 1-21 所示。图图 1-2

3、1 CORREL 对话框及输入结果对话框及输入结果（3）用相关系数宏计算相关系数第一步：单击工具菜单，选择数据分析选项，在数据分析选项中选择相关系数，弹出相关系数对话框，如图 1-22 所示：图图 1-22 相关系数对话框相关系数对话框第二步：在输入区域输入$B$1:$C$1，分组方式选择逐列，选择标志位于第一行，在输出区域中输入$E$1，单击确定，得输出结果如图 1-23 所示。图图 1-23 相关分析输出结果相关分析输出结果在上面的输出结果中，身高和体重的自相关系数均为 1，身高和体重的相关系数为0.896，和用函数计算的结果完全相同。2. 用用 EXCEL 进行回归分析进行回归分析EXC

4、EL 进行回归分析同样分函数和回归分析宏两种形式，其提供了 9 个函数用于建立回归模型和预测。这 9 个函数分别是：INTERCEPT返回线性回归模型的截距SLOPE返回线性回归模型的斜率RSQ返回线性回归模型的判定系数FORECAST返回一元线性回归模型的预测值STEYX计算估计的标准误TREND计算线性回归线的趋势值GROWTH返回指数曲线的趋势值LINEST返回线性回归模型的参数LOGEST返回指数曲线模型的参数用函数进行回归分析比较麻烦，我们这里介绍使用回归分析宏进行回归分析。第一步：单击工具菜单，选择数据分析选项，出现数据分析对话框，在分析工具中选择回归，如图 1-24 所示。图图

5、1-24 数据分析对话框数据分析对话框第二步：单击确定按钮，弹出回归对话框，在 Y 值输入区域输入$B$2：$B$11，在 X值输入区域输入$C$2：$C$11，在输出选项选择新工作表组，如图 1-25 所示。图图 1-25 回归对话框回归对话框第三步：单击确定按钮，得回归分析结果如图 1-26 所示。图图 1-26 EXCEL 回归分析结果回归分析结果在上面的输出结果中，第一部分为汇总统计，MultipleR 指复相关系数，RSquare 指判定系数，Adjusted 指调整的判定系数，标准误差指估计的标准误，观测值指样本容量；第二部分为方差分析，df 指自由度，SS 指平方和，MS 指均方

6、，F 指 F 统计量，Significance of F 指 p 值；第三部分包括：Intercept 指截距，Coefficient 指系数，tstat 指 t 统计量。实验三实验三 用用EXCEL进行预测进行预测实验目的：用实验目的：用 EXCEL 进行预测进行预测 实验内容：实验内容：一、用移动平均法进行预测一、用移动平均法进行预测二、用指数平滑法进行预测二、用指数平滑法进行预测三、趋势预测法进行预测三、趋势预测法进行预测实验步骤：实验步骤： 【例例 13-13】：某煤矿某年 1-11 月份采煤量如下表： 月份产量月份产量1 2 3 4 5 69.03 9.06 9.12 8.73 8.

7、94 9.307 8 9 10 11 129.15 9.36 9.45 9.30 9.24一、用移动平均法进行预测一、用移动平均法进行预测具体步骤： 第一步：将原始数据录入到单元格区域 A2：A12，如图 13-31 所示：图图 13-31EXCEL 数据集数据集 第二步：选择菜单条上的“工具”“数据分析”命令，弹出如图 13-32 所示的对话框：图图 13-32 数据分析对话框数据分析对话框 第三步：在“分析工具”框中选择“移动平均” ，单击“确定”按钮，弹出移动平均对 话框，相应作如下输入，即可得到如图 13-33 所示的对话框：（1） 在“输出区域”内输入：$A$2：$A$12，即原始数

8、据所在的单元格区域。（2） 在“间隔”内输入：3，表示使用三步移动平均法。 （3） 在“输出区域”内输入：B2，即将输出区域的左上角单元格定义为 B2。 （4） 选择“图表输出”复选框和“标准误差”复选框。13-33 移动平均对话框移动平均对话框 第四步：单击“确定”按钮，便可得到移动平均结果，如图 13-34 所示： 分析：在图中，B4：B12 对应的数据即为三步移动平均的预测值；单元格区域 C6：C12 即为标准误差。13-34 移动平均分析结果移动平均分析结果二、用指数平滑法进行预测：二、用指数平滑法进行预测：第一步：将原始数据输入到单元格 B2：B12； 第二步：选择菜单条上的“工具”

9、“数据分析”命令，弹出如图 13-35 所示的对话框：图图 13-35 数据分析对话框数据分析对话框 第三步：在“分析工具”中选择“指数平滑” ，单击“确定”按钮，弹出一个对话框， 作相应输入，即可得到如图 13-36 所示的对话框；13-36 指数平滑对话框指数平滑对话框 第四步：单击“确定”按钮，即可得到指数平滑结果，如图 13-37 所 示：图图 13-37 指数平滑结果指数平滑结果三、趋势预测法进行预测三、趋势预测法进行预测第一步：把相关数据输入到 EXCEL 中，其中月份输入 A1A11 单元格，月产量输入 B1B11 单元格，如图 13-38 所示：图图 13-38EXCEL 数据

10、集数据集 第二步：在工作表中选择一个空的单元格。在这里我们选择 D2 单元格。 第三步：选择插入下拉菜单。 第四步：选择函数选项。 第五步：当函数对话框出现时：在函数类别框中选择统计，在函数名字中 选择 FORECAST(预测)，如图 13-39 所示：图图 13-39 粘贴函数对话框粘贴函数对话框 第六步：单击确定按钮，出现预测对话框，在 x 中输入 12，在 know-ys 中 输入 B1：B11，在 know-xs 中输入 A1：A11，如图 13-40 所示：图图 13-40FORCAST 对话框对话框 第七步：单击确定按钮，预测结果出现在 D2 单元格中，如图 13-41 所示：图图

11、 13-41 趋势预测法预测结果趋势预测法预测结果实验四实验四 用用 EXCEL 进行时间序列分析进行时间序列分析实验目的：用实验目的：用 EXCELEXCEL 进行时间序列分析进行时间序列分析 实验步骤：实验步骤： 一、测定增长量和平均增长量一、测定增长量和平均增长量【例例 13-5】：根据 1995-2001 年河北省国内生产总值，计算逐期增长量、 累计增长量和平均增长量。如图 13-16图图 13-16 用用 EXCEL 计算增长量和平均增长量资料及结果计算增长量和平均增长量资料及结果 计算步骤如下： 第一步：在 A 列输入年份，在 B 列输入国内生产总值。 第二步：计算逐期增长量：在

12、C3 中输入公式：=B3-B2，并用鼠标拖曳将 公式复制到 C3：C8 区域。 第三步：计算累计增长量：在 D3 中输入公式：=B3-$B$2，并用鼠标拖曳 公式复制到 D3：D8 区域。 第四步：计算平均增长量（水平法）：在 C10 中输入公式：=（B8-B2） /5，按回车键，即可得到平均增长量。二、测定发展速度和平均发展速度二、测定发展速度和平均发展速度【例例 13-6】：以 1995-2001 年河北省国内生产总值为例，说明如何计算定 基发展速度、环比发展速度和平均发展速度。如图 13-17图图 13-17 用用 EXCEL 计算发展速度和平均发展速度资料及结果计算发展速度和平均发展速

13、度资料及结果 第一步：在 A 列输入年份，在 B 列输入国内生产总值。 第二步：计算定基发展速度：在 C3 中输入公式：=B3/$B$2，并用鼠标拖曳将公式复制到 C3：C8 区域。 第三步：计算环比发展速度：在 D3 中输入公式：=B3/B2，并用鼠标拖曳 将公式复制到 D3：D8 区域。 第四步：计算平均发展速度（水平法）：选中 C10 单元格，单击插入菜单， 选择函数选项，出现插入函数对话框后，选择 GEOMEAN（返回几何平均值） 函数，在数值区域中输入 D3：D8 即可。三、计算长期趋势三、计算长期趋势【例例 13-7】：我们用某企业某年 12 个的总产值资料来说明如何用移动平均 法

14、计算长期趋势。如图 13-18图图 13-18 用用 EXCEL 计算长期趋势资料及结果计算长期趋势资料及结果 第一步：在 A 列输入月份，在 B 列输入总产值。 第二步：计算三项移动平均：在 C3 中输入“=（B2+B3+B4）/3” ，并用鼠 标拖曳将公式复制到 C3：C12 区域。 第三步：计算四项移动平均：在 D4 中输入“=SUM（B2：B5）/4” ，并用 鼠标拖曳将公式复制到 D4：D12 区域。 第四步：计算二项移正平均数：在 E4 中输入“=（D4+D5）/2” ，并用公式 拖曳将公式复制到 E4：E11 区域。四、计算季节变动四、计算季节变动【例例 13-8】：利用某种商品

15、五年分季度的销售额资料，说明如何用移动平 均趋势剔除法测定季节变动。如图 13-19图图 13-19 用用 EXCEL 计算季节变动资料计算季节变动资料 第一步：按图上的格式在 A 列输入年份，在 B 列输入季别，在 C 列输入销 售收入。 第二步：计算四项移动平均：在 D3 中输入“=SUM（C2：C4）/4” ，并用 鼠标拖曳将公式复制到 D3：D19 区域。 第三步：计算趋势值（即二项移动平均）T：在 E4 中输入“=（D3+D4）/2” ，并用鼠标拖曳将公式复制到 E4：E19 区域。第四步：剔除长期趋势，即计算 Y/T：在 F4 中输入“=C4/E4” ，并用鼠标 拖曳将公式复制到

16、F4：F19 区域。 第五步：重新排列 F4：F19 区域中的数字，使同季的数字位于一列，共排 成四列。 第六步：计算各年同季平均数：在 B29 单元格中输入公式： =average(B25:B28)；在 C29 中输入公式=average(C25:C28)；在 D29 中输入公式 =average(D24:27)；在 E29 中输入公式=average(E24:E27)。 第七步：计算调整系数：在 B31 中输入公式：=4/sum(B29:E29) 第八步：计算季节比率：在 B30 中输入公式：=B29*$B$31，并用鼠标拖曳 将公式复制到单元格区域 B30：E30，就可以得到季节比率的值，具体结果见图 13-20：图图 13-20 用用 EXCEL 计算季节变动结果计算季节变动结果指数分析指数分析实验目的：用 EXCEL 进行指数分析 实验步骤： 指数分析法是研究社会经济现象数量变动情况的一种统计分析法。指数有 总指数与平均指数之分，在这一节我们介绍如何用 Excel 进行指数分析与因素 分析。一、用一、用 Excel 计算总指