【Excel】RANDARRAY関数等を使ってランダムな配列を作成

RANDARRAY関数(Microsoft365で使用可)を使ってみました。実務で乱数を使うことはあまりないのですが、テスト用のデータを作成する時などに役立ちそうです。

support.microsoft.com

以下、それぞれ20行くらいで作成することとします。

（１） 1,000～10,000 間のランダムな数値

A1の数式

=RANDARRAY(20,1,1000,10000,1)

（２） 100～10,000 間のランダムな数値(100円単位)

A1の数式

=RANDARRAY(20,1,1,100,1)*100

 

（３）2021年4月1日～2021年9月30日 間のランダムな日付

A1の数式

=RANDARRAY(20,1,44287,44469,1)

※2021/4/1のシリアル値 ＝44287 　、2021/9/30のシリアル値 ＝44469

　A列の表示形式を日付にしています。

 

（４） A ～ Z 間のランダムな値

A1の数式

=CHAR(RANDARRAY(20,1,65,90))

CHAR関数で文字コードを文字にしています。65 ＝ A 　で 90＝Z です。

（５）A ～ Z をランダムに表示（すべての文字を使用）

A1の数式

=SORTBY(CHAR(SEQUENCE(26,1,65)),RANDARRAY(26))

連続した数値の配列を作るSEQUENCE関数を使って、65～90の連番を作り、それをCHAR関数でアルファベットに変換。更に配列を並び替えるSORTBY関数の引数をRANDARRAY関数を使ってランダムに並べ替えています。

 

（６） 文字列のリストをランダムに表示

まず、F列に対象のリストを文字列として入力します。E列にはF列に対応する連番を入れますが、E1セルに次の数式を入力することでF列の入力セルに対して可変で付番することができます。

E1の数式

=SEQUENCE(COUNTA(F:F),1,1,1)

A1の数式

=VLOOKUP(RANDARRAY(20,1,1,MAX(E1#),1),OFFSET(E1,0,0,COUNTA(E:E),2),2,0)

RANDARRAY関数でE列の数字のランダムなリストを作ってから、VLOOKUP関数でその数字に対応するF列の文字列を表示しました。VLOOKUP関数の引数の範囲はOFFSET関数で可変にしています。

 

（７） メンバーリストからランダムなトーナメント表を作る

AL列のメンバーリストは文字入力。AK列の連番は⑥と同じです。

AK2の数式

=SEQUENCE(COUNTA(AL:AL),1,1,1)

AN列にランダムに表示した結果を左トーナメント表の18行目の各セルで参照しているだけです。（トーナメント表の18行目の名前セルはそれぞれセル結合しており配列で直接表示できないため）

AN2の数式

=VLOOKUP(SORTBY(AK2#,RANDARRAY(MAX(AK2#))),OFFSET(AK2,0,0,COUNTA(AK:AK),2),2,0)

この数式は⑤⑥を合わせたものなので説明は省略します。

 

（８） メンバーリストから指定したチーム数にランダムに振り分ける

E列のメンバーリストは文字入力。D列の連番は⑥⑦と同じです。

B列の名前は⑦と同じ要領でE列のリストをランダム表示しています。

H2セルのチーム数は手入力します。ここで入力したチーム数によってA列のチームが自動で振られるようにA2セルに数式を入力します。チームはアルファベットとし、2チームであればA、B  。3チームであればA、B、C となるようにしています。

数式の説明の前に、チーム数やメンバーを変更した時の動きを確認します。

【チーム数を6に変更】

A～Fの6チーム。A、Bがそれぞれ4名、C～Fがそれぞれ3名ずつに分けられました。

【更に1名追加（21人目の比嘉さん)、チーム数は6のまま】

A列、B列にも自動で追加されました。Cの人数が3→4名に増えました。

D2の数式

=SEQUENCE(COUNTA(E:E),1,1,1)

B2の数式

=VLOOKUP(SORTBY(D2#,RANDARRAY(MAX(D2#))),OFFSET(D2,0,0,COUNTA(D:D),2),2,0)

ここまでは⑦までと同じです。

A2の数式

=SORT(MID(MID(REPT(TEXTJOIN("",1,CHAR(SEQUENCE(H2,1,65,1))),ROUNDUP(MAX(D2#)/H2,0)),1,MAX(D2#)),SEQUENCE(LEN(MID(REPT(TEXTJOIN("",1,CHAR(SEQUENCE(H2,1,65,1))),ROUNDUP(MAX(D2#)/H2,0)),1,MAX(D2#)))),1))

かなり長くなってしまったので、何回かに分けて式を足していく形で以下にて説明したいと思います。（前後の画像の数式の文字色を合わせています）

＜その１＞

※元の体裁は残しつつ、新たにI5セルに数式を入れています。

まず、SEQUENCE関数でH2に入力されたチーム数分の65から始まる連番を作り、これにCHAR関数をかけることでアルファベットにします。さらにその結果をTEXTJOIN関数でつなぎ合わせます。I5セルの結果の通り、AからFまでの6つのアルファベットが出ました。

＜その２＞

先ほどの数式にREPT関数を追加します。この関数は指定した回数のテキストを繰り返すものです。繰り返すテキストは＜その１＞の結果の文字列ABCDEFです。回数ですが、繰り返した結果の文字数とメンバー数を合わせることできないので、メンバー数を下回らない最低限の文字数となるように、メンバー数(MAX関数でD2の配列の最大値）をチーム数で除した結果をROUNDUPした数を繰り返すことにします。今回は21÷6=3.5 ・・4回 となり、I5セルはABCDEF×4回の文字列(24文字)が表示された状態となります。

＜その３＞

次は、＜その２＞で出した文字列(24文字）から必要なメンバー数分の文字列(21文字）を抜き出します。MID関数を使って1文字目からメンバー数(MAX関数でD2の配列の最大値）までを抜き出します。I5セルの結果は21文字となりました。

＜その４＞

＜その３＞の結果はあくまで1セル内の21文字なのでこれを各セルに展開し、ランダム表示したメンバーに割り当ててあげなければなりません。1セルの文字列を分解するには、MID関数、SEQUENCE関数、LEN関数を使います。

まず、MID関数の対象文字列は＜その３＞の数式です。（上図の最初の下線部分）次の第２引数は、まず、＜その３＞の数式（上図の2番目の下線部分）にLEN関数で文字数を数えます。それを更にSEQUENCE関数で連番にします。MID関数の第３引数の文字数を1とすることで、1文字1セルに分割することができました。

最後に、SORT関数で囲ってアルファベット順に並べ変えます。これが最終結果(A3セル)となります。

以上となります。