![見出し画像](https://assets.st-note.com/production/uploads/images/102471097/rectangle_large_type_2_e0dc56e2c636253483bc73c7bc6b93e2.png?width=1200)
エクセルごはん第6回(ランダムな数字とアルファベットの文字列を作ろう)
こんにちは。
エクセルごはんです。
第6回目になります。
今回もExcel問題を通して、楽しく脳トレしていきましょう。
このnoteは、もしも会社に入社するのにExcelの試験が必要だったら、もしも学校に入学するのにExcelの試験科目があったら、そんな状況を想定して、Excel問題を出題し、読者の皆様のExcel力をゴリゴリ鍛えていこうと思っています。
問題
今回の問題は、前回の問題、ランダムな文字列を作ろうというものの類似問題です。セルB3に、アルファベット文字の他に数字も扱って、12文字の文字列を作ってください。26文字のランダムなアルファベット文字と0から9までの数字を、12文字分組み合わせることになります。
![](https://assets.st-note.com/img/1680939131689-TxOpop0T4j.png?width=1200)
完成形のイメージは例えば次になります。
![](https://assets.st-note.com/img/1680939139406-BPbWRJXWnG.png?width=1200)
ランダムなということは乱数を取得する関数の出番になりそうですね。
またアルファベットのAからZまでの文字を表示するには、専用の関数が必要そうです。
そして1文字ではなく、複数文字をランダムに作るには、少し仕掛けが必要そうです。
関数と配列数式を使って解いてみてください。
どうですか?出来ましたか?
問題を今一度おさらいしておきましょう。
![](https://assets.st-note.com/img/1680939180919-D3szw6JhAx.png?width=1200)
12文字のランダムなアルファベットと数字の文字列を作ります。
ランダムなので同じアルファベットが登場するかもしれないし、しないかもしれません。同じ数字が登場するかもしれないし、しないかもしれません。また、同じアルファベットが続くこともあるかもしれませんし、同じ数字が続くこともあるかもしれません。
どうやって複数文字を作るのでしょうか。しまも数字も入れて。
解説
ここからは解説に入ります。
セルB3に、
=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キーを押し、配列数式にします。
これで、12文字のランダムなアルファベットと数字の文字列が出来上がりました。
どういう仕組みになっているのか詳しく見ていきましょう。
まず、1文字のランダムなアルファベットを表示することを考えてみましょう。
アルファベットのAは数値コードで65です。アルファベットのZは数値コードで90です。文字の数値コードはCODE関数を使って調べることができます。
適当なセルに、
=CODE("A")
と入力すると65を返します。
適当なセルに、
=CODE("Z")
と入力すると90を返します。
他のアルファベットはどうなっているかですが、この65から90の間に収まっています。
試しに、適当なセルに、
=CONCAT(CHAR(ROW(A65:A90)))
と入力し、CtrlとShiftとEnterキーを押して配列数式にすると、全てのアルファベットが表示されます。
このことからCHAR関数に数値コード65から90を入れてあげれば良いことが分かったので、1文字のランダムなアルファベットを得るためには、
=CHAR(RANDBETWEEN(65,90))
とすれば良いわけです。
そして、数字ですが、CHAR関数で取得しなくても、数字なので、ROW関数で取得できますね。
ROW($A$1:$A$10)-1)
とすることで、0から9までの数字を得ます。
ROW($A$1:$A$10)
は1から10までの数字を得るので、これに-1して0から9までの数字にしています。
そして、
CONCAT(CHAR(ROW($A$65:$A$90)),ROW($A$1:$A$10)-1)
とすることで、アルファベットAからZまでの文字と0から9までの数字を一連の文字列として連結します。
あとはこの連結した文字列をMID関数で1文字切り取って取得すれば良いわけです。ランダムに取り出すためには、RANDBETWEEN関数を使って、最小値に1を、最大値にアルファベット26文字+0から9までの数字10文字分を合わせて36を設定します。
以上を踏まえて、ランダムなアルファベットの文字と数字を1文字取得するには、
=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の値になるなら1を返すという式ですが、絶対なるに決まっていますよね。ここは12回分RANDBETWEEN関数を処理したいので入れている、意味はあるけど意味のない式です。
要は12回分処理できれば良いので、他の、絶対、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))
これだとランダムな同じアルファベットか数字が12文字分表示されてしまいます。
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関数で12回分、1から36までのランダムな値を取得し、それを内側のCONCAT関数で連結したアルファベットと数字の文字列の開始位置として指定し、1文字取り出し、最後にまたCONCAT関数で連結して文字列にしています。
おつかれさまでした。
今回の問題は前回の問題の類似問題でしたが、どうだったでしょうか?
数字とアルファベットの文字が出る確率も均等になりましたでしょうか?
数字が2分の1出てしまうという事態になりませんでしたか?
これでいけると思っても意外なバグが見つかったりして、なかなか思うような答えにならず大変ですよね。でも、その試行錯誤には必ず意味があります。
次回も配列数式を扱った問題になります。
配列数式を使ったさまざまな問題に触れて、配列数式を学びつくしましょう。
それでは、また次回をお楽しみに。
この記事が気に入ったらサポートをしてみませんか?