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

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Excel统计分析,1,职场人生：,Excel+ppt,2,第一章 统计基础与数据描述,第二章 描述性统计,第三章 数据库统计函数,第四章 统计指数,提纲,3,例1.1：上证180指数的月收益率,统计基础与数据描述,上证,180,指数,时间,指数,收益率,2002/1,2,828.95,2002/2,2,869.65,0.014387,2002/3,2,941.43,0.025014,2002/4,3,028.49,0.029598,2002/5,2,773.89,-0.08407,2002/6,3,299.06,0.189326,C4=(B4-B3)/B3,4,例,1.2 COUNTIF,函数：计算区域内满足给定条件的单元格的个数,COUNTIF(Range,Criteria),，,Range,表示区域,Criteria,表示条件,统计基础与数据描述,上证,180,按指数分组,组号,上证,180,累积频率,观测值数目,1,2400.0,1,1,2,2400.0-2500.0,4,3,3,2500.0-2600.0,9,5,4,2600.0-2700.0,12,3,5,2700.0-2800.0,16,4,6,2800.0-2900.0,25,9,7,2900.0-3000.0,28,3,8,3000.0-3100.0,31,3,9,3100.0-3200.0,34,3,10,3200.0-3300.0,36,2,C3=COUNTIF(,例,1.1!B3:B38,=2400),D3=C3,D4=C4-C3,5,例,1.3 Frequency,函数：返回区域内数据的频率分布,Frequency(data_array,bins_array),，,data_array,统计基础与数据描述,上证,180,指数按收益率分组,应用,FREQUENCY,函数,间隔,观测值数目,-0.12,0,-0.08,3,-0.04,9,0,6,0.04,10,0.08,5,0.12,1,1,合计,35,B5=-0.12,等差序列，步长,0.04,，终止值,=0.12,C5=FREQUENCY(,例,1.1!C4:C38,B5:B11),C13=SUM(C5:C12),6,例1.4 频率分布图,统计基础与数据描述,上证,180,按指数分组,组号,上证,180,观测值数目,1,2400.0,1,2,2400.0-2500.0,3,3,2500.0-2600.0,5,4,2600.0-2700.0,3,5,2700.0-2800.0,4,6,2800.0-2900.0,9,7,2900.0-3000.0,3,8,3000.0-3100.0,3,9,3100.0-3200.0,3,10,3200.0-3300.0,2,7,例1.5 相对频率分布图,统计基础与数据描述,上证,180,按指数分组,组号,上证,180,观测值数目,相对频率,1,2400.0,1,0.0277778,2,2400.0-2500.0,3,0.0833333,3,2500.0-2600.0,5,0.1388889,4,2600.0-2700.0,3,0.0833333,5,2700.0-2800.0,4,0.1111111,6,2800.0-2900.0,9,0.25,7,2900.0-3000.0,3,0.0833333,8,3000.0-3100.0,3,0.0833333,9,3100.0-3200.0,3,0.0833333,10,3200.0-3300.0,2,0.0555556,36,D3=C3/$C$13,8,例1.6 累积频率分布图,统计基础与数据描述,上证,180,按指数分组,组号,指数值,累积频率,1,2400.0,1,2,2400.0-2500.0,4,3,2500.0-2600.0,9,4,2600.0-2700.0,12,5,2700.0-2800.0,16,6,2800.0-2900.0,25,7,2900.0-3000.0,28,8,3000.0-3100.0,31,9,3100.0-3200.0,34,10,3200.0-3300.0,36,9,例1.7 直方图,统计基础与数据描述,接收,频率,累积,%,-0.12,0,0.00%,-0.08,3,8.57%,-0.04,9,34.29%,0,6,51.43%,0.04,10,80.00%,0.08,5,94.29%,0.12,1,97.14%,0.16,0,97.14%,0.2,1,100.00%,其他,0,100.00%,10,中心趋势,离中趋势,偏度,峰度,描述性统计,11,中心趋势：,算术平均值,股票价格算术平均值,代码,证券名,价格,600000,浦发银行,7.24,600004,白云机场,8.54,600006,东风汽车,2.94,600008,首创股份,8.2,600009,上海机场,16.65,600011,华能国际,6.97,600015,华夏银行,4.05,600016,民生银行,5.69,600018,上港集箱,16.37,600019,宝钢股份,6.17,求和,82.82,算术平均值,8.282,股票价格算术平均值,代码,证券名,价格,600000,浦发银行,7.24,600004,白云机场,8.54,600006,东风汽车,2.94,600008,首创股份,8.2,600009,上海机场,16.65,600011,华能国际,6.97,600015,华夏银行,4.05,600016,民生银行,5.69,600018,上港集箱,16.37,600019,宝钢股份,6.17,算术平均值,8.282,C13=SUM(C3:C12),C14=C13/10,C13=AVERAGE(C3:C12),12,中心趋势：,几何平均值,第一年投资,100,元，亏损,50,元，收益率,=(50-100)/100=-50%,第二年投资,50,元，盈利,50,元，收益率,=(100-50)/50=100%,两年平均收益率,=(-50%+100%)/2=25%,13,上证,180,指数收益率几何平均值,时间,指数值,收益率,r,1+r,2003/12,2828.8,2004/1,3019.04,0.06725113,1.067251,2004/2,3140.53,0.04024127,1.040241,2004/3,3213.48,0.02322856,1.023229,2004/4,2912.81,-0.0935652,0.906435,2004/5,2819.49,-0.0320378,0.967962,2004/6,2528.44,-0.1032279,0.896772,2004/7,2548.49,0.00792979,1.00793,2004/8,2479.16,-0.0272043,0.972796,2004/9,2591,0.04511205,1.045112,2004/10,2452.73,-0.0533655,0.946635,2004/11,2490.28,0.01530947,1.015309,2004/12,2362.07,-0.0514842,0.948516,采用,GEOMEAN,函数求几何平均值,-0.01491,中心趋势：,几何平均值,D16=GEOMEAN(D4:D15)-1,14,众数:出现频率最高的数,中心趋势,：,众数,年龄的众数,20,20,19,21,21,20,18,23,20,22,19,21,21,18,21,21,众数,21,B10=MODE(A2:B9),15,中位数:居中的数,中心趋势,：,中位数,非组数据的中位数,8,12,9,12,10,13,10,14,11,15,11,16,11,18,11,19,中位数,11.5,B10=MEDIAN(A2:B9),16,调和平均数:数的倒数的算术平均值的倒数,中心趋势,：,调和平均数,求调和平均数,X,1/X,5,0.20,8,0.13,12,0.08,16,0.06,17,0.06,9,0.11,20,0.05,调和平均,10.13,B3=1/A3,B10=1/SUM(B3:B9),17,离中趋势,：,方差,日期,价格,20050307,7.65,20050308,7.78,20050309,7.57,20050310,7.45,20050311,7.41,20050314,7.37,20050315,7.16,20050316,7.16,20050317,7.13,20050318,7.19,样本方差,0.052201,B13=VAR(B3:B12),18,离中趋势,：,标准差,日期,价格,20050307,7.65,20050308,7.78,20050309,7.57,20050310,7.45,20050311,7.41,20050314,7.37,20050315,7.16,20050316,7.16,20050317,7.13,20050318,7.19,样本标准差,0.228476,B13=STDEV(B3:B12),19,离中趋势,：,四分位数,B13=QUARTILE(B3:B12,1),B14=QUARTILE(B3:B12,3),四分位数：位于25%和75%位置的数称为低四分位数和高四分位数,刻画数据相对于中位数的离散程度,日期,价格,20050307,7.65,20050308,7.78,20050309,7.57,20050310,7.45,20050311,7.41,20050314,7.37,20050315,7.16,20050316,7.16,20050317,7.13,20050318,7.19,Q1,7.17,Q3,7.54,QUARTILE(Array,Quart),Quart=,0,min,1,低四分位数,2,中位数,3,高四分位数,4,max,20,离中趋势,：,方差系数,B15=AVERAGE(B3:B14),B16=STDEV(B3:B14),C15=AVERAGE(C3:C14),C16=STDEV(C3:C14),B17=B16/B15,C17=C16/C15,方差系数：标准差与均值之间的比值。消除了量纲影响,采用方差系数度量分散程度,日期,东风汽车,上海机场,20050310,3.17,16.06,20050311,3.16,16.55,20050314,3.1,17.27,20050315,3.1,16.82,20050316,3.09,16.6,20050317,3.02,16.65,20050318,2.97,16.52,20050321,2.94,16.65,20050322,2.71,17.17,20050323,2.74,16.9,20050324,2.76,16.86,20050325,2.75,16.79,平均值,2.96,16.74,标准差,0.175627,0.3158346,方差系数,0.0594,0.0189,21,偏度：,偏斜度,股价偏斜度,日期,价格,20050301,6.4,20050302,6.38,20050303,6.44,20050304,6.36,20050307,6.24,20050308,6.35,20050309,6.29,20050310,6.16,20050311,6.12,20050314,6.08,20050315,5.99,20050316,5.93,20050317,5.97,20050318,5.93,20050321,5.94,20050322,5.54,20050323,5.36,20050324,5.4,偏斜度,-0.9236,B21=SKEW(B3:B20),22,偏度：,四分位数偏度系数,日期,价格,20050301,6.4,20050302,6.38,20050303,6.44,20050304,6.36,20050307,6.24,20050308,6.35,20050309,6.29,20050310,6.16,20050311,6.12,20050314,6.08,20050315,5.99,20050316,5.93,20050317,5.97,20050318,5.93,20050321,5.94,20050322,5.54,20050323,5.36,20050324,5.4,Q1,5.93,Q2,6.10,Q3,6.34,四分位偏度系数,0.1677019,B21=QUARTILE(B3:B20,1),B22=QUARTILE(B3:B20,2),B23=QUARTILE(B3:B20,3),B24=(B23+B21-2*B22)/(B23-B21),23,偏度：,Spearman,偏度系数,日期,价格,20050301,6.4,20050302,6.38,20050303,6.44,20050304,6.36,20050307,6.24,20050308,6.35,20050309,6.29,20050310,6.16,20050311,6.12,20050314,6.08,20050315,5.99,20050316,5.93,20050317,5.97,20050318,5.93,20050321,5.94,20050322,5.54,20050323,5.36,20050324,5.4,平均值,6.05,标准差,0.333500,中位数,6.1,Spearman,偏度系数,-0.46,B21=AVERAGE(B3:B20),B22=STDEV(B3:B20),B23=MEDIAN(B3:B20),B24=3*(B21-B23)/B22,24,峰度：,峰值,日期,上证指数,20050301,1,303.41,20050302,1,287.45,20050303,1,294.34,20050304,1,287.71,20050307,1,293.74,20050308,1,318.27,20050309,1,316.79,20050310,1,286.23,20050311,1,289.94,20050314,1,293.50,20050315,1,269.14,20050316,1,255.59,20050317,1,243.47,20050318,1,227.40,20050321,1,231.05,20050322,1,206.92,20050323,1,201.65,20050324,1,208.19,20050325,1,205.63,峰值,-1.36420044,B22=KURT(B3:B21),25,峰度：,矩峰度系数,3,，常峰态，正态分布,3,，高峰态,日期,上证指数,（X,i,-X）,2,（X,i,-X）,4,20050301,1,303.41,1534.87,2355814.24,20050302,1,287.45,539.05,290570.80,20050303,1,294.34,906.45,821658.19,20050304,1,287.71,551.19,303806.92,20050307,1,293.74,870.68,758092.01,20050308,1,318.27,2920.04,8526617.17,20050309,1,316.79,2762.28,7630174.09,20050310,1,286.23,483.88,234143.94,20050311,1,289.94,660.87,436747.56,20050314,1,293.50,856.58,733727.33,20050315,1,269.14,24.08,579.96,20050316,1,255.59,74.70,5579.36,20050317,1,243.47,431.09,185835.89,20050318,1,227.40,1356.64,1840479.55,20050321,1,231.05,1101.09,1212392.67,20050322,1,206.92,3284.74,10789502.01,20050323,1,201.65,3916.59,15339644.14,20050324,1,208.19,3140.78,9864477.36,20050325,1,205.63,3434.27,11794199.64,1,264.23,28849.85,73124042.80,0.000001,B22=AVERAGE(B3:B21),C3=(B3-$B$22)2,D3=(B3-$B$22)4,C22=SUM(D3:D21),D23=D22/18/(C22/18)4,26,Dfunctions(database,field,criteria),database,：数据库单元格区域,field,：字段,criteria,：包含过滤条件的单元格区域,DCOUNT,DCOUNTA,DSUM,DPRODUCT,DMAX,DMIN,DAVERAGE,DVAR,DSTDEV,DVARP,DSTDEVP,DGET,数据库统计函数,27,数据库方差、标准差函数,利用,DVAR,和,DSTDEV,求样本方差标准差,Gold,公司销售清单,销售单号,客户类型,类别,生产商,型号,数量,单价,总价,销售员,040500001,个人,台式电脑,戴尔,G280,2,9000,18000,刘,040500002,单位,台式电脑,惠普,H520,3,10000,30000,王,040500003,个人,笔记本,惠普,H320,1,15000,15000,李,040500004,个人,笔记本,联想,L20,2,12000,24000,刘,040500005,单位,台式电脑,联想,L32,5,8000,40000,王,040500006,单位,台式电脑,联想,L32,2,8000,16000,孙,040500006,个人,台式电脑,戴尔,G280,5,9000,45000,王,040500008,个人,笔记本,戴尔,G500,2,13000,26000,李,040500009,单位,台式电脑,联想,L32,2,8000,16000,李,040500010,个人,笔记本,戴尔,G500,6,13000,78000,王,040500011,单位,台式电脑,联想,L32,5,8000,40000,刘,040500012,个人,笔记本,戴尔,G500,2,13000,26000,王,040500013,个人,台式电脑,惠普,H520,1,10000,10000,刘,040500014,单位,笔记本,惠普,H320,5,15000,75000,刘,040500015,单位,台式电脑,联想,L32,6,8000,48000,刘,总价,样本方差,25000,374250000,=DVAR(A4:I19,H4,A21:A22),样本标准差,19345.54212,=DSTDEV(A4:I19,H4,A21:A22),28,统计指数,：,同等加权指数,同等加权指数,消费品,价格（元）,销量,期初,期末,期初,期末,大米,500,540,300,330,鸡蛋,5,5.8,6000,6450,盐,7,7.3,150,155,布匹,80,85,600,670,电视机,3000,3100,80,83,书,20,22,500,560,同等加权消费价格指数,104.10%,=SUM(C4:C9)/SUM(B4:B9),同等加权消费数量指数,108.10%,=SUM(E4:E9)/SUM(D4:D9),29,统计指数,：,拉氏指数,基期加权（拉氏）指数,消费品,价格（元）,销量,期初,P,0,期末,P,1,期初,Q,0,期末,Q,1,大米,500,540,300,330,鸡蛋,5,5.8,6000,6450,盐,7,7.3,150,155,布匹,80,85,600,670,电视机,3000,3100,80,83,书,20,22,500,560,P,0,*Q,0,P,1,*Q,0,P,0,*Q,1,大米,150000,=B4*D4,162000,=C4*D4,165000,=B4*E4,鸡蛋,30000,34800,32250,盐,1050,1095,1085,布匹,48000,51000,53600,电视机,240000,248000,249000,书,10000,11000,11200,479050,=SUM(B12:B17),507895,=SUM(D12:D17),512135,=SUM(F12:F17),拉氏消费价格指数,106.02%,=D18/B18,拉氏消费数量指数,106.91%,=F18/B18,30,统计指数,：,帕氏指数,现期加权（帕氏）指数,消费品,价格（元）,销量,期初,P,0,期末,P,1,期初,Q,0,期末,Q,1,大米,500,540,300,330,鸡蛋,5,5.8,6000,6450,盐,7,7.3,150,155,布匹,80,85,600,670,电视机,3000,3100,80,83,书,20,22,500,560,P,1,*Q,0,P,0,*Q,1,P,1,*Q,1,大米,162000,=C4*D4,165000,=B4*E4,178200,=C4*E4,鸡蛋,34800,32250,37410,盐,1095,1085,1131.5,布匹,51000,53600,56950,电视机,248000,249000,257300,书,11000,11200,12320,507895,=SUM(B12:B17),512135,=SUM(D12:D17),543311.5,=SUM(F12:F17),帕氏消费价格指数,106.97%,=F18/B18,帕氏消费数量指数,106.09%,=F18/D18,31,统计指数,：,埃奇沃斯指数,32,统计指数,：,费雪指数,33,统计指数,：,资本加权指数,资本加权指数,股票名称,价格（元）,发行在外的流通股股数,(,万股,),期初,P,0,期末,P,1,期初,S,0,期末,S,1,A,10.2,13.1,3000,3300,B,6.5,7.1,2000,3000,C,32.2,37.2,6000,70000,D,13.5,15.7,2500,2650,E,9.8,11,3100,3500,F,25.4,26.3,5200,7000,G,10.1,11.8,3300,3500,H,9.2,7.8,1500,1600,I,12.5,14,2800,3000,I,0,100,P,0,*S,0,P,1,*S,1,A,30600,=B4*D4,43230,=C4*E4,B,13000,21300,C,193200,2604000,D,33750,41605,E,30380,38500,F,132080,184100,G,33330,41300,H,13800,12480,I,35000,42000,515140,=SUM(B16:B24),3028515,=SUM(D16:D24),股票资本加权指数,587.90,=D25/B25*B14,34,某事件发生的概率为,p,，不发生的概率为,q,，则在,N,次实验中该事件发生,X,次的概率为,又称伯努利分布,BINOMDIST(number,trials,probability,cumulative),number,：实验成功的次数,trials,：独立实验的次数,probability,：每次独立实验成功的概率,cumulative,：,true,返回累积分布，,false,返回密度函数,概率分布：,二项分布,35,股票上涨的概率为0.6，20个交易日中上涨5天的概率，以及上涨小于等于5天的概率,概率分布：,二项分布,应用二项分布函数,BINOMDIST,求概率值,N,20,X,5,p,0.6,上涨,5,天,p(X),0.001294,=BINOMDIST(C3,C2,C4,0),上涨小于,5,天,F(X5),0.001612,=BINOMDIST(C3,C2,C4,1),36,股票上涨的概率为,0.6,，求,20,个交易日中概率分布函数大于等于临界值,0.75,的最小天数,CRITBINOM(,试验次数,每次试验的成功概率,临界值,),概率分布：,二项分布,应用二项分布函数,CRITBINOM,函数,N,20,p,0.6,0.75,二项分布的分布函数值大于等于,的最小,X,14,=CRITBINOM(C2,C3,C4),37,在,r,次成功之前失败的次数,X,的概率,(,帕斯卡分布,),NEGBINOMDIST(,失败次数,成功次数,成功概率,),灯泡合格概率,0.8,求找到,30,个合格灯泡之前，需要测试,10,个不合格灯泡的概率,概率分布：,负二项分布,应用负二项分布函数,NEGBINOMDIST,求概率值,p,0.8,r,30,X,10,测试,30,个合格之前须测试,10,个不合格的概率,0.08059,=NEGBINOMDIST(C4,C3,C2),38,25(N),种股票，,15(M),只沪市，,10(N-M),只深市，从中选,10(n),种，其中有,5(x),只属于沪市的概率,HYPGEODIST(,成功次数,样本数,总样本成功次数,总样本数,),概率分布：,超几何分布,应用超几何分布函数,HYPGEOMDIST,求概率,N,25,M,15,n,10,x,5,10,只股票中,5,只为上海交易所的概率,0.231512,=HYPGEOMDIST(E3,C3,E2,C2),39,随机事件发生的次数,POISSON(,事件数,期望值,返回的概率分布形式,),平均每天呼叫,20,次，一天收到,15,次和小于等于,15,次呼叫的概率,概率分布：,泊松分布,20,x,15,收到,15,次呼叫的概率,0.051649,=POISSON(E2,C2,0),收到小于等于,15,此呼叫的概率,0.156513,=POISSON(E2,C2,1),40,股票收益率呈正态分布，均值为,5%,，标准差为,2%,，求收益率为,4%,对应的概率密度函数值,概率分布：,正态分布,NORMDIST(x,均值,标准差,返回的概率分布形式,),正态分布函数,5.00%,0.02,4%,的收益率对应概率密度函数值,17.60327,=NORMDIST(0.04,C2,E2,0),小于等于,4%,的收益率概率,0.308538,=NORMDIST(0.04,C2,E2,1),41,股票收益率,80%,的可能性不超过某值，求这个值,概率分布：,正态分布,NORMSINV(,概率,均值,标准差,),正态分布函数,5.00%,0.02,80%,的概率下对应的临界值,0.066832,=NORMINV(0.8,C2,E2),42,概率分布：,正态分布,43,概率分布：,正态分布,44,45,一、计算机准备,二、分析工具库提供的统计分析方法,三、,数据基本信息计算,四、图表制作,五、假设检验,（一）,t,检验,1.,成对,2.,成组（二）,U,检验（,z,检验）,六、方差分析（一）单因素,（二）两因素,1,、无重复,2,、有重复,七、回归与相关,Excel,在统计分析中应用,45,一、计算机准备,1,、启动,Excel,，,检查,“,工具,”菜单中是否有“,数据分析,”命令。如果没有发现“数据分析”命令，就表示未加载“分析工具库”。,2,、加载“分析工具库”,。,步骤,：,工具 加载宏,分析工具库,确定,工具 数据分析 进入统计分析功能,46,单向分类资料的方差分析,两向分组单独观测值试验资料的方差分析,两向分组有重复观测值资料的方差分析,描述性统计量计算,二总体方差的同质性检验,编制次数分布表及绘制统计图,二、,分析工具库提供的统计分析方法,47,排位与百分比排位,两样本平均数,u,检验,配对资料的,t,检验,等方差非配对资料的,t,检验,异方差非配对资料的,t,检验,一元线性回归与多元线性回归分析,计算多个变量两两之间的相关系数及协方差,进行随机和顺序抽样,48,三、数据基本信息计算,（一）统计分析的粘贴函数,AVERAGE,计算算术平均值,BINOMDIST,计算二项式分布的概率值,CHIDIST,计算特定,2,分布的单尾概率值,CHIINV,计算一定单尾概率值时的,2,临界值,CHITEST,计算独立性检验的,2,值,CONFIDENCE,计算总体平均值的置信区间,CORREL,计算两组数据的相关系数,COVAR,计算两组数据的协方差,49,FDIST,计算特定,F,分布的单尾概率值,FINV,计算一定概率时的临界,F,值,FTEST,计算二个样本方差之比,F,值的概率,GEOMEAN,计算几何平均数,HARMEAN,计算调和平均数,INTERCEPT,计算直线回归的截距,MAX,计算最大值,MEDIAN,计算一组给定数字的中位数,MIN,计算最小值,50,MODE,计算一组数据的众数,NORMDIST,计算正态分布的累积函数,NORMINV,计算正态分布累积函数的逆函数,NORMSDIST,计算标准正态分布的累积函数,NORMSINV,计算标准正态分布累积函数的逆函数,POISSON,计算泊松分布的概率,SLOPE,计算给定数据的直线回归系数,STDEV,计算样本标准差,STDEVP,计算样本总体的标准差,51,TDIST,计算学生氏,-,t,分布的概率值,TINV,计算特定概率时学生氏,-,t,分布的临界,t,值,TTEST,计算,t,检验时的学生氏,-,t,检验相关的概率,VAR,计算样本的方差,VARP,计算样本总体的方差,ZTEST,计算,Z,检验的双尾概率值,52,53,（二）利用粘贴函数计算方法例举,1,、,算术平均数,。,=AVERAGE(,数值区域,),2,、,几何平均数,。,=GEOMEAN(,数据区域,),3,、,中数,。,=MEDIAN(,数据区域,),4,、,极差,。最大值,=MAX(,数据区域,),；最小值,=MIN(,数据,区域,),。极差,=,最大值,-,最小值,5,、,方差,。,=VARP(,数据区域,),6,、,标准差,。,=STDEVP(,数据区域,),7,、,变异系数,。标准差,/,算术平均数,53,（三）描述统计,Descriptive Statistics,54,55,56,57,四、图表制作,1,、输入原始数据和各组下限,【,可用功能：,=MIN()=MAX()】,2,、,工具 数据分析 直方图,3,、输入区域：原始数据,接受区域：各组下限,输出区域：图标位置,图表输出,4,、设定图表选项：,图表类型,；表题；坐标；选项等,57,342.1,340.7,348.4,346.0,343.4,342.7,346.0,341.1,344.0,348.0,346.3,346.0,340.3,344.2,342.2,344.1,345.0,340.5,344.2,344.0,343.5,344.2,342.6,343.7,345.5,339.3,350.2,337.3,345.3,358.2,344.2,345.8,331.2,342.1,342.4,340.5,350.0,343.2,347.0,340.2,344.0,353.3,340.2,336.3,348.9,340.2,356.1,346.0,345.6,346.2,340.6,339.7,342.3,352.8,342.6,350.3,348.5,344.0,350.0,335.1,340.3,338.2,345.5,345.6,349.0,336.7,342.0,338.4,343.9,343.7,341.1,347.1,342.5,350.0,343.5,345.6,345.0,348.6,344.2,341.1,346.8,350.2,339.9,346.6,339.9,344.3,346.2,338.0,341.1,347.3,347.2,339.8,344.4,347.2,341.0,341.0,343.3,342.3,339.5,343.0,表,4,1 100,听罐头样品的净重（,g,）,58,18,29,19,24,22,19,24,22,22,20,23,20,21,23,21,26,22,23,24,22,23,24,25,24,22,24,23,24,22,25,23,25,26,23,22,25,23,20,22,25,26,25,26,26,25,26,24,23,21,26,21,23,22,24,24,21,23,24,24,21,22,23,20,22,23,26,23,24,22,24,26,28,24,27,23,24,22,26,23,20,26,25,25,26,25,25,26,25,24,22,25,26,25,24,25,26,25,25,27,28,表,4-2 100,盒鲜枣每盒检出不合格枣数,59,【,例,5-1】,为了分析某种新型减肥药剂是否对人具有显著减肥作用，现随机选取,12,位自愿者进行试验，服药后，间隔,1,个疗程，分别测其,12,位自愿者的体重见下表，假设服药前后，除服用此减肥药剂外，其余的生活方式、生活条件均未变化。试问根据此试验结果，能否判断这种新型减肥药对人具有显著减肥作用。,自愿者编号,1,2,3,4,5,6,7,8,9,10,11,12,服药前体重,x,133,145,100,156,127,122,142,110,136,166,138,110,1,个疗程后体重,y,120,122,105,133,108,110,135,105,122,145,128,108,五、假设检验,Hypothesis Testing,(,一,)t,检验,1,、成对数据资料的平均数,t,检验,60,61,61,工具 数据分析,t-,检验：平均值的成对二样本分析,变量,1,区域：,B,2:,B,14,变量,2,区域：,C,2:,C,14,假设平均值：,0,标志,：,：,0.05,输出区域：,D,10,确定,61,62,63,64,65,结果分析,:,所以否定,H,0,，,接受,H,A,即：服用新型减肥药剂的自愿者体重有极显著差异。,因此可得出结论：这种新型减肥药剂对人具有减肥作用，效果极显著。,66,【,例,5-2】,某番茄品种分别按,3200,株,/667m,2,种植，各调查,5,个小区，得产量见下表，试测验两种密度的小麦产量有无显著差异。,x,1,x,2,500,530,535,550,525,545,520,555,550,520,67,2,、成组数据资料的平均数,t,检验,67,68,工具 数据分析,t,检验：双样本等方差检验,(t,检验：双样本异方差检验,),变量,1,区域：,A,1:,A,6,变量,2,区域：,B,1:,C,6,假设平均值：,0,标志,：,：,0.05,确定,68,69,t-,检验,:,双样本等方差假设,x1,x2,平均,526,540,方差,342.5,212.5,观测值,5,5,合并方差,277.5,假设平均差,0,df,8,t Stat,-1.3288212,P(T=t),单尾,0.1102784,t,单尾临界,1.85954803,P(T=t),双尾,0.2205568,t,双尾临界,2.30600413,结果分析：,t=1.33,t,0.05,=2.31,tt,0.05,接受,H,o,即：两种密度小区产量无差异。,69,【,例,5-3】,现有两种茶多糖提取工艺，分别从两种工艺中各取,1,个随机样本来测定其粗提物中的茶多糖含量，结果见表,4,。问两种工艺的粗提物中茶多糖含量有无显著差异？,表,5-3,两种工艺粗提物中茶多糖含量测定结果,双样本假设检验可先检验两个样本的方差是否相等，用,F,检验,法；然后再选择检验方法,利用,Excel,