北京大学：地理数学方法配套教材《基于Excel的地理数据分析》书籍PDF电子版（编著：陈彦光）

研究生地理数学方法(实习) PartI 子表格 Excel 输入 Y值输入区域) :1:c11 确定 X值输入区域C 回标志 □常数为零 帮助0) □置信度) 输出选项 ○输出区域@ ⊙新工作表狙 ○新工作薄 回残差〕 回残差图 回标准残差〔 回线性拟合图〔 正态分布 回正态概率图 图1-24包括数据标志 输入 Y值输入区域 :2:c11习 □确定 x值输入区域〖) $BS2: $B511 匚取消 □标志 □常数为零 〔帮助0 □置信度) 输出选项 ○输出区域@ ⊙新工作表组 ○新工作簿⑦ 残差 回残差 残差图① 回标准残差〔r〕 陇性拟合图① 正态分布 回正态概率图 图1-25不包括数据标志 (3)给出回归结果:设置完成以后,确定,即可得到全部回归结果(图1-2-6) (4)读取参数:在图1-2-6所示的结果中,读取如下数据,据此建立模型并开展统计 分析。截距:a=0356:斜率:b=1.813;相关系数:R=0.989;测定系数:R2=0.979:F值: F=371945:t值:19.286;回归标准误差:s=1.419;回归平方和:SSr=748.854;剩余平方 和:SSe=16.107:总平方和:SSt764961

研究生地理数学方法（实习） Part1 电子表格 Excel 7 图 1-2-4 包括数据标志 图 1-2-5 不包括数据标志 （3）给出回归结果：设置完成以后，确定，即可得到全部回归结果（图 1-2-6）。 （4）读取参数：在图 1-2-6 所示的结果中，读取如下数据，据此建立模型并开展统计 分析。截距：a=0.356；斜率：b=1.813；相关系数：R=0.989；测定系数：R2 =0.979；F 值： F=371.945；t 值：t=19.286；回归标准误差：s=1.419；回归平方和：SSr=748.854；剩余平方 和：SSe=16.107；总平方和：SSt=764.961

研究生地理数学方法(实习) Part1电子表格 Excel K L W 1 SUNARY OUTPUT 最大积雪深度(米) Resi dual P 最大积雪深度(米) 误差1. 60 20 共罐 16.106762.013345 9764,961 Coefficien根准误差 t P= alue Lower9ypr95限95吐限",0 22 RESIDUAL OUTPUT PROBABILITY OUTPUT 4观渊值溉面 积(残差 差标准残差百分比排低面积(干亩) 726.830872.3691281.770947 2632221,467781,09718 1036,983230,416770.31154 48,9 图1-26线性回归结果 5)写出模型表达式:根据上面的回归结果建立回归模型,并对结果进行检验。回归 模型为 y=a+bx=2356+1.813 关于模型的统计检验,R、R2、F值、t值、标准误差值等均可以直接从回归结果中读出。 1.3.2模型的统计检验 对于一元线性回归,只需要开展相关系数检验、标准误差检验和DW检验即可。不过, 作为方法介绍,不妨给出较为全面的说明。 (1)相关系数检验。在相关系数检验表中查出,当显著性水平取a=005、剩余自由度 为4=10-1-1=8时,相关系数的临界值为R0s=0632。显然 R=0989416>0.632=R0058 检验通过。有了R值,F值和t值均可计算出来 (2)F检验。F值的计算公式和结果为 0.989416 =371.945>5.318=F00s 1-R2 10-1-1 (1-0.989416 式中n=10为样品数目,m=1为自变量数目。显然与表中的结果一样 (3)t检验。t值的计算公式和结果为 R 0.989416 9.286>2306=1005 1-09894162 1V10-1-1

研究生地理数学方法（实习） Part1 电子表格 Excel 8 图 1-2-6 线性回归结果 （5）写出模型表达式：根据上面的回归结果建立回归模型，并对结果进行检验。回归 模型为 yˆ = a + bx = 2.356 +1.813x . 关于模型的统计检验，R、R2 、F 值、t 值、标准误差值等均可以直接从回归结果中读出。 1.3.2 模型的统计检验 对于一元线性回归，只需要开展相关系数检验、标准误差检验和 DW 检验即可。不过， 作为方法介绍，不妨给出较为全面的说明。 （1）相关系数检验。在相关系数检验表中查出，当显著性水平取 α=0.05、剩余自由度 为 df=10-1-1=8 时，相关系数的临界值为 R0.05,8=0.632。显然 632 0.05,8 R = 0.989416 > 0. = R , 检验通过。有了 R 值，F 值和 t 值均可计算出来。 （2）F 检验。F 值的计算公式和结果为 0.05,1,8 2 2 2 2 371.945 5.318 (1 0.989416 ) 10 1 1 1 0.989416 (1 ) 1 1 F R n m R F = > = − − − = − − − = . 式中 n=10 为样品数目，m=1 为自变量数目。显然与表中的结果一样。 （3）t 检验。t 值的计算公式和结果为 0.05,8 2 2 19.286 2.306 10 1 1 1 0.989416 0.989416 1 1 t n m R R t = > = − − − = − − − =

研究生地理数学方法(实习) Part1电子表格 Excel 可见,F值为t值的平方,即有192862=371.945。上述结果Exce都已经直接给出,这里通 过验算有助于理解这些统计量之间的联系。 查F分布表和t分布表,可以得到F值和t统计量的临界值。实际上,在Excl中,利 用公式finv可以方便地查处F统计量的临界值。语法是:finv(a,m,n-m-1),即 finv(显著性水平,自变量数目,样品数目-自变量数目-1) 任选一个单元格,输入公式“=FINV005,1,10-2)”,回车,立即得到5317645,即 F0018=5318 类似地,利用公式tin可以方便地查处t统计量的临界值。语法是:tinv(a,n-m-1),即 tinv(显著性水平,样品数目-自变量数目-1) 任选一个单元格,输入公式“=TNV(0.05,10-2)”,回车,立即得到23060056,即 to058=2.306 24「观测值顸测灌溉殊差标准残差残差平方 129.91284-1.31284-0.981361.723544 26221.21082-1.91082-1.428553.65122 340.79036-0.29036-0.21705040.084312 28436.07677-0.47677-0.35639030.227309 50.21755-1.31755-0.9848521.735949 644.778790.2212090.165356111.048933 31726.830872.3691281.77094725.612766 832.632221.467781.097180822.154379 33945.866540.8334570.623017290.69465 4_1036.98230.416770.311539650.173697 残差平方和16.1068 图127y的预测值及其相应的残差等 4)标准误差检验。回归结果中给出了残差(图1-2-7),据此可以计算标准误差。首 先求残差的平方 (2-)2 然后求残差平方和 e=∑:2=1.724+…+0.174=16107 于是标准误差为 (y1-y;) 16.107 Vv 从而得到变异系数

研究生地理数学方法（实习） Part1 电子表格 Excel 9 可见，F 值为 t 值的平方，即有 19.2862 =371.945。上述结果 Excel 都已经直接给出，这里通 过验算有助于理解这些统计量之间的联系。 查 F 分布表和 t 分布表，可以得到 F 值和 t 统计量的临界值。实际上，在 Excel 中，利 用公式 finv 可以方便地查处 F 统计量的临界值。语法是：finv(α, m, n-m-1)，即 finv(显著性水平，自变量数目，样品数目-自变量数目-1) 任选一个单元格，输入公式“=FINV(0.05,1,10-2)”，回车，立即得到 5.317645，即 F0.05,1,8 = 5.318 . 类似地，利用公式 tinv 可以方便地查处 t 统计量的临界值。语法是：tinv(α, n-m-1)，即 tinv(显著性水平，样品数目-自变量数目-1) 任选一个单元格，输入公式“=TINV(0.05,10-2)”，回车，立即得到 2.3060056，即 t0.05,8 = 2.306 . 图 1-2-7 y 的预测值及其相应的残差等 （4）标准误差检验。回归结果中给出了残差（图 1-2-7），据此可以计算标准误差。首 先求残差的平方 2 2 ( ˆ ) i i i ε = y − y ， 然后求残差平方和 1.724 0.174 16.107 10 1 2 = ∑ = + + = = = L n i i SSe ε , 于是标准误差为 1.419 8 1 16.107 ( ˆ ) 1 1 1 2 − = = = − − = ∑= SSe v y y n m s n i i i , 从而得到变异系数

研究生地理数学方法(实习) Part1电子表格 Excel ==0.0388<10~15%=0.1~0.15 36.53 利用平方和函数 sumsq可以直接求出残差平方和。如图1-2-7所示,残差序列位于第 列,即C列(图1-2-6)。在任意空白单元格输入公式“= SUMSQ(C24C34)”,回车,得到 6.1067604。用这个数除以剩余自由度8,然后开平方根,即可得到标准误差1418924 (5)DW检验。DW值的计算公式及结果为 (-1.911+1.313) (0417-0.833) (-1313)2+(-1.911)2+…+0.4172 在 Excel中计算Dw值非常方便。只要在图1-24所示的选项中选中“残差(R)”,最后的 回归结果就会给出残差序列。将残差序列复制出来,在适当的地方粘贴两次,注意最好错位 粘贴(参见图1-2-8)。然后,利用其中一列减去另外一列,得到残差之差,或者残差序列的 差分。注意,原来的数据有m=10个,残差之差应为n1=9个。利用函数 sumsq计算残差序 列的平方和,结果为161068;然后用 sumsq计算残差之差的平方和,结果为12.0949。第 二个平方和除以第一个平方和,即残差之差的平方和除以残差的平方和,即可得到DW值。 具体说来,DW值=1209416.1068-=0.751。 残差 观测值「残差1,31284残差之差 1-1.31284-1.91082-0.59798 2-1.91082-0.290361.620453 3-0.29036-0.476770.18641 4-0.476771-1.31755-0.84078 5 -1.317550.2212091.538763 60.2212092.3691282.147919 2.3691281.46778-0.90135 81.467780.833457-0.63432 90.834570,41677-0.41669 10 0.41677 D啊值 16.1068 12.09490.75092 图1-28利用残差计算DW值 最后就是DW统计检验了。取 nF=10,m=1(剩余自由度4=10-1-1=8),在统计 表中查表得d=0.94,d=1.29。显然 值=0.751<d=0.94,这意味着有序列正相关,预 测的结果可能令人怀疑。当然,对于本例,由于n15,DW值的估计结果不可靠,严格意 义的DW检验无法进行 §13回归结果详解 13.1数据表的解读 利用Exce的数据分析进行回归,可以得到一系列的统计参量。下面将图1-2-6回归结 果摘要( Summary Output)放大(图1-3-1)

研究生地理数学方法（实习） Part1 电子表格 Excel 10 0.0388 10 ~ 15% 0.1 ~ 0.15 36.53 1.419 = = < = y s . 利用平方和函数 sumsq 可以直接求出残差平方和。如图 1-2-7 所示，残差序列位于第 3 列，即 C 列（图 1-2-6）。在任意空白单元格输入公式“=SUMSQ(C24:C34)”，回车，得到 16.1067604。用这个数除以剩余自由度 8，然后开平方根，即可得到标准误差 1.418924。 （5）DW 检验。DW 值的计算公式及结果为 0.751 ( 1.313) ( 1.911) 0.417 ( 1.911 1.313) (0.417 0.833) ( ) DW 2 2 2 2 2 1 2 2 2 1 = − + − + + − + + + − = − = ∑ ∑ = = − L L n i i n i i i ε ε ε . 在 Excel 中计算 DW 值非常方便。只要在图 1-2-4 所示的选项中选中“残差（R）”，最后的 回归结果就会给出残差序列。将残差序列复制出来，在适当的地方粘贴两次，注意最好错位 粘贴（参见图 1-2-8）。然后，利用其中一列减去另外一列，得到残差之差，或者残差序列的 差分。注意，原来的数据有 n=10 个，残差之差应为 n-1=9 个。利用函数 sumsq 计算残差序 列的平方和，结果为 16.1068；然后用 sumsq 计算残差之差的平方和，结果为 12.0949。第 二个平方和除以第一个平方和，即残差之差的平方和除以残差的平方和，即可得到 DW 值。 具体说来，DW 值＝12.094/16.1068=0.751。 图 1-2-8 利用残差计算 DW 值 最后就是 DW 统计检验了。取 α=0.05，n=10，m=1（剩余自由度 df=10-1-1=8），在统计 表中查表得 dl=0.94，du=1.29。显然，DW 值=0.751< dl=0.94，这意味着有序列正相关，预 测的结果可能令人怀疑。当然，对于本例，由于 n<15，DW 值的估计结果不可靠，严格意 义的 DW 检验无法进行。 §1.3 回归结果详解 1.3.1 数据表的解读 利用 Excel 的数据分析进行回归，可以得到一系列的统计参量。下面将图 1-2-6 回归结 果摘要（Summary Output）放大（图 1-3-1）

研究生地理数学方法(实习) Part1电子表格 Excel SUMMARY OUTPUT 回归统计 Multiple 0.989416 R Square.978944 Adjusted.976312 标准误差1.418924 10 方差分析 F nificance 回归分析 1748.8542748.8542371.94535.42E-08 残差 816.106762.013345 764.961 Coefficien标准误差 t Stat p-value lower95‰per95‰限95.0限95.0 e35843182861289160.238185956515271858561527 图1-3-1回归结果摘要(局部放大) 下面逐步说明如下 第一部分:回归统计表 这一部分给出了相关系数、测定系数、校正测定系数、标准误差和样本数目(图1-3-2) 回归统计 Multiple 0. 989416 R Square. 978944 Adjusted 0. 976312 标准误差1.418924 观测值 图1-3-2回归统计表 逐行解释如下: Multiple对应的数据是相关系数( correlation coefficient,即R=0.989416。 R Square对应的数值为测定系数( determination coefficient),或称拟合优度 ( goodness of fit),它是相关系数的平方,即有R2=09894162=0.978944 Adjusted对应的是校正测定系数( adjusted determination coefficient),校正公式为 R=/、(n-11-R2)_1(n-1(-R2) 式中n为样品数,m为变量数,v为自由度(df),R2为测定系数。对于本例,m=10,m=1 R=0.978944,代入上式得 R=1-0-101-097894420976312 10-1 标准误差( standard error)对应的即所谓回归标准误差,计算公式上一节己经给出。 将SSe=16.10676代入计算可得 *16.10676=1.418924 V10-1-1

研究生地理数学方法（实习） Part1 电子表格 Excel 11 图 1-3-1 回归结果摘要（局部放大） 下面逐步说明如下： 第一部分：回归统计表 这一部分给出了相关系数、测定系数、校正测定系数、标准误差和样本数目（图 1-3-2）。 图 1-3-2 回归统计表 逐行解释如下： z Multiple 对应的数据是相关系数(correlation coefficient)，即 R=0.989416。 z R Square 对应的数值为测定系数（determination coefficient），或称拟合优度 （goodness of fit），它是相关系数的平方，即有 R2 =0.9894162 =0.978944。 z Adjusted 对应的是校正测定系数（adjusted determination coefficient），校正公式为 v n R n m n R Ra ( 1)(1 ) 1 1 ( 1)(1 ) 1 2 2 − − = − − − − − = − , 式中 n 为样品数，m 为变量数，v 为自由度（df），R2 为测定系数。对于本例，n=10，m=1， R2 =0.978944，代入上式得 0.976312 10 1 1 (10 1)(1 0.978944) 1 = − − − − Ra = − . z 标准误差（standard error）对应的即所谓回归标准误差，计算公式上一节已经给出。 将 SSe=16.10676 代入计算可得 *16.10676 1.418924 10 1 1 1 = − − s =