EXCEL在统计分析中的应用.ppt

第二部分EXCEL在统计分析中的应用 一 基本数理统计知识要利用计算机进行统计分析 就要求使用人员既要掌握一定的计算机操作方法 同时又要懂得一定的统计分析知识 本节先介绍统计分析中最基本的知识 1 总体与样本 总体 Population 是统计对象的测量值的总集合 对于感兴趣的整个组中 每个成员都对应一个这样的值 具体说来 是某省的人口 总体 或工业产值 总体 中某个指标测量值的集合 而不是那些人或物的集合 样本 Sample 是总体中的一些值的集合 子集 比如 一个样本可以是某个省中某县的人口的集合或某县工业产值的集合 总体与样本的重要区别在于 二者虽然都是一些值的集合 但是 总体 代表统计对象组成的整个测量值的集合 样本 只代表这个组群中的一个子组的测量值的集合 总体 样本 随机样本 RandomSample 是指既能够保证任何一个样本被选取的可能性都相同 又能够代表所在总体的趋势特征 一个样本充其量只是一个极其简单的随机样本 真正的随机样本的收集过程是一个科学性很强的复杂过程 2 参数与统计量 统计量 均值 用以描述数据 值 分布的中心位置 比如 对于一个总体 某省 的人口 其平均值称为总体均值 PopulationMean 记为 对于某样本 某县 的人口 其平均人口则为样本均值 SampleAverage 记为 方差 标准差的平方则是方差 方差表示数据内部的变异性 标准偏差 也为标准差 表示某人的身高或收入等测量值偏离均值多少 2 参数与统计量 参数 Parameters 是总体的统计量 用希腊字母表示 如总体均值 总体方差 2 总体标准差 统计量 Statistics 是样本的统计量 用一般的英文字母表示 如样本均值 样本方差S2 样本标准差S 3 正态分布 统计学上 一般都是假设被统计的样本是来自正态分布 NormalDistribution 的总体 正态分布是关于总体的一种理论分布 是有严格的数学定义的 从正态分布的总体中随机抽取的样本一般是服从正态分布的 4 经验规则的应用 如果数据来自服从正态分布的总体 可用下面的经验规则很快地概况出我们的数据 68 的观察值是落在离均值一个标准差 1 的范围内 95 的观察值是落在离均值二个标准差 2 的范围内 99 的观察值是落在离均值三个标准差 3 的范围内 5 小概率事件的实际不可能性原则 小概率事件 该随机事件发生的可能性很小的事件 随机事件发生的概率 0 1 如P A 0 01 表示统计规律上讲 100次重复实验中 该事件A应该只发生1次 小概率事件发生的实际不可能性原则 在一次或几次实验中 小概率事件可以认为实际上是不会发生的 例如 座火车 乘飞机等 反过来 若在一次或几次实验中 某随机事件发生了 则该事件我们可以认为不是小概率事件 这个思路成为了统计检验的基本准则 小概率事件发生的实际不可能性原则 5 显著性水平与 值 值是理论上的显著水平 也称为临界域或参考概率 理论的显著性水平 值的选择 值一般有3种取值 0 10 0 05 0 01 分别表示 中等显著 显著 高度显著 显著性水平0 05 即5 它表示 如果抽取100个样本 即进行100次的假设检验 而得出错误结论的次数大于等于5次 5 100 0 05 这里所说的 错误 实际上是指 当备择假设 对立假设H1 不正确但误认为是正确的 拒真 事件 例 有下列一对零假设和备择假设 H0 0 总体均值为0 H1 0 总体均值不为0 如果总体均值确实为0 但是您误认为总体均值不为0 这就称为 错误 统计学上称之为 第一类错误 也称之为 弃真错误 一般我们进行假设检验时 有H0和H1的设定 先确定显著性水平 如双尾检验 则如图所示 选取统计量 并用随机抽样得到的样本进行计算 最终得到相应的概率值P 若P 则我们接受H0 否则 拒绝H0 可见 发生小概率事件后 我们否定H0 这里就存在一个可能 即H0本来为真而被否决的情况 即拒真事件发生 同样 也存在 采伪事件 两类错误 弃真 的错误当备择假设 对立假设H1 不正确但误认为是正确的 统计上称这为一类错误为 弃真 的错误 1类错误 避免 弃真 的错误的方法是重新选择合适的 值 采伪 的错误本该拒绝原假设H0但未能拒绝 统计学上称之为 采伪 的错误 2类错误 若能控制好样本量 则能避免 采伪 的错误 二 相关与回归分析 回归分析和相关分析是分析现象间联系形态和密切程度的数学方法 所谓回归分析 就是对具有相互联系的现象 根据其关系的形态 选择一个合适的数据模式 用来近似地表达变量间平均变化关系 这个数学模式 称为回归方程式 相关分析是测定各个变量之间的关系密切程度的方法 它用一个指标数值表明变量之间关系的密切程度 这个表明关系密切程度的指标 称为相关指标 相关与回归分析可广泛应用于经济预测 如对农产量进行预测 对产量与成本用回归方程表示它们之间的关系等 回归与相关分析可解决的问题 1 通过回归与相关分析 观测变量之间是否有一定的联系 如存在联系 选择合适的数据模型对变量之间的联系给以近似描述 2 用统计指标说明变量之间关系的密切程度 这些统计指标还可以用来说明回归方程对观测值的拟合程度的好坏 3 根据样本资料求得的现象之间的联系形式和密切程度 推断总体中现象之间的联系形式和密切程度 4 根据自变量的数值 预测或控制因变量的数值 并应用统计推断方法 估计预测数值的可靠程度 第一节相关分析 1 相关系数 研究两个变量之间线性相关关系的密切程度 这个指标称为相关系数 1 散点图 将观察所得的各对资料依次用点绘于图上 从点子的分布是否集中以及分布的趋势 可以大致了解变量之间有无相关以及相关程度 见下页 注意 相关图虽可以说明变量间有无关系 但不能反映变量之间关系的密切程度 因此 需要计算相关系数 来描述两个变量间线性相关关系的密切程度 2 相关图 散点图 3 一元线性相关系数的计算 r计算公式 化简公式 注意 如果是分组资料 则要乘以每组的次数 相关系数的取值范围 1到 1之间 一般情况下 r 0为正相关 r 0为负相关 0 7 r 1 0高度相关 0 3 r 0 7中度相关 0 r 0 3低度相关 4 用 数据分析 工具求相关系数 假定有12个企业的产量与生产费用的资料 可据以计算相关系数 从散点图可知 有较强的正相关关系 用数据分析工具求相关系数的操作 1 在输入A B C三列原始数据后 单击 工具 菜单中的 数据分析 选项 从其对话框的 分析工具 列表中选择 相关系数 回车确认 进入相关系数对话框 2 在 相关系数 对话框的 输入区域 框中输入计算数据所在的单元格区域B1 C13 并在 分组方式 后面选择 逐列 在 分组方式 下面选择 标志位于第一行 3 在 输入区域 框中输入输出表左上角的单元格列行号D2 完成以上操回车确认 可得如下结果 X与Y的相关系数是0 9202 表明强正相关 第二节一元线性回归分析 1 一元线性回归分析的概念 一元线性回归是指一个因变量只有一个自变量有依从关系 它们之间关系的形态表现为具有直线趋势 标准形式为 Y a bX 注意 在分析时 首先可以作散点图以判定变量之间的关系是否直线型的 如果是直线型 再配合回归直线来表达变量之间的平均变化关系 一元线性回归方程的确定 通过散点图 表明两变量之间有明显的线性关系时 我们可以用很多直线来表示这两个变量之间的关系 如何在各种直线中选择一条最接近所有实际资料的直线 来构成我们的回归直线 通常是应用最小二乘法原则使 总误差的平方和为最小 来建模 可得以下回归方程公式 2 用数据分析工具求计算回归方程 1 在输入A B C三列原始数据后 单击 工具 菜单中的 数据分析 选项 从其对话框的 分析工具 列表中选择 回归 回车打开 回归 分析对话框 2 在 回归 分析工具对话框的 Y值输入区域 框中输入C2 C13 X值输入区域 框中输入B2 B13 3 在 输入区域 框中键输出表左上角的单元格行列号 本例为D2 4 在对话框中还列有以下几个复选框 用户可以根据需要选择 由此可得回归方程 Y 124 15 0 4207X a Interceptb XVariable 操作说明 如果要求回归直线从原点 0 0 开始 可单击 常数为0 复选框 默认的置信度为95 即所有的Y值以95 的概率保证在回归直线 1 96倍的估计标准误差的范围之内 如果要改变置信度 可单击 置信度 复选框 在其右侧框中输入指定的 如果要求计算结果输出残差表 并包括标准残差 可单击 残差 和 标准残差 残差即实际值减估计值的余额 等等 3 回归直线拟合程度的测定 为分析回归方程多大程度上解释了因变量的变化 有如下方法 1 相关系数 multipleR multipleR称为 复 相关系数 取值为 0 1 它是因变量Y与各自变量Xi之间的线性相关程度的度量 R值越接近1 表明Y与所有X之间的线性关系越密切 反之亦然 本例multipleR 0 9202 2 判定系数 RSquare RSquare称为方程的判定系数 取值为 0 1 它是方程中的自变量对Y的解释能力的度量 RSquare越接近1 表明回归方程拟合数据越好 或者说方程中的变量对Y的解释能力越强 反之亦然 本例RSquare 0 8468 3 调整的判定系数 AdjustedRSquare 判定系数是一个受自变量个数与样本规模之比影响的系数 为了消除这种第三方的影响 常用AdjustedRSquare来代替RSquare 两者的作用是一致的 本例AdjustedRSquare 0 8315 4 误差分析 标准误差 给出了用回归方程得出的预测值与实际值之间的差异 该值越小越说明回归方程质量越高 本例标准误差 6 7617 另外 也可从残差输出的表或图中看出回归直线于各点的拟合情况 本例除1号外 其余都小于正负10 说明拟合的回归直线符合实际 4 回归分析中的显著性检验 1 回归方程的检验 即检验自变量与因变量之间的线性关系是否显著 即进行F检验 EXCEL给出方差分析的显著性水平 SignificanceF 为判定结果是否显著 即说明回归方程的效果好 在社会科学研究中通常取0 05或0 01作为显著性的判定标准 如果计算出的 显著性F 0 05 或0 01 则表明方程中的自变量与因变量有显著的线性关系 即回归方程效果好 在本例输出结果中 找到方差分析表 从中可知 统计量F 55 28596 显著性F 2 22204E 05 即0 000022204 0 01说明X与Y之间的线性关系特别显著 所得的回归方程效果好 2 回归系数显著性检验 即检验回归方程中各个变量在方程中的重要程度 当回归方程检验显著时 可以认为回归方程中至少有一个回归系数是显著的 但是并不一定所有的回归系数都是显著的 我们希望在方程中保留最重要的变量 删除不显著的变量 为此可对每个变量的回归系数进行检验 一元线性回归方程标准格式 Y a bXEXCEL回归分析给出每个回归系数检验的显著性水平值 P value 同样 为判定哪个回归系数是否显著 即说明哪个自变量重要 通常取0 05或0 01作为显著性的判定标准 如果计算出的 P value 0 05 或0 01 则表明该自变量与因变量有显著的线性关系 即自变量重要 反之 该自变量则可从方程中去掉 在本例输出结果中 从回归系数显著性检验表从中可知 系数a 称Intercept 的显著性水平 3 86E 10系数b 称XVariable1 的显著性水平 2 22E 05都小于0 01 说明每个回归系数都是显著的 5 回归预测 不论使用何种方法进行回归分析 拟合回归方程并通过检验后 均可按给定的自变量X值来预测因变量Y值 如前拟合回归方程为 Y 124 15 0 4207X 如果产量达到150 千件 预测生成费用将达到 124 15 0 4207 150 187 25 千元 预测时要注意的问题 1 用于预测的回归方程必须是通过显著性检验的 2 预测时 自变量的取值必须在合理的范围内 3 假定其它条件不变的前提下 预测值才有其合理性 第三节多元线性回归 在实际中 往往影响Y的因素不只一个 一般地 考虑因变量Y与n个自变量x1 x2 xn的线性关系 多元线性回归方程 Y a b1x1 b2x2 bnxn右表列出了我国分地区家庭年人均粮食支出和年人均收入及粮食单价数据 我们感兴趣的是粮食支出与收入 粮食价格的关系 1 回归分析操作 用 回归分析 工具进行多元线性回归分析 1 建立数据表输入数据后 单击 工具 菜单中的 数据分析 选项 从其对话框中选择 回归 回车打开 回归 分析对话框 2 在对话框的 y值输入区域 框中输入 B3 B32 在 x值输入区域 框中输入 C3 D32 然后单击 输出区域 复选框 在其右侧框中键入输出表左上角的单元格行列号E2 3 其余各复选框可根据需要选定 回车确认 完成操作 得回归方程为 Y 87 38 0 35X1 206 54X2 2 回归直线拟合程度的测定 1 复相关系数 multipleR 0 9403 值接近1 表明Y与所有X之间的线性关系密切 2 判定系数 RSquare 0 8843 它是方程中的自变量对Y的解释能力的度量 现接近1 表明回归方程拟合数据好 或者说方程中的变量对Y的解释能力强 3 调整的判定系数 AdjustedRSquare 0 8