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

        

Excelを用いた科学技術計算が第2版になりました 30年10月

amazon:Excelで操る! ここまでできる科学技術計算 第2版

楽天: Excelで操る! ここまでできる科学技術計算 [ 神足 史人 ]



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


        図2 数学と英語の得点相関図
相関図の作成方法参照:エクセルの散布図グラフを用いた相関図の作成
相関係数の計算方法参照:エクセルを用いた相関係数と回帰直線の計算

主成分分析


16人のクラスで数学、理科、英語のテストをしました。その結果は図3です。


        図3 数学、理科、英語のテスト結果

 図3のテスト結果に基づき、得点相関図(図1、図2)を作成した。相関図より、点はある方向に分布しているようにみえる。合計点で個人の総合力を評価するより、この方向を設定し、この方向の成分値を計算し、比較するほうがより良い評価となると思われる。

 
       図4 主成分分析の説明

 平均点(xa,ya,za)から主成分方向(a,b,c)を想定し、i番目の人の点数(xi,yi,zi)と主成分方向との距離すなわち垂線の長さ(Li)を計算し、垂線の長さの2乗和が最小となる方向(a,b,c)を求める。
 最初に求まった解が第1主成分であり、平均点を通り第1主成分に直角方向を向くという条件を付加して求めた解が第2主成分である。第2、第3主成分になると、あまり意味がわからなくなってくる。
 通常、主成分分析は固有値問題として取り扱われているようだが、ここではExcelのソルバーを用いて、最適値問題として計算を試みる。


統計学が最強の学問である


たった1日で即戦力になるExcelの教科書

入門者のExcel VBA―初めての人にベストな学び方 (ブルーバックス)

Excel VBAのプログラミングのツボとコツがゼッタイにわかる本

定量分析実践講座―ケースで学ぶ意思決定の手法

統計数理は隠された未来をあらわにする

Excelで学ぶ統計解析―統計学理論をExcelでシミュレーションすれば、視覚的に理解できる

マンガでわかる統計学

ビジネスマンのための「数字力」養成講座 (ディスカヴァー携書 20)


完全独習 統計学入門




Excelで操る!ソルバーを用いた主成分分析(第1主成分)



 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)の2乗は3平方の定理より、次式となる。

  Li2 = Yi2 - Si2

 図5のように、第1主成分を適当に設定し、垂線の長さの2乗和を求める。


                    図5 第1主成分の計算(ソルバー実行前)


 メニューバーのツール→ソルバーを選択すると、図6のソルバー:パラメータ設定画面が現れる。ここでは、目標値として最小値を選択し、変化させるセルを第1主成分とし、目的セルは垂線の2乗和とする。主成分の大きさを1(単位ベクトル)にするため、主成分の大きさ=1の制約条件を設定する


           図6 ソルバー:パラメータ設定画面

 実行ボタンをクリックすると、図7のように、第1主成分が求まる。


                      図7 第1主成分の計算(ソルバー実行後)



Excelで操る!ソルバーを用いた主成分分析(第2主成分)



 第1主成分と第2主成分は直行するため、第一主成分と第2主成分の内積が0となる制約条件を追加すれば、第1主成分と同様に第2主成分ももとまる。


                   図2 第2主成分の計算(ソルバー実行後)





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

Excelで操る!ソルバーを用いた重回帰分析(NEW)





EXCELを用いた科学技術計算

EXCELのソルバーを用いた科学技術計算

Excelを用いた地球温暖化計算

Excelを用いた確率・統計計算

Excelを用いた関数計算

Excelを用いたグラフの作成



〒673−0036 兵庫県明石市松江62−14
(有)ゴッドフット企画



JR福知山線 脱線事故シミュレーション

脱線事故シミュレーションもEXCELで
(Yahoo Japan 掲載)


Copyright (C) GODFOOT 2010 All Rights Reserved.