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

.,1,Excel统计分析,.,2,职场人生：Excel+ppt,.,3,第一章统计基础与数据描述第二章描述性统计第三章数据库统计函数第四章统计指数,提纲,.,4,例1.1：上证180指数的月收益率,统计基础与数据描述,C4=(B4-B3)/B3,.,5,例1.2COUNTIF函数：计算区域内满足给定条件的单元格的个数COUNTIF(Range,Criteria)，Range表示区域,Criteria表示条件,统计基础与数据描述,C3=COUNTIF(例1.1!B3:B38,=2400),D3=C3D4=C4-C3,.,6,例1.3Frequency函数：返回区域内数据的频率分布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),.,7,例1.4频率分布图,统计基础与数据描述,.,8,例1.5相对频率分布图,统计基础与数据描述,D3=C3/$C$13,.,9,例1.6累积频率分布图,统计基础与数据描述,.,10,例1.7直方图,统计基础与数据描述,.,11,中心趋势离中趋势偏度峰度,描述性统计,.,12,中心趋势：算术平均值,C13=SUM(C3:C12)C14=C13/10,C13=AVERAGE(C3:C12),.,13,中心趋势：几何平均值,第一年投资100元，亏损50元，收益率=(50-100)/100=-50%第二年投资50元，盈利50元，收益率=(100-50)/50=100%两年平均收益率=(-50%+100%)/2=25%,.,14,中心趋势：几何平均值,D16=GEOMEAN(D4:D15)-1,.,15,众数:出现频率最高的数,中心趋势：众数,B10=MODE(A2:B9),.,16,中位数:居中的数,中心趋势：中位数,B10=MEDIAN(A2:B9),.,17,调和平均数:数的倒数的算术平均值的倒数,中心趋势：调和平均数,B3=1/A3,B10=1/SUM(B3:B9),.,18,离中趋势：方差,B13=VAR(B3:B12),.,19,离中趋势：标准差,B13=STDEV(B3:B12),.,20,离中趋势：四分位数,B13=QUARTILE(B3:B12,1)B14=QUARTILE(B3:B12,3),四分位数：位于25%和75%位置的数称为低四分位数和高四分位数,刻画数据相对于中位数的离散程度,QUARTILE(Array,Quart)Quart=0,min1,低四分位数2,中位数3,高四分位数4,max,.,21,离中趋势：方差系数,B15=AVERAGE(B3:B14)B16=STDEV(B3:B14)C15=AVERAGE(C3:C14)C16=STDEV(C3:C14)B17=B16/B15C17=C16/C15,方差系数：标准差与均值之间的比值。消除了量纲影响,.,22,偏度：偏斜度,B21=SKEW(B3:B20),.,23,偏度：四分位数偏度系数,B21=QUARTILE(B3:B20,1)B22=QUARTILE(B3:B20,2)B23=QUARTILE(B3:B20,3)B24=(B23+B21-2*B22)/(B23-B21),.,24,偏度：Spearman偏度系数,B21=AVERAGE(B3:B20)B22=STDEV(B3:B20)B23=MEDIAN(B3:B20)B24=3*(B21-B23)/B22,.,25,峰度：峰值,B22=KURT(B3:B21),.,26,峰度：矩峰度系数,3，常峰态，正态分布3，高峰态,B22=AVERAGE(B3:B21)C3=(B3-$B$22)2D3=(B3-$B$22)4C22=SUM(D3:D21)D23=D22/18/(C22/18)4,.,27,Dfunctions(database,field,criteria)database：数据库单元格区域field：字段criteria：包含过滤条件的单元格区域DCOUNT,DCOUNTA,DSUM,DPRODUCT,DMAX,DMIN,DAVERAGE,DVAR,DSTDEV,DVARP,DSTDEVP,DGET,数据库统计函数,.,28,.,29,统计指数：同等加权指数,.,30,统计指数：拉氏指数,.,31,统计指数：帕氏指数,.,32,统计指数：埃奇沃斯指数,.,33,统计指数：费雪指数,.,34,统计指数：资本加权指数,.,35,某事件发生的概率为p，不发生的概率为q，则在N次实验中该事件发生X次的概率为又称伯努利分布BINOMDIST(number,trials,probability,cumulative)number：实验成功的次数trials：独立实验的次数probability：每次独立实验成功的概率cumulative：true返回累积分布，false返回密度函数,概率分布：二项分布,.,36,股票上涨的概率为0.6，20个交易日中上涨5天的概率，以及上涨小于等于5天的概率,概率分布：二项分布,.,37,股票上涨的概率为0.6，求20个交易日中概率分布函数大于等于临界值0.75的最小天数CRITBINOM(试验次数,每次试验的成功概率,临界值),概率分布：二项分布,.,38,在r次成功之前失败的次数X的概率(帕斯卡分布)NEGBINOMDIST(失败次数,成功次数,成功概率)灯泡合格概率0.8,求找到30个合格灯泡之前，需要测试10个不合格灯泡的概率,概率分布：负二项分布,.,39,25(N)种股票，15(M)只沪市，10(N-M)只深市，从中选10(n)种，其中有5(x)只属于沪市的概率HYPGEODIST(成功次数,样本数,总样本成功次数,总样本数),概率分布：超几何分布,.,40,随机事件发生的次数POISSON(事件数,期望值,返回的概率分布形式)平均每天呼叫20次，一天收到15次和小于等于15次呼叫的概率,概率分布：泊松分布,.,41,股票收益率呈正态分布，均值为5%，标准差为2%，求收益率为4%对应的概率密度函数值,概率分布：正态分布,NORMDIST(x,均值,标准差,返回的概率分布形式),.,42,股票收益率80%的可能性不超过某值，求这个值,概率分布：正态分布,NORMSINV(概率,均值,标准差),.,43,概率分布：正态分布,.,44,概率分布：正态分布,.,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,（三）描述统计DescriptiveStatistics,.,55,.,56,.,57,57,四、图表制作1、输入原始数据和各组下限【可用功能：=MIN()=MAX()】2、工具数据分析直方图3、输入区域：原始数据接受区域：各组下限输出区域：图标位置图表输出4、设定图表选项：图表类型；表题；坐标；选项等,.,58,表41100听罐头样品的净重（g）,.,59,表4-2100盒鲜枣每盒检出不合格枣数,.,60,【例5-1】为了分析某种新型减肥药剂是否对人具有显著减肥作用，现随机选取12位自愿者进行试验，服药后，间隔1个疗程，分别测其12位自愿者的体重见下表，假设服药前后，除服用此减肥药剂外，其余的生活方式、生活条件均未变化。试问根据此试验结果，能否判断这种新型减肥药对人具有显著减肥作用。,五、假设检验HypothesisTesting,(一)t检验1、成对数据资料的平均数t检验,.,61,61,61,工具数据分析t-检验：平均值的成对二样本分析变量1区域：B2:B14变量2区域：C2:C14假设平均值：0标志：0.05输出区域：D10确定,.,62,.,63,.,64,.,65,.,66,结果分析:所以否定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.33t0.05=2.31tt0.05接受Ho即：两种密度小区产量无差异。,.,70,【例5-3】现有两种茶多糖提取工艺，分别从两种工艺中各取1个随机样本来测定其粗提物中的茶多糖含量，结果见表4。问两种工艺的粗提物中茶多糖含量有无显著差异？,表5-3两种工艺粗提物中茶多糖含量测定结果,双样本假设检验可先检验两个样本的方差是否相等，用F检验法；然后再选择检验方法,利用Excel提供的“F检验：双样本方差”工具进行检验。,.,71,（2）利用Excel提供的“F检验：双样本方差”工具进行检验。,.,72,结果分析：由F检验结果可得，F1.04430430,Z检验（u检验）：,.,76,教材P79【例4-4】某食品厂在甲乙两条生产线上各测了30个日产量如下表，试检验两条生产线的平均日产量有无显著差异？,.,77,先用粘贴函数=VAR(数值)计算方差,输入格式单行或单列,.,78,将方差数值代入用z-检验,.,79,推断结论,.,80,六、方差分析AnalysisofVariance,（一）单因素试验方差分析,【例6-1】以淀粉为原料生产葡萄糖过程中，残留的许多糖蜜可用于酱色生产。在生产酱色之前应尽可能彻底除杂，以保证酱色质量。为此对除杂方法进行选择。今选用5种除杂方法，每种方法做4次试验，试验结果见表5，试分析不同除杂方法的除杂效果有无差异？设各总体服从正态分布，且方差相等。,.,81,81,81,工具数据分析方差分析：单因素方差分析输入区域：A3:E7分组方式：行标志位于第1列：0.01确定,.,82,.,83,.,84,.,85,.,86,.,87,.,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天的检验分析结果见下表。试分析3名化验员的化验技术有无差异，以及每天的原料牛奶酸度有无差异。,（二）两因素试验方差分析,.,90,90,90,工具数据分析方差分析：无重复双因素输入区域：A1:D5标志：0.01确定,.,91