【破産確率】Excelで確率の問題を解く！③
本シリーズでは、Excelの関数を用いて使って確率の問題を解いていきます。コードをコピペしながら読み進めていただければ幸いです。最後に、この記事をもとに作成したExcelファイルをご用意しています！サクッと試したい方、きれいなレイアウトで確認したい方はぜひご利用ください！
【問題】60%の確率で儲かり、40%の確率で損をする。この賭けを続けた場合、破産する確率はどのくらいか。
今回も、確率の問題として比較的有名な問題を取り上げていきます。もう少し具体的に問題を設定しましょう。
現在あなたは10円を所持しながら、上記のゲームに参加しています。ゲームを1回終えた時点で、60%の確率であなたは賭けに勝利し、所持金は11円になっています。一方で、40%の確率で賭けに負け、あなたの所持金は9円になってしまいます。
この賭けが終わる条件は、次のうちのどちらかが満たされたときです。
①あなたの所持金が0になる
②あなたが儲けたい金額を手に入れ賭けをやめる
②の儲けたい金額を、今回は10円としましょう。つまり、所持金が20円になった時点であなたはこの賭けから降りることにします。
①前提条件を考える
今回は、モデルを考える前に前提条件を考えていきましょう。今回は、①賭けの勝率、②スタート時の所持金、③いくら儲けたら賭けをやめるか、を前提条件とします。この3つの数字を後から変更できるようにExcelを作成すれば、後々さまざまなシナリオをシミュレーションをすることができます。
例えば、勝率が下がったらどの程度破産確率が上がるか、スタート時の所持金が増えたらどのくらい破産しにくくなるのか、といった具合です。
前提条件①賭けの勝率
B4セル
=0.6前提条件②スタート時の所持金
B5セル
=10前提条件③いくら儲かったら賭けをやめるか（賭けをやめるときの所持金）
B6セル
=20これらのセルを参照し、前提条件を踏まえたモデルを作成していきましょう。
①モデルを考える
今回のモデルでは、〇〇セルから取得されるスタート時の所持金が変化していきます。E11セルに初期値を参照しましょう。
=$B$5また、F10セルには賭けの回数を入力しましょう。
=COLUMN()-5次に、下記の数式をF11セルにコピペしてください。
=IF(RAND()<$B$4,E11+1,E11-1)RAND関数は、0から1までの実数を返す関数です。この値が、前提条件である勝率より小さければ所持金にプラス1した値を返します。もし勝率より大きな値が出れば、所持金にマイナス1した値を返します。
このF10セル・F11セルを横にひとつずらすことで、賭けに参加する回数が1回増えることになります。問題文にある「賭けを続けたら」の部分ですね。回数が少ないと、破産もせず十分な儲けも得られません。例えば、横に5つしか伸ばさなかった場合、最大で5円の儲け、5円の損になりどっちつかずです。なので、次の操作をしましょう。
F10セル・F11セルの2セルを選択し、
右端をクリックしてGW列まで右にぐいーっとひっぱる200セル分引っ張りましょう！特にこの数字でなければだめ、というわけではありません。ですが、数字を大きくすればするほど、理論値に近づいていきます。
また、今回も判定用に行番号を入力していきましょう。A10セルに次の関数を入力します。
=ROW()-10③判定する！
それでは、それぞれの試行が「破産」なのか、「十分な儲け」なのか、はたまたそのどちらでもない「引き分け」なのかを判定していきましょう。今回の場合で言えば、100回の賭けに参加する間、所持金が1円から19円の範囲に収まっていた場合引き分けになります。
C11セル
=IFERROR(MATCH(0, $F11:$GW11,0 ), MAX($10:$10)+1)D11セル
=IFERROR(MATCH($B$6, $F11:$GW11, 0), MAX($10:$10)+1)1つめの関数は、初めて破産した時点を返します。MATCH関数を用いて、選択した範囲の中で0が初めて出現した場所を表しています。2つ目の関数は、初めて十分な儲けに到達した時点を返します。
この2つの時点を比較し、数字が早い方をその試行の結果とします。例えば、初めて0が発生する時点が30、初めて十分な儲けに到達した時点が50だったとしましょう。その場合、たとえ後半に十分な儲けを得ていても、途中それより前に破産しているわけですから、その試行は「破産」として判定します。
破産と十分な儲けのどちらにも到達しなかった場合、「引き分け」という値を返します。次の関数をB11セルに入力してください。
=IF(C11=D11,"引き分け",IF(C11<D11,"破産","勝ち"))今回も、1行が試行1回分になります。試行回数を増やすために、次は縦にぐいーっと引っ張りましょう。
A11セルからGW11セルを選択し、
右端をクリックして下にぐいーっとひっぱるA列の数字が300になるまで引っ張っていきましょう！
④集計する！
それでは仕上げとして、勝ち（十分な儲け）、破産、引き分けの状態をそれぞれ集計していきましょう。
E2セル
=MAX(A:A)E3セル
=COUNTIF(B:B, "勝ち")E4セル
=COUNTIF(B:B, "破産")E5セル
=COUNTIF(B:B, "引き分け")上から順番に、それぞれ「勝ち」「破産」「引き分け」の回数を数えています。これらの数字を使って、確率を計算していきます。
E6セル
=E4/(E2-E5)E2セル（試行回数）からE5セル（引き分けの回数）を引いた値が分母になり、分子にはE4セル（破産した回数）がきています。このセルが破産確率を表しています。
次に理論確率ですが、こちらはとても複雑です。漸化式を使っても解くことができます。
E7セル
=IFERROR((((1-B4)/B4)^B5-((1-B4)/B4)^B6)/(1-((1-B4)/B4)^B6), 1-B5/B6)B4セル、B5セル、B6セルの前提条件をもとに計算されているので、前提条件を変更するとこちらの理論確率も変化していきます。
では最後に、シミュレーションの確率と理論値の比較をしていきましょう。
E8セル
=E7-E6今回の場合、破産確率の理論値は1.7%ほどになります。シミュレーションを300回ほどに設定するのがおすすめです。
【忙しい人向け】きれいなエクセルファイル
今回の手順をもとにしたExcelファイルはこちらです！サクッと試してみたい方、時間の取れない方、きれいなレイアウトで見たい方はぜひご活用ください！
サンプルは300回の試行をした状態ですので、いろいろな回数で試してみてください！
いただいたサポートは、主に本の購入費用になります。まとまった金額になりましたら、noteの投稿などを通じて、皆様に還元していければと思っております。