Excelを使ったシミュレーション

質問

大学からの課題なのですが、Excelの経験が浅く、どのように考えたらいいかも分からない状況です(ー ー;)
どなたか教えてくれませんか。

スマホゲームのガチャを行う状況を想定してみよう。 お目当てのアイテムが4つあるとする(例えばイメージとして、武器、防具、兜、盾)。 この4つのアイテムはそれぞれ確率1%で当たる(つまり 96%はハズレ)。 また、同じアイテムを重ねる(凸する)ことで強化されるものとする。 ガチャを1回100円とした場合、すべてのお目当てアイテムを5個ずつ引き当てる(すべ て4凸する)ためには予算がどれくらいかかることが予想されるだろうか?
ヒント:数学的に厳密に期待値を求めるのは大変で、現実的ではないでしょう。そういう時 は、仮想のガチャをシミュレーションしてみるのが有効。シミュレーションを行うときには、 ランダムで数字を発生させるのがコツです。
例えば Excel であれば、=RANDBETWEEN(1,100)と入力することで、1から100までの 数字をランダムで発生させることができます。そうして、1が出たら武器、2が出たら防 具、・・が当たったとみなす。これで仮想ガチャを作れたのでそのあとは、いろいろと工夫 して考えてみてください。
※この問題は決まった解き方、答を求めるものではありません。Python などのプログラム 言語を使ってシミュレーションすることもできるでしょう。知識を動員して、いろいろ調べ て、知恵を使って各自が工夫して問題解決を図ることを求めます。

回答No.4

kkkkkm · ベストアンサー

No1です。
VBAではなく表で乱数を表示させて結果を出す場合の方法です。
A2に
=RANDBETWEEN(1,100)
と入れて下方向A1001までコピーします。1000個の乱数を表示します。
B1 に1
C1 に2
D1 に3
E1 に4
と入力します。
B2に
=COUNTIF($A$2:$A2,B$1)
と入力して右へE2までコピーします。
B2を選択して右下の角にカーソルを合わせて＋になったらダブルクリックします。1001行まで式がコピーされます。E2まで同じようにします。
各数値の出た行でカウントアップしてます。
F1 に1
G1 に2
H1 に3
I1 に4
と入力して
F2に
=MATCH(5,B2:B1001,0)
と入力して
右へI2までコピーします。
各数値がカウントされた列で最初に5が出た位置をA2からの相対値で(A2含めた値)表します。
J2に
=MAX(F2:I2)*100 & "円"
としてF2からI2の中での最大値(F2からI2列の中で最後に5に達した時点で終了となるので)を求めて予算を計算します。

VBAの場合もとりあえず1回だけの計算でしたが、回数を設定すれば複数回実行して最大・最小・平均を取ることができます。
上記の数式の場合、複数回の実行は手動での実行となります。

お礼

ありがとうございました！！
すごく分かりやすかったですm(__)m

回答No.10

Wap58

No.8の機能とほとんど変わらないけど新しい方が見通しいいかと思います
Perl使える環境なら良いのですが、以下のソースをコピーして
テキストにペーストしてtekitou.plとかで保存してください
ターミナルかコンソールかパワーシェルか知らないけど
テキストのディレクトリーに移動してperl tekitou.plで実行してください

use strict;
main_1();

sub data_1{
my( $re,$cou,$an,$i ) = @_;
$i++;
$an += 5000;
if( $cou < $an ){ $re->{$i}++; return; }
data_1( $re,$cou,$an,$i );
}### END data_1

sub print_1{
my( $re,$i,$an ) = @_;
$i++;
$an += 5000;
$re->{$i} = 0 if not $re->{$i};
$re->{'pa'} += $re->{$i}/10;
print'■'x"$re->{$i}"," $an円 : $re->{'pa'}% : $re->{$i}回\n";
print_1( $re,$i,$an ) if $an < 200001;
}### END print_1

sub main_1{
my( $an,$buk,$bou,$kab,$tat,$cou,$pri );
my $mem = 1000;
my $max = 0;
my $min = 100000;
my $re = {};

while($mem){
$an = int(rand(100))+1;

$buk++ if $an == 1;
$bou++ if $an == 2;
$kab++ if $an == 3;
$tat++ if $an == 4;
$cou++;
$pri++;

if($buk >= 5 and $bou >= 5 and $kab >= 5 and $tat >= 5 ){
$cou *= 100;
$max = $cou if $cou > $max;
$min = $cou if $cou < $min;
data_1( $re,$cou,15000,0 ); ### data_1 当たり回数保存
$mem--;
$buk = $bou = $kab = $tat = $cou = 0;
}### END if
}### END while

print_1( $re,0,15000 ); ### print_1 グラフプリント
$pri = int( $pri/10 );
print"\n 最大値 $max円\n 最小値 $min円\n 平均値 $pri円\n";
}### END main_1
__END__

回答No.9

HohoPapa

課題のゴールは
>予算がどれくらいかかることが予想されるだろうか?
これを求めることのようです。

そこで、予算をいくらにすると、どれくらいの確率で
>すべてのお目当てアイテムを5個ずつ引き当てる(すべ て4凸する)
ことができるのかをシミュレーションしてみました。

表の見方は、例えば13行目を例示すれば
アイテム数を30個引き当てるのに必要な予算が75千円（つまり750回）
これを1万回挑戦したら、6,202回、「すべ て4凸にする」ことができた。
都合、達成確率が62.02%ということです。

99%の確率で「すべ て4凸にする」には、
140千円程度の予算を見込む必要があるようです。
また、予算50千円でも1%程度の確率ですが、
「すべ て4凸にする」ことができるようです。

以下が、集計するサンプルコードです。

Sub Main()
　Const TryCnt = 10000
　Const StartItemCnt = 19
　Const EndItemCnt = 60
　Dim HitCnt As Long
　Dim MyR As Long
　Dim ItemCnt(4) As Long
　Dim i As Long
　Dim j As Long
　Dim k As Long

　With ThisWorkbook.Sheets(1)

　　'初期設定
　　.Cells.NumberFormatLocal = "G/標準"
　　.Cells.ClearContents
　　Range(.Columns(2), .Columns(4)).Style = "Comma [0]"
　　.Columns(5).NumberFormatLocal = "0.00%"
　　.Cells(1, 1).Value = "アイテム数"
　　.Cells(1, 2).Value = "予算"
　　.Cells(1, 3).Value = "挑戦回数"
　　.Cells(1, 4).Value = "達成回数"
　　.Cells(1, 5).Value = "達成率"

　　'ヒットするアイテム数ごとの集計
　　For k = StartItemCnt To EndItemCnt
　　　HitCnt = 0
　　　For i = 1 To TryCnt
　　　　Erase ItemCnt
　　　　For j = 1 To k
　　　　　MyR = WorksheetFunction.RandBetween(1, 4)
　　　　　ItemCnt(MyR) = ItemCnt(MyR) + 1
　　　　Next j
　　　 If ((ItemCnt(1) > 4) And _
　　　　　 (ItemCnt(2) > 4) And _
　　　　　 (ItemCnt(3) > 4) And _
　　　　　 (ItemCnt(4) > 4)) Then
　　　　 HitCnt = HitCnt + 1
　　　 End If
　　　Next i

　　　'集計結果をシートに出力
　　　.Cells(k - (StartItemCnt - 2), 1).Value = k
　　　.Cells(k - (StartItemCnt - 2), 2).Value = k / 4 * 100 * 100
　　　.Cells(k - (StartItemCnt - 2), 3).Value = TryCnt
　　　.Cells(k - (StartItemCnt - 2), 4).Value = HitCnt
　　　.Cells(k - (StartItemCnt - 2), 5).Value = HitCnt / TryCnt

　　Next k
　End With
End Sub

回答No.8

Wap58

perlでグラフ化してみました

use strict;

sub goo_1{
my( $re,$cou ) = @_;
$cou < 25000 ? $re->{'25000'}++ :
$cou < 30000 ? $re->{'30000'}++ :
$cou < 35000 ? $re->{'35000'}++ :
$cou < 40000 ? $re->{'40000'}++ :
$cou < 45000 ? $re->{'45000'}++ :
$cou < 50000 ? $re->{'50000'}++ :
$cou < 55000 ? $re->{'55000'}++ :
$cou < 60000 ? $re->{'60000'}++ :
$cou < 65000 ? $re->{'65000'}++ :
$cou < 70000 ? $re->{'70000'}++ :
$cou < 75000 ? $re->{'75000'}++ :
$cou < 80000 ? $re->{'80000'}++ :
$cou < 85000 ? $re->{'85000'}++ :
$cou < 90000 ? $re->{'90000'}++ :
$cou < 95000 ? $re->{'95000'}++ :
$cou < 100000 ? $re->{'100000'}++ :
$cou < 105000 ? $re->{'105000'}++ :
$cou < 110000 ? $re->{'110000'}++ :
$cou < 115000 ? $re->{'115000'}++ :
$cou < 120000 ? $re->{'120000'}++ :
$cou < 125000 ? $re->{'125000'}++ :
$cou < 130000 ? $re->{'130000'}++ :
$cou < 135000 ? $re->{'135000'}++ :
$cou < 140000 ? $re->{'140000'}++ :
$cou < 145000 ? $re->{'145000'}++ :
$cou < 150000 ? $re->{'150000'}++ :
$cou < 155000 ? $re->{'155000'}++ :
$cou < 160000 ? $re->{'160000'}++ :
$cou < 165000 ? $re->{'165000'}++ :
$cou < 170000 ? $re->{'170000'}++ :
$cou < 175000 ? $re->{'175000'}++ :
return;
}

my( $an,$buk,$bou,$kab,$tat,$cou,$pri );
my $mem = 100;
my $max = 0;
my $min = 100000;
my $re = {};

while($mem){
$an = int(rand(100))+1;

$buk++ if $an == 1;
$bou++ if $an == 2;
$kab++ if $an == 3;
$tat++ if $an == 4;
$cou++;
$pri++;

if($buk >= 5 and $bou >= 5 and $kab >= 5 and $tat >= 5 ){
$cou *= 100;
$max = $cou if $cou > $max;
$min = $cou if $cou < $min;
goo_1( $re,$cou );
$mem--;
$buk = $bou = $kab = $tat = $cou = 0;
}
}

print'■'x"$re->{'25000'}",' 25000円',"\n";
print'■'x"$re->{'30000'}",' 30000円',"\n";
print'■'x"$re->{'35000'}",' 35000円',"\n";
print'■'x"$re->{'40000'}",' 40000円',"\n";
print'■'x"$re->{'45000'}",' 45000円',"\n";
print'■'x"$re->{'50000'}",' 50000円',"\n";
print'■'x"$re->{'55000'}",' 55000円',"\n";
print'■'x"$re->{'60000'}",' 60000円',"\n";
print'■'x"$re->{'65000'}",' 65000円',"\n";
print'■'x"$re->{'70000'}",' 70000円',"\n";
print'■'x"$re->{'75000'}",' 75000円',"\n";
print'■'x"$re->{'80000'}",' 80000円',"\n";
print'■'x"$re->{'85000'}",' 85000円',"\n";
print'■'x"$re->{'90000'}",' 90000円',"\n";
print'■'x"$re->{'95000'}",' 95000円',"\n";
print'■'x"$re->{'100000'}",' 100000円',"\n";
print'■'x"$re->{'105000'}",' 105000円',"\n";
print'■'x"$re->{'110000'}",' 110000円',"\n";
print'■'x"$re->{'115000'}",' 115000円',"\n";
print'■'x"$re->{'120000'}",' 120000円',"\n";
print'■'x"$re->{'125000'}",' 125000円',"\n";
print'■'x"$re->{'130000'}",' 130000円',"\n";
print'■'x"$re->{'135000'}",' 135000円',"\n";
print'■'x"$re->{'140000'}",' 140000円',"\n";
print'■'x"$re->{'145000'}",' 145000円',"\n";
print'■'x"$re->{'150000'}",' 150000円',"\n";
print'■'x"$re->{'155000'}",' 155000円',"\n";
print'■'x"$re->{'160000'}",' 160000円',"\n";
print'■'x"$re->{'165000'}",' 165000円',"\n";
print'■'x"$re->{'170000'}",' 170000円',"\n";
print'■'x"$re->{'175000'}",' 175000円',"\n";

print"\n 最大値 $max円\n 最小値 $min円\n 平均値 $pri円\n";

回答No.7

HohoPapa

課題のゴールは
>予算がどれくらいかかることが予想されるだろうか?
これを求めることのようです。

最小値は、
（超奇跡的な幸運が必要ですが）
むろん、20回×100円＝2000円です。
他方最大値は、統計的な視点を無視すれば
理論的には無限大でしょう。

で、そのバラツキをグラフにしたときに
放物線、あるいはサインカーブになるのでは？
と予想しましたが、どうやら添付画像のようになるようです。

ピークは平均値のちょっと手前になりそうです。

以上のことから
提示したグラフの形状と平均値
さらに、予算額にどの程度の余裕を持たせるかを考慮して決めるのが
妥当な解のように思います。

回答No.6

Wap58

No.5さんのアプローチは気づかなかった
平均値は出てるんで最大値と最小値は乱数で求めます

#include <stdio.h>
#include <stdlib.h>
#include <time.h>

int main (void){
int an=0,buk=0,bou=0,kab=0,tat=0,cou=0,pri=0;
int mem = 100,max=0,min=100000;
srand(time(NULL));

while(mem){
an = rand()%100+1;

if( an == 1 ) buk++;
if( an == 2 ) bou++;
if( an == 3 ) kab++;
if( an == 4 ) tat++;
cou++;
pri++;

if(buk >= 5 && bou >= 5 && kab >= 5 && tat >= 5 ){
cou *= 100;
if( max < cou ) max = cou;
if( min > cou ) min = cou;
printf(" buki %d\n bougu %d\n kabto %d\n tate %d\n %d円 \n\n",
buk,bou,kab,tat,cou);
mem--;
buk = bou = kab = tat = cou = 0;
}
}
printf("平均 %d円\n最大 %d円\n最小 %d円\n",pri,max,min);
return 0;
}

回答No.5

HohoPapa

>=RANDBETWEEN(1,100)
というヒントに引きずられ、
この関数を使い、
>アイテムを5個ずつ引き当てる(すべ て4凸する)
を満たすまで繰り返し、何回繰り返したかを記録する。
この繰り返しを数百回～数千回行い、これらの平均を求める。
VBAのコードとすると
添付の注２の行を使ったコードです。
結果、約７４４回、７４４００円が炙り出てきます。

多くの方がこのようなアプローチをしているものと思います。
当初私もそうでした。

課題が求めているのは、そのようなアプローチではなく
もっと効率のよい方法を見出すことを求めているものと思います。

以下が私の考えたアプローチです。
これならシミュレーションに必要な試行回数が格段に少なくなります。
ざっくり、1/25の回数で済みます。

◇ステップ１
何個当てれば。
>アイテムを5個ずつ引き当てる(すべ て4凸する)
の条件を満たすかをシミュレーションします。
つまり、

RANDBETWEEN(1,4)
この関数を使い
>アイテムを5個ずつ引き当てる(すべ て4凸する)
を満たすまで繰り返し、何回繰り返したかを記録する。
この繰り返しを数百回～数千回行い、これらの平均を求める。
VBAのコードとすると
添付の注１の行を使ったコードです。
結果、約29.65個が炙り出てきます。

◇ステップ２
約29.65個の当たりを得るにはガチャを何回行う必要があるかを考える。
いずれかのアイテムの当たる確率が4%ですから、
29.7/4*100=741回、７４１００円が炙り出てきます。

Sub Main()
　Dim i As Long
　With ThisWorkbook.Sheets(1)
　　.Cells.ClearContents
　　.Cells(1, 1).Value = "武器"
　　.Cells(1, 2).Value = "防具"
　　.Cells(1, 3).Value = "兜"
　　.Cells(1, 4).Value = "盾"
　　.Cells(1, 5).Value = "回数"
　　.Cells(1, 6).Value = "平均"
　　For i = 2 To 10001　 '試行回数
　　　MySub i
　　Next i
　　.Cells(2, 6).Value = "=AVERAGE(E:E)"
　End With
End Sub

Sub MySub(LCnt As Long)
　Dim MyR As Long
　With ThisWorkbook.Sheets(1)
　　Do
　　　If _
　　　　.Cells(LCnt, 1).Value > 4 And _
　　　　.Cells(LCnt, 2).Value > 4 And _
　　　　.Cells(LCnt, 3).Value > 4 And _
　　　　.Cells(LCnt, 4).Value > 4 Then Exit Do
　　　MyR = WorksheetFunction.RandBetween(1, 4)　　'注１
　　　'MyR = WorksheetFunction.RandBetween(1, 100)　'注２
　　　If MyR < 5 Then
　　　　.Cells(LCnt, MyR).Value = .Cells(LCnt, MyR).Value + 1
　　　End If
　　　.Cells(LCnt, 5).Value = .Cells(LCnt, 5).Value + 1
　　Loop
　End With
End Sub

上のコードは、試行回数を10000回としています。
また、注１、２で他の行も変更すれば効率がいいと思いますが
ここでは比較しやすいように、
あえて、関数部分だけを書き換えています。

回答No.3

kon555

これ、課題としては「Excelを使え」というものではないですね。どんな方法でもいいので『シミュレーションしてみなさい』というタイプの課題ですね。
(にしても課題で(凸する)とか書くんですね。ちょっと面白いです)

　なので解法としては無限にあります。極めてシンプルな方法としては「サイコロを振る」です。100面体のサイコロというのは販売されていますのでこれを振って値を見て判定して記録、振って判定して記録、振って判定して記録・・・を繰り返せばシミュレートできます。
　ただこれを実際にやると凄く時間と労力がかかるし、記録のミスも増えるので、色々と工夫しましょう、というのが課題の趣旨ですね。

　例示されているExcelで言えば、どのセルにでも=RANDBETWEEN(1,100)と入力するとランダムに1から100が出ます。これがサイコロです。
　入力されたセルの数がサイコロを振った数になります。
　あとはこれをどう判定するか。どう記録するか、というのが問題になるわけです。

　一例として言えば、エクセルにはif関数というのがあり、これは特定の条件にあてはまると特定の値を返します。
　例えばA1セルに「=RANDBETWEEN(1,100)」と入力する。B1セルに「=IF(A1=1,1,0)」と入力します。
　するとA1セルのランダムの結果が1の時だけ、B1セルには「1」と表示されます。他の結果の時は0です。「=IF(A1=2,1,0)」ならランダム結果2を、「=IF(A1=3,1,0)」ならランダム結果3を記録できます。
　そして同様にif関数を使って、任意の値が出た回数を記録する事も可能です。
　添付画像はかなりの力押しですが、ランダム結果が1の場合はB列に1を出し、その出た回数をC列でカウントしています。
　これを同様の構造のままずーっと下まで繰り返せば、どこかで1が五回になり、C列で「5」が表示されます。
　つまりC列で「5」が表示された場所を見れば「何回目のランダム処理で1が4凸したか」が分かります。
　このように関数を上手く(というには不格好ですが)使えば、明確なシミュレーションとして成立させる事が出来るわけです。

　ほかにもVBAという、エクセルに付属したプログラムで行う方法もあります。またそれ以外のプログラム言語でも出来ます。というか、エクセルよりそれらでやる方がよほど簡単です。
　興味があれば、色々と調べてみる事をお勧めします。

回答No.2

Wap58

CカテゴリーなんでCで書きます
No.1さんと同じで1から100までの乱数でアイテムが全て５個以上揃ったら
memで100回(100円)実行なんで pri円がほぼ平均値じゃないかな

#include <stdio.h>
#include <stdlib.h>
#include <time.h>

int main (void){
int an=0,buk=0,bou=0,kab=0,tat=0,cou=0,pri=0;
int mem = 100;
srand(time(NULL));

while(mem){
an = rand()%100+1;

if( an == 1 ) buk++;
if( an == 2 ) bou++;
if( an == 3 ) kab++;
if( an == 4 ) tat++;
cou++;
pri++;

if(buk >= 5 && bou >= 5 && kab >= 5 && tat >= 5 ){
cou *= 100;
printf(" buki %d\n bougu %d\n kabto %d\n tate %d\n %d円 \n\n",
buk,bou,kab,tat,cou);
mem--;
buk = bou = kab = tat = cou = 0;
}
}
printf(" %d円\n",pri);
}

回答No.1

kkkkkm

エクセルでしたらマクロ(VBA)になると思いますが、とりあえず
1から100までの乱数発生で1,2,3,4が全て5回以上出た場合の予算を計算します。B列がそれぞれ出た回数です。
運がいい時悪い時(同じ数値が何回も出る)でかなりばらつきがありますので、何回か実行して平均を出すとかしたらいいのかもしれません。

Sub Test()
Dim mRand As Long
Dim f1 As Boolean, f2 As Boolean, f3 As Boolean, f4 As Boolean

Range("B1:D4").ClearContents
Range("A1").Value = "武器"
Range("A2").Value = "防具"
Range("A3").Value = "兜"
Range("A4").Value = "盾"
Range("C1").Value = "予算"
Do
mRand = WorksheetFunction.RandBetween(1, 100)
Select Case mRand
Case 1
Range("B1").Value = Range("B1").Value + 1
If Range("B1").Value >= 5 Then
f1 = True
End If
Case 2
Range("B2").Value = Range("B2").Value + 1
If Range("B2").Value >= 5 Then
f2 = True
End If
Case 3
Range("B3").Value = Range("B3").Value + 1
If Range("B3").Value >= 5 Then
f3 = True
End If
Case 4
Range("B4").Value = Range("B4").Value + 1
If Range("B4").Value >= 5 Then
f4 = True
End If
Case Else
End Select
Range("D1").Value = Range("D1").Value + 100
Loop While f1 = False Or f2 = False Or f3 = False Or f4 = False
End Sub