基于Excel的一元线性回归方程的建立

黄基廷　赵丽棉

摘要：通过实例说明在Excel平台上建立一元线性回归方程常用的方法和步骤，说明了借助Excel，几乎可以完成所有统计数据分析与处理，且利用不同的方法建立的一元线性回归方程都是一样的，我们可以根据需要选择不同的方法。

关键词：Excel平台；一元线性；回归方程

中图分类号：0212.4；G642.0 文献标志码：A 文章编号：1674-9324（2015）05-0203-02

在社会经济现象中，数量之间的变化常常是按比例变化，而且许多现象非线性的变化在较短的时间内也近似于线性变化，可以利用线性方法分析，这给数学运算带来很大方便，所以通过建立简单的一元线性回归方程进行回归分析是统计学中常用的方法。本文将通过实例说明如何在Excel平台上利用不同的方法建立一元线性回归方程。

1 一元线性回归方程的测定

测定两组变量和是否存在线性关系，可以通过二条途径：

一是画散点图，看数据点是否是大致沿直线分布；

二是计算两组变量的相关系数：

一般地，当0.8≤|r|<1时，认为x与y之间存在高度线性相关，可以建立一元线性回归方程。

2 未知参数的确定

未知参数的确定最常用的方法是最小二乘法，即实际值y与相应估计值的离差平方和最小，设一元线性回归方程为=a+bx，则Q=∑（y-a-bx）2，分别令Q对a，b的偏导数等于0得：

∑y=na+b∑x∑xy=a∑x+b∑x2 （2）

解此方程组即可得a，b的估计值。

3 基于Excel的一元线性回归方程的建立

例 为研究某一化学反应过程中，温度x（℃）对产品得率Y（%）的影响，测得数据并输入到Excel工作表中（图1）。

这里x是普通变量，Y是随机变量，求Y关于x的回归方程。

3.1 回归方程的测定

3.1.1 画散点图

第一步，拖动鼠标选定数值区域A2：B11，不包括数据上面的标志项。

第二步，选择“插入”菜单的“图表”子菜单，进入图表向导。

第三步，选择“图表类型”为“散点图”，然后单击“下一步”。

第四步，继续单击“下一步”，选择“标题”下的子项“图表标题”，在其中输入“产品得率与温度”，在“数值（X）轴（A）”子项中输入“温度”，在“数值（Y）轴（V）”子项中输入“温度”。单击“完成”，即生成图2所示结果。

从散点图可以看出，产品得率Y与温度之间存在着正的线性相关关系，可以计算它们的相关系数来确定两者相关的密切程度。

3.1.2 计算相关系数

第一步，用鼠标单击C2单元，输入“=A2^2”，回车得第一个x的平方值，然后将鼠标指针移至C2单元格右下角的小方块（填充柄）上，当指针变成+形时按住鼠标左键往下拖拽，至C11单元格放开鼠标，得所有x的平方值，类似地求出所有y的平方值。

第二步，在E2单元中输入“=A2*B2”，回车得第一个x与y的积，类似求出所有x与y的积。

第三步，利用工具栏中的“∑”，求得∑x=1450，∑y=670，∑xy=101570，∑x2=218500，∑y2=47225。

第四步，将数据代入公式（1）求得r=0.998129

结果表明产品得率与温度之间存在高度线性正相关关系，可以建立一元线性回归方程。

3.2 回归方程的建立

3.2.1 用常规方法建立一元线性回归方程

利用3.1.2算出的数据和公式（2），求得：b=0.4830，a=-2.7394即可确定回归方程为=-2.7394+0.483x

3.2.2 用“添加线性趋势线”建立一元线性回归方程

接3.1.1中的第四步，在图2中，用鼠标对准任一数据点，单击右键，选择“添加趋势线”，在“类型”选项卡中选择“线性”，在“选项”选项卡中单击“显示公式”和“显示R平方”复选框，再单击“确定”即可得如图3结果：

结果表明所求的回归方程也是：

=-2.7394+0.483x，R2=0.9963

3.2.3 运用数据分析工具建立一元线性回归方程

在Excel的数据分析工具中，有一个专用于进行回归分析的工具。使用此工具，可以更加方便快捷准确地进行回归分析，并能提供更多的数据信息。

第一步，用鼠标点击工作表中待分析数据的任一单元格，选择“工具”菜单的“数据分析”子菜单，用鼠标双击“回归”选项，进入回归对话框。

第二步，在回归对话框中，在“y值输入区域”框中输入B2：B11，在“x值输入区域”框中输入A2：A11，选中“标志”复选框，在“输出区域”中输入D2，选中“残差”、“标准残差”、“线性拟合图”等复选框。

第三步，单击“确定”按钮，即在以D2为起点的右边空白区域给出结果（图4）。

结果表明，趋势方程为：=-2.7394+0.483x

从判定系数看，在温度对产品得率的影响中，有99%可由该回归方程解释。从检验看，回归系数的p值远远小于0.05，表明是显著的。从F检验看，Significance F为5.35E-11，表明该模型通过了5%的显著性检验，模型整体也是显著的。所以回归方程是合适的。

从以上过程可看到，借助Excel，几乎可以完成所有统计数据分析与处理，且利用不同的方法建立的一元线性回归方程都是一样的，我们可以根据需要选择不同的方法。

参考文献：

[1]盛骤，等.概率论与数理统计[M].北京：高等教育出版社，2001：294-313.

[2]范秀荣，等.统计学原理[M].北京：高等教育出版社，2010：197-203.

[3]王剑武，等.统计学[M].长沙：湖南师范大学出版社，2014：192-198.

[4]黄应绘，等.统计学实验[M].成都：西南财经大学出版社，2009：111-121.

[5]马军.excel统计分析典型实例[M].北京：清华大学出版社，2009：221-244.