多項分布を使って人手を確保できる確率を計算する方法をExcelで実演

2022年3月2日

スポンサーリンク

人手を確保できる確率を人数別に知りたい場面

ある物流センターでは毎日アルバイトを雇っています。

本当は毎日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. 1~6桁目が0の時に7桁目を0~7に変化させ、合計が7になる数字を探す
  2. 0000007だけなので、これをエクセルシートの(2,1)セルに書き込む
  3. 1~5桁目が0で6桁目が1の時に7桁目を0~7に変化させ、合計が7になる数字を探す
  4. 0000016だけなので、これをエクセルシートの(3,1)セルに書き込む
  5. 以上をすべての組み合わせについて繰り返す

 

サンプルプログラムを以下に示します。

 

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を代入した特殊ケースが二項分布になります。

ですので、多項分布の方が応用範囲が広いのです。

その他、離散確率分布としてはベルヌーイ分布カテゴリ分布も有名ですが、これらもすべて多項分布の特殊ケースです。

従って、これら4つはまとめて勉強すると理解が進みます。

興味ある方は、こちらも読んでみて下さい。

ベルヌーイ分布/二項分布/カテゴリ分布/多項分布の関係をまとめてみた。