エクセルごはん第９回（ランダムなアルファベット表を作ろう）

こんにちは。
エクセルごはんです。

第９回目になります。
今回もExcelの問題を紹介します。

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

問題

エクセル君「今日はどんな問題かな？」
ごはん君「今日の問題は、ランダムなアルファベット表を作ろうという問題だよ。前回のランダムな九九表を作ろうという問題の類似問題になるよ」
エクセル君「じゃあ、前回の記事も参考になるのかな」
ごはん君「そうだね。前回の１から９までの数字がAからZまでのアルファベットに置き換わったと思ってもらえれば良いよ」
エクセル君「更新ボタンであるF９のボタンを押す度に、アルファベットが変わるんだね」
ごはん君「うん。完成形のイメージは例えば次のようになるね」

エクセル君「前回は重複しない１から９までの数字だったけど、今回は重複しないAからZまでのアルファベットだから、専用の関数が必要になるのかな」
ごはん君「そうだね。前回使わなかった専用の関数が必要になるね。でも、やることは似ているから、考え方は同じだよ。あと、やっぱり、重複しないが重要なテーマになるね」
エクセル君「どうやって、重複しないランダムなアルファベット表を作るんだろう？」
ごはん君「考えてみよう」

解説

ごはん君「前回の記事の、行に、重複しない１から９までの数字を入れる問題の解答を少し手直しするだけで、今回の問題の解答が作れるんだ」
エクセル君「前回の問題の解答を見てみるね…」

=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３に、
=CHAR(SMALL(IF(COUNTIF($B$2:B2,CHAR(ROW($A$65:$A$90)))=0,ROW($A$65:$A$90)),RANDBETWEEN(1,27-ROW(A1))))
と入力し、CtrlとShiftとEnterキーを押した。
セルB４からセルB２８までは、セルB３に入れた数式をオートフィル機能でコピーした。

エクセル君「CHAR関数を使っているんだね」
ごはん君「うん。CHAR関数がアルファベット文字を表示する専用の関数だね」

エクセル君「CHAR関数はどんな関数になるの？」
ごはん君「CHAR関数は、コード番号に対応する文字を返す関数なんだ。アルファベットのAはコード番号６５で、Bは６６で、Cは６７で、と続いていって、Zは９０になるんだ。これはCODE関数を使って調べることができるよ」

適当なセルに
=CODE("A")
と入力すると６５を返します。

エクセル君「それで、CHAR(ROW($A$65:$A$90))と６５から９０までを指定しているんだね」
ごはん君「うん。６５から９０までがアルファベットのAからZまでのコード番号になるからね」

エクセル君「前回の記事を読んでいるから、今回の解答が理解できるよ。
まず、COUNTIF($B$2:B2,CHAR(ROW($A$65:$A$90))の箇所だけど、ここはセルB２からセルB３、セルB４と１セルずつ範囲拡大していくセルに、アルファベットのAからZまでが含まれていないか調べているんだね)
ごはん君「うん。含まれていない場合は０を返すね」
エクセル君「そして、IF関数でCOUNTIF関数から返ってくる値が０だった場合に、６５から９０を取得して、それをSMALL関数の中に入れて、RANDBETWEEN関数でランダムに取り出して、最後にCHAR関数でアルファベット文字に変換しているんだね」
ごはん君「すごいね。よく理解できているね」
エクセル君「うん。RANDBETWEEN関数が、RANDBETWEEN(1,10-ROW(A1)からRANDBETWEEN(1,27-ROW(A1)に変わっているのは、１から９までの９つの数字に対して、アルファベット文字がAからZまでの２６文字だからだね」
ごはん君「そうだね。重複しないランダムな数字の取得の仕方は前回の記事を参考にしてほしい。今回は変更点にだけ注目してみたけど、大丈夫かな？」
エクセル君「変更された箇所をみてみよう」

行に、重複しない１から９までの数字を取得する数式
=SMALL(IF(COUNTIF($B$2:B2,ROW($A$1:$A$9))=0,ROW($A$1:$A$9)),RANDBETWEEN(1,10-ROW(A1)))

行に、重複しないAからZまでのアルファベットを取得する数式
=CHAR(SMALL(IF(COUNTIF($B$2:B2,CHAR(ROW($A$65:$A$90)))=0,ROW($A$65:$A$90)),RANDBETWEEN(1,27-ROW(A1))))

ごはん君「太字になっているのが変更された箇所だよ。よく見比べて、違いを把握してみよう。重複しないランダムな値を取得する仕組みは前回の問題と同じだから、分からなかったら、前回の記事を参考にしてね」

解答

セルB３に、
=CHAR(SMALL(IF(COUNTIF($B$2:B2,CHAR(ROW($A$65:$A$90)))=0,ROW($A$65:$A$90)),RANDBETWEEN(1,27-ROW(A1))))
と入力し、CtrlとShiftとEnterキーを押した。
セルB４からセルB２８までオートフィル機能で、セルB３の数式コピーした。

おつかれさまでした。
今回は、ランダムなアルファベット表を作ろうという問題でした。
前回の類似問題のため、解説を省いていますが、大丈夫だったでしょうか？
考え方の仕組みは同じなので、前回の記事が理解できていれば、今回の問題も対応できると思いますが、少し頭を悩ませたかもしれません。
それでは、また次回のエクセルごはんをお楽しみに。