エクセルで複数グループの平均値を比べるとき、単純に平均だけを見て「差がある」と判断すると、偶然のばらつきを見落とすことがあります。
そこで役立つ統計手法が分散分析です。
特に、一つの要因によって分けた複数グループを比較する場合には、一元配置分散分析を使います。
Excelの分析ツールを利用すれば、複雑な計算式を手入力しなくても、F値、P値、F境界値を含む検定結果を作成できます。
この記事で確認するポイントです。
・分析ツールを有効にする方法
・一元配置分散分析に適したデータの並べ方
・ANOVA 一元配置を実行する操作手順
・P値と有意水準から結論を読む考え方
サンプルでは、1行目を見出し行として、3つの研修方法ごとのテスト得点を比較します。
最初に結論をいえば、Excelの分析ツールからANOVA 一元配置を選び、見出しを含むデータ範囲と有意水準を指定すれば、複数グループの平均差を検定できます。
それでは、データ準備から結果の読み取り方まで順番に確認していきましょう。
Excelで一元配置分散分析を実行する手順
それではまず、分析ツールを使って一元配置分散分析を行う基本操作について解説していきます。
| 研修A | 研修B | 研修C |
|---|---|---|
| 72 | 81 | 76 |
| 75 | 84 | 79 |
| 70 | 80 | 77 |
| 74 | 83 | 78 |
分析ツールの有効化
一元配置分散分析を始める前に、Excelの分析ツールを利用できる状態にします。
リボンのデータタブに「データ分析」が表示されていれば、すでに準備は完了しています。
表示されていない場合は、ファイルタブからオプションを開き、アドインの設定を確認しましょう。
画面下部の管理でExcelアドインを選び、設定ボタンを押します。
表示された一覧で「分析ツール」にチェックを入れ、OKを選択してください。
これでデータタブの右側にデータ分析ボタンが追加されます。
分析ツールはExcelに標準で用意されているアドインであり、別途関数を入力しなくても統計解析を実行できる機能です。
【操作のポイント】分析ツールが見つからないときは、Excelを再起動するとリボン表示が更新されることがあります。
データ分析からANOVA 一元配置を選ぶ操作
続いて、分析ツールの一覧から一元配置分散分析を選択します。
データタブを開き、右端付近にあるデータ分析をクリックしましょう。
分析ツールのダイアログボックスが表示されたら、「分散分析 一元配置」または「ANOVA 一元配置」を選び、OKをクリックします。
一元配置は、一つの分類要因で分けられた3グループ以上の平均を比較するときに適した方法です。
たとえば研修方法、店舗、製造ライン、広告パターンなど、比較したい分類が一つの場合に利用できます。
二つのグループだけを比べる場合はt検定も選択肢になりますが、三つ以上の平均値を同時に比較するなら一元配置分散分析が基本です。
【操作のポイント】「分散分析 二元配置」と間違えないように、比較する要因の数を確認してから選びましょう。
入力範囲と出力先の指定
続いては、ANOVA 一元配置の設定画面で入力範囲と出力先を指定していきます。
入力範囲には、見出しを含めたA1からC5のような表全体を指定します。
グループごとに列へ並べた場合は、「グループ化」で列を選択してください。
1行目に研修A、研修B、研修Cのような項目名があるため、「先頭行をラベルとして使用」にチェックを入れます。
有意水準には通常0.05を入力します。
出力オプションでは、空いているセルを出力先に指定するか、新しいワークシートを選ぶと見やすくなります。
設定例です。
入力範囲 A1:C5
グループ化 列
先頭行をラベルとして使用 チェックあり
有意水準 0.05
出力先 E1
入力範囲に空白行や文字列が混ざると、分析結果が正しく作成されないことがあります。
【操作のポイント】元データを残したい場合は、新しいワークシートに出力して検定表とデータ表を分ける方法が便利です。
一元配置分散分析に使うデータの並べ方
続いては、分散分析でエラーを防ぐためのデータ構成を確認していきます。
| 店舗A | 店舗B | 店舗C |
|---|---|---|
| 125 | 143 | 132 |
| 130 | 147 | 129 |
| 128 | 145 | 135 |
グループごとに列へ配置する形式
それではまず、最も扱いやすい列方向のデータ配置について解説していきます。
Excelの一元配置分散分析では、各グループを列ごとに並べる形が一般的です。
1行目にはグループ名を入力し、2行目以降に各観測値を縦方向へ入力します。
今回ならA列を研修A、B列を研修B、C列を研修Cとして、各列にテスト得点を入力します。
グループごとの人数が同じでなくても、基本的には分析できます。
ただし、途中に空白セルがあるとデータ件数の認識に影響する場合があるため、欠損値の扱いはあらかじめ決めておく必要があります。
数値の単位は全グループでそろえることが重要です。
【操作のポイント】売上と利益率のように単位が異なる値を一つの分散分析表へ混在させないでください。
先頭行のラベルと数値の確認
続いては、見出し行と数値データの確認方法を見ていきます。
1行目に文字列の見出しがある場合、分析ツールの設定画面で先頭行をラベルとして使用にチェックを入れます。
この設定を忘れると、Excelが見出しをデータとして扱おうとしてエラーになることがあります。
見出しには「Aグループ」「Bグループ」のように、あとから見ても区別しやすい名称を入れるとよいでしょう。
得点や売上などの実測値は、セルの表示形式だけでなく、実際に数値として認識されているかも確認します。
左寄せで表示される数値や、セル左上の緑色の三角マークがある値は、文字列になっている可能性があります。
文字列として保存された数字は計算対象から外れることがあるため、警告マークから数値に変換しておくと安心です。
【操作のポイント】データ範囲を選択した状態でステータスバーの平均や個数を確認すると、異常値や文字列を見つけやすくなります。
欠損値と外れ値の扱い
続いては、空白セルや極端な値を含むデータの扱いを確認していきます。
分散分析はグループ内のばらつきとグループ間のばらつきを比較するため、一部の極端な値が結果へ大きく影響することがあります。
しかし、結果に合わないという理由だけで外れ値を削除することは適切ではありません。
入力ミス、測定ミス、対象外データと確認できる根拠がある場合に限り、修正または除外を検討します。
欠損値についても、空欄のままにするのか、対象行を除外するのか、別の方法で補完するのかを分析前に決めておきましょう。
分散分析前の確認項目です。
・列ごとに比較対象のグループが分かれているか
・1行目のラベルを設定する予定か
・空白セルや文字列が混在していないか
・外れ値に説明できる理由があるか
データ整理の根拠を残しておくと、あとで分析結果を報告するときの説明が明確になります。
【操作のポイント】元データのシートを複製してから整形すると、修正前の記録を安全に残せます。
分析ツールの設定画面と出力結果
続いては、入力範囲を指定した後に表示される分散分析の出力内容を確認していきます。
| グループ | データ数 | 平均 | 分散 |
|---|---|---|---|
| 研修A | 4 | 72.75 | 4.92 |
| 研修B | 4 | 82.00 | 3.33 |
| 研修C | 4 | 77.50 | 1.67 |
ANOVA 一元配置の設定画面
それではまず、Excelの設定画面で確認すべき項目を見ていきます。
入力範囲、グループ化、ラベル、有意水準、出力先を設定したらOKをクリックします。
有意水準は、偶然による差と判断する確率の基準です。
一般的な業務分析や研究の初歩では0.05がよく使われます。
有意水準0.05は、偶然にこの程度の差が出る確率が5パーセント未満なら、統計的に有意な差と判断する基準です。
【操作のポイント】出力先の右側と下側には十分な空きセルを確保し、既存データを上書きしないようにしてください。
要約表の平均と分散
続いては、出力された要約表の見方を確認していきます。
Excelは最初に、各グループのデータ数、合計、平均、分散を一覧で表示します。
平均はグループごとの代表値であり、分散はそのグループ内にどの程度のばらつきがあるかを示す数値です。
平均値が大きく異なっていても、各グループ内のばらつきが非常に大きければ、統計的に有意な差といえない場合があります。
反対に、平均差が比較的小さくても、グループ内のばらつきが小さければ有意差が出ることがあります。
平均との差とばらつきの関係です。
グループ間の差が大きいほど、有意差は見つかりやすくなります。
グループ内のばらつきが小さいほど、平均差は明確に見えやすくなります。
標本数が少なすぎる場合は、差があっても検出しにくくなります。
要約表は結論そのものではありませんが、ANOVA表の数値を理解する土台になります。
【操作のポイント】平均だけで判断せず、各グループの分散とデータ数も併せて確認しましょう。
ANOVA表のF値とP値
続いては、検定の中心となるANOVA表の項目を確認していきます。
ANOVA表には、変動、平方和、自由度、平均平方、F値、P値、F境界値などが表示されます。
特に判断で使うことが多いのは、F値、P値、有意水準、F境界値です。
F値は、グループ間のばらつきをグループ内のばらつきで割った指標です。
F値 ＝ グループ間の平均平方 ÷ グループ内の平均平方
F値が大きいほど、グループ間の平均差が相対的に大きい可能性があります。
P値は、各グループの母平均に本当は差がないと仮定したとき、観測された差以上の結果が偶然に得られる確率を表します。
P値が有意水準0.05より小さい場合、少なくとも一つのグループの平均がほかと異なると判断します。
【操作のポイント】P値が0.05未満でも、どのグループ同士に差があるかまでは一元配置分散分析だけでは確定しません。
分散分析の結果を判断する基準
続いては、P値とF値を使って一元配置分散分析の結果を判断する考え方を解説していきます。
| 判定項目 | 例 | 読み方 |
|---|---|---|
| P値 | 0.012 | 0.05未満なので有意差あり |
| F値 | 8.45 | F境界値との比較に使う |
| F境界値 | 4.26 | F値が上回れば有意差あり |
P値と有意水準の比較
それではまず、最もよく使われるP値による判定を確認していきます。
有意水準を0.05に設定した場合、P値が0.05未満なら帰無仮説を棄却します。
帰無仮説とは、比較するグループの母平均に差はないという仮定です。
P値が0.012なら0.05より小さいため、平均値に統計的な差があると判断できます。
一方でP値が0.08なら、0.05より大きいため、今回のデータだけでは平均差があると結論づけられません。
P値が0.05以上であっても、平均が完全に同じと証明されたわけではありません。
【操作のポイント】報告書には「有意差がない」ではなく、「有意な差は確認できなかった」と書くと正確です。
F値とF境界値の比較
続いては、F値とF境界値を用いる判定方法を確認していきます。
ExcelのANOVA表では、F値とF境界値も確認できます。
F値がF境界値より大きければ、設定した有意水準で有意差があると判断します。
たとえばF値が8.45でF境界値が4.26なら、8.45のほうが大きいため有意差ありという結論です。
この判定は、P値が有意水準より小さいという判定と同じ結論になるように計算されています。
判定の対応関係です。
P値 ＜ 0.05 有意差あり
F値 ＞ F境界値 有意差あり
P値とF値のどちらを使っても、同じ検定結果を確認できます。
初心者が結果を読む場合は、P値と有意水準の比較から始めると理解しやすいでしょう。
【操作のポイント】有意水準を0.01に変えた場合は、F境界値も変化するため、以前の数値を使い回さないでください。
有意差が出た後の追加分析
続いては、有意差が出た後に確認したい追加分析について解説していきます。
一元配置分散分析で有意差が確認できた場合、それは少なくとも一組の平均に差があることを示します。
ただし、研修Aと研修B、研修Bと研修C、研修Aと研修Cのどの組み合わせに差があるかまでは分かりません。
組み合わせごとの差を調べるには、多重比較を行います。
代表的な方法にはTukey法、Bonferroni法、Games-Howell法などがあります。
Excel標準の分析ツールだけでは多重比較が十分に行えないこともあるため、必要に応じて統計ソフトやアドインを使う選択肢があります。
複数回のt検定をそのまま繰り返すと、偶然に有意差が出る確率が高くなるため注意が必要です。
【操作のポイント】分散分析で有意差が出たら、平均値の大小だけで結論を急がず、どのペアに差があるかを追加確認しましょう。
一元配置分散分析で注意したい前提条件
続いては、分散分析の結果を適切に解釈するための前提条件を確認していきます。
| 確認項目 | 内容 |
|---|---|
| 独立性 | 各データが互いに独立していること |
| 正規性 | 各群の分布が極端に偏っていないこと |
| 等分散性 | 各群のばらつきが極端に異ならないこと |
観測値の独立性
それではまず、分散分析の基本となる独立性について解説していきます。
独立性とは、一つのデータが別のデータへ直接影響していない状態を意味します。
たとえば同じ人を研修前と研修後で測定したデータは、同一人物から得た値なので独立とはいえません。
このような対応のあるデータには、対応のあるt検定や反復測定分散分析など、別の手法が適する場合があります。
一方、別々の受講者を研修A、研修B、研修Cへ割り当てて得た得点なら、独立したデータとして扱いやすくなります。
【操作のポイント】同じ対象を複数回測定したデータかどうかを、分析前に必ず確認しましょう。
正規性とデータ分布
続いては、正規性とデータの分布について確認していきます。
一元配置分散分析では、各グループの母集団がおおむね正規分布に従うことが前提の一つです。
実務では完全な正規分布である必要はありませんが、極端な歪みや外れ値が多い場合は注意が必要です。
Excelではヒストグラムを作成したり、箱ひげ図を使ったりすると、データの偏りを視覚的に確認できます。
標本数が小さい場合ほど、分布の形や外れ値の影響を受けやすくなります。
データ分布を確認する目的です。
・極端な外れ値がないかを見るため
・天井効果や床効果が起きていないかを見るため
・平均値だけでは分からないばらつきを確認するため
分析結果をそのまま採用する前に、表とグラフの両方で元データを確認する姿勢が大切です。
【操作のポイント】ヒストグラムは挿入タブから統計グラフを選ぶと、分布の偏りを確認しやすくなります。
等分散性と検定方法の選択
続いては、グループ間の分散が近いかどうかを確認していきます。
等分散性とは、各グループの分散が大きく異ならないという前提です。
要約表の分散を比較して、一部のグループだけが極端に大きい場合は注意が必要になります。
分散の差が大きく、標本数もグループ間で大きく異なると、通常の一元配置分散分析の結果が安定しないことがあります。
そのような場合には、Welchの分散分析やノンパラメトリック検定であるKruskal-Wallis検定を検討する場面もあります。
分析ツールで出力できる結果だけでなく、データの性質に合った検定かどうかを考えることが重要です。
【操作のポイント】分散の差が大きいと感じたら、結論を急がず、統計に詳しい担当者へ相談する方法も有効です。
まとめ エクセルで分散分析を行う方法と分析ツールによる一元配置の検定手順
エクセルで一元配置分散分析を行うときは、まず分析ツールを有効にし、比較するグループを列ごとに整理します。
1行目に見出しを置く場合は、ANOVA 一元配置の設定画面で先頭行をラベルとして使用にチェックを入れましょう。
入力範囲、グループ化、有意水準、出力先を指定して実行すると、要約表とANOVA表が作成されます。
P値が有意水準0.05より小さい場合は、グループ間の平均に統計的な差があると判断できます。
一方で、有意差が出たとしても、どのグループ同士に差があるかを確認するには多重比較が必要です。
また、独立性、正規性、等分散性といった前提条件にも注意してください。
平均値だけを比べるのではなく、データ数、分散、P値、分析目的を合わせて確認することが、Excelで分散分析を活用するコツです。
