1、上机实习 常用分布概率计算的 Excel 应用利用 Excel 中的统计函数工具，可以计算二项分布、泊松分布、正态分布等常用概率分布的概率值、累积（分布）概率等。这里我们主要介绍如何用 Excel 来计算二项分布的概率值与累积概率，其他常用分布的概率计算等处理与此类似。3.1 二项分布的概率计算一、二项分布的（累积）概率值计算用 Excel 来计算二项分布的概率值 Pn(k)、累积概率 Fn(k)，需要用 BINOMDIST 函数，其格式为：BINOMDIST (number_s，trials, probability_s, cumulative)其中 number_s： 试验成功的次数 k；
2、trials： 独立试验的总次数 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)； F n(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)= C 15k(0.01)k(0.99)15-k ，k = 0, 1, , 15故所求概率为P(X2)=1-P(X1)=1-P(X=0)-P(X=1)=1-(0.99)15-150.01(0.99)14=
4、1-0.8600-0.1303=0.0097（2）当 3 人共同负责 80 台机床的维修时，设 Y 表示 80 台机床中同一时刻发生故障的台数，则 Y 服从 n=80、p=0.01 的二项分布，即YB(80,0.01)此时因为 n=8030, p=0.010.2所以可以利用泊松近似公式： 当 n 很大，p 较小时（一般只要 n30,p0.2 时） ，对任一确定的 k,有（其中 =np） ekqpCnk!来计算。由 =np=800.01=0.8, 利用泊松分布表，所求概率为P(Y4)=kkk80804)9.(1.8.04!)(ek=0.0091我们发现，虽然第二种情况平均每人需维修 27 台，比
5、第一种情况增加了 80%的工作量，但是其管理质量反而提高了。 Excel求解：已知 15 台机床中同一时刻发生故障的台数 XB(n,p), 其中 n=15, p=0.01，则所求概率为P(X2)=1-P(X1)=1-P(X=0)-P(X=1)=1- P 15(0)-P15(1)利用 Excel 计算概率值 P15(1)的步骤为：（一）函数法：在单元格中或工作表上方编辑栏中输入“= BINOMDIST(1,15,0.01,0)” 后回车，选定单元格即出现 P15(1)的概率为 0.130312（图 3-1） 。图 3-1 直接输入函数公式的结果（函数法）（二）菜单法：1. 点击图标“ fx” 或
6、选择“插入”下拉菜单的“函数”子菜单，即进入“函数”对话框（图 3-2） ；2. 在函数对话框中， “函数分类”中选择“统计” ， “函数名字”中选定“BINOMDIST”，再单击“确定” ；（图 3-2）图 3-2 “插入”下的“函数”对话框2. 进入“BINOMDIST”对话框（图 3-3） ，对选项输入适当的值：在 Number_s 窗口输入：1（试验成功的次数 k） ；在 Trials 窗口输入：15（独立试验的总次数 n） ；在 Probability_s 窗口输入：0.01（一次试验中成功的概率 p） ；在 Cumulative 窗口输入：0（或 FALSE，表明选定概率值 Pn(
7、k)） ；图 3-3 “BINOMDIST”对话框4最后单击“确定” ，相应单元格中就出现 P15(1)的概率 0.130312。类似地若要求 P15(0)的概率值，只需直接输入 “= BINOMDIST(0,15,0.01,0)”或利用菜单法，在其第 3 步选项 Number_s 窗口输入 0，即可得概率值 0.860058，则 P(X2)= 1- P 15(0)-P15(1)=1-0.860058-0.130312=0.00963。另外，P(X2)=1-P(X1)=1-F 15(1)，即也可以通过先求累积概率 F15(1)来求解。而要求出 F15(1)的值，只需在单元格上直接输入“= BI
8、NOMDIST(1,15,0.01,1)”回车即可；或利用上述菜单法步骤，在第 3 步的选项 Cumulative 窗口输入：1，即得到累积概率 F15(1)的值 0.99037，故有P(X2)=1-P(X1)=1- F 15(1)=1-0.99037=0.00963。对于例 3.1，Y 表示 80 台机床中同一时刻发生故障的台数，则 Y 服从 n=80、p=0.01 的二项分布，即 YB(80,0.01)。所求概率为P(Y4)=1- P(Y3)=1- F 80(3)利用 Excel,在单元格上直接输入“= BINOMDIST(3,80,0.01,1)”回车或与上述菜单法类似操作可得累积概率
9、F80(3)=0.991341，故所求概率的精确值为P(Y4)=1- P(Y3)=1- F 80(3)=1-0.991341=0.00866。（注意：例 3.1 原解中的结果是泊松近似值）对于泊松分布、正态分布、指数分布等的概率计算步骤与上述二项分布的概率计算过程类似，只需利用函数法正确输入相应分布的函数表达式即得结果；或在菜单法的第 2 步选择 POISSON、NORMDIST、EXPONDIST 等函数名，根据第 3 步对话框的指导输入相应的值即可。下面我们列出这些常用分布的统计函数及其应用。3.2 泊松分布的概率计算一、泊松分布的（累积）概率值计算在 Excel 中，我们用 POISSO
10、N 函数去计算泊松分布的概率值和累积概率值。其格式为：POISSON(x,mean,cumulative)其中 x: 事件数；Mean： 期望值即参数 。Cumulative： 为逻辑值，若取值为 1 或 TRUE，则计算累积概率值 P(Xx)，若取值为 0 或 FALSE，则计算随机事件发生的次数恰为 x 的概率值 P(X=x)。即对服从参数为 的泊松分布的概率值 P(X=k)和累积概率值 P(Xk) ，有P(X=k)=POISSON(k,0)；P(Xk)= POISSON(k,1)。例如，在例 3.1（2）的原解的泊松近似计算中，Y 近似服从 =np=800.01=0.8 的泊松分布 P(
11、)，需求 P(Y4)。则在 Excel 中，利用函数 POISSON(3,0.8,1)就可得到累积概率分布 P(Y3)的值 0.99092，则所求概率为P(Y4)=1- P(Y3)=1-0.99092=0.00908。3.3 正态分布的概率计算一、NORMDIST 函数计算正态分布 N(,2)的分布函数值 F(x)和密度值 f(x)在 Excel 中，用函数 NORMDIST 计算给定均值 和标准差 的正态分布 N(,2)的分布函数值 F(x)P(Xx)和概率密度函数值 f(x)。其格式为：NORMDIST(x,mean,standard_dev,cumulative)其中 x: 为需要计算其
12、分布的数值；Mean: 正态分布的均值 ；standard_dev: 正态分布的标准差 ；cumulative: 为一逻辑值，指明函数的形式。如果取为 1 或 TRUE，则计算分布函数 F(x)P(Xx)；如果取为 0 或 FALSE，计算密度函数f(x)。即对正态分布 N(,2)的分布函数值 F(x)和密度函数值 f(x)，有F(x)=NORMDIST(x,1)；f(x)=NORMDIST(x,0)说明：如果 mean=0 且 standard_dev=1，函数 NORMDIST 将计算标准正态分布 N(0,1)的分布函数 (x)和密度 (x)。Excel求解例 3.2 (1)：对零件直径
13、XN(135,5 2)，应求概率P(130X150)= F(150)-F(130)在 Excel 中，输入 “=NORMDIST(150,135,5,1)” 即可得到（累积）分布函数 F(150)的值“0.998650” ，或用菜单法进入函数“NORMDIST”对话框，输入相应的值（见图 3-4）即可得同样结果。图 3-4 “NORMDIST”对话框再输入“=NORMDIST(130,135,5,1)” （或菜单法）得到 F(130)的值“ 0.158655” ，故P(130X150)= F(150)-F(130)= 0.998650-0.158655=0.839995。二、NORMSDIST
14、 函数计算标准正态分布 N(0,1)的分布函数值 (x) 函数 NORMSDIST 是用于计算标准正态分布 N(0,1)的(累积)分布函数 (x)的值，该分布的均值为 0，标准差为 1，该函数计算可代替书后附表所附的标准正态分布表。其格式为NORMSDIST(z)其中 z：为需要计算其分布的数值。即对标准正态分布 N(0,1)的分布函数 (x)，有(x)= NORMSDIST(x)。例 3.3 设 ZN(0,1), 试求 P(-2Z2)。则输入“= NORMSDIST(2)” 可得 (2)的值“ 0.97724994”，输入“= NORMSDIST(-2)” 可得 (-2) 的值“0.0227
15、5006” ，故P(-2Z2)=(2)-(-2)=0.97724994-0.02275006=0.95449988。三、NORMSINV 函数计算标准正态分布 N(0,1)的分位数函数 NORMSINV 用于计算标准正态分布 N(0,1)的(累积)分布函数的逆函数 -1(p)。即已知概率值 (x)=p，由 NORMSINV(p)就可以得到 x(=-1(p)的值，该 x 就是对应于 p=1- 的标准正态分布 N(0,1)分位数 Z1-。函数 NORMSINV 的格式为NORMSINV(probability)其中 probability: 标准正态分布的概率值 p。则对标准正态分布 N(0,1)
16、的分位数 Z，有Z= NORMSINV(1-)。Excel求解例 3.2(2)：在例 3.2（2）原解的计算中，已求得9.0)5(，则由 Excel 中，NORMSINV(0.9)= 1.281551，得281.，故 = 5/1.281551=3.901522。3.4 指数分布的概率计算一、指数分布分布函数值和密度值的计算在 Excel 中，函数 EXPONDIST 用于计算指数分布的(累积)分布函数值 F(x)和概率密度函数值 f(x)。其格式为：EXPONDIST(x,lambda,cumulative)其中 x： 为需要计算其分布的数值；Lambda ： 指数分布的参数值 。Cumula
17、tive： 为逻辑值，指定函数形式。若取 1 或 TRUE，将计算分布函数F(x)；若 取 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(X1000)的概率值 0.632121，故所求的概率为P(X1000)=1- P(X1000)=1- F(1000)=1-0.632121=0.367879。
18、3.5 2 分布的概率计算一、CHIDIST 函数计算 2分布的概率值在 Excel 中 CHIDIST 函数用于计算 2分布的单侧概率值 = P(2x)。其格式为CHIDIST(x, deg_freedom)其中： x 用来计算 2分布单侧（尾）概率的数值。Deg_freedom 2分布的自由度 n。说明：如果参数 deg_freedom 不是整数，将被截尾取整。即对 2（n）分布单侧概率值 P(2x)，有P(2(n)x)= CHIDIST(x,n)。例如 已知 2 2(15),要计算 P(220)的概率值，则只要在 Excel 中，输入函数“=CHIDIST(20,15)”即可得到所求值
19、0.1719327。即P(220)= 0.1719327。二、CHIINV 函数计算 2分布的上侧 分位数CHIINV 函数用于计算 2分布的上侧 分位数 2(n), 也就是计算单侧概率的 CHIDIST函数的逆函数，即如果 =CHIDIST(x,n)，则 CHIINV(,n)=x。该函数的计算可代替概率统计书后所附的 2分布表。其格式为CHIINV( ,deg_freedom)其中 为 2分布的单侧概率 。Deg_freedom 2分布的自由度 n。说明: 如果参数 deg_freedom 不是整数，将被截尾取整。即对 2分布的上侧 分位数 2(n)，有2(n)= CHIINV(,n)。例如
20、，对 =0.05，n=10 时, 要求上侧 分位数 20.05(10)的值，只要在 Excel 中输入“=CHIINV(0.05,50)”即可得到“18.307029” ，即 20.05(10)= 18.307029。3.6 t 分布的概率 计算一、TDIST 函数计算 t分布的概率值在 Excel 中 TDIST 函数用于计算 t 分布的单侧概率值=P(tx)和双侧概率值=P(|t|x)。其格式为TDIST(x, deg_freedom, tails)其中 x 为需要计算 t 分布的数字。deg_freedom t 分布的自由度 n。tails 指明计算的概率值是单侧还是双侧的。若 tail
21、s=1 计算单侧概率值 =P(tx)；若 tails=2，则计算双侧概率值 =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)的概率值，用“TDIST(2,60,2)”即得 0.050033。 即 P(|t(60)|2)= 0.050033。二、TINV 函数计算 t分布双侧 分位数TINV 函数用于计算 t 分布的满足P(|t| t/2(n)=
22、（即 P(tt /2(n) =/2） 的双侧 分位数 t/2(n), 也就是计算双侧概率值函数 TDIST(,n,2)的逆函数，即如果=TDIST(x,n,2)，则 TINV(,n)=x。该函数的计算可代替书后 t 分布表(附表 6)。其格式为TINV(, deg_freedom)其中 为对应于 t 分布的双侧概率值；Deg_freedom 为 t 分布的自由度 n。说明：如果 deg_freedom 不为整数时将被截尾取整。注意，函数 TINV(,n)的值是 t/2(n)，如果需要计算 t 分布的上侧 分位数 t(n),应由“=TINV(2*,n)”得到，即t(n)=TINV(2,n)例如，
23、对 n=10 时, t 0.025(10)可由“=TINV(0.05,10)”得，其值为 2.228139；而 t 0.05(10)应由“=TINV(0.05*2,10)”得，其值为 1.812462。对 =0.05，n=50 时, t 0.05(50) 由“=TINV(0.05*2,50)”得，其值为 1.675905。而 TINV(0.05,50)=2.00856，是 t0.025(50)（Z 0.025=1.96）的值。3.7 F 分布的概率计算一、FDIST 函数计算 F 分布的概率值在 Excel 中 FDIST 函数用于计算 F 分布的单侧概率值=P(Fx)。其格式为FDIST(x
24、,deg_freedom1,deg_freedom2)其中： x 用来计算 F 分布单侧概率的数值；Deg_freedom1 F 分布的第一(分子)自由度 n1；Deg_freedom2 F 分布的第二(分母)自由度 n2。说明：如果参数 deg_freedom1 或 deg_freedom2 不是整数，将被截尾取整。即对 F(n1,n2)分布的单侧概率值 PF(n1,n2)x，有PF(n1,n2)x=FDIST(x,n1,n2)。例如，对 FF(10,5)，需求概率值 P(F0.3),则在 Excel中由“= FDIST(0.3,10,5)得0.950303，故 P(F(10,5)0.3)=
25、 0.950303。二、FINV 函数计算 F分布的上侧 分位数FINV 函数用于计算 F 分布的上侧 分位数 F(n1,n2), 也就是计算单侧概率的 FDIST函数的逆函数，即如果 =FDIST(x,n1,n2)，则 FINV(,n1,n2)=x。FINV 函数的计算可代替书后所附的 F 分布表。其格式为FINV(,deg_freedom1,deg_freedom2)其中 对应于 F 分布的单侧概率值；Deg_freedom1 F 分布的第一(分子)自由度 n1；Deg_freedom2 F 分布的第二(分母)自由度 n2。说明：如果 deg_freedom1 或 deg_freedom2
26、 不是整数，将被截尾取整。即对 F 分布的上侧 分位数 F(n1,n2)，有F(n1,n2)= FINV(,n1,n2)。例如，对 =0.05，F 0.05(10,5)可由“=FINV(0.05,10,5)”得，其值为 4.735057；而 F 0.05(5,10)则由“=FINV(0.05,5,10)”得，其值为 3.325837。另外，F 0.95(10,5)可由“=FINV(0.95,10,5)”直接求得，其值为 0.300677。最后我们给出 Excel 中常用连续型分布统计函数的简明意义对照表，供查阅。分 布 Excel 统计函数 对应概率值 Excel 统计函数 对应分位数正态分布
27、N(,2)NORMDIST(x,0) NORMDIST(x,1)正态密度 f(x)P(X x)F(x)NORMINV(p,) X1-p=F-1(p)标准正态分布N(0,1)NORMSDIST(x) PZx=(x) NORMSINV(p) Z1-p（= -1(p)）2 分布 2(n) CHIDIST(x,n) P2(n)x CHIINV(,n) 2(n)T 分布 t(n) TDIST(x,n,1) TDIST(x,n,2) Pt(n)x P|t(n)|x TINV(,n) TINV(*2,n) t/2(n) t(n)F 分布 F(n1,n2) FDIST(x,n1,n2) PF(n1,n2)x
28、FINV(,n1,n2) F(n1,n2)上机训练题三1. 一电子仪器由 200 个元件构成，每一元件在一年的工作期内发生故障的概率为0.001。设各元件是否发生故障是相互独立的，且只要有一元件发生故障，仪器就不能正常工作。利用 Excel 中的统计函数来求：（1）仪器正常工作一年以上的概率；（2）一年内有 2 个以上(2)元件发生故障的概率。 2. 已知 X 服从 =4 的泊松分布 P()，试用 Excel 求 P(X2)。4利用 Excel 中的统计函数来计算下列各值（1） 20.99(10)， 20.90(12)， 20.01(60)， 20.05(16)； （2）t 0.90(4)，t
29、 0.01(10)，t 0.05(12)，t 0.025(60)；（3）F 0.01(10, 9)，F 0.05(10, 9)，F 0.90(28, 2)，F 0.95(10, 8)。5用 Excel 求以下各分布的概率值（1）P ( 2(21)10) ； P (2(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(1)%置信区间总 体 参 数 条 件 100(
30、1)%置信区间2已知 nZX2/2未知 St/均 值 2未知（大样本 n30） n2/方 差 2 未知 )1(,)(22S 未知 ,(122S正态分布标准差 未知（大样本 n30） nZ/下面讨论用 Excel 软件来求正态总体的总体均值和方差的常用置信区间问题。4.1 用 Excel 求 2已知时总体均值的置信区间总体方差 2已知时，求总体均值 的 100(1)%的置信区间公式为：nZX/即 ),(2/2/。例 4.1 设某药厂生产的某种药片直径 X 是一随机变量，服从方差为 0.82 的正态分布。现从某日生产的药片中随机抽取 9 片，测得其直径分别为（单位：mm ）14.1，14.7，14
31、.7，14.4，14.6，14.5，14.5，14.8，14.2，试求该药片直径的均值 的 95%置信区间。解：对药片直径 X，已知 X 服从 N(, 0.82)。对于 1=0.95 ，则 =0.05，查标准正态分布分位数表得临界值Z/2 =Z0.025=1.96,又已知 =0.8，n=9, 故 52.0148.965.148096.5142/ n所以，该药片直径的均值 的 95%置信区间为（13.98，15.02） 。在 Excel 中，利用样本均值函数 AVERAGE 和置信区域函数 CONFIDENCE 就可以分别得到 x和 nZ2/的值，由此即可得到置信区间的上、下限。其中统计函数 A
32、VERAGE 和 CONFIDENCE 的格式分别为：AVERAGE(number1,number2, .) 返回参数平均值（算术平均值） x。其中 Number1, number2, . 要计算平均值的 130 个参数。参数可以是具体数字，或者是涉及数字的名称、数据范围或引用。CONFIDENCE(alpha, st_dev, size)，返回总体均值的置信区域，即样本均值任意一侧的区域大小 nZ2/。其中 alpha 显著水平 ，对应的置信度等于 100*(1-)%，亦即，如果 alpha 为 0.05，则置信度为 95%。st_dev 数据区域的总体标准差 ，假设为已知。size 样本容量 n。现以例 4.1 的求解来说明已知方差 2时，用 Excel 构造总体均值的置信区间的具体步骤。Excel求解例 4.1：为构造例 4.1 所求的置信区间，我们在工作表中输入下列内容：A 列输入例 4.1 的样本数据； C 列输入指标名称；D 列输入计算公式即可得到所需估计的 95%置信区间上、下限（见图 4-1） 。由图 4-1 中计算结果知，所求药片直径均值 的 95%置信区间为（13.98，15.02） 。
