《第六章 回归分析.ppt》由会员分享，可在线阅读，更多相关《第六章 回归分析.ppt（48页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、经济与管理应用软件第六章 Excel在回归分析中的应用6.1 概述现实世界中大多数现象表现为相关关系，人们通过大量观察，将现象之间的相关关系抽象概括为函数关系，并用函数形式或模型来描述与推断现象间的具体变动关系，用一个或一组变量的变化来估计与推算另一个变量的变化。这种分析方法称为回归分析。经济与管理应用软件回归分析的主要内容回归参数估计 方程拟合效果评价 回归参数的推断 经济与管理应用软件6.2 图表分析与回归函数分析 例 近年来国家教育部决定将各高校的后勤社 会化。某从事饮食业的企业家认为这是一 个很好的投资机会，他得到十组高校人数 与周边饭店的季销售额的数据资料，并想 根据高校的数据决策其
2、投资规模。经济与管理应用软件操作过程：打开“饭店.xls”工作簿，选择“饭店”工作表，如下图所示。经济与管理应用软件从“插入”菜单中选择“图表”选项，打开“图表向导”对话框如下图所示。在“图表类型”列表中选择XY散点图，单击“下一步”按钮。经济与管理应用软件在数据区域中输入B2:C11，选择“系列产生在列”，如下图所示，单击“下一步”按钮。经济与管理应用软件打开“图例”页面，取消图例，省略标题，如下图所示。经济与管理应用软件单击“完成”按钮，便得到XY散点图如下图所示。经济与管理应用软件如图1所示，用鼠标激活散点图，把鼠标放在任一数据点上，单击鼠标右键，打开菜单，在菜单栏里选择“填加趋势线”选
3、项，打开趋势线对话框如图2所示。经济与管理应用软件经济与管理应用软件打开“类型”页面，选择“线性”选项，Excel将显示一条拟合数据点的直线。打开“选项”页面如图3所示，在对话框下部选择“显示公式”和“显示R平方根”选项，单击“确定”按钮，便得到趋势回归图如图4所示。经济与管理应用软件经济与管理应用软件回归分析中的工作表函数截距函数INTERCEPT 功能：利用已知的 x 值与 y 值计算回归直线在y 轴 的截距。语法结构：INTERCEPT(known_ys,known_xs)斜率函数SLOPE 功能：返回根据 known_ys 和 known_xs 中的数据 点拟合的线性回归直线的斜率。语
4、法结构：SLOPE(known_ys,known_xs)经济与管理应用软件测定系数函数RSQ 功能：返回根据 known_ys 和 known_xs 中数 据点 计算得出的 Pearson 乘积矩相关系数的平方。语法结构：RSQ(known_ys,known_xs)估计标准误差函数STEYX 功能：返回通过线性回归法计算 y 预测值时所产生 的标准误差。标准误差用来度量根据单个 x 变量计算出的 y 预测值的误差量。语法结构：STEYX(known_ys,known_xs)经济与管理应用软件例 某企业希望确定其产品制造过程中的每 月成本支出与产量之间的关系，以制定 生产计划。试根据该企业选择历
5、年的产 量(吨)和成本支出(千元)的样本，计算 上面四个函数值。经济与管理应用软件操作过程：打开“成本产量.xls”工作簿，选择“成本产量”工作表，如下图所示。经济与管理应用软件在单元格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:C1
6、5,B2:B15)”，单击回车键。经济与管理应用软件6.3 回归分析工具 回归分析工具是通过对一组观察值使用“最小平方法”进行直线拟合，以分析一个或几个自变量对单个因变量的影响方向与影响程度的方法。它是Excel中数据分析工具的一个内容。回归分析的对话框如图5所示。经济与管理应用软件经济与管理应用软件例 某房地产经纪人从政府部门列举的地 区中随机抽取了15户居民作为样本，记录了他们的家庭住房面积及其相应 的价格，他想确认一下住房面积（平 方米）与价格（千元）的关系，并想 据此拟合住房价格的回归方程。经济与管理应用软件操作过程：打开“住房.xls”工作簿，选择“住房”工作表如下图所示。经济与管理
7、应用软件在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框如下图所示。经济与管理应用软件在“分析工具”列表中选择“回归”选项，单击“确定”按钮，打开“回归”对话框如下图所示。经济与管理应用软件在Y值输入区域中输入C1:C16。在X值输入区域中输入B1:B16。选择“标志”，置信度选择95%。在“输出选项”中选择“输出区域”，在其右边的位置输入“D1”，单击“确定”按钮。输出结果如下图所示。经济与管理应用软件回归分析工具的输出解释Excel的回归分析工具计算简便,但内容丰富，计算结果共分为三个模块：回归统计表 方差分析表回归参数 经济与管理应用软件回归统计表包括以下几部分内容：Mult
8、iple R（复相关系数R）：R2的平方根，又称为相关系数，它用来衡量变量x和y之间相关程度的大小。上节例中：R为0.848466，表示二者之间的关系是 高度正相关。R Square(复测定系数R2)：用来说明用自变量解释因变量变差的程度，以测量同因变量y的拟合效果。上节例中：复测定系数为0.719894，表明用自变量可解释因变量变差的71.99%。1.回归统计表经济与管理应用软件Adjusted R Square(调整复测定系数R2)：仅用于多元回归才有意义，它用于衡量加入独立变量后模型的拟合程度。当有新的独立变量加入后，即使这一变量同因变量之间不相关，未经修正的R2也要增大，修正的R2仅用
9、于比较含有同一个因变量的各种模型。标准误差：又称为标准回归误差或叫估计标准误差，它用来衡量拟合程度的大小，也用于计算与回归有关的其他统计量，此值越小，说明拟合程度越好。经济与管理应用软件观测值：是指用于估计回归方程的数据的观测值个数。2.方差分析表方差分析表的主要作用是通过F检验来判断回归模型的回归效果。3.回归参数表如下页图所示，回归参数表是表中最后一个部分：经济与管理应用软件经济与管理应用软件图中，回归参数如下：Intercept：截距0第二、三行：0(截距)和1(斜率)的各项指标。第二列：回归系数0(截距)和1(斜率)的值。第三列：回归系数的标准误差第四列：根据原假设Ho：0=1=0计算
10、的样本 统计量t的值。第五列：各个回归系数的p值(双侧)第六列：0和195%的置信区间的上下限。经济与管理应用软件案例：销售额与广告媒体的关系例 某VCD连锁店非常想知道在电视台做广告与在广播 电台做广告哪种媒体更有效。它收集了连锁店各个 商店的每月销售额（万元）和每月用在以上两种媒 介的广告支出。试问：在显著性水平为0.05的基础上，销售额是否同两种媒介的广告有关？每种媒介上的广告支出额对销售额的影响如何？哪种广告形式带来的成本效益更高?经济与管理应用软件操作过程：打开“VCD.xls”工作簿，选择“VCD”工作表，如下图所示。经济与管理应用软件在“工具”菜单中选择“数据分析”选项，打开“数
11、据分析”对话框，在“分析工具”列表中选择“回归”选项，单击“确定”按钮，进入“回归”对话框。在“Y值输入区域”中输入A1:A21单元格，它代表销售额的数据范围。在“X值输入区域”中输入B1:C21单元格，这里包括“广播”与“电视”两个自变量，回归工具要求自变量之间必须是相邻的，不能隔开。经济与管理应用软件选中标志。选择95%的置信度在“输出区域”中输入D1单元格，表示输出结果的起点。单击“确定”按钮。得多元回归计算结果如下图所示。经济与管理应用软件1.回归统计表调整复测定系数为28.99%，这说明两种媒体的广告支出只能解释销售额变动的29%，大约销售额变动的71%要由其他因素的变动来解释。估计
12、标准误差为210.9553，说明实际值与估计值之间的误差。经济与管理应用软件2.方差分析表 方差分析的目的是进行回归方程的回归效果检验，F统计量的P值约等于0.021，小于显著水平0.05，说明方程回归效果显著，方程中至少有一个回归系数显著不为零。经济与管理应用软件3.回归参数表 回归方程为：广播广告支出的回归系数的t统计量的p值近似等于0.013。说明在显著性水平0.05时要拒绝原假设，而在0.1显著水平时接受原假设。此题中的p值证明每月用于广播的广告支出同VCD盘的销售额是相关的。经济与管理应用软件电视广告支出的回归系数的t统计量的p值很高，接近0.18，值得注意。尽管它的回归系数同用于广
13、播广告支出的回归系数比较接近，但如此大的p值说明电视广告支出同销售额之间不存在相关，那么抽取的回归系数不为零的的概率就是p值(0.18)。在给定5%的显著性水平下，无法拒绝2为零的原假设。结论便是电视广告支出回归系数不显著。经济与管理应用软件决策建议：用于广播的广告支出的回归系数比较大，并且比用于电视广告支出的回归系数显著，这说明VCD连锁店应该把更多的广告支出用于广播，而减少对电视台的广告支出。经济与管理应用软件案例：成本产量多项式模型拟合 例 某企业财务经理欲对企业的成本进行控制，为了掌握成本与产量的数量关系，需要拟 合一个成本函数。财务经理搜集了50组成 本与产量的资料，试以0.05的显
14、著水平拟合 二项成本函数。经济与管理应用软件操作过程：打开“回归分析.xls”工作簿，选择“多项式”工作表，如下图所示。经济与管理应用软件绘制成本产量散点图，如下图所示。经济与管理应用软件 在B栏中任选一个单元格，打开“插入”菜单，选择“列”选项，则在A、B两列之间增加一列，成本数据将被移到C列。新列必须放在“产量”列旁边，因为Excel要求自变量是相邻的。在单元格B1中输入“产量平方”，在单元格B2中输入公式“=A22”以计算A2单元格数值的平方，并复制到B3:B51区域中的各个单元格中。如下页图所示。经济与管理应用软件经济与管理应用软件 在“工具”菜单中选择“数据分析”选项，打开“数据分析
15、”对话框，“分析工具”列表中选择“回归”选项，单击“确定”按钮,进入“回归”对话框，如下图所示。经济与管理应用软件在“Y值输入区域”中输入C1:C51，它代表成本的数据范围，在“X值输入区域”中输入A1:B51，这里包括“产量”与“产量平方”两个自变量，回归工具要求自变量之间必须是相邻的，不能隔开。选中标志。选择95%的置信度。经济与管理应用软件在“输出区域”中输入D1单元格，表示输出结果的起点。单击“确定”按钮。得多元回归计算结果如下图所示。经济与管理应用软件回归计算结果分析：R Square 约为0.79 说明产量与产量平方这两个变量可解释成本变化的79%，而其余的21%变动要由其他因素的影响来解释。F统计量为约89.18，根据F统计量得到的P值近似为0，远远小于显著水平0.05，说明回归方程有效。回归系数分别在单元格E18和E19中，其相应的t检验值及p值表明回归系数显著不为零，能够解释成本的变化。据此可写出方程：=12.78-0.1255x+0.000044x2