![見出し画像](https://assets.st-note.com/production/uploads/images/103629139/rectangle_large_type_2_de70b03bb01810410df77c2e78f0fe15.png?width=1200)
エクセルごはん第8回(ランダムな九九表を作ろう)
こんにちは。
エクセルごはんです。
第8回目になります。
今回もExcelの問題を紹介します。
前回、エクセル君とごはん君という二人の登場人物に登場してもらい、二人に問題の解説をしてもらいました。
今回も、エクセル君とごはん君の二人にExcelの問題を解説してもらいます。
エクセル君はエクセルが苦手です。
ごはん君はエクセルが得意です。
それでは、問題をどうぞ。
問題
エクセル君「今日はどんな問題かな?」
ごはん君「今日の問題は、ランダムな九九表を作ろうという問題だよ」
エクセル君「ランダムな九九表ってことは、1から9までの数字が不規則に並んでいるんだね」
ごはん君「うん。完成例は例えば次のようになるよ」
![](https://assets.st-note.com/img/1682116203717-xnhiVzckgH.png?width=1200)
エクセル君「更新ボタンであるF9キーを押す度に、列と行の1から9までの数字がランダムに変われば良いんだね」
ごはん君「そうだね」
エクセル君「ランダムな九九表を作るにはどうすれば良いのかな?」
ごはん君「考えてみよう」
解説
ごはん君「ランダムな九九表を作るには、ランダムな重複しない1から9までの数字を作ることができれば良いんだ」
エクセル君「重複しない1から9までの数字か。普通に、ランダムな1から9までの数字を1つ作るには次のようになるよね」
=RANDBETWEEN(1,9)
ごはん君「そうだね。RANDBETWEEN関数は最小値に設定した値から、最大値に設定した値までのランダムな整数を返す関数になるから、これで、1から9までの数字からランダムに一つ数字を持ってくるね」
エクセル君「これを重複しないように対応させるのか。どうすれば良いんだろう?」
ごはん君「まず、セルC2に4、セルD2に2、セルE2に7が入っている場合を仮定して、セルF2に入る次の数字をどうするか考えてみよう」
エクセル君「4と2と7が既に使われているなら、残りは、1,3,5,6,8,9の中からランダムで値を取ってきたいな」
ごはん君「そうだね。そうするには次の数式になるんだけど、仕組みは分かるかな?」
=SMALL({1,3,5,6,8,9},RANDBETWEEN(1,6))
エクセル君「SMALL関数の配列に1,3,5,6,8,9を指定して、順位をランダムにしているんだね」
ごはん君「うん。もう少し丁寧に見ると、=SMALL({1,3,5,6,8,9},1)と順位を1にすれば、SMALL関数に入れた配列の1番目に小さい値である1を返すよね。順位を2にすれば、SMALL関数に入れた配列の2番目に小さい値である3を返すよね。こんな風にして、ランダムに1,3,5,6,8,9の値を取り出すことができるんだ」
エクセル君「なるほど。じゃあ次は、どうやって、SMALL関数の配列に2,4,7の数字以外を取ってくるかだね」
ごはん君「セルC2に4、セルD2に2、セルE2に7が入っている状態で、セルF2に次の数式を入力してみよう」
セルF2に、
=CONCAT(COUNTIF($C$2:$E$2,ROW($A$1:$A$9)))
と入力し、CtrlとShiftとEnterキーを押した。
エクセル君「010100100と表示されたけど…」
ごはん君「1,2,3,4,5,6,7,8,9の中で、既に使われている数字、2,4,7にだけ、数字があるから、1と表示されるんだ」
エクセル君「そうか。じゃあ、これとIF関数を組み合わせて、1と表示されていないとき、0と表示されているときだけ、数字を取ってこれば良いんだね」
セルF2に、
=CONCAT(IF(COUNTIF($C$2:$E$2,ROW($A$1:$A$9))=0,ROW(A1:$A$9)))
と入力し、CtrlとShiftとEnterキーを押した。
エクセル君「『1FALSE3FALSE56FALSE89」と表示されたよ』
ごはん君「うん。これと先程のSMALL関数を組み合わせれば、1,3,5,6,8,9の中からランダムに一つ値を取ってこれるね」
セルF2に、
=SMALL(IF(COUNTIF($C$2:$E$2,ROW($A$1:$A$9))=0,ROW($A$1:$A$9)),RANDBETWEEN(1,6))
と入力し、CtrlとShiftとEnterキーを押した。
エクセル君「1,3,5,6,8,9の中からランダムに一つ値を取ってきてるよ。これでセルF2に入れる値はできたけど、次のセルG2はどうするの?」
ごはん君「セルG2は、今度はセルC2からセルF2までに使った値以外だから、COUNTIF関数の範囲を一つ広げて、RANDBETWEEN関数の最大値を一つ狭めるんだ」
セルG2に、
=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関数の最大値を狭めていけば良いのか」
ごはん君「うん。じゃあ、解答を紹介するよ。今まで解説した仕組みが理解できていれば、きっと分かるよ。まずは列から」
セルC2に、
=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キーを押した。
残りのセルD2からセルK2はオートフィル機能を使ってコピーした。
エクセル君「COUNTIF関数の範囲を、$B$2:B2とすることで、セルB2からセルB2までから、セルB2からセルC2まで、セルB2からセルD2までと、一つずつ範囲が拡大していくんだね」
ごはん君「うん。そして、RANDBETWEEN(1,10-COLUMN(A1))とすることで、RANDBETWEEN関数の最大値が9から8,7,6…と一つずつ減っていき、狭まっていくんだ」
エクセル君「行の方も同じように作れば良いんだよね」
ごはん君「うん。行も考え方は同じだよ。RANDBETWEEN関数の最大値に指定する値がCOLUMN関数を利用していたのから、ROW関数を利用したものに変わるだけだね」
セルB3に、
=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キーを押した。
残りのセルB4からセルB11はオートフィル機能を使ってコピーした。
エクセル君「列にも行にも重複しないランダムな1から9までの数字が入ったね。これでランダムな九九表の完成だね」
解答
列
セルC2に、
=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キーを押した。
残りのセルD2からセルK2はオートフィル機能を使ってコピーした。
行
セルB3に、
=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キーを押した。
残りのセルB4からセルB11はオートフィル機能を使ってコピーした。
おつかれさまでした。
今回は、ランダムな九九表を作ろうという問題でした。
重複しないランダムな数字を作ることがカギでしたね。
これで、もう九九の問題作りに苦労しませんね。
それでは、また次回のエクセルごはんをお楽しみに。
この記事が気に入ったらサポートをしてみませんか?