【Excel 】一様分布と正規分布に従う乱数の生成

 この記事では Excel で 一様分布に従う乱数正規分布に従う乱数 などを生成する方法を解説します。

【Excel】一様分布に従う乱数

 サイコロを振ると、どの目が出る確率も等しく 1/6 です。このように全ての事象が同じ確率で出現するような確率分布を 一様分布 とよびます。Excel には一様分布に従う乱数を返す RAND関数 および RANDBETWEEN関数 が用意されています。

【Excel】RAND 関数

 RAND関数 は 0 以上 1 未満の一様乱数を返します。任意のセルに

=RAND()

と入力すると 0.774636 や 0.153979 のような値が戻ってきます。0 以上 10 未満の数値がほしいときは 10 を掛けて乱数の幅を広げます。

=10*RAND()

 負の整数を含めた乱数を発生させることもできます。たとえば -1 以上 1 未満の乱数がほしいときには、乱数の幅を 2 倍に広げてから 1 を引きます。

=2*RAND()-1

 一般に a 以上 b 未満の乱数を発生させるときには

=(b-a)*RAND()+a

と入力します (a, b は任意の整数)。

【Excel】RANDBETWEEN 関数

 RANDBETWEEN関数 は指定した引数の間の一様な整数乱数を返します。

=RANDBETWEEN(最小値,最大値)

 たとえば

=RANDBETWEEN(1,100)

とすれば、1 ~ 100 のランダム整数を返します。引数には負の値も指定できます。たとえば

=RANDBETWEEN(-10,10)

と入力すれば -10 ~ 10 のランダム整数が返ります。

【VBA】再計算を行なわない乱数の生成

 RAND や RANDBETWEEN を含む数式が組込まれたセルは、シート上で何か操作を行なったときに再計算されてしまいます。[数式] タブの [計算方法の設定] で再計算をオフにすることもできますが、これはブック全体の再計算を止めてしまいます。状況によっては、乱数だけ固定したいという場合もあるでしょう。そこで、VBA で再計算を行なわない関数をつくっておいたので、必要な人はコピーして VBE を起動して標準モジュールに貼り付けてください。普通の Excel 関数と同じように使えます。

'[VBA] 再計算を行なわない一様乱数生成関数
Function FRAND() As Double
  Randomize
  FRAND = Rnd
End Function

 FRAND() をワークシートで使用すると、Fixed (固定化) された Random number (乱数) を表示します。使い方は RAND 関数と全く同じです。セルに

=FRAND()

と入力すると、0 以上 1 未満の一様乱数を表示します。0 以上 10 未満の乱数が欲しいときは、

=10*FRAND()

と入力します。1 ~ 10 の整数乱数を得たいときには、INT 関数で小数点以下を切り捨てて 1 を加えます。

=INT(10*FRAND()) + 1

より一般化して a から b の整数を得るには、

=INT((b - a + 1)*FRAND()) + a

と入力します。a と b は任意の整数です。ただ、こんな数式をわざわざ覚えるのは面倒だと思うので、整数乱数を生成する別の関数もつくっておきました。

'[VBA] シートに固定化された無作為整数を出力する関数
Function FRANDBETWEEN(a As Long, b As Long) As Long
  Randomize
  FRANDBETWEEN = Int((b - a + 1) * RND) + a
End Function

 FRANDBETWEEN(a,b) は a 以上 b 以下のランダムな整数を返します。たとえば

=FRANDBETWEEN(1,100)

は 1 から 100 までのランダム整数を返します。

=FRANDBETWEEN(-10,10)

は -10 から 10 までのランダム整数を返します。

正規分布に従う乱数

 0 以上 1 未満の一様乱数を発生させる RAND関数を用いて特定の分布に従うような乱数を生成することもできます。ある分布について累積分布関数 F(x) が与えられているとします。すなわち確率変数 X のとる値が X ≤ x となる確率が

F(x) = P(X ≤ x)

となります。このとき逆に F(x) = P1 となるような x は、F(x) の逆関数を用いて

x = F-1(P1)

によって与えられます。Excel には様々な分布について累積分布関数の逆関数の値を返す関数が用意されています。

 ここでは1例として、正規分布に従う乱数を作ってみます。正規分布における累積分布関数の逆関数の値は Excel 2010 以降は NORM.INV関数 を、Excel 2007 以前のバージョンでは NORMINV関数 を用いて得ることができます。

=NORM.INV(確率,平均値,標準偏差)
=NORMINV(確率,平均値,標準偏差)

 1つめの引数に RAND関数で生成した一様乱数を入れると、対応する x の値を返してきます。それでは成人男性の身長が平均 170 cm, 標準偏差 6 の正規分布に従うと仮定して(実際のデータとほぼ同じです)、サンプルデータ(統計ソフトの練習用に使えます)を作成してみましょう。

 正規分布逆関数データ作成

 上図のように、セル B3 に

=RAND()

と入力し、セル C3 には

=NORM.INV(B3,170,6)

と入力しておきます。とりあえず 400 個のデータを作ってみましょう。オートフィルなどを使って 402 行までデータを埋めてください(もっと多くてもかまいません)。横軸に x, 縦軸に F(x) をとって散布図を描くと、次のように累積分布関数のグラフの形になっています。

 正規分布における累積分布関数

 青い矢印は縦軸から値を1つ選ぶと、それに対して x が 1 つ定まることを示しています。[分析ツール] などを用いると、次のようなヒストグラムを描くこともできます。

 男性身長分布ヒストグラム

 かなりばらつきがありますが、より多くのデータを作成すれば、左右対称の正規分布の形に近づいていくはずです。

データ分析ツールによる乱数生成

 [データ分析ツール] を用いて各種の分布に従う確率変数を簡単に生成することができます(メニューにない場合はアドインが必要です)。ここでは平均 170, 標準偏差 6 の正規分布を作成してみます。[データ] ⇒ [データ分析ツール] ⇒ [乱数発生] を順に選択すると次のような画面が現れます。

 データ分析ツールによる乱数発生

 テキストボックスに必要な値を記入していきます。少しわかりにくいのが、[変数の数] と [乱数の数] ですが、それぞれ出力する乱数の列数と行数です。上の記入例では 1 列に 1000 個の乱数を生成させます。パラメータの項目には [平均] に 170, [標準偏差] に 6 を記入しておきます。出力オプションで [新規ワークシート] にチェックを入れておけば、新しいシートを自動的に作成して乱数を出力します。

コメント