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

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

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

ですよね。

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

についてまとめます！

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

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

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

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

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

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

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

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

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

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

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

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

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

SORTBY関数で並び替え

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

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

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

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

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

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

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

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

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

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

タカヒロ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

はい、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を設定していきましょう。

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

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

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

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

タカヒロ

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

VBAを実装する

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

VBAを実行する

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

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

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

VBAの説明

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

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

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

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

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

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

さいごに

いかがでしょうか。

今回は、

についてまとめました。

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