Excel在多元线性回归分析中的应用

1、Excel在多元线性回归分析中的应用 【摘要】如果回归分析中存在两个及两个以上的自变量就是多元回归，只有不同自变量的最优自合才能对因变量进行预测或者估计，一个自变量X对因变量Y进行估计是线性回归分析中的重点，与利用一个自变量展开估计及预测相比，这种方法的真实性与可靠性要更高，具有更大的实用意义。鉴于此，笔者首先对多元线性回归进行了概述，带着对多元线性回归的简单了解，引领大家从不同角度对多元线性回归分析中Excel的应用进行了进一步的探讨和分析。 【关键词】Excel；多元线性回归分析；应用 在线性回归分析中，用模型中一个自变量X对因变量Y进行估计是其中的重点，因为不同事物之间的联系是错综复杂的

2、，如果一个因变量发生变化，往往是因为受到另个或者多个自变量的影响而发生的，为了对线性回归分析中这种复杂的依存关系进行全面的揭示，从而提高控制及预测的精确度，需要对更多的自变量进行考虑，并结合实际情况建立起多元回归模型。实际上，多元回归分析的方法和原理在同一元线性回归分析中是基本相同的，但其中也存在一些不同点，比方说不可以利用散点图表示不同变量之间存在的关系，与简单线性回归相比，多元回归计算的难度要大很多，计算的难度随着变量的增多而越来越复杂，但是利用Excel可以顺利的将计算变得轻松且简单。下面我们就从不同角度针对Excel在多元线性回归分析中的应用展开进一步分析。 1 多元线性回归概述 如果

3、回归分析中存在两个及两个以上的自变量，那么这就是多元回归，实际上很多情况下一个现象都伴随着多个现象，与不同因素发生着联系，只有不同自变量的最优自合才能对因变量进行预测或者估计，这种分析方法往往比一个利用一个自变量展开估计及预测更加有效，同时也更加与实际情况相符合，所以，多元线性回归往往比一元线性回归具有更大的实用意义。 实际上多元线性回归的基本原理及计算过程与一元线性回归基本上是相同的，但是因为自变量的个数多，计算起来免不了会麻烦，通常在实际应用过程中需要借助统计学软件来完成。因为不同自变量的单位可能会存在差异，比方说在一个消费水平关系式中，职业、工资水平及地区等因素都会对消费水平产生影响，但

4、是这些消费因素的单位很明显是不同的，所以自变量前系数的大小并不能直接说明该因素是否重要，简单一点来说，同样的工资收入，如果利用元作为单位，与以百元作为单位相比得到的回归系数要小很多，但是工资水平对消费水平的影响情况却并没有发生改变，因此，需要想办法将不同自变量量化到统一的单位上来。具体来说就是将包括因变量的所有变量都转化成标准分，然后进行线性回归，这时得到的回归系数可以将自变量的重要程度直接反映出来，这时我们得到的回归方程就是标准回归方程，其回归系数被称作是标准回归系数。 2 回归分析问题的提出 我们经常会在现实世界中遇到一些相互制约、相互依赖的变量，这些变量之间往往存在着一定的关系，大致上可

5、以将这种关系分成两个种类：一种是确定性的关系，这种确定性关系可以利用函数来表示，另一种是不确定关系，一旦确定了一个变量之后，另外一个变量的值是不能得到确定的，这种情况下，针对自变量之间存在的非确定性关系展开研究和分析具有十分重要的现实意义。 对两个变量间的关系进行研究时，其中只有一个是随机变量，另外一个通常为一般变量，假设这两个变量分别为x和y，其中，x为一般变量，可以实现精确的测量或者控制；y为随机变量，这一随机变量为x取某个值以后才经过计算得到的，并不能在事先进行确定，通过试验观察我们可以发现，具有关系的两个变量之间存在很多对实测数据，在坐标中可以利用这些数据描绘出来一个散点图（如图1）。

6、 从上图中可以看出，与实测数据相对应的各点并没有在一条直线上，但是从位置上来看这些点都是趋向一条直线的，因此我们说这两个变量之间存在着线性关系，而这条直线就是y对X的回归直线，虽然这两个变量之间存在着非确定性函数关系，但是我们始终可以利用直线形式=a+bx对两个变量的规律性展开研究。下面我们以某商店为例，对其商店的规模大小、促销费用及年销售额之间的关系展开分析。 3 多元线性回归中Excel数据分析的应用 利用Excel数据分析方法对多元线性回归展开操作的过程中，首先需要单击工具栏，并在弹出的菜单中点击“数据分析”，然后在数据分析工具选项框中选择“回归”，并在输入、输出等选项中进行合理选择，选

7、择过程中需要对自变量X的输入加以注意，在输入X的过程中应按照已经确定好的不同自变量的顺序，将自变量的单元格引用范围共同放在X值的输入区域中。然后点击界面上的“确定”按钮，这样就能得到线性回归分析结果了（如图2）。 按照上面操作得到的显示结果，我们可以写出二元线性回归方程： Yi=b0+b1X1i+b2X2i=51.3127+1.4053X1i+6.3823X2i 式中，b1表示如果促销费用固定，那么商店规模增加1平方米，商店的年销售额将会增加1.4053万元；b2表示如果商店规模大小是固定的，那么商店促销费用如果增加1万元，其年销售额将会增加6.3823万元。从这里我们可以看出，与一元线性回归

8、方程中的回归系数b=1.6246相比，b1也就是商店规模大小的回归系数，将会小很多，之所以会出现这种结果，其原因在于一元线性回归方程中只对商店规模大小与年销售额之间的关系进行了考虑，并没有考虑到促销费用这一要素，这一重要因素受到了严重忽略，将促销费用的影响掺进了商店规模大小影响之中。b0表示截距，b0=51.3127万元，这一数值和一元线性回归方程中的截距（b0=+99.01）相比存在很大的不同，这主要是因为X1=0与X2=0二者都不再其样本的取值范围中，所以可以看出，对截距项进行解释需要非常谨慎。 如果判定系数=85.14%，则说明在年销售额变动中，可以由商店规模大小及促销费用的多少二者来对

9、有85.14%这样的判定系数进行解释，14.86%这一因素是随机误差，当第二个自变量引进来以后，回归方程判定系数85.14%与一元线性回归方程判定系数77.68%相比，明显提高了7.46个百分点。这里我们尤其要注意，通常自变量增加，在统计上效果并不明显，其判定系数的值也将得到增大。 商店年平均销售额的估计标准也是存在误差的，利用商店的估计标准误差为112.1015万元，利用Excel进行数据分析时，当引进第二个自变量促销费用，与一元线性回归方程的估计标准误差相比，回归方程的估计标准差有了一些下降，这就说明与一元线性回归方程相比，回归方程的代表性要高出很多。 如果假设显著性水平是0.05，b1的

10、检验统计量为6.2817，b2的检验统计量为2.4538，通过查表可以知道，t0.05/2（153）=2.1788。因为6.2817比2.1788大，而2.4538比2.1788大，以这些为依据我们可以拒绝H0：1=0以及H0：2=0的相关假设，判定在统计上这两个回归系数都是显著的。值得注意的是，如果这一例的显著性水平为0.01，而不是0.05，那么t0.01/2（153）=3.0545。虽然这时6.2817比3.0545大，但是2.4538始终比3.0535小，这种情况下如果拒绝H0：1=0的假设，也不能拒绝H0：2=0的假设，因此我们可以判定在统计上第二个回归系数是不显著的。 设计显著性水

11、平为0.05，经过查表可以了解F0.05（2，12）=3.89，F=34.38比F0.05（2，12）=3.89要大，这种情况下可以拒绝原来的假设，这时样本的r2是显著的，由这些依据可以推论出已经建立起来的二元线性回归模型是有效的，之所以说存在复相关的关系，主要是指一个因变量和不同自变量之间存在的相关性，一旦自变量出现了共同的变动，那么因变量将会随之发生变动，同时其相关程度也需要利用复相关系对其进行测定。本文中的例子，商店规模大小、促销费用以及年销售额三者之间的复相相关系数是0.9227，经过计算可以看出，商店规模和促销费用二者可以作为一个整体因素存在，这个整体因素与年销售额之间存在的相关性非

12、常高，同时与一元回归中商店规模单个自变量相比，其相关程度与年销售额之间存在较高的相关系数。这里值得一提的是，在我们对客观事物本质上为多因素影响的变量时，可以利用复相关、多元回归及偏相关展开进一步分析，这种分析方法与单相关和一元回归分析相比，准确性和真实性要更强一些。 4 结语 综上所述，我们经常会在现实世界中遇到一些相互制约、相互依赖的变量，这些变量之间往往存在着一定的关系，回归分析就是对客观事物数量之间依存关系而进行的一种分析，在数理统计中是十分常用的一种分析方法，从本文中一元线性回归和多元线性回归两种方法的比较分析中可以看出，利用Excel对线性回归问题进行分析时，可以与相关图表相配合，这样就可以将数据清晰的显示出来，从而大大提高办公效率。本文结合笔者的实际工作经验，首先针对线性回归分析进行了简单概述，然后从不同角度针对多元线性回归分析中Excel的应用展开了分析，供大家参考。 参考文献： 1 李传伟.基于Excel的向前增选法多元线性回归在体育教学中的应用J.中国管理信息化，2