研究生地理数学方法(实习) PartI电子表格Excl “数据分析”对话框,选择“回归”,然后“确定”,弹出“回归”分析选项框,选项框的各 选项与一元线性回归基本相同(图2-1-2)。具体说明如下, (2)输入选项。首先,将光标置于“Y值输入区域(Y)”中。从图2-1-1所示的F1单元 格起,至F19止,选中用作因变量全部数据连同标志,这时“Y值输入区域(Y)”的数据区域 中立即出现“sF$l$F$l9”。 回归 Y值输入区域r) 匚确定 取消 值输入区域〖 □标志① 口常数为零 帮助0n 口置信度吧 输出选项 ○输出区域) ⊙新工作表组 ○新工作薄 残差 □残差 口残差图① □标准残差 □线性拟合图① 正态分布 □正态概率图Q 图2-1-2回归分析选项框 回归 输入 确定一 Y值输入区域『 sF$1:$F$19 取消 值输入区域〖 C$1:$E$19 回标志 口常数为零( 帮助0 口置信度 输出选项 ○输出区域Q ⊙新工作表组 ○新工作薄) 残差 回残差 残差图① 回标准残差r 线性拟合图〔) 正态分布 回正态概率图c 图2-1-3设置完毕后的回归选项框(包括数据标志)
研究生地理数学方法（实习） Part1 电子表格 Excel 27 “数据分析”对话框，选择“回归”，然后“确定”，弹出“回归”分析选项框，选项框的各 选项与一元线性回归基本相同（图2-1-2）。具体说明如下。 （2）输入选项。首先，将光标置于“Y值输入区域（Y）”中。从图2-1-1所示的F1单元 格起，至F19止，选中用作因变量全部数据连同标志，这时“Y值输入区域（Y）”的数据区域 中立即出现“$F$1:$F$19”。 图2-1-2 回归分析选项框 图 2-1-3 设置完毕后的回归选项框（包括数据标志）
研究生地理数学方法(实习) PartI电子表格Excl 然后,将光标置于“X值输入区域(Ⅹ)”中。从图2-1-1所示的C1单元格起,至E19止, 选中用作自变量全部数据连同标志,这时“X值输入区域(Ⅹ)”中立即出现“$C$lSE$19” 当然,也可以直接在“ⅹ值输入区域(Ⅹ)”中手动输入地址为“$C$1:SE$19”的单元格 范围 注意,与一元线性回归的设置一样,这里数据范围包括数据标志“工业产值x1”、“农业 产值x2”、“固定资产投资x3”和“运输业产值y”。因此,选项框中一定选中“标志”项(图 2-1-3)。如果不设“标志”项,则“X值输入区域(X)”的对话框中应为“SC$2:SE$19”,“Y 值输入区域(Y)”的对话框中则是“$F$2:$F$19”。否则,计算结果不会准确。 完成上述设置以后,确定,立即给出回归结果。由于这里的“输出选项”选中了“新工 作表组(P)”(图2-1-3),输出结果在出现在新建的工作表上(图2-1-4) E GHI SUMMARY OUTPUT 回归统让 iple0.994296 5 R Square. 988625 标准误差0.35426 10方差分析 gnificanceF 12回归分析 3136.895845.63192405.57997.71E-14 残差 141.5751440.11251 14总计 15 Coefficien.标准误差 t Stat P-value Lower95%er95%限95,0限95,09 17 Intercept-1.0040.643156-1.561680.140679-2.383840.37503-2,383840.37503 18工业产值x0.0553260.018142.9406480.0107430.0149730.0956780.0149730.095678 19农业产值=0.004020.014029-0,286290.778946-0.034110.026073-0.03411.026073 20固定资产00906940.02598934897060.0036080.0349530,1464350.0349530146435 24 RESIDUAL OUTPUT PROBABILITY OUTPUT 26观测值运输业产残差标准残差 百分比排仨输业产值y ;以回归结巢数据 Sheet22/het3 图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
研究生地理数学方法（实习） Part1 电子表格 Excel 28 然后，将光标置于“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）。 图 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
研究生地理数学方法(实习) PartI电子表格Excl 于是得到模型 y=-10044+0.055031-0.0040x2+0.0907x3 (2)读出主要统计量,预备统计检验或者开展模型特征的初步分析。相关系数和相关系 数平方如下 R=0994296,R2=0.988625 更稳妥地,可以考察采用校正相关系数平方。 标准误差如下 s=0.335426 考虑到y的平均值5.559444,容易计算变异系数 5.55944 数值小于0.1,可以接受。 总体回归的F统计量为 F=405.5799 大于显著性水平为∝=0.05时的临界值Foos34=3.344,也大于显著性水平为a=0.01时的临界值 F014=5564。因此,F值没有问题。 回归系数的t统计量为 tb1=2.940648,th2=-0.28629,tb3=3.489706 其中农业产业的r统计量的绝对值小于时的临界值0s,14=2.145。为了明确起见,不妨将t统计 量添加到线性回归模型里面,得到 y=-10044+0.0553031-0.0040x2+00907x3 f值1.56129410.2863490 与t统计量等价的时P值。P小于0.05,表明回归系数的置信度达到95%以上,相应的检验在显 著性水平为a=005时可以通过;P小于001,表明回归系数的置信度达到99以上,相应的 检验在显著性水平为α=001时可以通过。其余依此类推。为了简明,可以将P值添加到线性回 归模型里面,得到 y=-10044+0.055301-0.0040x2+00907x3 P值0.1400.0110.7790.004 对于线性回归模型,截距的检验可以放松。原因在于:其一,截距代表某种初始值或者平均 值,所含信息不多;其二,一般说来,截距代表的初始值可能处于某种规律覆盖的范围之外。 根据残差数据,不难计算DW值,方法与一元线性回归完全一样,结果为DW=1.853。在 显著性水平为∝=005、回归自由度为m=3时,DW检验的临界值上下界分别为d=0.93的、 d=1.69。可见,DW检验没有问题 (3)模型问题诊断。上述模型存在如下问题。 其一,农业产值x的回归系数b2的符号与事理不符。回归系数为负号,意味着农业越是」
研究生地理数学方法（实习） Part1 电子表格 Excel 29 于是得到模型 1 2 3 yˆ = −1.0044 + 0.0553x − 0.0040x + 0.0907x . （2）读出主要统计量，预备统计检验或者开展模型特征的初步分析。相关系数和相关系 数平方如下 R = 0.994296 , 0.988625 2 R = . 更稳妥地，可以考察采用校正相关系数平方。 标准误差如下 s = 0.335426. 考虑到y的平均值5.559444，容易计算变异系数 0.06 5.559444 0.335426 = = y s . 数值小于0.1，可以接受。 总体回归的F统计量为 F = 405.5799 , 大于显著性水平为α=0.05时的临界值F0.05,3,14=3.344，也大于显著性水平为α=0.01时的临界值 F0.01,3,14=5.564。因此，F值没有问题。 回归系数的t统计量为 2.940648 tb1 = , 0.28629 tb2 = − , 3.489706 tb3 = . 其中农业产业的t统计量的绝对值小于时的临界值t0.05, 14=2.145。为了明确起见，不妨将t统计 量添加到线性回归模型里面，得到 1.561 2.941 - 0.286 3.490 ˆ 1.0044 0.0553 0.0040 0.0907 1 2 3 t值 y = − + x − x + x . 与t统计量等价的时P值。P小于0.05，表明回归系数的置信度达到95％以上，相应的t检验在显 著性水平为α=0.05时可以通过；P小于0.01，表明回归系数的置信度达到99%以上，相应的t 检验在显著性水平为α=0.01时可以通过。其余依此类推。为了简明，可以将P值添加到线性回 归模型里面，得到 0.140 0.011 0.779 0.004 ˆ 1.0044 0.0553 0.0040 0.0907 1 2 3 P值 y = − + x − x + x . 对于线性回归模型，截距的检验可以放松。原因在于：其一，截距代表某种初始值或者平均 值，所含信息不多；其二，一般说来，截距代表的初始值可能处于某种规律覆盖的范围之外。 根据残差数据，不难计算DW值，方法与一元线性回归完全一样，结果为DW=1.853。在 显著性水平为α=0.05、回归自由度为m=3时，DW检验的临界值上下界分别为dl=0.93的、 du=1.69。可见，DW检验没有问题。 （3）模型问题诊断。上述模型存在如下问题。 其一，农业产值x2的回归系数b2的符号与事理不符。回归系数为负号，意味着农业越是发
研究生地理数学方法(实习) PartI电子表格Excl 展,交通运输业越是受到负面影响。这在道理上是不通的。按理说,农业增长应该引起交通 运输业的进一步发展才对 其二,回归系数b2的I检验不能通过。回归系数的P值高达0.779,置信度只有20%左右, 这就有问题了。 其三,回归系数b2的绝对值偏小。 可以判定,自变量之间可能存在多重共线性问题。 212偏相关系数的计算和分析 在具有多重共线性的线性回归问题中,偏相关系数( partial correlation coefficient)在进行 变量取舍判断时具有一定的参考价值。 Excel不能直接给出偏相关系数,但借助有关的函数或 命令,可以方便地算出偏相关系数。计算公式为 这里Ry为第个自变量与因变量y之间的偏相关系数,c为相关系数矩阵的逆矩阵中对应的元 素。以三个自变量为例,简单相关系数矩阵可以表作 R1R12R13R1 R R31 R32 R33 R32 LRyI R, 2 Ry3 R, 假定C的逆矩阵为 333 则第一个自变量与因变量之间的偏相关系数为 第二个自变量与因变量之间的偏相关系数为 第三个自变量与因变量的偏相关系数为 Rr
研究生地理数学方法（实习） Part1 电子表格 Excel 30 展，交通运输业越是受到负面影响。这在道理上是不通的。按理说，农业增长应该引起交通 运输业的进一步发展才对。 其二，回归系数b2的t检验不能通过。回归系数的P值高达0.779，置信度只有20%左右， 这就有问题了。 其三，回归系数b2的绝对值偏小。 可以判定，自变量之间可能存在多重共线性问题。 2.1.2 偏相关系数的计算和分析 在具有多重共线性的线性回归问题中，偏相关系数（partial correlation coefficient）在进行 变量取舍判断时具有一定的参考价值。Excel不能直接给出偏相关系数，但借助有关的函数或 命令，可以方便地算出偏相关系数。计算公式为 jj yy jy x y c c c R j − = , 这里 x yj R 为第j个自变量与因变量y之间的偏相关系数，c为相关系数矩阵的逆矩阵中对应的元 素。以三个自变量为例，简单相关系数矩阵可以表作 ⎥ ⎥ ⎥ ⎥ ⎥ ⎦ ⎤ ⎢ ⎢ ⎢ ⎢ ⎢ ⎣ ⎡ = y y y yy y y R R R R R R R R R R R R R R R R C 1 2 3 31 32 33 32 21 22 23 2 11 12 13 1 . 假定C的逆矩阵为 ⎥ ⎥ ⎥ ⎥ ⎥ ⎦ ⎤ ⎢ ⎢ ⎢ ⎢ ⎢ ⎣ ⎡ = − y y y yy y y c c c c c c c c c c c c c c c c C 1 2 3 31 32 33 32 21 22 23 2 11 12 13 1 1 . 则第一个自变量与因变量之间的偏相关系数为 yy y x y c c c R 11 1 , 1 − = , 第二个自变量与因变量之间的偏相关系数为 yy y x y c c c R 22 2 , 2 − = , 第三个自变量与因变量的偏相关系数为 yy y x y c c c R 33 3 , 3 − =
研究生地理数学方法(实习) 子表格 Excel 上述公式,可以借助计算矩阵行列式的函数 mdeterm计算逆矩阵,然后计算偏相关系数。 速的办法是利用矩阵求逆函数 minverse。具体工作可以由以下几个步骤完成 第一步,计算相关系数 相关系数可以借助命令 correl:或者 pearson逐一计算。为了直观和便捷,不妨给出相关系数 矩阵。首先,沿着“工具(T)→数据分析(D)”的路径,从工具箱的“数据分析”对话框 中选择“相关系数”(图2-1-5)。 数据分析 分析工具A) 匚确定 取消 伤方差 〔帮助0 双样本方差 图2-1-5从“数据分析”选中“相关系数” 然后,根据图2-1-1所示的数据分布的单元格范围,在“相关系数”对话框中进行如下设 置(图2-1-6)。注意:“输入区域(I)”中包括自变量和因变量覆盖的数据范围,包括数据标 志,并且我们是逐列计算。 相关系数 输入区域① C1:F19 确定 分组方式 ⊙逐列c 取消 ◎逐行 帮助0 回标志位于第一行① 输出选项 ○输出区域 ⊙新工作表组P ○新工作簿) 图2-16相关系数选项框的设置 确定以后,得到相关系数矩阵。由于相关矩阵是对称的, Excel只给出了下三角部分(图 2-1-7)。容易根据对称性将上三角部分填补起来(图2-1-8)
研究生地理数学方法（实习） Part1 电子表格 Excel 31 有了上述公式，可以借助计算矩阵行列式的函数mdeterm计算逆矩阵，然后计算偏相关系数。 最快速的办法是利用矩阵求逆函数minverse。具体工作可以由以下几个步骤完成。 第一步，计算相关系数 相关系数可以借助命令correl或者pearson逐一计算。为了直观和便捷，不妨给出相关系数 矩阵。首先，沿着“工具（T）→数据分析（D）”的路径，从工具箱的“数据分析”对话框 中选择“相关系数”（图2-1-5）。 图 2-1-5 从“数据分析”选中“相关系数” 然后，根据图2-1-1所示的数据分布的单元格范围，在“相关系数”对话框中进行如下设 置（图2-1-6）。注意：“输入区域（I）”中包括自变量和因变量覆盖的数据范围，包括数据标 志，并且我们是逐列计算。 图 2-1-6 相关系数选项框的设置 确定以后，得到相关系数矩阵。由于相关矩阵是对称的，Excel只给出了下三角部分（图 2-1-7）。容易根据对称性将上三角部分填补起来（图2-1-8）