Excel 統計函數:BINOMDIST

文章翻譯 文章翻譯
文章編號: 827459 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

在此頁中

結論

本文將告訴您 Microsoft Office Excel 2003 和更新版本中的 BINOMDIST 函數、示範如何使用此函數,以及比較此函數在 Excel 2003 和更新版本與在舊版 Excel 中的結果。

Microsoft Excel 2004 for Mac 資訊

Excel 2004 for Mac 中的統計函數和 Excel 2003 和更新版本中的統計函數都是使用相同的演算法來更新的。本文中對於函數如何運作的說明,或是在 Excel 2003 和更新版本中修改函數的所有資訊,都適用於 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
成功,xP(確實成功 x 次)P(成功 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:
成功,xP(確實成功 x 次)P(成功 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 和舊版中,指向 [格式] 功能表上的 [欄],然後按一下 [最適欄寬]
您可能會想要格式化儲存格 B4:C22,以達到一致的可讀性 (例如,將數字格式化為五位小數點)。

儲存格 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 增加至 90 時,確實成功 x 次的機率增加,然後在 x 繼續增加至超過 90 時減少。90 或更少成功次數的機率正好超過 50%,如 C20 所示。99 或更少成功次數的機率約為 0.884。因此,只有 11.6% 的機會 (0.116 = 1 – 0.884) 可以有 100 或更多成功次數。

舊版 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", 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 函數,用於計算 x 項目在 n 項目總數中的組合數目。COMBIN(n,x) 有時寫成 nCx,且名為「組合係數」或只稱為「nx」。如果您嘗試使用 COMBIN,在一個儲存格中輸入 =COMBIN(1029,515) 而在另一個儲存格中輸入 =COMBIN(1030,515),第一個儲存格會傳回天文數字 1.4298E+308,第二個儲存格則會傳回 #NUM!,因為它更大。在舊版 Excel 中,COMBIN 的溢位會造成 BINOMDIST 的溢位。

COMBIN 在 Excel 2003 或更新版本中尚未修改。

Excel 2003 和更新版本中的結果

由於 Microsoft 診斷出溢位何時會造成 BINOMDIST 傳回 #NUM!,也知道 BINOMDIST 在沒有發生溢位時運作良好,因此 Microsoft 已在 Excel 2003 和更新版本中實作一種條件演算法。

此演算法在 n < 1030 時,使用舊版 Excel 中的 BINOMDIST 程式碼 (本文稍早已提及計算公式)。當 n >= 1030 時,Excel 2003 和更新版本則會使用本文稍後所述的替代演算法。

一般來說,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 和更新版本在步驟 2 中會計算儲存格 D7、D8...D14 (依此順序) 中的項目,在步驟 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 中才會出現錯誤。在這種情況下,BINOMDIST 在舊版 Excel 中會傳回 #NUM!,因為多項相乘的其中一項發生溢位。為了更正這個問題,Excel 2003 和更新版本會在發生溢位時,使用本文稍早所述的替代程序。

CRITBINOM、HYPGEOMDIST、NEGBINOMDIST 和 POISSON 函數在舊版 Excel 中都有類似的問題。這些函數也會傳回正確的數值結果,或者傳回 #NUM! 或 #DIV/0!。再次重申,問題發生是因為溢位 (或反向溢位) 所致,

您很容易便可以判斷這些問題是何時以及如何發生。Excel 2003 和更新版本是使用類似讓 BINOMDIST 在舊版傳回 #NUM! 時傳回正確答案的替代演算法。

屬性

文章編號: 827459 - 上次校閱: 2007年3月14日 - 版次: 4.0
這篇文章中的資訊適用於:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
關鍵字:?
kbexpertisebeginner kbinfo 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