エクセル関数で重複しない桁数の多い乱数の生成方法（範囲指定可能）【コピペ用テンプレートあり】
はじめに
エクセル関数のみで、桁数の多い乱数を重複なしで生成する方法 を紹介します。
下にテンプレート（セルデータサンプル）があるので、
生成できる値
生成したい値の
- 最小値
- 最大値
- 桁数
を指定できます。
たとえば、下記のテンプレートでは、
※ ただし、生成され得る値の数（1~9が範囲の場合は9個）が少ない場合は、重複する可能性があります。
エクセルでの算出方法
以下の 全セルをコピー ボタンをクリックし、エクセルの A1 セルに貼り付けると、 L列に乱数が出力されます。
出力する乱数の個数を増やす場合は、 E ~ M列の行数をコピー（オートフィル）で増やしてください。
生成する乱数の範囲や小数部桁数を変更したい場合は、 C2 ~ C4セルの値を変更してください。 C11セルの表示で確認できます。
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
| 1 | 設定欄 | 計算欄2 | 結果 | 確認欄 | ||||||||||
| 2 | 最小値 | 1000 | No. | 区間最小値 | 区間最大値 | 乱数（値） | 重複補正 | 乱数（順序） | 順序 | 出力値 | データ数 | |||
| 3 | 最大値 | 9999.9 | =ROW()-ROW(E$3) | =E3*$C$9 | =F3+2*$C$9-1 | =MOD(RANDBETWEEN(F3,G3),$C$8) | =MOD(H3+COUNTIF(H$2:H2,H3),$C$8) | =RAND() | =RANK.EQ(J3,J:J)-1 | =ROUNDDOWN(INDEX(I:I,MATCH(K3,E:E))/$C$8*($C$3-$C$2)+$C$2,$C$4) | =COUNTIF(L:L,L3) | |||
| 4 | 小数部桁数 | 1 | =ROW()-ROW(E$3) | =E4*$C$9 | =F4+2*$C$9-1 | =MOD(RANDBETWEEN(F4,G4),$C$8) | =MOD(H4+COUNTIF(H$2:H3,H4),$C$8) | =RAND() | =RANK.EQ(J4,J:J)-1 | =ROUNDDOWN(INDEX(I:I,MATCH(K4,E:E))/$C$8*($C$3-$C$2)+$C$2,$C$4) | =COUNTIF(L:L,L4) | |||
| 5 | =ROW()-ROW(E$3) | =E5*$C$9 | =F5+2*$C$9-1 | =MOD(RANDBETWEEN(F5,G5),$C$8) | =MOD(H5+COUNTIF(H$2:H4,H5),$C$8) | =RAND() | =RANK.EQ(J5,J:J)-1 | =ROUNDDOWN(INDEX(I:I,MATCH(K5,E:E))/$C$8*($C$3-$C$2)+$C$2,$C$4) | =COUNTIF(L:L,L5) | |||||
| 6 | 計算欄1 | =ROW()-ROW(E$3) | =E6*$C$9 | =F6+2*$C$9-1 | =MOD(RANDBETWEEN(F6,G6),$C$8) | =MOD(H6+COUNTIF(H$2:H5,H6),$C$8) | =RAND() | =RANK.EQ(J6,J:J)-1 | =ROUNDDOWN(INDEX(I:I,MATCH(K6,E:E))/$C$8*($C$3-$C$2)+$C$2,$C$4) | =COUNTIF(L:L,L6) | ||||
| 7 | 出力数 | =COUNT(E:E) | =ROW()-ROW(E$3) | =E7*$C$9 | =F7+2*$C$9-1 | =MOD(RANDBETWEEN(F7,G7),$C$8) | =MOD(H7+COUNTIF(H$2:H6,H7),$C$8) | =RAND() | =RANK.EQ(J7,J:J)-1 | =ROUNDDOWN(INDEX(I:I,MATCH(K7,E:E))/$C$8*($C$3-$C$2)+$C$2,$C$4) | =COUNTIF(L:L,L7) | |||
| 8 | 取り得る数値の個数 | =(C3-C2)/10^-C4+1 | =ROW()-ROW(E$3) | =E8*$C$9 | =F8+2*$C$9-1 | =MOD(RANDBETWEEN(F8,G8),$C$8) | =MOD(H8+COUNTIF(H$2:H7,H8),$C$8) | =RAND() | =RANK.EQ(J8,J:J)-1 | =ROUNDDOWN(INDEX(I:I,MATCH(K8,E:E))/$C$8*($C$3-$C$2)+$C$2,$C$4) | =COUNTIF(L:L,L8) | |||
| 9 | 個数/区間 | =C8/C7 | =ROW()-ROW(E$3) | =E9*$C$9 | =F9+2*$C$9-1 | =MOD(RANDBETWEEN(F9,G9),$C$8) | =MOD(H9+COUNTIF(H$2:H8,H9),$C$8) | =RAND() | =RANK.EQ(J9,J:J)-1 | =ROUNDDOWN(INDEX(I:I,MATCH(K9,E:E))/$C$8*($C$3-$C$2)+$C$2,$C$4) | =COUNTIF(L:L,L9) | |||
| 10 | =ROW()-ROW(E$3) | =E10*$C$9 | =F10+2*$C$9-1 | =MOD(RANDBETWEEN(F10,G10),$C$8) | =MOD(H10+COUNTIF(H$2:H9,H10),$C$8) | =RAND() | =RANK.EQ(J10,J:J)-1 | =ROUNDDOWN(INDEX(I:I,MATCH(K10,E:E))/$C$8*($C$3-$C$2)+$C$2,$C$4) | =COUNTIF(L:L,L10) | |||||
| 11 | 重複の有無 | =IF(MAX(M:M)＞1,"有","無") | =ROW()-ROW(E$3) | =E11*$C$9 | =F11+2*$C$9-1 | =MOD(RANDBETWEEN(F11,G11),$C$8) | =MOD(H11+COUNTIF(H$2:H10,H11),$C$8) | =RAND() | =RANK.EQ(J11,J:J)-1 | =ROUNDDOWN(INDEX(I:I,MATCH(K11,E:E))/$C$8*($C$3-$C$2)+$C$2,$C$4) | =COUNTIF(L:L,L11) | |||
| 12 | =ROW()-ROW(E$3) | =E12*$C$9 | =F12+2*$C$9-1 | =MOD(RANDBETWEEN(F12,G12),$C$8) | =MOD(H12+COUNTIF(H$2:H11,H12),$C$8) | =RAND() | =RANK.EQ(J12,J:J)-1 | =ROUNDDOWN(INDEX(I:I,MATCH(K12,E:E))/$C$8*($C$3-$C$2)+$C$2,$C$4) | =COUNTIF(L:L,L12) |
算出手順
乱数の生成工程は、少し多いですが、大まかには以下を行っています。
- 取り得る数値を、出力数の分だけ分割し区間を作成
- 区間をずらしながら、区間内で乱数を生成（重複が1回ずつ発生する可能性込み）
- 重複を解消
- 並び替え用の乱数を生成し、並び替え
1. 取り得る数値を、出力数の分だけ分割し区間を作成
乱数の重複がないように、
- i.
C7セル : 計算欄2の行数から、 出力数 を算出します。 - ii.
C8セル : 設定値（最小値、最大値、小数部桁数）を使って、 取り得る数値の個数 を算出します。- 例）最小値：1000.0、最大値：9999.9、小数部桁数：1の場合、90000個の数値が取り得る
- iii.
C9セル : iとiiから、 個数/区間 （1区間あたりの数値の個数）を算出します。
2. 区間をずらしながら、区間内で乱数を生成
分割した区間を使い、乱数を生成します。
ここで生成される乱数は整数（0 ~ 取り得る数値の個数）です。
- i.
F列 : 手順1の 個数/区間 の値を前の行に足しながら 乱数最小値 を設定します。 - ii.
G列 : 乱数最小値 + 2× 個数/区間 - 1 を 乱数最大値 に設定します。- 区間が他の区間と1回だけ重複するように "2×" を行い、重複解消のため "-1" を入れています。
- iii.
H列 :RANDBETWEEN()関数を使い、乱数最小値 ~ 乱数最大値の範囲で乱数を生成します。- 取り得る数値の個数 を超える値は折り返して0スタートするように、
MOD()関数を入れています。
- 取り得る数値の個数 を超える値は折り返して0スタートするように、
RANDBETWEEN関数
指定した範囲の中から、整数の乱数を返す。
例：
=RANDBETWEEN(F3,G3)
- 第1引数 (
F3): 範囲の最小値（必須）- 第2引数 (
G3): 範囲の最小値（必須）
MOD関数
第1引数を第2引数で割った余り（剰余）を返す。
例：
=MOD(12,10)=2 (10を超えると0スタートで折り返される)
- 第1引数 (
11): 除算の分子の値（必須）- 第2引数 (
10): 除算の分母の値（必須）
3. 重複を解消
手順2で発生した重複を解消します（I列）。
COUNTIF()関数を使い、重複を検出し、1を加えます
手順2と同様に、MOD()関数を使い、 取り得る数値の個数 を超える値は折り返して0スタートするようにしています。
COUNTIF関数
評価する データ範囲 と、 条件式 を指定し、条件に一致するセルの個数をカウントする。
例：
=COUNTIFS($B$3:$B$7,"="&$E5,$C$3:$C$7,"="&F$4)
- 第1引数 (
$B$3:$B$7): 評価する データ範囲 （必須）- 第2引数 (
"="&$E5): 第1引数のデータ範囲に適用する 条件式 （必須）
設定値によっては重複が発生する可能性あり
取り得る数値の個数 が少ない設定の場合、多数の重複が生じ、
ここでの処理によって解消しきれない場合があります。重複が残っている場合は
C11セルに有と表示されます。
4. 並び替え用の乱数を生成し、並び替え
手順3までの乱数は、昇順に並んでいるので、ランダムに並び替えます。
- i.
J列 :RAND()関数で各行ごとに乱数を生成します。 - ii.
K列 : iの値とRANK.EQ()関数で並び 順序 番号を作成します。 - iii.
L列 :MATCH(),INDEX()関数を使い、 順序 番号と No.を照合させ、手順3の乱数を並び替えます。 - iv.
L列 : 設定欄の最小値、最大値、小数部桁数に合うようにスケーリング、桁数を修正します（ROUNDDOWN()関数使用）。
RAND関数
0以上1未満の乱数を返す。
例：
=RAND()
RANK.EQ関数
第2引数の範囲のデータ列中で、第1引数のデータが何番目に位置するかを算出。
同順位の場合は、同順位内の最上位の値が返される。
第3引数は0または1で、降順または昇順のどちらで順位付けするかを設定
（今回はどちらでも可）。例：
=RANK.EQ(J3,J:J)
- 第1引数 (
J3): 順位付けするデータ- 第2引数 (
J:J): 順位付けするデータ列の全体範囲- 第3引数 : 降順(0) or 昇順(1) を設定（省略時は降順）
MATCH関数
指定した範囲の中で検索し、最初にヒットした相対位置を返す。
例：
=MATCH(K3,E:E)
- 第1引数 (
K3): 検索値（必須）- 第2引数 (
E:E): 検索範囲（必須）- 第3引数 : 照合の型を
0,-1,1で指定（オプション）
0: 完全一致したセルを返す1: 検索値以下の最大値のセルを返す-1: 検索値以上の最小値のセルを返す
INDEX関数
指定した範囲の中で、m番目（m行n列目）の値を返す
（m, nはそれぞれ第1引数、第2引数で指定）。例：
=INDEX(I:I,2))I列の2行目の値を返す
- 第1引数 (
I:I): 抽出する範囲（必須）- 第2引数 (
K3): 範囲から抽出する要素番号・行番号（必須）- 第3引数 : 範囲から抽出する列番号（オプション）