(1.4)--EXCEL数据管理与分析高阶实验.pdf

实验1一元线性回归模型分析

实验目的

1、初步掌握利用图表、函数、公式、数据分析等方法进行一元线性回归模型分析。

2、初步掌握利用EXCEL软件的图表及数据分析功能完成线性回归模型参数的估计。

3、掌握利用EXCEL函数的功能确定一元线性回归模型的参数的估计。

实验任务

选取最近五年的上证综合指数与深成指数数据进行两者之间的线性分析，本实验采用了五种方法进

行线性方程的参数估计。

1、利用散点图上的趋势图确定一元线性回归模型的方程

2、使用截距函数和斜率函数确定一元线性回归模型的方程

3、使用LINEST函数确定一元线性回归模型的方程

4、使用数据分析中的回归分析来确定一元线性回归模型的方程

5、利用回归参数的计算公式进行动态的计算来确定一元线性回归模型的方程

实验步骤

1、利用散点图上的趋势图确定一元线性回归模型的方程

可直接在数据的散点图上作趋势图，从而确定线性回归方程。

（1）打开“上证指数与深成指数表（原始表）.xlsx”工作薄，在“视图”选项卡中将首行、首列进

行窗口冻结。

（2）选中B、C两列数据，插入散点图，并更改坐标轴格式，更改横坐标轴的最小值为2500，最大值

为5500，主要刻度值为500；纵坐标最小值为7000，最大值为19000，主要刻度值为1000（为

了美化此图形，根据实际数据自行设置坐标的起止及间隔）；散点图的其他内容参照图1

图1

（3）选中该散点图表，在“图表工具”动态选项卡的“布局”中添加趋势线，在趋势线选项中选择“线

性”，并选择“显示公式”及“显示R平方值”，如图2所示。

1

图2

（4）根据步骤（3）可以得到上证综合收盘指数X与深成指数收盘指数Y之间的线性关系表示为y=

3.3502x+311.74。Y与X之间呈正相关关系。R²=0.9702，说明显著性不明显。

2、使用截距函数和斜率函数确定一元线性回归模型的方程

（1）分别在E2和E3单元格内输入“斜率”和“截距”，并利用SLOPE函数计算线性方程的斜率。在

F2单元格内插入斜率函数SLOPE（参数1，参数2），其中参数1为方程中变量Y的数值范围C2:C464，

参数2为方程中变量X的数值范围B2:B464。

（2）在F3单元格内插入截距函数INTERCEPT（参数1，参数2），参数的含义同斜率函数。

（3）确定斜率和截距后插入一文本框并在该文本框中写出该线性方程，如图3所示。

图3

3、使用数据分析中的回归分析来确定一元线性回归模型的方程

（1）点击EXCEL“文件”选项卡——“选项”——“加载项”，再点击最下方的“转到”，在加载宏的

对话框中选中你需要的加载项，本实验需要选择“分析工具库”即可在“数据”选项卡中获得“数

据分析”这项功能，如图4及图5所示。

2

图4

图5

（2）使用数据分析中的回归分析来确定一元线性回归模型的方程，首先需要利用步骤1中的方法制作

散点图并添加趋势线，以确定上证综合指数与深成指之间存在线性关系。其次，单击“数据”选项卡中

“分析”组的“数据分析”功能，并选中“回归”分析，在回归分析对话框中选中X和Y值的区域，并

设定置信度为95%及选中标准残差项。回归分析数据输出到一个新的工作表中，并将此工作表重命名为

“数据回归分析表”，如图6及图7所示。

3

图6

图7

（3）在