エクセルごはん第６回（ランダムな数字とアルファベットの文字列を作ろう）

こんにちは。
エクセルごはんです。
 
第６回目になります。
今回もExcel問題を通して、楽しく脳トレしていきましょう。
 
このnoteは、もしも会社に入社するのにExcelの試験が必要だったら、もしも学校に入学するのにExcelの試験科目があったら、そんな状況を想定して、Excel問題を出題し、読者の皆様のExcel力をゴリゴリ鍛えていこうと思っています。 

問題

今回の問題は、前回の問題、ランダムな文字列を作ろうというものの類似問題です。セルB３に、アルファベット文字の他に数字も扱って、１２文字の文字列を作ってください。２６文字のランダムなアルファベット文字と０から９までの数字を、１２文字分組み合わせることになります。 

完成形のイメージは例えば次になります。 

ランダムなということは乱数を取得する関数の出番になりそうですね。
またアルファベットのAからZまでの文字を表示するには、専用の関数が必要そうです。
そして１文字ではなく、複数文字をランダムに作るには、少し仕掛けが必要そうです。
 
関数と配列数式を使って解いてみてください。
どうですか？出来ましたか？
 
問題を今一度おさらいしておきましょう。

１２文字のランダムなアルファベットと数字の文字列を作ります。
ランダムなので同じアルファベットが登場するかもしれないし、しないかもしれません。同じ数字が登場するかもしれないし、しないかもしれません。また、同じアルファベットが続くこともあるかもしれませんし、同じ数字が続くこともあるかもしれません。
どうやって複数文字を作るのでしょうか。しまも数字も入れて。

解説

ここからは解説に入ります。
セルB３に、
=CONCAT(MID(CONCAT(CHAR(ROW($A$65:$A$90)),ROW($A$1:$A$10)-1),RANDBETWEEN(IF($A$1:$A$12=$A$1:$A$12,1),36),1))
と入力して、CtrlとShiftとEnterキーを押し、配列数式にします。
これで、１２文字のランダムなアルファベットと数字の文字列が出来上がりました。
 
どういう仕組みになっているのか詳しく見ていきましょう。
まず、１文字のランダムなアルファベットを表示することを考えてみましょう。
アルファベットのAは数値コードで６５です。アルファベットのZは数値コードで９０です。文字の数値コードはCODE関数を使って調べることができます。
適当なセルに、
=CODE("A")
と入力すると６５を返します。
適当なセルに、
=CODE("Z")
と入力すると９０を返します。
 
他のアルファベットはどうなっているかですが、この６５から９０の間に収まっています。
試しに、適当なセルに、
=CONCAT(CHAR(ROW(A65:A90)))
と入力し、CtrlとShiftとEnterキーを押して配列数式にすると、全てのアルファベットが表示されます。
このことからCHAR関数に数値コード６５から９０を入れてあげれば良いことが分かったので、１文字のランダムなアルファベットを得るためには、
=CHAR(RANDBETWEEN(65,90))
とすれば良いわけです。
 
そして、数字ですが、CHAR関数で取得しなくても、数字なので、ROW関数で取得できますね。
ROW($A$1:$A$10)-1)
とすることで、０から９までの数字を得ます。
ROW($A$1:$A$10)
は１から１０までの数字を得るので、これに-1して０から９までの数字にしています。
 
そして、
CONCAT(CHAR(ROW($A$65:$A$90)),ROW($A$1:$A$10)-1)
とすることで、アルファベットAからZまでの文字と０から９までの数字を一連の文字列として連結します。
 
あとはこの連結した文字列をMID関数で１文字切り取って取得すれば良いわけです。ランダムに取り出すためには、RANDBETWEEN関数を使って、最小値に１を、最大値にアルファベット２６文字＋０から９までの数字１０文字分を合わせて３６を設定します。
 
以上を踏まえて、ランダムなアルファベットの文字と数字を１文字取得するには、
=MID(CONCAT(CHAR(ROW($A$65:$A$90)),ROW($A$1:$A$10)-1),RANDBETWEEN(1,36),1)
と入力し、CtrlとShiftとEnterキーを押して配列数式にします。
 
さて、では複数のランダムなアルファベット文字を表示するにはどうすれば良いのか。
少し仕掛けが必要になります。
=CONCAT(MID(CONCAT(CHAR(ROW($A$65:$A$90)),ROW($A$1:$A$10)-1),RANDBETWEEN(IF($A$1:$A$12=$A$1:$A$12,1),36),1))
とRANDBETWEEN関数の内にIF関数があり、その式に意味はあるのですが、式の中身に意味はありません。
IF($A$1:$A$12=$A$1:$A$12,65)
として、A1からA12の値がA1からA12の値になるなら１を返すという式ですが、絶対なるに決まっていますよね。ここは１２回分RANDBETWEEN関数を処理したいので入れている、意味はあるけど意味のない式です。
要は１２回分処理できれば良いので、他の、絶対、IF関数の条件が真になる式を入れても大丈夫です。
 
さて、RANDBETWEEN関数を処理するのに、次の式ではダメなのですが、解答とどう違うか分かりますか？
=CONCAT(MID(CONCAT(CHAR(ROW($A$65:$A$90)),ROW($A$1:$A$10)-1),IF($A$1:$A$12=$A$1:$A$12,RANDBETWEEN(1,36)),1))
 
これだとランダムな同じアルファベットか数字が１２文字分表示されてしまいます。
RANDBETWEEN関数の外でIF関数を使っても再計算されないわけです。
なので、RANDBETWEEN関数の内でIF関数を使う必要があったのです。これが必要な仕掛けというわけです。
 
まとめると、
=CONCAT(MID(CONCAT(CHAR(ROW($A$65:$A$90)),ROW($A$1:$A$10)-1),RANDBETWEEN(IF($A$1:$A$12=$A$1:$A$12,1),36),1))
として、RANDBETWEEN関数で１２回分、１から３６までのランダムな値を取得し、それを内側のCONCAT関数で連結したアルファベットと数字の文字列の開始位置として指定し、１文字取り出し、最後にまたCONCAT関数で連結して文字列にしています。
 
おつかれさまでした。
今回の問題は前回の問題の類似問題でしたが、どうだったでしょうか？
数字とアルファベットの文字が出る確率も均等になりましたでしょうか？
数字が２分の１出てしまうという事態になりませんでしたか？
これでいけると思っても意外なバグが見つかったりして、なかなか思うような答えにならず大変ですよね。でも、その試行錯誤には必ず意味があります。
 
次回も配列数式を扱った問題になります。
配列数式を使ったさまざまな問題に触れて、配列数式を学びつくしましょう。
それでは、また次回をお楽しみに。