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

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

《常用分布概率计算的Excel应用.docx》由会员分享，可在线阅读，更多相关《常用分布概率计算的Excel应用.docx（17页珍藏版）》请在冰豆网上搜索。

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

上机实习常用分布概率计算的Excel应用

利用Excel中的统计函数工具，可以计算二项分布、泊松分布、正态分布等常用概率分布的概率值、累积（分布）概率等。

这里我们主要介绍如何用Excel来计算二项分布的概率值与累积概率，其他常用分布的概率计算等处理与此类似。

§3.1二项分布的概率计算

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

用Excel来计算二项分布的概率值Pn（k）、累积概率Fn（k），需要用BINOMDIST函数，其格式为：

BINOMDIST（number_s，trials,probability_s,cumulative）

其中number_s：

试验成功的次数k；

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）；Fn（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）

来计算。

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

P（Y≥4）=

≈

=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-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.在函数对话框中，“函数分类”中选择“统计”，“函数名字”中选定“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（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函数去计算泊松分布的概率值和累积概率值。

其格式为：

POISSON（x,mean,cumulative）

其中x:

事件数；

Mean：

期望值即参数λ。

Cumulative：

为逻辑值，若取值为1或TRUE，则计算累积概率值P（X≤x），若取值为0或FALSE，则计算随机事件发生的次数恰为x的概率值P（X=x）。

即对服从参数为λ的泊松分布的概率值P（X=k）和累积概率值P（X≤k），有

P（X=k）=POISSON（k,λ,0）；P（X≤k）=POISSON（k,λ,1）。

例如，在例3.1

（2）的原解的泊松近似计算中，Y近似服从λ=np=80×0.01=0.8的泊松分布P（λ），需求P（Y≥4）。

则在Excel中，利用函数POISSON（3,0.8,1）就可得到累积概率分布P（Y≤3）的值0.99092，则所求概率为

P（Y≥4）=1-P（Y≤3）=1-0.99092=0.00908。

§3.3正态分布的概率计算

一、NORMDIST函数计算正态分布N（μ,σ2）的分布函数值F（x）和密度值f（x）

在Excel中，用函数NORMDIST计算给定均值μ和标准差σ的正态分布N（μ,σ2）的分布函数值F（x）＝P（X≤x）和概率密度函数值f（x）。

其格式为：

NORMDIST（x,mean,standard_dev,cumulative）

其中x:

为需要计算其分布的数值；

Mean:

正态分布的均值μ；

standard_dev:

正态分布的标准差σ；

cumulative:

为一逻辑值，指明函数的形式。

如果取为1或TRUE，则计算分布函数F（x）＝P（X≤x）；如果取为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）：

对零件直径X～N（135,52），应求概率

P（130≤X≤150）=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（130≤X≤150）=F（150）-F（130）=0.998650-0.158655=0.839995。

二、NORMSDIST函数计算标准正态分布N（0,1）的分布函数值Φ（x）

函数NORMSDIST是用于计算标准正态分布N（0,1）的（累积）分布函数Φ（x）的值，该分布的均值为0，标准差为1，该函数计算可代替书后附表所附的标准正态分布表。

其格式为

NORMSDIST（z）

其中z：

为需要计算其分布的数值。

即对标准正态分布N（0,1）的分布函数Φ（x），有

Φ（x）=NORMSDIST（x）。

例3.3设Z～N（0,1）,试求P（-2≤Z≤2）。

则输入“=NORMSDIST

（2）”可得Φ

（2）的值“0.97724994”，输入“=NORMSDIST（-2）”可得Φ（-2）的值“0.02275006”，故

P（-2≤Z≤2）=Φ

（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）的分位数Zα，有

Zα=NORMSINV（1-α）。

Excel求解例3.2

（2）：

在例3.2

（2）原解的计算中，已求得

，

则由Excel中，NORMSINV（0.9）=1.281551，得

，

故σ=5/1.281551=3.901522。

§3.4指数分布的概率计算

一、指数分布分布函数值和密度值的计算

在Excel中，函数EXPONDIST用于计算指数分布的（累积）分布函数值F（x）和概率密度函数值f（x）。

其格式为：

EXPONDIST（x,lambda,cumulative）

其中x：

为需要计算其分布的数值；

Lambda ：

指数分布的参数值λ。

Cumulative：

为逻辑值，指定函数形式。

若取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（X≤1000）的概率值0.632121，故所求的概率为

P（X>1000）=1-P（X≤1000）=1-F（1000）=1-0.632121=0.367879。

§3.5χ2分布的概率计算

一、CHIDIST函数计算χ2分布的概率值

在Excel中CHIDIST函数用于计算χ2分布的单侧概率值α=P（χ2>x）。

其格式为

CHIDIST（x,deg_freedom）

其中：

x  用来计算χ2分布单侧（尾）概率的数值。

Deg_freedom  χ2分布的自由度n。

说明：

如果参数deg_freedom不是整数，将被截尾取整。

即对χ2（n）分布单侧概率值P（χ2>x），有

P（χ2（n）>x）=CHIDIST（x,n）。

例如已知χ2～χ2（15）,要计算P（χ2>20）的概率值，则只要在Excel中，输入函数“=CHIDIST（20,15）”即可得到所求值0.1719327。

即

P（χ2>20）=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）。

例如，对α=0.05，n=10时,要求上侧α分位数χ20.05（10）的值，只要在Excel中输入“=CHIINV（0.05,50）”即可得到“18.307029”，即χ20.05（10）=18.307029。

§3.6t分布的概率计算

一、TDIST函数计算t分布的概率值

在Excel中TDIST函数用于计算t分布的单侧概率值

α=P（t>x）

和双侧概率值

α=P（|t|>x）。

其格式为

TDIST（x,deg_freedom,tails）

其中x  为需要计算t分布的数字。

deg_freedom t分布的自由度n。

tails  指明计算的概率值是单侧还是双侧的。

若tails=1计算单侧概率值α=P（t>x）；若tails=2，则计算双侧概率值α=P（|t|>x）。

说明参数deg_freedom和tails不是整数时将被截尾取整。

即对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分布双侧α分位数

TINV函数用于计算t分布的满足

P（|t|>tα/2（n））=α（即P（t>tα/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）

例如，对n=10时,t0.025（10）可由“=TINV（0.05,10）”得，其值为2.228139；

而t0.05（10）应由“=TINV（0.05*2,10）”得，其值为1.812462。

对α=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.7F分布的概率计算

一、FDIST函数计算F分布的概率值

在Excel中FDIST函数用于计算F分布的单侧概率值

α=P（F>x）。

其格式为

FDIST（x,deg_freedom1,deg_freedom2）

其中：

x  用来计算F分布单侧概率的数值；

Deg_freedom1  F分布的第一（分子）自由度n1；

Deg_freedom2  F分布的第二（分母）自由度n2。

说明：

如果参数deg_freedom1或deg_freedom2不是整数，将被截尾取整。

即对F（n1,n2）分布的单侧概率值P{F（n1,n2）>x}，有

P{F（n1,n2）>x}=FDIST（x,n1,n2）。

例如，对F～F（10,5），需求概率值P（F>0.3）,则在Excel中由“=FDIST（0.3,10,5）得0.950303，故

P（F（10,5）>0.3）=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不是整数，将被截尾取整。

即对F分布的上侧α分位数Fα（n1,n2），有

Fα（n1,n2）=FINV（α,n1,n2）。

例如，对α=0.05，F0.05（10,5）可由“=FINV（0.05,10,5）”得，其值为4.735057；

而F0.05（5,10）则由“=FINV（0.05,5,10）”得，其值为3.325837。

另外，F0.95（10,5）可由“=FINV（0.95,10,5）”直接求得，其值为0.300677。

最后我们给出Excel中常用连续型分布统计函数的简明意义对照表，供查阅。

分布

Excel统计函数

对应概率值

Excel统计函数

对应分位数

正态分布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）

P{Z≤x}=Φ（x）

NORMSINV（p）

Z1-p（=Φ-1（p））

χ2分布χ2（n）

CHIDIST（x,n）

P{χ2（n）>x}

CHIINV（α,n）

χ2α（n）

T分布t（n）

TDIST（x,n,1）TDIST（x,n,2）

P{t（n）>x}P{|t（n）|>x}

TINV（α,n）TINV（α*2,n）

tα/2（n）

tα（n）

F分布F（n1,n2）

FDIST（x,n1,n2）

P{F（n1,n2）>x}

FINV（α,n1,n2）

Fα（n1,n2）

上机训练题三

1.一电子仪器由200个元件构成，每一元件在一年的工作期内发生故障的概率为0.001。

设各元件是否发生故障是相互独立的，且只要有一元件发生故障，仪器就不能正常工作。

利用Excel中的统计函数来求：

（1）仪器正常工作一年以上的概率；

（2）一年内有2个以上（≥2）元件发生故障的概率。

2.已知X服从λ=4的泊松分布P（λ），试用Excel求P（X<6）。

3.已知X～Ν（1.5,22），试用Excel中的统计函数来求：

（1）P（2<ξ≤2.5）；

（2）P（ξ<5）；（3）P（|X-1.5|>2）。

4．利用Excel中的统计函数来计算下列各值

（1）χ20.99（10），χ20.90（12），χ20.01（60），χ20.05（16）；

（2）t0.90（4），t0.01（10），t0.05（12），t0.025（60）；

（3）F0.01（10,9），F0.05（10,9），F0.90（28,2），F0.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（1－α）%置信区间

正

态

分

布

均值

μ

σ2已知

σ2未知

σ2未知（大样本n≥30）

方差

σ2

μ未知

标准差

σ

μ未知

μ未知（大样本n≥30）

下面讨论用Excel软件来求正态总体的总体均值和方差的常用置信区间问题。

§4.1用Excel求σ2已知时总体均值的置信区间

总体方差σ2已知时，求总体均值μ的100（1－α）%的置信区间公式为：

即

。

例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（μ,0.82）。

对于1－α=0.95，则α=0.05，查标准正态分布分位数表得临界值

Zα/2=Z0.025=1.96,

又已知σ=0.8，n=9,故

所以，该药片直径的均值μ的95%置信区间为（13.98，15.02）。

在Excel中，利用样本均值函数AVERAGE和置信区域函数CONFIDENCE就可以分别得到

和

的值，由此即可得到置信区间的上、下限。

其中统计函数AVERAGE和CONFIDENCE的格式分别为：

AVERAGE（number1,number2,...）返回参数平均值（算术平均值）

。

其中Number1,number2,...要计算平均值的1～30个参数。

参数可以是具体数字，或者是涉及数字的名称、数据范围或引用。

CONFIDENCE（alpha,st_dev,size），返回总体均值的置信区域，即样本均值任意一侧的区域大小

。

其中alpha  显著水平α，对应的置信度等于100*（1-α）%，

亦即，如果alpha为0.05，则置信度为95%。

st_dev  数据区域的总体标准差σ，假