excel,拟合优度怎么求

【www.myl5520.com--说明文】

利用Excel进行线性回归分析
篇一:excel,拟合优度怎么求

文档内容

1. 利用Excel进行一元线性回归分析 2. 利用Excel进行多元线性回归分析

1. 利用Excel进行一元线性回归分析

第一步，录入数据

以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）。

图1

第二步，作散点图

如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在“插入”菜单中打开“图表（H）”

。图表向导的图标为2）。

。选中数据后，数据变为蓝色（图

1

图2

点击“图表向导”以后，弹出如下对话框（图3）：

图3

在左边一栏中选中“XY散点图”，点击“完成”按钮，立即出现散点图的原始形式（图4）：

2

图4

第三步，回归

观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。

回归的步骤如下：

1. 首先，打开“工具”下拉菜单，可见数据分析选项（见图5）：

图5

用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图6）：

3

图6

2. 然后，选择“回归”，确定，弹出如下选项表（图7）：

图7

进行如下选择：X、Y值的输入区域（B1:B11，C1:C11），标志，置信度（95%），新工作表组，残差，线性拟合图（图8-1）。

或者：X、Y值的输入区域（B2:B11，C2:C11），置信度（95%），新工作表组，残差，线性拟合图（图8-2）。

注意：选中数据“标志”和不选“标志”，X、Y值的输入区域是不一样的：前者包括数据标志：

最大积雪深度x(米) 灌溉面积y(千亩)

后者不包括。这一点务请注意（图8）。

4

图8-1包括数据“标志”

图8-2不包括数据“标志”

3. 再后，确定，取得回归结果（图9）。

5

Excel回归分析结果的详细阐释
篇二: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)，它是相关系数的平方，即有R2=0.9894162=0.978944。

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

(n1)(1R2)

Ra1

nm1

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

Ra1

(101)(10.978944)

0.976312

1011

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

s

1

SSe

nm1

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

s

1

*16.106761.418924

1011

最后一行的观测值对应的是样本数目，即有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，即有

ˆii)2748.8542 SSr(y

i1

n

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

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

ˆi)216.10676 SSe(yiy

i1

n

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

第三行为总平方和或

n

称总变差SSt，即有

SSr(yii)2764.961

i1



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

SSrSSeSSt

而测定系数就是回归平方和在总平方和中所占的比重，即有excel,拟合优度怎么求。

R2excel,拟合优度怎么求。

SSr748.8542

0.978944 SSt764.961

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

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

MSr

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

SSr748.8542

748.8542 dfr1

MSe

SSe16.10676

2.013345 dfe8

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

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

F

R2

1

(1R2)

nm1

dfeR2 21R

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

F

8*0.978944

371.9453

10.978944

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

第三部分，回归参数表

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

表3 回归参数表

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

ˆi2.35641.8129xi y

或

yi2.35641.8129xii

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

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

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

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

ta

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

ab，tb

ˆˆsasb

ta

1.8129212.356438

19.28588 1.289167，tb

0.0940021.827876

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

t

R1R

nm1

2

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

t

0.98941610.9894161011

2

19.28588

对于一元线性回归，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，即有

1.85865a6.57153

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

1.59615b2.02969

第四部分，残差输出结果

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

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

第三列，用ei表示）以及标准残差（表4）。

表4 残差输出结果

预测值是用回归模型

ˆi2.35641.8129xi y

计算的结果，式中xi即原始数据的中的自变量。从图1可见，x1=15.2，代入上式，得excel,拟合优度怎么求。

ˆ12.35641.8129x12.35641.8129*15.229.91284 y

其余依此类推。

残差ei的计算公式为

ˆi eiyiy

从图1可见，y1=28.6，代入上式，得到

ˆ128.629.912841.31284 e1y1y

其余依此类推。

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

zi*

zivar(zi)

n



zii

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

ˆi)216.10676 SSee(yiy

2

ii1

i1

n

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

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

ˆi为因变量，作散点图，可得线性拟合图（图4）用最大积雪深度xi为自变量，用灌溉面积yi及其预测值y。

Excel回归结果的解读
篇三: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)，它是相关系数的平方，即有R2=0.9894162=0.978944。

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

(n1)(1R2)Ra1 nm1

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

Ra1(101)(10.978944)0.976312 1011

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

s1SSe nm1

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

s1*16.106761.418924 1011

最后一行的观测值对应的是样本数目，即有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，即有

ˆii)2748.8542 SSr(y

i1n

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

SSr又称组间离差平方和，反应出不同的因子对样本波动的影响

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

ˆi)216.10676 SSe(yiy

i1n

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

SSe又称组内离差平方和，是不考虑组间方差的纯随机影响

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

SSt(yi

i1ni)2764.961

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

SSr

SSeSSt

总离差平方和 = 组间离差平方和 + 组内离差平方和

样本数据的波动有两个来源：一个是随机波动，一个是因子影响。样本数据的波动，可通过总离差平方和来反映。这个总离差平方和可分解为组间方差和组内方差两部分。 而测定系数就是回归平方和在总平方和中所占的比重，即有

R2SSr748.85420.978944 SSt764.961

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

方差、均方差：表示一组数相对平均值的离散程度

R2：预测值与实际值相对平均值的分布情况比较，越接近1，说明预测值和实际值的分布情况越接近。

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

MSr

第二行为剩余均方差MSe，即有 SSr748.8542748.8542 dfr1

SSe16.106762.013345 dfe8MSe

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

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

FR2

1(1R2)nm1dfeR2 1R2

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

F

8*0.978944371.9453 10.978944

方差、均方差：表示一组数相对平均值的离散程度

F检验完整公式

SSR

MSRn F

 SSEMSE

nm1

F检验法是英国统计学家Fisher提出的，主要通过比较两组数据的均方差，以确定他们的精密度是否有显著性差异。

F < F表 表明两组数据没有显著差异；

F ≥ F表 表明两组数据存在显著差异。

此处的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2.35641.8129xi y

或

yi2.35641.8129xii

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

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

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

第三列t Stat对应的是统计量t值，用于对模型参数的检验，需要查表才能决定。t值是

回归系数与其标准误差的比值，即有

ta根据表3中的数据容易算出： ab，tb ˆbˆass

ta2.3564381.8129211.289167，tb19.28588 1.8278760.094002

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

tR

1R

nm12

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

t0.989416

10.989416

1011219.28588

对于一元线性回归，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，即有

1.85865a6.57153

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

1.59615b2.02969

第四部分，残差输出结果

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

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

示），残差（residuals，第三列，用ei表示）以及标准残差（表4）。

表4 残差输出结果

用Excel做线性回归分析
篇四:excel,拟合优度怎么求

用Excel进行一元线性回归分析

Excel功能强大，利用它的分析工具和函数，可以进行各种试验数据的多元线性回归分析。本文就从最简单的一元线性回归入手.

在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势预测和残差分析等等。很多专业读者遇见此类问题时往往寻求专业软件，比如在化工中经常用到的Origin和数学中常见的MATLAB等等。它们虽很专业，但其实使用Excel就完全够用了。我们已经知道在Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。

文章使用的是2000版的软件,我在其中的一些步骤也添加了2007版的注解.

1 利用Excel2000进行一元线性回归分析

首先录入数据.

以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）。

图1

第二步，作散点图

如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在“插入”菜单中打开“图表（H）(excel2007)”。图表向导的图标为据变为蓝色（图2）。

。选中数据后，数

1

本文来源：http://www.myl5520.com/youxiuzuowen/101479.html