Excelの二項分布とベルヌーイ分布：はい・いいえの事象と、n回のうちの回数
ひとことで言うと：ベルヌーイ分布は「起きるか」に1か0で答え、二項分布は「n回のうち何回か」に答えます。
ベルヌーイ分布は、はい・いいえの1回の事象です。リスクが確率pで起きる（1）か、起きない（0）かで、Excelでは =IF(RAND() < p, 1, 0) で引きます。二項分布は、同じ確率で独立なn回の試行のうち、「はい」の回数を数える分布で、=BINOM.INV(n, p, RAND()) で引きます。不良率4%の50個のロットでは、不良品は平均2個で、不良が1つもないロットは13.0%、5個以上のロットは4.9%の確率です。
ベルヌーイ分布：起きるか、起きないか
モデルの不確実性には、金額ではなく事象であるものが多くあります。許可が遅れるかどうか、サプライヤーが失敗するかどうか、登録簿のリスクが起きるかどうかです。ベルヌーイ分布は、確率pで1、それ以外は0になります。これに影響額を掛けると、各試行でのリスクのコストになります。=IF(RAND() < 0.3, 1, 0) * 500 は、試行の約30%で500、残りで0です。
| セル | 数式 | 値 |
|---|---|---|
| 乱数 u（I14） | =RAND() | |
| 引いた値（J14） | =IF(I14<D14, | |
| 1つのセルで引く | =IF(RAND()<D14, | |
| 平均（K14） | =D14 | 0.3 |
| P10 (L14) | =BINOM.INV(1, | 0 |
| P90 (M14) | =BINOM.INV(1, | 1 |
平均はpなので、このリスクの期待コストは0.3 × 500 = 150です。しかし、150かかる試行はありません。どの試行も、0か500のどちらかです。確率×影響額を固定値としてモデルに入れると、まさにこの点が隠れます。こうしたリスクの登録簿でコンティンジェンシーの大きさを決めるのにシミュレーションが要るのは、このためです（リスク登録簿の例を参照）。この行をxellstormで10,000回引くと、リスクは試行の30.0%で起きました。
二項分布：n回のうち何回か
同じ「はい・いいえ」の確率を、独立にn回繰り返し、「はい」の回数を数えると、二項分布になります。ロット内の不良品、オファーを受ける200人中の顧客数、20日のうち雨の日数などです。平均はn × p、標準偏差は√(n × p × (1 − p))で、p = 0.04の50個のロットでは、2と1.39です。ベルヌーイ分布は、n = 1の二項分布です。
グラフの見方：棒は、シミュレーションした10,000ロットのうち、不良品がその個数だったロットの割合、点はその理論上の確率です。
| セル | 数式 | 値 |
|---|---|---|
| 乱数 u（I15） | =RAND() | |
| 引いた値（J15） | =BINOM.INV(D15, | |
| 1つのセルで引く | =BINOM.INV(D15, | |
| 平均（K15） | =D15*F15 | 2 |
| P10 (L15) | =BINOM.INV(D15, | 0 |
| P90 (M15) | =BINOM.INV(D15, | 4 |
数値の確認：理論値と、Excelとxellstormがそれぞれ引いた10,000個の値の比較
| 理論値 | Excel（RAND()） | xellstorm | |
|---|---|---|---|
| 平均 | 2 | 2.01 | 2.00 |
| 標準偏差 | 1.39 | 1.40 | 1.39 |
| P10 | 0 | 0 | 0 |
| P50（中央値） | 2 | 2 | 2 |
| P90 | 4 | 4 | 4 |
Excelは RAND() で完全にランダムに引きます。xellstormは範囲全体に均等に散らして引く（ラテンハイパーキューブサンプリング）ので、統計値が理論値に近くなります。
Excelでの二項分布
BINOM.DIST(k, n, p, FALSE)は、ちょうどk個になる確率です。ロットの不良品がちょうど2個になる確率は27.6%です。TRUEにするとk個以下の確率になるので、=1-BINOM.DIST(4, 50, 0.04, TRUE)は、5個以上になる確率の4.9%を返します。BINOM.INV(n, p, probability)は逆の問いに答えます。指定した確率以上で「これ以下に収まる」と言える、最も小さい個数を返します。パーセンタイルです。RAND()を使うと、ランダムな個数を引けます。数式は=BINOM.INV(D15,F15,RAND())です。引数の順に注意してください。NORM.INVと違い、確率が最後に来ます。旧名はCRITBINOMです。
試行回数が多く確率が小さいと、二項分布は、同じ平均のポアソン分布に近づきます。不良のないロットになる確率は、二項分布で13.0%、平均2のポアソン分布で13.5%です。
xellstormでベルヌーイ分布と二項分布を入力する
- セルを入力にして、「分布」ステップでベルヌーイ分布を選んでpを入力するか、二項分布を選んでnとpを入力します。
- リスクイベントでは、影響額を別のセルに置き（固定値でも、PERTなど別の入力でも）、Excelファイルの中で掛け合わせます。そうすれば、リスクが起きるかどうかと、その大きさが切り離され、感度分析で、結果を左右しているのがどちらかがわかります。
- 過去の0/1の結果があれば、サイドパネルの「データから」でベルヌーイ分布を当てはめます。nがわかっている過去の回数なら、「二項分布：試行回数が既知の場合」にnを入力して、当てはめ直します。
「Excelファイルから取り込み」は、=IF(RAND() < p, 1, 0) をベルヌーイ分布の入力に、=BINOM.INV(n, p, RAND()) を二項分布の入力に変換します。アドイン向けのモデルは、RiskBernoulli、VoseBernoulli、PsiBernoulli、RiskBinomial、VoseBinomial、PsiBinomial から変換できます。
よくある質問
Excelでリスクが起きるかどうかをシミュレーションするには？
確率pで1を返す =IF(RAND() < p, 1, 0) を使い、リスクの影響額を掛けます。再計算を重ねると、1になる割合がpに近づきます。リスクごとに別の RAND() を使えば、リスクは互いに独立に起きます。
ベルヌーイ分布と二項分布の違いは何ですか？
ベルヌーイ分布は1回の試行で、結果は0か1です。二項分布は、同じ確率の独立なn回の試行で、結果は1になった回数（0からn）です。ベルヌーイ分布は、n = 1の二項分布です。
Excelで二項分布の乱数を作るには？
=BINOM.INV(n, p, RAND()) を使います。不良率4%の50個のロットなら =BINOM.INV(50, 0.04, RAND()) です。再計算のたびに0からnまでの新しい整数が出て、その平均はn × p = 2に近づきます。
二項分布とポアソン分布のどちらを使いますか？
機会の数nが決まっていて、どれも同じ確率なら二項分布で、回数はnを超えません。事象が期間内に何回でも、平均頻度で起こりうるならポアソン分布です。nが大きくpが小さいときは、2つはほぼ同じ答えになります。
関連ページ
- ガイド
確率分布の選び方：モンテカルロシミュレーションで使う分布はどれか？
2つの質問で、適した確率分布が見つかります。PERT分布、三角分布、正規分布、対数正規分布、ワイブル分布、ポアソン分布、二項分布、ベータ分布などについて、使いどころとExcelの数式を紹介します。 - リスク登録簿
リスク登録簿のコンティンジェンシー（予備費）はいくら必要か？
確率×最頻の影響額の合計は291ですが、シミュレーションでの平均損失は359、最悪5%の平均は1,436（USD千）です。コンティンジェンシーは最悪側から決めます。 - ガイド
Excelのポアソン分布：事象の回数の扱い方と、負の二項分布の使いどころ
ポアソン分布は、既知の平均頻度で起きる事象の、ある期間内の回数を表します。POISSON.DIST、逆関数なしでExcelでポアソン乱数を作る方法、ばらつきが大きい回数に向く負の二項分布を解説します。
xellstormは、Excelモデル向けのブラウザー型モンテカルロシミュレーションツールです。アドインは不要で、Excelファイルがパソコンの外に出ることはありません。