Excel 在回归分析中的应用.pdf

第13卷第2期武钢职工大学学报Vol.13.No.2

2001年6月JournalofUniversityforStaffandWorkersofWISGCOJune.2001

Excel在回归分析中的应用

涂虬

(武钢职工大学土建系武汉:43008)

摘要回归分析方法是数量统计常用的一种方法。本文详细介绍了如何使用EXCEL进行回归分析
的二种方法“回归”分析工具以及工作表函数:,并以实例进行了讲解。
关键词Excel回归分析工程表函数显著性检验
中图分类号:文献标识码:A

1前言

回归分析方法是处理多个变量之间相互关系的一种数学方法,是数理统计常用方法之一。从分析测试

的观点来看,回归分析的任务就是找出响应值y(因变量)与影响它的诸因素xi(自变量,i=1,2,3,⋯n)之间
的统计关系(回归模型),利用这种统计关系在一定置信度下由各因素的取值去预测响应值的范围,在众多的
预报变量中,判断哪些变量对自变量的影响是显著的,哪些变量的影响是不显著的;根据预报变量的给定值
来估计和预测精度。
常用的回归模型包括线性回归、非线性回归,前者又可分为一元线性回归、多元线性回归,后者分为可化
为一元线性方程的回归方程,如冥函数、指数函数、对数函数等,以及可化为多元线性方程的回归方程,如多
项式方程。传统的回归分析方法是对线性回归模型采用最小二乘法来拟合回归方程,然后计算相关系数进
行显著性检验,而对非线性方程,还得对自变量和因变量作适当的变换,把非线性方程转化为线性方程,然后
再用线性回归的方法处理。这种传统的回归计算方法,尤其对于多元非线性方程,求解过程非常繁琐,计算
复杂。
Excel在回归分析中能避开复杂的计算,把复杂的计算过程交给计算机处理,使用起来非常简单。本文
将探讨如何使用Excel进行回归分析。

2Excel回归分析过程与方法介绍

2.1回归分析过程
回归分析过程就是通过样本观测数据对模型中的因变量f(x)进行估计,分析随机误差项的分布特征,
使用模型进行预测预报,任何一个回归分析过程包括以下内容:
(1)建立回归模型,确定自变量和因变量,判定因变量f(x)的形式。
(2)用样本数据来拟合模型中的未知参数。
(3)确定随机误差项和估计量的分布性质。
(4)拟合优度检验,验证用X预测Y的拟合程度。
(5)利用模型进行预测和控制。
预测问题是对固定的X值预测相应的Y值,而控制问题可以看成是预测问题的反问题,通过控制X值
以便把Y的值控制在指定的范围内。

来稿日期:2001.02.05
作者简介:涂虬,男,1970年生,武钢职工大学副教授。
涂虬:Excel在回归分析中的应用52

采用Excel以取代传统的数学方法,计算(2),(3),(4),(5),这正是Excel用以回归分析的方便所在。
2.2回归分析方法介绍
Excel为用户提供了二种回归分析工具“回归”分析工具、工作表函数。:
2.2.1“回归”分析工具使用方法介绍
(1)在Excel工作区单元格中录入样本观测数据。
(2)建立回归模型,确定因变量f(x)的形式。
取样本观测数据,录入Excel中的图表向导,选用“XY散点图”,把样本数据连成曲线,观察曲线形状,建
立回归方程。
(3)启动“回归分析”分析工具
从“工具”菜单中选择“数据分析”子菜单,打开“数据分析”对话框,从中选择“回归”分析工具。如果“工
具”菜单中没有“数据分析”,从“工具”中选定“加载宏”,在“加载宏”中选定“分析工具库”即可在“工具”菜单
中加载“数据分析”。
(4)填写“回归分析”对话框,如图1所示。

“回归”对话框

(5)回归结果分析在回归“对话框中按“确定”按钮,可以得到回归结果。
2.2.2工作表函数使用说明
Excel提供了9个函数用于建立回归模型和回归预测,其中5个函数用于一元回归分析,4个函数用于
多元回归分析,这些函数列于表1。
表1用于回归分析的工作表函数

函数名定义语法格式

INTERCEPT一元线性回归模型α的估计值(截距)INTERCFPT(Known-y’s,Known-x’s)

SLOPE一元线性回归模型β的估计值(斜率)SLOPE(Known-y’s,Known-x’s)

RSQ一元线性回归模型的决定系数(r2)SRQ(Known-y’s,Known-x’s)

FORECAST依照一元线性回归模型的预测值FORECAST(new-x’s,Known-y’s,Known-x’s)

STEYX依照一元线性回归模型的标准误差STEYX(Known-y’s,Known-x’s)

TREND依照多元线性回归模型的预测值TREND(Known-y’s,Known-x’s,new-x’s,const)

GROWTH依照多元指数回归