Excel を使って「完全な」分散共分散行列を求める

先週金曜日のゼミで、インデックスポートフォリオを構築する方法を学習しました。その過程で、株式収益率の分散共分散行列を計算する必要があったので、 Excel の分析ツールを使って計算しました。が、エクセルの分析ツールが出力するのは、以下のような下三角部分だけでした。

これだと、上三角部分がないため、分散共分散行列の特長を生かした行列計算ができません。そこで、下三角の成分が、上三角部部を埋めて対称行列になるようにします。

まず、B2からD4の範囲をコピーして、適当なところに「形式を選択して貼り付け」を利用して、行列成分を入れ替えて貼り付けます。オプションは次のように設定します。

貼り付けた結果は次のようになります。

さらにB6からD8をコピーし、B2に「形式を選択して貼り付け」ます。オプションの「空白セルを無視する」にチェックを入れるのがポイントです。

貼り付けた結果は次のようになります。これで対称な「普通の」分散共分散行列ができあがりました。

この方法は、ゼミ生の徐楠さんから教えてもらいました。徐楠さんありがとう!!