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

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

2、rials :独立试验的总次数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台机床中同一时刻发生故障的台数，贝 U X服从 n=15,p=0.01的二项分布：XB(15,0.01),而 P( X= k)= C15k(0.01)k(0.99)15-k,k = 0, 1,，15故所求概率为P(X 2)=1-P(X 30, p=0.01 30,p 4）= Jq Jk!=0.0091我们发现，虽然第二种情况平均每人需维修27台，比第一种

4、情况增加了80%的工作量，但是其管理质量反而提高了。Excel 求解：已知 15台机床中同一时刻发生故障的台数XB（n,p）, 其中 n=15, p=0.01 ,则所求概率为P（X 2）=1-P（X 2)= 1-PI5(0)-PI5(1)=1-0.860058-0.130312=0.00963。另外，P(X 2)=1-P(X 2)=1-P(X 4)=1- P(Y 4)=1- P(Y 3)=1- F80(3)=1-0.991341=0.00866。(注意：例 3.1 原解中的结果是泊松近似值)对于泊松分布、正态分布、指数分布等的概率计算步骤与上述二项分布的概率计算过程类似，只需利用函数法正确输入

5、相应分布的函数表达式即得结果；或在菜单法的第 2步选择 POISSON、NORMDIST、EXPONDIST等函数名，根据第 3步对话框的指导输入相应的值即可。下面我们列出这些常用分布的统计函数及其应用。 3.2泊松分布的 概率计算、泊松分布的(累积)概率值计算在 Excel 中，我们用 POISSON函数去计算泊松分布的概率值和累积概率值。其格式为:POISSONx, mean cumulative )其中 x:事件数；Mean：期望值即参数九。Cumulative :为逻辑值，若取值为 1或 TRUE,则计算累积概率值P(Xx),若取值为 0 或 FALSE,则计算随机事件发生的次数恰为x

6、的概率值 P(X=x)。即对服从参数为 兀的泊松分布的概率值 P(X=k)和累积概率值 P(X k),有 P(X=k)=POISSON(k,乳 ,0) ;P(X4)。则在 Excel 中，利用函数POISSON(3,0.8,1)就可得到累积 概率分布 P(Y 4)=1- P(Y 3)=1-0.99092=0.00908。 3.3正态分布的概率计算一、NORMDIS函数计算正态分布N(比02)的分布函数值F(x)和密度值f(x)在 Excel 中，用函数 NORMDIS 计算给定均值 H 和标准差 cr 的正态分布 N(比。2)的分布 函数值 F(x)= P(X x)和概率密度函数值 f(x)。

7、其格式为：NORMDIS(ix,mean,standard_dev , cumulative)其中x:为需要计算其分布的数值；Mean:正态分布的均值standard_dev:正态分布的标准差cumulative:为一逻辑值，指明函数的形式。如果取为 1 或 TRUE 则计算分布函数 F(x) = P(X x);如果取为 0 或 FALSE计算密度函数 f(x)。即对正态分布 N( R02)的分布函数值 F(x)和密度函数值 f(x),有F(x)=NORMDIST(x,。1) ; f(x)=NORMDIST(x, H,。0)说明：如果 mean=0且 standard_dev=1，函数 NOR

8、MDIST等计算标准正态分布N(0,1)的分布函数中(x)和密度队 x)。Excel 求解例 3.2 (1):对零件直径 XN(135,52),应求概率P(130 XV 150)= F(150)-F(130)在 Excel 中，输入 =NORMDIST(150,135,5,1)”即可得到(累积)分布函数 F(150) 的值“ 0.998650 ”，或用菜单法进入函数“NORMDIST对话框，输入相应的值(见图 3-4) 即可得同样结果。图 3-4NORMDIST 对话框再输入 “ =NORMDIST(130,135,5,1)”(或菜单法)得到 F(130)的值 “ 0.158655 ”，故P(

9、130 XV 150)= F(150)-F(130)= 0.998650-0.158655=0.839995。二、NORMSDIST数计算标准正态分布N(0,1)的分布函数值中(x)函数 NORMSDIST用于计算标准正态分布N(0,1)的(累积)分布函数 6(x)的值，该分布的均值为 0，标准差为 1 ,该函数计算可代替书后附表所附的标准正态分布表。其格式为NORMSDISTZ)其中 z :为需要计算其分布的数值。即对标准正态分布 N(0,1)的分布函数中(x),有中(x)= NORMSDIST(x)。例3.3设 ZN(0,1),试求 P(-2 Z 2)。则输入 “ =NORMSDIST(2

10、)可得 中(2)的值 “ 0.97724994 ”，输入“=NORMSDIST(-2)可得 中(-2)的值 “ 0.02275006 ”，故P(-2 Z=1/1000=0.001的指数分布，由EXPONDIST(1000, 0.001 , 1)可得分布函数 F(1000)=P(X 1000)=1- P(X x)。其格式为CHIDIST(x, deg_freedom )其中： x用来计算 胃分布单侧(尾)概率的数值。Deg_freedom /分布的自由度 n。说明：如果参数 deg_freedom不是整数，将被截尾取整。即对72(n)分布单侧概率值 P(72x),有P(Z2(n)x)= CHID

11、IST(x,n)。例如 已知I72(15),要计算 P(7220)的概率值，贝U只要在Excel 中，输入函数“=CHIDIST(20,15)(20,15) ” 即可得到所求值 0.1719327。即P(Z220)= 0.1719327。二、CHIINV函数计算7分布的上侧a分位数CHIINV函数用于计算 ”分布的上侧 a 分位数Z2dn),也就是计算单侧概率的CHIDIST函数的逆函数，即如果a=CHIDIST(x,n)，贝 U CHIINV( a,n)=x。该函数的计算可代替概率统计书后所附的 ”分布表。其格式为CHIINV( a , deg_freedom )其中 a为”分布的单侧概率a

12、。Deg_freedomZ2分布的自由度 n。说明：如果参数 deg_freedom不是整数，将被截尾取整。即对冒分布的上侧 a 分位数 7n),有72顶)=CHIINV( a,n)。例如，对 a=0.05 , n=10时，要求上侧 a分位数72O.O5(10)的值，只要在 Excel 中输入“=CHIINV(0.05,50)CHIINV(0.05,50) ” 即可得到 “18.307029 ”，即720.05(10)= 18.307029 。 3.6 t分布的概率计算、TDIST函数计算t分布的概率值在 Excel 中 TDIST函数用于计算 t分布的单侧概率值：=P(tx)和双侧概率值a=

13、P(|t|x)。其格式为TDIST(x, deg_freedom , tails )其中 x为需要计算 t分布的数字。deg_freedom t 分布的自由度 n。tails指明计算的概率值是单侧还是双侧的。若 tails=1计算单侧概率值 a=P(tx);若 tails=2，则计算双侧概率值 ot=P(|t|x)。说明参数 deg_freedom 和 tails不是整数时将被截尾取整。即对 t(n)分布的单侧概率值 P(tx)和双侧概率值 P(|t|x),有P(t(n)x)= TDIST(x,n,1) ; P(|t(n)|x)= TDIST(x,n,2)。例如：要计算 P(|t(60)|2)

14、的概率值，用“ TDIST(2,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(tt /(n) = a/2 )的双侧a分位数 td2(n),也就是计算双侧概率值函数TDIST(ct,n,2)的逆函数，即如果a=TDIST(x,n,2)，则 TINV(ot,n)=x。该函数的计算可代替书后t 分布表(附表 6)。其格式为TINV(a,deg_freedom )其中a为对应于 t分布的双侧概率值；Deg_freedom

15、为 t 分布的自由度 n。说明：如果 deg_freedom 不为整数时将被截尾取整。注意，函数 TINV( a,n)的值是 t*(n)，如果需要计算 t 分布的上侧a分位数 tjn),应由 “ =TINV(2* a,n) ” 得到，即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.812462。对 a=0.05 , n=50 时，tO.O5(50)由 “ =TINV(0.05*2,50) ”得，其值为 1

16、.675905。而 TINV(0.05,50)TINV(0.05,50) =2.00856，是 tO.O25(50)ZO.O25=1.96 )的值。 3.7 F分布的概率计算一、FDIST函数计算 F分布的概率值在 Excel 中 FDIST函数用于计算 F分布的单侧概率值a=P(Fx)。其格式为FDIST(x, deg_freedom1 , deg_freedom2 )其中： x用来计算 F 分布单侧概率的数值；Deg_freedom1 F分布的第一(分子)自由度 n1;Deg_freedom2 F分布的第二(分母)自由度 n2。说明：如果参数 deg_freedom1 或 deg_free

17、dom2 不是整数，将被截尾取整。即对 F(ni,n2)分布的单侧概率值PF(ni,n2)x,有P(F(ni,n2)x=FDIST(x,ni,n2)。例如，对 FF(10,5)，需求 概率值 P(F0.3),P(F0.3),则在 ExcelExcel 中由“=FDIST(0.3,10,5) 得 0.950303 ,故P(FP(F(10,5) 0.3)=0.3)= 0.950303。二、FINV函数计算F分布的上侧a分位数FINV函数用于计算 F 分布的上侧a分位数 FG(n1,n2),也就是计算单侧概率的FDIST函数的逆函数，即如果 o(=FDIST(x,n1,n2)，则 FINV( o(,

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

19、 4.735057 ;而 F0.05(5,10)则由 “ =FINV(0.05,5,10) ”得，其值为 3.325837。另外，FO.95(10,5)可由 “ =FINV(0.95,10,5) ”直接求得，其值为 0.300677。最后我们给出 Excel 中常用连续型分布统计函数的简明意义对照表，供查阅。分布ExcelExcel 统计函数对应概率值ExcelExcel 统计函数对应分位数正态分布 N(Rf)NORMDIST(x, !4Q0)NORMDIST(x, !4jj1)正态密度 f(x)P(X x) = F(x)NORMINV(p, Rn)X1-p=F-1(p)标准正态分布N(0,1

20、)NORMSDIST(x)PZ xCHIINV( an)0)T 分布 t(n)TDIST(x,n,1)Pt(n)xTINV( an)t*)TDIST(x,n,2)P|t(n)|xTINV( Ot*2,n)W)F 分布 F(n1,n2)FDIST(x,n1,n2)PF(n1,n2)xFINV( O(,n1,n2)Fo(n1,n2)上机训练题二1.一电子仪器由 200个元件构成，每一元件在一年的工作期内发生故障的概率为0.001。设各元件是否发生故障是相互独立的，且只要有一元件发生故障，仪器就不能正常工作。利用 Excel 中的统计函数来求：(1)仪器正常工作一年以上的概率；(2) 一年内有 2

21、个以上( 2)元件发生故障的概率。2.已知 X服从 Z=4 的泊松分布 P，试用 Excel 求 P(X6)。3.已知 XN (1.5, 22),试用 Excel 中的统计函数来求：(1) P(2 EV 2.5) ; (2) P( E 2)4. 利用 Excel 中的统计函数来计算下列各值(1)20.99(10) ,20.90(12),20.01(60),#0.05(16);(2)t0.90(4), t0.01(10), t0.05(12), t0.025(60);(3)FO.OI(10,9) ,FO.05(10,9),FO.90(28,2),FO.95(10,8)。5. 用 Excel 求以

22、下各分布的概率值(1)P (J(21) 10); P (/(21) v 15);(2)P(t(4) 3);P(|t(4)| V 1.5);(3)P (F(4,12) V 5);P(F(4,12)3)。上机实习四用Excel求正态总体参数的置信区间首先我们列出求解单个总体常用参数的置信区间简要结果表，可供查阅。表 4-1单个总体参数的 100（1 -枫置信区间总体参数条件100(1 a)%M信区间正 态 分布均值2一，O已知 。XZa/230）-SXZaL7 n方差2CT,(n-1)S2(n-1)S2、(y y2，y y2/人算,1琴标准差(SJ72,S)享 1与知（大样本 n 30）S+Z里s

23、*卜面讨论用 Excel 软件来求正态总体的总体均值和方差的常用置信区间问题。 4.1用Excel求。2已知时总体均值的置信区间总体方差 号已知时，求总体均值 P的 100（1 a）%的置信区间公式为:（XZ：/2 ,X Z/2 ）即Vntn。例 4.1 设某药厂生产的某种药片直径X是一随机变量，服从方差为0.82的正态分布。现从某日生产的药片中随机抽取9 片，测得其直径分别为（单位：mm）14.1, 14.7 , 14.7 , 14.4 , 14.6 , 14.5 , 14.5 , 14.8 , 14.2,试求该药片直径的均值卜的 95%置信区间。解：对药片直径 X,已知 X 服从 N(H

24、0.82)o对于 1 o(=0.95,则=0.05,查标准正态分布分位数表得临界值Z：/2=Z0.025=1.96,又已知 cr=0.8, n=9,故0.8X_Z-./2=14.5_1.96.14.5_1.96 0.08 = 14.5 _ 0.52n9所以，该药片直径的均值 H的 95%置信区间为(13.98, 15.02)。在 Excel 中，利用样本均值函数 AVERAGE 和置信区域函数 CONFIDENC 就可以分别得CTIZ：./2. .一、. 一到 x 和Jn 的值，由此即可得到置信区间的上、下限。其中统计函数AVERAGE和CONFIDENC的格式分别为:返回参数平均值(算术平均

25、值)x要计算平均值的 130个参数。参数可以是具体数字，或者是涉及数字的名称、 数据范围或引用。,返回总体均值的置信区域，即样本均值任意一侧图 4-1说明：(1)在图 4-1中，F列为 D列的计算显示结果，当输入完公式后，回车即显示出 列结果，这里只是为了看清公式，才给出了D列的公式形式。(2)对于不同的样本数据，只要输入新的样本数据，再对D列公式中的样本数据区域相应修改，置信区间就会自动给出。如果需要不同的置信水平，只需改变置信区域函数 CONFIDENC的相应数值即可。AVERAG(number1,number2,.)其中 Number1, number2, .CONFIDENqalpha

26、, st_dev, size)其中 alphaZ-./2二的区域大小-. n。o(,对应的置信度等于100*(1- a)%,stdevsize显著水平 亦即，如果 alpha 为 0.05，则置信度为 95%。数据区域的总体标准差汀,假设为已知。样本容量现以例 4.1的求解来说明已知方差Excel 求解例 4.1 :为构造例 4.1A列输入例 4.1 的样本数据；n。a2时，用 Excel 构造总体均值的置信区间的具体步骤。所求的置信区间，我们在工作表中输入下列内容：C列输入指标名称；D 列输入计算公式4-1 )。F8J_=AC 1 D 1 EF 1 G114.1计算指标计算公式计其结果214

27、.7丰值=AVERAGE 3；四)14.5314.7置饴区域-C0NFIDENiCE(0.05f0,8,9) 0. 5226563414.4置信T艮=F2-F3 I13. 9背斑514.6置信上限=F2+F315. D22&614.5714.5814.01 1A14.210即可得到所需估计的 95%置信区间上、下限(见图 由图 4-1中计算结果知，所求药片直径均值卜的 95%置信区间为(13.98, 15.02)。求总体均值 P的 100（1 a）%的置信区间公式为:SS（X -t./2, Xt/2）即腴Vn 。12例儿童的每 100ml 血所含钙的实测数据为（单位：微克）54.8 ,

28、 72.3 , 53.6 ,已知该含钙量服从正态分布，试求该组儿童的每 解：由实测数据的计算可得到：X=1X=1 寸 X Xi2X-nX2)忌n，=59.14,酉顼-1i4=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 _/2=59.14 _1.79659.14 _4.46则n. 12故所求平均含钙量的90%置信区间为(54.68 , 63.6)。在 Excel 中，利用“数据分析”菜单的“描述统计”计算结果中“平均”和“置信度”S一 .一.一

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

30、体方差 控未知时,- SX_/2 n例 4.2设有一组共64.7 , 43.6 , 58.3 , 63.0 , 49.6 , 66.2 , 52.5 , 61.2 , 69.9 ,100ml 血平均含钙量的 90%置信区间。6.点击“确定”。如下列图 4-2所示图 4-3根据描述统计量计算结果中样本均值 (平均)=59.142 和置信区间半径(置信度)=4.464 , 就可得所求平均含钙量的90%置信区间为(59.142-4.464 , 59.142+4.464 )即(54.677 ,63.606)。 4.3用Excel求正态总体方差顼2的置信区间根据样本数据，求正态总体方差a 的 100(1

31、 - 口)置信区间公式为,(n -1)S2(n-1)S2、(2,2/1号其中 S2是样本方差，气出-瞠是72(n-1)分布的双侧临界值。例 4.3 设某生物寿命服从正态分布，今观察其一组样本寿命，得数据为：(小时)1050, 1100, 1080, 1120, 1200, 1250, 1040, 1130, 1300, 1200, 1270, 1300试估计该生物寿命的总体方差的90%置信区间。解：由样本数据计算得S2=9127.27,而 n=12,对于 1 ot=0.90，则 a=0.10, n-1=11，查男2分布表，得临界值X2踌(n-1)=*0.05(11)=19.675 ;弩 g(n

32、-1)=72.95(11)=4.575 ,(n?S,(n1)S)11 9127.2711 9127.27、2,2()则 2J =19.675,4.575故总体方差 廿的 90%置信区间是(5102.92 , 21945.34)。Excel 求解：下面我们通过对该例的求解来说明用Excel 构造方差。2置信区间的过程。在 Excel 中，为构造例 4.3 所求方差 a置信区间工作表，我们在工作表中输入下列内容：A列输入例 4.8的样本数据；C 列输入指标名称；D列输入计算公式即可得到所需估计的方差。2的 90%置信区间上、下限(见图 4-4)。因 1-ot=0.90，贝 U a=0.10 ,两个临界值为磐 g(1 1)=/20.05(1 1)和X2I-2(11)=0.95(11)，图 4-2由此即可得到样本数据的描述性统计量的结果，如图4-3所示=D3-DL6A54.872.353.t-64P743.6SS.363获.6标准误差中恒 模式怀推偏差样本方差5也14167置信上限IS土研7舞I2. 485853置信下限63, $0597跄75#ti/A3. 6112467土153561_最求计宣值斜域小度值值度大和数信-0.6329-0.1 SO 2928.743.67