2025/4/25

エクセルで分散共分散行列（共分散行列）の簡単な求め方【コピペですぐに使える】

はじめに

エクセルには、アドオンの「データ分析」ツールの中に分散共分散行列（共分散行列）を算出できる機能があります。ここではこの機能を使わず、エクセル関数のみで算出する方法を紹介します。

下にサンプルのセルデータがあるので、コピペですぐに使用できます。

分散共分散行列とは

分散共分散行列は、データセット内の変数の分散と、変数間の共分散を行列形式で表したものです。

行列の成分は

対角成分： データセット内の変数の分散
非対角成分： データセット内の変数間の共分散

を表します。

いつ使うのか？

多次元データの分布や全体的な構造を把握するのに役立ちます。 分散共分散行列は、以下をまとめて表しているためです。

データの各変数のバラツキ
データの変数同士がどの程度一緒に変動するか

定義式

変数が $X_1, X_2, \dots, X_n$ のデータセットがあるとき、分散共分散行列は以下のように定義されます。

\begin{pmatrix} {\rm Var}(X_1) & {\rm Cov}(X_1,X_2) & \dots & {\rm Cov}(X_1,X_n) \\ {\rm Cov}(X_1,X_2) & {\rm Var}(X_2) & \dots & {\rm Cov}(X_2,X_n) \\ \vdots & \vdots & \ddots & \vdots \\ {\rm Cov}(X_1,X_n) & {\rm Cov}(X_2,X_1) & \dots & {\rm Var}(X_n) \end{pmatrix}

${\rm Var}(X_i)$ : 変数 $X_i$ の分散
${\rm Cov}(X_i, X_j)$ : 変数 $X_i$ と $X_j$ の共分散

エクセルでの算出方法

以下の 全セルをコピー ボタンをクリックし、エクセルの A1 セルに貼り付けると、 H ~ J 列に3x3の分散共分散行列が算出されます。

A, B, C列のデータを書き換え、データの長さに応じてH4 ~ J6セル中の参照セルを書き換えることで、各自のデータにあわせて計算できます。データの変数を変える場合は、参照データ番号も含めて調整してください。

このサンプルでは、以下の結果が得られます。

\begin{pmatrix} {\rm Var}(X) & {\rm Cov}(X,Y) & {\rm Cov}(X,Z) \\ {\rm Cov}(X,Y) & {\rm Var}(Y) & {\rm Cov}(Y,Z) \\ {\rm Cov}(X,Z) & {\rm Cov}(Y,X) & {\rm Var}(Z) \end{pmatrix} = \begin{pmatrix} 2.5 & 3 & 2.5 \\ 3 & 4.3 & 3.3 \\ 2.5 & 3.3 & 4.3 \end{pmatrix}

	A	B	C	D	E	F	G	H	I	J	K
1	データ					分散共分散行列
2	X	Y	Z					0	1	2
3	1	11	21
4	2	12	22			0		=COVARIANCE.S(OFFSET($A$3:$A$7,0,H$2),OFFSET($A$3:$A$7,0,$F4))	=COVARIANCE.S(OFFSET($A$3:$A$7,0,I$2),OFFSET($A$3:$A$7,0,$F4))	=COVARIANCE.S(OFFSET($A$3:$A$7,0,J$2),OFFSET($A$3:$A$7,0,$F4))
5	3	13	20			1		=COVARIANCE.S(OFFSET($A$3:$A$7,0,H$2),OFFSET($A$3:$A$7,0,$F5))	=COVARIANCE.S(OFFSET($A$3:$A$7,0,I$2),OFFSET($A$3:$A$7,0,$F5))	=COVARIANCE.S(OFFSET($A$3:$A$7,0,J$2),OFFSET($A$3:$A$7,0,$F5))
6	4	16	24			2		=COVARIANCE.S(OFFSET($A$3:$A$7,0,H$2),OFFSET($A$3:$A$7,0,$F6))	=COVARIANCE.S(OFFSET($A$3:$A$7,0,I$2),OFFSET($A$3:$A$7,0,$F6))	=COVARIANCE.S(OFFSET($A$3:$A$7,0,J$2),OFFSET($A$3:$A$7,0,$F6))
7	5	15	25
8
9
10

Jspreadsheet CE

算出手順

エクセルで分散共分散行列を算出するための手順は以下の3つです。

1. 参照データ（変数）番号を記入

0 始まりで、データの変数分の連番を記入します。ここでは、X, Y, Z の3つの変数があるため、0, 1, 2 を記入します。

以下のように、2か所に記入する必要があります。

手順1a. 1列に連番を記入
手順1b. 1行に連番を記入

2. 行列要素を算出

OFFSET, COVARIANCE.S 関数を使用して、行列要素を算出します。（データの背景にある母集団を推測する必要のない場合は COVARIANCE.P 関数を使用します。）すべての要素の値を算出することで、分散共分散行列が得られます。

OFFSET関数

基準のデータ列（第1引数）から行（第2引数）と列（第3引数）をずらした位置の データ列 を返す。
例：=OFFSET($A$3:$A$7,1,2) $C$4:$C$8 を返す

第1引数 ($A$3:$A$7): 基準のデータ列（必須）

第2引数 (1): 基準からずらす行数（必須）

第3引数 (2): 基準からずらす列（必須）

第4引数: 参照するデータ列の行数（オプション）

第5引数: 参照するデータ列の列数（オプション）

COVARIANCE.S関数

第1引数と第2引数のデータ列間の不偏共分散を算出。
指定する2つのデータ列が等しい場合は、そのデータ列の分散を返す。
例：=COVARIANCE.S($A$3:$A$7,$B$3:$B$7)

第1引数 ($A$3:$A$7): 計算に使用するデータ列（必須）

第2引数 ($B$3:$B$7): 計算に使用するデータ列（必須）