いろんなところで書かれている内容ですが、いつも忘れて検索するので、備忘録的に書いておきます。

Excelで標準正規乱数を作るには、NORM.S.INV関数を使います。これは標準正規分布の累積密度関数の逆関数ですので、この中に(0,1)の一様乱数 RAND() を入れれば標準正規乱数になります。

=NORM.S.INV(RAND())

この乱数が1000個ほしければ、同じ関数を1000個のセルに入れればOK。

これらの平均および標準偏差をとれば、それぞれおよそ0, 1となっています。

また、この列を二つ並べれば、相関のない二つの標準正規分布乱数が得られます。

以下、rand1の確率変数を$${X}$$、rand2の確率変数を$${Y}$$と書くことにします。さて、このままですと、$${X}$$と$${Y}$$は相関＝0の標準正規分布乱数です。ここから作りたいのは、二つの標準正規分布乱数で、相関係数があらじめ定めた相関係数に一致するものです。その相関係数を$${\rho}$$とします。

まず一つ目の標準正規分布乱数は$${X}$$と同じとします。その確率変数を$${A}$$としましょう。つまり、$${A=X}$$です。よって問題は、$${X}$$と相関係数$${\rho}$$をもつ標準正規乱数$${B}$$をどのように作るか、です。ここは天下り的ですが、$${B=\rho X + \sqrt{1-\rho^2} Y}$$とします。すると、$${B}$$の平均は0、分散は$${\rho^2+(1-\rho^2)=1}$$、また、$${A}$$との相関係数は$${\rho}$$になります。

そこでExcelでは、Aの場所にrand1をコピーし、Bには上の式の通りにrand1とrand2を加工して入れます。

すると、AもBも平均0、標準偏差1になっています。そして、AとBの相関をとると、乱数の誤差を除いてちゃんと$${\rho}$$と一致していることがわかります。

ちなみにこのような$${A,B}$$の構成方法は他にもいろいろあるのですが、今回の方法はコレスキー分解を使った乱数生成の方法の二次元版になっています。それを簡単に解説します。

$${N}$$次の相関係数行列$${R}$$が与えられたとき、これが正定値であればコレスキー分解 $${R = L L^t}$$が得られます。$${L}$$は$${N}$$次下三角行列です。すると、無相関の$${N}$$次元標準正規分布を$${\bold{z}}$$として、$${\bold{x}}$$をこれの$${L}$$での変換$${\bold{x} = L.\bold{z}}$$とすれば、$${\bold{x}}$$は相関係数行列$${R}$$をもつ標準正規分布乱数になります。

これを一番簡単な二次元の場合で考えると、二次元の場合の相関係数行列は

$$
R = \begin{pmatrix} 1 & \rho \\ \rho & 1 \end{pmatrix}
$$

ですので、このコレスキー分解は

$$
L = \begin{pmatrix} 1 & 0 \\ \rho & \sqrt{1-\rho^2} \end{pmatrix}
$$

です。実際、

$$
LL^t = \begin{pmatrix} 1 & 0 \\ \rho & \sqrt{1-\rho^2} \end{pmatrix} \begin{pmatrix} 1 & \rho \\ 0 & \sqrt{1-\rho^2} \end{pmatrix}  = \begin{pmatrix} 1 & \rho \\ \rho & 1 \end{pmatrix}
$$

となります。よって、

$$
\begin{pmatrix} A \\ B \end{pmatrix} = \begin{pmatrix} 1 & 0 \\ \rho & \sqrt{1-\rho^2} \end{pmatrix}  \begin{pmatrix} X \\ Y \end{pmatrix} = \begin{pmatrix} X \\ \rho X + \sqrt{1-\rho^2}Y \end{pmatrix}
$$

となり、先に作成した$${A,B}$$と一致していることがわかります。