業務改善と標準化を同時に実現:MS 365業務システム開発

カイ二乗検定の後の「残差分析」をエクセルでやる方法

    
カイ二乗検定の後の「残差分析」をエクセルでやる方法
\ この記事を共有 /
カイ二乗検定の後の「残差分析」をエクセルでやる方法

カイ二乗検定の実施後にその中の項目のどこに違いがあったかを統計的に知る方法が「残差分析」です。その残差分析をエクセルで実施する方法を図解しています。また学習用テンプレートをダウンロードしてご自分で実施してみて下さい。

(動画時間:9:19)

ダウンロード  ←これをクリックして「カイ二乗検定と残差分析」エクセルテンプレートをダウンロード出来ます。

カイ二乗検定の残差分析とは?

こんにちは、リーンシグマ、ブラックベルトのマイク根上です。
業務改善コンサルをしています。

今日はこの動画リクエストからです。

カイ二乗検定の残差分析の動画リクエスト

「カイ二乗検定の残差分析をエクセルで行う方法を教えていただければ幸いです。」

Hawgleさん、動画リクエストありがとうございました。
ちょうど統計学の動画を作りたいと思っていましたので良かったです。

カイ二乗検定の記事は以前配信して、
その検定では複数のデータ群の集計の間に
無視できない大きな違いがあるかどうか、
統計的に調べる事ができる事を学びました。
⇒「カイ二乗検定とは?エクセルでわかりやすく実演」

しかし、カイ二乗検定で有意な違いがあったと分かったとしても、
複数の項目のどこに違いがあるかは分かりません。

そこで今回のリクエストの
残差分析をすればそれが分かるのです。
今日はそれをエクセルでやる方法をお見せします。

通常僕のサイトでは極力数式を使わずに説明をしていますが、
残念ながらこの残差分析の関数や
分析ツールがエクセルに無いですので、
今回はいくつか数式が出てくる事をご了承下さい。

2×2のカイ二乗検定では残差分析をやっても意味がない

先のカイ二乗検定の記事では
「ハンバーガーショップで学ぶ楽しい統計学」のサイトの
シナリオとサンプルデータをお借りしました。
⇒「ハンバーガーショップで学ぶ楽しい統計学のサイトのカイ二乗検定のページ」

カイ二乗検定 サンプルデータ

ワクワクバーガーの店員さんがポテトの売上は良いんだけど
フライドチキンの売上が低いのではないかと心配していて、
ライバルのモグモグバーガーの売上のデータと共に
カイ二乗検定を実施しました。

結果は心配してた通りワクワクバーガーのチキンの売上の割合は
統計的に低い事が分かりました。

それでこの後、残差分析をしたいところですが、
このシナリオでは2店舗の2商品(2×2)のデータで、
ワクワクバーガーのチキンの売上が悪いなら、
ポテトの売上が良い事が確定し、
逆にモグモグバーガーのポテトの売上は悪くて、
チキンの売上が良い事が確定します。

つまり、2×2のカイ二乗検定では残差分析は使えません。
分析をしても良いのですがやる前から結果は分かっています。

ですので、2×3や3×2以上のカイ二乗検定の後に
この残差分析をやるのです。

今回はポテトとチキンに
ハンバーガーの売上データを加えてやってみます。

前回のカイ二乗検定のやり方をおさらいする

先ほどと同じサイトの実践編ページのデータをお借りします。
⇒「楽しい統計学のサイトのカイ二乗検定の実践編のページ」

まず普通にカイ二乗検定をやってみます。

今回は学習用のテンプレートを作りましたので、
それに則ってやっていきます。無料ですのでダウンロードしてみて下さい。
⇒「カイ二乗検定と残差分析」エクセルテンプレートのダウンロード

カイ二乗検定 各横周辺和の全体の中の割合

上図がその実測値のデータです。
まず上図セルJ4とJ5で
この各店舗合計、それを横周辺和(セルI4とI5)と言いますが、
それの全体(セルI6)との割合を先に求めます。

ちなみにセルをダブルクリックすると上図の様に、
その場で数式が見えるし、参照しているセルに色が付くので便利です。

カイ二乗検定 期待値の計算

セルF12で期待値を求めるのにその各割合(J4)と
各メニューの合計、これを縦周辺和(セルF6)と言いますが、
それらを掛けて期待値を完成させます。
この計算方法は前回の動画で詳しくやりました。
⇒「カイ二乗検定とは?エクセルでわかりやすく実演」

期待値の表が完成したら、
CHISQ.TEST関数で、実測値範囲と期待値範囲を選べば、
カイ二乗検定のP値が計算できます。

CHISQ.TEST関数

結果は0.71%と出ました。
1%の有意水準でも「違いが無い」と言う帰無仮説を棄却できます
ので、
かなりの違いがありました。

しかし、今回は2x3のデータですので、
その中のどのメニューに大きな違いがあったのかは分かりません。
ですので、ここで残差分析をするのです。

カイ二乗検定の残差分析のやり方

まず、残差とは何でしょう?

漢字からは「残りの差」ですが、
カイ二乗検定での残差分析の中では
各「実測値と期待値の差」を比べて分析すると言う事です。

それをステップバイステップでやってみましょう。

「残差」と「標準化残差」を求める

各データの「実測値ー期待値」で全ての残差を求めます。(下図参照)
この残差の結果を見たらハンバーガーの残差が一番大きいですね。
しかしその売上自体も大きいですので何とも言えません。

残差の計算式

そこで、この残差を標準化する必要があります。
それを標準化残差と言い、
残差÷期待値の平方根で求められます。(下図参照)

標準化残差の計算式

平方根は英語でSquare Rootですので
エクセルの平方根の関数はSQRT関数となります。

この結果を見ると一つ問題があるのです。
理論上残差の様にメニュー毎に絶対値が同じで正負逆の数字に
ならなければいけないのですが、
この標準化残差はそうなっていません。

「残差分散」を求める

そこで残差分散を求めてそれを使って
標準化残差を調整します。

カイ二乗検定の後の残差分散の計算式

残差分散は上図の数式で求められ、
全て実測値を対象にします。
図内の左の表は実測値で、
右の表で残差分散を求めていきます。

ワクワクのポテトを例に式を見るとこうなります。

残差分散 =(1ー1250÷2000)×(1ー700÷2000)

セル内の数式は次の式になりますが、
コピペする時に絶対参照にするドルマークを上手く付ける必要があります。

=(1-$F47/$F$49)*(1-C$49/$F$49)

この数式をコピペした後に、
この1250のF47は下にはずれてほしいけど
F列から左右にはずれてほしくないのでFの横にドルマークを付けますが、

この2000のF49は横にも縦にもずれてほしくないので両方ともドルマークを付けます。

次の700のC49は横にはずれてほしいけど、
49行より上下にずれてほしくないので、
この49の前にドルマークを付けます。

最後の総数は同じ2000ですので、両方ドルマークを付けます。

これを全てのセルにコピペをしたら
ちゃんと思ったようにコピーされます。

 残差分散から「調整済み標準化残差」を求める

この残差分散を使って必要な「調整済み標準化残差」を
求める事ができるのです。下図内の式がそれです。

カイ二乗検定の後の調整済み標準化残差の計算式

ワクワクのポテトのセル内の式はこれです。

=I20/SQRT(F12*I47)

これをコピペすると、さっきの標準化残差が調整されて
全ての列で絶対値が全く同じの正負が逆の値になりました。(上図参照)

この中で絶対値が大きいほど違いがあるので、
やっぱりワクワクのチキンの売上の割合が一番低いみたいです。

各列のP値を求め、答えを見つける

最後にエクセル関数を使ってメニュー毎にP値を求めてみます。

その数式と計算結果が下図内の式と表になります。

カイ二乗検定の後の各列のP値の計算式

NORMSDIST関数の説明を見ると
「標準正規分布の累積分布関数の値を返します。」という事です。

ABS関数は英語のAbsoluteから、絶対値を取得します。

これでチキンのP値が0.36%で「違いが無い」と言う帰無仮説を完全に棄却できますし、
ワクワクバーガーのチキンの残差がマイナスなので、
その売上の割合が一番低い事が分かります。

しかし、ハンバーガーの残差はプラスで、P値が2.09%で、
これは5%の有意水準でしたら棄却できます。
ですのでハンバーガーの売上の割合は良いみたいです。

今言った有意水準はやはり、検定をやる前に
有意水準5%か1%どちらにするかを先に決めておいた方が良いでしょう。

参考までにこの残差分析を2×2のデータでやってみました。

カイ二乗検定の後の各列のP値の計算式(2×2)

カイ二乗検定のP値は3.46%で、
残差分析によるポテトもチキンのP値も同じ3.46%でした。

2×2のデータでやるといつも同じP値になります。
これで2×2のデータでは残差分析をする必要がない事がはっきりしましたね。

今回の計算方法は生物科学研究所 井口研究室のページを参考にさせて頂きました。
⇒「生物科学研究所 井口研究室のサイトのカイ二乗検定のページ」

皆さんどうでしたか?
ちょっと難しかったかもしれませんが、
ご自分でデータを入れて数式を書いていったらもっとご理解できるので、
今日お見せしたエクセルファイルを学習用として
ダウンロード可能にして実際にやってみて下さい。
⇒「カイ二乗検定と残差分析」エクセルテンプレートのダウンロード

<<カイ二乗の記事シリーズ>>