多項分布を使って人手を確保できる確率を計算する方法をExcelで実演
人手を確保できる確率を人数別に知りたい場面
ある物流センターでは毎日アルバイトを雇っています。
本当は毎日50人必要なのですが、そこまで集められず慢性的な人手不足です。
過去1か月間の出勤記録を調べたところ、確保できた人数は次の通りでした。
今後1週間で毎日35人以上集められる確率は何％でしょうか？
また、95%以上の確信度を持って確保できる毎日の平均人数は何人でしょうか？
Excel VBAで確保人数の組み合わせを列挙する
確保できる人数が何通りかあって、それぞれの確率が分かっているので、この問題は多項分布を使えば解けます。
多項分布の計算式と使い方を具体例を使ってわかりやすく解説します。
状態1、2、…、k をとる確率が p1 、p2 、…、pk のとき ( p1＋p2＋…＋pk＝1 ) 、N 回の試行で各状態を ni回 ( n1＋n2＋…＋nk＝N ) とる確率は次式で表されます。
N!/n1! … nk! (p1n1 … pknk)
ただし、確保人数について毎日7つの可能性があって、それが7日間続きますので、組み合わせの数は7の7乗＝823,543通りもあります。
これらすべてを虱潰しに調べることは、さすがに人手では無理ですね。
そこでExcel VBAの力を借りましょう。
こういうことはマクロ（Excel VBA)が最も得意とすることです。
まず確保できる人数は7通りのパターンがありますので、それぞれのパターンをp1からp7と名付けます。
私たちが知りたいのは、7日間のうちでp1～p7がそれぞれ何回出現するのかです。
従って、下記の条件を満たす7桁の数字をすべて調べることになります。
これをVBAで計算させるには、次のようにします。
- 1～6桁目が0の時に7桁目を0～7に変化させ、合計が7になる数字を探す
- 0000007だけなので、これをエクセルシートの(2,1)セルに書き込む
- 1～5桁目が0で6桁目が1の時に7桁目を0～7に変化させ、合計が7になる数字を探す
- 0000016だけなので、これをエクセルシートの(3,1)セルに書き込む
- 以上をすべての組み合わせについて繰り返す
サンプルプログラムを以下に示します。
Sub Macro1()
Dim CBN As String
Dim a1 As Integer
Dim a2 As Integer
Dim a3 As Integer
Dim a4 As Integer
Dim a5 As Integer
Dim a6 As Integer
Dim a7 As Integer
Dim XX As Long
Dim IG As Integer
XX = 1
For a1 = 0 To 7
For a2 = 0 To 7
For a3 = 0 To 7
For a4 = 0 To 7
For a5 = 0 To 7
For a6 = 0 To 7
For a7 = 0 To 7
If a1 + a2 + a3 + a4 + a5 + a6 + a7 <> 7 Then
IG = 1
Else
IG = 0
End If
If IG = 0 Then
CBN = a1 & a2 & a3 & a4 & a5 & a6 & a7
For I = 1 To XX
If Range(Cells(XX, 1), Cells(XX, 1)) = CBN Then
Else
XX = XX + 1
Range(Cells(XX, 1), Cells(XX, 1)) = CBN
End If
Next I
End If
Next a7
Next a6
Next a5
Next a4
Next a3
Next a2
Next a1
End Sub
これを実行すると、このような結果がシートに書き込まれます。
1716通りの組み合わせを、ものの数秒で出してくれました。
VBAって便利ですねえ。
多項分布で確率を計算する
毎日35人以上集められる確率
次に、それぞれの組み合わせについて確率を計算していきます。
計算自体は多項分布の式
N!/n1! … nk! (p1n1 … pknk)
に代入するだけです。
エクセルで計算すると、このようになります。
例えば、7日間のうち、45～49人を確保できた日が5日、50人以上を確保できた日が2日になる確率は0.0067%となります。
次に、毎日35人以上確保できる確率を求めます。
毎日34人以下しか手配できないことは許されないので、p1～p3はすべてゼロになるはずです。
従って、下記のように計算すれば、L列には35人以上確保できるパターンだけについて確率が書き込まれます。
そしてL列の確率をすべて合計すると21%になります。
従って、1週間のうち毎日35人以上を確保できる確率は21%しかありません。
95%以上の確率で確保できる平均人数
次に毎日確保できる人数の期待値を計算します。
期待値とは、確率も考慮した平均値ということです。
確率の高い値については、その分重みを付けて平均を取ります。
これは次のように計算できます。
次に期待値の大きい順に並べ替えます。
M列をキーにして降順で並べ替えをします。
それが終わったら、累積確率を下記のようにして書き込みます。
累積確率の列をスクロールしながら見ていくと、次のように95%になる行が見つかります。
その時の期待値は35.57です。
つまり、95%以上の確信度を持って確保できる毎日の平均人数は35人ということになります。
多項分布は二項分布の拡張版
Excel VBAも組み合わせたので少し難しくなってしまいましたが、多項分布はいろいろな場面に使えそうなことを感じていただけたでしょうか？
同じような確率分布に二項分布があり、実際、こちらの方が有名です。
しかし、多項分布は二項分布の拡張版です。
言い換えると、多項分布の式にk=2を代入した特殊ケースが二項分布になります。
ですので、多項分布の方が応用範囲が広いのです。
その他、離散確率分布としてはベルヌーイ分布やカテゴリ分布も有名ですが、これらもすべて多項分布の特殊ケースです。
従って、これら４つはまとめて勉強すると理解が進みます。
興味ある方は、こちらも読んでみて下さい。
ベルヌーイ分布／二項分布／カテゴリ分布／多項分布の関係をまとめてみた。