用Excel进行回归分析
一 实验目的
学习用Excel进行回归分析，确定因变量与自变量之间的回归模型；学会根据样本观测数据，估计并检验回归模型及未知参数；在众多的自变量中，判断哪些变量对因变量的影响是显著的，哪些变量的影响是不显著的；根据自变量的已知值或给定值来估计和预测因变量的值，并进一步掌握Excel的基本操作方法 二 实验原理 1 回归分析的概念
由一个或一组非随机变量来估计或预测某一个随机变量的观测值时，所建立的数学模型及所进行的统计分析，称为回归分析。按变量个数的多少，回归分析有一元回归分析与多元回归分析之分，多元回归分析的原理与一元回归分析的原理类似。按变量之间关系的形式，回归分析可以分为线性回归分析和非线性回归分析。 2 回归分析的主要内容
回归分析的内容包括如何确定因变量与自变量之间的回归模型；如何根据样本观测数据，估计并检验回归模型及未知参数；在众多的自变量中，判断哪些变量对因变量的影响是显著的，哪些变量的影响是不显著的；根据自变量的已知值或给定值来估计和预测因变量的值。 三 实验内容
1 利用图表进行分析
例1：某种合成纤维的强度与其拉伸倍数之间存在一定关系，图1所示（“线性回归分析”工作表）是实测12个纤维样品的强度y与相应的拉伸倍数x的数据记录。试求出它们之间的关系。
（1建立“线性回归分析”工作表，将数据及相关内容填入表格，如上图所示。 （2）利用“图表向导”绘制 “XY散点图”。 A. 点击工具栏的
，
B. 选择“XY散点图”日，然后点击下一步，出现一个对话框，在数据区域中输
入“=Sheet2!$B$2:$C$13”， 点击下一步，
C. 在对话框中点击“显示图例”的复选框，取消选择，然后点击“网格线”，
取消“主要网格线”。点击完成
D. 选择表中其中一点点击鼠标右键，在菜单中选择“添加趋势线”点击，出
现对话框，点击选项选择“显示公式”和“显示R平方值”，点击“确定” 即出现所相应的“XY散点图，”如下图所示：
1
2 Excel中的回归分析工作表函数 （1）截距函数
语法：INTERCEPT(known_y's,known_x's)
其中：Known_y's为因变的观察值或数据集合，Known_x's为自变的观察值或数据集合。
（2）斜率函数
语法：SLOPE(known_y's,known_x's)
其中：Known_y's为数字型因变量数据点数组或单元格区域；Known_x's为自变量数据点集合。 （3）决定系数函数
语法：RSQ(known_y's,known_x's)
其中：Known_y's为数组或数据点区域，Known_x's为数组或数据点区域。 （4）估计标准误差函数
语法：STEYX(known_y's,known_x's)
其中：Known_y's为因变量数据点数组或区域，Known_x's为自变量数据点数组或区域。
3 利用工作表函数进行回归分析
例2:在某大学一年级新生体检表中随机抽取10张，得到10名大学生的身高（x）和体重（y）的数据，如图23-3（“身高体重”工作表）所示。 用Excel提供的工作表函数进行相关计算。
（1） 建立“回归分析”工作表，输入相关内容，如下图：
（2）分别计算“截距”、“斜率”、“测定系数”、“估计标准误差”。
2
A．计算截距 选择B12单元格，点击工具栏中
，在其中下拉式菜单中选
择“其他函数”，出现一对话框，选择“或选择函数”中的“统计”，在列表框中选择“INTERCEPT”，然后点击“确定”。出现一对话框，在“Known_y's” 中输入“C2:C11”，在“Known_x's”中输入“Known_x's”，则在B12单元格得出数据“-79.4202”，即截距。 B．计算斜率 选择单元格13，点击工具栏中
，在其中下拉式菜单中选 择
“其他函数”，出现一对话框，选择“或选择函数”中的“统计”，在列表框中选择“SLOPE”，然后点击“确定”。出现一对话框，在“Known_y's” 中输入“C2:C11”，在“Known_x's”中输入“Known_x's”，则在B12单元格得出数据“0.804183”，即斜率。 C．计算决定系数 选择单元格14，点击工具栏中
，在其中下拉式菜单中选
择“其他函数”，出现一对话框，选择“或选择函数”中的“统计”，在列表框中选择“RSQ”，然后点击“确定”。出现一对话框，在“Known_y's” 中输入“C2:C11”，在“Known_x's”中输入“Known_x's”，则在B12单元格得出数据“0.6817018”，即决定系数。 D．计算标准误差 选择单元格15，点击工具栏中
，在其中下拉式菜单中选
择“其他函数”，出现一对话框，选择“或选择函数”中的“统计”，在列表框中选择“STEYX”，然后点击“确定”。出现一对话框，在“Known_y's” 中输入“C2:C11”，在“Known_x's”中输入“Known_x's”，则在B12单元格得出数据“2.8180738”，即标准误差。
（2） 根据计算结果可以得出直线方程：
4 回归分析工具的应用
例3：以例2资料为例，利用回归分析工具进行回归分析。 （1）建立“身高体重”工作表输入相关内容，如下图所示。
3
（2）在工具栏中选择“数据分析”，出现对话框，在“分析工具”列表框中选择“回归”点击“确定”。在对话框“Y值输入区域”输入“$B$2: $B$11”, “X值输入区域”输入“$A$2: $A$11”.再选择“输出区域”，并输入一空白区域，点击“确定”，即出现结果，如下图所示：
5 回归分析工具的输出解释
Excel回归分析工具的输出结果包括3个部分： (1)回归统计表
回归统计表包括以下几部分内容： ①Multiple R（复相关系数R）：是R2的平方根，又称为相关系数，用来衡量变量x和y之间相关程度的大小。本例中R为0.825652，表示二者之间的关系是高度正相关。
②R Square（复测定系数R2）：用来说明自变量解释因变量变差的程度，以测定因变量y的拟合效果。
③Adjusted R Square（调整复测定系数R2）：仅用于多元回归才有意义，它用于衡量加入变量后模型的拟合程度。当有新的变量加入后，即使这一变量同因变量之间不相关，未经修正的R2也要增大，修正的R2仅用于比较含有同一个因变量的各种模型。
④标准误差：用来衡量拟合程度的大小，也用于计算与回归相关的其他统计量，此值越小，说明拟合程度越好。
⑤观测值：用于估计回归方程的数据的观测值个数。 （2）方差分析表
方差分析表的主要作用是通过F检验来判断回归模型的回归效果。表中“回归分析”行计算的是估计值同均值之差（-）的各项指标；“残差”行是用于计算每个样本观察值与估计值之差（-）的各项指标；“总计”行用于计算每个值同均值之差（-）的各项指标。第二列df是自由度，第三列SS是离差的平方和，第四列MS是均方差，它是离差平方和除以自由度，第五列是F统计量，第六列Significance F是在显著性水平下的Fα的临界值 。 6 多元回归分析
例4：有一个工厂会计部门在估计每月管理费y时，用工人的劳动日数x1与机器的开工台数x2作自变量，现将当年10个月的数据搜集起来，如图（“多元回
4
归分析”工作表）所示，估计y对x1与x2的线性回归方程(α=0.05)。 （1）建立“多元回归分析”工作表 （2）打开“回归”对话框。
（3）在“Y值输入区域”中输入“D1:D11”，在“X值输入区域”中输入“B1:C11”；选择“标志”，置信度默认；在“输出选项”中选择“输出区域”，在其右边输入“A12”，单击“确定”按钮输出结果，如下图所示。
（3） 列出二元回归方程
四 实验结论与分析 通过本次实验，进一步掌握了Excel的操作方法，学会了用Excel进行回归分析，确定因变量与自变量之间的回归模型；学会根据样本观测数据，估计并检验回归模型及未知参数；在众多的自变量中，判断哪些变量对因变量的影响是显著的，哪些变量的影响是不显著的；根据自变量的已知值或给定值来估计和预测因变量的值。
5
