第六章 回归分析.ppt

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