Excelでt検定を行い、2つのグループに差があるかを確かめたいときに重要になる数値がp値です。
p値は結果を単純に比較するための数値ではなく、観測された差が偶然に生じる可能性を判断するための指標です。
関数を使う方法と分析ツールを使う方法を理解しておくと、売上比較、実験結果、アンケート集計、品質管理など、さまざまなデータ分析に応用できます。
p値の確認で押さえたいポイントです。
・一般的にはp値が0.05未満なら有意差ありと判断します。
・対応のあるデータか、独立したデータかで使う関数が変わります。
・p値だけで結論を急がず、平均値、標本数、データの内容も確認します。
この記事では、Excelでp値を求める手順と、t検定による有意差の確認方法を順番に解説していきます。
Excelでp値を求める方法1【T.TEST関数によるt検定】
| A | B | C |
|---|---|---|
| 1 | 施策A | 施策B |
| 2 | 72 | 79 |
| 3 | 68 | 83 |
| 4 | 75 | 80 |
| 5 | 70 | 85 |
それではまず、関数だけでp値を返せるT.TEST関数について解説していきます。
T.TEST関数の基本構文
T.TEST関数は、2組の数値データを比較し、平均との差が偶然に起きたと考えられる確率をp値として返す関数です。
関数の構文は、T.TEST(配列1,配列2,尾部,検定の種類)です。
=T.TEST(B2:B11,C2:C11,2,3)
1つ目の配列には施策Aのデータ範囲、2つ目の配列には施策Bのデータ範囲を指定します。
3つ目の引数である尾部には、通常は両側検定を示す2を入力します。
4つ目の引数である検定の種類には、対応関係と分散の考え方に応じた数値を入力します。
セルB2からB11、C2からC11のように、1行目の見出しを含めず、数値だけの範囲を指定することが基本です。
両側検定と片側検定の選び方
尾部の指定は、両側検定なら2、片側検定なら1です。
両側検定は、施策Aと施策Bの平均が異なるかを確認したいときに使います。
たとえば、新しい広告案の反応率が従来案より高いか低いかを限定せず、違いがあるかを知りたい場面では両側検定が適しています。
片側検定は、施策Bの平均が施策Aより高いかというように、事前に検証したい方向が明確な場合に使います。
実務で迷った場合は、まず両側検定の2を使う考え方が無難です。
結果を見た後で都合のよい片側検定へ変更すると、判断が偏るおそれがあります。
片側検定は仮説を立てた時点で使用を決めるという流れを意識しましょう。
関数を入力してp値を表示する手順
結果を表示したい空白セル、たとえばE2をクリックします。
数式バーに=T.TEST(B2:B11,C2:C11,2,3)と入力し、Enterキーを押します。
表示された小数がp値です。
たとえば0.032と表示された場合、偶然だけで同程度以上の差が出る確率は約3.2パーセントと読み取れます。
p値はパーセント表示にしてもかまいませんが、0.05との比較を行うなら小数表示のまま確認すると分かりやすいでしょう。
【操作のポイント】T.TEST関数の結果セルは、平均値や判定結果のセルとは分けて配置すると、分析表を見直しやすくなります。
対応のあるt検定と対応のないt検定の判別
| A | B | C |
|---|---|---|
| 1 | 改善前 | 改善後 |
| 2 | 58 | 66 |
| 3 | 61 | 68 |
| 4 | 55 | 59 |
続いては、検定の種類を選ぶために必要なデータの対応関係を確認していきます。
同じ対象を比較する対応のあるデータ
対応のあるデータとは、同一人物、同一商品、同一設備などを2回測定したように、行ごとの値が結び付いているデータです。
たとえば研修前と研修後で同じ10人のテスト得点を比較する場合、A列の2行目とB列の2行目は同じ人の結果です。
このような場合は、T.TEST関数の4つ目の引数に1を指定します。
=T.TEST(B2:B11,C2:C11,2,1)
対応のあるt検定では、各行の差に注目して分析します。
片方の列だけを並べ替えると対応関係が壊れ、検定結果も正しくなくなります。
別々の対象を比較する対応のないデータ
対応のないデータとは、施策Aを見た人と施策Bを見た人が別々で、行ごとにペアが存在しないデータです。
たとえばA店舗の来店客50人とB店舗の来店客50人の購入額を比べる場合は、通常、対応のないt検定を使います。
この場合は、等分散を仮定するなら2、等分散を仮定しないなら3を指定します。
分散が同じと明確に判断できない場面では、Welchのt検定に相当する3を選ぶ方法が実務でよく用いられます。
検定の種類を決める確認事項
関数を入力する前に、各行が同じ対象を示しているかを確認します。
同一対象の前後比較なら1、別グループの比較なら2または3という整理です。
さらに、標本数が大きく違う場合や、ばらつきの大きさが明らかに違う場合は、等分散を仮定しない3が選択肢になります。
検定の種類1は対応のあるt検定です。
検定の種類2は等分散を仮定した対応のないt検定です。
検定の種類3は等分散を仮定しない対応のないt検定です。
【操作のポイント】データの見た目ではなく、各行が同じ対象に対応しているかという収集方法で検定の種類を判断します。
p値と有意差の判定基準
| A | B | C |
|---|---|---|
| 1 | p値 | 判定 |
| 2 | 0.032 | 有意差あり |
| 3 | 0.128 | 有意差なし |
続いては、算出したp値から有意差をどのように判断するかを確認していきます。
有意水準0.05との比較
有意水準は、偶然の差を差があると誤って判断するリスクを、どこまで許容するかを示す基準です。
多くの分析では有意水準を0.05に設定します。
p値が0.05未満なら統計的に有意差あり、p値が0.05以上なら統計的に有意差なしと表現するのが基本です。
=IF(E2<0.05,”有意差あり”,”有意差なし”)
上記の式では、E2セルにp値が入っている前提で、自動的に判定文を表示できます。
ただし、0.05は絶対的な境界線ではなく、分野や目的により0.01や0.10を使う場合もあります。
p値が示す意味
p値が0.03だった場合、差がないという前提のもとで、今回と同程度以上の差が偶然に出る確率は約3パーセントという意味になります。
p値が小さいほど、偶然だけで説明することが難しくなります。
一方で、p値は効果の大きさそのものを表す数値ではありません。
p値が非常に小さくても、標本数が多ければ実務上は小さな差が有意になることがあります。
逆に標本数が少ないと、大きく見える差でも有意差なしになるかもしれません。
有意差なしの読み方
p値が0.05以上だった場合、2つの平均に差が絶対にないと証明されたわけではありません。
今回集めたデータからは、偶然ではない差があると十分に言い切れなかったという意味です。
データ数が不足している、ばらつきが大きい、測定条件が不安定であるといった理由も考えられます。
有意差ありは、偶然とは考えにくい差が確認できたという判断です。
有意差なしは、差がないと断定する結果ではありません。
【操作のポイント】p値の判定と同時に、平均値の差が業務上どれほど重要かも確認しましょう。
分析ツールによるt検定の実行手順
| A | B | C |
|---|---|---|
| 1 | グループA | グループB |
| 2 | 41 | 47 |
| 3 | 44 | 51 |
| 4 | 39 | 48 |
続いては、Excelの分析ツールを利用してt検定の表を出力する手順を確認していきます。
分析ツールの表示設定
Excelのリボンにデータ分析が表示されていない場合は、最初にアドインを有効にします。
ファイルタブからオプションを開き、アドインを選択します。
画面下部の管理でExcelアドインを選び、設定をクリックします。
分析ツールにチェックを入れてOKを押すと、データタブの右側にデータ分析が表示されます。
t検定メニューと入力範囲
データタブのデータ分析をクリックすると、分析ツールの一覧が開きます。
対応のあるデータならt検定、対応のある2標本による平均の検定を選びます。
独立した2グループで分散が等しいとみなすなら、等分散を仮定した2標本による検定を選びます。
分散が異なる可能性を考慮するなら、等分散を仮定しない2標本による検定を選択します。
変数1の入力範囲にはB1:B11、変数2の入力範囲にはC1:C11を指定します。
1行目を含めた場合は、ラベルにチェックを入れます。
出力表のp値の読み取り
分析結果には平均、分散、観測数、自由度、t値、P値などが表形式で出力されます。
両側検定を選んだ場合に特に確認する項目は、P(T<=t)両側です。
この両側p値を有意水準と比較することで、平均差についての判定ができます。
P(T<=t)両側が0.018で、有意水準が0.05なら、0.018<0.05となるため有意差ありと判断します。
分析ツールは途中の計算値も確認できるため、報告書や検証資料として根拠を残したい場面に便利です。
【操作のポイント】分析ツールの出力先は、新規ワークシートを選ぶと元データを崩さずに確認できます。
p値の検証で確認したい注意点
| A | B | C |
|---|---|---|
| 1 | 確認項目 | 内容 |
| 2 | 空白セル | 範囲内の空欄を確認 |
| 3 | 外れ値 | 極端な値の理由を確認 |
続いては、p値を正しく活用するために確認したいデータと解釈の注意点を解説していきます。
空白セルと文字列の混在
計算範囲に空白セル、エラー値、文字列が混ざっていると、想定外の結果になる場合があります。
データを入力する前に、数値として認識されているかを確認しましょう。
数値が左寄せで表示されている場合は、文字列として保存されている可能性があります。
見出し行を含める場合と含めない場合を混同しないことも大切です。
関数では原則として見出しを除外し、分析ツールではラベル設定と範囲指定を一致させます。
外れ値と平均値の確認
p値だけを見る前に、AVERAGE関数で各グループの平均値を確認します。
=AVERAGE(B2:B11)
=AVERAGE(C2:C11)
平均値の差がどの程度あるのかを示すことで、統計的な差と実務上の意味を分けて考えられます。
また、極端に大きい値や小さい値が1件だけ存在すると、平均やp値に影響することがあります。
外れ値を安易に削除するのではなく、入力ミス、測定ミス、特殊な条件の発生など、理由を確認する姿勢が必要です。
複数回の検定と解釈
多くの項目を同時に検定すると、偶然にp値が0.05未満になる項目が現れやすくなります。
たとえば20項目をそれぞれ有意水準0.05で確認すると、差がなくても有意に見える結果が含まれる可能性があります。
検定回数が多い分析では、事前の仮説と比較項目を整理することが重要です。
有意差ありという判定を、因果関係の証明と同じ意味で使わないようにも注意しましょう。
【操作のポイント】p値、平均値、標本数、データ収集条件を同じ表で確認できるようにすると、誤った解釈を防ぎやすくなります。
まとめ エクセルでp値を求める方法とt検定の有意差確認
Excelでp値を求めるときは、T.TEST関数またはデータ分析ツールを使う方法があります。
T.TEST関数では、配列、尾部、検定の種類を正しく指定することが重要です。
同じ対象の前後比較では対応のあるt検定を選び、別々のグループを比較する場合は対応のないt検定を選択します。
一般的にはp値が0.05未満なら有意差ありと判断しますが、p値だけで効果の大きさや施策の価値まで決められるわけではありません。
平均値、標本数、ばらつき、外れ値、データを集めた条件もあわせて確認しましょう。
関数で手早くp値を出したい場合はT.TEST関数、計算過程を含む表を作成したい場合は分析ツールが便利です。
検定の目的とデータの対応関係を先に整理することが、Excelで正確な有意差判定を行う近道です。
