基于Excel的地理数据分析多元线性回归分析.docx
《基于Excel的地理数据分析多元线性回归分析.docx》由会员分享，可在线阅读，更多相关《基于Excel的地理数据分析多元线性回归分析.docx（24页珍藏版）》请在冰豆网上搜索。
基于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所示的“输出摘要（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临界值。
升级会员 