Excelの関数と条件付き書式だけでビンゴマシンを作る！【NO マクロ】

今回はExcelの関数だけでビンゴマシーンを作る方法をご紹介します。

今回は１～75の数字でやっていますが、増やすことも減らす事も可能です。

ビンゴマシンを作る上で３つ重要なことがあります。

１～７５の範囲の数字をランダムに出力する事
一度出た数字は表示しない（除外）
除外した数字を視覚でわかるように色付け（条件付き書式）

これを抑えた上で作っていきます。
特にCountifの使い方の勉強にもなりますのでぜひ、試してみてください。

手順
まとめ

手順

前準備（１～７５の数字を計算する用の場所を作成）
１～７５の範囲の数字をランダムで出力する
一度出た数字を除外する
条件付き書式を使って、出た数字に着色する
NAエラー処理と微調整

今回はINDEX・MATCHコンビ（VLOOKUPの代わり）と、MAX・RANK・RUND・COUNTIF・IFNA関数を使って作っていきます。

たくさん関数を使いますが、できる限りわかりやすく説明していきます。

手順①：前準備（１～７５の数字を計算する用の場所を作成）

エリアとしては図のように６つ作ります。

それぞれの役割は以下の通りです。

①のA列は出玉の列（今回の場合は１～７５になります。）

②のB列はランダムで計算する列

③のC列はB列を元にランク付けする列

④Bingoでいう所の出玉
大きく見せるためにセル結合をしていますが、最初の段階ではセル結合をしないようにしてください。
今回配列数式を使用するのですが、セル結合をすると配列数式が使えなくなるためです。

⑤出た番号を記載する表

⑥出た番号を元に色付けし、現在出ている番号を視覚的にわかりやすくする場所

これで下準備が終了です。
次から、ビンゴマシンの関数式を作っていきます。

手順②：１～７５の範囲の数字をランダムで出力する

ここでは「１～７５の数字が出ること」と「それらの数字がランダムで出るようにすること」の方法になります。

まずは数字がランダムで出るようにする方法ですが、RAND関数を使って、ランダムな数字を出力するしていきます。

RAND関数とは

RAND では、0 以上で 1 より小さい実数の乱数を返します。ワークシートが計算されるたびに、新しい実数の乱数が返されます。
Microsoft サポート引用

テストデータなどを作る際、重宝する関数ですので覚えておくといいです。

それではB列に=RAND()をA列の１～75に全て入力します。
入力すると以下のようになります。

これで、ランダムに数字が入るようになりました。

この次は、１～７５の範囲の数字でランダムになるようにしていきます。

B列を元に、ランク付けをしていくのですが、C列にRANK関数でB列の乱数に対しランク付けします。
式はRANK(数値,参照,[順序])となりますので、この場合=RANK(B2,$B$2:$B$76)を入力します。

オートフィルをして、１～７５の数字までRANK関数を適用させます。
ちなみに、RANDBETWEENという関数もあるのですが、これは使いません。（重複する可能性がRAND関数と比べ高くなるためです。）

これで、１～７５の数字がランダムで出るようになりましたので、④エリアで出玉を出すようにしていきます。

入力する式は以下の式です。

=INDEX($A$1:$C$75,MATCH(MAX($C$1:$C$75),$C$1:$C$75,0),1)

この関数でやっていることはこのようなイメージです。

次は、出た数字の除外方法です。

手順③：一度出た数字を除外する

このBingoマシンを作る過程で一番難しいところです。
関数の式はこちらになります。

=INDEX($A$1:$C$75,MATCH(MAX((COUNTIF($I$2:$P$11,$A$1:$A$75)=0)*($C$1:$C$75)),$C$1:$C$75,0),1)

配列数式になりますので、決定する際はCtrl ＋ Shift ＋ Enterで確定してください。

手順②の時と変わっている部分がこの式になります
(COUNTIF($I$2:$P$11,$A$1:$A$75)=0)*($C$1:$C$75)

やっていることは以下の２つです。

出た番号（I2:P11)の中にA列の数字があるかどうかをCountifで判定する
Countifで0となるもの（まだ出ていない数字）をTRUEにする
TRUEは１をFALSEは0をC列のランクの数字をかける

Countifで判定したものイメージがこちらです。

これで出た番号エリアにあるものはFALSE＝0、ないものはTRUE＝1となります。

これにC列のランクをかけると、既に出ている番号は0になり、出た番号エリアにない数字だけが表示できるようになります。

手順④：条件付き書式を使って、出た数字に着色する

既に出た番号かどうかがわかるようにするために、⑥のエリアに色付けしていきます。

条件付き書式で着色するのですが、まず下図のように色付けするエリアを選択します。
そして、条件付き書式 → 新しいルール → 数式を使用して、書式設定するセルを決定の順にクリックします。
ルールには=COUNTIF($I$2:$P$11,I13)=1を入力して、書式を設定し完了です。

先ほども使用しました、Countifですがここでも判定で使用します。
この場所では、出た番号と下の表にある数字が一致していたら、下の表の数字のセルの色を付ける。
としています。

手順⑤：NAエラー処理と微調整

最後は、１～７５の数字が出尽くすと、NAエラーになりますので、その回避を入れます。
A列からC列を非表示にして、出玉エリアのセルを大きくして見やすくして完成です。

まずは、NAエラー回避ですが、③の式をIFNA関数で閉じます。

=IFNA(INDEX($A$1:$C$75,MATCH(MAX((COUNTIF($I$2:$P$11,$A$1:$A$75)=0)*($C$1:$C$75)),$C$1:$C$75,0),1),”終了!”)
IFNAエラー関数は、(値,NAの場合の値)の構成になっていますので、最後のNAの場合の値に”終了！”と出るようになっています。
終了と出るようにしたイメージがこちらです。