Googleスプレッドシート LET,LAMBDの再帰式で ⚀サイコロシミュレーション⚅
Googleスプレッドシート、GAS、Googleドライブと 書きたいネタや書いておくべきネタは色々あるんですが、華月さんの noteで Excelにおける LET&LAMBDA再帰式の解説を読んだんで、自分も再帰式ネタ書いてみようかなと。
仕組みというよりは、こんな時に使うといいよー、こんな風に使うと面白いかもー、といった軽めの内容です。
というわけで、再帰式とは？ とか、LET、LAMBDAで なぜ再帰式が作れるのか？といった 部分はすっとばしてます。ご了承ください。
この辺りの解説は 華月さんの note 👆 がわかりやすくまとめてくれてます。
前回のnoteは 連動プルダウンを実現する数式の改良版を紹介しました。
m個のサイコロを振って、すべてピン ⚀ が出るまで何回かかるかをシミュレーションする
今回のゴールはこれです。
空白セルでDeleteを押してシートを再計算させることで、指定した数のサイコロを振って
全てのサイコロの目が ⚀ となるまでサイコロを振り続けるをシミュレーションしています。
サイコロが2個、3個と増えると、⚀ が揃うまでの回数は一気に増えますね。
サイコロ3個だと ⚀⚀⚀ が出るまで、500回越えとなるケースも。
この ⚀のゾロ目が出るまでサイコロを振る動作を繰り返し、サイコロを振った回数と 成功するまでの全てのサイコロの目を縦並びにログとして出力する処理を D2セルに入れた一つの式でおこなっています。（見出し行も込みで）
同時に振るサイコロの数は A3 セルを参照しており、連動してタイトル行も増減するようにしています。
また使わない時の負荷を軽減する為に、A1を起動スイッチとして利用。チェックボックスをオフにしている時は処理を実行しない（空白を返す）という設定も入れています。
それ以外にも 条件付き書式を設定していたりしますが、とりあえずは D2に入れる式を考えてみましょう！ というのが今回のテーマです。
自力で挑戦してみたい！という人は、この段階で先に式を作ってから続きをお読みください。
回数が決まってない処理は 再帰を使おう
今回の処理は再帰式を組む必要があります。
ExcelやGoogleスプレッドシートといった表計算ソフトは、基本的には実行回数が決まっている処理しか行えませんでした。（今までは）
その為、サイコロを2個振って⚀⚀ と 両方 1 になるまでのシミュレーションを実行しようとした場合、かつては 十分に大きい回数（たとえば 確率的には 6×6 で 36回に1回は出るはずなので 多めにみて100回くらい）実行した上で、⚀⚀が出た回数より上の結果だけを参照する。
こんな処理で対応していたわけです。
でも、サイコロの目は運次第なんで、2個のサイコロを 100回振ったら必ず ⚀⚀ が出るか？というと、必ずしもそうとは言えません。
1回目で出るかもしれないし、500回目で出るかもしれない。
このような 計算回数が決まっていない、一つ前の処理の結果次第で 再度同じ処理を繰り返す。これをプログラミングである VBAやGASを使わずシート関数で実現できるのが LET、LAMBDAを使った再帰式です。
REDUCE関数とLET ＆ LAMBDAの再帰式はどっちが良いか？
一つ前の結果を参照する「繰り返し処理」関数といえば、REDUCE関数があります。こちらも非常に便利で活用すべき関数です。
じゃあ、どう使い分けたらよいか？
一つの目安としては、やはり 計算回数が決まっているか、いないか？ で判断すると良いでしょう。
LET、LAMBDAの再帰式は、次の処理にもちこせる 引数の数が幾つでも指定できたり、欲しい結果が出たら処理を終えることができるといった利点があります。
一方で 式が複雑になりがち（可読性の問題）、計算回数が上限に達しやすい（高負荷）といった欠点もあります。
ちなみに同じような計算処理だと LET ＆ LAMBDAの再帰式は REDUCEよりもだいぶ早く上限に達してしまいます。
この数列の和を 公式無しで 実直に繰り返す処理だと、LETとLAMBDAの再帰式は 9997までは大丈夫ですが、9998で上限オーバーでエラーなります。（ギリ1万届かず）
REDUCEの場合は、666664まで大丈夫で、666665 で上限オーバーでエラーがでます。（約6万6千）
計算の上限は結構差がありますね。
得意、不得意はあるかもしれませんが、REDUCEで書けるものは REDUCEを使った方が良さそうです。
でも、先ほど書いた通り ある結果が出るまでサイコロを振る 今回の処理は、完全にランダムで何回で終わるかわからない処理なので 再帰式を使うべきケースです。
他には素因数分解など、実行してみないと終わりがわからない計算も再帰式が適していると言えます。
それでは再帰式を使ったサイコロシミュ―レーション、順番にお題を解きながら作成していきましょう！
指定した数字範囲のランダムな整数配列をつくる
まずは サイコロ m個を 1回振った時を、シート関数を組み合わせてどんな式を作れば表現できるか考えてみたいと思います。
ただ、いきなり m個は ハードルが高いですし、サイコロだとちょっと難しいかもしれないので、まずは固定値 1～6までのランダムな「数値」（整数）を 3個 横に並べる式を考えてみましょう。
Q1. 1～6までのランダムな数値を 横並びに3つ 1つの式で出力したい
3個の1～6までのランダムな数字（整数）を横に3つ並べて出力する式を考える。
上の画像だと A2 に入れる式ですね。考えてみましょう！
↓↓↓
回答は以下
↓↓↓
A1. 1～6までのランダムな数値を 横並びに3つ 1つの式で出力する
回答です。
={RANDBETWEEN(1,6),RANDBETWEEN(1,6),RANDBETWEEN(1,6)}
1～6のランダムな整数を返す RANDBETWEEN関数を単純に3つ並べた式ですｗ
別の式を作った人は、そっちの方がベターな式なんですが少しだけお待ちください。
別解の前に、乱数系関数について理解を深めておきましょう。
ランダム系関数（RAND系関数）を理解する
3つのRAND系関数と スプレッドシートにおける 乱数について解説します。
3つのランダム系関数（RAND系関数）
RAND系関数は ランダムな結果が欲しい時に重宝する関数です。
RAND系関数は3つあって
RAND関数 0 以上 1 未満の乱数を返す
RAND()
https://support.google.com/docs/answer/3093438?hl=ja
RANDBETWEEN関数 下限以上、上限以下の整数の一様乱数を返す
RANDBETWEEN(下限, 上限)
https://support.google.com/docs/answer/3093507?hl=ja
RANDARRAY関数 0 以上 1 以下の乱数の配列を生成
RANDARRAY（行数, 列数）
https://support.google.com/docs/answer/9211904?hl=ja
このような違いがあります。
これらの関数はExcelにもGoogleスプレッドシートにもありますが、RANDARRAY関数は仕様が違います。
GoogleスプレッドシートのRANDARRAYは 0～1未満の 乱数を指定した配列で返す、RAND関数のスピル版といった仕様です。
一方 Excelの RAND関数は 引数が5つあり
=RANDARRAY([行],[列],[最小],[最大値],[整数])
こんな構成になっています。
画像のケースは、 2行3列の 1～10までの整数をランダムに表示する配列を生成する式になっています。
つまりExcelのRANDARRAY関数は、単に RAND関数を配列にするのではなく、RANDBETWEENの機能も兼ね備えた RAND系関数の最終進化系といえる関数なのです。
これは Googleスプレッドシート使いとしては、ぐぬぬぬ となりますｗ
Googleスプレッドシートの RANDARRAY関数は、Excelのように 範囲指定や整数指定ができない為、基本的には先ほどの回答のように RANDBETWEENを3つ横に連結するのが一番簡単なのです。
ランダム系関数（RAND系関数）は、ブラウザ単位で計算結果が違う
ランダム系関数（RAND系関数）ですが注意点があります。
それは、この関数の結果は ブラウザのタブ単位で違うという点です。
乱数の計算はどうやら クライアント側（ブラウザ）で処理されているようで、同じスプレッドシートを複数人で開いていた場合でも、乱数の結果は各ブラウザのタブ毎に違う数値が表示されます。
また、サーバー側の数値とも違う為、GASで ランダム系関数（RAND系関数）の結果を取得しても 見えている数値と同じものが取得できません。
この乱数の処理、表示結果がブラウザタブ単位でバラバラという仕様は、Web版の Excelも同様です。
1つの画面をみんなで見るなら問題ないですが、共有しているスプレッドシートを各自の画面で見ながら乱数を利用する時や GASで乱数の結果を取得するような場合は注意しましょう。
※GASを使うなら乱数はGAS内で生成しちゃった方がよいです。
サイコロを m 個振った結果を式で表現する
先ほどはサイコロの数が3個で固定だったので、RANDBETWEENを横に3つ連結という方法を使いましたが、これが10個だったら 記述が長くて煩雑ですし、m個（たとえば A1セルの数字）だけ横に並べる と可変にした場合は、この方法が使えません。
では、どうすればよいか？考えてみましょう。
Q2. 1～6までのランダムな数値を 横並びにm個 1つの式で出力したい
Googleスプレッドシートで A1セルの数値の数だけ、1～6までのランダムな数値（整数）を横に並べる式はどのように作ればよいでしょうか？
Excelだったら =RANDARRAY(1,A1,1,6,TRUE) で出来るんですが・・・。
考えてみましょう！
↓↓↓
回答は以下
↓↓↓
A2. 1～6までのランダムな数値を 横並びにm個 1つの式で出力する
回答です。2つ用意しました。
１つは
=MAP(SEQUENCE(1,A1),LAMBDA(v,RANDBETWEEN(1,6)))
MAP関数を使って、第1引数に SEQUENCE(1,A1) で 横並びの数値配列を与えて、第1引数の配列と同じサイズで RANDBETWEEN(1,6) を返すという方法。
これは MAPじゃなくて SCAN や MAKEARRAYを使っても良いです。
やはり LAMBDAヘルパー関数は強いですね。
でも、今回はこっちじゃない方法を使いたいと思います。
A2b. 1～6までのランダムな数値を 横並びにm個 1つの式で出力する（別解）
もう一つの回答です
=ARRAYFORMULA(INT(RANDARRAY(1,A1)*6+1))
こちらは GAS（というより JavaScript）で 2つの数字の間の整数をランダムに取得する方法を シート関数に落とし込んだやり方です。
Math.floor(Math.random() * (maxFloored - minCeiled + 1) + minCeiled); // 上限を含み、下限も含むこの部分を シート関数にしています。
Floorはシート関数にもありますが、小数点以下を切り捨てて整数にしたいだけなんで INT関数でも良いですね。
で、今回の場合は サイコロなんで 最大値が 6、最小値が 1ですから
=INT(RANDARRAY(1,A1)*(最大値 - 最小値 + 1 )+ 最小値)
にあてはめると
=INT(RANDARRAY(1,A1)*( 6 - 1 + 1 )+ 1 )
となり、計算すると
=INT(RANDARRAY(1,A1)* 6 + 1 )
このようになります。
ただ、この処理は配列計算になるのでこのままでは処理されず ARRAYFORMULAが必要です。
=ARRAYFORMULA(INT(RANDARRAY(1,A1)*6+1))
最終的に こんな式になります。
一般的な 最小値と最大値を指定して、その間（最大値、最小値を含む）のランダムな整数配列を生成する式は
=ARRYAOFMRULA(INT(RANDARRAY( 行数 , 列数 )*( 最大値 - 最小値 + 1 )+ 最小値 ))
これです。 ExcelのRANDARRAYを代用するのはなかなか大変ですｗ
Q3. サイコロの絵文字を使って m個のサイコロを振った時の結果を 横並びに表示する式を作りたい
1～6のランダムな数字を m個 横並びに表示させることは出来ました。
それでは最後に サイコロの絵文字に置き換えて、m個のサイコロを振った時の結果を表示する式を作ってみましょう。
ちなみに ⚀ の絵文字は、 サイコロ と打って変換で候補に出てきます。
考えてみましょう！
↓↓↓
回答は以下
↓↓↓
A3. サイコロの絵文字を使って m個のサイコロを振った時の結果を 横並びに表示する式を作る
回答です。
=ARRAYFORMULA(CHAR(INT(RANDARRAY(1,6)*6)+9856))
まず確認すべきは ⚀ ～⚅ の文字コードです。
CODE関数を使うと ⚀～⚅ が コードだと 9856～9861と連番になっていることが確認できます。
ということは 最小値が 9856、最大値が9861で 先ほどの Q2の別解の式にあてはめると
=ARRAYFORMULA(INT(RANDARRAY(1,A1)*6+9856))
このようになります。
で、この結果の文字コードの配列を今度は CHAR関数で文字（絵文字）に戻せばよいので
=ARRAYFORMULA(CHAR(INT(RANDARRAY(1,A1)*6+9856)))
これで完成です。
Excelで再現する場合は UNICODE関数、UNICHAR関数を 使いましょう。
m個のサイコロを振って、 全てのサイコロが⚀になるまで繰り返しログを出力する式を考える
サイコロをｍ個振るを式で表現することは出来ました。
では、これをベースに 全てのサイコロが ⚀ となるまで繰り返し、そこまでの結果ログ（回数とサイコロの目）を出力する式を考えてみましょう。
ここからヒントを出していきますので、ここまでの流れで自力で式が作れそうだ！と思った人は、数式作成にチャレンジしてからこの先を読むことをお勧めします。
Q4. m個のサイコロを振って、 全てのサイコロが⚀になるまで繰り返しログを出力する式を作りたい
まだA1のチェックボックス考慮は不要です。
再帰式を作るにあたって考えるべきは、LAMBDA内で なにが引数に必要か？（なにを次の処理に持ち越したいか？）です。
今回の場合は
再帰式自身 ・・・ func
サイコロの数 ・・・ m
処理の回数 ・・・ i
一つ前の結果 ・・・ pv
これらが必要となります。
これをLETで名前付き関数として Funcと定義します。
つまり式としては
=ARRAYFORMULA(LET(Func,LAMBDA(func,m,i,pv,【実際の計算式部分】…
このようになります。これを完成させてみましょう！
なお、見出し行部分も ダイスの数である mに応じて可変となる為、数式内で生成する必要があります。
↓↓↓
回答は以下
↓↓↓
A4. m個のサイコロを振って、 全てのサイコロが⚀になるまで繰り返しログを出力する式を作る
回答です。
=ARRAYFORMULA(
LET(
Func,LAMBDA(func,m,i,pv,
LET(
d,CHAR(INT(RANDARRAY(1,m)*6+9856)),
result,VSTACK(pv,{i,d}),
IF(COUNTIF(d,"⚀")=m,result,func(func,m,i+1,result))
)
),
Func(Func,A3,1,{"回数","ダイス"&SEQUENCE(1,A3)})
)
)まずはこんな式にしましょう。
解説していきます。
=ARRAYFORMULA(
LET(
Func,LAMBDA(func,m,i,pv,冒頭のこの部分はよいですね。内部で配列処理が必要なので、とりあえず大外に ARRAYFORMULAを付けて全体に配列効果を付与しておきましょう。
この後の処理ですが、m個のサイコロを横並びにする関数の結果を 何度か使う必要があるので、LAMBDA内で再度LET関数で
LET(
d,CHAR(INT(RANDARRAY(1,m)*6+9856)),
result,VSTACK(pv,{i,d}),このように定義します。
d,CHAR(INT(RANDARRAY(1,m)*6+9856)),
こちらは さっき作成したm個のサイコロを 振った結果を横並びに出力する式ですね。これを d と置いています。
そして、これを { i , d } として 実行回数（サイコロを振った回数である i ）と横連結、さらにそれを 一つ前までの結果 pv と VSTACKで縦に連結した結果を resultと置いています。
ここは d を定義しないで 一つの式で result を定義してしまってもよいです。
ここまでがLETの変数定義部分で、この後の IF関数が実際の処理です。見ていきましょう。
全てのサイコロが ⚀ということで、d に ⚀ が m個 ある状態なので
COUNTIF(d,"⚀")=m こうなったら reult を返す
IF(COUNTIF(d,"⚀")=m,result,違う場合は 次のサイコロを振ることになるので、 再帰（同じ処理を繰り返し）させます。
func(func,m,i+1,result)
定義した式の中に定義した式が登場する。。
ここが混乱するところですが、個人的には「頭で理解するより 何度も使って慣れていくうちに腹落ちしてくる」を推奨します。
ま、人に教えならこれだと駄目なんですがｗ
引数に関しては、式とmはそのまま で、 第3引数の 実行回数の i は i+1 で一つ増やして、第4引数の pvの箇所には 一つ前までの結果は今回の結果である result を。
IF(COUNTIF(d,"⚀")=m,result,func(func,m,i+1,result))これが実行部分の式です。
=ARRAYFORMULA(
LET(
Func,LAMBDA(func,m,i,pv,
LET(
d,CHAR(INT(RANDARRAY(1,m)*6+9856)),
result,VSTACK(pv,{i,d}),
IF(COUNTIF(d,"⚀")=m,result,func(func,m,i+1,result))
)
),ここまでが LAMBDAで作った式を LET関数で Func という名前付き関数として定義するまでの部分。
で、これを実行するのが
Func(Func,A3,1,{"回数","ダイス"&SEQUENCE(1,A3)})最後のこの部分です。
第1引数は そのまま自作式である Func を
第2引数の m は ダイスの数なので A3
第3引数 ｉ は 初回は 1から開始
第4引数 pv は初期値として タイトル行を生成する式
{"回数","ダイス"&SEQUENCE(1,A3)}
を入れています。
これでも動くんですが、まだA1セルのチェックボックスがTRUEの時だけ式を実行するという起動スイッチが入っていないのと 最後の実行式部分の
Func(Func,A3,1,{"回数","ダイス"&SEQUENCE(1,A3)})
の引数指定が、なんとも格好悪いのをなんとかしたいですね。
LET、LAMBDAの再帰式を使った式をもう1回LAMBDAで再定義する
これらを解消させた式が
=LET(
Func,LAMBDA(func,m,i,pv,
LET(
d,CHAR(INT(RANDARRAY(1,m)*6+9856)),
result,VSTACK(pv,{i,d}),
IF(COUNTIF(d,"⚀")=m,result,func(func,m,i+1,result))
)
),
DICE,LAMBDA(a,m,ARRAYFORMULA(IF(a,Func(Func,m,1,{"回数","ダイス"&SEQUENCE(1,m)}),))),
DICE(A1,A3)
)こんな式です。
一度 Funcで定義した名前付き関数を、もう一回 LAMBDで くるんで DICEという関数に再定義しています。
ここで
LAMBDA(a,m,ARRAYFORMULA(IF(a,Func(Func,m,1,{"回数","ダイス"&SEQUENCE(1,m)}),)))
とすることで、
a ・・・ 実行スイッチ（チェックボックスのセル A1 ）
m ・・・ ダイスの数（数値をしていしているセル A3）
という セル参照にする 2つの引数だけを残して、他はすべて関数内に入れ込んでいます。
加えて 一番大外に配置していたARRAYFORMULAもここにもってくることで、式をスッキリさせました。
これによって最後の部分が DICE(A1,A3) という、2つの引数を指定するだけの簡潔な式にすることが出来ました。
サイコロ3回を振って 3つとも⚀が出る確率は 1 / 6×6×6 で、確率上は 216回振ったら 1回は3つとも⚀が出るのですが、実際にシミュレーションすると 800回を超えることもあれば 4回目に出ることもありますね。
再帰式でサイコロを振った結果をシミュレーションする式が作れました～。
※もっとスッキリ記述する方法があるかもしれないので、自分だったらこう書くという別解がありましたら Ｘや noteコメントでお寄せください。
【おまけ】サイコロシミュレーションの結果を 反復計算で 記録する
最後にちょっとだけオマケです。
LET、LAMBDAの再帰式に加え、飛び道具と言える 反復計算（循環参照）を使って 今回のサイコロシミュレーションの結果（何回目でサイコロがすべて ⚀ が出たか）をログとして残してみましょう。
つまり
こんなことが出来る！ってことです。
上のＧＩＦ動画では 3つのサイコロを振って 3つも ⚀が出る回数は
1回目の試行では 306回
2回目の試行では 298回
3回目の試行では 183回
4回目の試行では 37回
・・・
といったログを残すことが出来てますね。
久しぶりの反復計算ネタですｗ
これはどうやって作るか？は、今回の本題とズレるんで機会があれば書きますが、どうしても興味ある！って人には 有料コンテンツでシートを配布します。
解説無しなんで、よっぽど興味がある人と 投げ銭感覚で mirを応援したい！っていう方だけ ポチってください。
運がよければ 10回以下で ⚀4つのケースを引きあてることも出来るかも！？
ただしこの計算処理は結構負荷がかかっているので、サイコロの数は4つくらいが限界で、5個だと固まって落ちることが多いです。
次回こそは軽めのネタを書きたいと思いますｗ
👇以下有料ゾーン。今回の サイコロシミュレーション（おまけログ保持機能入り配布）
【有料】サイコロシミュレーションの結果を 反復計算で 記録するスプレッドシート 配布用
ここから先は
¥ 300
Amazonギフトカード5,000円分が当たる
チップ大歓迎です。やる気がアップしますｗ