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

上机实习常用分布概率计算的Excel应用利用Excel中的统计函数工具，可以计算二项分布、泊松分布、正态分布等常用概率分布的概率值、累积（分布）概率等。这里我们主要介绍如何用Excel来计算二项分布的概率值与累积概率，其他常用分布的概率计算等处理与此类似。

§3.1 二项分布的概率计算

一、二项分布的（累积）概率值计算

用Excel来计算二项分布的概率值P n(k)、累积概率F n(k)，需要用BINOMDIST函数，其格式为：

BINOMDIST (number_s，trials, probability_s, cumulative)

其中 number_s：试验成功的次数k；

trials：独立试验的总次数n；

probability_s：一次试验中成功的概率p；

cumulative：为一逻辑值，若取0或FALSE时，计算概率值P n(k)；若取1

或TRUE时，则计算累积概率F n(k)，。

即对二项分布B(n,p)的概率值P n(k)和累积概率F n(k)，有

P n(k)=BINOMDIST(k,n,p,0)；F n(k)= BINOMDIST(k,n,p,1)

现结合下列机床维修问题的概率计算来稀疏现象（小概率事件）发生次数说明计算二项分布概率的具体步骤。

例3.1某车间有各自独立运行的机床若干台，设每台机床发生故障的概率为0.01，每台机床的故障需要一名维修工来排除，试求在下列两种情形下机床发生故障而得不到及时维修的概率：

（1）一人负责15台机床的维修；

（2）3人共同负责80台机床的维修。

原解：（1）依题意，维修人员是否能及时维修机床，取决于同一时刻发生故障的机床数。

设X表示15台机床中同一时刻发生故障的台数，则X服从n=15,p=0.01的二项分布：

X～B(15,0.01)，

而 P(X= k)= C15k(0.01)k(0.99)15-k，k = 0, 1, …, 15

故所求概率为

P(X≥2)=1-P(X≤1)=1-P(X=0)-P(X=1)

=1-(0.99)15-15×0.01×(0.99)14

=1-0.8600-0.1303=0.0097

（2）当3人共同负责80台机床的维修时，设Y表示80台机床中同一时刻发生故障的台数，则Y服从n=80、p=0.01的二项分布，即

Y～B(80,0.01)

此时因为 n=80≥30, p=0.01≤0.2

所以可以利用泊松近似公式：当n很大，p较小时（一般只要n≥30,p≤0.2时），对任一确定的k,有（其中 =np）

λλ--≈e k q

p C k k n k k n !

来计算。 由λ=np=80×0.01=0.8, 利用泊松分布表，所求概率为

P(Y ≥4)=k k k k C -=∑8080480)99.0()01.0(≈8.080

4!)8.0(-=∑e k k k =0.0091 我们发现，虽然第二种情况平均每人需维修27台，比第一种情况增加了80%的工作量，但是其管理质量反而提高了。

Excel 求解：已知15台机床中同一时刻发生故障的台数X ～B(n,p), 其中n=15, p=0.01，则所求概率为

P(X ≥2)=1-P(X ≤1)=1-P(X=0)-P(X=1)=1- P 15(0)-P 15(1)

利用Excel 计算概率值P 15(1)的步骤为：

（一）函数法：

在单元格中或工作表上方编辑栏中输入“= BINOMDIST(1,15,0.01,0)” 后回车，选定单元格即出现P 15(1)的概率为0.130312（图3-1）。

图3-1 直接输入函数公式的结果（函数法）

（二）菜单法：

1. 点击图标“f x ” 或选择“插入”下拉菜单的“函数”子菜单，即进入“函数”对话框（图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，表明选定概率值P n(k)）；

图3-3 “BINOMDIST”对话框

4．最后单击“确定”，相应单元格中就出现P15(1)的概率0.130312。

类似地若要求P15(0)的概率值，只需直接输入“= BINOMDIST(0,15,0.01,0)”或利用菜单法，在其第3步选项Number_s窗口输入0，即可得概率值0.860058，则

P(X≥2)= 1- P15(0)-P15(1)=1-0.860058-0.130312=0.00963。

另外，P(X≥2)=1-P(X≤1)=1-F15(1)，即也可以通过先求累积概率F15(1)来求解。而要求出F15(1)的值，只需在单元格上直接输入“= BINOMDIST(1,15,0.01,1)”回车即可；或利用上述菜单法步骤，在第3步的选项Cumulative窗口输入：1，即得到累积概率F15(1)的值0.99037，故有

P(X≥2)=1-P(X≤1)=1- F15(1)=1-0.99037=0.00963。

对于例3.1，Y表示80台机床中同一时刻发生故障的台数，则Y服从n=80、p=0.01的二项分布，即Y～B(80,0.01)。

所求概率为

P(Y≥4)=1- P(Y≤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原解中的结果是泊松近似值）

对于泊松分布、正态分布、指数分布等的概率计算步骤与上述二项分布的概率计算过程类似，只需利用函数法正确输入相应分布的函数表达式即得结果；或在菜单法的第2步选择POISSON、NORMDIST、EXPONDIST等函数名，根据第3步对话框的指导输入相应的值即可。下面我们列出这些常用分布的统计函数及其应用。

§3.2 泊松分布的概率计算

一、泊松分布的（累积）概率值计算

在Excel中，我们用POISSON 函数去计算泊松分布的概率值和累积概率值。其格式为：