常用分布概率计算的Excel应用

1、上机实习常用分布概率计算的Excel应用利用Excel中的统计函数工具，可以计算二项分布、泊松分布、正态分布等常用概率分布的概率值、累积(分布)概率等。这里我们主要介绍如何用Excel来计算二项分布的概率值与累积概率，其他常用分布的概率计算等处理与此类似。§3.1二项分布的概率计算一、二项分布的(累积)概率值计算用Excel来计算二项分布的概率值Pn(k)、累积概率Fn(k),需要用BINOMDIST1数，其格式为：BINOMDIST(number_s,trials,probability_s,cumulative)其中number_s:试验成功的次数k;trials:独立试验的总次

2、数n;probability_s:一次试验中成功的概率p;cumulative:为一逻辑值，若取0或FALSE时，计算概率值Pn(k);若取1或TRUE寸，则计算累积概率Fn(k),。即对二项分布B(n,p)的概率值Pn(k)和累积概率Fn(k),有Pn(k)=BINOMDIST(k,n,p,0);Fn(k)=BINOMDIST(k,n,p,1)现结合下列机床维修问题的概率计算来稀疏现象(小概率事件)发生次数说明计算二项分布概率的具体步骤。例3.1某车间有各自独立运行的机床若干台，设每台机床发生故障的概率为0.01,每台机床的故障需要一名维修工来排除，试求在下列两种情形下机床发生故障而得不到及

3、时维修的概率：(1) 一人负责15台机床的维修；(2) 3人共同负责80台机床的维修。原解：(1)依题意，维修人员是否能及时维修机床，取决于同一时刻发生故障的机床数。设X表示15台机床中同一时刻发生故障的台数，则X服从n=15,p=0.01的二项分布：XB(15,0.01),而P(X=k)=Ci5k(0.01)k(0.99)15-k,k=0,1,15故所求概率为P(X>2)=1-P(XW1)=1-P(X=0)-P(X=1)=1-(0.99)15-15X0.01X(0.99)14=1-0.8600-0.1303=0.0097(2)当3人共同负责80台机床的维修时，设Y表示80台机床中同一时

4、刻发生故障的台数，则Y服从n=80、p=0.01的二项分布，即丫B(80,0.01)此时因为n=80>30,p=0.01<0.2所以可以利用泊松近似公式：当n很大，p较小时(一般只要n>30,pW0.2时)，对任一确定的k,有(其中A=np)ckkn*Cnpq-ek!来计算。由?=np=80X0.01=0.8,利用泊松分布表，所求概率为8080k'、C80(0.01)k(0.99)*'、(l8Le©8P(Y>4)=TTk!=0.0091我们发现，虽然第二种情况平均每人需维修27台，比第一种情况增加了80%的工作量，但是其管理质量反而提高了。Ex

5、cel求解：已知15台机床中同一时刻发生故障的台数XB(n,p),其中n=15,p=0.01,则所求概率为P(X>2)=1-P(X&1)=1-P(X=0)-P(X=1)=1-P15(0)-P15(1)利用Excel计算概率值P15(1)的步骤为：(一)函数法：在单元格中或工作表上方编辑栏中输入“=BINOMDIST(1,15,0.01,0)”后回车，选定单元格即出现P15(1)的概率为0.130312(图3-1)。图3-1直接输入函数公式的结果(函数法)(二)菜单法：1 .点击图标“fx”或选择“插入”下拉菜单的“函数”子菜单，即进入“函数”对话框(图3-2);2 .在函数对话框

6、中，“函数分类”中选择“统计”，“函数名字”中选定“BINOMDIST,再单击“确定”；(图3-2)图3-2“插入”下的“函数”对话框2.进入“BINOMDIST对话中g(图3-3),对选项输入适当的值：在Number_s窗口输入：1（试验成功的次数k）;在Trials窗口输入：15（独立试验的总次数n）;在Probability_s窗口输入：0.01（一次试验中成功的概率p）;在Cumulative窗口输入：0（或FALSE表明选定概率值Pn（k）;图3-3"BINOMDIST对话框4.最后单击“确定”，相应单元格中就出现Pi5（1）的概率0.130312。类似地若要求Pi5（0）

7、的概率值，只需直接输入“=BINOMDIST（0,15,0.01,0）”或利用菜单法，在其第3步选项Number_s窗口输入0,即可得概率值0.860058,则P（X>2）=1-Pi5（0）-Pi5（1）=1-0.860058-0.130312=0.00963。另外，P（X>2）=1-P（X<1）=1-Fi5（1）,即也可以通过先求累积概率Fi5（1）来求解。而要求出Fi5（1）的值，只需在单元格上直接输入“=BINOMDIST（1,15,0.01,1）”回车即可；或利用上述菜单法步骤，在第3步的选项Cumulative窗口输入：1,即得到累积概率Fi5（1）的值0.9903

8、7,故有P（X>2）=1-P（XW1）=1-Fi5（1）=1-0.99037=0.00963。对于例3.1,Y表示80台机床中同一时刻发生故障的台数，则Y服从n=80、p=0.01的二项分布，即YB（80,0.01）。所求概率为P（丫>4）=1-P（丫<3）=1-F80（3）利用Excel,在单元格上直接输入“=BINOMDIST（3,80,0.01,1）”回车或与上述菜单法类似操作可得累积概率F80（3）=0.991341,故所求概率的精确值为P（Y>4）=1-P（Y<3）=1-F80（3）=1-0.991341=0.00866。（注意：例3.1原解中的结果是泊

9、松近似值）对于泊松分布、正态分布、指数分布等的概率计算步骤与上述二项分布的概率计算过程类似，只需利用函数法正确输入相应分布的函数表达式即得结果；或在菜单法的第2步选择POISSON、NORMDIST、EXPONDIST等函数名，根据第3步对话框的指导输入相应的值即可。下面我们列出这些常用分布的统计函数及其应用。§ 3.2 泊松分布的概率计算、泊松分布的（累积）概率值计算在Excel中，我们用POISSON函数去计算泊松分布的概率值和累积概率值。其格式为:POISSORx,meancumulative)其中x:事件数；Mean：期望值即参数人。Cumulative:为逻辑值，若取值为1

10、或TRUE,则计算累积概率值P(X<x),若取值为0或FALSE,则计算随机事件发生的次数恰为x的概率值P(X=x)。即对服从参数为九的泊松分布的概率值P(X=k)和累积概率值P(XWk),有P(X=k)=POISSON(k,九0)；P(X<k)=POISSON(k,%1)。例如，在例3.1(2)的原解的泊松近似计算中，丫近似服从炉np=80X0.01=0.8的泊松分布P(»,需求P(Y>4)o则在Excel中，利用函数POISSON(3,0.8,1)就可得到累积概率分布P(YW3)的值0.99092,则所求概率为P(Y>4)=1-P(Y&3)=1-0

11、.99092=0.00908。§ 3.3 正态分布的概率计算一、NORMDIS函数计算正态分布N(此仃2)的分布函数值F(x)和密度值f(x)在Excel中，用函数NORMDIS计算给定均值R和标准差仃的正态分布N(也仃2)的分布函数值F(x)=P(XWx)和概率密度函数值f(x)。其格式为：NORMDIS(ix,mean,standard_dev,cumulative)其中x:为需要计算其分布的数值；Mean:正态分布的均值也standard_dev:正态分布的标准差仃；cumulative:为一逻辑值，指明函数的形式。如果取为1或TRUE则计算分布函数F(x)=P(X<x)

12、;如果取为0或FALSE计算密度函数f(x)。即对正态分布N(Ra)的分布函数值F(x)和密度函数值f(x),有F(x)=NORMDIST(x,R口1);f(x)=NORMDIST(x,N,。0)说明：如果mean=0且standard_dev=1,函数NORMDIST各计算标准正态分布N(0,1)的分布函数G(x)和密度或x)。Excel求解例3.2(1):对零件直径XN(135,52),应求概率P(130<X<150)=F(150)-F(130)在Excel中，输入“=NORMDIST(150,135,5,1)”即可得到(累积)分布函数F(150)的值“0.998650”，或用

13、菜单法进入函数“NORMDIST对话框，输入相应的值(见图3-4)即可得同样结果。HO期口工.XJ=NOR1DI£T05O,135,5,1)BOEMBIST=0.93B630033返回正嘉分布函数值.逻辑值决定函数的泰式.果积分布函数，使用TRUE；恨率密度函费,使用除画i+苴结果匚0.990630033|确定|取消图3-4"NORMDIST对话框再输入“=NORMDIST(130,135,5,1)”(或菜单法)得到F(130)的值“0.158655”，故P(130<X<150)=F(150)-F(130)=0.998650-0.158655=0.839995。

14、二、NORMSDIST数计算标准正态分布N(0,1)的分布函数值(x)函数NORMSDIST用于计算标准正态分布N(0,1)的(累积)分布函数6(x)的值，该分布的均值为0,标准差为1,该函数计算可代替书后附表所附的标准正态分布表。其格式为NORMSDISTZ)其中z:为需要计算其分布的数值。即对标准正态分布N(0,1)的分布函数G(x),有(x)=NORMSDIST(x)。例3.3设ZN(0,1),试求P(-2WZW2)。则输入“=NORMSDIST(2)可得(2)的值“0.97724994"，输入“=NORMSDIST(-2)可得(-2)的值“0.02275006”，故P(-2W

15、ZW2)=6(2)-(-2)=0.97724994-0.02275006=0.95449988。三、NORMSINV数计算标准正态分布N(0,1)的分位数函数NORMSIN用于计算标准正态分布N(0,1)的(累积)分布函数的逆函数G-1(p)。即已知概率值6(x)=p,由NORMSINV(p就可以得到x(=G-1(p)的值，该x就是对应于p=1-a的标准正态分布N(0,1)分位数Z1-国函数NORMSINVJ格式为NORMSINV(probability)其中probability:标准正态分布的概率值p。则对标准正态分布N(0,1)的分位数Zq有Z迁NORMSINV(1-a)。Excel求解

16、例3.2(2):在例3.2(2)原解的计算中，已求得中岛=0.9仃，则由Excel中，NORMSINV(0.9)=1.281551,得55=1.281551仃，故a=5/1.281551=3.901522。§ 3.4 数分布的概率计算一、指数分布分布函数值和密度值的计算在Excel中，函数EXPONDIS碉于计算指数分布的(累积)分布函数值F(x)和概率密度函数值f(x)。其格式为：EXPONDIST(x,lambda,cumulative)其中为需要计算其分布的数值;Lambda:指数分布的参数值入。Cumulative:为逻辑值，指定函数形式。若取1或TRUE将计算分布函数F(x

17、);若取0或FALSE,则计算密度函数f(x)。即对指数分布的分布函数值F(x)和密度函数值f(x),有F(x)=EXPONDIST(x,九1);f(x)=EXPONDIST(x,九,0)Excel求解例3.4:因X服从九二1/1000=0.001的指数分布，由EXPONDIST(1000,0.001,1)可得分布函数F(1000)=P(Xw1000)的概率值0.632121,故所求的概率为P(X>1000)=1-P(X<1000)=1-F(1000)=1-0.632121=0.367879。§ 3.5 分布的概率计算一、CHIDIST函数计算必分布的概率值在Excel中

18、CHIDIST函数用于计算,分布的单侧概率值a=P(/>x)。其格式为CHIDIST(x,deg_freedom)其中：x用来计算片分布单侧(尾)概率的数值。Deg_freedom,分布的自由度n。说明：如果参数deg_freedom不是整数，将被截尾取整。即对/(n)分布单侧概率值P(72>x),有P(Z2(n)>x)=CHIDIST(x,n)。例如已知/(15),要计算P(片>20)的概率值，则只要在Excel中，输入函数“二CHIDIST(20,15)”即可得到所求值0.1719327。即P(Z2>20)=0.1719327。二、CHIINV函数计算，分布的

19、上侧a分位数CHIINV函数用于计算,分布的上侧口分位数n)，也就是计算单侧概率的CHIDIST函数的逆函数，即如果a=CHIDIST(x,n),则CHIINV(a,n)=x。该函数的计算可代替概率统计书后所附的胃分布表。其格式为CHIINV(a,deg_freedom)其中a为片分布的单侧概率aoDeg_freedom,分布的自由度n。说明：如果参数deg_freedom不是整数，将被截尾取整。即对,分布的上侧a分位数72n),有,加)二CHIINV(a,n)。例如，对a=0.05,n=10时，要求上侧a分位数左.。)的值，只要在Excel中输入“二CHIINV(0.05,50)”即可得到“

20、18.307029”，即胃0.05(1。)=18.307029。§ 3.6 t分布的概率计算、TDIST函数计算t分布的概率值在Excel中TDIST函数用于计算t分布的单侧概率值:二P(t>x)和双侧概率值a=P(|t|>x)。其格式为TDIST(x,deg_freedom,tails)其中x为需要计算t分布的数字。deg_freedomt分布的自由度n。tails指明计算的概率值是单侧还是双侧的。若tails=1计算单侧概率值a=P(t>x);若tails=2,则计算双侧概率值ot=P(|t|>x)。说明参数deg_freedom和tails不是整数时将被

21、截尾取整。即对t(n)分布的单侧概率值P(t>x)和双侧概率值P(|t|>x),有P(t(n)>x)=TDIST(x,n,1);P(|t(n)|>x)=TDIST(x,n,2)。例如：要计算P(|t(60)|>2)的概率值，用“TDIST(2,60,2)”即得0.050033。即P(|t(60)|>2)=0.050033。二、TINV函数计算t分布双侧a分位数TINV函数用于计算t分布的满足P(|t|>t凄(n)尸a(即P(t>t3(n)=a/2)的双侧a分位数t(n),也就是计算双侧概率值函数TDIST(a,n,2)的逆函数，即如果a=TDIS

22、T(x,n,2),则TINV(a,n)=x。该函数的计算可代替书后t分布表(附表6)。其格式为TINV(a,deg_freedom)其中a为对应于t分布的双侧概率值；Deg_freedom为t分布的自由度n。说明：如果deg_freedom不为整数时将被截尾取整。注意，函数TINV(a,n)的值是t/2(n),如果需要计算t分布的上侧a分位数tn),应由“=TINV(2*an)”得到，即t.(n)=TINV(2:,n)例如，对n=10时，t0.025(10)可由“=TINV(0.05,10)”得，其值为2.228139;而t0.05(10)应由“=TINV(0.05*2,10)”得，其值为1.

23、812462。对a=0.05,n=50时，t0.05(50)由“=TINV(0.05*2,50)”得，其值为1.675905。而TINV(0.05,50)=2.00856,是t0.025(50)(=Z0.025=1.96)的值。§ 3.7 F分布的概率计算一、FDIST函数计算F分布的概率值在Excel中FDIST函数用于计算F分布的单侧概率值a=P(F>x)0其格式为FDIST(x,deg_freedom1,deg_freedom2)其中：x用来计算F分布单侧概率的数值；Deg_freedom1F分布的第一(分子)自由度n1;Deg_freedom2F分布的第二(分母)自由度

24、n2°说明：如果参数deg_freedom1或deg_freedom2不是整数，将被截尾取整。即对F(ni,n2)分布的单侧概率值PF(ni,n2)>x,有PF(ni,n2)>x=FDIST(x,ni,n2)。例如，对FF(10,5)，需求概率值P(F>0.3),则在Excel中由“=FDIST(0.3,10,5)得0.950303,故P(F(10,5)>0.3)=0.950303。二、FINV函数计算F分布的上侧a分位数FINV函数用于计算F分布的上侧a分位数F/n1,n2),也就是计算单侧概率的FDIST函数的逆函数，即如果o(=FDIST(x,n1,n2

25、),则FINV(a,n1,n2)=xoFINV函数的计算可代替书后所附的F分布表。其格式为FINV(a,deg_freedom1,deg_freedom2)其中a对应于F分布的单侧概率值；Deg_freedom1F分布的第一(分子)自由度m;Deg_freedom2F分布的第二(分母)自由度n2。说明：如果deg_freedom1或deg_freedom2不是整数，将被截尾取整。即对F分布的上侧1a分位数F&n1,n2),有FJn1,n2)=FINV(a,n1,n2)o例如，对a=0.05,Fo.05(10,5)可由“=FINV(0.05,10,5)”得，其值为4.735057;而F0

26、.05(5,10)则由“=FINV(0.05,5,10)”得，其值为3.325837。另外，Fo.95(10,5)可由“=FINV(0.95,10,5)”直接求得，其值为0.300677。最后我们给出Excel中常用连续型分布统计函数的简明意义对照表，供查阅。分布Excel统计函数对应概率值Excel统计函数对应分位数正态分布N(Rtf)NORMDIST(x,R50)NORMDIST(x,14q1)正态密度f(x)P(X<x)=F(x)NORMINV(p,R0X1-p=F-1(p)标准正态分布N(0,1)NORMSDIST(x)PZ<x=(x)NORMSINV(p)Z1-p(3(p

27、)罡分布n)CHIDIST(x,n)P2(n)>xCHIINV(qn)/n)T分布t(n)TDIST(x,n,1)Pt(n)>xTINV(Hn)td2(n)TDIST(x,n,2)P|t(n)|>xTINV(Ot*2,n)tdn)F分布F(n1,n2)FDIST(x,n1,n2)PF(n1,n2)>xFINV(a,m,n2)F&n1,n2)上机训练题三1.一电子仪器由200个元件构成，每一元件在一年的工作期内发生故障的概率为0.001。设各元件是否发生故障是相互独立的，且只要有一元件发生故障，仪器就不能正常工作。利用Excel中的统计函数来求：(1)仪器正常工作

28、一年以上的概率；(2) 一年内有2个以上(>2)元件发生故障的概率。2 .已知X服从九=4的泊松分布P(K),试用Excel求P(X<6)。3 .已知XN(1.5,22),试用Excel中的统计函数来求：(1)P(2<"2.5);(2)P(E<5);(3)P(|X-1.5|>2)4.利用Excel中的统计函数来计算下列各值(1) 20.99(10),比0.90(12),20.01(60),0.05(16);(2) t0.90(4),t0.01(10),t0.05(12),t0.025(60)；(3) Fo.0i(10,9),Fo.05(10,9),Fo.

29、90(28,2),Fo.95(10,8)。5,用Excel求以下各分布的概率值(1) P(/(21)>10);P(片(21)<15);(2) P(t(4)>3);P(|t(4)|1.5);(3) P(F(4,12)<5);P(F(4,12)>3)。上机实习四用Excel求正态总体参数的置信区间首先我们列出求解单个总体常用参数的置信区间简要结果表，可供查阅。表4-1单个总体参数的100（10%置信区间总体参数条件100(1ot)%置信区间正态分布均值2仃已知(JX±Za/2<n02小-,SX±ta/2Tn2仃未知（大样本n>30）SX

30、±Za/2-nn方差2CT22,(n-1)S2(n-1)S2、(丁2,丁2)及寒，ix标准差口的A学I1，口未知（大样本n>30）s+z3s32后卜面讨论用Excel软件来求正态总体的总体均值和方差的常用置信区间问题。§4.1用Excel求。2已知时总体均值的置信区间总体方差苦已知时，求总体均值N的100（16的置信区间公式为:X一乙2n（XZ/2,XZ/2）即Vn7n。例4.1设某药厂生产的某种药片直径X是一随机变量，服从方差为0.82的正态分布。现从某日生产的药片中随机抽取9片，测得其直径分别为（单位：mm）14.1,14.7,14.7,14.4,14.6,14.

31、5,14.5,14.8,14.2,试求该药片直径的均值N的95%置信区间。解：对药片直径X,已知X服从N(N,0.82)o对于1口=0.95,则口=0.05,查标准正态分布分位数表得临界值Z：/2=Z0.025=1.96,又已知仃=0.8,n=9,故0.8X_Z/2=14.5_1.9614.5_1.960.08=14.5_0.52n9所以，该药片直径的均值N的95%置信区间为(13.98,15.02)。在Excel中，利用样本均值函数AVERAGE和置信区域函数CONFIDENC就可以分别得Z-/2到X和3n的值，由此即可得到置信区间的上、下限。其中统计函数AVERAGE和CONFIDENC的

32、格式分另1J为:AVERAG(Bumber1,number2,.)其中Number1,number2,.返回参数平均值(算术平均值)xCONFIDENqalpha,st_dev,size)要计算平均值的130个参数。参数可以是具体数字，或者是涉及数字的名称、数据范围或引用。,返回总体均值的置信区域，即样本均值任意一侧乙/2二的区域大小Vn。其中alpha显著水平«,对应的置彳t度等于100*(1-a)%,亦即，如果alpha为0.05,则置信度为95%。st_dev数据区域的总体标准差。，假设为已知。size样本容量n。现以例4.1的求解来说明已知方差¥时，用Excel构造

33、总体均值的置信区间的具体步骤。Excel求解例4.1:为构造例4.1所求的置信区间，我们在工作表中输入下列内容：A列输入例4.1的样本数据；C列输入指标名称；D列输入计算公式即可得到所需估计的95%置信区间上、下限(见图4-1)。由图4-1中计算结果知，所求药片直径均值的95%置信区间为(13.98,15.02)。F8二=ECDE.F-14.1计算指标计算公式甘算结果14J样本均值=AVERAGE14.514,7置咕区域-CONFIDENCE(0.05f0,8r9)522666314.4置信F艮=F2.F3巧.9音冽56T8T1014.6置信上限=F2+F315.022&&M.

34、5.14.SI114.2图4-1说明：(1)在图4-1中，F列为D列的计算显示结果，当输入完公式后，回车即显示出F列结果，这里只是为了看清公式，才给出了D列的公式形式。(2)对于不同的样本数据，只要输入新的样本数据，再对D列公式中的样本数据区域相应修改，置信区间就会自动给出。如果需要不同的置信水平，只需改变置信区域函数CONFIDENC的相应数值即可。§4.2用Excel求。2未知时总体均值的置信区间总体方差C2未知时，求总体均值N的100（1a）%的置信区间公式为：SSSX二t-/2（X-t./2,X-t./2）vn即vnVn。例4.2设有一组共12例儿童的每100ml血所含钙的实

35、测数据为（单位：微克）：54.8,72.3,53.6,64.7,43.6,58.3,63.0,49.6,66.2,52.5,61.2,69.9,已知该含钙量服从正态分布，试求该组儿童的每100ml血平均含钙量的90%置信区间。解：由实测数据的计算可得到：-1XXini土=59.14,nciCXi2nX2)2S2=n-1ij=74.15,S=S=8.61又对于1-a=0.90,o=0.1,而自由度n-1=11，查t分布表得临界值t：/2(n-1)=10.05(11)=1.796S8.61X_t.y2=59.14.1.79659.14_4.46则一n12故所求平均含钙量的90%置信区间为(54.6

36、8,63.6)。在Excel中，利用“数据分析”菜单的“描述统计”计算结果中“平均”和“置信度”Str./2就可分别得到x和vn的值，由此即可得到所求置信区间。Excel求解例4.1:现以例4.1的求解来说明求置信区间的具体操作步骤：1 .输入数据：将例4.1样本数据输入到工作表中的A1:A12（见图4-3）;2 .在菜单中选取“工具一数据分析一描述统计”，点击“确定”；3 .当出现“描述统计”对话框后，指定参数：（图4-2）在“输入区域”方框内键入A1:A12;在“分组方式”圆点内选择逐列；在“输出选项”中选择“输出区域”为C1;选定“汇总统计”；选定“平均数置信度”，并将置信度改为“90”

37、%6.点击“确定”。如下列图4-2所示图4-2由此即可得到样本数据的描述性统计量的结果，如图JM5FSd=D3-DL64-3所示56789101112131415ABC.B.EV54.8Ml72.355.6平均：5%141仃置信上限闻-I-<7桶隹误差工4通ES3置信下限63.605T43.6币值_的.755S.3./丁#N/A63b准偏差S.611246妆5样本方差7,.15356度值值斜域小峰瑞区最90度和致信求计置-0,6S259-0.1802928.743.672.3709.7124.464302图4-3根据描述统计量计算结果中样本均值(平均)=59.142和置信区间半径(置信度

38、)=4.464,就可得所求平均含钙量的90%置信区间为(59.142-4.464,59.142+4.464)即(54.677,63.606)。§4.3用Excel求正态总体方差02的置信区间根据样本数据，求正态总体方差¥的100(1置信区间公式为_2_2,(n-1)S(n-1)S_(2，2)2其中S2是样本方差，*3泸1-0/2是？2(n-1)分布的双侧临界值。例4.3设某生物寿命服从正态分布，今观察其一组样本寿命，得数据为：(小时)1050,1100,1080,1120,1200,1250,1040,1130,1300,1200,1270,1300试估计该生物寿命的总体方差的90%置信区间。解：由样本数据计算得S2=9127.27,而n=12,对于1ot=0.90,则久=0.10,n-1=11,查片分布表，得临界值胃酸(n-1)=/0.05(11)=19.