Excel 在相关与回归分析中的应用

北京市石景山区业余大学 张俊荣

相关分析与回归分析是研究现象之间数量关系的两种重要的数据分析技术。Excel 软件提供了数据分析的可视化操作界面，使相关与回归分析工作简单化。本文从实际问题入手，分析了相关分析与回归分析的统计学理论与方法，并对如何应用Excel 软件进行相关分析、回归分析与预测进行了研究。

当今社会，人们越来越重视数据分析。拥有数据思维，分析数据间的逻辑关系，就会为组织带来商业价值。因此，正确分析和运用数据，已经成为职业人士必备的工作技能。数据分析曾经是一项专业性很强的工作，工作任务繁重，需要具备统计学的功底，掌握经济学的理论，进行复杂的核算与分析。现在，Excel 软件将烦琐的数据分析核算过程进行了封装，提供了可视化的操作界面，使数据分析工作简单化。普通员工通过学习和训练，使用Excel，也能进行数据分析，并进行经济预测。相关分析与回归分析是数据分析的主要方法，两者紧密结合，相互补充。相关分析只研究变量之间相关的方向和程度，不能推断相关的具体形式，也无法从一个变量的变化来推测另一个变量，需要依靠回归分析来表现相关的具体形式。而回归分析则需要依靠相关分析来表现变量之间数量变化的相关程度，只有当变量之间存在高度相关时，进行回归分析探寻相关的具体形式才有意义。因此，在实际应用中，为了达到分析和研究的目的，往往需要把相关分析和回归分析结合起来使用。

1 相关分析

1.1 问题的提出

两个变量之间是否相关，相关的程度如何？比如：水上世界的娱乐人数与温度是否相关，关联程度怎么样？

1.2 统计学理论与方法

相关分析是检验衡量两变量之间关联强度的分析过程。按相关关系的方向划分，两变量之间的相关关系可分为三种情况：（1）正相关。当一个变量增加/减少时，另一个变量也相应地增加/减少。（2）负相关。一个变量增加/减少时，另一个变量却减少/增加。（3）无相关。两变量是独立的，互不影响。

1.3 应用Excel 进行相关分析

首先，做个说明：论文中，软件操作的表述均以Excel 2016 为蓝本。

（1）散点图法。在Excel 中，先选择要分析的数据，然后点击“插入”选项卡中“图表”区的“插入散点图(X、Y)或气泡图”，在弹出的列表中点击“散点图”，就得到了相关图。相关图反映了所选数据之间的相关性质与相关方向，我们可以根据相关图，分析所选数据的相关关系。（2）计算相关系数。我们可以使用Correl()函数计算相关系数。例如：某水上世界针对不同温度对娱乐人数进行了统计，数据如图1所示，要求：计算娱乐人数与温度之间的相关系数。我们可以在D2 单元格中输入“=CORREL(A2∶A10,B2∶B10)”，回车后便计算出了相关系数，计算结果为0.9945，说明娱乐人数与温度之间存在高度正相关关系。

我们也可以使用Excel 的数据分析功能计算相关系数。点击“数据”选项卡中的“数据分析”按钮，在弹出的对话框中选择“相关系数”，然后点击“确定”按钮，弹出“相关系数”对话框，在“输入区域”中输入“$A$1∶$B$10”，选择“标志位于第一行”，在“输出区域”，输入“$D$4”，点击“确定”按钮，便计算出了相关系数，如图1 所示。可见，计算结果与函数法相同。

图1 统计数据与计算的相关系数结果Fig.1 Statistical data and calculated correlation coefficient results

2 回归分析

2.1 问题的提出

某公司研发出了一种新氮肥，针对不同施肥量对农作物产量的影响进行了试验，试验数据如图2 所示。分析农作物产量与施肥量之间的关系，并预测施肥量为5.2g 时农作物的产量。

图2 试验数据与回归分析结果Fig.2 Test data and regression analysis results

2.2 统计学理论与方法

回归分析是一种预测性的建模技术，研究的是因变量和自变量之间的因果关系。例如，道路交通事故数量与司机的鲁莽驾驶之间的因果关系。进行回归分析，需先建立回归模型，再根据实测数据求解模型的各个参数，然后评价回归模型是否能够很好地拟合实测数据。如果能够很好地拟合，就可以根据自变量作进一步的预测。回归分析最简单的形式是一元线性回归，它有一个因变量和一个自变量，因此可用线性方程y=ax+b 去拟合一系列变量x 和y 的观测值。如果不借助于软件，应用纯数学的方法建立一元线性回归模型，进行回归分析，需要手工计算系数，做最小二乘估计，计算起来繁琐复杂，做非线性回归分析、多元回归分析的工作量则更大，更容易出错。

2.3 应用Excel 进行回归分析与预测

Excel 的数据分析功能将数学模型和经济理论进行了封装，为我们提供了简单可视化的操作接口。类似于开车不需要了解发动机的原理，我们不需要做背后的数学建模与求解，不需要了解设计及生产过程，只需要借助Excel 的数据分析功能，通过可视化的操作界面，进行几步简单的操作，输入数据，Excel 就能进行回归分析，将计算分析结果呈现在我们面前。Excel 的数据分析功能使繁琐的分析核算工作变得简单，借助Excel 数据分析工具，普通员工也能做原来想做而无法完成的数据分析工作。下面，我们应用Excel 对农作物产量与施肥量之间的关系进行回归分析与预测。

（1）数据分析法。在Excel 中，点击“数据”选项卡中的“数据分析”按钮，在弹出的对话框中选择“回归”，然后点击“确定”按钮，弹出“回归”对话框，在“Y 值输入区域”输入“$B$1∶$B$12”，在“X 值输入区域”输入“$A$1∶$A$12”，在输出区域中输出“$D$1”，然后点击“确定”，Excel 进行计算后输出分析结果，如图2 所示。根据输出结果，求出施肥量与产量之间的线性关系为Y=0.0229X+0.3427，拟合程度为0.5659，即56.59%，说明拟合得不够好。相关系数为0.75，说明施肥量与产量之间中度线性正相关。将x=5.2 代入关系式，求得y=0.46，预测出农作物的产量为0.46 公斤。

（2）散点图法。在Excel 中，选择施肥量与产量数据，然后点击“插入”选项卡中“图表”区的“插入散点图(X、Y)或气泡图”，在弹出的列表中选“散点图”，Excel系统就绘制出了施肥量与产量之间关系的散点图，如图3所示。右键点击散点，在弹出的菜单中选择“添加趋势线”，在Excel 窗口右侧出现“设置趋势线格式”窗格，选择“显示公式”“显示R 平方值”，系统就在图中显示出趋势线方程y=0.0229x+0.3427、拟合程度R=0.5659。可见，散点图法与数据分析法的计算结果是一致的。如果所有的点都在这条直线上，说明求出的线与原始数据拟合程度100%的好。观察散点图，发现大多数点都不在这条线上，说明拟合得不够好。

图3 施肥量与产量之间线性关系的散点图Fig.3 Scatter diagram of linear relationship between fertilization and yield

相对来说，散点图法更简便、直观，可以描述线性回归关系，也可以描述非线性回归关系，但只能进行一元回归分析。数据分析法适合一元回归分析，也适合多元回归分析，但只能进行线性回归分析。

（3）非线性回归分析。从图3 中散点变化的趋势来看，产量与施肥量之间的关系更像一条抛物线。删除直线趋势线，添加“多项式”趋势线。用二项式重新拟合，得到二次多项式趋势线方程y=-0.0069x+0.0914x+0.2399 和拟合程度R=0.9609，如图4 所示。很明显，拟合程度从线性方程的57%提高到二次多项式的96%，对比图3与图4，可以看出，与线性方程相比，二次多项式方程更能成功地反映出观察点的拟合程度。根据此回归模型进行预测，将x=5.2 代入方程，求得y=0.53。预测出农作物产量为0.53 公斤。

图4 施肥量与产量之间非线性回归分析的结果Fig.4 Results of nonlinear regression analysis between fertilization and yield

从经济学角度看，二次多项式拟合方程分为规模报酬递增、规模报酬不变、规模报酬递减三个阶段，说明：氮肥做为一种生产要素，开始时，随着施肥量的增加可以快速提高农作物的产量，当施肥到达一定量后，产量增加的效果就不明显了，如果过量施肥，反而会影响产量。

（4）多元线性回归分析。影响农作物产量的因素除了施肥量，还有土壤、水分和光照条件等。如果考虑这些因素，影响因子则不止一个，就需要做多元回归分析，这时就无法应用散点图的方法了，只能使用数据分析方法。但数据分析方法只适合于分析线性关系，如果是非线性关系，需要先转化为线性关系。

3 结语

相关分析与回归分析密不可分，是研究现象之间数量关系的两种重要的数据分析技术。Excel 软件将烦琐的数据分析核算过程进行了封装，提供了散点图、相关系数函数、回归分析工具等可视化的操作界面，使相关与回归分析工作变得简单。使用Excel 软件，可以先对数据进行相关分析，如果数据之间高度相关，可进行回归分析，深入探寻相关的具体形式，从而对公司的未来发展趋势做出预测，为公司制定业务目标提供有效的战略参考和决策依据，以确保公司的持续健康发展。

引用

[1] 朱小华,徐向东,马玉鑫.统计学基础[M].北京:中国人民大学出版社,2016.

[2] 杨雄,曾智.Excel在一元线性回归分析中的应用[J].保山学院学报,2021,40(2):66-73.

[3] 叶峰.基于Excel的一元回归分析教学的实践[J].数学学习与研究,2019(2):123-124.

[4] 张国锋.Excel商务应用与建模[M].北京:清华大学出版社,2009.