Excel 统计函数:BINOMDIST

文章翻译 文章翻译
文章编号: 827459 - 查看本文应用于的产品
展开全部 | 关闭全部

本文内容

概要

本文介绍了 Microsoft Office Excel 2003 及更高版本的 Excel 中的 BINOMDIST 函数,说明了如何使用此函数,并且将 Excel 2003 及更高版本的 Excel 中的此函数结果与更早版本的 Excel 中的此函数结果进行了比较。

Microsoft Excel 2004 for Mac 信息

更新 Excel 2004 for Mac 中的统计函数时所使用的算法与更新 Excel 2003 和更高版本的 Excel 中的统计函数时所使用的算法相同。本文中介绍函数工作方式或者如何针对 Excel 2003 及更高版本的 Excel 修改函数的所有信息同样适用于 Excel 2004 for Mac。

更多信息

cumulative = TRUE 时,BINOMDIST(x, n, p, cumulative) 函数返回在 n 次独立的贝努利试验中成功次数小于或等于 x 的概率。每次试验都有关联的成功概率 p(失败概率为 1-p)。当 cumulative = FALSE 时,BINOMDIST 返回成功次数恰好为 x 的概率。

语法

BINOMDIST(x, n, p, cumulative)

参数

  • x 是一个非负整数
  • n 是一个正整数
  • 0 < p < 1
  • Cumulative 是一个逻辑变量,值为 TRUE 或 FALSE

用法示例

作出以下假设:
  • 在棒球运动中,“.300 击球员”每次击球(每次试验)时击中(成功)的概率为 0.300。
  • 连续的各次击球就是独立的贝努利试验。
您可以使用下表来查找这样一位击球员在 10 次试验中击中次数恰好等于 0、1、2、… 或 10 的概率,以及此击球员在 10 次试验中击中次数等于 0、小于或等于 1、小于或等于 2、…、小于或等于 9、小于或等于 10 的概率。

如果此击球员在他的前 200 次试验中击中了 50 次(平均击中率为 .250),则他必须在接下来的 300 次试验中击中 100 次以使击中总数达到 150 次,这样才能在 500 次试验中保持平均击中率为 .300。您可以使用下表来分析此击球员获得足以保持其平均水平的击中次数的概率。当此击球员在他的前 200 次试验中只击中了 50 次时,棒球讲解员经常会提到“平均击中率规律”,以告知球迷们不必担心此击球员的能力,因为“到赛季结束时,他的平均击中率将达到 .300”。如果试验确实是独立的,击球员在任一次试验中的成功概率确实为 0.3,则这种推理是不合理的,因为前 200 次试验的结果不会影响后 300 次试验的成功或失败。

要说明 BINOMDIST 的使用,请创建一个空白 Excel 工作表,复制下表,选中空白 Excel 工作表中的单元格“A1”,然后粘贴各项,这样下表将填满工作表中的单元格 A1:C22。
收起该表格展开该表格
试验次数10
成功概率0.3
成功次数 x成功次数恰好等于 x 的概率成功次数小于或等于 x 的概率
0=BINOMDIST(A4,$B$1,$B$2,FALSE)=BINOMDIST(A4,$B$1,$B$2,TRUE)
1=BINOMDIST(A5,$B$1,$B$2,FALSE)=BINOMDIST(A5,$B$1,$B$2,TRUE)
2=BINOMDIST(A6,$B$1,$B$2,FALSE)=BINOMDIST(A6,$B$1,$B$2,TRUE)
3=BINOMDIST(A7,$B$1,$B$2,FALSE)=BINOMDIST(A7,$B$1,$B$2,TRUE)
4=BINOMDIST(A8,$B$1,$B$2,FALSE)=BINOMDIST(A8,$B$1,$B$2,TRUE)
5=BINOMDIST(A9,$B$1,$B$2,FALSE)=BINOMDIST(A9,$B$1,$B$2,TRUE)
6=BINOMDIST(A10,$B$1,$B$2,FALSE)=BINOMDIST(A10,$B$1,$B$2,TRUE)
7=BINOMDIST(A11,$B$1,$B$2,FALSE)=BINOMDIST(A11,$B$1,$B$2,TRUE)
8=BINOMDIST(A12,$B$1,$B$2,FALSE)=BINOMDIST(A12,$B$1,$B$2,TRUE)
9=BINOMDIST(A13,$B$1,$B$2,FALSE)=BINOMDIST(A13,$B$1,$B$2,TRUE)
10=BINOMDIST(A14,$B$1,$B$2,FALSE)=BINOMDIST(A14,$B$1,$B$2,TRUE)
300 次试验,成功概率为 0.3:
成功次数 x成功次数恰好等于 x 的概率成功次数小于或等于 x 的概率
89=BINOMDIST(A18,300,0.3,FALSE)=BINOMDIST(A18,300,0.3,TRUE)
90=BINOMDIST(A19,300,0.3,FALSE)=BINOMDIST(A19,300,0.3,TRUE)
99=BINOMDIST(A20,300,0.3,FALSE)=BINOMDIST(A20,300,0.3,TRUE)
100=BINOMDIST(A21,300,0.3,FALSE)=BINOMDIST(A21,300,0.3,TRUE)
101=BINOMDIST(A22,300,0.3,FALSE)=BINOMDIST(A22,300,0.3,TRUE)
注意:在将此表粘贴到新的 Excel 工作表中后,单击“粘贴选项”按钮,然后单击“匹配目标格式”。在粘贴区域仍处于选中状态的情况下,针对所运行的 Excel 版本执行下列过程之一:
  • 在 Microsoft Office Excel 2007 中,单击“开始”选项卡,单击“单元格”组中的“格式”,然后单击“自动调整列宽”。
  • 在 Excel 2003 及更早版本的 Excel 中,指向“格式”菜单上的“列”,然后单击“最适合的列宽”。
您可能希望对单元格 B4:C22 进行格式设置以实现一致的可读性(例如,将数字格式设置为保留 5 位小数)。

单元格 B4:B14 显示在 10 次试验中成功次数恰好等于 x 的概率。最可能的成功次数为 3。成功次数等于 0、6、7、8、9 或 10 的概率都小于 0.05,共计约为 0.076。因此,成功次数等于 1、2、3、4 或 5 的概率约为 1 – 0.076 = 0.924。单元格 C4:C14 显示在 10 次试验中成功次数小于或等于 x 的概率。您可以验证任意行中的列 C 中的项是否等于该行及以下各行的列 B 中的所有项之和。

B18:B20 显示了在 300 次试验中最可能的成功次数是 90。成功次数恰好等于 x 的概率随着 x 逐渐增大到 90 而逐渐增大;然后随着 x 超过 90 继续增大而逐渐减小。成功次数小于或等于 90 的概率稍高于 50%,如 C20 所示。成功次数小于或等于 99 的概率约为 0.884。因此,成功次数大于或等于 100 的概率只有 11.6% (0.116 = 1 – 0.884)。

早期版本的 Excel 中的结果

Knusel(请参阅“注释 1”)记录了因数值溢出 BINOMDIST 没有返回数值答案而是生成 #NUM! 的实例。当 BINOMDIST 返回数值答案时,这些答案是正确的。只有当试验次数大于或等于 1030 时,BINOMDIST 才会返回 #NUM!。如果 n < 1030,则不会出现计算问题。在实际情况中,n 的值不可能如此之高。在独立试验的次数相当高的情况下,用户可能想要通过一个正态分布(如果 n*pn*(1-p) 足够大,例如两个值都大于 30)或泊松分布以接近二项式分布。

注释 1:Knusel L. 的“On the Accuracy of Statistical Distributions in Microsoft Excel 97”(Microsoft Excel 97 统计分布的精确性),Computational Statistics and Data Analysis(计算统计学与数据分析)(1998),26:375-377。

对于非累积情况,BINOMDIST(x, n, p, false) 使用以下公式
COMBIN(n,x)*(p^x)*((1-p)^(n-x))
COMBIN 是一种 Excel 函数,提供从总体 n 项中取出 x 项的组合数。COMBIN(n,x) 有时写为 nCx,称为“组合系数”或仅仅是“n choose x”。如果您在试用 COMBIN 时在一个单元格中键入了 =COMBIN(1029,515),在另一个不同的单元格中键入了 =COMBIN(1030,515),则第一个单元格将返回一个极大的数字 1.4298E+308,而第二个单元格返回 #NUM!,因为它要更大得多。在更早版本的 Excel 中,COMBIN 的溢出会导致 BINOMDIST 溢出。

对于 Excel 2003 及更高版本的 Excel 尚未对 COMBIN 进行修改。

Excel 2003 及更高版本的 Excel 中的结果

因为 Microsoft 已诊断出溢出何时会导致 BINOMDIST 返回 #NUM!,并且知道 BINOMDIST 在没有发生溢出时运行良好,所以 Microsoft 在 Excel 2003 及更高版本的 Excel 中执行了条件算法。

n < 1030 时,此算法使用更早版本的 Excel 中的 BINOMDIST 代码(本文前面提到的计算公式)。当 n >= 1030 时,Excel 2003 及更高版本的 Excel 使用将在本文后面介绍的替代算法。

通常,COMBIN 发生溢出的原因是它的数值太大,而 p^x 和 (1-p)^(n-x) 都是无穷小。如果可以将它们相乘,则乘积将是一个介于 0 和 1 之间的有现实意义的概率。不过,因为现有的有限算法无法将它们相乘,所以替代算法避免了 COMBIN 的计算。

Microsoft 的方法可计算成功次数恰好等于 x 的所有概率的未经换算的和,随后将这些概率用于换算。此方法还可计算您希望 BINOMDIST 返回的概率的未经换算的值。最后,此方法使用换算系数返回正确的 BINOMDIST 值。

此算法利用了 COMBIN(n,k)*(p^k)*((1-p)^(n-k)) 形式的邻项比率的形式简单这一事实。此算法的执行过程如下列步骤中的伪代码所示。

步骤 0:(初始化)。将 TotalUnscaledProbabilityUnscaledResult 属性初始化为 0。将常量 EssentiallyZero 初始化为一个非常小的数,如 10^(-12)。

步骤 1:得到 n*p 并将其向下舍入为最接近的整数 m。在 n 次试验中最可能的成功次数为 mm+1。随着 km 减小到 m-1,进而减小到 m-2,依此类推,COMBIN(n,k)*(p^k)*((1-p)^(n-k)) 会越来越小。同样,随着 km+1 增大到 m+2,进而增大到 m+3,依此类推,COMBIN(n,k)*(p^k)*((1-p)^(n-k)) 也会越来越小。
TotalUnscaledProbability = TotalUnscaledProbability + 1;
If (m == x) then UnscaledResult = UnscaledResult + 1;
If (cumulative && m < x) then UnscaledResult = UnscaledResult + 1;
步骤 2:计算 k > m 的未经换算的概率:
PreviousValue = 1;
Done = FALSE;
k = m + 1;
While (not Done && k <= n)
  {
	CurrentValue = PreviousValue * (n – k + 1) * p / (k * (1 – p));
	TotalUnscaledProbability = TotalUnscaledProbability + CurrentValue;
	If (k == x) then UnscaledResult = UnscaledResult + CurrentValue;
	If (cumulative && k < x) then UnscaledResult = UnscaledResult + 
		CurrentValue;
	If (CurrentValue <= EssentiallyZero) then Done = TRUE;
	PreviousValue = CurrentValue;
	k = k+1;
  }
end While;
步骤 3:计算 k < m 的未经换算的概率:
PreviousValue = 1;
Done = FALSE;
k = m - 1;
While (not Done && k >= 0)
  {
	CurrentValue = PreviousValue * k+1 * (1-p) / ((n – k) * p);
	TotalUnscaledProbability = TotalUnscaledProbability + CurrentValue;
	If (k == x) then UnscaledResult = UnscaledResult + CurrentValue;
	If (cumulative && k < x) then UnscaledResult = UnscaledResult + 
		CurrentValue;
	If (CurrentValue <= EssentiallyZero) then Done = TRUE;
	PreviousValue = CurrentValue;
	k = k-1;
  }
end While;
步骤 4:组合未经换算的结果:
Return UnscaledResult/TotalUnscaledProbability;
虽然此方法只用于 n >= 1030 的情况,但是您可以向 Excel 工作表中添加以下数据,这些数据可帮助您手动执行此算法以计算 BINOMDIST(3, 10, 0.3, TRUE)(在棒球运动示例中,.300 击球员在 10 次试验中击中次数小于或等于 3 的概率)。

为了说明这一点,请复制下表,选中您在前面创建的 Excel 工作表中的单元格“D4”,然后粘贴各项,这样下表将填满工作表中的单元格 D1:E15。
收起该表格展开该表格
=D5*(1-$B$2)*(A4+1)/($B$2*($B$1-A4))=D4/$D$15
=D6*(1-$B$2)*(A5+1)/($B$2*($B$1-A5))=D5/$D$15
1=D6/$D$15
=D6*$B$2*($B$1-A7+1)/((1-$B$2)*A7)=D7/$D$15
=D7*$B$2*($B$1-A8+1)/((1-$B$2)*A8)=D8/$D$15
=D8*$B$2*($B$1-A9+1)/((1-$B$2)*A9)=D9/$D$15
=D9*$B$2*($B$1-A10+1)/((1-$B$2)*A10)=D10/$D$15
=D10*$B$2*($B$1-A11+1)/((1-$B$2)*A11)=D11/$D$15
=D11*$B$2*($B$1-A12+1)/((1-$B$2)*A12)=D12/$D$15
=D12*$B$2*($B$1-A13+1)/((1-$B$2)*A13)=D13/$D$15
=D13*$B$2*($B$1-A14+1)/((1-$B$2)*A14)=D14/$D$15
=SUM(D4:D14)
列 D 包含未经换算的概率。单元格 D6 中的 1 是此算法的步骤 1 的结果。Excel 2003 及更高版本的 Excel 在步骤 2 中计算单元格 D7、D8、…、D14(按此顺序)中的项。Excel 在步骤 3 中计算单元格 D5 和 D4(按此顺序)中的项。所有未经换算的概率之和显示在 D15 中。

要计算成功次数小于或等于 3 的概率,请在任意空白单元格中键入以下公式:
= SUM(D4:D7)/D15
在前面的示例中,EssentiallyZero 没有停止步骤 2 或 3。但是,如果您要计算 BINOMDIST(550, 2000, 0.3, TRUE),EssentiallyZero 可能会停止步骤 2 或步骤 3。n = 2000、p = 0.3 的二元随机变量的分布近似于平均值为 600、标准偏差 SQRT(2000*0.3*(1 – 0.3)) = SQRT(420) = 20.5 的正态分布。那么,805 对应于高于平均值的 10 个标准偏差,395 对应于低于平均值的 10 个标准偏差。根据 EssentiallyZero 的设置,EssentiallyZero 可能在达到 805 之前就停止了步骤 2,在达到 395 之前就停止了步骤 3。

结束语

只有当试验次数大于或等于 1030 时,才会在早于 Excel 2003 的 Excel 版本中出现不准确性。在此类情况下,更早版本的 Excel 中的 BINOMDIST 返回 #NUM!,因为相乘的几项中有一项溢出。为了纠正此行为,Excel 2003 及更高版本的 Excel 在发生此类溢出时使用本文前面提到的替代过程。

在更早版本的 Excel 中,CRITBINOM、HYPGEOMDIST、NEGBINOMDIST 和 POISSON 函数表现出类似的行为。这些函数也可返回正确的数值结果或 #NUM! 或 #DIV/0!。同样,问题发生的原因也是溢出(或下溢)。

确定这些问题发生的时间和方式是很容易的。在更早版本的 Excel 返回 #NUM! 的情况下,Excel 2003 及更高版本的 Excel 使用类似于 BINOMDIST 所用的替代算法以返回正确答案。

属性

文章编号: 827459 - 最后修改: 2007年2月26日 - 修订: 4.0
这篇文章中的信息适用于:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
关键字:?
kbinfo kbexpertisebeginner KB827459
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com