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

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

《基于Excel的地理数据分析多元线性回归分析docx.docx》由会员分享，可在线阅读，更多相关《基于Excel的地理数据分析多元线性回归分析docx.docx（24页珍藏版）》请在冰豆网上搜索。

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

基于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所示的“输出摘要（SUMMARYOUTPUT）”

中可以读出截距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偏相关系数的计算和分析

在具有多重共线性的线性回归问题中，偏相关系数（partialcorrelationcoefficient）在进行

变量取舍判断时具有一定的参考价值。

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,

从回归结果摘要（SummaryOutput）的“回归统计”中，可以读到复相关系数（R）的平方值（R

Square）为R2=0.97898（图2-2-1），于是得到容忍度

1210.978980.02102

1Tol=−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;

tb1=4.200968,tb3=3.632285.

根据上述结果，建立二元回归模型如下：

0.0990.0010.0029

0.89890.051310.09123

P值

y=−+x+x

利用残差或者标准残差容易算出，DW值约为1.769。

在显著性水平为α=0.05、回归自由度为

m=2时，DW检验的临界值上下界分别为dl=1.05的、du=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（这时仅仅给出回归系数，否则会给出斜

率和必要的统计参量）。

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

相对于直

接键入的函数而言，从编辑栏里调出的函数更为直观，其优点是视野开阔，便于初学者把握。

下面具体说明。

第一步，选择数值区域。

在工作表中选中一个5*（m+1）的空白区域——即5行×（m+1）

列，这里m为自变量数据。

本例有3个自变量，故空白区域为5*4（图2-3-1）。

第二步，输入计算公式。

在命令linest后面的括号中键入表示变量范围的参数和有关统计

要求的参数（图2-3-2）。

根据图2-1-1所示的数据排列，完整的函数表达为

“=LINEST（F2:

F19,C2:

E19,TRUE,TRUE）”。

注意，定义数据分布范围的时候，不要考虑数据标志。

第三步，获取结果。

左手按住Ctrl+Shift健，右手揿Enter健，立即得到模型拟合结果（图

2-3-3）。

第四步，结果解读。

对比图2-3-3与图2-1-4，不难判读上述结果。

在图2-3-3所示的回归结

果中，第一行为回归系数——回归系数的排列顺序从左到右依次为：

b3、b2、b1和常数项a。

具体说来

30.09069b=,20.00402b=−,10.053326b=,a=−1.004403.

第二行为回归系数对应的标准误差

Seb3=0.02599,Seb2=0.014029,Seb1=0.018814,Sea=0.6431563.

第三行为测定系数（Rsquare）和模型拟合的标准误差

R2=0.98862,s=0.335426.

第四行为F值和剩余自由度

F=405.58,df=14.

第五行为回归平方和和剩余平方和。

即

SSr=136.896,SSe=1.575144.

根据上述数值计算结果可以建立模型并开展统计检验分析。

2.3.2利用线性回归函数对话框

上面的第二步有些似乎抽象，为了直观起见，可以调出回归函数对话框。

沿着主菜单的

“插入（I）”→“fx函数（F）…”路径打开“插入函数”选项框；也可以直接在状态栏单击

函数图标fx，弹出插入函数选项框（图2-3-4）。

在统计函数中找到线性回归函数linest（图2-3-4），确定，即可弹出线性回归函数对话框

（图2-3-5）。

根据图2-1-1所示的数据排列，设置对话框中的各个选项。

仍然要注意的是，定

义数据分布范围的时候，不要考虑数据标志。

完成设置之后，同时按住Ctrl+Shift健，确定，即可得到线性回归的快速估计结果。

特别

强调，这里仅仅说明整个操作过程的第二步，其他步骤包括数据范围布置、结果分析等等与

上一小节介绍的过程完全一样。

§2.4统计检验临界值的查询

对于多元线性回归分析的F检验和t检验，我们需要查表得到临界值。

为此翻书殊为不便，

而且有时候身边没有这类统计学书籍。

今天，很多数学软件或者电子表格软件都可以借助相

应的命令计算统计检验的临界值。

利用Excel计算F检验和t检验的临界值相当方便。

2.4.1F检验的临界值查询

在Excel中，查阅F分布临界值的命令为finv，函数表达式为

finv（α,m,n-m-1）,

用中文表达就是：

Finv（显著性水平，变量数，自由度），或者Finv（显著性水平，分子自由度，

分母自由度）。

有些著作中将变量数称为“分子自由度”——实则“回归自由度”，将样品数

减去变量数再减1称为“分母自由度”——实则剩余自由度。

这种命名从F值的计算公式可以

得到理解。

F值的计算公式为

侧检验）。

Excel计算的t值和临界值都是基于双边临界区域，故系统默认双侧检验（分布尾数

为2）。

利用t分布函数tdist可以将参数的t值全部转换为P值，这样更为直观。

转换函数的语

法是：

tdist（t的绝对值，剩余自由度，尾数）。

由于Excel采用双尾检验，尾数固定为2，于是

命令格式为tdist（abs（t）,n-m-1,2），这里abs为取绝对值函数。

反过来，利益tinv函数可以将

参数的P值转换为t值的绝对值，语法是：

tinv（P,n-m-1）。

以图2-1-4所示的结果为例，在J17

单元格中输入公式“=TDIST（ABS（D17）,$B$13,2）”，回车，得到截距的P值；双击J17单元格

的右下角，可得全部回归系数的P值。

在K17单元格中输入公式

“=TINV（E17,$B$13）*SIGN（B17）”，回车，得到截距的t值；双击K17单元格的右下角，可

得全部回归系数的t值。

2.4.3相关系数检验的临界值查询

回归分析通常只对简单相关系数进行临界检验，而简单相关系数与F值或者t值等价，故

只要查出F值或者t值，就可以计算出简单相关系数的临界值。

F值与简单相关系数的关系为

参照图2-4-1所示，将显著性水平0.05和0.01安排在第二行，自由度的数值（n-2）安排

在第A列和D列。

借助上面的公式，可以计算任意的相关系数临界值。

在单元格B3中输入公式“=（（FINV（B$2,1,$A3）/$A3）/（1+FINV（B$2,1,$A3）/$A3））^0.5”，回

车，得到0.997。

抓住B3单元格的右下角，待到鼠标光标变成细小黑十字，右拉至单元格C3，

得到1.000（更精确地，得到0.999877）。

选中B3和C3单元格，将鼠标光标指向右下角，待其

变为黑十字填充柄，双击，得到显著性水平为0.05和0.01、自由度为1～20的全部临界值。

采

用同样的方法，不难计算更多的R临界值。