附Excel在统计分析中的应用.ppt

Excel在统计分析中的应用 基本篇 Excel统计分析工具库建立 Excel提供了一组强大的数据分析工具 分析工具库 分析工具库 的安装 1 在Excel 工具 菜单中 单击 加载宏 命令 2 由 加载宏 对话框中找到 分析工具库 选项 将其选中 然后单击 确定 按钮 即可完成安装 3 安装完成后在Excel 工具 菜单下就会新增加 数据分析 命令 如果在步骤2 加载宏 对话框中没有找到 分析工具库 选项 就必须运行 Microsoftoffice安装 程序来加载 分析工具库 1描述统计DescriptiveStatistics 描述统计的任务就是描述随机变量的统计规律性 常用的描述统计量有数学期望 方差 协方差 相关系数 矩等 数学期望 方差是最常用的两个统计量 描述统计分析简单 描述统计分析 统计量计算 2假设检验HypothesisTesting 假设检验就是根据样本的信息来判断总体分布是否具有假设的特征 例如 已知样本来自正态总体 那么是否能说明它是来自均值等于的正态分布呢 又如 已知两个相对独立的样本分别来自两个正态分布 那么是否能说这两个总体的均值相同 或方差相同 这些都属于假设检验问题 有时为了比较两种产品 两种仪器 两种方法等等的差异性 常在相同的条件下作对比试验 得到一批成对的观察值 然后分析观察数据 从而作出推断 这种方法称为逐对比较法 在假设检验中利用t统计量进行检验 因而称为基于成对数据的t检验或成对观测值的t检验 成对数据资料中的成对数据不是相互独立的 而是存在某种联系 因而进行成对数据平均数显著性检验时 应从成对数据入手 2 1成对数据的假设检验 例题 为了分析某种新型减肥药剂是否对人具有显著减肥作用 现随机选取12位自愿者进行试验 服药后 间隔1个疗程 分别测其12位自愿者的体重见表2 成对数据资料的均值检验 假设服药前后 除服用此减肥药剂外 其余的生活方式 生活条件均未变化 试问根据此试验结果 能否判断这种新型减肥药对人具有显著减肥作用 本例为典型的成对观测值t检验问题 1 利用Excel提供的统计函数TTEST来检验 有两种检验方法 录入试验数据 插入统计函数TTEST 单击确定按钮 弹出TTEST对话窗口 本例t检验所计算的概率值p 0 000604 远远小于0 01 那么否定H0 接受HA 即服药前后自愿者的体重有极限著变化 表明减肥药剂有极限著减肥效果 2 利用Excel2000提供的 t 检验 平均值的成对二样本分析 工具进行分析 先将观察数据输入工作表中 选择工具菜单中的数据分析命令 弹出数据分析对话框 在分析工具列表中 选择t 检验 平均值的成对二样本分析工具 单击确定按钮 弹出t 检验 平均值的成对二样本分析对话窗口 对检验结果分析 可以看出 样本的t统计量等于4 74566402 大于t0 05 2的双边临界值2 20098627 也大于t0 01 2的双边临界值3 105815 可查也可计算 即 所以拒绝原假设H0 在置信度为0 01的情况下 服用新型减肥药剂的自愿者体重有极显著差异 因此 可得出结论 这种新型减肥药剂对人具有减肥作用 效果极显著 也可以由概率值P的大小来直接判断 现在来讨论有关两个正态总体均值和方差的假设检验问题 假设有两个相互独立的样本 分别来自于正态总体 和 均未知 试从样本统计量去推断总体的均值 方差是否相等 即 2 2双样本假设检验 现有两种茶多糖提取工艺 分别从两种工艺中各取1个随机样本来测定其粗提物中的茶多糖含量 结果见表4 问两种工艺的粗提物中茶多糖含量有无显著差异 这是一个典型的双样本假设检验问题 下面就方差检验和均值检验分别给予讨论 表4两种工艺粗提物中茶多糖含量测定结果 目的是检验两个样本的方差是否相等 2 2 1双样本方差的检验 F检验 1 利用统计函数进行检验 FTEST 有两种方法 本例计算的概率值P 0 936852912 0 05 表明双样本的方差无显著差异 也就是说两种工艺的粗提物中茶多糖含量的方差相等 2 利用Excel2000提供的 F检验 双样本方差 工具进行检验 对检验结果分析 可知 Excel2000只提供了F检验的单尾临界值 而本例属于双边假设检验问题 因此需要查找双尾临界值 查F分布表得 0 05 n1 5 n2 6 由F检验结果可得 F 1 044304 F0 025 4 5 7 38788 故接受原假设H0 认为两个样本的方差相等 即两种工艺的粗提物中茶多糖含量的方差相等 注意 由于Excel2000只提供F检验的单尾临界值 因此对于显著性水平 的双边F检验问题 在F检验 双样本方差对话框中的 框内如果输入 2 则Excel即可给出 临界值 这样可以避免查F分布表的麻烦 对于本例 如果在 框内输入0 025 Excel即可给出F检验的单尾临界值 2 2 2双样本等方差均值假设检验 1 Excel提供的统计函数TTEST来检验 有两种检验方法 本例计算结果P 0 20080 0 05 表明两个样本的均数无显著差异性 即两种工艺的粗提物中茶多糖含量无显著差异 2 利用Excel2000提供的 t检验 双样本等方差假设 工具进行检验 对检验结果分析 可知 所以接受原假设H0 即两种工艺的粗提物中茶多糖含量无显著差异 在以下的情况中 可以利用Z检验分析工具来检验两个总体均值之间是否存在差异 总体方差及标准差已知 不论样本数的大小为多少 我们都可以使用Z检验来进行总体平均差 1 2 的假设检验 当总体方差及标准差未知的情况 但所抽取的样本数大于30时 我们也可以使用Z检验来进行总体平均差的假设检验 2 3Z检验 双样本均值分析 一般而言 在两总体方差为已知 总体平均差 1 2 的统计量 可以用下面的公式求得 若两总体方差为未知 总体平均差 1 2 的统计量 可以用下面的公式求得 当两总体的样本数量小于30时 或者是无法得知两总体的方差和标准差值时 可以使用t检验 在两总体方差为未知的情况下 则小样本总体平均差 1 2 的检验统计量为 也就是说 当两总体方差为已知时 我们使用Z分布来进行检验 若总体方差未知时 我们使用t分布来检验 方差分析AnalysisofVariance ANOVA 3 1单因素试验方差分析 科学试验中仅仅考察一个因素的试验 称为单因素试验 单因素试验是最简单的科学试验 也是最常用的预备试验设计方法 例如 以淀粉为原料生产葡萄糖过程中 残留的许多糖蜜可用于酱色生产 在生产酱色之前应尽可能彻底除杂 以保证酱色质量 为此对除杂方法进行选择 今选用5种除杂方法 每种方法做4次试验 试验结果见表5 试分析不同除杂方法的除杂效果有无差异 设各总体服从正态分布 且方差相等 本研究的试验指标是除杂量 除杂方法为试验因素 不同的5种除杂方法就是试验因素的五个不同水平 假定除了除杂方法这一试验因素外 其余的一切试验条件都相同 这就是单因素试验 试验的目的是要分析5种不同除杂方法的除杂效果有无显著的差异 以确定最佳除杂方法 这是典型的单因素试验方差分析问题 借助于Excel单因素方差分析工具进行分析 对统计结果进行分析 可以看出 单元格区域A13 G17中的数据与单因素试验方差分析表中的各个统计量相对应 其中组间为因素A 组内为误差e 总计为总和 差异源为方差来源 SS为平方和 df为自由度 MS为均方 方差 F为F值 P value为接受原假设H0的概率 此P值越接近0 说明接受原假设的可能性就越小 反之亦然 Fcrit为拒绝域的临界值 方差分析结果可见F 49 55323 F0 01 d4 15 4 89 F0 05 4 15 3 055568 所以 在显著性水平0 01下拒绝原假设H0 认为5种不同除杂方法的除杂效果有极显著差异 但哪几个方法差异显著 哪几个方法差异不显著 尚需进一步进行多重比较分析 但Excel分析工具尚不能自动完成 由平均数大小可以初步判断A4方法的除杂效果理想 3 2双因素试验方差分析 如果在试验中只有两个因素在改变 而其他因素保持不变 则称为双因素试验 双因素试验的方差分析就是观察两个因素的不同水平对研究对象的影响是否有显著性差异 根据是否考虑两个因素的交互作用 又将双因素方差分析分为双因素有重复试验的方差分析和双因素无重复试验的方差分析 3 2 1双因素有重复试验的方差分析 例如 在生产某种金属材料时 使用了四种原料 三种热处理温度 对于每种原料与每种热处理温度的组合各生产两次 产品强度的测定结果如图19所示 问原料 处理温度以及这两者的交互作用对产品强度是否有显著的影响 取显著性水平 0 05 利用Excel2000提供的方差分析 可重复双因素分析工具分析 样本为因素A 列为因素B 交互为因素A与B的交互作用 内部为误差 总计为总和 差异源为方差来源 SS为平方和 df为自由度 MS为均方 F为F值 P value为接受原假设H0的概率 Fcrit为拒绝域的临界值Fa u v 在显著性水平0 05下 原料和处理温度这两个因素对产品强度的影响都是显著的 且FI 14 9288远大于 2 9961 故两者的交互作用效应是高度显著的 3 2 2双因素无重复试验的方差分析 某厂现有化验员3人 担任该厂牛奶酸度 T 的检验 每天从牛奶中抽样一次进行检验 连续10天的检验分析结果见表10 试分析3名化验员的化验技术有无差异 以及每天的原料牛奶酸度有无差异 利用Excel2000提供的 方差分析 无重复双因素分析 工具分析 4回归分析RegressionAnalysis 设所研究的对象 受多个因素 的影响 假定影响因素与 的关系是线性的 则可建立多元线性回归数学模型 式中 代表影响因素 通常是可以控制或预先给定的 故称为解释变量或自变量 代表各种随机因素对的影响的总和 称为随机误差项 根据中心极限定理 可以认为它服从正态分布 即 为所研究的对象 称为被解释变量或因变量 例如 在改革中 某食品企业重视科技人才 提供了足够的科研经费 获得了良好的经济效益 表11是该食品企业1987 1998年的经济效益 科研人员 科研经费的统计数据 根据这些统计数据 试建立企业经济效益与科研人员 科研经费之间的回归方程 回归分析举例 本实例有两个自变量 科研人员 x1 与科研经费 x2 因变量为经济效益 y 相关性分析 选择工具菜单中的数据分析命令 弹出数据分析对话框 在分析工具列表框中 选相关系数工具 这时将出现相关系数对话框 科研人员 科研经费和经济效益都有较强的相关性 因此 需要利用回归分析工具进一步建立关系式 选择工具菜单中的数据分析命令 弹出数据分析对话框 在分析工具列表框中 选回归工具 这时 将弹出回归对话框 回归模型的建立操作步骤如下 在回归统计区域中 给出的 为0 997644 调整后的 为0 997121 均很接近1 说明 与 的关系很密切 检验 检验 在方差分析区域 给出的 检验值为1905 786远远大于 4 26 说明 与 之间的回归效果非常显著 检验 回归系数的检验 科研人员与科研经费对该企业的经济效益有显著影响 优化回归方程为 5正交试验结果分析 在实际工作中 常常需要同时考察3个或3个以上的试验因素 若进行全面试验 则试验的规模很大 往往因试验条件的限制而难于实施 对于多因素多水平试验 一般采用部分实施方法来设计试验的 如正交试验设计 均匀试验设计等 正交试验设计就是安排多因素试验 寻求最优水平组合的一种高效率试验设计方法 它从试验的全部水平组合中 挑选部分有代表性的水平组合进行试验 通过对这部分试验结果的分析了解全面试验的情况 找出最优的水平组合 5 1极差分析 根据极差Rj的大小 可以判断各因素对试验指标的影响主次 比较各R值大小 可见RB RA RD RC 所以因素对试验指标影响的主次顺序是BADC 即加酶量影响最大 其次是加水量和酶解时间 而酶解温度的影响较小 5 2方差分析 实例 自溶酵母提取物是一种多用途食品配料 为探讨啤酒酵母的最适自溶条件 安排三因素三水平正交试验 试验指标为自