[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『サイコロの確率の出し方』（たなか）
エクセルの式というより、数学要素が強いのですが、
どなたか分かる方、またはこのサイトで計算できるよ、このサイトで聞く方がいいよ、等をご存じの方がいらっしゃいましたら、
お教えいただけましたら幸いです。
・６面体のサイコロ
・１０個
・６面体にはあたりとはずれの２つの絵柄があり、10個それぞれあたりの数とはずれの数が違う
例えば、
・６面体のうち、５面が当たりのサイコロ ４個
・６面体のうち、４面が当たりのサイコロ ３個
・６面体のうち、１面が当たりのサイコロ ３個
１０個のサイコロを同時にふり、
あたりが０個〜10個でるそれぞれの確率
をエクセル表でまとめたいと思っています。
サイコロの数自体や、それぞれのサイコロのあたりの面の数を変えた場合の動きもまとめたいと思っており、
こう計算するといいが分かる方がいらっしゃいましたら、
ご教授賜れましたら幸いです。
よろしくお願い致します。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
確率なので、学生の時に習ってると思うのですが、
エクセル計算でだせる方法を探しております。
（たなか） 2018/02/03(土) 23:34
（γ） 2018/02/04(日) 06:43
yさんの回答は、高尚すぎて私にはさっばり理解できませんでしたので、
自分でも分かるレベルで回答します。
まず、サイコロ10個だと組み合わせが大きくなりすぎるので、
4個の場合で考えてみます。
・６面体のうち、５面が当たりのサイコロ 2個
・６面体のうち、４面が当たりのサイコロ 1個
・６面体のうち、１面が当たりのサイコロ 1個
とします。当たりとはずれの２通りですので、
４個の場合の組み合わせは、下記の16通り(2^4)ですね。
(0 がはずれ、1が当たり）
0 0 0 0
0 0 0 1
0 0 1 0
0 0 1 1
0 1 0 0
0 1 0 1
0 1 1 0
0 1 1 1
1 0 0 0
1 0 0 1
1 0 1 0
1 0 1 1
1 1 0 0
1 1 0 1
1 1 1 0
1 1 1 1
すべてはずれ(0 0 0 0)の場合、
５面が当たりのサイコロがはずれの確率は 1/6
４面が当たりのサイコロがはずれの確率は 2/6
１面が当たりのサイコロがはずれの確率は 5/6
同時に４個のサイコロを振りますので、
同時に複数の事象が発生する場合は、それぞれの確率をかけますので、
1/6*1/6*2/6*5/6 = 0.007716049
の確率になります。
当たりが1回の場合は、下記の４通り
(0 0 0 1)の場合、1/6*1/6*2/6*1/6 = 0.00154321
(0 0 1 0)の場合、1/6*1/6*4/6*5/6 = 0.015432099
(0 1 0 0)の場合、1/6*5/6*2/6*5/6 = 0.038580247
(1 0 0 0)の場合、5/6*1/6*2/6*5/6 = 0.038580247
それぞれの事象は同時に発生するものでないので、それぞれの確率を加算します。
すると 0.094135802 となりますのて、これが、１回当たりが出る場合の確率。
当たり２回、3回、４回の場合の確率も同様に計算します。
考え方としてはこのようになると思います。
数学的な確率の考え方としてはあってますよね（大昔に習ったことなので自信なし）。
これをVBAに翻訳すればいいことになります。
（hatena） 2018/02/05(月) 00:38
シートに下記のようにサイコロ個数と当たり数を入力しておきます。
A B C D E
1 サイコロ個数 4
2 当たり数 5 5 4 1
A4 から組み合わせを出力します。
Public Sub AllCases()
Dim i As Long, a() As Long, r As Range
Dim SaiCount As Long
SaiCount = Range("B1")
Set r = Range("A4")
For i = 0 To 2 ^ SaiCount - 1
a = ToBinArray(i, 4)
r.Resize(, 4) = ToBinArray(i, 4)
Set r = r.Offset(1)
Next
End Sub
Function ToBinArray(ByVal a As Long, dig As Long) As Long()
Dim p As Long, buf() As Long
ReDim buf(dig)
For p = dig - 1 To 0 Step -1
buf(p) = (a Mod 2)
a = a \ 2
Next
ToBinArray = buf
End Function
（hatena） 2018/02/05(月) 01:32
確率変数X,Y,Zを以下とします。 X・・・６面体のうち、５面が当たりのサイコロ を４回振ったときのあたりの回数 Y・・・６面体のうち、４面が当たりのサイコロ を３回振ったときのあたりの回数 Z・・・６面体のうち、１面が当たりのサイコロ を３回振ったときのあたりの回数 このとき、X+Y+Z という確率変数の分布を求めることが課題と言い換えられます。 （同じ種類の4個のサイコロを同時に振るのも、 一つのサイコロを4回繰り返すのも同じことです）
(1) Xは、成功確率が5/6(失敗は1/6)の試行を4回繰り返す時の成功回数ですから、 その確率は、二項分布に従います。
つまり、成功回数が k回 である確率は、
（4個からk個を取る組み合わせの数）× ((5/6)^k) × ((1-(5/6))^(4-k))
で表されます。二項分布については例えば
https://ja.wikipedia.org/wiki/%E4%BA%8C%E9%A0%85%E5%88%86%E5%B8%83
を参照。
Excelのワークシート関数を使えば
For k = 0 To 4
probX(k) = WorksheetFunction.Binom_Dist(k, 4, (5 / 6), False)
Next
として求められます。
Y,Zも同様です。
(2)
X＋Y は、0から7までに値をとる確率変数ですが、
その確率convo1(k)としますと、
For k = 0 To 7
For j = 0 To 4
If (k - j) >= 0 And (k - j) <= 3 Then
convo1(k) = convo1(k) + probX(j) * probY(k - j)
End If
Next
Next
などと計算できます。
（convoは X,Yの畳み込み(convolution)の意味です）
(3)
その結果を、今度はZとの和をとればよいですから、
同様にして、
' (X + Y) + Z の確率分布
For k = 0 To 10
For j = 0 To 7
If (k - j) >= 0 And (k - j) <= 3 Then
convo2(k) = convo2(k) + convo1(j) * probZ(k - j)
End If
Next
Next
として得られる convo2(k)が求める確率分布です。
(4) 纏めると以下のとおりです。 Sub test()
Dim probX(0 To 4) As Double
Dim probY(0 To 3) As Double
Dim probZ(0 To 3) As Double
Dim convo1(0 To 7) As Double
Dim convo2(0 To 10) As Double
Dim k As Long
For k = 0 To 4
probX(k) = WorksheetFunction.Binom_Dist(k, 4, (5 / 6), False)
Next
For k = 0 To 3
probY(k) = WorksheetFunction.Binom_Dist(k, 3, (4 / 6), False)
Next
For k = 0 To 3
probZ(k) = WorksheetFunction.Binom_Dist(k, 3, (1 / 6), False)
Next
' X + Y の確率分布
For k = 0 To 7
For j = 0 To 4
If (k - j) >= 0 And (k - j) <= 3 Then
convo1(k) = convo1(k) + probX(j) * probY(k - j)
End If
Next
Next
' (X + Y) + Z の確率分布
For k = 0 To 10
For j = 0 To 7
If (k - j) >= 0 And (k - j) <= 3 Then
convo2(k) = convo2(k) + convo1(j) * probZ(k - j)
End If
Next
Next
'結果を書き出します
Sheet1.Range("A1").Resize(11, 1).Value = Application.Transpose(convo2)
End Sub
あとは、上記のパラメータを可変にすればよいでしょう。 3種類のサイコロの成功確率とか、それぞれの試行回数ですね。 場合によっては、サイコロの種類も増やすのでしょうか？ 原理的には同じです。
（γ） 2018/02/05(月) 06:16
＞hatenaさま
ご教授ありがとうございます！
「すべての組み合わせをシートに書き出す処理」を10個バージョンで作成することはできました！
ありがとうございます。
そこからの確率の出し方が見いだせずだったので、改めて研究してみます。
10個のサイコロをふるので、
パターンは「６の10乗」で60466176通り、
全てがはずれで０点の確率は、0.002％
全てが当たりで10点の確率は、0.066％
かなと思ったのですが、１〜９点の計算方法が分からず、
且つ０点と10点の確率も違う場合は根本から出し方を考え直してみます。
＞γさま
「宿題や課題ではないです、必要なものをサイコロに置き換えて書いてます」を伝えたかっただけなのですが、
お気を悪くされてしまったようでしたらすみません。
hatenaさまが書いてくださっている通り「シンプルにしました」と書くべきでした、すみません。
ご回答ありがとうございます。
元々VBAやプログラミングに全く知識がないため、
参考書と見比べながら実行してみたのですが、
答えを見つけ出すことができませんでした。
丁寧にお教えいただいたのにすみません。
一週間後にまた改めてチャレンジしてみます。
＞みなさま
一週間ほどパソコンが触れない環境になるため、また一週間後に教えていただいたものを
ひとつひとつ解読できるようひもといてみます。
hatenaさまが書いてくださった「すべての組み合わせをシートに書き出す処理」まではできたので、
ここから確率を導き出す方法がありましたらお教えいただけましたら幸いです。
エクセルの縦軸にサイコロの名前「サイコロA〜サイコロJ」、その横にそれぞれのサイコロのあたりの面数「５５５５４４４１１１」
横軸に合計当たりの数「０点〜10点」
と記載し、12×11のマス目から計算できないか、当たりの面数を書き換えることで、それぞれの点数がどう動くかを見れないだろうか、と模索していたのですが、
１点〜９点までの組み合わせが複数あるので、行き詰った次第です。
説明が稚拙ですみません。
・６面体であることには変化なし（８面体になったり、10面体になったりはしない）
・それぞれのサイコロの当たりの数は変動する（が、一旦例文のものの確率の導きだし方を知りたい）
という形です。よろしくお願い致します。
（たなか） 2018/02/05(月) 13:54
二項分布ですか。Wiki読んでみたけど難しいですね。統計学の範疇にはいるのでしょうか。
とりあえず、前回の回答の方針で作成してみました。
事前に Sheet1 に下記のように、サイコロの個数と、
それぞれのサイコロの当たり数を入力してから、
下記の Main を実行してください。
A B C D E F G H I J K
1 サイコロ個数 10
2 当たり数 5 5 5 5 4 4 4 3 3 3
Public Sub Main()
Dim Dic As Object
Dim i As Long, a() As Long
Dim SaiCount As Long
Dim HitCount() As Variant
Dim K() As Double '当たり確率、はずれ確率の配列
Dim ACnt As String
Set Dic = CreateObject("Scripting.Dictionary")
With Sheet1
SaiCount = .Range("B1").Value 'さいころ個数
HitCount = .Range("B2").Resize(, .Range("B1")).Value 'あたり数の配列
End With
ReDim K(LBound(HitCount, 2) To UBound(HitCount, 2), 1)
For i = LBound(HitCount, 2) To UBound(HitCount, 2)
K(i, 1) = HitCount(1, i) / 6 '当たり確率
K(i, 0) = 1 - K(i, 1) 'はずれ確率
Next
For i = 0 To 2 ^ SaiCount - 1
a = ToBinArray(i, SaiCount)
ACnt = SumArray(a)
Dic(ACnt) = Dic(ACnt) + Kakuritu(a, K)
Next
'結果を書き出し
Sheet1.Range("A4").Resize(Dic.Count) = WorksheetFunction.Transpose(Dic.keys)
Sheet1.Range("B4").Resize(Dic.Count) = WorksheetFunction.Transpose(Dic.items)
Set Dic = Nothing
End Sub
'２進数変換を利用して、n番目の当たり/はずれの組み合わせを取得
Function ToBinArray(ByVal n As Long, dig As Long) As Long()
Dim p As Long, buf() As Long
ReDim buf(dig)
For p = dig - 1 To 0 Step -1
buf(p) = (n Mod 2)
n = n \ 2
Next
ToBinArray = buf
End Function
Public Function Kakuritu(a() As Long, K() As Double) As Double
Dim i As Long, rng As Range
Kakuritu = K(1, a(0))
For i = 2 To UBound(K, 1)
Kakuritu = Kakuritu * K(i, a(i - 1))
Next
End Function
Public Function SumArray(a) As Long
Dim i As Long
For i = LBound(a) To UBound(a)
SumArray = SumArray + a(i)
Next
End Function
結果は、Sheet1 の A4 から下記のように表示されます。
0 0.0000165382 1 0.0004399154 2 0.0049237445 3 0.0301048970 4 0.1092817247 5 0.2398202923 6 0.3101466843 7 0.2192743262 8 0.0738859358 9 0.0114444148 10 0.0006615269
yさんのと同じ結果になったので、間違いはないと思います。
サイコロ個数と当たり数を書き換えれば、別の条件でも結果を得られます。
（hatena） 2018/02/05(月) 14:01
前提条件ですが、全部アタリとか全部ハズレのサイコロも考慮なのですかね？ まぁ、計算方法はそのままで良いのですが。
そして、アタリ個数毎の確率計算は既にγ（ガンマ）さんがコーディングしてくれていますよね。これを理解すると良いでしょう。
しかし、個数毎の確率計算は良いですが、アタリ数を変えたときの変化って、最終的には10個まとめて振って、アタリが何個出るかの期待値1つで表現できるのではないかと思うのですが、いかがでしょう？ 11個の確率を並べてグラフにでもして比較しても、真ん中は多くて、端は少ないよね〜、くらいの事しか判らなくて、役立たないように思います。
期待値だけで良いならば、(5*4+4*3+1*3)/(6*10)*10 = 5.8個 という簡単な式で終わってしまうので、個数毎の確率を計算した後はどうするのだろうなぁ？、等と考えてしまいました。
（???） 2018/02/05(月) 14:43
ご回答ありがとうございます！！！
求めていた通りのものを作成することができました！！
私の拙い文章から、全てを汲み取ってくださり、本当にありがとうございました！
一週間をまたずに解決することができ、とても助かりました！ありがとうございました！
またぜひよろしくお願い致します！
＞？？？さま
＞個数毎の確率を計算した後はどうするのだろうなぁ？、等と考えてしまいました。
「このパターンで固定したときに０点が何％でるか、１点が何％でるか」というそれぞれを知りたかったので、hatenaさまので解決することができました。
ご興味をお持ちいただき、読んでいただき、ありがとうございました。
またぜひよろしくお願い致します。
（たなか） 2018/02/05(月) 15:57
ロジックは高校レベルの確率の知識で組み立ててます。
すべての組み合わせを生成する。
各組み合わせの確率は、各サイコロの確率を乗算する。
その確率を当たり数毎に加算する。
というシンプルなものです。
それを下記のようなコードで実現しています。
(1) 当たり=1 はずれ=0 としてすべての組み合わせを生成する。
４個なら、
0 0 0 0
0 0 0 1
0 0 1 0
0 0 1 1
0 1 0 0
・・・
となりますが、これは２進数と同じです。
そこで、数値を２進数に変換するロジックで
当たり/はずれの組合せの配列を生成します。
それが、ToBinArray関数です。
(2) 生成した一つの組み合わせ配列から、当たり数（点数）と確率を計算する。
当たり数は、SumArray関数で計算します。
確率は、Kakuritu関数で計算します。
各要素の当たり確率、はずれ確率をかけて得られます。
各サイコロの当たり確率、はずれ確率は、配列K() に格納されていますので、それを利用します。
(3) Mainでの処理
上記で取得した一つの組み合わせの当たり数と確率を、順にDictionaryに格納します。
当たり数をkeyにして、同じkeyに対応する確率をItemに加算していきます。
これで、当たり数に対応する確率の合計がDictionaryに格納されますので、
そのキー配列(keys)とアイテム配列(Items)をシートに書き出す。
以上です。
（hatena） 2018/02/05(月) 17:02
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.