【具体例でわかりやすく!】多項分布は何に使えるの?|バイト確保人数の予測で実演

2021年7月29日

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

本当は毎日50人必要なのですが、そこまで集められず慢性的な人手不足です。

過去1か月間の出勤記録を調べたところ、確保できた人数は次の通りでした。

 

今後1週間で毎日35人以上集められる確率は何%でしょうか?

また、95%以上の確信度を持って確保できる毎日の平均人数は何人でしょうか?

 

マクロで組み合わせを計算する

確保できる人数が何通りかあって、それぞれの確率が分かっているので、この問題は多項分布を使えば解けます。

>> 多項分布の使い方【到着時間から学ぶ】

 

状態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通りもあります。

これらすべてを虱潰しに調べることは、さすがに人手では無理ですね。

 

そこでエクセルマクロの力を借りましょう。

こういうことはマクロが最も得意とすることです。

 

まず確保できる人数は7通りのパターンがありますので、それぞれのパターンをp1からp7と名付けます。

 

私たちが知りたいのは、7日間のうちでp1~p7がそれぞれ何回出現するのかです。

従って、下記の条件を満たす7桁の数字をすべて調べることになります。

 

これをマクロで計算させるには、次のようにします。

  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通りの組み合わせを、ものの数秒で出してくれました。

マクロって便利ですねえ。

 

毎日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人ということになります。