Excelで操る！ソルバーの最小化機能を用いた主成分分析

amazon：Excelで操る! ここまでできる科学技術計算 第2版 楽天： Excelで操る! ここまでできる科学技術計算 [ 神足　史人 ]

図１　数学と理科の得点相関図

図２　数学と英語の得点相関図

１６人のクラスで数学、理科、英語のテストをしました。その結果は図３です。 　　　　　　　　図３　数学、理科、英語のテスト結果 　図３のテスト結果に基づき、得点相関図(図１、図２）を作成した。相関図より、点はある方向に分布しているようにみえる。合計点で個人の総合力を評価するより、この方向を設定し、この方向の成分値を計算し、比較するほうがより良い評価となると思われる。 　 　　　　　　　図４　主成分分析の説明 　平均点(xa,ya,za)から主成分方向(a,b,c)を想定し、i番目の人の点数(xi,yi,zi)と主成分方向との距離すなわち垂線の長さ(Li)を計算し、垂線の長さの２乗和が最小となる方向(a,b,c)を求める。 　最初に求まった解が第１主成分であり、平均点を通り第１主成分に直角方向を向くという条件を付加して求めた解が第２主成分である。第２、第３主成分になると、あまり意味がわからなくなってくる。 　通常、主成分分析は固有値問題として取り扱われているようだが、ここではExcelのソルバーを用いて、最適値問題として計算を試みる。

統計学が最強の学問である たった1日で即戦力になるExcelの教科書 入門者のExcel VBA―初めての人にベストな学び方 (ブルーバックス) Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 定量分析実践講座―ケースで学ぶ意思決定の手法 統計数理は隠された未来をあらわにする Excelで学ぶ統計解析―統計学理論をExcelでシミュレーションすれば、視覚的に理解できる マンガでわかる統計学 ビジネスマンのための「数字力」養成講座 (ディスカヴァー携書 20) 完全独習 統計学入門

i番目の人の点数の主成分値(Si)は次式となる。 　　Si = ( Pi・V)/|V| 　　　　　　Pi：i番目の人の点数(xi,yi,zi) 　　　　　　V：主成分ベクトル(a,b,c) 　　　　　　( Pi・V)：ベクトルの内積(成分どうしの積の和) 　　平均点(xa,ya,za)からi番目の人の点数までの距離(Ti)は次式となる。 　　Ti=√( (xi-xa)2+(yi-ya)2+(zi-za)2 ) 　　垂線の長さ(Li)の２乗は３平方の定理より、次式となる。 　　Li2 = Yi2 - Si2 　図５のように、第１主成分を適当に設定し、垂線の長さの２乗和を求める。 　　　　　　　　　　　　　　　　　　　　図5　第１主成分の計算(ソルバー実行前) 　メニューバーのツール→ソルバーを選択すると、図６のソルバー：パラメータ設定画面が現れる。ここでは、目標値として最小値を選択し、変化させるセルを第１主成分とし、目的セルは垂線の２乗和とする。主成分の大きさを１(単位ベクトル)にするため、主成分の大きさ=1の制約条件を設定する。 　　　　　　　　　　　図6 ソルバー：パラメータ設定画面 　実行ボタンをクリックすると、図７のように、第１主成分が求まる。 　　　　　　　　　　　　　　　　　　　　　　図７　第１主成分の計算(ソルバー実行後)

第１主成分と第２主成分は直行するため、第一主成分と第２主成分の内積が０となる制約条件を追加すれば、第１主成分と同様に第２主成分ももとまる。 　　　　　　　　　　　　　　　　　　　図２　第2主成分の計算(ソルバー実行後)

エクセルを用い平均値と標準偏差から偏差値を計算する