ExcelVBAで正規分布乱数を生成してヒストグラムと基本統計量をチェック

　ここでは、信頼性解析の準備としてExcelVBAで（疑似）正規分布乱数を生成します。つぎに、”データ分析ツール”の中の”基本統計量”と”ヒストグラム”を使って生成した乱数のチェックを行います。また、ヒストグラムと正規分布確率密度関数を同グラフ内に重ねて表示します。

ExcelVBAで（疑似）正規分布乱数を生成

　Excelで使われる一様分布乱数RAND()と同様に、ExcelVBAではRNDで一様分布乱数（区間[0,1)で確率密度関数が一定である乱数）を発生できます。これを利用して、今回は正規分布関数の逆関数NormInvから疑似正規分布乱数を生成します。

WorksheetFunction.NormInv（Rnd ,平均値 ,標準偏差）

　プロシージャでは、エクセルシート内から平均、分散、発生回数を読み込んで、B列に疑似正規分布乱数を出力しています。

データ分析ツールで乱数のチェック

　生成した乱数から基本統計量とヒストグラムを図化したエクセルシートです。基本統計量とヒストグラムはExcelにある”データ分析ツール”を利用しました。

データ分析ツールの導入

　データ分析ツールを利用するには、[ファイル]　→　[オプション]　→　 [アドイン]　→　 [設定]へと進む（下右図）と、下左図のようなアドインを設定する窓が開きます。分析ツールをチェックしてOKを押すと、エクセルシートにて分析ツールが利用できるようになります（[データ]　→　[データ分析]で利用）。

基本統計量の算定

　 [データ]　→　[データ分析] 　→　 [基本統計量]へと進みます。”入力範囲”に発生させたB列の乱数範囲を指定し、”出力先”に結果の表を出力する左上のセルを指定、出力したい統計量をチェック（上述の出力では”統計情報(s)”をチェック）すると、基本統計量が出力されます。

ヒストグラムの作成

　 [データ]　→　[データ分析] 　→　 [ヒストグラム]と進みます。 ”入力範囲”に発生させたB列の乱数範囲を指定し、 ”データ区間”にヒストグラムのデータ区間を示したセルを指定します。上述のシートでは基本統計量を見ながらC列にデータ区分を設定しました。

　 ”出力先”に結果の表を出力する左上のセルを指定して、出力したい図を選定（上述のシートでは”グラフ作成”をチェック）すると、ヒストグラム表と図が出力されます。

ヒストグラムと正規分布関数のグラフ化

　下の図では、発生させた乱数と正規確率密度関数とを重ねて表示し、おおよその精度を検証しています。

　この図は以下の手順で描いています。

1. 発生させた乱数と正規確率密度関数とを、ヒストグラムの各区間の中央値をｘとして表にまとめる。
2. ヒストグラムのオレンジ線を指定しておいて、[グラフツール]→[デザイン]→[グラフの種類の変更]へと進む。
3. ヒストグラムのデータウィ第2軸をクリックし、集合縦棒に変更する。
4. 縦軸の1軸と2軸が合っていることを確認して、2軸を消す。
5. 横軸をｘのデータ範囲に揃える（集合縦棒の横軸を散布図と合わすため）。

まとめ

　今回は、ExcelVBAで（疑似）正規分布乱数を生成し、”データ分析ツール”を用いて生成した乱数のチェックを行い、ヒストグラムと正規分布確率密度関数を同グラフ内に重ねて表示してみました。

　実際に実行してみると、Excelでも簡便に疑似乱数が発生可能であり、MCS（モンテカルロシミュレーション）にも利用できそうです。

　Excelでの疑似一様乱数の手法を調べましたが、よくわかりません。Mersenne Twister（周期の問題あり）であるとする情報を出している記事もありましたが、確認が取れていません。この辺りは、専門家ではないこともあり、ここでは検討を行わない事としたいと思います。

　なお、Pythonで正規分布乱数を生成してヒストグラムと基本統計量をチェックにPythonを用いて同様の作業を行ってますので、興味のある方はどうぞ。