EXCELで確率の計算方法がどうしてもわかりません。困っております。

質問

どうしても、EXCELの関数で求めたいことがありまして
ご質問させていただきます。

箱の中に白玉が２つ、黒玉が３つあるとします。
白玉を引くと掛け金が２倍になり、
黒玉を引くと掛け金が没収されます。
玉は引いたあと、元の箱に戻します。
ちなみに、掛け金は毎回１万円とします。

「手持ちの資金を１００万円からスタートして、
合計１００回玉を引いたとき、最後の１００回目
の手持ち資金がいくらになっているか？」

この一連の作業をエクセルの関数で計算
したいのですが、どんな数式を入れればよいのか
全くわかりません。

どなたか、詳しい方、ぜひお知恵をお貸しいただけない
でしょうか？よろしくお願いいたします。

回答No.6

Wendy02 · ベストアンサー

こんにちは。

ためしに、シミュレートをしてみました。

最初に、VBAの乱数ジェネレータが本当に乱数を発生しているのか調べてみました。1千万回の乱数発生で、1から5の出る発生回数は、

1,997,375
1,998,943
2,002,960
2,004,866
1,995,856

という状況です。ほぼ偏りはないと思われます。
（これは、シミュレートの前に、乱数が発生しているか調べる必要があると思われます。そのまますると、違っていることがあるからです。）

以下のコード自体はあまり自信があるわけではないのですが、100回の取り出し計算で、10万回を繰り返し、それを10回行ってみました。

Sub Test()
　　Dim Moto As Long
　　Dim Nokori As Long
　　Dim Sogo As Currency
　　Dim ar(4) As Long

　　Moto = 100 '万円
　　　　For n = 1 To 100000 '回数
　　　　　　Randomize　'1クールごとに、ランダマイズをする
　　　　　　For i = 1 To 100
　　　　　　　　j = Int(Rnd() * 5) + 1
　　　　　　　　If j > 2 Then
　　　　　　　　　　Nokori = Moto - 1
　　　　　　　　Else
　　　　　　　　　　Nokori = Moto + 2
　　　　　　　　End If
　　　　　　　　Moto = Nokori
　　　　　　　　ar(j - 1) = ar(j - 1) + 1　'出玉の回数の集計
　　　　　　Next
　　　　　　Sogo = Sogo + Nokori
　　　　　　Moto = 100
　　　　Next n
　　　　With Range("A65536").End(xlUp)
　　　　　 .Offset(1).Value = Sogo / 100000
　　　　　 .Offset(1, 1).Resize(, 5).Value = ar()
　　　　End With
　　　　Erase ar()
　　　　Sogo = 0
End Sub

結果：

120.04251
120.09153
119.96343
120.03294
120.04068
120.075
119.97189
120.02163
120.03249
120.02769
--------------------
平均：120.029979

出玉の平均値

　　　1　　　　　2　　　　　　3　　　　　　4　　　　　5
2000418.7　　2000580.6　　1999941.6　　1999948.2　　1999110.9
合計：1千万回

結果は、#4さんの通りですね。

なお、ワークシートでは、ランダム関数が乱数ジェネレータを更新しないので公平に出ないはずですから、シミュレートはおそらくは出来ないように思います。

お礼

私の代わりにマクロでシミュレーションまで
していただきまして、本当にありがとう
ございました。
正直、プログラミングがかなり苦手なので
大変参考になりました。

回答No.9

NNAQ

１００回 玉を引いたとき、白玉を引くのは(当たり前ですが)平均４０回、
つまり手持ち資金は、平均８０万円になる。

平均なので、毎回必ずぴったり４０回ではないので、

=CRITBINOM(100,0.4,0.025)　は、31
=CRITBINOM(100,0.4,0.975)　は、50
約９５％の確率で白玉を３１～５０回引くので、
手持ち資金は、約９５％の確率で６２～１００万円になる。

=CRITBINOM(100,0.4,0.005)　は、28
=CRITBINOM(100,0.4,0.995)　は、53
約９９％の確率で白玉を２８～５３回引くので、
手持ち資金は、約９９％の確率で５６～１０６万円になる。

=1-BINOMDIST(50,100,0.4,TRUE)　は、0.01676…
儲けが出る確率は、約１．７％。

詳しくないけど、多分こんな感じ？？？

お礼

皆様ほんとうにありがとうございました。
大変、有意義なご回答に感謝・感謝です。

回答No.8

Wendy02

こんばんは。

私の#6 のVBAのシミュレーションでは、頭の中での予想とは違っていました。私の結果が違っていれば、その論理でどこが違っているか考えなくてはなりません。誰がどうとかは、私には関係がありません。

# 白玉を引くと掛け金が２倍になり、
問題は、元の質問のここの点の解釈になります。掛け金は、どうなるのか、ということですね。

掛け金、1万円は負ければ、その1万円マイナスだけれども、勝ちになると、2万円で、その内の1万円は掛け金である、ということですね。つまり、元金に対して差し引き1万円増ということですね。おそらく、そう考えるのが自然かもしれません。掛け金とは別とはかかれていませんからね。それは、ご質問者さんに判定をお任せします。

VBAのコードの中の、
Else
　Nokori = Moto + 1　'←Moto + 2 を変更しました。

では、それでシミュレートをしてみます。

79.97966
80.02118
80.00474
79.96564
79.99966
80.01758
79.97602
79.98436
80.01638
80.01056

80万円という結果になります。

なお、ワークシート関数での論理は良いとしても、Rand()関数のシミュレートは、乱数の予想値からすると、遥かに少ないはずですから、うまくいかないかもしれません。統計を取ってみないとはっきりとはいえませんが。

回答No.7

maggoteating

正しいのは　ANo.3さんで、　シミュレーションなら　ANo.2さん　の通りのように思います。　シート計算だろうが　ＶＢＡ　だろうが本質的には変わりないはずです。

＞一回で得られる金額＝ 20,000円×(2/5) + (-10,000円)×(3/5) = 2,000円　・・・？

　10,000円×(2/5) + (-10,000円)×(3/5) = -2,000円　ではありませんか？

＞よって 100回の後には 1,000,000 + 2,000×100 = 1,200,000円 を期待できる。　
　ではなく100回の後には 1,000,000 - 2,000×100 = 800,000円 を期待できる　ではありませんか？

お礼

maggoteatingさんありがとうございます。
このケースだと、期待値がマイナスだったことに、
いま気付きました。
>箱の中に白玉が２つ、黒玉が３つあるとします
この記述を間違っておりました。

「箱の中に白玉が３つ、黒玉が２つあるとします」
こちらに、訂正させてください。

期待値がマイナスのシミュレーションをいくら
やっても、最終的には掛け金がマイナスに
なってるのは当たり前ですもんね。

貴重なご意見ありがとうございました。

回答No.5

ka_na_de

１万円を掛けて、掛け金が２倍になるとは、結局＋１万円となるのでは？

だとすると、期待値はredowlさんの計算結果になると思いますがいかがでしょうか？

ただ、この質問はエクセルでシミュレーションがしてみたいということではないのでしょうか？

回答No.4

koko_u_

期待値でいえば。

一回で得られる金額＝ 20,000円×(2/5) + (-10,000円)×(3/5) = 2,000円

よって 100回の後には 1,000,000 + 2,000×100 = 1,200,000円 を期待できる。

お礼

投資で資金管理を行うにあたって、
掛け金をいくらに設定するか？を
考えていたところだったので、
助かりました。ありがとうございます。

回答No.3

redowl

理論的には
１回ごとの期待値は、常に８０００円
１００回行えば、８０万の期待値。
投資金は１００万円だから、１００回終了時点で２０万円の損失。

よって、手持ち残金は、８０万円前後。

お礼

お忙しい中、ご協力くださいました
ありがとうございました。

回答No.2

ka_na_de

例えば、

A1：回数、B1：くじ結果、C1:配当、D1：残金と見出しをつける。

<A:回数列の設定＞
　１）A2：　０
　２）A3：　=A2+1 A4以降はコピー＆ペーストで100までの連番作成
<B：くじ結果列の設定＞
　１）B2:　空白
　２）B3:　=INT(RAND()*5)　と入力しB4以降はコピー＆ペースト
＜C:配当列の設定＞
　１）C2:　空白
　２）C3：　=IF(OR(B3=0,B3=1),10000,-10000)　と入力しC4以降はコピー＆ペースト
＜D:残高列の設定＞
　１）C2：1000000
　２）D3：　=D2+C3　と入力しD4以降はコピー＆ペースト

以上です。
ただし、上記の結果は１セット（100回試行）の結果でしかありません。
同じ処理を100セット行って平均を出すなどの計算は、
VBAで記述したほうが良いかと思います。

お礼

まさに、私が求めていた数式がこれです。
ありがとうございました。
ためしに計算してみましたが、ka_na_deさん
がおっしゃる通り、最終的にはVBAで記述した
ほうが良さそうですね。
とても困っていたので、助かりました。
ありがとうございました。

回答No.1

koko_u_

簡単なシミュレーションがしたいということですか？

rand() 関数を使えば、0から1までの間の数をランダムに生成できるので、
int(rand()*4) とすれば 0, 1, 2, 3, 4 が多分ランダムに生成できるでしょう。

あとは if 文で 0, 1 なら 20,000円戻ってきて、2, 3, 4 なら 10,000円を失う式を書いて、これを 100回コピーすれば出来上がり。

お礼

早速、お答えいただきましてありがとうございます。
試しに、計算したいと思います。