Excel在统计分析中的应用(全113页).ppt

Excel统计分析,职场人生：Excel+ppt,第一章 统计基础与数据描述 第二章 描述性统计 第三章 数据库统计函数 第四章 统计指数,提纲,例1.1：上证180指数的月收益率,统计基础与数据描述,C4=(B4-B3)/B3,例1.2 COUNTIF函数：计算区域内满足给定条件的单元格的个数 COUNTIF(Range, Criteria)，Range表示区域, Criteria表示条件,统计基础与数据描述,C3=COUNTIF(例1.1!B3:B38,“=2400“),D3=C3 D4=C4-C3,例1.3 Frequency函数：返回区域内数据的频率分布 Frequency(data_array, bins_array)，data_array,统计基础与数据描述,B5=-0.12 等差序列，步长0.04，终止值=0.12,C5=FREQUENCY(例1.1!C4:C38,B5:B11),C13=SUM(C5:C12),例1.4 频率分布图,统计基础与数据描述,例1.5 相对频率分布图,统计基础与数据描述,D3=C3/$C$13,例1.6 累积频率分布图,统计基础与数据描述,例1.7 直方图,统计基础与数据描述,中心趋势 离中趋势 偏度 峰度,描述性统计,中心趋势：算术平均值,C13=SUM(C3:C12) C14=C13/10,C13=AVERAGE(C3:C12),中心趋势：几何平均值,第一年投资100元，亏损50元，收益率=(50-100)/100 = -50% 第二年投资50元，盈利50元，收益率=(100-50)/50 = 100% 两年平均收益率=(-50%+100%)/2=25%,中心趋势：几何平均值,D16=GEOMEAN(D4:D15)-1,众数:出现频率最高的数,中心趋势：众数,B10=MODE(A2:B9),中位数:居中的数,中心趋势：中位数,B10=MEDIAN(A2:B9),调和平均数:数的倒数的算术平均值的倒数,中心趋势：调和平均数,B3=1/A3,B10=1/SUM(B3:B9),离中趋势：方差,B13=VAR(B3:B12),离中趋势：标准差,B13=STDEV(B3:B12),离中趋势：四分位数,B13=QUARTILE(B3:B12,1) B14=QUARTILE(B3:B12,3),四分位数：位于25%和75%位置的数称为低四分位数和高四分位数,刻画数据相对于中位数的离散程度,QUARTILE(Array, Quart) Quart= 0,min 1,低四分位数 2,中位数 3,高四分位数 4,max,离中趋势：方差系数,B15=AVERAGE(B3:B14) B16=STDEV(B3:B14) C15=AVERAGE(C3:C14) C16=STDEV(C3:C14) B17=B16/B15 C17=C16/C15,方差系数：标准差与均值之间的比值。消除了量纲影响,偏度：偏斜度,B21=SKEW(B3:B20),偏度：四分位数偏度系数,B21=QUARTILE(B3:B20,1) B22=QUARTILE(B3:B20,2) B23=QUARTILE(B3:B20,3) B24=(B23+B21-2*B22)/(B23-B21),偏度：Spearman偏度系数,B21=AVERAGE(B3:B20) B22=STDEV(B3:B20) B23=MEDIAN(B3:B20) B24=3*(B21-B23)/B22,峰度：峰值,B22=KURT(B3:B21),峰度：矩峰度系数,3，常峰态，正态分布 3，高峰态,B22=AVERAGE(B3:B21) C3=(B3-$B$22)2 D3=(B3-$B$22)4 C22=SUM(D3:D21) D23=D22/18/(C22/18)4,Dfunctions(database, field, criteria) database：数据库单元格区域 field：字段 criteria：包含过滤条件的单元格区域 DCOUNT, DCOUNTA, DSUM, DPRODUCT, DMAX, DMIN, DAVERAGE, DVAR, DSTDEV, DVARP, DSTDEVP, DGET,数据库统计函数,统计指数：同等加权指数,统计指数：拉氏指数,统计指数：帕氏指数,统计指数：埃奇沃斯指数,统计指数：费雪指数,统计指数：资本加权指数,某事件发生的概率为p，不发生的概率为q，则在N次实验中该事件发生X次的概率为 又称伯努利分布 BINOMDIST(number, trials, probability, cumulative) number：实验成功的次数 trials：独立实验的次数 probability：每次独立实验成功的概率 cumulative：true返回累积分布，false返回密度函数,概率分布：二项分布,股票上涨的概率为0.6，20个交易日中上涨5天的概率，以及上涨小于等于5天的概率,概率分布：二项分布,股票上涨的概率为0.6，求20个交易日中概率分布函数大于等于临界值0.75的最小天数 CRITBINOM(试验次数, 每次试验的成功概率, 临界值),概率分布：二项分布,在r次成功之前失败的次数X的概率(帕斯卡分布) NEGBINOMDIST(失败次数, 成功次数, 成功概率) 灯泡合格概率0.8, 求找到30个合格灯泡之前，需要测试10个不合格灯泡的概率,概率分布：负二项分布,25(N)种股票，15(M)只沪市，10(N-M)只深市，从中选10(n)种，其中有5(x)只属于沪市的概率 HYPGEODIST(成功次数, 样本数, 总样本成功次数, 总样本数),概率分布：超几何分布,随机事件发生的次数 POISSON(事件数, 期望值, 返回的概率分布形式) 平均每天呼叫20次，一天收到15次和小于等于15次呼叫的概率,概率分布：泊松分布,股票收益率呈正态分布，均值为5%，标准差为2%，求收益率为4%对应的概率密度函数值,概率分布：正态分布,NORMDIST(x, 均值, 标准差, 返回的概率分布形式),股票收益率80%的可能性不超过某值，求这个值,概率分布：正态分布,NORMSINV(概率, 均值, 标准差),概率分布：正态分布,概率分布：正态分布,45,45,一、计算机准备 二、分析工具库提供的统计分析方法 三、数据基本信息计算 四、图表制作 五、假设检验（一）t检验1.成对2.成组（二）U检验（z检验） 六、方差分析（一）单因素 （二）两因素 1、无重复 2、有重复 七、回归与相关,Excel在统计分析中应用,46,一、计算机准备 1、启动Excel ， 检查“工具”菜单中是否有“数据分析”命令。如果没有发现“数据分析”命令，就表示未加载“分析工具库”。 2、加载“分析工具库”。步骤： 工具 加载宏 分析工具库 确定 工具 数据分析 进入统计分析功能,47, 单向分类资料的方差分析 两向分组单独观测值试验资料的方差分析 两向分组有重复观测值资料的方差分析 描述性统计量计算 二总体方差的同质性检验 编制次数分布表及绘制统计图,二、分析工具库提供的统计分析方法,48, 排位与百分比排位 两样本平均数u检验 配对资料的t检验 等方差非配对资料的t检验 异方差非配对资料的t检验 一元线性回归与多元线性回归分析 计算多个变量两两之间的相关系数及协方差 进行随机和顺序抽样,49,三、数据基本信息计算 （一）统计分析的粘贴函数 AVERAGE 计算算术平均值 BINOMDIST 计算二项式分布的概率值 CHIDIST 计算特定2分布的单尾概率值 CHIINV 计算一定单尾概率值时的2临界值 CHITEST 计算独立性检验的2值 CONFIDENCE 计算总体平均值的置信区间 CORREL 计算两组数据的相关系数 COVAR 计算两组数据的协方差,50,FDIST 计算特定 F 分布的单尾概率值 FINV 计算一定概率时的临界 F 值 FTEST 计算二个样本方差之比 F 值的概率 GEOMEAN 计算几何平均数 HARMEAN 计算调和平均数 INTERCEPT 计算直线回归的截距 MAX 计算最大值 MEDIAN 计算一组给定数字的中位数 MIN 计算最小值,51,MODE 计算一组数据的众数 NORMDIST 计算正态分布的累积函数 NORMINV 计算正态分布累积函数的逆函数 NORMSDIST 计算标准正态分布的累积函数 NORMSINV 计算标准正态分布累积函数的逆函数 POISSON 计算泊松分布的概率 SLOPE 计算给定数据的直线回归系数 STDEV 计算样本标准差 STDEVP 计算样本总体的标准差,52,TDIST 计算学生氏-t 分布的概率值 TINV 计算特定概率时学生氏-t 分布的临界t值 TTEST 计算t 检验时的学生氏-t检验相关的概率 VAR 计算样本的方差 VARP 计算样本总体的方差 ZTEST 计算Z检验的双尾概率值,53,53,（二）利用粘贴函数计算方法例举 1、算术平均数。=AVERAGE(数值区域) 2、几何平均数。=GEOMEAN(数据区域) 3、中数。=MEDIAN(数据区域) 4、极差。最大值=MAX(数据区域)；最小值=MIN(数据 区域)。极差=最大值-最小值 5、方差。=VARP(数据区域) 6、标准差。=STDEVP(数据区域) 7、变异系数。标准差/算术平均数,54,（三）描述统计 Descriptive Statistics,57,57,四、图表制作 1、输入原始数据和各组下限【 可用功能：=MIN( ) =MAX( ) 】 2、工具 数据分析 直方图 3、输入区域：原始数据 接受区域：各组下限 输出区域：图标位置 图表输出 4、设定图表选项：图表类型；表题；坐标；选项等,58,表41 100听罐头样品的净重（g）,59,表4-2 100盒鲜枣每盒检出不合格枣数,60,【例5-1】为了分析某种新型减肥药剂是否对人具有显著减肥作用，现随机选取12位自愿者进行试验，服药后，间隔1个疗程，分别测其12位自愿者的体重见下表，假设服药前后，除服用此减肥药剂外，其余的生活方式、生活条件均未变化。试问根据此试验结果，能否判断这种新型减肥药对人具有显著减肥作用。,五、假设检验 Hypothesis Testing,(一)t检验 1、成对数据资料的平均数t检验,61,61,工具 数据分析 t-检验：平均值的成对二样本分析 变量1区域：B2:B14 变量2区域：C2:C14 假设平均值：0 标志： ：0.05 输出区域：D10 确定,62,63,结果分析: 所以否定H0 ，接受HA 即：服用新型减肥药剂的自愿者体重有极显著差异。 因此可得出结论：这种新型减肥药剂对人具有减肥作用，效果极显著。,67,【例5-2】某番茄品种分别按3200株/667m2种植，各调查5个小区，得产量见下表，试测验两种密度的小麦产量有无显著差异。,67,2、成组数据资料的平均数t检验,68,68,工具 数据分析 t 检验：双样本等方差检验(t 检验：双样本异方差检验) 变量1区域：A1:A6 变量2区域：B1:C6 假设平均值：0 标志： ：0.05 确定,69,69,结果分析： t=1.33 t0.05=2.31 tt0.05 接受Ho 即：两种密度小区产量无差异。,70,【例5-3】现有两种茶多糖提取工艺，分别从两种工艺中各取1个随机样本来测定其粗提物中的茶多糖含量，结果见表4。问两种工艺的粗提物中茶多糖含量有无显著差异？,表5-3 两种工艺粗提物中茶多糖含量测定结果, 双样本假设检验可先检验两个样本的方差是否相等，用F检验法；然后再选择检验方法,利用Excel 提供的“F检验：双样本方差”工具进行检验。,71,（2）利用Excel 提供的“F检验：双样本方差”工具进行检验。,72,结果分析：由F检验结果可得，F1.044304F0.025（4,5）=7.38788,故接受H0，认为两个样本的方差相等，即两种工艺的粗提物中茶多糖含量的方差相等。 注意：Excel只提供了F检验的单尾临界值，而本例属于双边假设检验问题，因此需要查找双尾临界值。查F分布表得（ =0.05，n1=5，n2=6）,73,因此选用 ：t检验：双样本等方差假设,74,结果分析：,故接受H0，即：两种工艺的粗提物中茶多糖含量无显著差异。,75,在以下的情况中，可以利用Z 检验分析工具来检验两个总体均值之间是否存在差异。 总体方差已知 当总体方差未知，n30,Z 检验（u检验）：,教材P79【例4-4】某食品厂在甲乙两条生产线上各测了30个日产量如下表，试检验两条生产线的平均日产量有无显著差异？,先用粘贴函数=VAR(数值)计算方差,输入格式单行或单列,将方差数值代入 用z-检验,推断结论,80,六、方差分析 Analysis of Variance,（一）单因素试验方差分析,【例6-1】以淀粉为原料生产葡萄糖过程中，残留的许多糖蜜可用于酱色生产。在生产酱色之前应尽可能彻底除杂，以保证酱色质量。为此对除杂方法进行选择。今选用5种除杂方法，每种方法做4次试验，试验结果见表5，试分析不同除杂方法的除杂效果有无差异？设各总体服从正态分布，且方差相等。,81,81,工具 数据分析 方差分析：单因素方差分析 输入区域：A3:E7 分组方式：行 标志位于第1列： ：0.01 确定,88,注意：对照具体事例，汇总显著水平0.05和0.01两次计算结果，先将方差分析表做具体化修改完善。 F测验：F=49.55F0.01（4，15）4.89，所以，在显著性水平0.01下拒绝原假设H0，认为5种不同除杂方法的除杂效果有极显著差异。 但哪几个方法差异显著，哪几个方法差异不显著，尚需进一步进行多重比较分析，但Excel分析工具尚不能自动完成，无法完成最后结论表述，需手工完成多重比较。,89,1、两因素无重复试验的方差分析,【例6-2】某厂现有化验员3人，担任该厂牛奶酸度（T）的检验。每天从牛奶中抽样一次进行检验，连续10天的