《excel在统计分析中的应用.ppt》由会员分享，可在线阅读，更多相关《excel在统计分析中的应用.ppt（69页珍藏版）》请在三一办公上搜索。
1、1,1,一、计算机准备二、分析工具库提供的统计分析方法三、数据基本信息计算四、图表制作五、假设检验（一）t检验1.成对2.成组（二）U检验（z检验）六、方差分析（一）单因素（二）两因素 1、无重复 2、有重复七、回归与相关,Excel在统计分析中应用,2,一、计算机准备1、启动Excel，检查“工具”菜单中是否有“数据分析”命令。如果没有发现“数据分析”命令，就表示未加载“分析工具库”。2、加载“分析工具库”。步骤：工具 加载宏 分析工具库 确定 工具 数据分析 进入统计分析功能,3,单向分类资料的方差分析 两向分组单独观测值试验资料的方差分析 两向分组有重复观测值资料的方差分析 描述性统计量
2、计算 二总体方差的同质性检验 编制次数分布表及绘制统计图,二、分析工具库提供的统计分析方法,4,排位与百分比排位 两样本平均数u检验 配对资料的t检验 等方差非配对资料的t检验 异方差非配对资料的t检验 一元线性回归与多元线性回归分析 计算多个变量两两之间的相关系数及协方差 进行随机和顺序抽样,5,三、数据基本信息计算（一）统计分析的粘贴函数AVERAGE 计算算术平均值BINOMDIST 计算二项式分布的概率值CHIDIST 计算特定2分布的单尾概率值CHIINV 计算一定单尾概率值时的2临界值CHITEST 计算独立性检验的2值CONFIDENCE 计算总体平均值的置信区间CORREL 计
3、算两组数据的相关系数COVAR 计算两组数据的协方差,6,FDIST 计算特定 F 分布的单尾概率值FINV 计算一定概率时的临界 F 值FTEST 计算二个样本方差之比 F 值的概率GEOMEAN 计算几何平均数HARMEAN 计算调和平均数INTERCEPT 计算直线回归的截距MAX 计算最大值MEDIAN 计算一组给定数字的中位数MIN 计算最小值,7,MODE 计算一组数据的众数NORMDIST 计算正态分布的累积函数NORMINV 计算正态分布累积函数的逆函数NORMSDIST 计算标准正态分布的累积函数NORMSINV 计算标准正态分布累积函数的逆函数POISSON 计算泊松分布的
4、概率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、方差。=V
5、ARP(数据区域)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个
6、疗程，分别测其12位自愿者的体重见下表，假设服药前后，除服用此减肥药剂外，其余的生活方式、生活条件均未变化。试问根据此试验结果，能否判断这种新型减肥药对人具有显著减肥作用。,五、假设检验 Hypothesis Testing,(一)t检验 1、成对数据资料的平均数t检验,17,17,工具 数据分析 t-检验：平均值的成对二样本分析变量1区域：B2:B14变量2区域：C2:C14假设平均值：0标志：0.05 输出区域：D10 确定,18,19,结果分析:所以否定H0，接受HA 即：服用新型减肥药剂的自愿者体重有极显著差异。因此可得出结论：这种新型减肥药剂对人具有减肥作用，效果极显著。,23,【例
7、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.31tt0.05接受Ho即：两种密度小区产量无差异。,26,【例5-3】现有两种茶多糖提取工艺，分别从两种工艺中各取1个随机样本来测定其粗提物中的茶多糖含量，结果见表4。问两种工艺的粗提物中茶多糖含量有无显著差异？,表5-3
8、 两种工艺粗提物中茶多糖含量测定结果,双样本假设检验可先检验两个样本的方差是否相等，用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检验：双样本等方差假
9、设,30,结果分析：,故接受H0，即：两种工艺的粗提物中茶多糖含量无显著差异。,31,在以下的情况中，可以利用Z 检验分析工具来检验两个总体均值之间是否存在差异。总体方差已知 当总体方差未知，n30,Z 检验（u检验）：,教材P79【例4-4】某食品厂在甲乙两条生产线上各测了30个日产量如下表，试检验两条生产线的平均日产量有无显著差异？,先用粘贴函数=VAR(数值)计算方差,输入格式单行或单列,将方差数值代入 用z-检验,推断结论,36,六、方差分析 Analysis of Variance,（一）单因素试验方差分析,【例6-1】以淀粉为原料生产葡萄糖过程中，残留的许多糖蜜可用于酱色生产。在生
10、产酱色之前应尽可能彻底除杂，以保证酱色质量。为此对除杂方法进行选择。今选用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种不同除杂方法的除杂效果有极显著差异。但哪几个方法差异显著，哪几个方法差异不显著
11、，尚需进一步进行多重比较分析，但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种肥料于
12、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四个品种做产量比较。,注意：方差分析表要对照试验做具体化修改完善。,62,整理后：,63,【例6-5】将A1、A2、A3、A4四种生长素，并用B1、B2、B3三种时间浸渍菜用大豆品种种子，45天后处理平均单株干物重（g）列入表6-5 试做方差分析。,64,整理后：,65,65,教材P157【例6-1】某食品感官评定时，测得食品甜度与蔗糖质量分数的关系如下表，试求y对x的直线回归方程。,七、直线回归与相关,输入格式,得回归方程为：,
