エクセルで主成分分析を行う方法

「ファイル」タブを選択します。

①「その他」、②「オプション」の順に選択します。

「Excel のオプション」ダイアログボックスが表示されます。

①「アドイン」、②「ソルバー アドイン」の順に選択して、③「設定」ボタンを押します。

「アドイン」ダイアログボックスが表示されます。

①「ソルバー アドイン」、②「OK」ボタンを押します。

以上の操作で、ソルバーアドインを表示することができました。

「データ」タブを選択すると、赤矢印で示した位置にアドインが表示されます。

上記のような表を作成します。

今回は会社1から会社10までの評価についてまとめた表になります。

まずはデータの標準化を行います。

標準化はデータを中心化した後、基準化する操作を指します。

基準化することでデータの単位が異なる場合やデータの重さに差があるものでも扱いやすくなります。

基準化の公式は「=(各値-平均値)/標準偏差」です。

まずは値の平均値と標準偏差を計算します。

「平均」と「標準偏差」の項目名を入力します。

まずは各値の平均を求めます。

「=AVERAGE(B2:B11)」と入力し、Enterを押します。

AVERAGE関数についての詳細は以下の記事をご確認ください。

ExcelのAVERAGE関数の使い方｜引数の平均を返す

平均値が求められました。オートフィルで他のセルにコピーします。

セルの右下にあるフィルハンドルをドラッグします。

平均値が求められました。

次に標準偏差を計算します。標準偏差はSTDEV.S（スタンダードディービエーション・エス）関数を使って求めます。

「=STDEV.S(B2:B11)」と入力し、Enterを押します。

標準偏差については以下の記事でご紹介しています。

標準偏差をエクセルの関数で求める方法とグラフの作り方（散布図）

標準偏差が求められました。オートフィルで他のセルにコピーします。

セルの右下にあるフィルハンドルをドラッグします。

小数点以下の数値が多すぎて見づらい場合は、適宜数値の桁数を調整しましょう。

①桁数を調整したいセルを範囲選択して、②「小数点以下の表示桁数を減らす」を数回クリックします。

小数点以下の表示桁数を調整できました。

次に数値の基準化を行うための表を作成します。

A1セルからF13セルの表をコピーして作成しましょう。

①表を範囲選択してキーボードのCtrl + Cを押します。

②貼り付けたいセルを選択して、Ctrl + Vを押します。

コピーして基準化の表を作成できました。

B16セルからF25セルまでを選択して、キーボードの「Del（デリート）を押します。

基準化の表（B16セルからF25セル）から値をすべて削除できたので、次に基準化の計算を行います。

数式は「=(各値-平均値)/標準偏差」です。

B16セルに「=(B2-B$12)/B$13」と入力しEnterを押します。

後からオートフィルでコピーしたいので「$」マークで複合参照をかけておきます。

基準化した値が表示されました。

他のセルにも同じ数式をオートフィルでコピーします。

セルの右下のフィルハンドルを右や下にドラッグしてコピーしましょう。

値の基準化が完了しました。

平均が0、標準偏差が1になっていれば問題ありません。

「3.99968E-16」などの数値はほとんど0なのでこのままで問題ありません。

まずは第一主成分を求めます。第一主成分は「データの総合評価」のような位置づけになります。

基準化した相関係数の行列の固有値と固有ベクトルを求めますが、エクセルにはその機能がないため「ソルバー」というアドインを使用します。

後ほど説明しますが係数には制限をかけます。

そのため、制限を設定するための表としてH1セルからM2セルに「係数」「2乗」などの項目名を入力しておきます。

お好みでセルの塗りつぶしの色など、体裁も整えましょう。

次に主成分を求めるための表を作成しておきます。

①A1セルからF13セルの表を選択してキーボードのCtrl + Cを押します。

②表を貼り付けたいセル（例：H4セル）を選択して、Ctrl + Vを押します。

貼り付けた表を上記の画像のように整えましょう。

今回は第五主成分まであるので「第一主成分」から「第五主成分」まで項目名を入力しておきます。

最後の行に「分散」という項目名を追加しておきましょう。

以上で表ができました。

まずは各主成分の係数を求める表を作成しておきましょう。

ただし係数は大きくすればそれだけ分散が広がってしまうので、「係数を2乗した値の合計が1になる」という制約条件を設定します。

以下の手順で、「係数を2乗した値の合計が1になるかどうか」をソルバーで求めるための表を作成しましょう。

POWER関数の書式は「=POWER(数値,指数)」で、数値の引数に指定した値をべき乗できる関数です。

I2セルに「=POWER(I1,2)」と入力してEnterを押します。

数式が入力できました。今はI1セルに値が入っていないため「0」と表示されます。

オートフィルでコピーしましょう。

「合計が1になるか」が係数の条件なので、N2セルにSUM関数を入力しておきましょう。

N2セルに「=SUM(I2:M2)」と入力して、Enterを押します。

次に主成分を求める数式を入力します。

主成分は各値に係数をかけたものをすべて足して求めます。

計算にはSUMPRODUCT関数を使います。

I5セルに「=SUMPRODUCT($I$1:$M$1,B16:F16)」と入力してEnterを押します。

SUMPRODUCT関数の詳細については以下の記事でご紹介しています。

ExcelのSUMPRODUCT関数の使い方｜範囲または配列の積を合計する

I5セルに主成分を求める数式が入力できました。

係数が入っていないためI4セルが「0」になっています。

仮の係数として「0.5」と入力し、Enterを押します。

オートフィルで他のセルにもコピーします。

すると、主成分が計算されます。

I5セルの数式もオートフィルでコピーしましょう。

次に第一主成分の分散を求めます。

分散は「VAR.S（バリアンスエス）関数」を使って求めます。

I15セルに「=VAR.S(I5:I14)」と入力し、Enterを押します。

以上で第一主成分の分散が最大になる値を求める準備ができました。

次にソルバーアドインを使います。

上記「ソルバーアドインを有効にする」セクションでご紹介した方法を参考にソルバーアドインを表示しましょう。

①「データ」タブ、②「ソルバー」の順に選択します。

「ソルバーのパラメーター」ダイアログボックスが表示されました。

簡単に説明すると「目的セル」に指定したセルの値（分散）が「最大化」するときの「変数セル」の値を「制約条件」を加味しながら求める、という設定になります。

①「目的セルの設定」に第一主成分の分散を求めたセル（例：I15セル）を入力して、②「最大値」を選択します。

セルの指定は、セルをクリックすることでも可能です。絶対参照は自動的にかかるために入力する必要はありません。

次に「分散値が最大化するときの値」を求めるセル範囲を指定します。

①I1セルからM1セルを範囲選択して入力します。

次に制約条件を追加します。

②「追加」ボタンを押します。

「制約条件の追加」ダイアログボックスが表示されます。

①N2セルの合計値が「1」になるようにしたいので、「セル参照」にN2セルを入力します。

②「=」を選択して、③制約条件に「1」と入力します。

④「OK」ボタンを押します。

「ソルバーのパラメーター」ダイアログボックスに戻ります。

①「制約のない変数を非負数にする」のチェックマークを外して、②「解決」ボタンを押します。

「ソルバーの結果」ダイアログボックスが表示されました。

「OK」ボタンを押します。

以上の操作で係数が求められて、第一主成分の値も求められました。

次に第二主成分も求めます。

①第一主成分を求める時に使った表をコピーして、②任意のセルに貼り付けます。

係数をリセットするので、I18セルからM18セルまでを範囲選択してキーボードのDelを押します。

仮の係数として「0.5」と入力しオートフィルでコピーしておきましょう。

第二主成分は第一主成分の軸に対して直交する必要があります。

第一主成分のデータと重なるデータがないようにするためです。

そのため、主成分の係数をかけ合わせた数（積和）が0になるように制約条件を追加します。

O19セルに「=SUMPRODUCT(I1:M1,I18:M18)」と入力してEnterを押します。

積和の数式を設定できました。

次に第一主成分の計算と同じように、第二主成分にも数式を入力します。

J5セルに「=SUMPRODUCT($I$18:$M$18,B16:F16)」と入力してEnterを押します。

すると、第二主成分が計算されます。

JI5セルの数式をオートフィルでコピーします。

第二主成分の分散を求めます。

J15セルに「=VAR.S(J5:J14)」と入力し、Enterを押します。

以上で第二主成分の分散が最大になる値を求める準備ができました。

次にソルバーアドインを使います。

①「データ」タブ、②「ソルバー」の順に選択します。

「ソルバーのパラメーター」ダイアログボックスが表示されました。

「すべてリセット」ボタンを押します。

「OK」ボタンを押します。

設定をリセットできたので、再度設定を行います。

①「目的セルの設定」に第二主成分の分散を求めたセル（例：J15セル）を入力して、②「最大値」を選択します。

セルの指定は、セルをクリックすることでも可能です。絶対参照は自動的にかかるために入力する必要はありません。

次に「分散値が最大化するときの値」を求めるセル範囲を指定します。

①I18セルからM18セルを範囲選択して入力します。

次に制約条件を追加します。

②「追加」ボタンを押します。

「制約条件の追加」ダイアログボックスが表示されます。

①N19セルの合計値が「1」になるようにしたいので、「セル参照」にN19セルを入力します。

②「=」を選択して、③制約条件に「1」と入力します。

④次に積和についても条件として設定したいので「追加」ボタンを押します。

①O19セルの積和が「0」になるようにしたいので、「セル参照」にO19セルを入力します。

②「=」を選択して、③制約条件に「0」と入力します。

④「OK」ボタンを押します。

「ソルバーのパラメーター」ダイアログボックスに戻ります。

①「制約のない変数を非負数にする」のチェックマークを外して、②「解決」ボタンを押します。

以上の操作で第二主成分を求めることができました。

次に第三主成分を求めます。

①第二主成分を求める時に使った表をコピーして、②任意のセルに貼り付けます。

仮の係数として「0.5」を入力しておきましょう。

第三主成分でも係数の積和が0になるように制約条件を追加します。

まずは、第二主成分と第三主成分の積和が0になるように設定します。

O22セルに「=SUMPRODUCT(I18:M18,I21:M21)」と入力してEnterを押します。

第三主成分以降は、それまでの係数との積和も必要になります。

第一主成分と第三主成分の積和も0になるように設定します。

P22セルに「=SUMPRODUCT(I1:M1,I21:M21)」と入力してEnterを押します。

積和の数式を設定できました。

次に第一主成分、第二主成分の計算と同じように、第三主成分にも数式を入力します。

K5セルに「=SUMPRODUCT($I$21:$M$21,B16:F16)と入力」してEnterを押します。

すると、第三主成分が計算されます。

K5セルの数式をオートフィルでコピーします。

第三主成分の分散を求めます。

J15セルに「=VAR.S(K5:K14)」と入力し、Enterを押します。

以上で第三主成分の分散が最大になる値を求める準備ができました。

次にソルバーアドインを使います。

①「データ」タブ、②「ソルバー」の順に選択します。

「ソルバーのパラメーター」ダイアログボックスが表示されました。

「すべてリセット」ボタンを押します。

「OK」ボタンを押します。

設定をリセットできたので、再度設定を行います。

①「目的セルの設定」に第三主成分の分散を求めたセル（例：K15セル）を入力して、②「最大値」を選択します。

セルの指定は、セルをクリックすることでも可能です。絶対参照は自動的にかかるために入力する必要はありません。

次に「分散値が最大化するときの値」を求めるセル範囲を指定します。

①I21セルからM21セルを範囲選択して入力します。

次に制約条件を追加します。

②「追加」ボタンを押します。

「制約条件の追加」ダイアログボックスが表示されます。

①N22セルの合計値が「1」になるようにしたいので、「セル参照」にN22セルを入力します。

②「=」を選択して、③制約条件に「1」と入力します。

④次に積和についても条件として設定したいので「追加」ボタンを押します。

①O22セルの積和（第二主成分と第三主成分の積和）が「0」になるようにしたいので、「セル参照」にO22セルを入力します。

②「=」を選択して、③制約条件に「0」と入力します。

④「追加」ボタンを押します。

次に第三主成分と第一主成分の積和が0になるように設定します。

①「セル参照」にP22セルを入力します。

②「=」を選択して、③制約条件に「0」と入力します。

④「OK」ボタンを押します。

「ソルバーのパラメーター」ダイアログボックスに戻ります。

①「制約のない変数を非負数にする」のチェックマークを外して、②「解決」ボタンを押します。

「OK」ボタンを押します。

第三主成分が求められました。

同様の操作で第四主成分と第五主成分も求めます。

次に、第一主成分から第五主成分からどこまで採用するかを決めます。

ほとんどの場合、第一主成分と第二主成分が採用されます。

主成分でどこまでデータを説明できるかを寄与率と累積寄与率を求めることで解釈します。

まずは、分散の合計を出します。

N15セルに「=SUM(I15:M15)」と入力し、Enterを押します。

まず、寄与率を求めます。

寄与率は分散全体のうち、対象となる値がどれくらいを占めているかを計算で求めます。

計算式は「=対象となる分散/分散の合計」です。

任意のセルに「=I15/$N$15」と入力しEnterを押します。

オートフィルでコピーします。

寄与率が求められました。

次に累積寄与率を求めます。

累積寄与率は、それぞれの寄与率を一つずつ足しながら求めます。

任意のセルに「=SUM($Q$4:Q4)」と入力しEnterを押します。

オートフィルでコピーします。

累積寄与率は50%を超えているかが目安になります。

今回は第一主成分のみでも50%を超えているため問題ありません。

第一主成分と第二主成分を採用することにします。

次に主成分の値を使って散布図を作成します。

①第一主成分と第二主成分の値（例：I5セルからJ14セル）、②「挿入タブ」の順に選択します。

③「散布図またはバブル チャートの挿入」、④「散布図」の順に選択します。

散布図が作成できました。

タイトルを任意の名前に変更します。

グラフは横軸が第一主成分、縦軸が第二主成分になります。

表示されていない場合は、①グラフ、②「グラフ要素」の順に選択します。

③「軸ラベル」にチェックマークを入れます。

それぞれの軸ラベルに「第一主成分」「第二主成分」と入力します。

軸ラベルが設定できました。

このままではどのデータがどの会社かわからないので「データラベル」を追加します。

①「グラフ要素」を選択し、②「データ ラベル」にチェックマークを入れます。

③データ ラベルの「＞」にマウスオーバーして、④「その他のオプション」を選択します。

すると、画面右端に「データ ラベルの書式」ダイアログボックスが表示されます。

「セルの値」にチェックマークを入れます。

できるだけシンプルなグラフにしたい場合は、「セルの値」のみにチェックマークが入った状態にしましょう。

「データ ラベル範囲」ダイアログボックスが表示されます。

①会社名が入力されたセルを範囲選択して、②「OK」ボタンを押します。

会社の名前がグラフ上に追加されました。

データに重なって見づらい場合は、データラベルをダブルクリックして、もう一度クリックするとドラッグで移動できるようになります。

以上でグラフが完成しました。

主成分分析で散布図が作成できましたが、このままではどんな表であるのかがわからないため第一主成分と第二主成分の解釈をおこいます。

以下の方法で、各主成分の係数を求めます。

各主成分の係数でグラフを作成します。

①項目を範囲選択します。

②キーボードのCtrlを押したまま、第一主成分の係数を範囲選択します。選択できたらCtrl + Cを押してコピーします。

①任意のセルを選択します。

②「ホーム」タブ、③「貼り付け」、④「形式を選択して貼り付け」の順に選択します。

「形式を選択して貼り付け」ダイアログボックスが表示されます。

①「値」を選択して、②「行/列の入れ替え」にチェックマークを入れます。

③「OK」ボタンを押します。

データを貼り付けられました。

値を大きい順に並べ替えます。

①貼り付けたデータのうち「値」が表示されているセル（例：Z4セル）を選択します。

②「データ」タブ、③「降順」の順に選択します。

データが大きい順に並べられました。

横棒グラフを作成します。

①データ、②「挿入」タブ、③「おすすめグラフ」の順に選択します。

「グラフの挿入」ダイアログボックスが表示されます。

①「横棒」、②「集合横棒」、③「集合横棒」の順に選択します。

④「OK」ボタンを押します。

グラフが作成できました。

グラフを見てみると全体的に値が高いので「総合的な評価」と解釈しておきます。

第二主成分についても同じように横軸グラフを作成しましょう。

「財務安定性」「製品の品質」の値が高いです。

以上の操作で主成分分析が完了しました。

グラフを見てみると例えば、「会社7」は第一主成分は総合評価が高く、「会社4」は安定性の評価が高いことが分かります。