【Excel】エクセルでデータ分析を行うやり方（分散分析・因子分析）
Excelで売上、アンケート、検査値などのデータを扱っていると、平均との差が偶然なのか、複数の質問項目に共通する傾向があるのかを判断したい場面があります。
そのようなときに役立つのが、Excelの分析ツールを使った分散分析と因子分析です。
分散分析はグループ間の平均値の差を統計的に検討する方法であり、因子分析は多くの項目に隠れた共通要因を探る方法です。
分析の目的とデータの並べ方を先に整理すると、難しく見える統計処理もExcel上で進めやすくなります。
この記事で確認するポイント
・分散分析で平均値の違いを判断する流れ
・因子分析に向くデータと集計の考え方
・分析ツールが表示されないときの準備
・結果表の読み方と実務での活かし方
ここでは、1行目に見出しがあるサンプルデータを使い、Excelでデータ分析を行うやり方を順番に解説します。
Excelで分散分析を行う手順
それではまず、複数グループの平均に差があるかを調べる分散分析について解説していきます。
| 店舗A | 店舗B | 店舗C |
|---|---|---|
| 82 | 76 | 91 |
| 85 | 79 | 94 |
| 80 | 75 | 89 |
この例では、各列に店舗ごとの日別売上を入力し、店舗ごとの平均売上に統計的な差があるかを確認します。
データ分析ツールの有効化
分散分析を実行する前に、Excelのアドインである分析ツールを有効にしておきましょう。
Windows版Excelでは、ファイルタブからオプションを開き、左側のメニューでアドインを選択します。
画面下部の管理でExcelアドインを選び、設定をクリックすると、利用できるアドインの一覧が表示されます。
分析ツールにチェックを入れてOKを選ぶと、データタブの右側にデータ分析の項目が追加されます。
データ分析が見当たらない場合は、まずアドインの有効化を確認することが近道です。
分析ツールは統計処理用の機能群です。
分散分析、回帰分析、ヒストグラム、基本統計量などを同じ入口から選択できます。
一元配置分散分析の実行
1つの条件だけでグループを比較する場合は、一元配置分散分析を選択します。
データタブのデータ分析をクリックし、一覧から分散分析 一元配置を選びます。
入力範囲には見出しを含めてA1からC4を指定し、グループ化は列、先頭行をラベルとして使用にチェックを入れます。
有意水準は通常0.05のままで問題ありません。
出力先は新規ワークシートを選ぶと、元データを残したまま結果を見比べやすくなります。
列ごとに比較対象のグループを配置することが、一元配置分散分析を正しく実行する基本です。
【操作のポイント】空白セルや文字列が途中に混ざると計算対象が想定と変わるため、入力範囲を選ぶ前に表の欠損を確認しましょう。
有意確率と平均値の確認
出力された表では、最初に各グループの件数、合計、平均、分散を確認します。
平均との差が大きく見えても、データのばらつきが大きい場合は、偶然の範囲と判断されることがあります。
次にANOVA表のP値を見ます。
判断の目安
P値が0.05未満の場合は、少なくとも1組のグループ間に平均差があると判断します。
P値が0.05以上の場合は、今回のデータだけでは平均差があるとは言い切れません。
P値が小さいことは、すべての店舗の平均が互いに違うことを直接示すものではありません。
差が出た組み合わせまで知りたい場合は、各グループの平均、箱ひげ図、追加の多重比較を組み合わせて検討します。
【操作のポイント】P値だけを切り取らず、平均値、件数、分散を同じ画面で確認すると、実務上の差の大きさも判断しやすくなります。
二元配置分散分析と繰り返しの扱い
続いては、店舗と月のように二つの要因を同時に確認する二元配置分散分析を見ていきます。
| 月 | 店舗A | 店舗B | 店舗C |
|---|---|---|---|
| 4月 | 82 | 76 | 91 |
| 5月 | 86 | 78 | 93 |
| 6月 | 81 | 80 | 88 |
行に月、列に店舗を置くと、店舗差と月差を区別して捉えやすくなります。
二つの要因を置くデータ構造
二元配置分散分析では、比較したい二つの分類軸を明確にします。
たとえば、店舗別の差と月別の差を調べるなら、店舗と月が要因です。
各セルには、その組み合わせに対応する測定値を入力します。
同じ店舗かつ同じ月の測定を複数回行ったデータなら、繰り返しありの形式を検討します。
一方で、各組み合わせに値が一つだけなら、繰り返しなしの形式が対象になります。
繰り返しの有無は、データの並べ方ではなく、同じ条件で独立した測定が複数あるかで決まります。
分析メニューの選択
データ分析の一覧には、分散分析 二元配置 繰り返しなしと、分散分析 二元配置 繰り返しありがあります。
繰り返しなしでは、行と列のそれぞれの主効果を確認できますが、交互作用は分離して検討できません。
繰り返しありでは、1標本あたりの行数を指定し、店舗と月が組み合わさったときの影響も検討できます。
入力範囲に見出しを含める場合は、ラベルの位置と指定範囲がずれていないかを確認しましょう。
主効果は、店舗だけ、または月だけに着目した平均的な影響です。
交互作用は、ある店舗では月の影響が大きい一方、別の店舗では小さいといった組み合わせの影響を指します。
結果表の読み分け
出力表では、行、列、交互作用、誤差という行が並びます。
店舗を列に置いた場合は、列のP値から店舗による違いを、行のP値から月による違いを読み取れます。
交互作用のP値が小さいときは、単純に店舗全体の平均だけで結論を出すのは避けましょう。
交互作用があるデータでは、月別に店舗を比較するなど、組み合わせごとのグラフも重要になります。
【操作のポイント】比較する要因を増やすほど必要なデータ量も増えるため、各条件の件数が極端に少なくならないよう収集段階から設計します。
因子分析のためのデータ準備
続いては、多数の質問項目や評価項目に共通する傾向を見つける因子分析の準備を確認していきます。
| 回答者 | 価格満足 | 品質満足 | 接客満足 | 再利用意向 |
|---|---|---|---|---|
| 1 | 4 | 5 | 4 | 5 |
| 2 | 3 | 3 | 5 | 4 |
| 3 | 5 | 4 | 4 | 5 |
因子分析では、行に回答者や観測対象、列に質問項目や測定項目を置く形が基本です。
項目とサンプル数の整備
因子分析は、複数の項目どうしの相関関係から、共通する潜在的な因子を探す手法です。
価格満足と品質満足が似た動きをし、接客満足と再利用意向にも別のまとまりが見られるなら、項目の背後にいくつかの評価軸が存在する可能性があります。
1行目には項目名を入れ、2行目以降には数値を入力します。
回答者番号のような識別用の列は、相関を計算する対象から外します。
因子分析では、項目数に対して十分な回答数を確保するほど、結果の安定性を評価しやすくなります。
入力前の確認事項
・すべての項目で評価の向きをそろえる
・未回答を空欄のまま放置せず処理方法を決める
・文字列ではなく数値として入力する
相関行列の作成
Excel標準の分析ツールには、一般的な統計ソフトのような因子分析専用コマンドが搭載されていない環境があります。
その場合でも、まず相関係数を求めることで、項目間に共通性がありそうかを確認できます。
相関係数をセルで確認する場合、たとえば価格満足がB列、品質満足がC列なら、空いているセルに次の数式を入力します。
=CORREL(B2:B11,C2:C11)
この数式は、B2からB11とC2からC11の二つの範囲にある値の直線的な関連の強さを求めます。
先頭行の見出しを含めず、実際の回答データだけを範囲にする点が重要です。
数式を右方向または下方向へオートフィルする場合は、固定したい範囲にドル記号を付けます。
=CORREL($B$2:$B$11,C2:C11)
ただし、相関行列では行と列の組み合わせごとに範囲が変わるため、各項目の列位置を確認しながら作成するほうが誤りを防げます。
相関がほとんどない項目ばかりなら、共通因子を抽出する意義は小さくなるかもしれません。
外部統計ツールとの連携
本格的な因子分析では、因子数の決定、因子負荷量、共通性、回転後の解釈まで確認します。
Excelで相関行列や前処理を行い、因子抽出そのものは統計ソフトやアドインに渡す運用も実務的です。
因子負荷量は、各項目がどの因子と強く関連するかを表す数値です。
絶対値の大きい因子負荷量を持つ項目をまとめて読むと、因子に名前を付けやすくなります。
たとえば品質、価格、再利用意向に高い負荷量が集まれば、総合満足のような因子として解釈できる場合があります。
【操作のポイント】因子名は数値だけで機械的に決めず、各質問文の内容と業務知識を照らして意味の通る名称にしましょう。
分散分析に使う数式と前処理
続いては、分散分析の前にExcel関数で確認しておきたい平均、分散、件数の計算を見ていきます。
| 項目 | 店舗A | 計算式の例 |
|---|---|---|
| 平均 | 82.3 | =AVERAGE(B2:B4) |
| 標本分散 | 6.3 | =VAR.S(B2:B4) |
| 件数 | 3 | =COUNT(B2:B4) |
平均値と件数の確認
分散分析の実行前には、各グループの平均と件数を関数で確かめます。
=AVERAGE(B2:B4)
AVERAGE関数は指定範囲の平均値を返します。
見出しがB1にある場合、B2以降を指定することで文字列を含めずに計算できます。
件数はCOUNT関数で確認できます。
=COUNT(B2:B4)
グループごとの件数に大きな偏りがある場合は、P値だけでなくデータ収集の背景も確認しましょう。
分散と標準偏差の把握
同じ平均値でも、値の散らばり方が異なれば分析結果の受け止め方は変わります。
標本データの分散にはVAR.S関数、標準偏差にはSTDEV.S関数を使います。
=VAR.S(B2:B4)
=STDEV.S(B2:B4)
分散は偏差を二乗して平均的な散らばりを表す量であり、標準偏差は元の単位に近い尺度でばらつきを表します。
極端に大きい値や入力ミスがあると分散が急に大きくなるため、元データの確認は欠かせません。
欠損値と外れ値の取り扱い
空欄、エラー値、単位の異なる数値が含まれていると、分析結果の信頼性が下がります。
未回答をゼロとして入力すると、実際にゼロという回答と区別できなくなるため注意が必要です。
外れ値を削除するか残すかは、単に平均から遠いかだけで決めず、入力ミス、測定異常、実際に起きた事象のどれに当たるかを確認します。
【操作のポイント】分析用シートを複製し、元データを保持したまま欠損処理や外れ値確認を行うと、後から判断を説明しやすくなります。
分析結果の読み方と可視化
続いては、分散分析と因子分析で得た数値を、報告や意思決定につなげる読み方を確認していきます。
| 確認項目 | 見る数値 | 判断の方向 |
|---|---|---|
| 平均差 | P値 | 0.05未満か確認 |
| ばらつき | 標準偏差 | 極端な差を確認 |
| 因子の意味 | 因子負荷量 | 高い項目を確認 |
P値と実務上の差
P値が0.05未満なら、統計的には差があると考えられます。
しかし、差がわずかで業務に影響しないなら、改善施策の優先順位は高くないかもしれません。
反対に、P値が基準を少し上回っていても、顧客満足や利益に大きく関わる差なら、追加調査を行う価値があります。
統計的有意差と、現場で意味のある差は分けて考えることが大切です。
グラフによる傾向の共有
平均値は集合縦棒グラフ、ばらつきは箱ひげ図を使うと、表だけでは伝わりにくい傾向を共有できます。
グラフを作成するときは、縦軸の開始値や単位を確認し、差を実際以上に大きく見せないようにしましょう。
二元配置分散分析では、月を横軸、店舗を系列にした折れ線グラフを作ると、交互作用の可能性を視覚的に確認できます。
因子分析の結果は、因子名と各項目の関係を短い説明文で添えると、統計に詳しくない相手にも伝わりやすくなります。
報告書に残す判断根拠
分析結果を報告するときは、目的、対象期間、件数、使用した方法、主要な数値、解釈の順で整理します。
たとえば店舗別売上を比較したなら、どの期間のどの売上を対象にし、一元配置分散分析を行ったのかを明記します。
因子分析では、使用した質問項目、抽出した因子数、因子名を残すと、次回の調査との比較がしやすくなります。
【操作のポイント】結果のスクリーンショットだけでは根拠が不足しやすいため、入力範囲や有意水準も文章で残しておきましょう。
まとめ 分散分析と因子分析によるエクセルデータ分析
Excelでデータ分析を行うときは、平均の違いを調べたいのか、項目の背後にある共通傾向を探したいのかを最初に決めることが重要です。
分散分析では、データ分析ツールを有効にし、比較するグループを列または行に整理してから、一元配置または二元配置の形式を選びます。
P値は平均差を判断する有用な目安ですが、平均、件数、ばらつきと合わせて読むことで実務に活かせます。
因子分析では、回答者を行、質問項目を列に置き、項目間の相関を確認するところから始めましょう。
Excelは前処理、関数による確認、グラフ作成に便利であり、本格的な因子抽出では外部の統計ツールを併用する方法もあります。
データの欠損、外れ値、入力範囲を丁寧に確認し、数値の意味を業務の状況と結び付けながら、納得できる分析へ進めていきましょう。
