回归分析(excel).ppt

《回归分析(excel).ppt》由会员分享，可在线阅读，更多相关《回归分析(excel).ppt（75页珍藏版）》请在三一办公上搜索。

1、第10章 回归分析,1,第十章 回归分析,10.1 线性回归分析的基本原理 10.2 图表分析与回归函数分析 10.3 Excel 回归分析工具 10.4 多元回归分析 10.5 非线性回归分析 10.6 品质变量回归分析,下一页,返回目录,第10章 回归分析,2,10.1 线性回归分析的基本原理,10.1.1 回归分析的概念 10.1.2 回归分析的主要内容,上一页,下一页,返回本章首页,第10章 回归分析,3,10.1.1 回归分析的概念,现实世界中大多数现象表现为相关关系，人们通过大量观察，将现象之间的相关关系抽象概括为函数关系，并用函数形式或模型来描述与推断现象间的具体变动关系，用一个

2、或一组变量的变化来估计与推算另一个变量的变化。这种分析方法称为回归分析。,上一页,下一页,返回本节首页,第10章 回归分析,4,10.1.2 回归分析的主要内容,回归参数估计 方程拟合效果评价 回归参数的推断,上一页,下一页,返回本节首页,第10章 回归分析,5,10.2 图表分析与回归函数分析,10.2.1 利用图表进行回归分析 10.2.2 Excel中的回归分析工作表函数 10.2.3 利用工作表函数进行回归分析,上一页,下一页,返回本章首页,第10章 回归分析,6,10.2.1 利用图表进行回归分析,例 近年来国家教育部决定将各高校的后勤社 会化。某从事饮食业的企业家认为这是一 个很好

3、的投资机会，他得到十组高校人数 与周边饭店的季销售额的数据资料，并想 根据高校的数据决策其投资规模。,上一页,下一页,返回本节首页,第10章 回归分析,7,操作过程：,上一页,下一页,返回本节首页,打开“第10章 简单线性回归.xls”工作簿，选择“饭店”工作表，如下图所示。,第10章 回归分析,8,从“插入”菜单中选择“图表”选项，打开“图表向导”对话框如下图所示。在“图表类型”列表中选择XY散点图，单击“下一步”按钮。,上一页,下一页,返回本节首页,第10章 回归分析,9,在数据区域中输入B2:C11，选择“系列产生在列”，如下图所示，单击“下一步”按钮。,上一页,下一页,返回本节首页,第

4、10章 回归分析,10,打开“图例”页面，取消图例，省略标题，如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,11,单击“完成”按钮，便得到XY散点图如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,12,如图1所示，用鼠标激活散点图，把鼠标放在任一数据点上，单击鼠标右键，打开菜单，在菜单栏里选择“填加趋势线”选项，打开趋势线对话框如图2所示。,图1,上一页,下一页,返回本节首页,第10章 回归分析,13,图2,上一页,下一页,返回本节首页,第10章 回归分析,14,打开“类型”页面，选择“线性”选项，Excel将显示一条拟合数据点的直线。打开“选项”页面如图3所

5、示，在对话框下部选择“显示公式”和“显示R平方根”选项，单击“确定”按钮，便得到趋势回归图如图4所示。,图3,上一页,下一页,返回本节首页,第10章 回归分析,15,图4,上一页,下一页,返回本节首页,第10章 回归分析,16,10.2.2 Excel中的回归分析工作表函数,截距函数INTERCEPT 功能：利用已知的 x 值与 y 值计算回归直线在y 轴 的截距。语法结构：INTERCEPT(known_ys,known_xs)斜率函数SLOPE 功能：返回根据 known_ys 和 known_xs 中的数据 点拟合的线性回归直线的斜率。语法结构：SLOPE(known_ys,known_

6、xs),上一页,下一页,返回本节首页,第10章 回归分析,17,测定系数函数RSQ 功能：返回根据 known_ys 和 known_xs 中数 据点 计算得出的 Pearson 乘积矩相关系数的平方。语法结构：RSQ(known_ys,known_xs)估计标准误差函数STEYX 功能：返回通过线性回归法计算 y 预测值时所产生 的标准误差。标准误差用来度量根据单个 x 变量计算出的 y 预测值的误差量。语法结构：STEYX(known_ys,known_xs),上一页,下一页,返回本节首页,第10章 回归分析,18,10.2.3 利用工作表函数进行回归分析,例 某企业希望确定其产品制造过程

7、中的每 月成本支出与产量之间的关系，以制定 生产计划。试根据该企业选择历年的产 量(吨)和成本支出(千元)的样本，计算 上面四个函数值。,上一页,下一页,返回本节首页,第10章 回归分析,19,操作过程：,打开“第10章 简单线性回归.xls”工作簿，选择“成本产量”工作表，如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,20,在单元格A19、A20、A21和A22中分别输入“截距b0”、“斜率b1”、“估计标准误差”和“测定系数”。在单元格B19中输入公式：“=INTERCEPT(C2:C15,B2:B15)”，单击回车键。在单元格B20中输入公式：“=SLOPE(C2:C1

8、5,B2:B15)”，单击回车键。在单元格B21中输入公式：“=STEYX(C2:C15,B2:B15)”，单击回车键。在单元格B22中输入公式：“=RSQ(C2:C15,B2:B15)”，单击回车键。,上一页,下一页,返回本节首页,第10章 回归分析,21,10.3 Excel 回归分析工具,10.3.1 回归分析工具的主要内容 10.3.2 回归分析工具的应用 10.3.3 回归分析工具的输出解释,上一页,下一页,返回本章首页,第10章 回归分析,22,10.3.1 回归分析工具的主要内容,回归分析工具是通过对一组观察值使用“最小平方法”进行直线拟合，以分析一个或几个自变量对单个因变量的影

9、响方向与影响程度的方法。它是Excel中数据分析工具的一个内容。回归分析的对话框如图5所示。,上一页,下一页,返回本节首页,第10章 回归分析,23,图5,上一页,下一页,返回本节首页,第10章 回归分析,24,10.3.2 回归分析工具的应用,例 某房地产经纪人从政府部门列举的地 区中随机抽取了15户居民作为样本，记录了他们的家庭住房面积及其相应 的价格，他想确认一下住房面积（平 方米）与价格（千元）的关系，并想 据此拟合住房价格的回归方程。,上一页,下一页,返回本节首页,第10章 回归分析,25,操作过程：,打开“第10章 简单线性回归.xls”工作簿，选择“住房”工作表如下图所示。,上一

10、页,下一页,返回本节首页,第10章 回归分析,26,在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,27,在“分析工具”列表中选择“回归”选项，单击“确定”按钮，打开“回归”对话框如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,28,在Y值输入区域中输入C1:C16。在X值输入区域中输入B1:B16。选择“标志”，置信度选择95%。在“输出选项”中选择“输出区域”，在其右边的位置输入“D1”，单击“确定”按钮。输出结果如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,29,Excel的回归

11、分析工具计算简便,但内容丰富，计算结果共分为三个模块：回归统计表 方差分析表回归参数,回归分析工具的输出解释,上一页,下一页,返回本节首页,第10章 回归分析,30,回归统计表包括以下几部分内容：Multiple R（复相关系数R）：R2的平方根，又称为相关系数，它用来衡量变量x和y之间相关程度的大小。上节例中：R为0.848466，表示二者之间的关系是 高度正相关。R Square(复测定系数R2)：用来说明用自变量解释因变量变差的程度，以测量同因变量y的拟合效果。上节例中：复测定系数为0.719894，表明用自变量可解释因变量变差的71.99%。,1.回归统计表,上一页,下一页,返回本节首

12、页,第10章 回归分析,31,Adjusted R Square(调整复测定系数R2)：仅用于多元回归才有意义，它用于衡量加入独立变量后模型的拟合程度。当有新的独立变量加入后，即使这一变量同因变量之间不相关，未经修正的R2也要增大，修正的R2仅用于比较含有同一个因变量的各种模型。标准误差：又称为标准回归误差或叫估计标准误差，它用来衡量拟合程度的大小，也用于计算与回归有关的其他统计量，此值越小，说明拟合程度越好。,上一页,下一页,返回本节首页,第10章 回归分析,32,观测值：是指用于估计回归方程的数据的观测值个数。2.方差分析表方差分析表的主要作用是通过F检验来判断回归模型的回归效果。3.回归

13、参数表如下页图所示，回归参数表是表中最后一个部分：,上一页,下一页,返回本节首页,第10章 回归分析,33,上一页,下一页,返回本节首页,第10章 回归分析,34,图中，回归参数如下：Intercept：截距0第二、三行：0(截距)和1(斜率)的各项指标。第二列：回归系数0(截距)和1(斜率)的值。第三列：回归系数的标准误差第四列：根据原假设Ho：0=1=0计算的样本 统计量t的值。第五列：各个回归系数的p值(双侧)第六列：0和195%的置信区间的上下限。,上一页,下一页,返回本节首页,第10章 回归分析,35,10.4 多元回归分析,10.4.1 案例研究：销售额与广告媒体的关系 10.4.

14、2 回归输出结果解释,上一页,下一页,返回本章首页,第10章 回归分析,36,10.4.1 案例研究：销售额与广告媒体的关系,例 某VCD连锁店非常想知道在电视台做广告与在广播 电台做广告哪种媒体更有效。它收集了连锁店各个 商店的每月销售额（万元）和每月用在以上两种媒 介的广告支出。试问：在显著性水平为0.05的基础上，销售额是否同两种媒介的广告有关？每种媒介上的广告支出额对销售额的影响如何？哪种广告形式带来的成本效益更高?,上一页,下一页,返回本节首页,第10章 回归分析,37,操作过程：,打开“第10章 多元回归分析.xls”工作簿，选择“VCD”工作表，如下图所示。,上一页,下一页,返回

15、本节首页,第10章 回归分析,38,在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框，在“分析工具”列表中选择“回归”选项，单击“确定”按钮，进入“回归”对话框。在“Y值输入区域”中输入A1:A21单元格，它代表销售额的数据范围。在“X值输入区域”中输入B1:C21单元格，这里包括“广播”与“电视”两个自变量，回归工具要求自变量之间必须是相邻的，不能隔开。,上一页,下一页,返回本节首页,第10章 回归分析,39,选中标志。选择95%的置信度在“输出区域”中输入D1单元格，表示输出结果的起点。单击“确定”按钮。得多元回归计算结果如下图所示。,上一页,下一页,返回本节首页,第10章 回

16、归分析,40,10.4.2 回归输出结果解释,1.回归统计表调整复测定系数为28.99%，这说明两种媒体的广告支出只能解释销售额变动的29%，大约销售额变动的71%要由其他因素的变动来解释。估计标准误差为210.9553，说明实际值与估计值之间的误差。,上一页,下一页,返回本节首页,第10章 回归分析,41,2.方差分析表 方差分析的目的是进行回归方程的回归效果检验，F统计量的P值约等于0.021，小于显著水平0.05，说明方程回归效果显著，方程中至少有一个回归系数显著不为零。,上一页,下一页,返回本节首页,第10章 回归分析,42,3.回归参数表 回归方程为：广播广告支出的回归系数的t统计量

17、的p值近似等于0.013。说明在显著性水平0.05时要拒绝原假设，而在0.1显著水平时接受原假设。此题中的p值证明每月用于广播的广告支出同VCD盘的销售额是相关的。,上一页,下一页,返回本节首页,第10章 回归分析,43,电视广告支出的回归系数的t统计量的p值很高，接近0.18，值得注意。尽管它的回归系数同用于广播广告支出的回归系数比较接近，但如此大的p值说明电视广告支出同销售额之间不存在相关，那么抽取的回归系数不为零的的概率就是p值(0.18)。在给定5%的显著性水平下，无法拒绝2为零的原假设。结论便是电视广告支出回归系数不显著。,上一页,下一页,返回本节首页,第10章 回归分析,44,决策

18、建议：用于广播的广告支出的回归系数比较大，并且比用于电视广告支出的回归系数显著，这说明VCD连锁店应该把更多的广告支出用于广播，而减少对电视台的广告支出。,上一页,下一页,返回本节首页,第10章 回归分析,45,10.5 非线性回归分析,10.5.1 非线性关系的线性化10.5.2 案例研究：成本产量多项式模型拟合10.5.3 案例研究：产量收益对数模型拟合,上一页,下一页,返回本章首页,第10章 回归分析,46,10.5.1 非线性关系的线性化,实际分析工作中，有许多回归模型的因变量与自变量之间的关系并不呈现线性关系，此时，可以通过线性变换来使其线性化，从而利用回归分析工具进行分析。,上一页

19、,下一页,返回本节首页,第10章 回归分析,47,1.多项式模型在只有一个自变量的情况下，多项式模型形式如下：2.对数模型对数模型方程为：,上一页,下一页,返回本节首页,第10章 回归分析,48,3.幂函数幂函数的方程形式为：4.指数模型指数模型方程为：,上一页,下一页,返回本节首页,第10章 回归分析,49,10.5.2 案例研究：成本产量多项式模型拟合,例 某企业财务经理欲对企业的成本进行控制，为了掌握成本与产量的数量关系，需要拟 合一个成本函数。财务经理搜集了50组成 本与产量的资料，试以0.05的显著水平拟合 二项成本函数。,上一页,下一页,返回本节首页,第10章 回归分析,50,操作

20、过程：,打开“第10章 回归分析.xls”工作簿，选择“多项式”工作表，如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,51,绘制成本产量散点图，详细过程参照10.2.1制图过程。如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,52,在B栏中任选一个单元格，打开“插入”菜单，选择“列”选项，则在A、B两列之间增加一列，成本数据将被移到C列。新列必须放在“产量”列旁边，因为Excel要求自变量是相邻的。在单元格B1中输入“产量平方”，在单元格B2中输入公式“=A22”以计算A2单元格数值的平方，并复制到B3:B51区域中的各个单元格中。如下页图所示。,上一页,下一

21、页,返回本节首页,第10章 回归分析,53,上一页,下一页,返回本节首页,第10章 回归分析,54,在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框，“分析工具”列表中选择“回归”选项，单击“确定”按钮,进入“回归”对话框，如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,55,在“Y值输入区域”中输入C1:C51，它代表成本的数据范围，在“X值输入区域”中输入A1:B51，这里包括“产量”与“产量平方”两个自变量，回归工具要求自变量之间必须是相邻的，不能隔开。选中标志。选择95%的置信度。,上一页,下一页,返回本节首页,第10章 回归分析,56,在“输出区域”中输入

22、D1单元格，表示输出结果的起点。单击“确定”按钮。得多元回归计算结果如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,57,回归计算结果分析：,R Square 约为0.79 说明产量与产量平方这两个变量可解释成本变化的79%，而其余的21%变动要由其他因素的影响来解释。F统计量为约89.18，根据F统计量得到的P值近似为0，远远小于显著水平0.05，说明回归方程有效。回归系数分别在单元格E18和E19中，其相应的t检验值及p值表明回归系数显著不为零，能够解释成本的变化。据此可写出方程：,x+0.000044x2,上一页,下一页,返回本节首页,第10章 回归分析,58,10.5.

23、3 案例研究：产量收益对数模型拟合,例 某制衣企业想了解产量与收益之间 的关系，为此收集整理了历年的产 量收益数据资料情况，根据这些资 料建立适当模型说明产量收益之间 的关系。,上一页,下一页,返回本节首页,第10章 回归分析,59,操作过程：,打开“第10章 回归分析.xls”工作簿，选择“对数”工作表如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,60,制作产量收益散点图判断采用的模型形式，如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,61,利用回归分析工具得到分析结果如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,62,回归计算结果分析：

24、,R Square 约为0.87 说明产量与产量平方这两个变量可解释成本变化的87%，而其余的13%变动要由其他因素的影响来解释。F统计量为约318.83，根据F统计量得到的P值近似为0，远远小于显著水平0.05，说明回归方程有效。回归系数分别在单元格E17和E18中，其相应的t检验值及p值表明回归系数显著不为零，能够解释成本的变化。据此可写出方程：,=162.05+25.83Ln(x),上一页,下一页,返回本节首页,第10章 回归分析,63,10.6 品质变量回归分析,10.6.1 两种表现的品质变量回归分析 10.6.2 多种表现的品质变量回归分析,上一页,下一页,返回本章首页,第10章

25、回归分析,64,10.6.1 两种表现的品质变量回归分析,例 某大学教务处对学生的动手能力颇感兴趣，在 研究中发现学生的学习成绩特别是统计成绩同 计算机有关。他们将学生分成两类，一类是利 用计算机学习统计，另一类是不用计算机学习 统计。现随机从利用计算机与不用计算机的学 生中抽取两个样本，包括统计成绩和过去的绩 分点在显著水平0.05上，能否确定使用计算机 学生的统计成绩高于不使用计算机学生的统计 成绩？,上一页,下一页,返回本节首页,第10章 回归分析,65,操作过程：,打开“第10章 回归分析.xls”工作簿，选择“计算机”工作表如下图所示。,上一页,下一页,返回本节首页,第10章 回归分

26、析,66,在C栏中任选一个单元格，打开“插入”菜单，选择“列”选项，则在B、C两列之间增加一列。在单元格C1中输入“计算机”。在单元格C2中输入条件函数IF的表达式“=IF(D2=”是“,1,0)”，式中表示如果真则为1，如果不真则为0。单元格C2显示值1，将其复制到C3:C21各单元格中。,上一页,下一页,返回本节首页,第10章 回归分析,67,在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框，在“分析工具”列表中选择“回归”选项，单击“确定”按钮，进入“回归”对话框，如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,68,在“Y值输入区域”输入地址为A1:A21，

27、在“X值输入区域”输入地址为B1:C21；选择“标志”和95%的置信度。在“输出区域”中输入E2，单击“确定”按钮，得计算结果如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,69,10.6.2 多种表现的品质变量回归分析,例 现有某地区15个房地产的售价、居住面 积、评估价值和建筑等级(低、中、高)。试根据表中资料拟合售价的预测模型。,上一页,下一页,返回本节首页,第10章 回归分析,70,操作过程：,打开“第10章 回归分析.xls”工作簿，选择“房地产”工作表如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,71,在单元格E1、F1和G1中分别输入“高”、“中

28、”和“低”。在单元格E2中输入公式：IF(D2“高”,1,0)。在单元格F2中输入公式IF(D2“中”,1,0)。在单元格G2中输入公式：IF(D2“低”,1,0)将单元格E2:G2的内容复制到E3:G16区域中的各单元格。,上一页,下一页,返回本节首页,第10章 回归分析,72,选中D列，点击鼠标右键，在弹出的工具条中选择“剪切”；再选中H列，点击鼠标右键，在弹出的工具条中选择“插入剪切单元格”，则调整后的工作表如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,73,在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框，在“分析工具”列表中选择“回归”选项，单击“确定”按钮，进入“回归”对话框，如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,74,在“Y 值输入区域”输入地址为A1:A16 单元格；在“X 值输入区域”输入地址为B1:E1单元格选择“标志”和95%的置信度；在“输出区域”中输入A25单元格；单击“确定”按钮，计算结果如下图所示。,上一页,下一页,返回本节首页,第10章 回归分析,75,Thank you very much!,谢谢！,上一页,退出,返回本章首页,