【Excel】主成分分析をエクセルで行う方法は？データを要約して特徴をつかむ手順（ソルバー）

主成分分析は、売上、価格、評価、利用回数など、複数の数値項目を少ない指標に要約し、データに隠れた傾向を把握するための分析手法です。

Excelには主成分分析の専用ボタンはありませんが、相関行列、固有値、固有ベクトルを使えば、ソルバーを補助的に利用して考え方を再現できます。

この記事では、1行目に見出しがあるサンプルデータを使い、標準化から主成分得点の計算までを順番に確認します。

Excelだけで計算する場合は、結果だけでなく数式の意味も理解しておくと、分析対象が変わっても応用しやすくなります。

主成分分析は項目を単純に減らすだけではなく、情報のばらつきを最もよく説明する新しい軸を作る方法です。

 

Excelで主成分分析を進める全体手順

それではまず、Excelで主成分分析を行うための全体像について解説していきます。

商品	価格	評価	販売数
A	1200	4.2	85
B	1800	4.8	142
C	900	3.6	46

 

分析対象となるデータ範囲

主成分分析では、行に観測対象、列に数値項目を配置します。

たとえばA列に商品名、B列からD列に価格、評価、販売数を入力した場合、実際に計算へ使う範囲は数値が入ったB列からD列です。

文字列である商品名は識別用として残しますが、相関係数や主成分得点の演算には含めません。

各列は同じ意味を持つ数値の集まりとして整えることが、分析精度の出発点です。

欠損値、全角の数値、単位を含む文字列が混在すると計算結果が崩れるため、事前に確認しましょう。

 

主成分の役割

第1主成分は、元の項目が持つ情報のうち、最も大きなばらつきを説明する軸です。

第2主成分は第1主成分と直交し、第1主成分では説明できなかったばらつきを次に大きく説明します。

たとえば価格、評価、販売数がそろって高い商品がある場合、第1主成分は総合的な商品力に近い意味を持つかもしれません。

ただし、主成分の名称は計算後に係数や散布図を読んで判断するものであり、最初から決めつけないことが重要です。

 

ソルバーを使う場面

Excelのソルバーは、設定した目的セルを最大化または最小化する値を探すアドインです。

主成分分析では、固有ベクトルの候補に対して、相関行列が持つ情報をどの程度説明できるかを計算し、条件付きで調整する場面に活用できます。

厳密な固有値分解を標準機能だけで完全自動化するよりも、ソルバーは計算構造を理解するための補助として考えると扱いやすいでしょう。

 

データ標準化と平均値の計算

続いては、単位の違う数値を比較できる形にそろえる標準化を確認していきます。

商品	価格	価格の標準化値
A	1200	-0.31
B	1800	1.18
C	900	-1.06

 

標準化が必要となる理由

価格が数百円から数千円、評価が1から5、販売数が数十から数百というように桁が違うままでは、値の大きい列が分析を支配します。

そこで各値から平均を引き、標準偏差で割ることで、平均が0、標準偏差が1となる標準化値へ変換します。

標準化を行うと、価格と評価のように単位が違う項目でも、変動の大きさを同じ基準で比較できます。

 

AVERAGE関数とSTDEV.S関数

価格データがB2からB11に入力されている場合、平均値は空いているセルに次の数式で求めます。

標本標準偏差は、別のセルに次の数式を入力します。

対象が母集団の全件であり、母標準偏差を使う明確な理由がある場合はSTDEV.P関数を使いますが、通常のアンケートや抽出データではSTDEV.S関数がよく使われます。

平均値と標準偏差のセルは、後から数式をコピーできるように、項目ごとに横並びで置くと見通しがよくなります。

 

標準化数式の入力

たとえばB列の元データをF列へ標準化し、B13に平均、B14に標準偏差を置いた場合、F2には次の数式を入力します。

ドル記号を付けた絶対参照により、F2の数式を下方向へコピーしても、平均と標準偏差の参照先は固定されます。

先頭セルに数式を入れた後は、フィルハンドルをダブルクリックまたは下へドラッグしてオートフィルするのが効率的です。

 

相関行列による項目間の関係

続いては、標準化したデータから相関行列を作る方法を確認していきます。

	価格	評価	販売数
価格	1.000	0.742	0.681
評価	0.742	1.000	0.813
販売数	0.681	0.813	1.000

 

CORREL関数による相関係数

相関係数は、二つの項目が同じ方向へ動く度合いをマイナス1から1までの数値で表します。

標準化した価格がF2からF11、評価がG2からG11にある場合、二項目の相関係数は次の数式です。

1に近いほど強い正の相関、マイナス1に近いほど強い負の相関、0に近いほど線形の関係が弱いことを示します。

相関係数が高いからといって、片方がもう片方の原因であるとは限りません。

 

相関行列の配置

相関行列では、縦方向と横方向に同じ項目名を並べ、交差するセルにCORREL関数を入力します。

価格と価格、評価と評価のような同一項目同士のセルは必ず1になります。

また、価格と評価の相関係数は、評価と価格のセルと同じ値になります。

この左右対称の形が保たれていない場合、参照範囲がずれている可能性があります。

 

分析前に確認したい相関の偏り

二つの項目の相関係数が極端に高い場合、それらは非常に似た情報を含んでいる可能性があります。

主成分分析は、このように重複した情報を要約する用途と相性がよい手法です。

一方で、ある列が全く変化しない場合は標準偏差が0となり標準化できないため、その列は除外します。

極端な外れ値も相関係数を大きく動かすため、入力誤りなのか意味のある実績なのかを確認しましょう。

 

ソルバーによる主成分係数の探索

続いては、ソルバーを使って主成分係数を調整する考え方を確認していきます。

項目	係数候補	主成分への寄与
価格	0.55	正
評価	0.61	正
販売数	0.57	正

 

固有ベクトルと係数の意味

第1主成分は、標準化した各項目に係数を掛けて足し合わせた新しい変数です。

価格の係数をa、評価の係数をb、販売数の係数をcとすると、第1主成分得点は各行について計算できます。

係数の絶対値が大きい項目ほど、その主成分に強く関係していると読めます。

係数の符号は全体を反転しても主成分としての性質は変わらないため、正負だけで優劣を判断しないことが大切です。

 

目的セルと制約条件

ソルバーでは、係数候補を入れたセルを変数セルに指定し、主成分得点の分散を最大にするよう設定します。

ただし、係数を無制限に大きくすると分散も大きくなってしまうため、係数の二乗和を1にする制約が必要です。

この条件により、係数の大きさをそろえた状態で、最も情報量の大きい方向を探索できます。

主成分得点列の分散はVAR.S関数、係数の二乗和はSUMSQ関数を使うと計算しやすいでしょう。

 

ソルバー設定の操作画面

係数候補をH2からH4、係数の二乗和をH6、主成分得点の分散をH7に置いた例で考えます。

データタブの分析グループからソルバーを開き、目的セルをH7、目標を最大値、変数セルをH2からH4に指定します。

制約の追加画面では、H6を1に等しい条件として登録します。

解決方法はGRG非線形を選ぶと、連続した係数を扱いやすくなります。

ソルバーの結果は初期値に影響されることがあるため、初期係数を変えて同じ傾向になるか確認すると安心です。

 

主成分得点と寄与率の読み方

続いては、計算した主成分をデータの特徴として読む方法を確認していきます。

商品	第1主成分	第2主成分	解釈例
A	0.24	-0.75	平均的
B	1.18	0.21	総合的に高い
C	-1.06	0.53	異なる特徴

 

SUMPRODUCT関数による得点計算

標準化した価格、評価、販売数がF2からH2にあり、係数がF14からH14にあるとします。

第1主成分得点をI2に求める数式は次のとおりです。

SUMPRODUCT関数は対応するセル同士を掛け、その結果を合計するため、主成分得点の計算に適しています。

数式を下へコピーすると、商品ごとの得点を一覧で確認できます。

得点が大きい観測対象は、その主成分が表す特徴を強く持つ対象として読むことができます。

 

寄与率と累積寄与率

寄与率は、各主成分が全体のばらつきをどの程度説明しているかを表す割合です。

第1主成分の固有値を全固有値の合計で割ると、第1主成分の寄与率になります。

第1主成分と第2主成分の寄与率を足したものが累積寄与率です。

累積寄与率が高いほど、少ない主成分で元データの情報を多く残せていると考えられます。

どこまで主成分を採用するかは目的によりますが、散布図で可視化する場合は第1主成分と第2主成分の二軸が基本となります。

 

散布図による特徴把握

第1主成分得点を横軸、第2主成分得点を縦軸にして散布図を作ると、似た特徴を持つ商品や顧客の位置関係を直感的に見られます。

右上に集まる対象、左側に離れている対象などを確認し、元の数値へ戻って理由を考えます。

主成分分析だけで結論を出すのではなく、元データ、業務知識、時期や母数も合わせて検討しましょう。

 

Excelでの主成分分析における注意点

続いては、Excelで主成分分析を扱う際に見落としやすい注意点を確認していきます。

確認項目	チェック内容
データ数	項目数に対して十分な行数があるか
欠損値	空白やエラーが混ざっていないか
解釈	係数と元データを合わせて読むか

 

サンプル数と項目数のバランス

項目数が多いのにデータ件数が少ないと、偶然の偏りを主成分として拾ってしまうおそれがあります。

少なくとも項目数を大きく上回る観測数を用意し、可能であれば別の期間や別のデータでも傾向を確認しましょう。

主成分分析はデータの要約手法であり、少ないデータから万能な予測を作るものではありません。

 

欠損値と外れ値の扱い

空白セルを0として埋めると、実際には値がないことと数値が0であることが混同されます。

欠損の理由を確認し、対象行を除外するのか、妥当な方法で補完するのかを先に決める必要があります。

外れ値についても、単純に削除するのではなく、入力ミス、特別なキャンペーン、異常検知対象などの背景を確認します。

分析前のデータクリーニングが、主成分の解釈を左右します。

 

専用ツールとの使い分け

Excelは少量から中規模のデータを学習目的や業務の一次分析として扱うのに便利です。

一方で、大量データ、複数の前処理、統計的検定、再現性の高い分析が必要な場合は、PythonやRなどの統計ツールも検討しましょう。

Excelで計算過程を可視化してから専用ツールへ移ると、各指標の意味を理解したまま分析を発展させられます。

 

まとめ エクセルで主成分分析を行う手順

Excelで主成分分析を行う場合は、まず数値データを整え、平均と標準偏差を使って各列を標準化します。

次にCORREL関数で相関行列を作成し、項目間にどのような重なりがあるかを確認します。

ソルバーでは係数の二乗和を1にする制約を設定し、主成分得点の分散が大きくなる係数を探索します。

その後、SUMPRODUCT関数で得点を計算し、係数、寄与率、散布図を組み合わせてデータの特徴を解釈します。

重要なのは、得られた主成分に名前を付ける前に、どの項目がどの程度寄与しているかを丁寧に確認することです。

Excelの数式とソルバーを使った手順を理解すれば、売上分析、顧客分類、商品比較など、複数指標を扱う場面でデータを見通しよく要約できるようになります。