回归分析(excel)PPT优秀课件

第10章 回归分析1第十章 回归分析 10.1 线性回归分析的基本原理 10.2 图表分析与回归函数分析 10.3 Excel 回归分析工具 10.4 多元回归分析 10.5 非线性回归分析 10.6 品质变量回归分析 下一页返回目录第10章 回归分析210.1 线性回归分析的基本原理 10.1.1 回归分析的概念 10.1.2 回归分析的主要内容 上一页下一页返回本章首页第10章 回归分析310.1.1 回归分析的概念现实世界中大多数现象表现为相关关系，人们通过大量观察，将现象之间的相关关系抽象概括为函数关系，并用函数形式或模型来描述与推断现象间的具体变动关系，用一个或一组变量的变化来估计与推算另一个变量的变化。这种分析方法称为回归分析。 上一页下一页返回本节首页第10章 回归分析410.1.2 回归分析的主要内容回归参数估计 方程拟合效果评价 回归参数的推断 上一页下一页返回本节首页第10章 回归分析510.2 图表分析与回归函数分析 10.2.1 利用图表进行回归分析 10.2.2 Excel中的回归分析工作表函数 10.2.3 利用工作表函数进行回归分析 上一页下一页返回本章首页第10章 回归分析610.2.1 利用图表进行回归分析例 近年来国家教育部决定将各高校的后勤社 会化。某从事饮食业的企业家认为这是一 个很好的投资机会，他得到十组高校人数 与周边饭店的季销售额的数据资料，并想 根据高校的数据决策其投资规模。 上一页下一页返回本节首页第10章 回归分析7操作过程：上一页下一页返回本节首页打开“第10章 简单线性回归.xls”工作簿，选择“饭店” 工作表，如下图所示。第10章 回归分析8从“插入”菜单中选择“图表”选项，打开“图表向导”对话框如下图所示。在“图表类型”列表中选择XY散点图，单击“下一步”按钮。上一页下一页返回本节首页第10章 回归分析9在数据区域中输入B2:C11，选择“系列产生在列”，如下图所示，单击“下一步”按钮。上一页下一页返回本节首页第10章 回归分析10打开“图例”页面，取消图例，省略标题，如下图所示。上一页下一页返回本节首页第10章 回归分析11单击“完成”按钮，便得到XY散点图如下图所示。050100150200250051015202530上一页下一页返回本节首页第10章 回归分析12如图1所示，用鼠标激活散点图，把鼠标放在任一数据点上，单击鼠标右键，打开菜单，在菜单栏里选择“填加趋势线”选项，打开趋势线对话框如图2所示。 图1上一页下一页返回本节首页第10章 回归分析13图2上一页下一页返回本节首页第10章 回归分析14打开“类型”页面，选择“线性”选项，Excel将显示一条拟合数据点的直线。 打开“选项”页面如图3所示，在对话框下部选择“显示公式”和“显示R平方根”选项，单击“确定”按钮，便得到趋势回归图如图4所示。图3上一页下一页返回本节首页第10章 回归分析15y = 5x + 60R2 = 0.9027050100150200250051015202530图4上一页下一页返回本节首页第10章 回归分析1610.2.2 Excel中的回归分析工作表函数截距函数INTERCEPT 功能：利用已知的 x 值与 y 值计算回归直线在y 轴 的截距。 语法结构： INTERCEPT(known_ys,known_xs)斜率函数SLOPE 功能：返回根据 known_ys 和 known_xs 中的数据 点拟合的线性回归直线的斜率。 语法结构：SLOPE(known_ys,known_xs)上一页下一页返回本节首页第10章 回归分析17测定系数函数RSQ 功能：返回根据 known_ys 和 known_xs 中数 据点 计算得出的 Pearson 乘积矩相关系数的平方。 语法结构：RSQ(known_ys,known_xs)估计标准误差函数STEYX 功能：返回通过线性回归法计算 y 预测值时所产生 的标准误差。标准误差用来度量根据单个 x 变量计算出的 y 预测值的误差量。 语法结构：STEYX(known_ys,known_xs) 上一页下一页返回本节首页第10章 回归分析1810.2.3 利用工作表函数进行回归分析 例 某企业希望确定其产品制造过程中的每 月成本支出与产量之间的关系，以制定 生产计划。试根据该企业选择历年的产 量(吨)和成本支出(千元)的样本，计算 上面四个函数值。 上一页下一页返回本节首页第10章 回归分析19操作过程：打开“第10章 简单线性回归.xls”工作簿，选择“成本产量”工作表，如下图所示。上一页下一页返回本节首页第10章 回归分析20在单元格A19、A20、A21和A22中分别输入“截距b0”、“斜率b1”、“估计标准误差”和“测定系数” 。在单元格B19中输入公式：“=INTERCEPT(C2:C15,B2:B15)” ，单击回车键。在单元格B20中输入公式： “=SLOPE(C2:C15,B2:B15)”，单击回车键。在单元格B21中输入公式： “=STEYX(C2:C15,B2:B15)”，单击回车键。在单元格B22中输入公式： “=RSQ(C2:C15,B2:B15)”，单击回车键。上一页下一页返回本节首页第10章 回归分析2110.3 Excel 回归分析工具 10.3.1 回归分析工具的主要内容 10.3.2 回归分析工具的应用 10.3.3 回归分析工具的输出解释 上一页下一页返回本章首页第10章 回归分析2210.3.1 回归分析工具的主要内容 回归分析工具是通过对一组观察值使用“最小平方法”进行直线拟合，以分析一个或几个自变量对单个因变量的影响方向与影响程度的方法。它是Excel中数据分析工具的一个内容。回归分析的对话框如图5所示 。上一页下一页返回本节首页第10章 回归分析23图5上一页下一页返回本节首页第10章 回归分析2410.3.2 回归分析工具的应用例 某房地产经纪人从政府部门列举的地 区中随机抽取了15户居民作为样本， 记录了他们的家庭住房面积及其相应 的价格，他想确认一下住房面积（平 方米）与价格（千元）的关系，并想 据此拟合住房价格的回归方程。 上一页下一页返回本节首页第10章 回归分析25操作过程：打开“第10章 简单线性回归.xls”工作簿，选择“住房”工作表如下图所示。上一页下一页返回本节首页第10章 回归分析26在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框如下图所示。上一页下一页返回本节首页第10章 回归分析27在“分析工具”列表中选择“回归”选项，单击“确定”按钮，打开“回归”对话框如下图所示。上一页下一页返回本节首页第10章 回归分析28在Y值输入区域中输入C1:C16。在X值输入区域中输入B1:B16。选择“标志”，置信度选择95%。在“输出选项”中选择“输出区域”，在其右边的位置输入“D1”，单击 “确定”按钮。输出结果如下图所示。上一页下一页返回本节首页第10章 回归分析29Excel的回归分析工具计算简便,但内容丰富，计算结果共分为三个模块：回归统计表 方差分析表回归参数 10.3.3 回归分析工具的输出解释上一页下一页返回本节首页第10章 回归分析30回归统计表包括以下几部分内容：Multiple R（复相关系数R）：R2的平方根，又称为相关系数，它用来衡量变量x和y之间相关程度的大小。 上节例中：R为0.848466，表示二者之间的关系是 高度正相关。R Square(复测定系数R2 )：用来说明用自变量解释因变量变差的程度，以测量同因变量y的拟合效果。 上节例中：复测定系数为0.719894，表明用自变量可解释因变量变差的71.99%。1. 回归统计表上一页下一页返回本节首页第10章 回归分析31Adjusted R Square (调整复测定系数R2)：仅用于多元回归才有意义，它用于衡量加入独立变量后模型的拟合程度。当有新的独立变量加入后，即使这一变量同因变量之间不相关，未经修正的R2也要增大，修正的R2仅用于比较含有同一个因变量的各种模型。标准误差：又称为标准回归误差或叫估计标准误差，它用来衡量拟合程度的大小，也用于计算与回归有关的其他统计量，此值越小，说明拟合程度越好。上一页下一页返回本节首页第10章 回归分析32观测值：是指用于估计回归方程的数据的观测值个数。2. 方差分析表方差分析表的主要作用是通过F检验来判断回归模型的回归效果。3. 回归参数表如下页图所示，回归参数表是表中最后一个部分：上一页下一页返回本节首页第10章 回归分析33上一页下一页返回本节首页第10章 回归分析34图中，回归参数如下：Intercept：截距0第二、三行：0(截距) 和1(斜率)的各项指标。第二列：回归系数0(截距)和1(斜率)的值。第三列：回归系数的标准误差第四列：根据原假设Ho：0=1=0计算的样本 统计量t的值。第五列：各个回归系数的p值(双侧)第六列：0和195%的置信区间的上下限。 上一页下一页返回本节首页第10章 回归分析3510.4 多元回归分析 10.4.1 案例研究：销售额与广告媒体的关系 10.4.2 回归输出结果解释上一页下一页返回本章首页第10章 回归分析3610.4.1 案例研究：销售额与广告媒体的关系例 某VCD连锁店非常想知道在电视台做广告与在广播 电台做广告哪种媒体更有效。它收集了连锁店各个 商店的每月销售额（万元）和每月用在以上两种媒 介的广告支出。试问：在显著性水平为0.05的基础上，销售额是否同两种媒介的广告有关？每种媒介上的广告支出额对销售额的影响如何？哪种广告形式带来的成本效益更高?上一页下一页返回本节首页第10章 回归分析37操作过程：打开“第10章 多元回归分析.xls”工作簿，选择“VCD”工作表，如下图所示。上一页下一页返回本节首页第10章 回归分析38在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框，在“分析工具”列表中选择 “回归”选项，单击“确定”按钮，进入“回归”对话框。在“Y值输入区域”中输入A1:A21单元格，它代表销售额的数据范围。在“X值输入区域”中输入B1:C21单元格，这里包括“广播”与“电视”两个自变量，回归工具要求自变量之间必须是相邻的，不能隔开。上一页下一页返回本节首页第10章 回归分析39选中标志。选择95%的置信度在“输出区域”中输入D1单元格，表示输出结果的起点。单击“确定”按钮。得多元回归计算结果如下图所示。上一页下一页返回本节首页第10章 回归分析4010.4.2 回归输出结果解释 1. 回归统计表调整复测定系数为28.99%，这说明两种媒体的广告支出只能解释销售额变动的29%，大约销售额变动的71%要由其他因素的变动来解释。估计标准误差为210.9553，说明实际值与估计值之间的误差 。上一页下一页返回本节首页第10章 回归分析412. 方差分析表 方差分析的目的是进行回归方程的回归效果检验，F统计量的P值约等于0.021，小于显著水平0.05，说明方程回归效果显著，方程中至少有一个回归系数显著不为零。 上一页下一页返回本节首页第10章 回归分析423. 回归参数表 回归方程为： 广播广告支出的回归系数的t统计量的p值近似等于0.013。说明在显著性水平0.05时要拒绝原假设，而在0.1显著水平时接受原假设。此题中的p值证明每月用于广播的广告支出同VCD盘的销售额是相关的。 2175.127151.154565.256xxy上一页下一页返回本节首页第10章 回归分析43电视广告支出的回归系数的t统计量的p值很高，接近0.18，值得注意。尽管它的回归系数同用于广播广告支出的回归系数比较接近，但如此大的p值说明电视广告支出同销售额之间不存在相关，那么抽取的回归系数不为零的的概率就是p值(0.18)。在给定5%的显著性水平下，无法拒绝2为零的原假设。结论便是电视广告支出回归系数不显著。上一页下一页返回本节首页第10章 回归分析44决策建议： 用于广播的广告支出的回归系数比较大，并且比用于电视广告支出的回归系数显著，这说明VCD连锁店应该把更多的广告支出用于广播，而减少对电视台的广告支出。上一页下一页返回本节首页第10章 回归分析4510.5 非线性回归分析 10.5.1 非线性关系的线性化10.5.2 案例研究：成本产量多项式模型拟合10.5.3 案例研究：产量收益对数模型拟合 上一页下一页返回本章首页第10章 回归分析4610.5.1 非线性关系的线性化实际分析工作中，有许多回归模型的因变量与自变量之间的关系并不呈现线性关系，此时，可以通过线性变换来使其线性化，从而利用回归分析工具进行分析。上一页下一页返回本节首页第10章 回归分析471. 多项式模型在只有一个自变量的情况下，多项式模型形式如下：2. 对数模型对数模型方程为： .2210 xbxbby)( xbLnay上一页下一页返回本节首页第10章 回归分析483. 幂函数幂函数的方程形式为：4. 指数模型指数模型方程为： baxybxcey上一页下一页返回本节首页第10章 回归分析4910.5.2 案例研究：成本产量多项式模型拟合 例 某企业财务经理欲对企业的成本进行控制， 为了掌握成本与产量的数量关系，需要拟 合一个成本函数。财务经理搜集了50组成 本与产量的资料，试以0.05的显著水平拟合 二项成本函数。 上一页下一页返回本节首页第10章 回归分析50操作过程：打开“第10章 回归分析.xls”工作簿，选择“多项式”工作表，如下图所示。上一页下一页返回本节首页第10章 回归分析51绘制成本产量散点图，详细过程参照10.2.1制图过程。如下图所示。成本产量散点图1030507090450950145019502450产量成本上一页下一页返回本节首页第10章 回归分析52 在B栏中任选一个单元格，打开“插入”菜单，选择“列”选项，则在A、B两列之间增加一列，成本数据将被移到C列。新列必须放在“产量”列旁边，因为Excel要求自变量是相邻的。在单元格B1中输入“产量平方”，在单元格B2中输入公式“=A22”以计算A2单元格数值的平方，并复制到B3:B51区域中的各个单元格中。如下页图所示。上一页下一页返回本节首页第10章 回归分析53上一页下一页返回本节首页第10章 回归分析54 在 “工具”菜单中选择“数据分析”选项，打开“数据分析”对话框，“分析工具”列表中选择 “回归”选项，单击“确定”按钮,进入“回归”对话框，如下图所示。 上一页下一页返回本节首页第10章 回归分析55在“Y值输入区域”中输入C1:C51，它代表成本的数据范围，在“X值输入区域”中输入A1:B51，这里包括“产量”与“产量平方”两个自变量，回归工具要求自变量之间必须是相邻的，不能隔开。选中标志。选择95%的置信度。上一页下一页返回本节首页第10章 回归分析56在“输出区域”中输入D1单元格，表示输出结果的起点。单击“确定”按钮。得多元回归计算结果如下图所示。上一页下一页返回本节首页第10章 回归分析57回归计算结果分析：R Square 约为0.79 说明产量与产量平方这两个变量可解释成本变化的79%，而其余的21%变动要由其他因素的影响来解释。F统计量为约89.18，根据F统计量得到的P值近似为0，远远小于显著水平0.05，说明回归方程有效。回归系数分别在单元格E18和E19中，其相应的t检验值及p值表明回归系数显著不为零，能够解释成本的变化。据此可写出方程：y =12.78-0.1255x+0.000044x2 上一页下一页返回本节首页第10章 回归分析5810.5.3 案例研究：产量收益对数模型拟合例 某制衣企业想了解产量与收益之间 的关系，为此收集整理了历年的产 量收益数据资料情况，根据这些资 料建立适当模型说明产量收益之间 的关系。 上一页下一页返回本节首页第10章 回归分析59操作过程：打开“第10章 回归分析.xls”工作簿，选择“对数”工作表如下图所示。上一页下一页返回本节首页第10章 回归分析60制作产量收益散点图判断采用的模型形式，如下图所示。产量收益散点图05101520253035404505001000150020002500 产量收益上一页下一页返回本节首页第10章 回归分析61利用回归分析工具得到分析结果如下图所示。 上一页下一页返回本节首页第10章 回归分析62回归计算结果分析：R Square 约为0.87 说明产量与产量平方这两个变量可解释成本变化的87%，而其余的13%变动要由其他因素的影响来解释。F统计量为约318.83，根据F统计量得到的P值近似为0，远远小于显著水平0.05，说明回归方程有效。回归系数分别在单元格E17和E18中，其相应的t检验值及p值表明回归系数显著不为零，能够解释成本的变化。据此可写出方程： y =162.05+25.83Ln(x)上一页下一页返回本节首页第10章 回归分析6310.6 品质变量回归分析 10.6.1 两种表现的品质变量回归分析 10.6.2 多种表现的品质变量回归分析上一页下一页返回本章首页第10章 回归分析6410.6.1 两种表现的品质变量回归分析 例 某大学教务处对学生的动手能力颇感兴趣，在 研究中发现学生的学习成绩特别是统计成绩同 计算机有关。他们将学生分成两类，一类是利 用计算机学习统计，另一类是不用计算机学习 统计。现随机从利用计算机与不用计算机的学 生中抽取两个样本，包括统计成绩和过去的绩 分点在显著水平0.05上，能否确定使用计算机 学生的统计成绩高于不使用计算机学生的统计 成绩？ 上一页下一页返回本节首页第10章 回归分析65操作过程：打开“第10章 回归分析.xls”工作簿，选择“计算机”工作表如下图所示。上一页下一页返回本节首页第10章 回归分析66在C栏中任选一个单元格，打开“插入”菜单，选择“列”选项，则在B、C两列之间增加一列。在单元格C1中输入“计算机”。在单元格C2中输入条件函数IF的表达式“=IF(D2=”是“,1,0)”，式中表示如果真则为1，如果不真则为0。单元格C2显示值1，将其复制到C3:C21各单元格中。上一页下一页返回本节首页第10章 回归分析67在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框，在“分析工具”列表中选择 “回归”选项，单击“确定”按钮，进入“回归”对话框，如下图所示。上一页下一页返回本节首页第10章 回归分析68在“Y值输入区域”输入地址为A1:A21，在“X值输入区域”输入地址为B1:C21；选择“标志”和95%的置信度。在“输出区域”中输入E2，单击“确定”按钮，得计算结果如下图所示。上一页下一页返回本节首页第10章 回归分析6910.6.2 多种表现的品质变量回归分析 例 现有某地区15个房地产的售价、居住面 积、评估价值和建筑等级(低、中、高)。 试根据表中资料拟合售价的预测模型 。上一页下一页返回本节首页第10章 回归分析70操作过程： 打开“第10章 回归分析.xls”工作簿，选择“房地产”工作表如下图所示。上一页下一页返回本节首页第10章 回归分析71在单元格E1、F1和G1中分别输入“高”、“中”和“低”。 在 单 元 格 E 2 中 输 入 公 式 ： I F ( D 2 “高”,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上一页退出返回本章首页个人观点供参考，欢迎讨论