Excel回归结果的解读

Excel回归结果的解读

利用Excel的数据分析进行回归，可以得到一系列的统计参量。下面以连续10年积雪深度和灌溉面积序列（图1）为例给予详细的说明。

图1 连续10年的最大积雪深度与灌溉面积（1971－1980） 回归结果摘要（Summary Output）如下（图2）：

图2 利用数据分析工具得到的回归结果

第一部分：回归统计表

这一部分给出了相关系数、测定系数、校正测定系数、标准误差和样本数目如下（表1）：

表1 回归统计表

逐行说明如下：

Multiple 对应的数据是相关系数(correlation coefficient)，即R=0.989416。

R Square 对应的数值为测定系数(determination coefficient)，或称拟合优度(goodness of fit)，它是相关系数的平方，即有R 2=0.9894162=0.978944。

Adjusted 对应的是校正测定系数(adjusted determination coefficient)，计算公式为

1

)

1)(1(12?????

=m n R n R a 式中n 为样本数，m 为变量数，R 2为测定系数。对于本例，n =10，m =1，R 2=0.978944，代入上式得

976312.01

110)

978944.01)(110(1=?????

=a R

标准误差（standard error ）对应的即所谓标准误差，计算公式为

SSe 1

1

??=

m n s

这里SSe 为剩余平方和，可以从下面的方差分析表中读出，即有SSe=16.10676，代入上式可得

418924.110676.16*1

1101

=??=

s

最后一行的观测值对应的是样本数目，即有n =10。

第二部分，方差分析表

方差分析部分包括自由度、误差平方和、均方差、F 值、P 值等（表2）。

表2 方差分析表（ANOVA）

逐列、分行说明如下：

第一列df 对应的是自由度（degree of freedom ），第一行是回归自由度dfr ，等于变量数目，即dfr=m ；第二行为残差自由度dfe ，等于样本数目减去变量数目再减1，即有dfe=n -m -1；第三行为总自由度dft ，等于样本数目减1，即有dft=n -1。对于本例，m =1，n =10，因此，

dfr=1，dfe=n -m -1=8，dft=n -1=9。

第二列SS 对应的是误差平方和，或称变差。第一行为回归平方和或称回归变差SSr ，即有

8542.748)?(SSr 1

2=?=∑=n

i i i y y

它表征的是因变量的预测值对其平均值的总偏差。

第二行为剩余平方和（也称残差平方和）或称剩余变差SSe ，即有

10676.16)?(SSe 1

2=?=∑=n

i i i y

y 它表征的是因变量对其预测值的总偏差，这个数值越大，意味着拟合的效果越差。上述的y

的标准误差即由SSe 给出。

第三行为总平方和或称总变差SSt ，即有

961.764)(SSr 1

2=?=∑=n

i i i y y

它表示的是因变量对其平均值的总偏差。容易验证748.8542+16.10676=764.961，即有

SSt SSe SSr =+

而测定系数就是回归平方和在总平方和中所占的比重，即有

978944.0961

.7648542

.748SSt SSr 2===

R 显然这个数值越大，拟合的效果也就越好。

第四列MS 对应的是均方差，它是误差平方和除以相应的自由度得到的商。第一行为回归均方差MSr ，即有

8542.74818542

.748dfr SSr MSr ===

第二行为剩余均方差MSe ，即有

013345.28

10676

.16dfe SSe MSe ===

显然这个数值越小，拟合的效果也就越好。

第四列对应的是F 值，用于线性关系的判定。对于一元线性回归，F 值的计算公式为

2

2

22

1dfe )1(1

R

R R m n R F ?=???=

式中R 2=0.978944，dfe=10-1-1=8，因此

9453.371978944

.01978944

.0*8=?=

F

第五列Significance F 对应的是在显著性水平下的F α临界值，其实等于P 值，即弃真概率。所谓“弃真概率”即模型为假的概率，显然1-P 便是模型为真的概率。可见，P 值越小越好。对于本例，P =0.0000000542<0.0001，故置信度达到99.99%以上。

第三部分，回归参数表

回归参数表包括回归模型的截距、斜率及其有关的检验参数（表3）。

表3 回归参数表

第一列Coefficients 对应的模型的回归系数，包括截距a =2.356437929和斜率b =1.812921065，由此可以建立回归模型

i i x y

8129.13564.2?+= 或

i i i x y ε++=8129.13564.2

第二列为回归系数的标准误差（用a s ?或b s ?表示），误差值越小，表明参数的精确度越高。

这个参数较少使用，只是在一些特别的场合出现。例如L. Benguigui 等人在When and where is a city fractal ?一文中将斜率对应的标准误差值作为分形演化的标准，建议采用0.04作为分维判定的统计指标（参见EPB2000）。

不常使用标准误差的原因在于：其统计信息已经包含在后述的t 检验中。

第三列t Stat 对应的是统计量t 值，用于对模型参数的检验，需要查表才能决定。t 值是回归系数与其标准误差的比值，即有

a a s a t ?=

，b

b s b t ?=

根据表3中的数据容易算出：

289167.1827876.1356438.2==

a t ，28588.19094002

.0812921

.1==b t

对于一元线性回归，t 值可用相关系数或测定系数计算，公式如下

1

12

???=

m n R

R t

将R=0.989416、n =10、m =1代入上式得到

28588.191

110989416.01989416.02

=???=

t

对于一元线性回归，F 值与t 值都与相关系数R 等价，因此，相关系数检验就已包含了这部分信息。但是，对于多元线性回归，t 检验就不可缺省了。

第四列P value 对应的是参数的P 值（双侧）。当P<0.05时，可以认为模型在α=0.05的水平上显著，或者置信度达到95%；当P <0.01时，可以认为模型在α=0.01的水平上显著，或者置信度达到99%；当P <0.001时，可以认为模型在α=0.001的水平上显著，或者置信度

达到99.9%。对于本例，P=0.0000000542<0.0001，故可认为在α=0.0001的水平上显著，或者置信度达到99.99%。P 值检验与t 值检验是等价的，但P 值不用查表，显然要方便得多。

最后几列给出的回归系数以95%为置信区间的上限和下限。可以看出，在α=0.05的显著水平上，截距的变化上限和下限为-1.85865和6.57153，即有

57153.685865.1≤≤?a

斜率的变化极限则为1.59615和2.02969，即有

02969.259615.1≤≤b

第四部分，残差输出结果

这一部分为选择输出内容，如果在“回归”分析选项框中没有选中有关内容，则输出结果不会给出这部分结果。

残差输出中包括观测值序号（第一列，用i 表示），因变量的预测值（第二列，用i y

?表示），残差（residuals ，第三列，用e i 表示）以及标准残差（表4）

。 表4 残差输出结果

预测值是用回归模型

i i x y

8129.13564.2?+= 计算的结果，式中x i 即原始数据的中的自变量。从图1可见，x 1=15.2，代入上式，得

118129.13564.2?x y

+=91284.292.15*8129.13564.2=+= 其余依此类推。

残差e i 的计算公式为

i i i y

y e ??= 从图1可见，y 1=28.6，代入上式，得到

31284.191284.296.28?111?=?=?=y

y e 其余依此类推。

标准残差即残差的数据标准化结果，借助均值命令average 和标准差命令stdev 容易验证，残差的算术平均值为0，标准差为1.337774。利用求平均值命令standardize(残差的单元格范围，均值，标准差)立即算出表4中的结果。当然，也可以利用数据标准化公式

)

var(*i i i z z z z ?=

i

i z

z σ?=

逐一计算。将残差平方再求和，便得到残差平方和即剩余平方和，即有

10676.16)?(1

21

2

=?==∑∑==n

i i i n i i y

y e SSe 利用Excel 的求平方和命令sumsq 容易验证上述结果。

以最大积雪深度x i 为自变量，以残差e i 为因变量，作散点图，可得残差图（图3）。残差点列的分布越是没有趋势（没有规则，即越是随机），回归的结果就越是可靠。

用最大积雪深度x i 为自变量，用灌溉面积y i 及其预测值i y

?为因变量，作散点图，可得线性拟合图（图4）。

图3 残差图

图4 线性拟合图

第五部分，概率输出结果

在选项输出中，还有一个概率输出（Probability Output）表（表5）。第一列是按等差数列设计的百分比排位，第二列则是原始数据因变量的自下而上排序（即从小到大）——选中图1中的第三列（C 列）数据，用鼠标点击自下而上排序按钮，立即得到表5中的第二列数值。当然，也可以沿着主菜单的“数据(D)→排序(S)”路径，打开数据排序选项框，进行数据排序。

用表5中的数据作散点图，可以得到Excel所谓的正态概率图（图5）。

5 概率输出表

表

图5 正态概率图

【几点说明】

第一， 多元线性回归与一元线性回归结果相似，只是变量数目m≠1，F值和t值等统计量与R值也不再等价,因而不能直接从相关系数计算出来。

第二， 利用SPSS给出的结果与Excel也大同小异。当然，SPSS可以给出更多的统计量，如DW值。在表示方法上，SPSS也有一些不同，例如P Value（P值）

用 Sig.（显著性）表征，因为二者等价。只要能够读懂Excel的回归摘要，

就可以读懂SPSS回归输出结果的大部分内容。

EXCEL数据分析处理

EXCEL数据分析处理 Excel提供了强大的数据分析处理功能，利用它们可以实现对数据的排序、分类汇总、筛选及数据透视等操作。 在进行数据分析处理之前，首先必须注意以下几个问题： （1）避免在数据清单中存在有空行和空列。 （2）避免在单元格的开头和末尾键入空格。 （3）避免在一张工作表中建立多个数据清单，每张工作表应仅使用一个数据清单。 （4）在工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。 （5）关键数据应置于数据清单的顶部或底部。 2.3.1 数据排序 2.3.1.1 数据排序的规则 Excel允许对字符、数字等数据按大小顺序进行升序或降序排列，要进行排序的数据称之为关键字。不同类型的关键字的排序规则如下： 数值：按数值的大小。 字母：按字母先后顺序。 日期：按日期的先后。 汉字：按汉语拼音的顺序或按笔画顺序。 逻辑值：升序时FALSE排在TRUE前面，降序时相反。 空格：总是排在最后。 2.3.1.2 数据排序步骤 （1）单击数据区中要进行排序的任意单元格。 （2）单击【数据】菜单，选择【排序】项，系统将弹出【排序】对话框，如图2-35所示。 图2-35 【排序】对话框

（3）在【排序】对话框中用下拉列表框选择要排序的关键字，关键字有“主要关键字”、“次要关键字”和“第三关键字”，根据需要分别选择不同的关键字； （4）单击【确定】按钮，数据就按要求进行了排序。 当只有一个关键字时，可以单击工具栏上的升序按钮或降序按钮，进行自动排序。 2.3.1.3 自定义排序 在有些情况下，对数据的排序顺序可能非常特殊，既不是按数值大小次序、也不是按汉字的拼音顺序或笔画顺序，而是按照指定的特殊次序，如对总公司的各个分公司按照要求的顺序进行排序，按产品的种类或规格排序等等，这时就需要自定义排序。 利用自定义排序方法进行排序，首先应建立自定义序列，其方法可参阅第1章的有关内容。建立好自定义序列后，即可对数据进行排序，方法是：单击数据区中要进行排序的任意单元格，单击【数据】菜单，选择【排序】项，在弹出的【排序】对话框中单击【选项】按钮，系统弹出【排序选项】对话框，如图2-36所示，在【自定义排序次序】的下拉列表中，选择前面建立的自定义序列，然后单击【确定】按钮，即可对数据进行自定义排序。 图2-36 【排序选项】对话框 2.3.2 数据的查找与筛选 企业的管理人员经常需要在数据库或数据清单众多的数据中找出需要的数据，Excel提供了功能强大的数据查找与筛选工具。数据查找是指从原始数据中提取满足条件的数据记录，源数据不会改变，也不会被隐藏；数据筛选是指把数据库或数据清单中所有不满足条件的数据记录隐藏起来，只显示满足条件的数据记录。常用的数据查找与筛选方法有：记录单查找、自动筛选和高级筛选。 下面结合实例说明各种查找方法的具体应用。

智慧树知到《excel数据处理与分析》章节测试答案

第一章单元测试 1、在Excel中，一个工作簿就是一个Excel文件，其扩展名为（）。A:.XLSX B:.DBFX C:.EXEX D:.LBLX 答案:【.XLSX】 2、在Excel中，一个工作簿可以包含（）工作表。 A:1个 B:2个 C:多个 D:3个 答案:【多个】 3、Excel电子表格A1到C5为对角构成的区域，其表示方法是（）。A:A1:C5 B:A1,C5 C:C5;A1 D:A1+C5 答案:【A1:C5】 4、以下单元格引用中，下列哪一项属于混合引用（）。 A:E3 B:$C$18

C:$D$13 D:B$20 答案:【B$20】 5、在Excel中，下面关于单元格的叙述正确的是（）。 A:在编辑的过程中，单元格地址在不同的环境中会有所变化 B:工作表中单元格是由单元格地址来表示的 C:为了区分不同工作表中相同地址的单元格地址，可以在单元格前加上工作表的名称，中间用“#”间隔 D:A4表示第4列第1行的单元格 答案:【工作表中单元格是由单元格地址来表示的】 6、Excel在公式运算中，如果引用第6行的绝对地址，第D列的相对地址，则应为（）。 A:$D6 B:D$6 C:$6D D:6D 答案:【D$6】 7、在Excel中，进行公式复制时（）发生改变。 A:绝对地址中的地址表达式 B:绝对地址中所引用的单元格 C:相对地址中的地址偏移量 D:相对地址中所引用的单元格 答案:【相对地址中所引用的单元格】 8、在下列Excel运算符中，优先级最高的是（）。

A:& B:<> C:: D:^ 答案:【:】 9、设在B1单元格存有一公式为：=A$5，将其复制到D1后，公式变为=C$5。 A:对 B:错 答案:【对】 10、设在单元格A1中有公式：=B1+B2，若将其复制到单元格C1中则公式为=D1+D2。 A:错 B:对 答案:【对】 第二章单元测试 1、在Excel中，给当前单元格输入数值型数据时，默认为（）。 A:左对齐 B:居中 C:随机 D:右对齐 答案:【右对齐】 2、在工作表的单元格内输入数据时，可以使用"自动填充"的方法，填充柄是选定区域（）的小黑方块。