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,（三）描述统计 Descriptive Statistics,13,13,四、图表制作 1、输入原始数据和各组下限【 可用功能：=MIN( ) =MAX( ) 】 2、工具 数据分析 直方图 3、输入区域：原始数据 接受区域：各组下限 输出区域：图标位置 图表输出 4、设定图表选项：图表类型；表题；坐标；选项等,14,表41 100听罐头样品的净重（g）,15,表4-2 100盒鲜枣每盒检出不合格枣数,16,【例5-1】为了分析某种新型减肥药剂是否对人具有显著减肥作用，现随机选取12位自愿者进行试验，服药后，间隔1个疗程，分别测其12位自愿者的体重见下表，假设服药前后，除服用此减肥药剂外，其余的生活方式、生活条件均未变化。试问根据此试验结果，能否判断这种新型减肥药对人具有显著减肥作用。,五、假设检验 Hypothesis Testing,(一)t检验 1、成对数据资料的平均数t检验,17,17,工具 数据分析 t-检验：平均值的成对二样本分析 变量1区域：B2:B14 变量2区域：C2:C14 假设平均值：0 标志： ：0.05 输出区域：D10 确定,18,19,结果分析: 所以否定H0 ，接受HA 即：服用新型减肥药剂的自愿者体重有极显著差异。 因此可得出结论：这种新型减肥药剂对人具有减肥作用，效果极显著。,23,【例5-2】某番茄品种分别按3200株/667m2种植，各调查5个小区，得产量见下表，试测验两种密度的小麦产量有无显著差异。,23,2、成组数据资料的平均数t检验,24,24,工具 数据分析 t 检验：双样本等方差检验(t 检验：双样本异方差检验) 变量1区域：A1:A6 变量2区域：B1:C6 假设平均值：0 标志： ：0.05 确定,25,25,结果分析： t=1.33 t0.05=2.31 tt0.05 接受Ho 即：两种密度小区产量无差异。,26,【例5-3】现有两种茶多糖提取工艺，分别从两种工艺中各取1个随机样本来测定其粗提物中的茶多糖含量，结果见表4。问两种工艺的粗提物中茶多糖含量有无显著差异？,表5-3 两种工艺粗提物中茶多糖含量测定结果, 双样本假设检验可先检验两个样本的方差是否相等，用F检验法；然后再选择检验方法,利用Excel 提供的“F检验：双样本方差”工具进行检验。,27,（2）利用Excel 提供的“F检验：双样本方差”工具进行检验。,28,结果分析：由F检验结果可得，F1.044304F0.025（4,5）=7.38788,故接受H0，认为两个样本的方差相等，即两种工艺的粗提物中茶多糖含量的方差相等。 注意：Excel只提供了F检验的单尾临界值，而本例属于双边假设检验问题，因此需要查找双尾临界值。查F分布表得（ =0.05，n1=5，n2=6）,29,因此选用 ：t检验：双样本等方差假设,30,结果分析：,故接受H0，即：两种工艺的粗提物中茶多糖含量无显著差异。,31,在以下的情况中，可以利用Z 检验分析工具来检验两个总体均值之间是否存在差异。 总体方差已知 当总体方差未知，n30,Z 检验（u检验）：,教材P79【例4-4】某食品厂在甲乙两条生产线上各测了30个日产量如下表，试检验两条生产线的平均日产量有无显著差异？,先用粘贴函数=VAR(数值)计算方差,输入格式单行或单列,将方差数值代入 用z-检验,推断结论,36,六、方差分析 Analysis of Variance,（一）单因素试验方差分析,【例6-1】以淀粉为原料生产葡萄糖过程中，残留的许多糖蜜可用于酱色生产。在生产酱色之前应尽可能彻底除杂，以保证酱色质量。为此对除杂方法进行选择。今选用5种除杂方法，每种方法做4次试验，试验结果见表5，试分析不同除杂方法的除杂效果有无差异？设各总体服从正态分布，且方差相等。,37,37,工具 数据分析 方差分析：单因素方差分析 输入区域：A3:E7 分组方式：行 标志位于第1列： ：0.01 确定,44,注意：对照具体事例，汇总显著水平0.05和0.01两次计算结果，先将方差分析表做具体化修改完善。 F测验：F=49.55F0.01（4，15）4.89，所以，在显著性水平0.01下拒绝原假设H0，认为5种不同除杂方法的除杂效果有极显著差异。 但哪几个方法差异显著，哪几个方法差异不显著，尚需进一步进行多重比较分析，但Excel分析工具尚不能自动完成，无法完成最后结论表述，需手工完成多重比较。,45,1、两因素无重复试验的方差分析,【例6-2】某厂现有化验员3人，担任该厂牛奶酸度（T）的检验。每天从牛奶中抽样一次进行检验，连续10天的检验分析结果见下表。试分析3名化验员的化验技术有无差异，以及每天的原料牛奶酸度有无差异。,（二）两因素试验方差分析,46,46,工具 数据分析 方差分析：无重复双因素 输入区域：A1:D5 标志： ：0.01 确定,继续手工完成结果推断和多重比较过程,52,表6-7 3种肥料施于3种土壤的小麦产量（g),52,【例6-6】施用A1、A2、A3 3种肥料于B1、B2、B3 3种土壤，以小麦为指示作物，每处理组合种3盆，得产量结果(g)于表6-7。试作方差分析。,2、两因素有重复试验的方差分析,53,输入格式,54,54,工具 数据分析 方差分析：可重复双因素 输入区域：A1:D10 每一样本的行数：3 ：0.05 确定,60,整理后,继续手工完成结果推断和多重比较过程,61,61,【例6-4】设有A、B、C、D、E这5个大豆品种，其中E为对照，进行大区比较试验，成熟后分别在5块地随机抽取4个样点，每点产量（kg）列入下表，试对A、B、C、D四个品种做产量比较。,注意：方差分析表要对照试验做