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 の値を取る論理変数

使用例

次の事例で説明します。
  • 野球で、"3 割打者" は、打席 (各試行) ごとに 0.300 の確率でヒット (成功) を打ちます。
  • 連続する複数回の打席は、独立したベルヌーイ試行です。
以下の表を使用して、このような打者が、10 回の試行で正確に 0、1、2、... 10 本のヒットを打つ確率と、10 回の試行で 0、1 以下、2 以下、... 9 以下、10 以下の本数のヒットを打つ確率を求めることができます。

この打者が最初の 200 回の試行で 50 本のヒットを打った (この時点では打率 2 割 5 分) 場合、次の 300 回の試行では 100 本のヒットを打ち、通算すると 500 回の試行で 150 本のヒット (打率 3 割) を打つ可能性が高くなります。以下の表を使用して、打者が自分の打率を維持するのに必要なヒットを打つ可能性を分析できます。野球の解説者は、最初の 200 打席で 50 本のヒットしか打たなかった打者の成績について、"シーズンの終わりまでには彼の打率はきっと 3 割になるだろう" から心配する必要はないと言うときに、よく "打率からすると" と口にします。各試行が完全に独立しており、打者がいずれの試行でも実際に 0.3 の成功率を持つ場合、この推論は誤りです。それは、最初の 200 回の試行の結果が残りの 300 回の試行の成功または失敗に影響することはないからです。

BINOMDIST を使用するには、まず、空の Excel ワークシートを作成します。以下の表をコピーし、空の Excel ワークシートのセル A1 をクリックし、エントリを貼り付けます。これにより、以下の表の各項目がワークシートのセル A1:C22 に入力されます。
元に戻す全体を表示する
試行数 10
成功率 0.3
成功数 x P(正確な成功数 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
成功数 x P(正確な成功数 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 およびそれ以前の 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 またはそれ以下の回数の成功が得られる確率は、C20 に示されているように 50% を少し上回る値です。99 またはそれ以下の回数の成功が得られる確率は、およそ 0.884 です。したがって、100 回以上の成功数が得られる確率は 11.6% (0.116 = 1 ? 0.884) にすぎません。

以前のバージョンの Excel の計算結果

BINOMDIST が数値解を返さず、代わりに、数値オーバーフローによる #NUM! が表示されるという事例が Knusel (注 1 参照) により文書化されています。BINOMDIST が数値解を返すときには、それらは正しい答えです。BINOMDIST が #NUM! を返すのは、試行回数が 1030 以上になるときだけです。n < 1030 の場合には、計算上の問題はありません。実際には、n がこのような大きな値になることはほとんどありません。独立試行の回数がこのような大きな値になると、二項分布を正規分布やポアソン分布などに近似させることもできます (n*p および n*(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 は、n 項目の母集団からの x 項目の組み合わせの数を返す Excel 関数です。COMBIN(n,x) は nCx と記述して "二項係数" と呼ぶ場合や、単に "n choose x" と記述する場合があります。任意のセルに =COMBIN(1029,515) と入力し、別のセルに =COMBIN(1030,515) と入力して COMBIN を実行すると、1 つ目のセルでは 1.4298E+308 という非常に大きな値が返され、2 つ目のセルでは値が大きすぎるために #NUM! が表示されます。以前のバージョンの Excel では、COMBIN のオーバーフローは BINOMDIST のオーバーフローの原因になります。

COMBIN は、Excel 2003 およびそれ以降のバージョンの Excel では変更されていません。

Excel 2003 およびそれ以降のバージョンの Excel の結果

マイクロソフトでは、オーバーフローにより BINOMDIST が #NUM! を返すのはどのような場合であるかを調査し、オーバーフローが発生していないときには BINOMDIST は正常に機能することを確認しました。このため、Excel 2003 およびそれ以降のバージョンの Excel では条件付きのアルゴリズムが実装されました。

このアルゴリズムは、n < 1030 の場合は、以前のバージョンの Excel の BINOMDIST コード (この資料の前半で説明している計算式) を使用します。n >= 1030 の場合は、この資料の後半で説明している別のアルゴリズムを使用します。

通常、COMBIN のオーバーフローの原因は値が大きすぎるためですが、p^x および (1-p)^(n-x) はいずれも無限小の値です。これらの値の積を求めることができると仮定すると、実際の確率は 0 か 1 のいずれかの値になります。しかし、実際には計算上の制約が存在し、これらを掛け合わせることはできないため、この新しいアルゴリズムでは COMBIN の評価を行いません。

マイクロソフトの手法では、正確に x 回の成功が得られる確率の合計をスケーリングせずに算出し、この値を後でスケーリングに使用します。また、BINOMDIST で返されることが期待されている確率の値を、スケーリングせずに算出します。最後に、スケーリング係数を使用して、BINOMDIST の結果として正しい値を返します。

このアルゴリズムは、COMBIN(n,k)*(p^k)*((1-p)^(n-k)) という表現で得られる連続する項の比率は、単純な表現形式を持つという事実を利用しています。次の手順のコードは、このアルゴリズムの処理を示すものです。

手順 0 : 初期化処理。TotalUnscaledProbability プロパティと UnscaledResult プロパティを 0 に初期化します。定数 EssentiallyZero を 10^(-12) などの非常に小さな数値に初期化します。

手順 1 : n*p を求め、切り捨てを行って最も近似する整数 m にします。n 回の試行を行うときに最も可能性の高い成功数は、m または m+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) を計算できます (野球の例では、3 割打者が 10 回の試行を行うと、3 本またはそれ以下の本数のヒットを打つ可能性があることになります)。

実際に確認するには、以下の表をコピーし、前に作成した Excel ワークシートでセル D4 をクリックし、エントリを貼り付けます。これにより、以下の表の各項目がワークシートのセル D4: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. の計算結果です。手順 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 の設定値によっては、手順 2. が 805 に到達する前に中断されたり、手順 3. が 395 に到達する前に中断されたりする可能性があります。

まとめ

Excel 2003 よりも前のバージョンの Excel で不正確な値が生じるのは、試行回数が 1030 以上の場合のみです。以前のバージョンの Excel でこの不具合が発生すると、BINOMDIST で #NUM! が返されます。これは、互いに掛け合わせる連続する項のうちの 1 つの項がオーバーフローするためです。この動作を修正するため、Excel 2003 およびそれ以降のバージョンの Excel では、このようなオーバーフローが発生すると考えられる場合は、この資料の後半で説明した別の手順を使用します。

CRITBINOM、HYPGEOMDIST、NEGBINOMDIST、および POISSON の各関数は、以前のバージョンの Excel では類似した現象を示します。これらの関数も同様に、正しい数値解か、そうでなければ #NUM! または #DIV/0! を返します。この場合も、この問題の原因はオーバーフローまたはアンダーフローです。

この問題が発生する条件は簡単に確認できます。Excel 2003 およびそれ以降のバージョンの Excel では BINOMDIST と類似した別のアルゴリズムが使用されており、以前のバージョンでは #NUM! が返されていた場合でも正しい解が返されます。

プロパティ

文書番号: 827459 - 最終更新日: 2007年3月20日 - リビジョン: 4.0
この資料は以下の製品について記述したものです。
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
キーワード:?
kbexpertisebeginner kbinfo KB827459
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

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