【行列を使って計算!】多変量正規分布のグラフをエクセルで作成する方法|サンプルファイル付き
3変数以上の正規分布は「行列」で表すことが不可欠
2変量正規分布は次式で表すことができます。
しかし、3変量以上になると、行列を使わないと式が複雑になりすぎてしまいます。
従って、3変量以上の正規分布を扱うには行列を使いこなすことが不可欠です。
【相関なし/あり】二変量正規分布の3Dグラフをエクセルで作成する方法を実演
の記事では、行列を使わない上式を使って3Dグラフを作成する方法を紹介しました。
そこで今回は、同じグラフを行列を使って作成してみます。
これができれば、3変量以上の多変量正規分布にも拡張できるようになります。
一部マクロ(VBA)を使っていますが、本記事の最後でサンプルファイルをダウンロードできます。
「行列」で表した多変量正規分布の式
多変量正規分布は行列を使うと次式で表すことができます。
この式でx、μ、Σは行列です。
2変量の場合には次のようになります。
つまり、2つの変数x1とx2をそのまま縦に並べた行列(ベクトル)がxとなり、2つの変数の平均μ1とμ2を縦に並べた行列がμになります。
もう一つ、正規分布の性質を決めるために必要な分散については、x1の分散σ12とx2の分散σ22が2×2行列Σの対角成分になり、x1とx2の共分散σ12が非対角成分になります。
|Σ|はΣの行列式でスカラー値になります。
xTはxの転置行列で、xが2×1の行列ですので、xTは1×2の行列になります。
従って、
は1×2行列かける2×2行列かける2×1行列ですので、スカラー値になります。
よって、f(x)はスカラー値になります。
f(x)は、二つの変数x1とx2がある値を取る時の確率密度を表します。
行列演算をするためのエクセル関数の使い方
行列はエクセル関数で計算できます。
行列式は次のように計算できます。
転置行列は次のように計算できます。
配列数式を使っていることに注意です。
配列数式についての解説はこちら
>> エクセルの【配列数式】って知ってる?複数の値をいっぺんに計算できる便利技
逆行列は次のように計算できます。
積は次のように計算できます。
多変量正規分布をエクセルシートで計算する
それでは
の式を計算していきましょう。
x1とx2を可変にしてエクセルシートで計算すると、次のようになります。

これでx1=3、x2=3になる確率密度が0.000629であることが分かりました。
グラフを描くためにはx1=-3~3、x2=-3~3の範囲の0.1刻みの数値についての確率密度を計算する必要があります。
要するに、次のような表を作りたいわけです。

しかし、すべてのマス目を埋めるためには、x1とx2を60×60=3,600通りに変更する必要があります。
これを一つずつやるのは誰でも嫌になりますね。
マクロ(VBA)で2次元の表に自動書き込みする
そこでマクロ(VBA)の出番です。
計算シートの黄色の2つのセルを-3から0.1刻みで3まで変えて、f(x)の計算結果を上の表にコピペしていく作業をマクロでやらせましょう。
計算シートを「Cal」シート、上の表を入力するシートを「Result」シートとし、下記のようにプログラムすることで表を作成できます。
Sub Macro1()
'
Dim XX As Double
Dim YY As Double
Worksheets(“Result").Select
For I = 3 To 63
For J = 8 To 68
XX = Range(Cells(7, I), Cells(7, I))
YY = Range(Cells(J, 2), Cells(J, 2))
Worksheets(“Cal").Select
Range(Cells(3, 5), Cells(3, 5)) = XX
Range(Cells(4, 5), Cells(4, 5)) = YY
Range(Cells(26, 3), Cells(26, 3)).Select
Selection.Copy
Worksheets(“Result").Select
Range(Cells(J, I), Cells(J, I)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next J
Next I
'
End Sub
3D等高線でグラフにする
できた表を3D等高線グラフにすると、このような可愛いグラフになります。
サンプルファイルはこちらからダウンロードできます。
マクロの使い方については、入門編をこちらで解説しています。
>> 【マクロ初心者向け】物流シミュレーションをするためにこれだけは知っておきたいこと
>> 物流シミュレーションに必要な機能はたったこれだけ!最短距離でマクロを理解【算術計算】
>> 物流シミュレーションに必要な機能はたったこれだけ!最短距離でマクロを理解【場合分け】