第 8章 回归分析
8.1 线性回归分析的基本原理
8.2 图表分析与回归函数分析
8.3 Excel回归分析工具
8.4 多元回归分析
8.5 非线性回归分析本章学习目标
u回归分析的基本思想
u利用 Excel图表进行线性回归分析
u利用 Excel回归分析工作表函数进行线性回归分析
u利用 Excel回归分析工具进行一元及多元线性回归分析
u非线性回归分析的基本思路
8.1 线性回归分析的基本原理
8.1.1 回归分析的概念
8.1.2 回归分析的主要内容返回首页
8.1.1 回归分析的概念首先要区分两种主要类型的变量：一种变量相当于通常函数关系中的自变量，对这样的变量能够赋予一个需要的值（如室内的温度、施肥量）或者能够取到一个可观测但不能人为控制的值（如室外的温度），这样的变量称为自变量；自变量的变化能引起另一些变量（如水稻亩产量）的变化，这样的变量称为因变量。
由一个或一组非随机变量来估计或预测某一个随机变量的观测值时,所建立的数学模型及所进行的统计分析,称为回归分析 。 因此,回归分析是研究随机变量与非随机变量之间的数量关系的一种数学方法 。 如果所建立的模型是线性的就称为线性回归分析 。 线性回归分析不仅告诉我们怎样建立变量间的数学表达式,即经验公式,而且还利用概率统计知识进行分析讨论,判断出所建立的经验公式的有效性,从而可以进行预测或估计 。
返回本节
8.1.2 回归分析的主要内容回归分析的内容包括如何确定因变量与自变量之间的回归模型；如何根据样本观测数据,估计并检验回归模型及未知参数；在众多的自变量中,判断哪些变量对因变量的影响是显著的,
哪些变量的影响是不显著的；根据自变量的已知值或给定值来估计和预测因变量的值 。
Excel提供了许多回归分析的方法与工具,它们可用于不同的分析目的 。
返回本节
8.2 图表分析与回归函数分析
8.2.1 利用图表进行分析
8.2.2 Excel中的回归分析工作表函数
8.2.3 利用工作表函数进行回归分析返回首页
8.2.1 利用图表进行分析例 8-1 某种合成纤维的强度与其拉伸倍数之间存在一定关系，图 8-1所示（“线性回归分析”
工作表）是实测 12个纤维样品的强度 y与相应的拉伸倍数 x的数据记录。试求出它们之间的关系。
（ 1） 打开,线性回归分析,工作表 。
（ 2） 在工具栏上选择,图表向导,按钮,单击打开图表向导对话框,如图 8-2所示,在
,图表类型,列表框中选择,XY散点图,,
单击,下一步,按钮进入图表向导步骤 2。
（ 3） 在图表向导步骤 2对话框的,数据区域,中输入
,B2:C13”,选择,系列产生在,为,列,,如图 8-3
所示,单击,下一步,按钮进入步骤 3。
（ 4） 在图表向导步骤 3的对话框中,打开,图例,页面,取消,显示图例,,省略标题,如图 8-4所示 。
（ 5） 单击,完成,按钮,得到 XY散点图如图 8-5所示 。
（ 6） 在散点图中,把鼠标放在任一数据点上,右击,
在快捷菜单中选择,添加趋势线,,打开趋势线对话框 。
（ 7） 在,添加趋势线,对话框中打开,类型,页面,
选择,线性,选项,在,选项,页面中选择,显示公式,和,显示 R平方,选项,单击,确定,按钮,得到趋势回归图,如图 8-6所示 。
图 8-1,线性回归分析,xls”工作表图 8-2 图表向导（步骤 1）
图 8-3 图表向导（步骤 2）
图 8-4 图表向导（步骤 3）
图 8-5 XY散点图图 8-6 趋势回归直线返回本节
8.2.2 Excel中的回归分析工作表函数
Excel提供的回归分析工作表函数主要有以下几个：
（ 1） 截距函数 。
（ 2） 斜率函数 。
（ 3） 测定系数函数 。
（ 4） 估计标准误差函数 。
（ 1）截距函数。
其功能是利用现有的 x值与 y值计算直线与 y轴的 截 距 。 截 距 为 穿 过 已 知 的 known_x's 和
known_y's数据点的线性回归线与 y轴的交点 。
当自变量为 0时,使用 INTERCEPT函数可以决定因变量的值 。 例如,当所有的数据点都是在 室 温 或 更 高 的 温 度 下 取 得 的,可 以 用
INTERCEPT函数预测在 0° C时金属的电阻 。
语法,INTERCEPT(known_y's,known_x's)
图 8-7 x,y数据图 8-8 计算截距
（ 2）斜率函数。
该函数返回根据 known_y's和 known_x's中的数据点拟合的线性回归直线的斜率 。 斜率为直线上任意两点的垂直距离与水平距离的比值,也就是回归直线的变化率 。
语法,SLOPE (known_y's,known_x's)
其中,Known_y's为数字型因变量数据点数组或单元格区域； Known_x's为自变量数据点集合。
（ 3）测定系数函数。
（ 3） 测定系数函数 。
该函数返回根据 known_y's和 known_x's中数据点计算得出的乘积矩相关系数的平方 。 R平方值可以解释为 y方差与 x方差的比例 。
语法,RSQ(known_y's,known_x's)
回归直线的斜率计算公式如下：
2
2
xxn
yxxyn
b
图 8-9 计算斜率
（ 4）估计标准误差函数。
该函数返回通过线性回归法计算每个 x的 y预测值时所产生的标准误差 。 标准误差用来度量根据单个 x变量计算出的 y预测值的误差量 。
语法,STEYX(known_y's,known_x's)
其中,Known_y's为因变量数据点数组或区域,
Known_x's为自变量数据点数组或区域 。
预测值 y的标准误差计算公式如下：
22
2
22
2
1
xxn
yxxyn
yyn
nn
S yx
返回本节
8.2.3 利用工作表函数进行回归分析例 8-4 在某大学一年级新生体检表中随机抽取 10张，得到 10名大学生的身高（ x） 和体重（ y） 的数据，如图 8-10（“身高体重”
工作表）所示。
用 Excel提供的工作表函数进行相关计算。
（ 1）在单元格 A12~A15中分别输入“截距”、“斜率”、“测定系数”、“估计标准误差”。
（ 2）在单元格 B12中输入公式,=INTERCEPT(C2:C11,B2:B11)”，
回车后显示 -79.42015。
（ 3）在单元格 B13中输入公式,=SLOPE(C2:C11,B2:B11)”,回车后显示 0.8041825。
（ 4）在单元格 B14中输入公式,=RSQ(C2:C11,B2:B11)”,回车后显示 0.6817018。
（ 5）在单元格 B15中输入公式,=STEYX(C2:C11,B2:B11)”,回车后显示 2.8180738。计算结果如图 8-8所示。
图 8-10,身高体重”工作表图 8-11,身高体重”回归计算结果返回本节
8.3 Excel回归分析工具
8.3.1 回归分析工具的主要内容
8.3.2 回归分析工具的应用
8.3.3 回归分析工具的输出解释返回首页
8.3.1 回归分析工具的主要内容回归分析工具是通过对一组观察值使用,最小平方法,进行直线拟合,以分析一个或多个自变量对单个因变量的影响方向与影响程度的方法 。 它是 Excel中数据分析工具的一个内容 。
在,工具,菜单中选择,数据分析,选项,会出现,数据分析,对话框,在分析工具中选择,回归,,单击,确定,按钮就会进入,回归,对话框,如图 8-12所示 。 在此对话框中主要包括以下内容：
Y值输入区域：
X值输入区域：
标志：
置信度：
常数为零：
输出区域：
新工作表组：
新工作簿：
残差：
标准残差：
残差图：
线形拟合图：
正态概率图：
返回本节
8.3.2 回归分析工具的应用例 8-5 以例 8-4资料为例,利用回归分析工具进行回归分析 。
（ 1） 打开,身高体重,工作表 。
（ 2） 在,工具,菜单中选择,数据分析,选项,在,分析工具,列表中选择,回归,,单击,确定,按钮,
打开,回归,对话框 。
（ 3） 在,Y值输入区域,中输入,$C$1,$C$11”,在
,X值输入区域,中输入,$B$1,$B$11”； 选择,标志,,置信度默认；在,输出选项,中选择,输出区域,,在其右边输入,$D$1”,如图 8-13所示,单击
,确定,按钮输出结果,如图 8-14所示 。
图 8-13,回归”对话框图 8-14 回归分析结果返回本节
8.3.3 回归分析工具的输出解释
Excel回归分析工具的输出结果包括 3个部分：
1,回归统计表
2,方差分析表
3,回归参数表回归统计表包括以下几部分内容：
（ 1） Multiple R（ 复相关系数 R）,
（ 2） R Square（ 复测定系数 R2）,
（ 3） Adjusted R Square（ 调整复测定系数
R2）,
（ 4） 标准误差：
（ 5） 观测值：
返回本节
8.4 多元回归分析例 8-6 有一个工厂会计部门在估计每月管理费
y时,用工人的劳动日数 x1与机器的开工台数 x2
作自变量,现将当年 10个月的数据搜集起来,
如图 8-15（,多元回归分析,工作表 ） 所示,
估计 y对 x1与 x2的线性回归方程 (α=0.05)。
返回首页
（ 1） 在,工具,菜单中选择,数据分析,选项,在,分析工具,列表中选择,回归,,单击,确定,按钮,打开,回归,对话框 。
（ 2） 在,Y值输入区域,中输入,D1:D11”，
在,X值输入区域,中输入,B1:C11”； 选择
,标志,,置信度默认；在,输出选项,中选择,输出区域,,在其右边输入,A12”,单击
,确定,按钮输出结果,如图 8-16所示 。
图 8-15,多元回归分析”工作表图 8-16 二元线性回归分析计算结果返回本节
8.5 非线性回归分析以最小平方法分析非线性关系资料在数量变化上的规律叫做非线性回归分析 。 从非线性回归的角度看,线性回归仅是其中的一个特例 。 一个恰当的非线性回归方程的确定不是很容易的,
一般要经过变量转换,将非线性问题转化为线性问题解决 。 下面讨论几种非线性方程线性化的情况 。
返回首页
1．
（ 1）添加趋势线。
（ 2）利用回归分析工具。
xbay lg
表 8-1 微量元素超标量与患病人数图 8-17 添加对数趋势线结果图 8-18,回归”工具获得的对数曲线模型拟合结果
2．
xaby?
表 8-2 氰化物浓度数据图 8-19 添加指数趋势线结果
8-20,回归”工具获得的指数曲线模型拟合结果
3,bxax ey?
返回本节
8.1 线性回归分析的基本原理
8.2 图表分析与回归函数分析
8.3 Excel回归分析工具
8.4 多元回归分析
8.5 非线性回归分析本章学习目标
u回归分析的基本思想
u利用 Excel图表进行线性回归分析
u利用 Excel回归分析工作表函数进行线性回归分析
u利用 Excel回归分析工具进行一元及多元线性回归分析
u非线性回归分析的基本思路
8.1 线性回归分析的基本原理
8.1.1 回归分析的概念
8.1.2 回归分析的主要内容返回首页
8.1.1 回归分析的概念首先要区分两种主要类型的变量：一种变量相当于通常函数关系中的自变量，对这样的变量能够赋予一个需要的值（如室内的温度、施肥量）或者能够取到一个可观测但不能人为控制的值（如室外的温度），这样的变量称为自变量；自变量的变化能引起另一些变量（如水稻亩产量）的变化，这样的变量称为因变量。
由一个或一组非随机变量来估计或预测某一个随机变量的观测值时,所建立的数学模型及所进行的统计分析,称为回归分析 。 因此,回归分析是研究随机变量与非随机变量之间的数量关系的一种数学方法 。 如果所建立的模型是线性的就称为线性回归分析 。 线性回归分析不仅告诉我们怎样建立变量间的数学表达式,即经验公式,而且还利用概率统计知识进行分析讨论,判断出所建立的经验公式的有效性,从而可以进行预测或估计 。
返回本节
8.1.2 回归分析的主要内容回归分析的内容包括如何确定因变量与自变量之间的回归模型；如何根据样本观测数据,估计并检验回归模型及未知参数；在众多的自变量中,判断哪些变量对因变量的影响是显著的,
哪些变量的影响是不显著的；根据自变量的已知值或给定值来估计和预测因变量的值 。
Excel提供了许多回归分析的方法与工具,它们可用于不同的分析目的 。
返回本节
8.2 图表分析与回归函数分析
8.2.1 利用图表进行分析
8.2.2 Excel中的回归分析工作表函数
8.2.3 利用工作表函数进行回归分析返回首页
8.2.1 利用图表进行分析例 8-1 某种合成纤维的强度与其拉伸倍数之间存在一定关系，图 8-1所示（“线性回归分析”
工作表）是实测 12个纤维样品的强度 y与相应的拉伸倍数 x的数据记录。试求出它们之间的关系。
（ 1） 打开,线性回归分析,工作表 。
（ 2） 在工具栏上选择,图表向导,按钮,单击打开图表向导对话框,如图 8-2所示,在
,图表类型,列表框中选择,XY散点图,,
单击,下一步,按钮进入图表向导步骤 2。
（ 3） 在图表向导步骤 2对话框的,数据区域,中输入
,B2:C13”,选择,系列产生在,为,列,,如图 8-3
所示,单击,下一步,按钮进入步骤 3。
（ 4） 在图表向导步骤 3的对话框中,打开,图例,页面,取消,显示图例,,省略标题,如图 8-4所示 。
（ 5） 单击,完成,按钮,得到 XY散点图如图 8-5所示 。
（ 6） 在散点图中,把鼠标放在任一数据点上,右击,
在快捷菜单中选择,添加趋势线,,打开趋势线对话框 。
（ 7） 在,添加趋势线,对话框中打开,类型,页面,
选择,线性,选项,在,选项,页面中选择,显示公式,和,显示 R平方,选项,单击,确定,按钮,得到趋势回归图,如图 8-6所示 。
图 8-1,线性回归分析,xls”工作表图 8-2 图表向导（步骤 1）
图 8-3 图表向导（步骤 2）
图 8-4 图表向导（步骤 3）
图 8-5 XY散点图图 8-6 趋势回归直线返回本节
8.2.2 Excel中的回归分析工作表函数
Excel提供的回归分析工作表函数主要有以下几个：
（ 1） 截距函数 。
（ 2） 斜率函数 。
（ 3） 测定系数函数 。
（ 4） 估计标准误差函数 。
（ 1）截距函数。
其功能是利用现有的 x值与 y值计算直线与 y轴的 截 距 。 截 距 为 穿 过 已 知 的 known_x's 和
known_y's数据点的线性回归线与 y轴的交点 。
当自变量为 0时,使用 INTERCEPT函数可以决定因变量的值 。 例如,当所有的数据点都是在 室 温 或 更 高 的 温 度 下 取 得 的,可 以 用
INTERCEPT函数预测在 0° C时金属的电阻 。
语法,INTERCEPT(known_y's,known_x's)
图 8-7 x,y数据图 8-8 计算截距
（ 2）斜率函数。
该函数返回根据 known_y's和 known_x's中的数据点拟合的线性回归直线的斜率 。 斜率为直线上任意两点的垂直距离与水平距离的比值,也就是回归直线的变化率 。
语法,SLOPE (known_y's,known_x's)
其中,Known_y's为数字型因变量数据点数组或单元格区域； Known_x's为自变量数据点集合。
（ 3）测定系数函数。
（ 3） 测定系数函数 。
该函数返回根据 known_y's和 known_x's中数据点计算得出的乘积矩相关系数的平方 。 R平方值可以解释为 y方差与 x方差的比例 。
语法,RSQ(known_y's,known_x's)
回归直线的斜率计算公式如下：
2
2
xxn
yxxyn
b
图 8-9 计算斜率
（ 4）估计标准误差函数。
该函数返回通过线性回归法计算每个 x的 y预测值时所产生的标准误差 。 标准误差用来度量根据单个 x变量计算出的 y预测值的误差量 。
语法,STEYX(known_y's,known_x's)
其中,Known_y's为因变量数据点数组或区域,
Known_x's为自变量数据点数组或区域 。
预测值 y的标准误差计算公式如下：
22
2
22
2
1
xxn
yxxyn
yyn
nn
S yx
返回本节
8.2.3 利用工作表函数进行回归分析例 8-4 在某大学一年级新生体检表中随机抽取 10张，得到 10名大学生的身高（ x） 和体重（ y） 的数据，如图 8-10（“身高体重”
工作表）所示。
用 Excel提供的工作表函数进行相关计算。
（ 1）在单元格 A12~A15中分别输入“截距”、“斜率”、“测定系数”、“估计标准误差”。
（ 2）在单元格 B12中输入公式,=INTERCEPT(C2:C11,B2:B11)”，
回车后显示 -79.42015。
（ 3）在单元格 B13中输入公式,=SLOPE(C2:C11,B2:B11)”,回车后显示 0.8041825。
（ 4）在单元格 B14中输入公式,=RSQ(C2:C11,B2:B11)”,回车后显示 0.6817018。
（ 5）在单元格 B15中输入公式,=STEYX(C2:C11,B2:B11)”,回车后显示 2.8180738。计算结果如图 8-8所示。
图 8-10,身高体重”工作表图 8-11,身高体重”回归计算结果返回本节
8.3 Excel回归分析工具
8.3.1 回归分析工具的主要内容
8.3.2 回归分析工具的应用
8.3.3 回归分析工具的输出解释返回首页
8.3.1 回归分析工具的主要内容回归分析工具是通过对一组观察值使用,最小平方法,进行直线拟合,以分析一个或多个自变量对单个因变量的影响方向与影响程度的方法 。 它是 Excel中数据分析工具的一个内容 。
在,工具,菜单中选择,数据分析,选项,会出现,数据分析,对话框,在分析工具中选择,回归,,单击,确定,按钮就会进入,回归,对话框,如图 8-12所示 。 在此对话框中主要包括以下内容：
Y值输入区域：
X值输入区域：
标志：
置信度：
常数为零：
输出区域：
新工作表组：
新工作簿：
残差：
标准残差：
残差图：
线形拟合图：
正态概率图：
返回本节
8.3.2 回归分析工具的应用例 8-5 以例 8-4资料为例,利用回归分析工具进行回归分析 。
（ 1） 打开,身高体重,工作表 。
（ 2） 在,工具,菜单中选择,数据分析,选项,在,分析工具,列表中选择,回归,,单击,确定,按钮,
打开,回归,对话框 。
（ 3） 在,Y值输入区域,中输入,$C$1,$C$11”,在
,X值输入区域,中输入,$B$1,$B$11”； 选择,标志,,置信度默认；在,输出选项,中选择,输出区域,,在其右边输入,$D$1”,如图 8-13所示,单击
,确定,按钮输出结果,如图 8-14所示 。
图 8-13,回归”对话框图 8-14 回归分析结果返回本节
8.3.3 回归分析工具的输出解释
Excel回归分析工具的输出结果包括 3个部分：
1,回归统计表
2,方差分析表
3,回归参数表回归统计表包括以下几部分内容：
（ 1） Multiple R（ 复相关系数 R）,
（ 2） R Square（ 复测定系数 R2）,
（ 3） Adjusted R Square（ 调整复测定系数
R2）,
（ 4） 标准误差：
（ 5） 观测值：
返回本节
8.4 多元回归分析例 8-6 有一个工厂会计部门在估计每月管理费
y时,用工人的劳动日数 x1与机器的开工台数 x2
作自变量,现将当年 10个月的数据搜集起来,
如图 8-15（,多元回归分析,工作表 ） 所示,
估计 y对 x1与 x2的线性回归方程 (α=0.05)。
返回首页
（ 1） 在,工具,菜单中选择,数据分析,选项,在,分析工具,列表中选择,回归,,单击,确定,按钮,打开,回归,对话框 。
（ 2） 在,Y值输入区域,中输入,D1:D11”，
在,X值输入区域,中输入,B1:C11”； 选择
,标志,,置信度默认；在,输出选项,中选择,输出区域,,在其右边输入,A12”,单击
,确定,按钮输出结果,如图 8-16所示 。
图 8-15,多元回归分析”工作表图 8-16 二元线性回归分析计算结果返回本节
8.5 非线性回归分析以最小平方法分析非线性关系资料在数量变化上的规律叫做非线性回归分析 。 从非线性回归的角度看,线性回归仅是其中的一个特例 。 一个恰当的非线性回归方程的确定不是很容易的,
一般要经过变量转换,将非线性问题转化为线性问题解决 。 下面讨论几种非线性方程线性化的情况 。
返回首页
1．
（ 1）添加趋势线。
（ 2）利用回归分析工具。
xbay lg
表 8-1 微量元素超标量与患病人数图 8-17 添加对数趋势线结果图 8-18,回归”工具获得的对数曲线模型拟合结果
2．
xaby?
表 8-2 氰化物浓度数据图 8-19 添加指数趋势线结果
8-20,回归”工具获得的指数曲线模型拟合结果
3,bxax ey?
返回本节
