Excelでカイ2乗検定をする方法！アンケートのクロス集計からp値の見方まで

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

アンケートの結果は、性別と賛否のように2つの項目が関係しているかを知りたい場面でよく使います。
今回は
・回答をCOUNTIFSでクロス集計し、CHISQ.TEST関数でp値を求めて「有意差」を判定する方法
をゴールにします。
p値が0.05より小さければ「2つの項目には関係がありそうだ」と判断できるようになります。

上の画像が完成形です。性別×賛否のクロス集計表・期待度数・p値・判定までを1枚のシートにまとめています。

なお、期待度数の求め方や、うまくいかないときの対処法（トラブルシュート）、VBAで一括集計する方法もあわせてご紹介します。
早速実装して試してみましょう！

STEP1. アンケートの回答を用意する

まずはカイ2乗検定の元になるアンケートの回答を用意します。
今回は「性別」と「賛否」の2項目で、30人分の回答をまとめました。
A列が回答No、B列が性別、C列が賛否です。
1行目が見出し、2行目以降がデータになります。
この回答をもとに、性別と賛否に関係があるかどうかを調べていきましょう。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. COUNTIFSで性別×賛否のクロス集計表を作る

まず、回答を「性別」×「賛否」の件数にまとめたクロス集計表を作ります。
表の右側の空いている場所に、見出しを入力します。
E1セルに「性別」、F1セルに「賛成」、G1セルに「反対」、H1セルに「合計」 です。
次に E2セルに「男性」、E3セルに「女性」、E4セルに「合計」 と入力します。
続いて件数を数えます。
F2セルに次のCOUNTIFS関数を入力してください。

=COUNTIFS($B$2:$B$31,$E2,$C$2:$C$31,F$1)

これは「B列が男性、かつC列が賛成の件数」を数えています。
この数式をF2セルからH3セルまでコピーすると、4つのマスの件数が一気に出ます。
合計行（4行目）は =SUM(F2:F3) のように縦を合計します。

クロス集計の数式はどのセルに入れる？

実際の入力画面がこちらです。
セルを選んで、数式バーにそのまま入力します。
入力中は引数のヒントが出るので、順番を確認しながら進められます。
引数は「範囲と条件」のペアを繰り返すだけなので、カンタンですね。

STEP3. 期待度数を求める

カイ2乗検定では、実際の件数（実測度数）と期待度数を比べます。
期待度数とは「性別と賛否に関係がないと仮定したときに、ここに入るはずの件数」です。
期待度数は次の式で求められます。
期待度数 ＝ その行の合計 × その列の合計 ÷ 全体の合計

E6セルに「期待度数」、F6セルに「賛成」、G6セルに「反対」を入力します。
左側は E7セルに「男性」、E8セルに「女性」 です。
男性・賛成の期待度数をF7セルに入力します。

=$H2*F$4/$H$4

これは「男性の合計（H2）× 賛成の合計（F4）÷ 全体の合計（H4）」を計算しています。
この数式をF7セルからG8セルまでコピーすると、4つの期待度数がすべて求まります。
数値は比較しやすいように小数点第2位まで表示しておきましょう。

期待度数の数式はこう入れる

数式バーの様子がこちらです。
H2（行の合計）と F4（列の合計）は動かしたくないので、列・行の記号の前に $ を付けます。
H4（全体の合計）は $H$4 と両方固定しておくのがコツです。

STEP4. CHISQ.TEST関数でp値を求める

いよいよ検定です。
E10セルに見出し「検定結果」、E11セルに「p値」と入力します。
そしてF11セルに次の数式を入れてください。

=CHISQ.TEST(F2:G3,F7:G8)

CHISQ.TESTは、実測度数の範囲と期待度数の範囲を渡すと、p値を返します。
第1引数が実測度数（F2:G3）、第2引数が期待度数（F7:G8）です。
2つの範囲は行数・列数をそろえて指定するのがポイントです。

STEP5. p値を見て「有意差」を判定する

p値が出たら、あとは基準と比べるだけです。
p値は「性別と賛否に関係がない（偶然のばらつき）としたときに、今回のような偏りが起きる確率」を表します。
この確率が小さいほど「偶然では説明しにくい＝関係がありそうだ」と判断できます。
慣例的に、p値が0.05（5%）より小さければ「有意差あり」 とします。
判断の目安として、基準値と自由度も出しておきましょう。

E12セルに「基準値」、F12セルに次の数式を入れます。

=CHISQ.INV(0.95,1)

これは自由度1のカイ2乗分布で上側5%の点（3.841）を返します。
E13セルに「自由度」、F13セルに =(2-1)*(2-1) を入れてください。
2×2のクロス集計なので、自由度は1になります。
最後にE14セルに「判定」、F14セルに次のIF関数を入れます。

=IF(F11<0.05,"有意差あり","有意差なし")

p値が0.05未満なら「有意差あり」と表示されます。
今回の結果を表にまとめると、次のようになりました。

VBAでクロス集計と検定を一括で行う

毎回同じ分析をするなら、VBAで自動化するのがいちばんラクです。
Alt+F11でVBAエディターを開き、標準モジュールに次のコードを貼り付けます。

Sub Kai2Test()
    Dim ws As Worksheet, lastRow As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    ws.Range("E1:H1").Value = Array("性別", "賛成", "反対", "合計")
    ws.Range("E2").Value = "男性"
    ws.Range("E3").Value = "女性"
    ws.Range("E4").Value = "合計"
    ws.Range("F2:G3").Formula = _
        "=COUNTIFS($B$2:$B$" & lastRow & ",$E2,$C$2:$C$" & lastRow & ",F$1)"
    ws.Range("F4").Formula = "=SUM(F2:F3)"
    ws.Range("G4").Formula = "=SUM(G2:G3)"
    ws.Range("H2:H3").Formula = "=SUM(F2:G2)"
    ws.Range("H4").Formula = "=SUM(H2:H3)"
    ws.Range("F7").Formula = "=$H2*F$4/$H$4"
    ws.Range("F11").Formula = "=CHISQ.TEST(F2:G3,F7:G8)"
End Sub

実行すると、クロス集計と検定の数式がまとめて入ります。
ボタンに登録しておけば、クリック1つで分析できるようになります。

タカヒロ

COUNTIFSの範囲を $B$2:$B$31 と固定している点に注目してください。
回答が増えても範囲を自動で伸ばせるように、最終行は lastRow で求めています。
数式を1つずつ手で入れるのが面倒なときほど、VBAが効きますよ。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったようにいかないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

関数の説明

今回使う3つの関数の概要です。
どれも統計や集計でよく使うので、違いを押さえておきましょう。

CHISQ.TEST関数（カイスクエア・テスト）

=CHISQ.TEST(F2:G3, F7:G8)

CHISQ.INV関数（カイスクエア・インバース）

=CHISQ.INV(0.95, 1)

COUNTIFS関数（カウントイフス）

=COUNTIFS($B$2:$B$31, $E2, $C$2:$C$31, F$1)

完成

性別×賛否のクロス集計からp値まで、1枚のシートにまとまりました。
今回のp値は0.0253で、0.05を下回ったので「有意差あり」と判定できました。
思ったとおりに動きましたか？
集計のCOUNTIFSと検定のCHISQ.TESTを一度作っておけば、回答を追加したときも自動で再計算されます。
アンケート結果の読み取りが、ぐっとスムーズになりますね。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプルデータで、ぜひ一度試してみてください。
クロス集計とカイ2乗検定ができるようになると、アンケート結果の読み取りがぐっとラクになりますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。