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

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

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

常用分布概率计算的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某车间有各自独立运行的机床假设干台，设每台机床发生故障的概率为，每

台机床的故障需要一名维修工来排除，试求在以下两种情形下机床发生故障而得不到及时维

修的概率：

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

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

原解：

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

设X表示15台机床中同一时刻发生故障的台数，那么X服从的二项分布：

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（Xw1）=1-P（X=0）-P（X=1）

=1-（0.99）15-15X0.01X（0.99）14

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

丫〜B（80,0.01）

此时因为n=80>30,p=0.01w

所以可以利用泊松近似公式：

当n很大，p较小时（一般只要n>30,pw时），对

任一确定的k,有（其中’=np）

kknk

Cnpq■

ek!

来计算。

由‘=np=80X0.01=0.8,利用泊松分布表，

80

kk80_k

ZC80〔0.01〕〔0.99〕一

P〔Y>4〕=k=4

我们发现，虽然第二种情况平均每人需维修

但是其管理质量反而提高了。

Excel求解：

15台机床中同一时刻发生故障的台数X〜B〔n,p〕,其中

那么所求概率为

所求概率为

-80（0.8）k

z_

k±4k!

27台，比第一种情况增加了

80%的工作量,

n=15,p=0.01,

P（X>2）=1-P（Xw1）=1-P（X=O）-P（X=1）=1-Pi5（0）-Pi5

（1）

利用Excel计算概率值Pi5

（1）的步骤为：

（一）函数法：

在单元格中或工作表上方编辑栏中输入“=BIN0MDIST〔1,15,0.01,0〕〞

单元格即出现P15〔1〕的概率为0.130312〔图3-1〕。

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

后回车，选定

〔二〕菜单法：

1.点击图标话框〔图3-2〕;

『X〞或选择“插入〞下拉菜单的“函数〞子菜单，即进入“函数〞对

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.最后单击"确定〞，相应单元格中就出现只5

（1）的概率。

类似地假设要求已5（0）的概率值，只需直接输入“=BIN0MDIST（0,15,0.01,0）〞或利用菜

单法，在其第3步选项Number_s窗口输入0,即可得概率值，贝U

P（X>2）=1-P15（0）-P15

（1）=1-0.860058-0.130312=0.00963。

另外，P（X>2）=1-P（XW1）=1-F15

（1），即也可以通过先求累积概率F15

（1）来求解。

而要

求出F15

（1）的值，只需在单元格上直接输入“=BINOMDIST（1,15,0.01,1）〞回车即可；或利

用上述菜单法步骤，在第3步的选项Cumulative窗口输入：

1，即得到累积概率卩仆⑴的值

，故有

P（X>2）=1-P（XW1）=1-F15

（1）=1-0.99037=0.00963。

对于例3.1,Y表示80台机床中同一时刻发生故障的台数，那么Y服从n=80、的

二项分布，即丫〜B（80,0.01）。

所求概率为

P（Y>4）=1-P（YW3）=1-F80（3）

利用Excel,在单元格上直接输入“=BIN0MDIST（3,80,0.01,1）〞回车或与上述菜单法类似

操作可得累积概率F80，故所求概率的精确值为

P（Y>4）=1-P（YW3）=1-F80（仁0.00866。

（注意：

例原解中的结果是泊松近似值）

对于泊松分布、正态分布、指数分布等的概率计算步骤与上述二项分布的概率计算过

程类似，只需利用函数法正确输入相应分布的函数表达式即得结果；或在菜单法的第2步选

择POISSON、NORMDIST、EXPONDIST等函数名，根据第3步对话框的指导输入相应的值即可。

下面我们列出这些常用分布的统计函数及其应用。

§3.2泊松分布的概率计算

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

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

其格式为:

POISSONx,meancumulative）

其中x:

事件数；

Mean：

期望值即参数■。

Cumulative:

为逻辑值，假设取值为1或TRUE,那么计算累积概率值P（XWx）,

假设取值为0或FALSE,那么计算随机事件发生的次数恰为x的概率值P（X=x）。

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

P（X=k）=POISSON（k,-,0）;P（X

例如，在例3.1

（2）的原解的泊松近似计算中，Y近似服从・=np=80X的泊松分布P（），需求P（Y>4）。

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

P（Y>4）=1-P（Y<3）=1-0.99092=0.00908。

§3.3正态分布的概率计算

一、NORMDIS函数计算正态分布N（~；」）的分布函数值F（x）和密度值f（x）在Excel中，用函数NORMDIS计算给定均值」和标准差匚的正态分布N（）,■?

）的分布函数值F（x）=P（Xwx）和概率密度函数值f（x）。

其格式为：

NORMDIS（ix,mean,standard_dev,cumulative）

其中x:

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

Mean:

正态分布的均值七

standard_dev:

正态分布的标准差cr;

cumulative:

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

如果取为1或TRUE那么计算分布

函数F（x）=P（Xwx）;如果取为0或FALSE计算密度函数f（x）。

即对正态分布N（J"的分布函数值F（x）和密度函数值f（x），有

F（x）=NORMDIST（x,4匚1）;f（x）=NORMDIST（x,4匚0）

说明：

如果mean=0且standard_dev=1，函数NORMDIST将计算标准正态分布N（0,1）

的分布函数G（x）和密度（x）。

Excel求解例3.2

（1）:

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

P（130wXw150）=F（150）-F（130）

在Excel中，输入“=NORMDIST（150,135,5,1）〞即可得到（累积）分布函数F（150）的值“0.998650〞，或用菜单法进入函数“NORMDIST对话框，输入相应的值（见图3-4）即可得同样结果。

再输入“=NORMDIST（130,135,5,1）〞（或菜单法）得到F（130）的值“0.158655〞，故P（130

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

函数NORMSDIS是用于计算标准正态分布N（0,1）的（累积）分布函数：

.：

」（x）的值，该分布

的均值为0,标准差为1，该函数计算可代替书后附表所附的标准正态分布表。

其格式为

NORMSDISTZ）

其中z:

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

即对标准正态分布N（0,1）的分布函数：

.：

」（x），有

G（x）=NORMSDIST（x）。

例3.3设Z〜N（0,1）,试求P（-2

那么输入“=NORMSDIST

（2）可得门⑵的值“0.97724994〞，输入“=

NORMSDIST（-2）可得①（-2）的值“0.02275006〞,故

P（-2

G

（2）-门（-2）=0.97724994-0.02275006=0.95449988。

三、NORMSIN函数计算标准正态分布N（0,1）的分位数

函数NORMSIN用于计算标准正态分布N（0,1）的（累积）分布函数的逆函数G-1（p）。

即已知概率值>（x）=p，由NORMSINV（p就可以得到x（=G-1（p））的值，该x就是对应于p=1-的标准正态分布N（0,1）分位数Z1--P函数NORMSIN的格式为

NORMSINV（probability）

其中probability:

标准正态分布的概率值p。

那么对标准正态分布N（0,1）的分位数Z有

Z-=NORMSINV（1<）。

Excel求解例3.2

（2）:

在例3.2

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

：

CT

那么由Excel中，，得

5

1.281551

故匚=5/1.281551=3.901522。

§指数分布的概率计算

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

在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服从的指数分布，由

EXP0NDIST（1000,，1）

可得分布函数F（1000）=P（X<1000）的概率值，故所求的概率为P（X>1000）=1-P（X<1000）=1-F（1000）=1-0.632121=0.367879。

§3.52分布的概率计算

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

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

其格式为

CHIDIST（x,deg_freedom）

其中：

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

Deg_freedom/分布的自由度n。

说明：

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

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

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

例如t〜32（15）,要计算P（E2>20）的概率值，那么只要在Excel中，输入函数

“=CHIDIST（20,15）〞即可得到所求值。

即

P（2>20）=。

二、CHIINV函数计算2分布的上侧:

分位数

CHIINV函数用于计算2分布的上侧：

•分位数2-（n）,也就是计算单侧概率的CHIDIST函数的逆函数，即如果：

=CHIDIST（x,n），贝UCHIINV（:

・,n）=x。

该函数的计算可代替概率统

计书后所附的2分布表。

其格式为

CHIINV（a,deg_freedom）

其中.寫为2分布的单侧概率,-；；o

Deg_freedom肇分布的自由度n。

说明：

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

即对2分布的上侧：

•分位数2：

（n），有

2-（n）=CHIINV（:

n）。

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

§3.6t分布的概率计算

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

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

：

=P（t>x）和双侧概率值

：

=P（|t|>x）。

其格式为

TDIST（x,deg_freedom,tails）

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

deg_freedomt分布的自由度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）〞即得。

即

P（|t（60）|>2）=0.050033。

二、TINV函数计算t分布双侧〉分位数

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

P（|t|>t：

/2（n））=:

.（即P（t>t述（n））=：

/2）

的双侧：

•分位数t-/2（n）,也就是计算双侧概率值函数TDIST（:

n,2）的逆函数，即如果

:

=TDIST（x,n,2），那么TINV（二n）=x。

该函数的计算可代替书后t分布表（附表6）。

其格式为

TINV（o,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时,t（10）可由“=TINV（0.05,10）〞得，其值为2.228139；

而t（10）应由“=TINV（0.05*2,10）〞得，其值为。

对，n=50时，t（50）由“=TINV（0.05*2,50）〞得，其值为。

而TINV（0.05,50），是t（50）（~Z°.025=1.96）的值。

§3.7F分布的概率计算

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

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

：

=P（F>x）。

其格式为

FDIST（x,deg_freedom1,deg_freedom2）

其中：

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

Deg_freedom1F分布的第一（分子）自由度n1;Deg_freedom2F分布的第二（分母）自由度n2。

说明：

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

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

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

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

P（F（10,5）>0.3）=。

二、FINV函数计算F分布的上侧分位数

FINV函数用于计算F分布的上侧「分位数F：

.（n1,n2）,也就是计算单侧概率的FDIST函

数的逆函数，即如果：

=FDIST（x,n1,n2），那么FINV（〉,n1,n2）=x。

FINV函数的计算可代替书后所附的F分布表。

其格式为

FINV（口,deg_freedom1,deg_freedom2）

其中

a

Deg_freedom1

Deg_freedom2

说明：

如果deg_freedom1

即对F分布的上侧

对应于F分布的单侧概率值；

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

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

或deg_freedom2不是整数，将被截尾取整。

:

-分位数F-（n1,n2），有

F（n1,n2）=FINV（:

n1,n?

）。

例如，对，F°.05（10,5）可由“=FINV（0.05,10,5）〞得，其值为4.735057；

而F0.05（5,10）那么由“=FINV（0.05,5,10）〞得，其值为。

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

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

分布

Excel统计函数

对应概率值

Excel统计函数

对应分位数

正态分布N（U

NORMDIST（x,!

4q0）

NORMDIST（x,!

4

正态密度f（x）

P（X

NORMINV（p,Rn）

■1

X1-p=F1（p）

标准正态分布

N（0,1）

NORMSDIST（x）

P{Z

NORMSINV（p）

乙-p（=E（p））

里分布严（n）

CHIDIST（x,n）

P{疋（n）>x}

CHIINV（qn）

誓&n）

T分布t（n）

TDIST（x,n,1）

P{t（n）>x}

TINV（gn）

to/2（n）

TDIST（x,n,2）

P{|t（n）|>x}

TINV（Ot*2,n）

d）

F分布F（n1,%）

FDIST（x,n1,n2）

P{F（n1,n2）>x}

FINV（am,n2）

F^n1,n2）

上机训练题二

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

。

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

能正常工作。

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

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

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

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

3.X〜N（1.5,2），试用Excel中的统计函数来求：

（1）P（2

（2）P（E<5）;（3）P（|X-1.5|>2）

4.利用Excel中的统计函数来计算以下各值

（1）2（10），/（12）,Z；01（6O）,『（16）；

（2）t（4）,t（1O）,t（12）,t（6O）；

（3）F（1O,9）,F（1O,9）,F（28,2）,F（1O,8）。

5.用Excel求以下各分布的概率值

（1）P（^（21）>10）;P（於（21）V15）;

（2）P（t（4）>3）;P（|t（4）|V）;

（3）P（F（4,12）V5）;P（F（4,12）>3）。

上机实习四用Excel求正态总体参数的置信区间

首先我们列出求解单个总体常用参数的置信区间简要结果表，可供查阅。

表4-1单个总体参数的100〔1-：

•〕%置信区间

总体

参数

条件

100（1-ot）%置信区间

正态分布

均值

O

—（J

X士Z0（/2

"n

CT未知

"a"梟

2

a未知〔大样本n?

30〕

-S

Pn

方差

2

CT

4未知

（（n-1）S2（n-1）S2）

（了2,了2丿

丄孕Z1XX

标准差

4未知

（SJ72'^72）

P未知〔大样本n>30〕

s+z丄

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

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

总体方差孑时，求总体均值.L的100〔1—:

■〕%的置信区间公式为：

—CT

（X-Z：

/2—,

X'Z：

./2——）

例设某药厂生产的某种药片直径

X是一随机变量，服从方差为2的正态分布。

现从某日生产的药片中随机抽取9片，测得其直径分别为〔单位：

mm〕

14.1,14.7,14.7,14.4,14.6,14.5,14.5,14.8,14.2,试求该药片直径的均值J的95%置信区间。

解：

对药片直径X,X服从NQ2〕。

对于1—：

，那么：

，查标准正态分布分位数表得临界值

Z-12=Z=1.96,

又；「=,n=9,故

X_Z../2——:

14.5——:

14.5_1.960.08=14.5_

Jnv9

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

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

—Zr.、/2―

到x和-.n的值，由此即可得到置信区间的上、下限。

其中统计函数AVERAGE和CO