Excel関数/VBAで指定数値から重複しない乱数を生成する方法!

Excel関数/VBAで指定数値から重複しない乱数を生成したいときはないでしょうか。

けど、そんな中で悩むことは、

・Excel関数で指定数値から重複しない乱数を生成したいが方法がよくわからない
・Excel VBAで指定数値から重複しない乱数を生成したいが方法がよくわからない

ですよね。

今回はそんなお悩みを解決する

・Excel関数で指定数値から重複しない乱数を生成する方法
・Excel VBAで指定数値から重複しない乱数を生成する方法

についてまとめます!

指定数値から重複しない乱数を生成する関数について

Excel関数で指定数値から重複しない乱数を生成する方法について説明をします。

Excelワークシート上で乱数を取り扱う関数は3つあります。

関数名 RAND
機能 0 以上 1 未満の乱数を返します。
書式 =RAND()
関数名 RANDBETWEEN
機能 指定された数値の範囲内の乱数を返します。
書式 =RANDBETWEEN(最小値, 最大値)
関数名 RANDARRAY
機能 0 ~ 1 の範囲の乱数を配列で返します。 ただし、入力する行と列の数、最小値と最大値、および整数または小数の値を返すかどうかを指定することができます。
書式 =RANDARRAY([行], [列], [最小], [最大], [整数])
備考 RANDARRAY関数はスピルとなります。Office2019、Microsoft365以降のバージョンの対応となります。

標準仕様の範囲で数値を指定できる関数は、RANDBETWEEN関数とRANDARRAY関数になります。

引数の最大値と最小値に範囲設定したい数値を指定すればできます。

しかしながら、さらに重複させない条件となると、対応している関数はありません。

ですので、別の関数を組み合わせて重複させない仕組みを実現させる必要があります。

関数の組み合わせで指定数値から重複しない乱数を生成する

指定数値から重複しない乱数を生成する関数の組み合わせについて説明します。

SEQUENCE関数で指定数値の配列を作成

まずは、SEQUENCE関数で指定数値の配列を作成します。
SEQUENCE関数の書式は以下の通りです。

SEQUENCE関数の書式/機能/引数

 

関数名 SEQUENCE
機能 1、2、3、4 など、配列内の連続した数値の一覧を生成します。
書式 =SEQUENCE(行, [列], [開始], [目盛り])
引数 行:出力先の行数を指定します。
列:出力先の列数を指定します。
目盛:生成する整数の間隔を指定します。省略した場合は1つづつ増えます。

例えば、5から14の間を指定したい場合は以下のようにします。
Excel側にシートを準備し、A1に入れるようにしましょう。

=SEQUENCE(10,1,5)

SORTBY関数で並び替え

次にSORTBY関数と乱数系関数で先ほどのSEQUENCE関数で作成した配列をランダムに並び替えていきます。

SORTBY関数の書式/機能/引数

SORTBY関数の書式は以下の通りです。

 

関数名 SORTBY
機能 範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。
書式 =SORTBY(範囲, 基準1, [順序1], [基準2, 順序2]…)
引数 範囲:並び替えの対象となる範囲や配列を指定します。
基準:並び替え基準の範囲や配列を指定します。
順序:並び替えの順序を指定します。
1が昇順でデフォルト、
-1が降順となります。

SORTBY関数の第一引数「範囲」にSEQUENCE関数で作成した配列を指定し、第二引数の「基準」に乱数系関数で生成した数値や配列を指定することにより乱数に準じた形で並び替えが行われる形となります。

RANDARRAY関数でランダムな並び替え基準を指定

SORTBY関数の第二引数の「基準」にRANDARRAY関数で生成した乱数の配列を指定します。
これにより第一引数に指定した数値がランダムに並び替えらえる形となります。

RANDARRAY関数の書式/機能/引数

RANDARRAY関数の書式/機能/引数については以下の通りです。

 

関数名 RANDARRAY
機能 0 ~ 1 の範囲の乱数を配列で返します。 ただし、入力する行と列の数、最小値と最大値、および整数または小数の値を返すかどうかを指定することができます。
書式 =RANDARRAY([行], [列], [最小], [最大], [整数])
引数 行:乱数出力先の行数を指定します。
列:乱数出力先の列数を指定します。
最小値:乱数の最小値を指定します。省略すると0の指定となります。
最大値:乱数の最大値を指定します。省略すると1の指定となります。
整数:整数の乱数を発生させるかを指定します。
TRUE:整数の乱数を発生
FALSEまたは省略:実数(小数)の乱数を発生
備考 SEQUENCE関数はスピルとなります。Office2019、Microsoft365以降のバージョンの対応となります。

関数の数式をシートへ入力する

RANDARRAY関数は配列数式として入力されます。引数に10を指定した場合は第一引数の行数に該当され、10個の配列と乱数が生成されます。

タカヒロ
タカヒロ
なかなか複雑ですので、実際に入力してみましょう。

B1セルにRANDARRAY関数の数式を入れます。10行指定なので引数は10としています。

=RANDARRAY(10)

C1セルにA,B列を指定したSORTBY関数を入れます。

=SORTBY(A1:A10,B1:B10,1)

はい、乱数が重複無しに生成されましたね!

1行で指定数値から重複しない乱数を生成する

1行で指定数値から重複しない乱数を生成する方法について説明をします。

これまで、各列ごとに配列を展開してSORTBY関数でまとめて並び替えをしてきましたが、この配列を直接SORTBY関数に割り当てることもできます。

サンプルと同じく5から14までの間で重複しない乱数を生成させるとなると、以下の数式となります。

=SORTBY(SEQUENCE(10,1,5),RANDARRAY(10))

A1へ入力してみましょう。

はい、1行でRAND関数で乱数が重複無しに生成されましたね。

各引数に指定する内容は以下の通りとなっています。

SORTBY(SEQUENCE(<配列数>,<列数>,<開始の値>),RANDARRAY(<配列数>))

他の乱数系関数で重複しない乱数を生成する

他の乱数系関数でも重複しない乱数を生成してみましょう。

RAND関数で重複しない乱数を生成

RAND関数をB1からB10まで入力します。

=RAND()

はい、RAND関数で乱数が重複無しに生成されました。

RANDBETWEEN関数で重複しない乱数を生成

RANDBETWEEN関数で最小値を1,最大値を10にしてB1からB10まで入力します。

=RANDBETWEEN(1,15)

はい、RANDBETWEEN関数で乱数が重複無しに生成されましたね。

ちなみにSORTBY関数の基準にて、該当する値が重複していたとしても、範囲の値側で並び替えられていることがわかります。

サンプルでは並び替え指数は1の昇順となっています。

VBAで指定数値から重複しない乱数を生成する

指定数値から重複しない乱数を生成するVBAについて説明をします。

VBAで乱数を発生させる関数はRnd関数が用意されていますが、ワークシート関数と同様に重複する場合があります。

一般的に値が重複しないまで繰り返し乱数を生成して重複をさせないやり方がありますが、乱数生成の数が多くなるほど繰り返し処理も多くなるのであまり良いやり方とはいえません。

そこで、VBAのRnd関数は使用せず、ワークシート関数のSORTBY関数、SEQUENCE関数、RANDARRAY関数の組み合わせで生成された乱数を生成し、それをVBAで取得することにより実現していきたいと思います。

VBAコード

指定数値から重複しない乱数を生成するVBAは以下の通りとなります。

Sub 指定数値から重複しない乱数を生成()

    Dim arrTemp As Variant
    Dim intMin As Integer
    Dim intMaxRow As Integer
    Dim strStartCell As String
    
    '乱数の最小値を入力します。
    intMin = 5
    
    '作成したい配列数を入力します。※乱数最大値以上の配列数を指定すると重複値が発生しますので注意してください。
    intMaxRow = 10
    
    '出力先の列を入力します。
    strStartCell = "A"

    '値を消去します。
    Range(strStartCell & ":" & strStartCell).Clear

    '指定列1行目にワークシート関数のSORTBY関数、SEQUENCE関数、RANDARRAY関数を代入します。
    Range(strStartCell & "1").Formula2 = "=SORTBY(SEQUENCE(" & intMaxRow & ",1," & intMin & "),RANDARRAY(" & intMaxRow & "))"
    
    '関数の結果を配列に格納します。
    arrTemp = Range(strStartCell & "1:" & strStartCell & intMaxRow)
    
    '配列の値を指定列1行目から入力します。
    Range(strStartCell & "1:" & strStartCell & intMaxRow).Value = arrTemp
    
End Sub

VBAを設定する

VBAを設定していきましょう。

乱数の最小値を入力します。

intMin = 5

作成したい配列数を入力します。※乱数最大値以上の配列数を指定すると重複値が発生しますので注意してください。

intMaxRow = 10

出力先となるシートを用意して、出力先の列を入力します。

サンプルではA列に指定していますので、A1以降に重複しない乱数が生成される形となります。

strStartCell = "A"

タカヒロ
タカヒロ
環境に応じてセルや値の範囲を変更してください。

VBAを実装する

VBAの実装手順は下記「VBA実装手順」をご参照ください。

VBAを実行する

VBAを実行して、重複しない乱数が生成されるか確認をしてみましょう。

「指定数値から重複しない乱数を生成」のマクロを選択して実行します。

はい、乱数が重複無しに生成され、式自体ではなく値が入力されていますね!

VBAの説明

VBAの内容について説明をします。

一旦Excelシート側の値を消去します。

Range(strStartCell & ":" & strStartCell).Clear

指定列1行目にワークシート関数のSORTBY関数、SEQUENCE関数、RANDARRAY関数を代入します。それぞれの引数へ代入している値はワークシート関数と同様となります。

Range(strStartCell & "1").Formula2 = "=SORTBY(SEQUENCE(" & intMaxRow & ",1," & intMin & "),RANDARRAY(" & intMaxRow & "))"

関数の結果を配列に格納します。

arrTemp = Range(strStartCell & "1:" & strStartCell & intMaxRow)

配列の値を指定列1行目から入力します。

Range(strStartCell & "1:" & strStartCell & intMaxRow).Value = arrTemp

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」+「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、

・Excel関数で指定数値から重複しない乱数を生成する方法
・Excel VBAで指定数値から重複しない乱数を生成する方法

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。




タカヒロ

プログラミングやマーケティングなどに興味あるけど
どのスクールが良いか悩むときはないですか?

タカヒロもスクール選びにとても苦労しました…

そんな時には数ある中から第三者目線でいまの貴方に合ったスクールを提案してくれるスクマドのカウンセリングサービスがおすすめです。

メリットだけでなくディメリットもズバリと伝えてくれます!

これでお悩み時間を短縮できますね!詳細は☟になります。

※完全無料です。ご安心ください。





コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です