エクセルごはん第８回（ランダムな九九表を作ろう）

こんにちは。
エクセルごはんです。
 
第８回目になります。
今回もExcelの問題を紹介します。
 
前回、エクセル君とごはん君という二人の登場人物に登場してもらい、二人に問題の解説をしてもらいました。
今回も、エクセル君とごはん君の二人にExcelの問題を解説してもらいます。
エクセル君はエクセルが苦手です。
ごはん君はエクセルが得意です。
それでは、問題をどうぞ。

問題

 
エクセル君「今日はどんな問題かな？」
ごはん君「今日の問題は、ランダムな九九表を作ろうという問題だよ」
 
エクセル君「ランダムな九九表ってことは、１から９までの数字が不規則に並んでいるんだね」
ごはん君「うん。完成例は例えば次のようになるよ」
 

エクセル君「更新ボタンであるF９キーを押す度に、列と行の１から９までの数字がランダムに変われば良いんだね」
ごはん君「そうだね」
 
エクセル君「ランダムな九九表を作るにはどうすれば良いのかな？」
ごはん君「考えてみよう」
 

解説

 
ごはん君「ランダムな九九表を作るには、ランダムな重複しない１から９までの数字を作ることができれば良いんだ」
エクセル君「重複しない１から９までの数字か。普通に、ランダムな１から９までの数字を１つ作るには次のようになるよね」
=RANDBETWEEN(1,9)
ごはん君「そうだね。RANDBETWEEN関数は最小値に設定した値から、最大値に設定した値までのランダムな整数を返す関数になるから、これで、１から９までの数字からランダムに一つ数字を持ってくるね」
エクセル君「これを重複しないように対応させるのか。どうすれば良いんだろう？」
 
ごはん君「まず、セルC２に４、セルD２に２、セルE２に７が入っている場合を仮定して、セルＦ２に入る次の数字をどうするか考えてみよう」
エクセル君「４と２と７が既に使われているなら、残りは、１，３，５，６，８，９の中からランダムで値を取ってきたいな」
ごはん君「そうだね。そうするには次の数式になるんだけど、仕組みは分かるかな？」
=SMALL({1,3,5,6,8,9},RANDBETWEEN(1,6))
エクセル君「ＳＭＡＬＬ関数の配列に１，３，５，６，８，９を指定して、順位をランダムにしているんだね」
ごはん君「うん。もう少し丁寧に見ると、=SMALL({1,3,5,6,8,9},1)と順位を１にすれば、SMALL関数に入れた配列の１番目に小さい値である１を返すよね。順位を２にすれば、SMALL関数に入れた配列の２番目に小さい値である３を返すよね。こんな風にして、ランダムに１，３，５，６，８，９の値を取り出すことができるんだ」
 
エクセル君「なるほど。じゃあ次は、どうやって、ＳＭＡＬＬ関数の配列に２，４，７の数字以外を取ってくるかだね」
ごはん君「セルC２に４、セルD２に２、セルE２に７が入っている状態で、セルＦ２に次の数式を入力してみよう」
セルＦ２に、
=CONCAT(COUNTIF($C$2:$E$2,ROW($A$1:$A$9)))
と入力し、CtrlとShiftとEnterキーを押した。
エクセル君「010100100と表示されたけど…」
ごはん君「１，２，３，４，５，６，７，８，９の中で、既に使われている数字、２，４，７にだけ、数字があるから、１と表示されるんだ」
エクセル君「そうか。じゃあ、これとＩＦ関数を組み合わせて、１と表示されていないとき、０と表示されているときだけ、数字を取ってこれば良いんだね」
セルＦ２に、
=CONCAT(IF(COUNTIF($C$2:$E$2,ROW($A$1:$A$9))=0,ROW(A1:$A$9)))
と入力し、CtrlとShiftとEnterキーを押した。
エクセル君「『1FALSE3FALSE56FALSE89」と表示されたよ』
ごはん君「うん。これと先程のSMALL関数を組み合わせれば、１，３，５，６，８，９の中からランダムに一つ値を取ってこれるね」
セルF２に、
=SMALL(IF(COUNTIF($C$2:$E$2,ROW($A$1:$A$9))=0,ROW($A$1:$A$9)),RANDBETWEEN(1,6))
と入力し、CtrlとShiftとEnterキーを押した。
エクセル君「１，３，５，６，８，９の中からランダムに一つ値を取ってきてるよ。これでセルF２に入れる値はできたけど、次のセルG２はどうするの？」
 
ごはん君「セルG２は、今度はセルC２からセルF２までに使った値以外だから、COUNTIF関数の範囲を一つ広げて、RANDBETWEEN関数の最大値を一つ狭めるんだ」
セルG２に、
=SMALL(IF(COUNTIF($C$2:$F$2,ROW($A$1:$A$9))=0,ROW($A$1:$A$9)),RANDBETWEEN(1,5))
と入力し、CtrlとShiftとEnterキーを押した。
エクセル君「こうやって、COUNTIF関数の範囲を広げていって、RANDBETWEEN関数の最大値を狭めていけば良いのか」
 
ごはん君「うん。じゃあ、解答を紹介するよ。今まで解説した仕組みが理解できていれば、きっと分かるよ。まずは列から」
セルC２に、
=SMALL(IF(COUNTIF($B$2:B2,ROW($A$1:$A$9))=0,ROW($A$1:$A$9)),RANDBETWEEN(1,10-COLUMN(A1)))
と入力し、CtrlとShiftとEnterキーを押した。
残りのセルD２からセルK２はオートフィル機能を使ってコピーした。
エクセル君「COUNTIF関数の範囲を、$B$2:B2とすることで、セルB２からセルB２までから、セルB２からセルC２まで、セルB２からセルD２までと、一つずつ範囲が拡大していくんだね」
ごはん君「うん。そして、RANDBETWEEN(1,10-COLUMN(A1))とすることで、RANDBETWEEN関数の最大値が９から８，７，６…と一つずつ減っていき、狭まっていくんだ」
 
エクセル君「行の方も同じように作れば良いんだよね」
ごはん君「うん。行も考え方は同じだよ。RANDBETWEEN関数の最大値に指定する値がCOLUMN関数を利用していたのから、ROW関数を利用したものに変わるだけだね」
セルB３に、
=SMALL(IF(COUNTIF($B$2:B2,ROW($A$1:$A$9))=0,ROW($A$1:$A$9)),RANDBETWEEN(1,10-ROW(A1)))
と入力し、CtrlとShiftとEnterキーを押した。
残りのセルB４からセルB１１はオートフィル機能を使ってコピーした。
エクセル君「列にも行にも重複しないランダムな１から９までの数字が入ったね。これでランダムな九九表の完成だね」
 

解答

 列
セルC２に、
=SMALL(IF(COUNTIF($B$2:B2,ROW($A$1:$A$9))=0,ROW($A$1:$A$9)),RANDBETWEEN(1,10-COLUMN(A1)))
と入力し、CtrlとShiftとEnterキーを押した。
残りのセルD２からセルK２はオートフィル機能を使ってコピーした。
 
行
セルB３に、
=SMALL(IF(COUNTIF($B$2:B2,ROW($A$1:$A$9))=0,ROW($A$1:$A$9)),RANDBETWEEN(1,10-ROW(A1)))
と入力し、CtrlとShiftとEnterキーを押した。
残りのセルB４からセルB１１はオートフィル機能を使ってコピーした。
 
おつかれさまでした。
今回は、ランダムな九九表を作ろうという問題でした。
重複しないランダムな数字を作ることがカギでしたね。
これで、もう九九の問題作りに苦労しませんね。
それでは、また次回のエクセルごはんをお楽しみに。