【GAS不要！？】チェックボックスでビンゴゲームをつくる -2

「GASなしで ビンゴゲームが作れる！？」というネタの続きです。年をまたいじゃいました。

同シリーズ前回の記事

前回、ユーザー用のビンゴカードのシート作成まで完了しました。

いよいよ、ビンゴマシンを作っていきましょう。

ビンゴゲーム作成の手順

以下の流れで、前回は4までを解説しました。

1. スプレッドシートの設定で反復計算を オンにする

2. ビンゴの大枠を作る

3. ビンゴカードを作成する（数式）

4. ビンゴカードを作成する（条件付き書式）　← ここまで前回

5. ビンゴマシンを作成する（数式）

6. ビンゴマシンを作成する（条件付き書式）

7. 完成、テスト

まずは大枠まで作ったビンゴマシンに式を入れて、くじ引き（ランダムな重複しない数字が表示される）機能を実装していきましょう。

5.ビンゴマシンを作成する（数式）

1から T6 セルで設定した数値（最小25、最大75）までの 重複しない数値をランダムに表示させていく ＆ 一度 表示した数値は変動しない（ロックする）をどう実現するか？

1. 1回1回のチェックで重複しない数をランダムに表示させる

2. 最初に全くじ引きを実施して一つ一つ表示する方法

この 2つの方法が思いつきますが、まずはリアルビンゴっぽい1回1回クジ引き（数字をランダム表示）させる方法を試してみましょう。

ビンゴマシン検討1. 1回1回のチェックで重複しない数をランダムに表示させる

都度クジ引きを行う方式、つまりP3 以降のチェックボックスにチェックを入れたときに

• 1～設定数値 までの数字から

• 既にQ3:Q77 に表示されている数字とは重複しない

• ランダムな数字を表示させる　かつ

• Q3:Q77に 表示された数字は固定される

• 別の場所で開いているユーザーと一緒に楽しめる

これらの条件を満たしいビンゴを実現すのは、かなり難しいのです。

というか、色々やりましたが物理的に無理でした。（もしかしたら回避方法があるかもですが、 mirは出来ませんでした）

一番無理だったのが、（オンラインビンゴとして ）「別の場所で開いているユーザーと 一緒に楽しめる」 部分です。

この部分を諦めた場合、つまり オンラインビンゴとしての利用ではなくユーザー側のビンゴカードシートを使わず、印刷した紙（もしくは市販のビンゴカード）でビンゴをする時に、ビンゴマシンとしてだけ使う なら以下のように 2か所に式を入れる方法で実現できます。

実際にくじ引き（重複しないランダムな数値を取得する）式は、B3に入れた以下になります。

これはまさに循環参照を活用した式で、

このFILTER式で、既に出た数値（Q3:Q77に既に固定表示されている数値）を除外してランダム表示させています。

COUNTIF(Q3:Q77,SEQUENCE(T6))=0

この部分の条件式で、Q3:Q77に 1つもない SEQUENE(T6) ※T6が35なら 1～35の連番 のみに絞り込んでいます。重複排除ですね。

この重複排除した 1列目（SEQUENCEで生成した ビンゴの数値）、2列目（乱数）の配列をSORT関数で 2列目の乱数で並び替えを実施、INDEXで1列目の1番上の値だけを取得しています。

そして、チェックで出た数値を固定していく式がQ3に入れた

こちらになります。割と簡単でしたかね？

動作検証：1回1回のチェックで重複しない数をランダムに表示させる

実際に動かしてみましょう。

B3セルのクジ引きゾーンが、数字が動くことで ルーレット感もあっていい感じですね。P2のチェックで ビンゴスタートし、P3以降のチェックで B3 に表示された数字を固定しています。

Q3以下が 全部 B3を参照しているので 見栄えが悪いですが、この部分はこのような形で事前に参照にしておかないと 安定しません。見栄えの悪さは、後で条件付き書式で文字を 白（背景と同じ）にすれば見えなくできるのでOK。

なかなかいい感じですが、これは オンラインビンゴでは使えない（せっかく作ったビンゴカードシートが 使えない）という問題があります。

issue: 乱数関連の表示は 別シート、別ウィンドウから参照すると安定しない

たとえば オンラインビンゴとして遊ぼうとした際に

このように同じスプレッドシートを  別のPC・スマホで開いた際、同じビンゴマシンシートを見ているはずなのに、B3の数値が違ってしまいます。

これは 1つのPCで別タブで 開いても同様で、どのビンゴシートもビンゴマシンシートのB3を参照しているはずなのに違う数値が表示されています。

上のように Deleteで乱数を再計算させたgif動画だと、より顕著にわかりますね。

1回も同じ表示になりませんｗ

これではオンラインビンゴとしては ゲームが成り立ちませんね。

乱数を使った式は、別で開いた時に 同じ表示にならない（不安定）

ってことです。

他の人とスプレッドシートを共有する時は注意しましょう。

循環参照では、サーバー側とブラウザ表示でズレることがある

では、なぜ固定しているはずなのに、このようなズレが発生するか？

星の王子さま の名言「本当に大切は物は目に見えない」に通じるものがありますが、乱数は今見えてるものは実は正しくないんです。

真実は更新された先（サーバーの向こう側）にあります。

どういうことか？

循環参照で 33,3,22,26,14 と乱数を固定したように見えますが、F5更新をかけてブラウザを開きなおすと、まったく違う数字に差し変わってしまいます。

これは、実はブラウザで表示されている固定された（固定したつもりの）数字と、サーバー側で認識している（実際に）固定された数字にズレが発生しているからです。

固定した状態で更新をするとブラウザ上で表示されていた固定部分の数字は、サーバー側で認識している固定数字に置き換えられます。

一度更新してサーバー側と同期すれば、再度更新しても変わりません。しかし、その後にチェックで固定した（同期していない）数字は、再度更新の際に置き変わってしまいます。

つまり、以下のように 別窓で 同じビンゴマシンシートを開いて、普通にチェックを入れてクジ引きをしていくとズレが発生しますが、それぞれだ更新をかけると同じサーバー側の数字に揃うってことです。

1回くじを引くたびにオーナー、ユーザーともに更新をかければ、オンラインビンゴは成立するってことです。

でも、毎回毎回全員更新かけるって・・・。
さすがに現実的じゃないですね。

では、もう一つの方法ならオンラインビンゴとして成り立たせることが出来るのか？

検証してみましょう。

ビンゴマシン検討2. 最初に全くじ引きを実施して一つ一つ表示する方法

もう一つは、毎回クジ引きをするのではなく、最初に全てシャッフルしておいて、固定した数を上から順に表示する、という手法。

検討1が、毎回ガラガラポンをしていたのに対して、こちらは 最初にトランプをシャッフルして山を作り、上から順にカードをめくっていくイメージです。

検討1は、B3 に 実際のクジ引き式（ランダムに表示させる式）を入れてましたが、今回は逆で Q3が 実際のクジ引き式（最初にランダムに数字が出る順番を確定させる式）となっています。

こちらを Q3に入れることで、1～T6の数値（今回は設定している 35）までの 数値をランダムに並び替えたものを P2にチェックが入った段階でロックさせています。

R3 以下に  1～35（上の場合は35に設定しているので）をランダムに並び替えた、縦1列の配列が表示されているのがわかりますね。

ランダム並び替えの部分は

この式ですね。 T6セルの数値(上の場合は35）を使って、SEQUENCEで 1～35までの連番を生成、それを RANDARRAY(T6) で生成した 縦に35個の乱数をキーに SORT関数で並び替えています。

この部分は、前回のビンゴカードシート作成の際に使った式とほぼ同じです。検討１の式に比べると簡単ですね。

もちろん、Q列に最初から最後まで答え（くじ引き結果）が全部表示されちゃってるので、これは P列にチェックが入った時だけ Q列を見えるようにする条件付き書式を後ほど設定します。

B3 には P3:P77で チェックが入っている中の一番下の右隣り（Q3:Q77)を、ビ表示させる式を入れてます。

ここは XLOOKUP の第6引数を -1とすることで、 検索値 trueを 下から 検索しています。

さらに、第4引数（エラー時の値）を 空にすることで、P列が何もチェックされてない状態、もしくは BINGO STARTの P2だけチェックされた状態では 一致なしエラーとなるケースで空白を返しています。

XLOOKUPの検索モードについては、過去の noteで解説してます。

issue: この方法でもサーバー側とのズレはある

この方法だからといって、乱数における サーバー側と ブラウザ表示のズレは発生します。

上記のようにP2をチェックして 22,25,33,28といった並びを固定しても Ctrl + F5で ブラウザを更新すると 24,16,29,13 とまったく違う数値になってしまいます。

でも今回の場合は、最初に全部の並びを固定しているので、実際のビンゴ開始前に1回 更新をかけてしまえば OKです。

上の gif画像では、同じ シートを別窓で開いているだけなのに、並びが一致していないのがわかりますね。

でも、P2チェックで固定してから更新をかけると 同じ並びになっています。この状態にすれば、オンラインビンゴとして機能するわけです。

もちろん、更新は オーナー側、ユーザー側それぞれ必要となるので、ユーザー側に対応をお願いしないといけません。運用ルールですね。

一応、GAS無しという縛りを無視して、ここだけ GASを使っちゃうという手もあります。

Q列で直接ランダム表示させる式を入れずに、例えば隣のR列でランダム表示させ、ロックしたら GASで R列を getVlaues, P列に setValuesとすると、サーバー側の値が貼り付けられます。

R列の数字と全く違う並びが Q列に貼付けされましたが、更新をかけると R列の数字がサーバー側の数字に変わり、Q列と一致しています。

でも今回はGAS不要をうたってるので、これは使わない方向でいきましょう。

ビンゴマシン 2つの方法 まとめ

2つの方法を検証しましたが、どちらもメリット・デメリットあります。

という使い分けが良さそうです。

最後に 見栄えを良くして 余計な数字を見せないようにしましょう。
条件付き書式を使います。

条件付き書式はどちらの方法でも 同じ設定でOKです。

6. ビンゴマシンを作成する（条件付き書式）

以下のようにビンゴマシンシートに4つの条件付き書式をセットします。

1つずつ進めていきましょう。

P列にチェックの入っていないセルを見えなくする書式設定

まずは 全部丸見えではなく、P列のチェックが入った隣のQ列のみ 数字を見せたい ってことで、Q3:Q77 に対して 条件付き書式を設定します。

条件付き書式のカスタム数式は、範囲に対して自動でスピル計算されるので、範囲の開始セル（一番左上のセル） で 結果がTRUEとなる式を組み立てればよいです。

Googleスプレッドシートの条件付き書式については、カレンダーの回の 土日祝色付けで （わりと）詳しく説明しています。

今回の場合はP列にチェックが入ってない時は 見せない（背景の白と同じく文字を白にする）とすればよいです。

チェックが入ってない時を TRUEとするってことで、本来のチェックボックスの

チェックが入っている TRUE
チェックが入ってない FALSE

を反転させます。 NOT関数の出番ですね。

これで Q列の設定は OKです。

チェックを付ける前の ６番目 Q8は空欄に見えるけど、実際は 26が入ってるのがわかりますね。

チェックと同時に、固定 + 条件付き書式解除 して 表示させてます。

前回のビンゴゲーム記事、ビンゴシートの条件付き書式の際、

このような式で P列を条件に加えていた理由は、この為だったんです。

見えてなくても次に出る数字がP列に入っているので、チェックを条件に加えないと、次に出る数字まで色付けされてしまうのを避ける為です。

使っていない（数字がない）セルをグレーにする書式設定

これは カスタム数式不要で、空白を条件にすればよいです。簡単ですね。

設定できたら「完了」ボタンではなく、右下の「 ＋ 条件を追加」を押すことで、そのまま D3:M10の範囲に 連続で別の条件付き書式を設定できます。

動きを確認しておきましょう。

T6 でビンゴのナンバーの最大値を切り替えた際に、D3:M10の連番が変動します。それに合わせて使っていないセルがグレーになりました。

今出た数字のセルを 目立たせる 書式設定

B3 セルで今出た目を目立たせる条件付き書式を設定しましょう。

B3は絶対参照にしておく必要がある点に注意。

動作確認し、問題なければOK。

既に出た数字を色付けしてわかるようにする書式設定

最後は既に出た数字をわかるよにする条件付き書式を設定しましょう。

ここの式は、P列にチェックがついている Q列の数字が D3:M10にあったら COUNTIFSで1以上（TRUE）となるという式です。

これを動かすと、今出た数値に加え今までに出た数値が色付けされてわかるようになります。

ここで大事なのは、条件付き書式の設定順序です。

=COUNTIFS($P$3:$P$77,true,$Q$3:$Q$77,D3) では 今出た（今チェックした）数字も条件に合致してしまいますが、先に設定した 　=D3=$B$3　の条件が優先されるので、今出た数字がわかるようになっています。

Googleスプレッドシートの 条件付き書式が、一致した条件でストップする仕様についてもカレンダー回で触れています。

これで ビンゴマシンも完成しました。

実際に動かしてテストしてみましょう！

7. 完成、ビンゴマシンをテストしよう

2通り検証したビンゴマシンをそれぞれテストしてみましょう。

ビンゴマシン1.１回１回ランダム表示

こちらはビンゴマシンの表示と ビンゴカードシートでズレが発生するので、残念ながら ビンゴカードシートは 使えません。（印刷して使うのはOK）

あくまでも プロジェクターやモニター等に ビンゴマシンシートを映しながら、皆がリアルで集まって遊ぶ 用のビンゴ になります。

以下の手順で 遊びましょう。

B3セルの数字が ちょっと動くのがいい感じですね。

リアルでしか使えませんが、こちらだとトラブル等で 1つ戻す（チェックを一回外して入れ直す）引き直しをした時に、再度ランダム抽出となる点も魅力です。

1回1回のランダム表示でも、最後まで重複しない数字が表示されることも確認しておきましょう。

35番目（P37）にチェックをつけると、Q列に一致しない数がないので B3が #N/A エラーになってますね。

エラー処理してもいいですが、普通はビンゴは最後まで引かずに終わるので気にしなくていいでしょう。

遊び終わってチェックを一括で外す際は、以前取り上げたチェックボックス一括ON/OFFが使えますね。P列を選択して スペース2回でOKです。

ビンゴマシン2. 最初に全くじ引きを実施

リモートでメンバーと オンラインビンゴを楽しむならこちらの方法になります。

同じスプレッドシートを遠隔で 複数人が開きながら遊ぶ、という使い方を想定して動かします。

Google Meetや他の会議・会話ツール、Discordなどを使えばみんなでワイワイ楽しめるかも。

遊ぶ手順としては以下の通り

Play1. 【オーナー】ナンバーの最大値を決める （25～75）
ここはビンゴ1と同じで問題ないので、

Play2. 【ユーザー】ビンゴシートに名前を入力してもらう
ここから見ていきましょう。

オーナーが最大値を35としたので、ユーザーがスプレッドシートを開くと、ユーザー側のビンゴカードシートは、上のように 1～35の数字がランダムに入ります。

まだロックしていないので、自分、オーナー、他のユーザーのいずれかが 編集する度に 乱数が再計算される為、ビンゴシートの中身はコロコロ変わります。

ビンゴの出る数値は最初に全部決まっていたとしても、ビンゴカードシート がランダムに変化するので、どのシートが有利といったことは一切ありません。

ユーザー側数名が Delete連打をして、再計算を繰り返すと固まったり、落ちたりするので注意。

F５アタックならぬ、DELアタックは禁止行為ですｗ

ここで名前を確定させた際にも再計算で、ビンゴカードの数字が変わるのも楽しみの一つです。

ただし、現状の設定では 他のユーザーが名前を入れたり編集したら、全ビンゴカードが再計算となるので、ユーザーが「この並び（ビンゴカード）でロックしたい」という選択は出来ません。

そもそもビンゴマシンと同様に、固定したつもりでも 更新をかけると ビンゴカードの数字の並びもサーバー側の固定した並びに置き換わります。

ビンゴシートをユーザーが好みで 選ぶことは、まず不可能と思った方がいいでしょう。

Play3. 【オーナー】ビンゴシートをロック 
Play4. 【オーナー】BINGO STSART（P2） にチェックを入れる

ユーザー側が全員名前を書き終わって準備できたら、オーナーがビンゴシートをロックします。

T3にチェックを入れるだけですが、これによってビンゴカードはロックされ、移行は数字が変わりません。

ただし、この段階では  同じユーザー1（ビンゴ太郎さん）のビンゴカードでも 開いているブラウザによって、ズレが生じています。

左と右で ビンゴカードの数字の並びが違うのがわかりますね。

Play4. 【オーナー】BINGO STSART（P2） にチェックを入れる
重要：【オーナー】【ユーザー】各自 ブラウザを更新

P2にチェックを入れてビンゴマシンも固定させてから、オーナー、ユーザーともに ブラウザを更新することで、全員の環境を同期させます。

同じビンゴ太郎さんの ビンゴカードを見てるはずなのに、上は まったく違う数字の並びになってます。更新後は全て揃っているのがわかりますね。

重要なので、もう一度書きます。

1. ビンゴマシンシートの T2　（ビンゴシートロック）にチェック

2. ビンゴマシンシートの P2　（ビンゴスタート）にチェック

3. 参加者全員が Ctrl + F5 もしくは 更新ボタンで ブラウザを更新

これで 全員の見ている景色を一致させます。
これでようやくビンゴがスタートできるのです。

実際にゲームとして動かしてみましょう。

こんな感じになります。

当たり前のことなんですが、数値のズレで振り回された mirとしては、B3の数値が揃ってるのが感動モンですｗ

各シートで出た数字には 自動で色がついてます。

10回目のくじ引き で19が出た段階で、ビンゴ次郎さんが リーチ、ビンゴ三郎さんは Wリーチです！

リーチ、ビンゴは申告制なので ユーザーが自分で言う必要があります。

そして遂に13回目、9が出て ビンゴ三郎さんが揃いました。
BINGO！です。

頑張れば、BINGO表示を数式でやれそうですが、ちょっと斜めビンゴは面倒そうですね。

これ以上数式増やすと シートが重くなりそうですし、今回は ここまでにしておきましょう。

Googleスプレッドシートで作るビンゴゲーム まとめ

チェックボックス小ネタの回で思いついて、なんか面白そうかなと軽い気持ちで書いてみたら、結構苦戦しましたね。年も越しちゃいましたｗ

重複しない数をランダム表示、ロックで固定までは良かったんですが、まさかの表示ごとにズレが発生。

オンラインビンゴで遊ぶ際の、この 同じスプレッドシートを別に開いた際に発生するズレには頭を悩まされました。

最終的に 数字をロックして固定表示した後で、全員がブラウザ更新という運用ルールでの解決としました。

ぶっちゃけ、最初からGASでやった方が簡単な気もしますが、循環参照・反復計算を活用した面白ネタということで、興味あれば是非試してみてください。

ちょっとだけこの流れに関係するんですが、次回は Googleスプレッドシートで スピンボタン ってテーマで書こうと思います。

■次のシリーズの記事