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

未分類

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

結果画像

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

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

  • 1~75の範囲の数字をランダムに出力する事
  • 一度出た数字は表示しない(除外)
  • 除外した数字を視覚でわかるように色付け(条件付き書式)

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

手順

  1. 前準備(1~75の数字を計算する用の場所を作成)
  2. 1~75の範囲の数字をランダムで出力する
  3. 一度出た数字を除外する
  4. 条件付き書式を使って、出た数字に着色する
  5. NAエラー処理と微調整

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

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

手順①:前準備(1~75の数字を計算する用の場所を作成)

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

エリアの説明図

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

①のA列は出玉の列(今回の場合は1~75になります。)

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

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

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

⑤出た番号を記載する表

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

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

手順②:1~75の範囲の数字をランダムで出力する

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

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

RAND関数とは

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

Microsoft サポート引用

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

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

RAND関数の入力図

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

この次は、1~75の範囲の数字でランダムになるようにしていきます。

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

RANK関数の入力図

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

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

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

=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)

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

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

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

COUNTIFで判定したもののイメージ

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

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

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

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

条件付き書式を設定した時のイメージ

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

条件付き書式のルール

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

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

最後は、1~75の数字が出尽くすと、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の場合の値に”終了!”と出るようになっています。
終了と出るようにしたイメージがこちらです。

終了のイメージ画像

A列とC列を非表示・出玉のセルをセル結合で大きくして完成です。

※セル結合する前に関数式を入力しておくことを忘れないようにしてください。

遊び方

  1. F9キー(機種によってFnキー+F9)を押す
  2. 出た番号を、出た番号エリアに入力する

最初は1の手順が必要ですが、2の番号を入力した時に自動計算が走りますので、2の手順を繰り返すだけです。

まとめ

いかがでしたでしょうか。

おさらいになりますが、Excelの関数だけで、ビンゴマシンを作る手順はこちらです。

  1. 前準備(1~75の数字を計算する用の場所を作成)
  2. 1~75の範囲の数字をランダムで出力する
  3. 一度出た数字を除外する
  4. 条件付き書式を使って、出た数字に着色する
  5. NAエラー処理と微調整

マクロが組めると演出などができるようになるのですが、Excel関数だけでも楽しめるものは作れますよ。今回使ったテクニック(除外方法)などは他でも役立ちますので、ぜひ使ってみてください。

コメント