excel関数でRNDBETWEEN(A,B)は一様分布になるため、A~Bの範囲で標準分布となる関数を教えてください
- User
excel関数でRNDBETWEEN(A,B)は一様分布になるため、A~Bの範囲で標準分布となる関数を教えてください
Assistant
ExcelでAからBの範囲で正規分布(ご質問の「標準分布」は、おそらく一般的な「正規分布」を指しているかと存じます)に従う乱数を生成するには、いくつかの関数を組み合わせる必要があります。
RANDBETWEEN(A,B)がAとBの間で均等な確率で値を返す(一様分布)のに対し、正規分布は中央値付近に値が集まりやすい釣鐘型の分布となります。ここでは、指定された範囲A~Bに収まるように調整された正規分布乱数(切断正規分布とも呼ばれます)を生成する方法を2つのアプローチでご紹介します。
基本的な考え方 正規分布に従う乱数は、通常
NORM.INV(RAND(), 平均, 標準偏差)で生成できます。RAND()は0から1までの一様乱数を生成し、NORM.INVはそれを指定された平均と標準偏差を持つ正規分布の対応する値に変換します。 しかし、これだけでは生成される値がA~Bの範囲に収まるとは限りません。そこで、確率の範囲を調整します。- Aに対応する累積確率
P_A = NORM.DIST(A, 平均, 標準偏差, TRUE) - Bに対応する累積確率
P_B = NORM.DIST(B, 平均, 標準偏差, TRUE) P_AからP_Bの範囲で一様な乱数を生成します:scaled_rand = RAND() * (P_B - P_A) + P_A- この
scaled_randをNORM.INVに渡して、A~Bの範囲に限定された正規分布乱数を得ます:NORM.INV(scaled_rand, 平均, 標準偏差)
問題は、この「平均」と「標準偏差」をどう設定するかです。
方法1:平均と標準偏差をユーザーが別途指定する場合
ご自身で分布の平均(μ)と標準偏差(σ)を決めたい場合は、以下の数式を使用します。
- A: 範囲の下限値 (例: セルE1)
- B: 範囲の上限値 (例: セルF1)
- μ: 正規分布の平均 (例: セルG1)
- σ: 正規分布の標準偏差 (例: セルH1)
数式:
=NORM.INV(RAND()*(NORM.DIST(F1,G1,H1,TRUE)-NORM.DIST(E1,G1,H1,TRUE))+NORM.DIST(E1,G1,H1,TRUE),G1,H1)この方法では、AとBの範囲が指定した正規分布のどの部分を切り出すかを柔軟に制御できます。ただし、AやBがμから大きく離れている場合、生成される値がAやBの近くに偏る可能性があります。
方法2:範囲AとBから平均と標準偏差を自動的に計算する場合
AとBのみを指定し、それに基づいて自然な釣鐘型の分布を範囲内に作りたい場合は、以下のように平均と標準偏差を仮定します。
- 平均 (μ):
(A + B) / 2(範囲の中央値) - 標準偏差 (σ):
(B - A) / 6- この仮定は、AとBの範囲が正規分布のデータの約99.7%を含む(つまり、平均 ±3σ の範囲にほぼ収まる)という考えに基づいています。これにより、AとBの間に値が自然に分布しやすくなります。
Excelのバージョンに応じて、以下のいずれかの数式を使用します。 (AがセルA1、BがセルB1に入力されているとします)
a) LET関数が使える場合 (Excel 2021, Microsoft 365)
LET関数を使うと、数式が読みやすくなります。=LET( val_A, A1, val_B, B1, lower_b, MIN(val_A, val_B), upper_b, MAX(val_A, val_B), IF(lower_b = upper_b, lower_b, /* AとBが同じならその値を返す */ LET( mean_val, (lower_b + upper_b) / 2, stdev_val, (upper_b - lower_b) / 6, prob_A, NORM.DIST(lower_b, mean_val, stdev_val, TRUE), prob_B, NORM.DIST(upper_b, mean_val, stdev_val, TRUE), random_percentile, RAND() * (prob_B - prob_A) + prob_A, NORM.INV(random_percentile, mean_val, stdev_val) ) ) )b) LET関数が使えない場合 (Excel 2019以前など。補助セルを利用) 数式が長くなるため、補助セルを使うと管理しやすくなります。
- A1セル: 範囲の下限値 (A)
- B1セル: 範囲の上限値 (B)
以下の数式をそれぞれ別のセルに入力します。
- C1セル (平均 μ):
=(MIN(A1,B1)+MAX(A1,B1))/2 - D1セル (標準偏差 σ):
=IF(MIN(A1,B1)=MAX(A1,B1), 0, (MAX(A1,B1)-MIN(A1,B1))/6) - E1セル (A~Bの範囲の正規分布乱数):
=IF(D1=0, C1, NORM.INV(RAND()*(NORM.DIST(MAX(A1,B1),C1,D1,TRUE)-NORM.DIST(MIN(A1,B1),C1,D1,TRUE))+NORM.DIST(MIN(A1,B1),C1,D1,TRUE),C1,D1))
このE1セルに、A1とB1で指定された範囲内で正規分布に従う乱数が表示されます。
補足
- これらの数式は、F9キーを押すか、シート上で何か変更が加えられるたびに再計算され、新しい乱数が生成されます。
- 方法2の標準偏差の計算で
(B-A)/6の6という数字は調整可能です。この値を小さくする(例:4)と分布はより中央に尖り、大きくする(例:8)とより平たい分布になります。 - Excelの
NORM.DISTおよびNORM.INV関数は、標準偏差が0の場合の扱いに注意が必要です。上記の方法2の数式では、A=B (範囲がない) の場合にエラーを避けるための処理を入れています。
ご希望の用途に合わせて、いずれかの方法をお試しください。
- Aに対応する累積確率
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.