皆さんは何かの余興担当でビンゴゲームを準備したことはありますでしょうか。
ビンゴカードは100均で簡単に手に入りますが、エクセルで簡単にオリジナルのビンゴカードを作ってみませんか？
ビンゴカードはRAND()関数を使って、簡単に作ることができますので、今回はその方法を紹介していきます。
また今回紹介する方法を使うことで、一般的なビンゴカード（１～75の数字からランダムで24個が記載されている）だけではなく、文字や記号も入れたオリジナルのビンゴカードを作ることもできます。
今回説明に使用したエクセルファイルを無料ダウンロードできるようにしましたので、お急ぎの方は記事の後半までジャンプしてください。
私も以前５0人規模の宴会の余興担当となり、クイズとビンゴを組み合わせた”QuizBingo”を企画しました。このQuizBingoはQuizKnockさんの企画をオマージュしたものです。
後半の方に概要も記載しますのでよかったらぜひ参考にしてみてください。
【ランダムな数字を生成】RAND()関数を使う
ここでは、まずRAND()関数の使い方を説明します。RAND()関数は以下のように記載します。
=RAND()
カッコの中には何も入れなくても大丈夫です。
Enterを押して確定すると、0～1の間のランダムな数が自動生成されます。
デフォルトの設定では6桁くらいの表示ですが、小数点第15位までの精度で生成されます。
他のセルを編集したりするたびに、再評価されるので、作業をしていると結構コロコロ数字が変わるような印象になります。
『F9』キー（再計算ショートカット）を押すことで数値が更新されます。
一度ビンゴカードのフォーマットを一度作ってしまえば、F9キーを利用して、形は同じで中の数字が異なるカードを何枚も量産することができます。
RAND()関数に関連した関数としてRANDBETWEEN()関数があります。
RANDBETWEEN()関数は、指定した最小値と最大値の間で整数をランダムに生成してくれます。
例えば『=RANDBETWEET(1,75)』と入力すれば１～７５の整数がランダムで生成されます。
これを使えばRAND()関数とRANK()関数を組み合わせる必要がないように感じますが、
このRANDBETWEEN()関数も、全く同じ数が同時に生成される可能性がある関数です。
実際に１～７５という狭い範囲の数を、75個同時に生成すると、高確率で数字の被りが発生してしまいます。
RAND()関数は数式の中に入れ込むこともできます。
例えば、『=IF(RAND() < 0.5, “成功”, “失敗”)』と入力すれば50％の確率で成功、失敗が判定されます。
その他にも、例えば『=ROUND(RAND(),6)』と入力すると0.1234560000・・・と小数点以下7桁を四捨五入して6桁の表示になります。
通常RAND()関数は小数点以下15桁の精度ですが、ROUND()関数を使えば小数点の数を四捨五入でまとめることができます。（ROUNDDOWN()関数を使うと、切り捨て処理になります。
これを使って、GPS座標のランダム出力をして、Google Mapを使った”GeoGuessr”みたいなゲームをしたら面白そうですね。
どどんRAND()関数はランダムな自動生成ですが、理論上は同時に全く同じ数字が生成される可能性があります。
ただしRAND()関数で生成された2つの数値が、全く同じ数値になる確率は100穣分の１（1/1030）と極めて低いので、不具合が起きる可能性はほぼないといっていいでしょう。
私もいまだかつて出会ったことがありません。
【数字に順番をつける】RANK()関数を使う
RANK()関数
ここでは、まずRANK()関数の使い方を説明します。RANK()関数は以下のように記載します。
=RANK(数値,範囲,順序)
説明のために、以下のRAND()関数で生成した5つの数値を、順位付けしてB列に表示したいと思います。
まずB1のセルに数式を書く場合、RNAK()関数の数値,範囲,順序の部分にはそれぞれ以下を入力します。
数値：評価する数値を参照するのでこの場合は『A1』を入力します。
範囲：どの範囲の数値の中で順位を評価するか指定したいのでこの場合は『A1:A5』を入力します。
この時、他のセルにコピー＆ペーストすると参照範囲がずれるので『$A$1:$A$5』もしくは『A$1:A$5』と入力するとよいでしょう。
順序：0を入力すると降順（大きい順）で、１を入力すると昇順（小さい順）で評価されます。この順序の入力は省略可能で、何も入力しないと降順で評価されます。
RANK()関数は参照範囲の数値に全く同じ数値があると、その数値は同じ順位になります。
同じ順位の数字が2つ以上あると、その次の順位はスキップされます。
エクセルの数式で使う『＄』マークは絶対参照という演算子です。
＄マークを使うことで参照するセルを固定することができるので複数セルに数式をコピー＆ペーストする場合などに非常に便利です。
・行だけ固定したい場合は『A$1』のように数字の前に＄を記載します。
・列だけ固定したい場合は『$A1』のようにアルファベットの前に＄を記載します。
・行列どちらも固定、つまり、特定のセルに固定したい場合は『$A$1』のようにアルファベットと数字のそれぞれの前に＄マークを記載します。
普段は行列の固定を使用することが多いと思いますが、「片方だけの固定もできるんだ」と知っておくと役立つ場面もあるので効率よく使っていきましょう。
RANK.EQ()関数
RANK()関数と似たような関数にRANK.EQ（）関数があります。記載方法や処理動作はRANK()関数とほぼ同じと考えてよいです。
RANK()関数と同様に、順位付けして同じ値がある場合、その値に対して同じ順位を付けますが、次の順位はスキップします。
RANK()関数とRANK.EQ()関数は基本的に同じ機能を持っていますが、RANK.EQ()関数は新しいバージョンのエクセルで推奨される関数です。
どちらの関数も同じ結果を得られるので、今のところは旧バージョンからも使えるRANK()関数の方が汎用性が高いといえそうです。
最新のエクセルでは『RANK.AVG()関数：同じ値に対して、平均ランクを返す関数』のように関連した関数がアップデートされています。
一般的なビンゴカード（１～７５）の作成手順
方眼セルは同じシート内で部分的に作成してもいいですし、別シートに印刷専用として用意してもOKです。
方眼セルの作り方は以下の記事を参考にしてください。
これでビンゴカードは完成です。印刷は一枚ずつ行い、一回印刷するごとに、『F9』を押して、RAND()関数の数値の再生成を行いましょう。
あとはお好みで塗りつぶしを使ったり、宴会名のタイトルを入れたり、効果を追加します。
幹事さんの企画次第でそれぞれのビンゴカードの真ん中を、そのまま数字にしておいてもいいですし、『FREE』など書き換えてもOKです。
どどん一度に3枚分のカードを用意するのは、生成した１～75の数字がまんべんなく、どこかのカードには入るようにするためです。
生成した75個の数字から２５個だけ抜き出して1枚ずつカードを作った場合、
例えばビンゴで”７４”というボールが出たときに、「誰も開けられる人がいない！」という偏りが出る可能性があります。
数字に偏りがあると、一度に大量のビンゴが発生してしまう可能性もあります。不公平さをなくすための、ちょっとした配慮です。
１～75以外を使ったオリジナルカードの作成手順
エクセルでビンゴカードを作る一番のメリットは、手作り感を演出できるという点です。
また、その他のメリットとして、市販にはない１～75以外の数字や文字列を織り交ぜたビンゴカードが用意できるという点があります。
ここでは最新のエクセルで利用可能なXLOOKUP()関数と旧バージョンで利用可能なVLOOKUP()関数を使った２つの方法で説明します。
XLOOKUP()関数とVLOOKUP()関数の違いは以下の記事を参考にしてみてください。
XLOOKUP()関数を使う方法
Office 365やExcel 2021以降を使っているなら、XLOOKUP()関数を使う方法が簡単です。
作り方は、一般的な1～75のカードと途中まで同じです。
オリジナルの答えなので75個用意できないかも知れません。
その場合は、
- 25個～50個：１枚のカード
- 50個～75個：２枚のカード
- 75個以上 ：３枚のカード
と、一度に作成するカードの枚数を調整しましょう。
RANK()関数で順位付けした数字の横の列にオリジナルの答えを用意します。
各マスに
=XLOOKUP(1～75の数字,RANK()関数の列,オリジナルの答えの列)
をそれぞれ参照します。
例えば1マス目には
=XLOOKUP(1,$B$14:$B$88,$C$14:$C$88)２マス目には
=XLOOKUP(2,$B$14:$B$88,$C$14:$C$88)と全てのマスに関数を入力します。
全てのマスに関数が入れ終われば、完成です。
あとは以下の例のように好みのデザインになるようにフォントやセルの枠線を整えます。
VLOOKUP()関数を使う方法
Excel 2019以前のバージョンを使用している方は、XLOOKUP()関数が利用できない可能性があるのでVLOOKUP関数で作成します。
作り方は、一般的な1～75のカードと途中まで同じです。
RANK()関数で順位付けした数字の横の列に1～75までの数字を連番で用意します。
RANK()関数で順位付けした数字の横の列にオリジナルの答えを用意します。
図のようなイメージの表になります。
各マスに
=VLOOKUP(RANK()関数の列,『通し番号』と『答え』の２列の範囲,『2』（答えの列を参照）,FALSE)
をそれぞれ参照します。
例えば1マス目には
=VLOOKUP($B14,$C$14:$D$88,2,FALSE)２マス目には
=VLOOKUP($B15,$C$14:$D$88,2,FALSE)と全てのマスに関数を入力します。
全てのマスに関数が入れ終われば、完成です。
あとは好みのデザインになるようにフォントやセルの枠線を整えます。
VLOOKUP()関数を使い方を簡単に説明します。
・今回の例の『$B14』はRANK()関数で順位付けした数字になります。
・『$C$14:$D$88』はVLOOKUP()関数の参照の範囲です。ここでは『通し番号』と『オリジナルの答え』が記載されている範囲全体を指定します。
・『2』はVLOOKUP()関数で参照範囲から何列目の値を返すかを指定する数字です。ここでは１列目が『通し番号』で、2列目の『オリジナルの答え』を返したいので『２』を入力します。
・『FALSE』はVLOOKUP()関数の検索方法です。
TRUE(または指定なし）で近似一致を探します。逆にFALSEは完全一致を意味します。
今回はどちらでもいいですが、万が一、近似値を探しに行った結果、カード内に重複するデータが存在するとビンゴとして成り立たないのでFALSEを入力しています。
無料ダウンロード
今回説明に使用したエクセルファイルを無料でダウンロードできるようにしました。
タブは３つ用意しています。
- 通常のビンゴカード（１～75のランダムな数字）
- オリジナルのビンゴカード（VLOOKUP関数で作成したバージョン）
- オリジナルのビンゴカード（XLOOKUP関数で作成したバージョン）
皆様の好みに応じて、オリジナルの答えを編集したり、カードの装飾を変えたりしてみてください。
おまけ：QuizBingoの概要
ここからは実際に私がQuizBingo（クイズビンゴ）の概要を紹介します。
基本ルールはビンゴと同じです。
- ビンゴカードに書いてある数字は用意されたクイズの答えになっています。
- 席順に指名していき，指名された人は渡されたクイズメニュー（合計75問）の中からクイズを選択します。
- クイズを選ぶ際は自身が開けたい数字が答えになりそうな問題を選びます。
- 例えば『Ｑ１：富士山の標高は？』というクイズを選べば3776 (m)が開きます。
- 全く答えが分からなくても、適当に問題番号を宣言してもらうことでゲームは進行していきます。
その他のルールとして
- スマホ検索は禁止
- 真ん中はFREE
- リーチになったら宣言して席を立つ
- １ラインビンゴでクリア
- 問題を選ぶ際は、予想する答えを言っても言わなくてもいい（言わない方が戦略的にはいい）
- 出た数字は前のスクリーンに映していく（ホワイトボードとか大きめの紙に書いていってもいいですね）
- ビンゴになった順に賞品をもらう（同時にビンゴが出た場合はじゃんけんで順番を決める）
みたいな感じで進めていきました。
どどん参加者が75人以下なら、一人１回は順番が回ってくる計算ですが、
実際やってみた感じでは20問目くらいからリーチが出始めて、40問目くらいからビンゴが連発します。
40人以下くらいが楽しめる規模感かなと思います。
実際のクイズの作成に当たっては、必ず数字が答えになるように問題文を考えました。
その答えもなるべく似たり寄ったりの数字になるように、
・１～100までの数字が答えになるもの
・100～3000までの数字が答えになるもの
で半々くらいの配分で用意しました。
そうすることで、簡単には答えが分からないようになります。
答えが分からなくても、適当に問題を選ぶだけで進行していくので、運要素強めの難度設定にしました。
どどん以下は実際に使用した『食育ジャンル』の17問です。
雰囲気を感じてもらえればと思います。
答えが被らないように75問用意するというのは結構難しいです
自己責任で使ってもらってもいいですよ（笑）
- Ｑ１：レモン１個(約100g)の可食部に含まれるビタミンＣはレモン５個分
- Ｑ２：「いいにほんしょく」の語呂合わせから、11月24日は和食の日である
- Ｑ３：豆腐業界では豆腐に使われる大豆の数は、「一丁380粒」と言われている
- Ｑ４：お米の計量単位で「1俵」というと、60kgのことである
- Ｑ５：野菜類で一番水分量が多いのはアロエの99%(文科省食品成分ＤＢより)
- Ｑ６：緑黄色野菜とは可食部100g当たりカロテン含量が600μg以上の野菜をいう
- Ｑ７：マイナス 18 ℃以下で保存されたアイスは賞味期限の記載を省略できる
- Ｑ８：ごはん１杯分(150g)に入っているお米の量は約3000粒である
- Ｑ９：10歳以上の野菜摂取量は1日350gが推奨されている
- Ｑ10：直立した生卵30個が耐えられる重量は約90kgである
- Ｑ11：消費者庁による令和３年度の食品ロス推計量は523万トン である
- Ｑ12：令和４年度の全国の食料自給率はカロリーベースで38％であった
- Ｑ13：カップヌードル(醤油)を2個食べると１日の塩分摂取上限約７gを超える
- Ｑ14：10月13日はさつまいもの日(「九里四里(栗)よりうまい○○里」が由来）
- Ｑ15：「食肉小売品質基準」によって定められる牛肉の部位の数は11種類
- Ｑ16：2cm以上のミディアム ステーキは片面を強火で30秒後、弱火で120秒焼く
- Ｑ17：『茶寿』といえば、108歳を祝う賀寿である
さいごに
ビンゴカード作る場面は限られていると思いますが（笑）、今回紹介したRAND()関数とRANK()関数の使い方は、他にも応用がききます。
例えば、チーム戦の組み合わせを決めたり、会食の席順を決めたり、自分の恣意的要素を排除できるので気持ち的にも楽かと思います。
全員ランダムではなく、各テーブルに管理職1名ずつ固定して、その他のメンバーはランダム。。。なんてことももちろんできます。
状況に応じていろいろ活用してみてください。
皆さんのお仕事に少しでも役立てれば幸いです。ではまたﾉｼ
コメント
コメント一覧 （2件）
はじめましてm(_ _)m
イベントで数字ではない文字列のビンゴカードを作るため、苦心していましたがこちら「どどん」さんのExcelでの作成方法が大変分かりやすく（理解するのに少し時間がかかりましたが、それは私の理解力の問題です）無事に魚のビンゴカードを作成できました！！
ありがとうございました！！
コメントありがとうございます！
少し煩雑な内容になってしまいましたが、うまく読み解いていただいてうれしいです。
イベント大変だと思いますが頑張ってください。一生懸命取り組んだ時間はきっと周りに伝わると思います^^