Excel回归分析结果的详细阐释

1 Excel 回归分析结果的详细阐释回归分析结果的详细阐释 利用 Excel 的数据分析进行回归 可以得到一系列的统计参量 下面以连续 10 年积雪深度和灌溉面积序列 图 1 为例给予详细的说明 图图 1 1 连续连续 1010 年的最大积雪深度与灌溉面积 年的最大积雪深度与灌溉面积 19711971 19801980 回归结果摘要 Summary Output 如下 图 2 图图 2 2 利用数据分析工具得到的回归结果利用数据分析工具得到的回归结果 第一部分 回归统计表 这一部分给出了相关系数 测定系数 校正测定系数 标准误差和样本数目如下 表 1 表表 1 1 回归统计表回归统计表 2 逐行说明如下 Multiple 对应的数据是相关系数 correlation coefficient 即 R 0 989416 R Square 对应的数值为测定系数 determination coefficient 或称拟合优度 goodness of fit 它是相关系数的平 方 即有 R2 0 9894162 0 978944 Adjusted 对应的是校正测定系数 adjusted determination coefficient 计算公式为 1 1 1 1 2 mn Rn Ra 式中 n 为样本数 m 为变量数 R2为测定系数 对于本例 n 10 m 1 R2 0 978944 代入上式得 976312 0 1110 978944 0 1 110 1 a R 标准误差 standard error 对应的即所谓标准误差 计算公式为 SSe 1 1 mn s 这里 SSe 为剩余平方和 可以从下面的方差分析表中读出 即有 SSe 16 10676 代入上式可得 418924 1 10676 16 1110 1 s 最后一行的观测值对应的是样本数目 即有 n 10 第二部分 方差分析表 方差分析部分包括自由度 误差平方和 均方差 F 值 P 值等 表 2 表表 2 2 方差分析表 方差分析表 ANOVAANOVA 逐列 分行说明如下 第一列 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 ii yy 它表征的是因变量的预测值对其平均值的总偏差 第二行为剩余平方和 也称残差平方和 或称剩余变差 SSe 即有 10676 16 SSe 1 2 n i ii yy 3 它表征的是因变量对其预测值的总偏差 这个数值越大 意味着拟合的效果越差 上述的 y 的标准误差即由 SSe 给出 第三行为总平方和或 称总变差 SSt 即有 它表示的是因变量对其平均值的总偏差 容易验证 748 8542 16 10676 764 961 即有 SStSSeSSr 而测定系数就是回归平方和在总平方和中所占的比重 即有 978944 0 961 764 8542 748 SSt SSr 2 R 显然这个数值越大 拟合的效果也就越好 第四列 MS 对应的是均方差 它是误差平方和除以相应的自由度得到的商 第一行为回归均方差 MSr 即有 8542 748 1 8542 748 dfr SSr MSr 第二行为剩余均方差 MSe 即有 013345 2 8 10676 16 dfe SSe MSe 显然这个数值越小 拟合的效果也就越好 第四列对应的是 F 值 用于线性关系的判定 对于一元线性回归 F 值的计算公式为 2 2 2 2 1 dfe 1 1 1 R R R mn R F 式中 R2 0 978944 dfe 10 1 1 8 因此 9453 371 978944 0 1 978944 0 8 F 第五列 Significance F 对应的是在显著性水平下的 F 临界值 其实等于 P 值 即弃真概率 所谓 弃真概率 即模型为假的概率 显然 1 P 便是模型为真的概率 可见 P 值越小越好 对于本例 P 0 0000000542 0 0001 故置信度达到 99 99 以上 第三部分 回归参数表 回归参数表包括回归模型的截距 斜率及其有关的检验参数 表 3 表表 3 3 回归参数表回归参数表 第一列 Coefficients 对应的模型的回归系数 包括截距 a 2 356437929 和斜率 b 1 812921065 由此可以建立回 归模型 ii xy8129 1 3564 2 或 961 764 SSr 1 2 n i ii yy 4 iii xy 8129 1 3564 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 1 827876 1 356438 2 a t28588 19 094002 0 812921 1 b t 对于一元线性回归 t 值可用相关系数或测定系数计算 公式如下 1 1 2 mn R R t 将 R 0 989416 n 10 m 1 代入上式得到 28588 19 1110 989416 0 1 989416 0 2 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 6 85865 1 a 斜率的变化极限则为 1 59615 和 2 02969 即有 02969 2 59615 1 b 第四部分 残差输出结果 这一部分为选择输出内容 如果在 回归 分析选项框中没有选中有关内容 则输出结果不会给出这部分结 果 残差输出中包括观测值序号 第一列 用 i 表示 因变量的预测值 第二列 用表示 残差 residuals i y 第三列 用 ei表示 以及标准残差 表 4 表表 4 4 残差输出结果残差输出结果 5 预测值是用回归模型 ii xy8129 1 3564 2 计算的结果 式中 xi即原始数据的中的自变量 从图 1 可见 x1 15 2 代入上式 得 11 8129 1 3564 2 xy 91284 29 2 15 8129 1 3564 2 其余依此类推 残差 ei的计算公式为 iii yye 从图 1 可见 y1 28 6 代入上式 得到 31284 1 91284 29 6 28 1 11 yye 其余依此类推 标准残差即残差的数据标准化结果 借助均值命令 average 和标准差命令 stdev 容易验证 残差的算术平均值 为 0 标准差为 1 337774 利用求平均值命令 standardize 残差的单元格范围 均值 标准差 立即算出表 4 中的结果 当然 也可以利用数据标准化公式 var i i i z zz z i i zz 逐一计算 将残差平方再求和 便得到残差平方和即剩余平方和 即有 10676 16 1 2 1 2 n i ii n i i yyeSSe 利用 Excel 的求平方和命令 sumsq 容易验证上述结果 以最大积雪深度 xi为自变量 以残差 ei为因变量 作散点图 可得残差图 图 3 残差点列的分布越是没有 趋势 没有规则 即越是随机 回归的结果就越是可靠 用最大积雪深度 xi为自变量 用灌溉面积 yi及其预测值为因变量 作散点图 可得线性拟合图 图 4 i y 最大积雪深度x 米 Residual Plot 3 2 1 0 1 2 3 051015202530 最大积雪深度x 米 残差 图图 3 3 残差图残差图 最大积雪深度x 米 Line Fit Plot 0 10 20 30 40 50 60 0102030 最大积雪深度x 米 灌溉面积y 千亩 灌溉面积y 千亩 预测 灌溉面积 y 千亩 图图 4 4 线性拟合图线性拟合图 第五部分 概率输出结果 在选项输出中 还有一个概率输出 Probability Output 表 表 5 第一列是按等差数列设计的百分比排位 第二列则是原始数据因变量的自下而上排序 即从小到大 选中图 1 中的第三列 C 列 数据 用鼠标点击 6 自下而上排序按钮 立即得到表 5 中的第二列数值 当然 也可以沿着主菜单的 数据 D 排序 S 路径 打开数据排序选项框 进行数据排序 用表 5 中的数据作散点图 可以得到 Excel 所谓的正态概率图 图 5 表表 5 5 概率输出表概率输出表 Normal Probability Plot 0 10 20 30 40 50 60 020406080100 Sample Percentile 灌溉面积y 千亩 图图 5 5 正态概率图正态概率图 几点说明 第一 多元线