《excel回归分析》ppt课件

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

2、性回归分析的根本思绪非线性回归分析的根本思绪8.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 利用任务表函数进展回归分析利用任务表函

6、数进展回归分析前往首页8.2.1 利用图表进展分析利用图表进展分析例例8-1 某种合成纤维的强度与其拉伸倍数之间某种合成纤维的强度与其拉伸倍数之间存在一定关系，图存在一定关系，图8-1所示所示“线性回归分析线性回归分析任务表是实测任务表是实测12个纤维样品的强度个纤维样品的强度y与相与相应的拉伸倍数应的拉伸倍数x的数据记录。试求出它们之间的数据记录。试求出它们之间的关系。的关系。 1翻开翻开“线性回归分析任务表。线性回归分析任务表。2在工具栏上选择在工具栏上选择“图表导游按钮，单图表导游按钮，单击翻开图表导游对话框，如图击翻开图表导游对话框，如图8-2所示，在所示，在“图表类型列表框中选择图表

7、类型列表框中选择“XY散点图，散点图，单击单击“下一步按钮进入图表导游步骤下一步按钮进入图表导游步骤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.2

9、Excel中的回归分析任务表函数中的回归分析任务表函数Excel提供的回归分析任务表函数主要有以下提供的回归分析任务表函数主要有以下几个：几个：1截距函数。截距函数。2斜率函数。斜率函数。 3测定系数函数。测定系数函数。4估计规范误差函数。估计规范误差函数。 1截距函数。截距函数。其功能是利用现有的其功能是利用现有的x值与值与y值计算直线与值计算直线与y轴轴的截距。截距为穿过知的的截距。截距为穿过知的known_xs和和known_ys数据点的线性回归线与数据点的线性回归线与y轴的交点。轴的交点。当自变量为当自变量为0时，运用时，运用INTERCEPT函数可以函数可以决议因变量的值。例如，当一

10、切的数据点都是决议因变量的值。例如，当一切的数据点都是在 室 温 或 更 高 的 温 度 下 获 得 的 ， 可 以 用在 室 温 或 更 高 的 温 度 下 获 得 的 ， 可 以 用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(known_

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

13、预测值预测值y的规范误差计算公式如下：的规范误差计算公式如下： 2222221xxnyxxynyynnnSyx前往本节8.2.3 利用任务表函数进展回归分析利用任务表函数进展回归分析例例8-4 在某大学一年级新生体检表中随机抽取在某大学一年级新生体检表中随机抽取10张，得到张，得到10名大名大学生的身高学生的身高x和体重和体重y的数据，如图的数据，如图8-10“身高体重身高体重任务表所示。任务表所示。用用Excel提供的任务表函数进展相关计算。提供的任务表函数进展相关计算。1在单元格在单元格A12A15中分别输入中分别输入“截距、截距、“斜率、斜率、“测测定系数、定系数、“估计规范误差。估计规

14、范误差。2在单元格在单元格B12中输入公式中输入公式“=INTERCEPT(C2:C11,B2:B11)，回车后显示，回车后显示-79.42021。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回归参数表回归参数表回归统计表包括以下几部分内容：回归统计表包括以下几部分内容：1Multiple R复相关系数复相关系数R：2R Square复测定系数复测定系数R2：

19、3Adjusted R Square调整复测定系数调整复测定系数R2： 4规范误差：规范误差： 5观测值：观测值： 前往本节8.4 多元回归分析多元回归分析例例8-6 有一个工厂会计部门在估计每月管理费有一个工厂会计部门在估计每月管理费y时，用工人的劳动日数时，用工人的劳动日数x1与机器的开工台数与机器的开工台数x2作自变量，现将当年作自变量，现将当年10个月的数据搜集起来，个月的数据搜集起来，如图如图8-15“多元回归分析任务表所示，多元回归分析任务表所示，估计估计y对对x1与与x2的线性回归方程的线性回归方程(=0.05)。前往首页1在在“工具菜单中选择工具菜单中选择“数据分析选数据分析选项，在项，在“分析工具列表中选择分析工具列表中选择“回归，单回归，单击击“确定按钮，翻开确定按钮，翻开“回归对话框。回归对话框。