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

mir

2022年12月29日 11:23

2022年最後の記事です。

Googleスプレッドシートのチェックボックス関連ネタ をもう少しやります。

間に BYROWのメガシンカや和暦変換の記事を挟んだんで、だいぶ間が空いてしまいましたが、前のチェックボックス記事の続きです。

GASなしで実現できるチェックボックス小ネタを幾つか紹介しましたが、最後にチラッと 乱数の固定を応用することで「GASなしでビンゴゲームが作れる！？」という話に触れました。

今回はこの GASなしで Googleスプレッドシートでビンゴゲームを作る方法を解説していきましょう。もちろんチェックボックスを活用します。

同シリーズ前回の記事

Googleスプレッドシートで作るビンゴゲームの流れ

前提条件と完成形のイメージ

GASやプログラミングは一切なしで作るので、凝った演出・映え的なカスタマイズは無理（もしくは割愛）と思ってください。ビンゴの基本部分には対応しています。

以下のようなビンゴゲームを想定しています。

・GASは使わない
・反復計算、シート関数、条件付き書式、チェックボックスを利用
・オーナー1名、ユーザー最大4名で遊べる
・一つのスプレッドシートの中で 5枚のシートを使って行う
・シート1 をビンゴマシン用とし、シート2～5をビンゴカードとする
・ビンゴカードは 5x5 真ん中がFREEとなっているものをランダムに生成
・今出た数字、過去に出た数字は自動でビンゴカード上で色付け
・ビンゴの数字の最大値は 25～最大75まで切り替え可能とする

リーチ！やビンゴ！といった宣言 は数式で拾うことも出来そうですが、今回はその要素は割愛して ユーザーからの申告制としましょう。

ビジュアル（映え）に関しても、もうちょい凝ることも出来ますが、これも本題から外れるので見栄えにはあまり手を加えていません。ご自由にどうぞです。

ユーザーは4名としてますが、単純にビンゴカード シートのコピーで増やせます。ただビンゴシートを増やすごとに、計算処理・条件付き書式の処理が増えて 動きが重くなるんで注意。

もちろんビンゴシートを紙に印刷（もしくは市販のビンゴカードを利用）してビンゴマシンだけの利用とすれば、大人数で遊ぶことも可能です。

ビンゴカードの数字を 25～75の可変にしたのは、人数やゲーム時間に応じて調整しやすくする為です。

1～75までの24個のランダムな数字（5x5 の真ん中のFREEを１つ除くので 25-1 ）とすれば、なかなかビンゴにならないので大人数で時間長めに盛り上がりたい時に使えますし、短時間でサクっとやりたい場合は上限数値を 25とか35 に指定すれば良いですね。

完成形のイメージはできたでしょうか？

作成の手順

以下の流れでやっていきましょう。
プログラミングは不要ですが、それなりに工数はかかります。

スプレッドシートの設定で反復計算をオンにする
ビンゴの大枠を作る
ビンゴカードを作成する（数式）
ビンゴカードを作成する（条件付き書式）
ビンゴマシンを作成する（数式）
ビンゴマシンを作成する（条件付き書式）
完成、テスト

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

これはチェックボックス番外編の内容とまったく同じなので、そちらの記事を参考に設定してください。

ちなみに「反復計算」の設定は、スプレッドシートのコピーを作成した場合も引き継がれます。つまりコピーしたスプレッドシートも反復計算がＯＮになっている状態です。

影響はないと思いますが、一応注意しておきましょう。

2.ビンゴの大枠を作る

まずは大枠を作っていきましょう。シートを1枚追加して、

シート1　・・・　シート名を ビンゴマシン
シート2　・・・　シート名を ユーザー1

このようにシート名を変更。ビンゴカードのユーザー用シートは4つ作りますが、完成したシートをコピーした方が楽なのでそれは最後の作業です。

ビンゴマシンシートの大枠を作成

さすがに、こういうのは動画でやった方が伝わりやすい気がしますね。とりあえずビンゴマシンシートは上記のように設定します。

まだ数式や条件付き書式は入れなくてOK。
色、セルのサイズ、文字サイズ、フォントは好みで調整してください。

ナンバーの最大値を入れる T1セルは、25～75 で可変にしたいので、10刻みの プルダウンにしておきましょう。

（余談）プルダウンの設定がサイドバーになった

T6 を選択してメニューから　データ > データの入力規制と選び、「プルダウン」を選択。

25,35,45,55,65,75

と数値を入れてエンターを繰り返し選択肢を作成。
さらに詳細オプションを開き、表示スタイルを矢印 としてから完了。

ちなみに、直近 2022年12月のプルダウンに関するアップデートで、プルダウンの設定がダイアログから サイドバーに変更になりました。

さらにプルダウンのスタイルのデフォルトがチップ表示になったんで、旧来型のプルダウンにしたい場合は表示スタイル矢印とする必要があります。うーん、わかりづらい。

プルダウンの色付けは容易に出来るけど、今のところメリットをあまり感じないアップデートだな。。

このT6セルと連動したい 2か所の設定をしましょう。

ビンゴの際、今が何回目の抽選（ガラガラ）かわかるように、P列のチェックボックスの左隣（Ｏ列）に T6と連動する連番が入るようにしましょう。

O3セルに以下の式をいれます。

=SEQUENCE(T6)

これによって1から T6で設定した数値までの連番が縦並びで表示されます。

同じく 今まで出た目が色付けされるゾーンも T6 に連動して数字を表示さたいので、D3セルに以下の式を入れます。

=ARRAYFORMULA(IF(SEQUENCE(8,10)>$T$6,,SEQUENCE(8,10)))

SEQUENCEで 10行 8列に1～80までの数字を生成し、それをIF でT6より大きい場合は空白にするとしています。

以下のように T6 のプルダウンと連動するか確認しておきましょう。

これで ビンゴマシンシートの大枠は準備OK。

ビンゴカードシートの大枠を作成

次にビンゴカードシートの大枠を作っていきます。

こっちは非常にシンプルで、方眼紙のように 行・列が見た目同じくらいのサイズになるように調整して E3:I7 を外枠で囲いビンゴカードとします。最初から穴があく 中央の Free マス G5は最初から色付けしておきましょう。「明るい黄3」としました。

行・列を同じピクセル値にすると、見た目的にやや縦長になるので 行を列の9割くらいのピクセル値にするといい感じです。

ユーザーの名前入力用に F1:I1をセル統合、書式中央寄せにして薄く色付けして下線を引いておく。

ビンゴの出た目がユーザーのシート側でも見れるように、B3:C4をセル結合し

='ビンゴマシン'!B3

と式を入れる。

その他の見栄えは画像を参考にしていただき、お好みで調整を。不要な行、列は削除して領域を小さくしておくと良いです。

これでビンゴカードの大枠も完成。

こっちは簡単ですね。このままビンゴカードを先に作っていきます。

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

いよいよビンゴカードの部分の数式を組んでいきましょう。

ここは QAでいってみましょう。
自信のある人は数式作成にチャレンジしてみてください。

Q. ビンゴカードを 1行数式と反復計算で作るには？

条件は以下の通りです。

■ビンゴカード作成の条件
・E3セルのみに式を入れる
・5x5 マス（E3:I7）にナンバーが展開される
・カードの中央 G5はナンバーではなく Free という文字を入れる
・ナンバーは
　- 別シート（ビンゴマシンシート）の T3 (25～75）を上限とした
　- 1～上限数値までの重複しないランダムな整数
・ビンゴマシンシートの T3（カードロックのチェックボックス）が
　チェック ONの時はビンゴカードが固定される（変動しない）

以下のような表示・動きになればOK

どうでしょう、式を作れそうでしょうか？

↓

ここから回答

↓

A. ビンゴカードを 1行数式と反復計算で作る

ちょっとハードルが高いので、まず条件を緩和して簡単な式を作ってみましょう。

まずは簡単な条件でビンゴカードを作ってみる

■ビンゴカード簡易版の条件
・E3セルのみに式を入れる
・5x5 マス（E3:I7）にナンバーが展開される
・~~カードの~~~~中央 G5はナンバーではなく Free という文字~~~~を入れる~~
・ナンバーは
　- ~~別シート（ビンゴマシンシート）の T3 (25～75）を上限とした~~
　- ~~1～上限数値~~ 1～25までの重複しないランダムな整数
・ ~~ビンゴマシンシートの T3（カードロックのチェックボックス）が~~
　~~チェック ONの時はビンゴカードが固定~~~~される（変動しない）~~

取り消し線でいくつかの条件を一旦除外しました。
これなら少し難易度が下がったんじゃないでしょうか？

この要件は、1～25の数字を 5x5 のマスにランダムに入れれば良いってことです。

「ランダム」ということは、乱数を使いそうだってのはわかりますよね？
でも上限、下限を設定してランダムな整数を返す RANDBETWEEN関数を Arrayformula と組み合わせて

=ARRAYFORMULA(IF(SEQUENCE(5,5)>0,RANDBETWEEN(1,25),))

こんな式を E3に入れてみても・・・

ランダムではありますが、全てのマスに同じ数が入っちゃいます。

同じく RANDARRAY という乱数配列を返す関数があるので

=RANDARRAY(5,5)

こうしたところで、

ただの乱数（0から1までの間の小数）が 5x5マスに入るだけで、1～25の整数という条件にはマッチしません。

ではどうするか？

これは、1～25までの数値を 25個の 乱数をキーに並べ替えをして、それを 5列折り返しにすると考えれば良いです。

ステップを踏んで作ると以下の流れになります。

① 1～25の縦並びの数字と 25行1列の乱数配列を用意する
② 乱数をキーに 1～25を SORTで並び替え
③ 並べ替えた 1～25の1列を5列折り返しで配置

① の1～25の縦並びは SEQUENCE(25)、25行1列の乱数配列は RANDARRAY(25)　とするのは、わかりますね。
※列数は省略で1という扱い

②は並び替えなので SORT関数の出番です。
画像は一度出力して範囲を指定してますが、
①の式をそのまま SORT関数に使うと

=SORT(SEQUENCE(25),RANDARRAY(25),true)

このようになります。昇順、降順は気にしなくてよいので、とりあえず true でよいでしょう。

③はちょっと難しいです。
この 1～25の数字をランダムに並び替えた 1列の配列を 5x5 （折り返し）にするには、Excelであれば WRAPROWS 関数 の出番ですね。

でも残念ながら Googleスプレッドシートにはこれに該当する関数がないので、複数の関数を組み合わせて自作するしかありません。

といっても、この方法は土日更新の現在進行中の「Excel 14の新関数」シリーズで紹介しています。

Arrayformula + SEQUENCE + XLOOKUP で対応できますね。

①の式をそのまま使って式をつくると

=ARRAYFORMULA(XLOOKUP(SEQUENCE(5,5),SEQUENCE(25),SORT(SEQUENCE(25),RANDARRAY(25),true)))

こうなります。

これが 25までの数値を 5x5 にランダムに表示する 簡易版ビンゴカード式です。

可変する上限への対応を考える

■ビンゴカード簡易版の条件
・E3セルのみに式を入れる
・5x5 マス（E3:I7）にナンバーが展開される
・~~カードの中央 G5はナンバーではなく Free という文字を入れる~~
・ナンバーは
　- 別シート（ビンゴマシンシート）の T3 (25～75）を上限とした
　- 1～上限数値までの重複しないランダムな整数
・ビンゴマシンシートの T3（カードロックのチェックボックス）が
　チェック ONの時はビンゴカードが固定される（変動しない）

先ほど除外した条件を1つ盛り込みましょう。

5x5のマスにちょうど収まる 25ではなく、1～ 35、または45と最大75までの可変数字を上限とした場合はどうなるでしょうか？

実は、これは気にする必要はないんです。

Arrayformula + SEQUENCE +XLOOKUP の式（最後の 5x5 の折り返し処理）で並び替えたランダムな縦1列の数値配列の 上から25番目までが使われるってだけなんで、35でも45でも75でも影響はありません。

先ほど作成した式の 25 としていた箇所をビンゴマシンシートの T6参照に変更するだけで OK

=ARRAYFORMULA(XLOOKUP(SEQUENCE(5,5),SEQUENCE('ビンゴマシン'!T6),SORT(SEQUENCE('ビンゴマシン'!T6),RANDARRAY('ビンゴマシン'!T6),true)))

ちょっと煩雑なんで、この辺りからLAMBDA化して（ラムって）いきましょう。

=LAMBDA(num,ARRAYFORMULA(XLOOKUP(SEQUENCE(5,5),SEQUENCE(num),SORT(SEQUENCE(num),RANDARRAY(num),true))))('ビンゴマシン'!T6)

ビンゴカード中央を Freeの文字にする

続いて

・カードの中央 G5はナンバーではなく Free という文字を入れる

この条件に対応させましょう。

これも難しいことは無くて、特にこの処理に関しては ExcelでWRAPROWS関数を使う折り返し処理に慣れていると、気づきにくい部分かもしれません。

今回の折り返し処理は、XLOOKUPの第1引数（検索値）を SEQUENCE(5,5)で生成（ビンゴカードのマスに連番を振るイメージ）し、並び替えした 1～最大値を 順番に 1の箇所に1番目、2の箇所に 2番目と入れていく動きです。

※詳しくは過去記事参照

つまり画像のように中央の黄色箇所（Freeを入れたいセル）の番号は 13 なんで並び替えたデータの13番目を参照してくるわけです。

それなら、SEQUENCE(5,5)が 13の時だけ Freeを入れるように IFで分岐させれば良いってなりますね。

=LAMBDA(num,ARRAYFORMULA(IF(SEQUENCE(5,5)=13,"Free",XLOOKUP(SEQUENCE(5,5),SEQUENCE(num),SORT(SEQUENCE(num),RANDARRAY(num),true)))))('ビンゴマシン'!T6)

チェックボックスONでランダム表示をロックする

・ビンゴマシンシートの T3（カードロックのチェックボックス）が　チェック ONの時はビンゴカードが固定される（変動しない）

ランダム表示のロックということで、やっと チェックボックスの出番です。ようやく前回の記事の続きって感じになってきました。

これは、まんま 前回のチェックボックス記事の乱数固定の応用です。

違うのは、シートが別って点とランダムなのが配列というだけですが、これも普通に記述すれば問題ないです。

=IF('ビンゴマシン'!T3,{E3:I7},LAMBDA(num,ARRAYFORMULA(IF(SEQUENCE(5,5)=13,"Free",XLOOKUP(SEQUENCE(5,5),SEQUENCE(num),SORT(SEQUENCE(num),RANDARRAY(num),true)))))('ビンゴマシン'!T6))

ちょっとカッコ悪いというか、ヘンダーソン先生的に言うなら「ノットエレガント」ですね。

LAMBDAの中に入れ込んじゃいましょう。

ビンゴカード数式完成版

=LAMBDA(lock,num,ARRAYFORMULA(IF(lock,E3:I7, IF(SEQUENCE(5,5)=13,"FREE",XLOOKUP(SEQUENCE(5,5),SEQUENCE(num),SORT(SEQUENCE(num),RANDARRAY(num),true))))))('ビンゴマシン'!$T$3,'ビンゴマシン'!T6)

なかなかの難易度でしたが、とりあえず ビンゴカードの数式が完成しました～。

4. ビンゴカードを作成する（条件付き書式）

数式に続いて、ビンゴカードの条件付き書式による色付けを設定しましょう。

今出た目を色付け強調表示する

まずビンゴで今出た目 のマスを目立たせる為に

セル塗りつぶし色・・・　明るいオレンジ3
文字色　・・・　赤

とする条件付き書式を設定しましょう。

これは簡単ですね。

ビンゴの出た目はビンゴマシンシートを参照して B3セルに表示させるように大枠を組んだんで、条件付き書式・カスタム数式で範囲の開始セル（左上）の式を作成すればよいです。

範囲　E3:I7
書式ルール　カスタム数式　=E3=$B$3

これでOKです。

既に出た数字を色付けする

続いて既にビンゴで出た目のマスがわかるようにする為に

セル塗りつぶし色・・・　明るい赤3

とする条件付き書式を設定しましょう。

こちらは別シートの ビンゴマシンシート のQ3:Q77 を参照し、ビンゴシートの数字が Q3:Q77 に存在する場合は書式を適用するという設定にします。

Googleスプレッドシートの条件付き書式は、他ののシートのセルを条件に使う場合はちょっと特殊で、 INDIRECT関数を使う必要があります。

これも過去のカレンダー番外編で触れてます。

重複チェックはCOUNTIFを使えばよいですね。

通常なら以下のカスタム数式を条件としたいところですが、

　=COUNTIF(INDIRECT("ビンゴマシン!Q3:Q77"),E3)

今回はちょっと事情がありまして、

範囲　E3:I7書式ルール　カスタム数式　=E3=$B$3
書式ルール　カスタム数式
　=COUNTIFS(INDIRECT("ビンゴマシン!P3:P77"),true,INDIRECT("ビンゴマシン!Q3:Q77"),E3)

このようにしておいてください。

なんで COUNTIFSにして P列のチェックボックスも条件に加えているか？これは 後で説明します。

ビンゴマシンカードの Q列に適当に手入力、P列のチェックボックスにチェックを入れてみてテストしましょう。

きちんと色付けが反映されていればOK。

これでビンゴカードは完成です。

いよいよビンゴマシーンへ・・・

今回はビンゴゲームの主に ユーザー側のビンゴカードシートの作成までを紹介しました。乱数ロックも登場しましたね。

いよいよ次回はビンゴマシンを GASなしでどのように作成するか？
ですが、申し訳ないですが 年末年始は1週お休みをいただきます。

後編（ビンゴマシン作成）は、年明けまでお待ちください！

■このシリーズの次の記事

この記事が気に入ったらサポートをしてみませんか？