第8章Excel教程__回归分析.ppt

《第8章Excel教程__回归分析.ppt》由会员分享，可在线阅读，更多相关《第8章Excel教程__回归分析.ppt（51页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第8章章 回归分析回归分析8.1线性回归分析的基本原理线性回归分析的基本原理8.2图表分析与回归函数分析图表分析与回归函数分析8.3Excel回归分析工具回归分析工具8.4多元回归分析多元回归分析8.5非线性回归分析非线性回归分析本章学习目标本章学习目标uu回归分析的基本思想回归分析的基本思想uu利用利用Excel图表进行线性回归分析图表进行线性回归分析uu利利用用Excel回回归归分分析析工工作作表表函函数数进进行行线线性性回回归归分分析析uu利利用用Excel回回归归分分析析工工具具进进行行一一元元及及多多元元线线性性回回归分析归分析uu非线性回归分析的基本思路非线性回归分析的基本思路8

2、.1 线性回归分析的基本原理线性回归分析的基本原理8.1.1 回归分析的概念回归分析的概念8.1.2 回归分析的主要内容回归分析的主要内容返回首页8.1.1 回归分析的概念回归分析的概念首先要区分两种主要类型的变量：一种变量相首先要区分两种主要类型的变量：一种变量相当于通常函数关系中的自变量，对这样的变量当于通常函数关系中的自变量，对这样的变量能够赋予一个需要的值（如室内的温度、施肥能够赋予一个需要的值（如室内的温度、施肥量）或者能够取到一个可观测但不能人为控制量）或者能够取到一个可观测但不能人为控制的值（如室外的温度），这样的变量称为自变的值（如室外的温度），这样的变量称为自变量；自变量的变

3、化能引起另一些变量（如水稻量；自变量的变化能引起另一些变量（如水稻亩产量）的变化，这样的变量称为因变量。亩产量）的变化，这样的变量称为因变量。由由一一个个或或一一组组非非随随机机变变量量来来估估计计或或预预测测某某一一个个随随机机变变量量的的观观测测值值时时，所所建建立立的的数数学学模模型型及及所所进进行行的的统统计计分分析析，称称为为回回归归分分析析。因因此此，回回归归分分析析是是研研究究随随机机变变量量与与非非随随机机变变量量之之间间的的数数量量关关系系的的一一种种数数学学方方法法。如如果果所所建建立立的的模模型型是是线线性性的的就就称称为为线线性性回回归归分分析析。线线性性回回归归分分析

4、析不不仅仅告告诉诉我我们们怎怎样样建建立立变变量量间间的的数数学学表表达达式式，即即经经验验公公式式，而而且且还还利利用用概概率率统统计计知知识识进进行行分分析析讨讨论论，判判断断出出所所建建立立的的经经验验公公式式的的有有效效性性，从从而而可以进行预测或估计。可以进行预测或估计。返回本节8.1.2 回归分析的主要内容回归分析的主要内容回回归归分分析析的的内内容容包包括括如如何何确确定定因因变变量量与与自自变变量量之之间间的的回回归归模模型型；如如何何根根据据样样本本观观测测数数据据，估估计计并并检检验验回回归归模模型型及及未未知知参参数数；在在众众多多的的自自变变量量中中，判判断断哪哪些些变

5、变量量对对因因变变量量的的影影响响是是显显著著的的，哪哪些些变变量量的的影影响响是是不不显显著著的的；根根据据自自变变量量的的已已知值或给定值来估计和预测因变量的值。知值或给定值来估计和预测因变量的值。Excel提提供供了了许许多多回回归归分分析析的的方方法法与与工工具具，它它们们可用于不同的分析目的。可用于不同的分析目的。返回本节8.2 图表分析与回归函数分析图表分析与回归函数分析8.2.1 利用图表进行分析利用图表进行分析8.2.2 Excel中的回归分析工作表函数中的回归分析工作表函数8.2.3 利用工作表函数进行回归分析利用工作表函数进行回归分析返回首页8.2.1 利用图表进行分析利用

6、图表进行分析例例8-1某种合成纤维的强度与其拉伸倍数之间某种合成纤维的强度与其拉伸倍数之间存在一定关系，图存在一定关系，图8-1所示（所示（“线性回归分析线性回归分析”工作表）是实测工作表）是实测12个纤维样品的强度个纤维样品的强度y与相应与相应的拉伸倍数的拉伸倍数x的数据记录。试求出它们之间的的数据记录。试求出它们之间的关系。关系。（1）打开）打开“线性回归分析线性回归分析”工作表。工作表。（2）在在工工具具栏栏上上选选择择“图图表表向向导导”按按钮钮，单单击击打打开开图图表表向向导导对对话话框框，如如图图8-2所所示示，在在“图图表表类类型型”列列表表框框中中选选择择“XY散散点点图图”，

7、单单击击“下一步下一步”按钮进入图表向导步骤按钮进入图表向导步骤2。（3）在在图图表表向向导导步步骤骤2对对话话框框的的“数数据据区区域域”中中输输入入“B2:C13”，选选择择“系系列列产产生生在在”为为“列列”，如如图图8-3所示，单击所示，单击“下一步下一步”按钮进入步骤按钮进入步骤3。（4）在在图图表表向向导导步步骤骤3的的对对话话框框中中，打打开开“图图例例”页页面，取消面，取消“显示图例显示图例”，省略标题，如图，省略标题，如图8-4所示。所示。（5）单击）单击“完成完成”按钮，得到按钮，得到XY散点图如图散点图如图8-5所示。所示。（6）在在散散点点图图中中，把把鼠鼠标标放放在在

8、任任一一数数据据点点上上，右右击击，在在快快捷捷菜菜单单中中选选择择“添添加加趋趋势势线线”，打打开开趋趋势势线线对对话话框。框。（7）在在“添添加加趋趋势势线线”对对话话框框中中打打开开“类类型型”页页面面，选选择择“线线性性”选选项项，在在“选选项项”页页面面中中选选择择“显显示示公公式式”和和“显显示示R平平方方”选选项项，单单击击“确确定定”按按钮钮，得得到到趋势回归图，如图趋势回归图，如图8-6所示。所示。图8-1 “线性回归分析.xls”工作表图8-2 图表向导（步骤1）图8-3 图表向导（步骤2）图8-4 图表向导（步骤3）图8-5 XY散点图图8-6 趋势回归直线返回本节8.2

9、.2 Excel中的回归分析工作表函数中的回归分析工作表函数Excel提提供供的的回回归归分分析析工工作作表表函函数数主主要要有有以以下下几几个：个：（1）截距函数。）截距函数。（2）斜率函数。）斜率函数。（3）测定系数函数。）测定系数函数。（4）估计标准误差函数。）估计标准误差函数。（1）截距函数。）截距函数。其其功功能能是是利利用用现现有有的的x值值与与y值值计计算算直直线线与与y轴轴的的 截截 距距。截截 距距 为为 穿穿 过过 已已 知知 的的 known_xs和和known_ys数数据据点点的的线线性性回回归归线线与与y轴轴的的交交点点。当当自自变变量量为为0时时，使使用用INTER

10、CEPT函函数数可可以以决决定定因因变变量量的的值值。例例如如，当当所所有有的的数数据据点点都都是是在在室室 温温 或或 更更 高高 的的 温温 度度 下下 取取 得得 的的，可可 以以 用用INTERCEPT函数预测在函数预测在0C时金属的电阻。时金属的电阻。语法语法：INTERCEPT(known_ys,known_xs)图8-7 x、y数据图8-8 计算截距（2）斜率函数。）斜率函数。该该函函数数返返回回根根据据known_ys和和known_xs中中的的数数据据点点拟拟合合的的线线性性回回归归直直线线的的斜斜率率。斜斜率率为为直直线线上上任任意意两两点点的的垂垂直直距距离离与与水水平平

11、距距离离的的比比值值，也也就是回归直线的变化率。就是回归直线的变化率。语法：语法：SLOPE(known_ys,known_xs)其中：其中：Known_ys为数字型因变量数据点数组为数字型因变量数据点数组或单元格区域；或单元格区域；Known_xs为自变量数据点集为自变量数据点集合。合。（3）测定系数函数。）测定系数函数。（3）测定系数函数。）测定系数函数。该该函函数数返返回回根根据据known_ys和和known_xs中中数数据据点点计计算算得得出出的的乘乘积积矩矩相相关关系系数数的的平平方方。R平平方方值值可以解释为可以解释为y方差与方差与x方差的比例。方差的比例。语法：语法：RSQ(k

12、nown_ys,known_xs)回归直线的斜率计算公式如下：回归直线的斜率计算公式如下：图8-9 计算斜率（4）估计标准误差函数。）估计标准误差函数。该该函函数数返返回回通通过过线线性性回回归归法法计计算算每每个个x的的y预预测测值值时时所所产产生生的的标标准准误误差差。标标准准误误差差用用来来度度量量根根据单个据单个x变量计算出的变量计算出的y预测值的误差量。预测值的误差量。语法：语法：STEYX(known_ys,known_xs)其其中中：Known_ys为为因因变变量量数数据据点点数数组组或或区区域域，Known_xs为自变量数据点数组或区域。为自变量数据点数组或区域。预测值预测值y

13、的标准误差计算公式如下：的标准误差计算公式如下：返回本节8.2.3 利用工作表函数进行回归分析利用工作表函数进行回归分析例例8-4 在某大学一年级新生体检表中随机抽取在某大学一年级新生体检表中随机抽取10张，得到张，得到10名大名大学生的身高（学生的身高（x）和体重（和体重（y）的数据，如图的数据，如图8-10（“身高体重身高体重”工作表）所示。工作表）所示。用用Excel提供的工作表函数进行相关计算。提供的工作表函数进行相关计算。（1）在单元格）在单元格A12A15中分别输入中分别输入“截距截距”、“斜率斜率”、“测定测定系数系数”、“估计标准误差估计标准误差”。（2）在单元格）在单元格B1

14、2中输入公式中输入公式“=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 “身高体重”工作

15、表 图8-11 “身高体重”回归计算结果返回本节8.3 Excel回归分析工具回归分析工具8.3.1 回归分析工具的主要内容回归分析工具的主要内容8.3.2 回归分析工具的应用回归分析工具的应用8.3.3 回归分析工具的输出解释回归分析工具的输出解释返回首页8.3.1 回归分析工具的主要内容回归分析工具的主要内容回回归归分分析析工工具具是是通通过过对对一一组组观观察察值值使使用用“最最小小平平方方法法”进进行行直直线线拟拟合合，以以分分析析一一个个或或多多个个自自变变量量对对单单个个因因变变量量的的影影响响方方向向与与影影响响程程度度的的方方法。它是法。它是Excel中数据分析工具的一个内容。

16、中数据分析工具的一个内容。在在“工工具具”菜菜单单中中选选择择“数数据据分分析析”选选项项，会会出出现现“数数据据分分析析”对对话话框框，在在分分析析工工具具中中选选择择“回回归归”，单单击击“确确定定”按按钮钮就就会会进进入入“回回归归”对对话话框框，如如图图8-12所所示示。在在此此对对话话框框中中主主要要包包括括以以下下内内容：容：Y值输入区域：值输入区域：X值输入区域：值输入区域：标志：标志：置信度：置信度：常数为零：常数为零：输出区域：输出区域：新工作表组：新工作表组：新工作簿：新工作簿：残差：残差：标准残差：标准残差：残差图：残差图：线形拟合图：线形拟合图：正态概率图：正态概率图：

17、返回本节8.3.2 回归分析工具的应用回归分析工具的应用例例8-5 以以例例8-4资资料料为为例例，利利用用回回归归分分析析工工具具进进行行回回归归分分析。析。（1）打开）打开“身高体重身高体重”工作表。工作表。（2）在在“工工具具”菜菜单单中中选选择择“数数据据分分析析”选选项项，在在“分分析析工工具具”列列表表中中选选择择“回回归归”，单单击击“确确定定”按按钮钮，打开打开“回归回归”对话框。对话框。（3）在在“Y值值输输入入区区域域”中中输输入入“$C$1:$C$11”，在在“X值值输输入入区区域域”中中输输入入“$B$1:$B$11”；选选择择“标标志志”，置置信信度度默默认认；在在“

18、输输出出选选项项”中中选选择择“输输出出区区域域”，在在其其右右边边输输入入“$D$1”，如如图图8-13所所示示，单单击击“确确定定”按钮输出结果，如图按钮输出结果，如图8-14所示。所示。图8-13 “回归”对话框 图8-14 回归分析结果返回本节8.3.3 回归分析工具的输出解释回归分析工具的输出解释Excel回归分析工具的输出结果包括回归分析工具的输出结果包括3个部分：个部分：1回归统计表回归统计表2方差分析表方差分析表3回归参数表回归参数表回归统计表包括以下几部分内容：回归统计表包括以下几部分内容：（1）MultipleR（复相关系数复相关系数R）：）：（2）RSquare（复测定系

19、数复测定系数R2）：）：（3）AdjustedRSquare（调调整整复复测测定定系系数数R2）：）：（4）标准误差：）标准误差：（5）观测值：）观测值：返回本节8.4 多元回归分析多元回归分析例例8-6有有一一个个工工厂厂会会计计部部门门在在估估计计每每月月管管理理费费y时时，用用工工人人的的劳劳动动日日数数x1与与机机器器的的开开工工台台数数x2作作自自变变量量，现现将将当当年年10个个月月的的数数据据搜搜集集起起来来，如如图图8-15（“多多元元回回归归分分析析”工工作作表表）所所示示，估计估计y对对x1与与x2的线性回归方程的线性回归方程(=0.05)。返回首页（1）在在“工工具具”菜

20、菜单单中中选选择择“数数据据分分析析”选选项项，在在“分分析析工工具具”列列表表中中选选择择“回回归归”，单单击击“确定确定”按钮，打开按钮，打开“回归回归”对话框。对话框。（2）在在“Y值值输输入入区区域域”中中输输入入“D1:D11”，在在“X值值输输入入区区域域”中中输输入入“B1:C11”；选选择择“标标志志”，置置信信度度默默认认；在在“输输出出选选项项”中中选选择择“输输出出区区域域”，在在其其右右边边输输入入“A12”，单单击击“确定确定”按钮输出结果，如图按钮输出结果，如图8-16所示。所示。图8-15 “多元回归分析”工作表图8-16 二元线性回归分析计算结果返回本节8.5

21、非线性回归分析非线性回归分析以以最最小小平平方方法法分分析析非非线线性性关关系系资资料料在在数数量量变变化化上上的的规规律律叫叫做做非非线线性性回回归归分分析析。从从非非线线性性回回归归的的角角度度看看，线线性性回回归归仅仅是是其其中中的的一一个个特特例例。一一个个恰恰当当的的非非线线性性回回归归方方程程的的确确定定不不是是很很容容易易的的，一一般般要要经经过过变变量量转转换换，将将非非线线性性问问题题转转化化为为线线性性问问题题解解决决。下下面面讨讨论论几几种种非非线线性性方方程程线线性性化化的情况。的情况。返回首页1（1）添加趋势线。）添加趋势线。（2）利用回归分析工具。）利用回归分析工具。表8-1 微量元素超标量与患病人数图8-17 添加对数趋势线结果图8-18 “回归”工具获得的对数曲线模型拟合结果2表8-2 氰化物浓度数据图8-19 添加指数趋势线结果8-20 “回归”工具获得的指数曲线模型拟合结果3返回本节