Excel在计量地理学教学中的应用以简单线性回归分析为例.pdf

3 2009205211收到, 2009206230改回 3 3 张海峰,男, 1969年生,副教授,研究方向:区域发展与管理。 文章编号: 100325850(2009)0820045205 Excel在计量地理学教学中的应用 以简单线性回归分析为例 Application of M icrosoft Excel to Quantitative Geography Teaching A Case Study of Si mple L inear Regression 张海峰 (青海师范大学生命与地理科学学院 西宁 810008) 【摘 要】 讨论了M icrosoft Excel与大型专业统计分析软件的优缺点,进而阐明了M icrosoft Excel在地理学一般 定量分析中的广泛性及其应用于计量地理学教学的必要性,并以简单线性回归分析为例,说明了其在计量地理 学教学中的应用步骤与实现方法。 【关键词】计量地理学, M icrosoft Excel,线性回归分析,应用 中图分类号:TP391文献标识码: A ABSTRACT This paper made an introduction to the analysis of si mple linear regression of Excel I N TERCEPT,SLOPE, FORECA ST, TREND, L I N EST, RSQ and STEYX, and their application in statistical analysis1These functions are very suitable to use in statistic analysis and in geography teaching, as they not only solve the problem of complicated interpolation and inaccurate exam in the table checking method, but also avoid the troublesome computer programm ing1 KEYWORDS quantitative geography, M icrosoft Excel,si mple linear regression, application 计量地理学,是20世纪50年代末期以来发展起 来的现代地理学的方法论学科,是地理系统分析与建 模的基本工具。 地理系统是由各种要素组成的多要素、 多级别、 复杂的、 开放的巨系统,各要素之间相互联系、 相互影响、 相互作用、 相互制约。 在地理学研究中,常常 用到回归分析,来定量研究相关要素之间的定量关系, 用以建立数学模型,进而对其未来进行预测,从而为相 关决策提供参考依据。 由于计算机技术的长足进步和发展,在进行回归 分析时,很少有人再利用手算或是用计算器来进行数 据分析,大多使用较著名的统计分析软件包,如SA S, SPSS, BMDP, STA T IST ICA , SYSTA T, EV IEW S, MA TLAB, S2PLU S等。这些统计分析软件包固然功 能强大,不仅能进行单变量分析,而且可做各种复杂的 多变量分析。 然而,这些大型统计分析软件存在两大缺 点, 大型软件价格过于昂贵(高达数万元至数十万 元 ), 通常是政府机构、 企业或学校购买, SA S更是只 租不卖,对一般、 普通民众、 甚至一些小公司而言,都负 担不起,因此,普及性低。 大型软件艰深难懂,非短 期内所能学会,通常需要数月甚至更长时间才能熟练 应用。况且,在对数据进行处理时,遇到的问题大多是 一些简单的统计分析,如简单统计描述,画各种统计图 表,或者进行t检验、 方差分析、 相关分析及回归分析。 作这些统计分析时,大多可使用M icrosoft Office软件 包中的Excel软件来实现。 Excel是一种使用极方便的电子表格软件,有强 大的数据管理功能,能制作各种统计图表,具有丰富的 财会和统计函数,并且Excel在 “分析工具库” 中,提供 了一组数据分析工具。 使用这些分析工具时,只需指出 数据所在的单元格和提供必要的参数,该工具就会使 用适宜的统计或工程函数,对数据做处理,给出相应的 结果,有些工具在输出时还能产生类型多样的统计图 表。Excel不仅价格便宜,而且非常普及,几乎所有的 品牌机都预装有M icrosoft Office。虽然Excel的统计 功能不及上述专业统计软件,不过对于大多数用户及 地理学研究中许多问题而言, Excel能够满足需要。更 何况Excel强大的会计、 绘图、 数据库管理等功能是上 述软件所欠缺的。此外, Excel与W ord的兼容性也是 其他软件所不能及的,这为将Excel定量分析结果及 相关图件导入W ord文档提供了极大的方便。基于上 述原因,在计量地理学教学中,充分发挥Excel的统计 分析功能是必要而可行的。当然,对于Excel所不能解 决的问题,仍然需要介绍和学习专业统计分析软件。 这 里要强调的是对于Excel的一些基础应用,许多学生 在计算机基础等公共选修课程中已学过,在计量地理 学教学中,主要针对其统计分析的相关功能进行详细 学习。 本文以简单线性回归分析为例,简要说明Excel 54 第22卷 第8期电 脑 开 发 与 应 用 (总 637) 在计量地理学教学中的应用。 1 Excel中常用线性回归分析及预测函数简 介 在Excel中 提 供 了 丰 富 的 统 计 函 数,例 如 I N TERCEPT,SLOPE,L I N EST, FORECA ST , TREND, RSQ , STEYX等,这些函数为进行线性回 归分析和预测提供了良好的工具支持。要正确合理 地应用及操作Excel进行简单线性回归分析,首先需 要对有关函数进行必要了解,下面对主要函数进行 简要介绍。 I N TERCEPT该函数利用已知的x值与y值计 算直线与y轴的截距。 截距为穿过known xs和known ys数据点的线性回归线与y轴的交点。 当已知自变量 为零时,利用截距可以决定因变量的值。函数语法为: I N TERCEPT(knownys, knownxs) 其中: Knownys为所观察的因变量数据或数据组。 Knownxs为所观察的自变量数据或数据组。 SLOPE 该函数返回根据knownxs和knownys中的 数据点拟合的线性回归直线的斜率。斜率为直线上任 意两点的垂直距离与水平距离的比值,也就是回归直 线的变化率。函数语法为: SLOPE(knownys, knownxs) 其中: Knownys为数字型因变量数组或单元格区 域。 Knownxs为自变量数据点集合。 FORECA ST 该函数根据给定的数据计算或预测未来值。此预 测值为基于一系列已知的x值推导出的y值。以数组 或数据区域的形式给定x值和y值后,返回基于x的线 性回归预测值。 使用此函数可以对未来值进行预测。 函 数语法为: FORECA ST(X, knownys, knownxs) 其中: X为需要进行预测的数据点。 knownys为因变量数组或数据区域。 knownxs为自变量数组或数据区域。 TREND 该函数返回一条线性回归拟合线的一组纵坐标值 (y值 ), 即找到适合给定的数组knownys和known xs的直线(用最小二乘法 ), 并返回指定数组newxs 值在直线上对应的y值。函数语法为: TREND(knownys, knownxs, newxs, const) 其中: 参数known ys、known xs和newxs必须是 具有相关尺寸的数组或单元格区域。 knownys为已知关系y= mx+ b中的值集合。 如果knownys包含1列m行,则knownxs 包含c列m行,其中c大于或等于1。c是预测值变量的 数目;m是数据点数目。 newxs必须包含c列r行,其中r大于或等于1。 (如果数据排列在行中而不是排列在列中,则必须保持 类似的尺寸关系。) constant是一个逻辑参数,必须设置为TRU E或 FAL SE(或者0或1, Excel分别将其解释为FAL SE 或TRU E。)TREND的最后三个参数都是可选的;省 略第四个参数将被解释为TRU E。 L I N EST 该函数使用最小二乘法对已知数据进行最佳直线 拟合,并返回描述此直线的数组。 因为此函数返回数值 数组,故必须以数组公式的形式输入。 假定直线方程为: y=m x+b或y=m1x1+m2x2+b(如果x值是 多重的) 式中因变量y是自变量x的函数值。mi(i= 1, 2, )值是与每个xi(i= 1, 2,)相对应的系数,b是常 数。注意y、x和m可以是向量。函数L I N EST返回的 数组是mn,mn- 1,m1,b。函数L I N EST还可返回 附加回归统计值。函数语法为: L I N EST(knownys,knownxs,const, stats) 其中: knownys是关系表达式y= mx+ b中已知的y 集合。 如果数组knownys在一列中,则Knownxs的 每一列都被当作单独的变量。 如果数组knownys在一行中,则Knownxs的 每一行都被当作单独的变量。 knownxs是关系表达式y= mx+ b中已知的可 选x值集合。 数组knownxs中包括一个或多个变量集合。如 果只用到一个变量,只要knownys和knownxs维 数相同,可以是任何形状的选定区域。 如果用到不只一 个变量, knownys必须是向量(即必须是一行或一 列的区域。) 64 (总 638) Excel在计量地理学教学中的应用2009年 如果省略knownxs,则假设该数组是1, 2, 3111,其大小与knownys相同。 const为一逻辑值,指明是否强制使常数b为0。 如果const为TRU E或省略,b将被正常计算。 如果const为FAL SE,b将被设为0,并同时调整m 值使y=m x。 stats为一逻辑值,指明是否返回附加回归统计 值。 如果stats为TRU E,函数L I N EST返回附加回归 统计值,如果stats为FAL SE或省略,函数L I N EST只 返回系数m和常数项b。 RSQ 该函数返回根据knownys和knownxs中数 据点计算得出的Pearson积矩相关系数R的平方。R平 方值可以解释为y方差与x方差的比例。函数语法为: RSQ(knownys, knownxs) 其中: knownys为数组或数据点区域。 knownxs为数组或数据点区域。 STEYX 该函数返回通过线性回归计算y预测值时所产生 的标准误差。标准误差用来试题根据间个x变量计算 出的y预测值的误差量。函数语法为: STEYX(knownys, knownxs) 其中: knownys为数组或数据点区域。 knownxs为数组或数据点区域。 2 简单线性回归分析过程与方法 211 相关分析 进行回归分析,首先要通过定性分析,即要素之间 关系进行判定时,遵循 “专业分析先于统计分析” 原则, 在此过程中可通过编制相关表、 绘制相关图和计算相 关系数等方法来判断现象之间是否存在相关关系、 相 关关系的类型及密切程度。 表1 北京市各月平均气温与5cm平均地温表单位: 月份123456789101112 气温- 417- 2134141312201224122610241619151215410- 218 地温- 316- 1145111415221326192812261521111314416- 119 数据来源:张超,杨秉赓1 计量地理学基础,第88页,高等教育出版社, 1991年第2版。 散点图的绘制 散点图是根据相关表中的观测 数据在坐标图中所绘制的点状图形。散点图用于直观 地表现测量数据的原始分布状况,可从点的位置判断 测量值的高低、 大小、 变动趋势或变化范围,从而在概 判断现象间的相关关系,是一种直观描述变量之间相 互关系的图形。 我们可以利用Excel提供的强大的图表 功能,将统计表中的数据用图表的形式表示出来。这里 以北京市各月平均气温与5cm平均地温测量数据为例 (表1),以M icrosoft Excel2003为软件平台进行分析, 具体操作步骤如下: a1 选定工作表中的平均气温和平均地温两列数 据,单机插入菜单的图表菜单项,启动图表向导。 b1 在图表向导的标准类型中选择 “X Y散点 图”,在 “子图表类型” 中选择 “散点图”,并单击 “下一 步” 。 c1 选择数据区域选项卡,在 “数据区域” 栏导入 平均气温和平均地温两列数据,系列产生选择 “列”,并 单击 “下一步” 。 d1 在图表选项对话框中设置,标题为 “散点图”, X轴名称 “气温”,Y轴名称 “地温”,图例选择不显示 图,并单击 “下一步” 。 e1 设置创建图表放置位置。 f1 单击 “完成” 。即可以完成散点图的绘制(如图1 所示)。 图1 北京市各月平均气温与5cm平均地温散点图 图1表明,北京市各月平均气温与5cm平均地温 两者之间具有很强直线相关性。 相关系数的计算。散点图显示的是要素之间符 合何种曲线关系的一个大概的结论,通常需要更加深 入的定量分析,精确地界定变量之间的关系,把变量之 间关联的紧密程度用统计指标定量予以衡量。相关系 数主要用于检验拟合模型的线性关系的显著性程度。 相关系数的平方(R 2) 称为测定系数(determ ination coefficient,或译 “决定系数” 、 “判定系数” 、 “可决系数” 等 ), 又叫拟合优度(goodness of fit)。相关系数的计算 公式为: R= (x i-x ) (y i-y ) (x i-x )2(y i-y )2= cov(xi,yi) V ar(xi)V ar(yi) = 74 第22卷 第8期电 脑 开 发 与 应 用 (总 639) x iyi- x iyi?n x 2 i- 1 n (xi) 2 y 2 i- 1 n (yi) 2 (1) 式中x = 1 n n i= 1 xi,及y = 1 n n i= 1 yi为平均值。 可以快速方便地利用Excel提供的函数完成相关 系数的计算。具体步骤如下: a1 单击插入菜单栏的函数菜单项,在搜索函 数中,输入 “相关系数”,单击 “转到” 。 b1 选择函数 “CORREL”,单击 “确定” 。 c1 在A rray1和A rray2中分别导入气温和地温数 据,单击 “确定” 。 求得相关系数r= 01999 51,说明北京市各月平均 气温与地温之间存在着高度的直线相关。接下来可以 根据观测资料构建回归方程,进行回归分析。 212 回归分析 如果两个变量之间的关系接近直线型,则所求得 的回归方程为线性方程。回归分析的重要内容之一,就 是根据变量观测值计算截距a和斜率b,构建回归方程。 回归分析有两种途径来实现,一种为通过插入菜单 中的 “函数” 命令进行,另一种方法是通过工具菜单中 的 “数据分析” 命令进行。 通过求截距(a)和斜率(b)函数来实现。截距是 拟合回归直线与Y轴的交点, Excel可以直接利用两个 变量的观察值来求回归直线的截距,函数功能及语法 如前文所述I N TERCEPT。实际操作中,前几步与相关 系数计算的操作相似,在弹出的对话框中,变量known ys赋值:导入 “平均地温” 的数据;变量knownxs 赋值:导入 “平均气温” 的数据;并单击 “确定” 按钮,就 可以快速求出截距a= 01837 83。 用同样的方法,利用SLOPE函数,求出斜率b= 11049 323。可以得出北京市各月平均气温与5cm平均 地温之间的回归直线方程为y= 01837 8+ 11049 323x。 通过工具菜单中的 “数据分析” 命令来实现,单 击工具菜单,选择 “数据分析” 命令,弹出数据分析对 话框,选择 “回归” 命令,弹出 “回归” 对话框,选择Y值 及X值区域、 置信度、 残差、 输出区域等选项,单击 “确 定” 就给出分析结果。 对于表1数据进行回归分析后结果如下: 表2 回归统计概要 M ultiple RR SquareAdjusted R Square标准误差观测值 01999 51201999 02401998 92701394 9612 表2表明,相关系数是01999 512,说明北京市各月 平均气温与 5m 平均地温高度相关;判定系数为01999 024,估计的标准误差为01394 96。 表3 方差分析 dfSSM SFSignificance F 回归分析11 59713431 597134310 239182118E216 残差1011559 93501155 994 总计111 5981903 方差分析表明,回归平方和SSreg是1 5971343,剩 余平方和SSe是11559 936,总平方和是1 5981903。各 自除以其自由度,即为其均方差M S。 回归均方差 M Sreg是1 5971343,剩余均方差M Se是01155 994,两者 相除得F检验值为10 23918,该值大于F检验临界值 10104,可见该回归模型显著性水平达0101,模型效果 显著。 表4 回归分析常量及变量系数检验 Coefficients标准误差t StatP2valueLower 95%Upper 95%下限9510%上限9510% Intercept01837 8301165 48651062 84701000 4901469 10411206 55501469 10411206 555 X Variable 111049 32301010 37 1011191 92118E21611026 21811072 42811026 21811072 428 表4是回归模型有关参数及其检验。常数项是 01837 83,自变量系数是11049 323。截距的t统计值 51062 487和p值01000 49,可以得知常数项也达到 0101的显著性水平。从自变量系数来看,t统计值是 1011191 9,p值是2118E216。 截距的标准误差为01165 486,其95%的置信区间 为 (0 1469 104, 11206 555)。 斜 率 的 标 准 误 差 为 01010 37,其95%的置信区间为 (1 1026 218, 11072 428) 。 表5 残差RESI DUAL OUTPUT 观测值预测Y标准残差残差观测值预测Y残差标准残差 1- 41093 9911311 77801493 9897281120 2301211 82801079 77 2- 11575 6101466 33801175 6138261651 18- 01401 45- 01151 18 351454 851- 01942 3- 01354 859211299 63- 01530 11- 01199 63 4141688 89- 01501 61- 01188 8910131954 37- 11472 11- 01554 37 5221034 1601705 94301265 8441151035 122- 11155 46- 01435 12 6261231 4511775 32601668 55212- 21100 2801531 82601200 275 (下转第51页) 84 (总 640) Excel在计量地理学教学中的应用2009年 按钮 “改变视角” 的回调函数viewCallback,用于 通过改变视角,观看信号的时频映射关系特性。 程序代 码如下: function viewCallback(hObject, eventdata, handles) axes(handles1 axes2) for b= 1: 5: 75 view(b, 12); pause (0 102); end 按钮 “频谱图” 的回调函数pingpuCallback,用于 根据视角变化图,找出时频对应关系,画出频谱图,程 序代码如下: function pingpuCallback(hObject, eventdata, handles) m=str2double(get(handles1cishu, String ); axes(handles1 axes4) an =zeros(m + 1, 1); an (1) =0; for i =1: 23m an(i+ 1) =2?(i3pi)3sin(i3pi?2); %计算系数an cn(i+ 1) =abs (2 ?(i3pi)3sin(i3pi?2); %计算幅 度谱 end k =0: 23m; stem(k, cn, linew idth, 2); axis (0 23 m 0 1); grid on 程序运行后,输入分解项数的数值(设输入5),按 下 “周期信号输出” 按钮,则输出方波,对周期方波信号 进行傅里叶级数分解,并对分解后的各次谐波动态叠 加演示,从中可以看出,叠加的项数越多,越接近周期 方波信号,同时也看到 “吉布斯” 现象;按下 “时频关系 观察” 按钮,则输出三维图形,在三维立体图中展示了 周期信号的谐波特性,即非正弦周期信号是由不同频 率的谐波组成,其各次谐波只能出现在基波频率的整 数倍上,其频谱是离散的,频率越高,该谐波的幅值越 小;按下改变视角按钮,则进行三维图形旋转,通过观 看旋转过程,可以很容易得出信号的时域特性和频域 特性之间的映射关系,为理