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

1 1 一 计算机准备二 分析工具库提供的统计分析方法三 数据基本信息计算四 图表制作五 假设检验 一 t检验1 成对2 成组 二 U检验 z检验 六 方差分析 一 单因素 二 两因素1 无重复2 有重复七 回归与相关 Excel在统计分析中应用 2 一 计算机准备1 启动Excel 检查 工具 菜单中是否有 数据分析 命令 如果没有发现 数据分析 命令 就表示未加载 分析工具库 2 加载 分析工具库 步骤 工具加载宏分析工具库确定工具数据分析进入统计分析功能 3 单向分类资料的方差分析 两向分组单独观测值试验资料的方差分析 两向分组有重复观测值资料的方差分析 描述性统计量计算 二总体方差的同质性检验 编制次数分布表及绘制统计图 二 分析工具库提供的统计分析方法 4 排位与百分比排位 两样本平均数u检验 配对资料的t检验 等方差非配对资料的t检验 异方差非配对资料的t检验 一元线性回归与多元线性回归分析 计算多个变量两两之间的相关系数及协方差 进行随机和顺序抽样 5 三 数据基本信息计算 一 统计分析的粘贴函数AVERAGE计算算术平均值BINOMDIST计算二项式分布的概率值CHIDIST计算特定 2分布的单尾概率值CHIINV计算一定单尾概率值时的 2临界值CHITEST计算独立性检验的 2值CONFIDENCE计算总体平均值的置信区间CORREL计算两组数据的相关系数COVAR计算两组数据的协方差 6 FDIST计算特定F分布的单尾概率值FINV计算一定概率时的临界F值FTEST计算二个样本方差之比F值的概率GEOMEAN计算几何平均数HARMEAN计算调和平均数INTERCEPT计算直线回归的截距MAX计算最大值MEDIAN计算一组给定数字的中位数MIN计算最小值 7 MODE计算一组数据的众数NORMDIST计算正态分布的累积函数NORMINV计算正态分布累积函数的逆函数NORMSDIST计算标准正态分布的累积函数NORMSINV计算标准正态分布累积函数的逆函数POISSON计算泊松分布的概率SLOPE计算给定数据的直线回归系数STDEV计算样本标准差STDEVP计算样本总体的标准差 8 TDIST计算学生氏 t分布的概率值TINV计算特定概率时学生氏 t分布的临界t值TTEST计算t检验时的学生氏 t检验相关的概率VAR计算样本的方差VARP计算样本总体的方差ZTEST计算Z检验的双尾概率值 9 9 二 利用粘贴函数计算方法例举1 算术平均数 AVERAGE 数值区域 2 几何平均数 GEOMEAN 数据区域 3 中数 MEDIAN 数据区域 4 极差 最大值 MAX 数据区域 最小值 MIN 数据区域 极差 最大值 最小值5 方差 VARP 数据区域 6 标准差 STDEVP 数据区域 7 变异系数 标准差 算术平均数 10 三 描述统计DescriptiveStatistics 13 13 四 图表制作1 输入原始数据和各组下限 可用功能 MIN MAX 2 工具数据分析直方图3 输入区域 原始数据接受区域 各组下限输出区域 图标位置图表输出4 设定图表选项 图表类型 表题 坐标 选项等 14 表4 1100听罐头样品的净重 g 15 表4 2100盒鲜枣每盒检出不合格枣数 16 例5 1 为了分析某种新型减肥药剂是否对人具有显著减肥作用 现随机选取12位自愿者进行试验 服药后 间隔1个疗程 分别测其12位自愿者的体重见下表 假设服药前后 除服用此减肥药剂外 其余的生活方式 生活条件均未变化 试问根据此试验结果 能否判断这种新型减肥药对人具有显著减肥作用 五 假设检验HypothesisTesting 一 t检验1 成对数据资料的平均数t检验 17 17 工具数据分析t 检验 平均值的成对二样本分析变量1区域 B 2 B 14变量2区域 C 2 C 14假设平均值 0标志 0 05输出区域 D 10确定 18 19 结果分析 所以否定H0 接受HA即 服用新型减肥药剂的自愿者体重有极显著差异 因此可得出结论 这种新型减肥药剂对人具有减肥作用 效果极显著 23 例5 2 某番茄品种分别按3200株 667m2种植 各调查5个小区 得产量见下表 试测验两种密度的小麦产量有无显著差异 23 2 成组数据资料的平均数t检验 24 24 工具数据分析t检验 双样本等方差检验 t检验 双样本异方差检验 变量1区域 A 1 A 6变量2区域 B 1 C 6假设平均值 0标志 0 05确定 25 25 结果分析 t 1 33t0 05 2 31 t t0 05接受Ho即 两种密度小区产量无差异 26 例5 3 现有两种茶多糖提取工艺 分别从两种工艺中各取1个随机样本来测定其粗提物中的茶多糖含量 结果见表4 问两种工艺的粗提物中茶多糖含量有无显著差异 表5 3两种工艺粗提物中茶多糖含量测定结果 双样本假设检验可先检验两个样本的方差是否相等 用F检验法 然后再选择检验方法 利用Excel提供的 F检验 双样本方差 工具进行检验 27 2 利用Excel提供的 F检验 双样本方差 工具进行检验 28 结果分析 由F检验结果可得 F 1 044304 F0 025 4 5 7 38788 故接受H0 认为两个样本的方差相等 即两种工艺的粗提物中茶多糖含量的方差相等 注意 Excel只提供了F检验的单尾临界值 而本例属于双边假设检验问题 因此需要查找双尾临界值 查F分布表得 0 05 n1 5 n2 6 29 因此选用 t检验 双样本等方差假设 30 结果分析 故接受H0 即 两种工艺的粗提物中茶多糖含量无显著差异 31 在以下的情况中 可以利用Z检验分析工具来检验两个总体均值之间是否存在差异 总体方差已知 当总体方差未知 n 30 Z检验 u检验 教材P79 例4 4 某食品厂在甲乙两条生产线上各测了30个日产量如下表 试检验两条生产线的平均日产量有无显著差异 先用粘贴函数 VAR 数值 计算方差 输入格式单行或单列 将方差数值代入用z 检验 推断 结论 36 六 方差分析AnalysisofVariance 一 单因素试验方差分析 例6 1 以淀粉为原料生产葡萄糖过程中 残留的许多糖蜜可用于酱色生产 在生产酱色之前应尽可能彻底除杂 以保证酱色质量 为此对除杂方法进行选择 今选用5种除杂方法 每种方法做4次试验 试验结果见表5 试分析不同除杂方法的除杂效果有无差异 设各总体服从正态分布 且方差相等 37 37 工具数据分析方差分析 单因素方差分析输入区域 A 3 E 7分组方式 行标志位于第1列 0 01确定 44 注意 对照具体事例 汇总显著水平0 05和0 01两次计算结果 先将方差分析表做具体化修改完善 F测验 F 49 55 F0 01 4 15 4 89 所以 在显著性水平0 01下拒绝原假设H0 认为5种不同除杂方法的除杂效果有极显著差异 但哪几个方法差异显著 哪几个方法差异不显著 尚需进一步进行多重比较分析 但Excel分析工具尚不能自动完成 无法完成最后结论表述 需手工完成多重比较 45 1 两因素无重复试验的方差分析 例6 2 某厂现有化验员3人 担任该厂牛奶酸度 T 的检验 每天从牛奶中抽样一次进行检验 连续10天的检验分析结果见下表 试分析3名化验员的化验技术有无差异 以及每天的原料牛奶酸度有无差异 二 两因素试验方差分析 46 46 工具数据分析方差分析 无重复双因素输入区域 A 1 D 5标志 0 01确定 继续手工完成结果推断和多重比较过程 52 表6 73种肥料施于3种土壤的小麦产量 g 52 例6 6 施用A1 A2 A33种肥料于B1 B2 B33种土壤 以小麦为指示作物 每处理组合种3盆 得产量结果 g 于表6 7 试作方差分析 2 两因素有重复试验的方差分析 53 输入格式 54 54 工具数据分析方差分析 可重复双因素输入区域 A 1 D 10每一样本的行数 3 0 05确定 60 整理后 继续手工完成结果推断和多重比较过程 61 61 例6 4 设有A B C D E这5个大豆品种 其中E为对照 进行大区比较试验 成熟后分别在5块地随机抽取4个样点 每点产量 kg 列入下表 试对A B C D四个品种做产量比较 注意 方差分析表要对照试验做具体化修改完善 62 整理后 63 例6 5 将A1 A2 A3 A4四种生长素 并用B1 B2 B3三种时间浸渍菜用大豆品种种子 45天后处理平均单株干物重 g 列入表6 5试做方差分析 64 整理后 65 65 教材P157 例6 1 某食品感官评定时 测得食品甜度与蔗糖质量分数的关系如下表 试求y对x的直线回归方程 七 直线回归与相关 输入格式 得回归方程为