【行列を使って計算！】多変量正規分布のグラフをエクセルで作成する方法｜サンプルファイル付き

2021年8月8日2024年5月19日

３変数以上の正規分布は「行列」で表すことが不可欠

2変量正規分布は次式で表すことができます。

しかし、3変量以上になると、行列を使わないと式が複雑になりすぎてしまいます。

従って、3変量以上の正規分布を扱うには行列を使いこなすことが不可欠です。

 

【相関なし／あり】二変量正規分布の3Dグラフをエクセルで作成する方法を実演

の記事では、行列を使わない上式を使って3Dグラフを作成する方法を紹介しました。

そこで今回は、同じグラフを行列を使って作成してみます。

これができれば、3変量以上の多変量正規分布にも拡張できるようになります。

 

一部マクロ（VBA）を使っていますが、本記事の最後でサンプルファイルをダウンロードできます。

 

「行列」で表した多変量正規分布の式

多変量正規分布は行列を使うと次式で表すことができます。

この式でx、μ、Σは行列です。

2変量の場合には次のようになります。

 

つまり、2つの変数x₁とx₂をそのまま縦に並べた行列（ベクトル）がxとなり、2つの変数の平均μ₁とμ₂を縦に並べた行列がμになります。

もう一つ、正規分布の性質を決めるために必要な分散については、x₁の分散σ₁²とx₂の分散σ₂²が2×2行列Σの対角成分になり、x₁とx₂の共分散σ₁₂が非対角成分になります。

 

｜Σ｜はΣの行列式でスカラー値になります。

x^Tはxの転置行列で、xが2×1の行列ですので、x^Tは1×2の行列になります。

従って、

は1×2行列かける2×2行列かける2×1行列ですので、スカラー値になります。

よって、f(x)はスカラー値になります。

 

f(x)は、二つの変数x₁とx₂がある値を取る時の確率密度を表します。

 

行列演算をするためのエクセル関数の使い方

行列はエクセル関数で計算できます。

行列式は次のように計算できます。

 

転置行列は次のように計算できます。

配列数式を使っていることに注意です。

配列数式についての解説はこちら

>> エクセルの【配列数式】って知ってる？複数の値をいっぺんに計算できる便利技

 

逆行列は次のように計算できます。

 

積は次のように計算できます。

 

多変量正規分布をエクセルシートで計算する

それでは

の式を計算していきましょう。

x₁とx₂を可変にしてエクセルシートで計算すると、次のようになります。

 

これでx₁=3、x₂=3になる確率密度が0.000629であることが分かりました。

グラフを描くためにはx₁=-3～3、x₂=-3～3の範囲の0.1刻みの数値についての確率密度を計算する必要があります。

要するに、次のような表を作りたいわけです。

 

しかし、すべてのマス目を埋めるためには、x₁とx₂を60×60＝3,600通りに変更する必要があります。

これを一つずつやるのは誰でも嫌になりますね。

 

マクロ（VBA）で２次元の表に自動書き込みする

そこでマクロ（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等高線グラフにすると、このような可愛いグラフになります。

 

サンプルファイルはこちらからダウンロードできます。

 

マクロの使い方については、入門編をこちらで解説しています。

>> 【マクロ初心者向け】物流シミュレーションをするためにこれだけは知っておきたいこと

>> 物流シミュレーションに必要な機能はたったこれだけ！最短距離でマクロを理解【算術計算】

>> 物流シミュレーションに必要な機能はたったこれだけ！最短距離でマクロを理解【場合分け】