基于Excel的地理数据分析多元线性回归分析

基于Excel 的地理数据分析多元线性回归分析多元线性回归分析是一元线性回归分析的推广，或者说一元线性回归分析是多元线性回归分析的特例。

掌握了一元线性回归分析，就不能学习多元线性回归分析方法了。

利用Excel 进行多元线性回归与一元线性回归的过程大体相似，操作上有些细节方面的微妙差别。

不过，对于多元线性回归，统计检验的内容相对复杂。

下面以一个简单的实例予以说明。

【例】某省工业产值、农业产值、固定资产投资对运输业产值的影响分析。

通过产值的回归模型，探索影响交通运输业的主要因素。

我们想要搞清楚的是，在工业、农业和固定资产投资等方面，究竟是哪些因素直接影响运输业的发展。

数据来源于李一智主编的《经济预测技术》。

原始数据来源不详。

§2.1 多元回归过程2.1.1 常规分析在Excel 中，多元线性回归大体上可以分为如下几个步骤实现。

第一步，录入数据。

结果如下图所示（图2-1-1）。

第二步，计算过程。

比较简单，分为如下若干个步骤。

（1）打开回归对话框。

沿着主菜单的“工具（T）”→“数据分析（D）…”路径打开（2）“数据分析”对话框，选择“回归”，然后“确定”，弹出“回归”分析选项框，选项框的各（3）选项与一元线性回归基本相同（图2-1-2）。

具体说明如下。

（4）（2）输入选项。

首先，将光标置于“Y值输入区域（Y）”中。

从图2-1-1所示的F1单元（5）格起，至F19止，选中用作因变量全部数据连同标志，这时“Y值输入区域（Y）”的数据区域（6）中立即出现“$F$1:$F$19”。

然后，将光标置于“X值输入区域（X）”中。

从图2-1-1所示的C1单元格起，至E19止，选中用作自变量全部数据连同标志，这时“X值输入区域（X）”中立即出现“$C$1:$E$19”——当然，也可以直接在“X值输入区域（X）”中手动输入地址为“$C$1:$E$19”的单元格范围。

注意，与一元线性回归的设置一样，这里数据范围包括数据标志“工业产值x1”、“农业产值x2”、“固定资产投资x3”和“运输业产值y”。

因此，选项框中一定选中“标志”项（图2-1-3）。

如果不设“标志”项，则“X值输入区域（X）”的对话框中应为“$C$2:$E$19”，“Y值输入区域（Y）”的对话框中则是“$F$2:$F$19”。

否则，计算结果不会准确。

完成上述设置以后，确定，立即给出回归结果。

由于这里的“输出选项”选中了“新工作表组（P）”（图2-1-3），输出结果在出现在新建的工作表上（图2-1-4）。

第三步，结果解读。

这一步与一元线性回归也没有太大差别。

（1）读出回归系数，建立模型。

从图2-1-4所示的“输出摘要（SUMMARY OUTPUT）”中可以读出截距a，以及三个回归系数b1、b2和b3，对应于三个变量工业产值x1、农业产值x1、固定资产投资x2。

数值如下a = −1.0044 , b1 = 0.053326 , b2 = −0.00402 , b3 = 0.090694 .与t统计量等价的时P值。

P小于0.05，表明回归系数的置信度达到95％以上，相应的t检验在显著性水平为α=0.05时可以通过；P小于0.01，表明回归系数的置信度达到99%以上，相应的t 检验在显著性水平为α=0.01时可以通过。

其余依此类推。

为了简明，可以将P值添加到线性回归模型里面，得到展，交通运输业越是受到负面影响。

这在道理上是不通的。

按理说，农业增长应该引起交通运输业的进一步发展才对。

其二，回归系数b2的t检验不能通过。

回归系数的P值高达0.779，置信度只有20%左右，这就有问题了。

其三，回归系数b2的绝对值偏小。

可以判定，自变量之间可能存在多重共线性问题。

2.1.2 偏相关系数的计算和分析在具有多重共线性的线性回归问题中，偏相关系数（partial correlation coefficient）在进行变量取舍判断时具有一定的参考价值。

Excel不能直接给出偏相关系数，但借助有关的函数或命令，可以方便地算出偏相关系数。

计算公式为有了上述公式，可以借助计算矩阵行列式的函数mdeterm计算逆矩阵，然后计算偏相关系数。

最快速的办法是利用矩阵求逆函数minverse。

具体工作可以由以下几个步骤完成。

第一步，计算相关系数相关系数可以借助命令correl或者pearson逐一计算。

为了直观和便捷，不妨给出相关系数矩阵。

首先，沿着“工具（T）→数据分析（D）”的路径，从工具箱的“数据分析”对话框中选择“相关系数”（图2-1-5）。

然后，根据图2-1-1所示的数据分布的单元格范围，在“相关系数”对话框中进行如下设置（图2-1-6）。

注意：“输入区域（I）”中包括自变量和因变量覆盖的数据范围，包括数据标志，并且我们是逐列计算。

确定以后，得到相关系数矩阵。

由于相关矩阵是对称的，Excel只给出了下三角部分（图2-1-7）。

容易根据对称性将上三角部分填补起来（图2-1-8）。

第二步，计算逆矩阵借助函数minverse，非常容易得到相关系数矩阵的逆矩阵。

Minverse的语法如下：Minverse(Array)。

Array为行数和列数相等的数组。

具体到我们的问题，则是先选中一个4×4的数值区域，然后键入“=minverse()”，再然后将光标置于括号中，选中相关的数据——注意不含标志（图2-1-8）。

同时按下Ctrl键和Shift键，回车，立即得到逆矩阵（图2-1-9）。

第四步，偏相关系数分析偏相关系数是假定在一个模型中其他变量不变的情况下，一个自变量与因变量的相关性。

从图2-1-7所示的计算结果可以看出，农业产值与运输业产值的简单相关系数很高，且为正值（0.965）。

但是，在多元线性回归模型中，SPSS给出的偏相关系数很小且为负（-0.076）。

这就是说，单就相关性而言，农业产值与运输业产值肯定是高度正相关的；但是，在模型中，偏相关系数却“说”农业产值对运输业的贡献很小且为负。

这是相互矛盾的。

究其根源，可能是因为农业产值与其他变量具有相关性，因为共线性导致模型回归系数及其检验参量失真。

也可能属于如下情况，农业对运输业的贡献可能是间接的，是通过其他产业部门如工业发生影响。

一言以蔽之，农业产值与运输业产值的偏相关系数暗示两个问题：一是数值太小，表明相关性很低，从而意味着它在线性回归模型中的地位不重要；二是数值为负，表明负相关。

这两种情况都与简单相关系数反映的情况不一致，与我们对现实世界的认识也不尽相符。

这是违背常理的计算结果——农业发展反而导致运输业滞缓。

由此可见，偏相关系数反映的信息与回归系数和t值（或者P值）给出的结果彼此呼应。

§2.2 多重共线性分析2.2.1 共线性判断根据上面的回归参数和相应统计量的初步考察可以判定，模型中存在自变量共线性问题。

有必要对模型中的自变量进行多重共线性判断，然后调整模型的结构。

为了分析多重共线性问题，有必要计算出各个自变量对应的容忍度（Tol）和方差膨胀因子（VIF）。

计算方法如下。

(1) 逐步计算第一步，以工业产值（x1）为因变量，以农业产值（x2）和固定资产投资（x3）为自变量，基于如下模型进行多元线性回归x1 = C + ax2 + bx3 ,从回归结果摘要（Summary Output）的“回归统计”中，可以读到复相关系数（R）的平方值（R Square）为R2=0.97898（图2-2-1），于是得到容忍度1 2 1 0.97898 0.021021 Tol = −R = − = ,相应地，方差膨胀因子为(2) 矩阵计算利用矩阵函数，可以非常方便地计算出VIF值，进而算出Tol值。

首先，借助数据分析的相关系数计算功能，利用前面说明的方法计算自变量的相关系数矩阵（图2-2-2）；然后，借助矩阵求逆函数minverse计算相关系数矩阵的逆矩阵（图2-2-3）。

可以看出，这个逆矩阵的对角线上的元素，就是相应的VIF值。

利用矩阵运算，远比逐步计算的效率高。

根据上面的计算结果可以看到，所有的VIF值都大于经验上的检验标准（VIF=10）。

其中工业产值（x1）对应的VIF值最大，这意味着它与其他变量的共线性最强；农业产值（x2）对应的VIF值为次大，固定资产投资（x3）对应的VIF值相对最小。

但是，考虑到回归系数的合理性，首先应该考虑到剔除农业产值，用剩余的变量进行多元线性回归。

2.2.2 剔除异常变量剔除异常变量x2（农业产值），用剩余的自变量x1、x3与y回归（图2-2-4），回归步骤自然是重复上述过程（参见图2-2-5），最后给出的回归结果如下（图2-2-6）。

从图2-2-6中容易读出回归系数估计值和相应的统计量：a = −0.89889 , b1 = 0.051328 , b3 = 0.091229 ;R = 0.994263 , R2 = 0.988558 ;s = 0.324999 ;F = 647.973 ;t b1 = 4.200968 , t b3 = 3.632285 .根据上述结果，建立二元回归模型如下：0.099 0.001 0.00290.8989 0.0513 1 0.0912 3P值y = − + x + x,利用残差或者标准残差容易算出，DW 值约为1.769。

在显著性水平为α=0.05、回归自由度为m=2 时，DW 检验的临界值上下界分别为d l=1.05 的、d u=1.53。

显然，相对于第一次回归结果，回归系数的符号正常，检验参数F值提高了，标准误差s值降低了，t值检验均可通过。

相关系数R有所降低，这也比较正常——一般来说，增加变量数目通常提高复相关系数，减少变量则降低复相关系数。

相对于第一次和第二次回归结果，回归系数的符号正常，但检验参数F值降低了，标准误差s值提高了，t值检验均可通过，相关系数R有所降低。

比较而言，这一次的P值似乎更为合理，回归系数估计值没有任何难以理解之处。

根据上述结果，建立二元回归模型如下§2.3 借助线性回归函数快速拟合2.3.1 直接的公式运算利用线性拟合函数linest可以对模型参数即重要的统计量进行快速估计。

线性拟合函数的语法如下LINEST(known_y's, known_x's, const,stats)这里，known_y’s表示因变量y对应的已知数据集合，known_x’s表示自变量x对应的已知数据集合，const和stats为逻辑值，只能取true或者false，const的默认值为true（这时可以得到正常估计的截距，否则截距为0），stats的默认值为false（这时仅仅给出回归系数，否则会给出斜率和必要的统计参量）。

上述函数可以直接键入，也可以从“编辑栏”中调出函数。