【Excel】エクセルで分散比を求める方法と計算の基本（分散分析F値）

エクセルで複数グループの数値を比較していると、平均値の差が偶然のばらつきなのか、それとも意味のある差なのかを判断したい場面があります。

このときに役立つ考え方が分散比であり、分散分析ではF値として表示されます。

分散比はグループ間のばらつきとグループ内のばらつきを比較する数値で、Excelの分析ツールや関数を使えば、統計ソフトを使わずに確認できます。

ここでは、1行目に見出しがある表を使い、分散比の意味、計算式、Excelでの分散分析の実行方法、結果の読み方までを順に解説します。

なお、同じ分散比という言葉でも、二つの分散を比較するF検定と、三つ以上のグループを比較する分散分析では、目的と表の読み方が少し異なります。

 

エクセルで分散分析F値を求める方法

それではまず、エクセルで分散分析のF値を求める操作について解説していきます。

グループA	グループB	グループC
72	81	91
75	79	88
70	84	93
74	82	90
69	80	94

上のように、比較したい各グループを列ごとに配置しておくと、Excelの分析ツールを使って一元配置分散分析を実行できます。

一元配置分散分析は、一つの要因によって分けた三群以上の平均値を比較する代表的な手法です。

 

分析ツールの有効化

分析ツールがリボンに表示されていない場合は、最初にアドインを有効にします。

ファイルタブからオプションを開き、左側のメニューでアドインを選択します。

画面下部の管理でExcelアドインを選び、設定をクリックしましょう。

表示された一覧で分析ツールにチェックを入れ、OKを選択すると、データタブの右側にデータ分析が表示されます。

分析ツールは分散分析だけでなく、回帰分析、ヒストグラム、移動平均などにも使える便利な機能です。

 

一元配置分散分析の実行

データタブのデータ分析をクリックし、一覧から分散分析 一元配置を選択します。

入力範囲には、見出しを含めたA1からC6のような範囲を指定します。

先頭行をラベルとして使用にチェックを入れると、結果表にグループAやグループBといった列見出しが反映されます。

グループ化は列を選び、有意水準は通常0.05のままで問題ありません。

出力先は新規ワークシートを選ぶと、元データを残したまま結果を確認しやすくなります。

OKをクリックすると、概要表と分散分析表が作成され、その中のF列に分散比であるF値が表示されます。

横方向にグループを並べる場合は列を選び、縦方向に並べる場合は行を選ぶことが重要です。

 

F値とP値の確認

出力された分散分析表では、行にグループ間、グループ内、合計が表示されます。

グループ間の行にあるF値が、各グループの平均の違いを検討するための分散比です。

P値が有意水準より小さい場合は、少なくとも一組のグループ平均に差があると判断します。

たとえばP値が0.03で有意水準が0.05なら、0.03は0.05より小さいため、有意差ありと読む流れです。

一方、P値が0.20なら、今回のデータだけでは平均値に統計的な差があるとは言い切れません。

F値が大きくても、標本数や自由度によって判断が変わるため、P値またはF境界値と必ず組み合わせます。

 

分散比と分散分析F値の意味

続いては、分散比と分散分析F値が何を示す数値なのかを確認していきます。

比較対象	ばらつきの内容	F値への影響
グループ間	各グループ平均の差	大きいほどF値は大きくなりやすい
グループ内	同じグループ内の個人差や誤差	大きいほどF値は小さくなりやすい
F値	二つのばらつきの比	平均差の検討材料

分散とは、データが平均値からどの程度広がっているかを二乗して表す指標です。

分散比は二つの分散を割り算で比較するため、単位が消え、異なるばらつきの大きさを相対的に確認できます。

 

グループ間変動

グループ間変動は、グループごとの平均値が全体平均からどれほど離れているかを表します。

たとえば三つの学習方法でテスト結果を比べ、平均点が70点、81点、91点なら、グループ間のばらつきは比較的大きくなります。

各グループの平均が近い場合には、グループ間変動は小さくなります。

グループ間変動が大きいことは、要因による平均差が存在する可能性を示す材料です。

ただし、平均値だけを見て結論を急ぐことはできません。

同じグループに属するデータの散らばりも大きければ、平均値の差は偶然に生じた可能性が残るためです。

 

グループ内変動

グループ内変動は、同じグループ内にある個々の数値のばらつきです。

学習方法が同じでも、元の学力、勉強時間、問題との相性などによって得点は変わります。

このような要因による個人差や測定誤差を、分散分析ではグループ内の変動として扱います。

グループ内変動が小さく、各グループの平均が明確に離れているなら、F値は大きくなりやすい傾向です。

反対に、グループ内の値が大きく散らばっていれば、平均に差があってもF値は小さくなることがあります。

 

F値による比較

F値は、分散分析ではグループ間平均平方をグループ内平均平方で割った値です。

分子にあるグループ間平均平方は平均値の差に関するばらつきを、分母にあるグループ内平均平方は各グループ内部のばらつきを表します。

F値が1に近い場合は、グループ間とグループ内のばらつきに大きな差がない状態と考えられます。

F値が1よりかなり大きい場合は、グループ内の偶然のばらつきだけでは説明しにくい平均差があるかもしれません。

ただし、F値に絶対的な合格ラインはありません。

自由度と有意水準によってF境界値が変わるため、出力表のP値とF境界を確認しましょう。

 

分散比の計算式とExcel関数

続いては、分散比を数式とExcel関数で計算する基本を確認していきます。

A列 グループA	B列 グループB	C列 計算結果
72	81	VAR.Sの結果を比較
75	79	F値は大きい分散÷小さい分散
70	84	データは2行目から開始
74	82	見出しは1行目
69	80	標本分散を使用

二つのデータ群の散らばりを比較するだけなら、分散を求めてから大きい方を小さい方で割る方法が分かりやすいでしょう。

三群以上の平均値の差を検討する場合は、手計算よりも分析ツールの分散分析 一元配置を使う方が実務的です。

 

標本分散を求めるVAR.S関数

調査対象が母集団の一部である場合は、標本分散を求めるVAR.S関数を使います。

この数式では、A1の見出しを除き、A2からA6までのグループAの数値を対象にします。

B列のグループBなら、=VAR.S(B2:B6)と入力します。

通常のアンケート結果や実験データでは、母分散ではなく標本分散を使うケースが多いでしょう。

VAR.Sはデータ数から1を引いた自由度で補正するため、標本から母集団のばらつきを推定する目的に合います。

関数の引数には連続する範囲だけでなく、複数のセルや数値を指定することもできますが、比較では同じ条件の範囲を使うことが大切です。

 

母分散を求めるVAR.P関数

対象となる全件データがそろっている場合は、VAR.P関数で母分散を求めます。

たとえば、ある部署に所属する全社員のデータを漏れなく集計し、その部署だけのばらつきを記述したい場合にはVAR.Pが候補になります。

VAR.SとVAR.Pは似ていますが、分母の扱いが異なるため、同じデータでも結果は一致しません。

比較する複数グループでは、片方だけVAR.S、もう片方だけVAR.Pという混在を避けましょう。

分析の目的に合わせて分散の種類を統一することが、正しい分散比の前提です。

迷うときは、標本データとして扱うVAR.Sを選ぶ考え方が一般的です。

 

大きい分散を分子にする計算

二つの分散の比を求めるときは、一般に大きい方の分散を分子、小さい方を分母に置きます。

この式は、二つの標本分散を計算し、大きい値を小さい値で割るため、結果は必ず1以上になります。

セルに分散を別々に求めた場合は、たとえばD2にグループAの分散、E2にグループBの分散を置き、=MAX(D2:E2)/MIN(D2:E2)としても構いません。

ただし、この比だけでは統計的な有意差を判定できません。

二群の分散差を検定したい場合はF.TEST関数、三群以上の平均差を調べたい場合は分散分析を使い分けます。

 

F.TEST関数による二群の分散比較

続いては、二つのグループの分散が等しいとみなせるかを確認するF.TEST関数について解説していきます。

A列 商品Aの測定値	B列 商品Bの測定値	C列 判定用
100	96	F.TESTのP値
102	105	有意水準0.05と比較
99	93	二群の比較
101	108	見出しは1行目
98	94	データは2行目から

F.TEST関数は、二つの配列の分散に差があるかどうかについて、両側確率を返す関数です。

分散分析のF値そのものを返す関数ではなく、二つの分散を検定するためのP値を返す点に注意しましょう。

 

F.TEST関数の入力方法

空いているセルを選び、次のように入力します。

数式を確定すると、商品Aと商品Bの分散が等しいという仮説に対するP値が返されます。

配列1と配列2には、数値が入った範囲だけを指定します。

見出しが文字列として含まれていても関数は数値だけを扱いますが、入力範囲を明確にするため、A2:A6のようにデータ部分だけを指定する習慣がおすすめです。

空白セルやエラー値が多いと、意図した比較にならないことがあります。

比較前に、各列のデータ件数と入力ミスを確認しておくと安心です。

 

Excel画面での数式入力

数式バーにF.TEST関数を入力し、Enterキーで確定すると、選択セルにP値が表示されます。

画面例ではC6を操作対象として赤枠で示していますが、実際には結果を表示したい任意の空白セルを選べます。

数式の参照範囲を変更する場合は、数式バー内のA2:A6やB2:B6をドラッグで選び直すことも可能です。

 

P値と等分散の判断

F.TESTの結果が0.05より小さい場合、有意水準5パーセントでは二群の分散が等しいとは考えにくいと判断します。

結果が0.05以上なら、分散が異なるという十分な証拠は得られなかったと読むのが基本です。

P値が大きいことは分散が完全に同じという証明ではなく、差があると判断する根拠が弱いという意味です。

この違いは、統計結果を説明するときに大切なポイントです。

平均値を比較するt検定などでは、等分散を仮定するかどうかが分析方法の選択に関わる場合があります。

F.TESTの結果は、その前提を検討する補助情報として活用できます。

 

分散分析表の読み方と有意差判定

続いては、Excelが出力する分散分析表の項目と有意差の判定方法を確認していきます。

変動要因	自由度	変動	分散	観測された分散比	P値
グループ間	2	1120.5	560.25	24.68	0.0003
グループ内	12	272.4	22.70
合計	14	1392.9

Excelの表では、観測された分散比の欄がF値に相当します。

表記はExcelのバージョンや表示言語で異なることがありますが、F、観測された分散比、P値、F境界という項目を探すと判断しやすくなります。

 

自由度の役割

自由度は、計算に使える独立した情報量を示す数値です。

一元配置分散分析では、グループ間の自由度はグループ数から1を引いた値になります。

グループが三つなら、グループ間の自由度は2です。

グループ内の自由度は、全データ数からグループ数を引いて求めます。

各グループが5件ずつで三群なら全データ数は15件なので、グループ内の自由度は12になります。

自由度はF境界値の計算に使われるため、データ件数やグループ数を変えると、同じF値でも判定が変わることがあります。

 

平均平方とF値の関係

変動の欄は、各データが平均からどれだけ離れているかを合計した量です。

分散分析では、この変動を自由度で割った平均平方を使います。

例では560.25を22.70で割ることで、約24.68というF値になります。

グループ間平均平方がグループ内平均平方より大きいほど、F値は大きくなります。

この構造を理解すると、Excelの結果表が単なる数値の一覧ではなく、平均差と誤差を比較した表だと分かるでしょう。

 

有意水準とF境界値

有意水準は、偶然の差を有意差と誤って判断する確率の基準です。

よく使われる0.05は、5パーセントの有意水準を意味します。

Excelの分散分析表では、F値がF境界より大きい場合、有意水準の条件で有意差ありと判断できます。

また、P値が有意水準より小さいかを確認しても、同じ結論に到達します。

P値とF境界値は別の表示方法ですが、同じ仮説検定の判定を補助する情報です。

有意差が出た場合でも、どのグループ同士に差があるかまでは一元配置分散分析だけで特定できません。

必要に応じて多重比較や追加の検定を行い、平均値の差を詳しく確認しましょう。

 

分散比計算で起こりやすいエラーと注意点

続いては、エクセルで分散比やF値を扱うときに起こりやすいミスと注意点を確認していきます。

確認項目	よくある状態	確認方法
入力範囲	見出しや別データが混在	範囲選択を見直す
データ数	群ごとに極端に少ない	件数を確認する
外れ値	入力誤りが混じる	元データを点検する
前提条件	独立性が満たされない	収集方法を確認する

関数や分析ツールの操作が正しくても、元データや分析目的が合っていなければ、解釈を誤る可能性があります。

 

空白セルと文字列の混在

入力範囲に空白セルや文字列、エラー値が混ざっていると、計算対象やデータ数が想定とずれることがあります。

特にコピーしたデータには、見た目では分からない空白文字が含まれている場合があります。

数値として扱われているかを確認するには、セルの表示形式だけでなく、数式バーの内容も確認しましょう。

エラー値がある状態でVAR.SやF.TESTを実行すると、結果がエラーになることがあります。

不要な文字列を削除し、欠損値をどのように扱うかをあらかじめ決めることが大切です。

 

少なすぎるデータ数

各グループのデータ数が少なすぎると、ばらつきの推定が不安定になります。

一つの値しかないグループでは、標本分散を求められません。

二つしかないデータでも計算は可能ですが、偶然の影響を強く受けやすくなります。

統計的な有意差が出なかった場合は、差がないと断定する前に、データ数が十分かを検討する必要があります。

検証の目的や実務上の制約に合わせて、必要なサンプル数を計画する視点も欠かせません。

数値の量だけでなく、各グループの件数が著しく偏っていないかも確認しましょう。

 

有意差と実務上の差

統計的に有意であることと、実務で重要な差があることは同じではありません。

データ数が非常に多い場合には、ごく小さな平均差でもP値が小さくなることがあります。

逆に、実務では意味のある差が見えていても、データ数不足によって有意差が出ないこともあります。

F値とP値の判定に加え、平均値の差、ばらつき、業務上の基準を合わせて評価することが重要です。

たとえば品質管理なら、統計上の差だけではなく、許容範囲や不良率への影響を確認します。

販売施策なら、平均売上の差が施策コストを上回るかという観点も必要になるでしょう。

 

まとめ エクセルで分散分析F値と分散比を求める方法

エクセルで分散比を確認する場合は、まず比較したい目的が二群の分散比較なのか、三群以上の平均値比較なのかを整理しましょう。

二つのデータ群の分散を比較したい場合は、VAR.S関数などで分散を求め、大きい分散を小さい分散で割ることで分散比を算出できます。

さらに分散が等しいかを検討したいときは、=F.TEST(配列1,配列2)でP値を確認します。

三つ以上のグループの平均値を比較したい場合は、データタブのデータ分析から分散分析 一元配置を選ぶ方法が便利です。

分散分析表では、グループ間の行にあるF値、P値、F境界値を確認することが基本です。

P値が有意水準より小さい、またはF値がF境界より大きい場合には、グループ平均に統計的な差があると判断します。

ただし、有意差が出たとしても、どの組み合わせに差があるのか、実務上どの程度の意味があるのかは別途確認が必要です。

データ範囲、見出し行、空白セル、分散関数の種類を丁寧に確認し、F値を数字だけで終わらせず、データの背景とあわせて活用していきましょう。