2017年4月20日木曜日

外れ値や異常値を判断する統計処理をエクセルでやる方法

エクセルだけで統計処理するシリーズです.

今回は,これまでネットであまり取り上げられていなかった「外れ値」や「異常値」ではないかと思われるデータの処理について取り上げます.
かなり以前から「ネットにあがっていないから」と思って取り上げるつもりだったのですが・・・,のんびりしているうちに,どうやらウィキペディアにも掲載されるようになりました.
外れ値(wikipedia)

でも,具体例を使ってエクセルで算出しているブログ記事があるのも良いかと思います.

データを取ってみたところ,「これって外れ値や異常値じゃないか?」というのは,例えば以下のようなものです.


Iさんが「48」ということで,他の人たちよりちょっと大きい数値です.
これが何かしらのエラーや記入ミスなどで起こったものかどうか判別したいというケースはありますよね.

クソ! こいつさえ居なければ・・,こいつさえ居なくなれば有意性がある(ない)と言えるのにぃ! って悔しい思いをしている人は多いことと思います.

もしくは,もっと穏やかで健全な理由として「一般的で平均的なデータだけを扱ったことにしたい」という人もいるでしょう.

つまり,この集団と計測データの平均的な分布パターンから,その当該データが統計学的に大きく離れた値であることを示したい.「だからこの値を異常値として除外した」と言いたい(できれば「統計学的に有意性が認められたため」と言い放ちたい)ということです.

考え方としては,「平均値および正規分布からどれだけ離れた値なのか?」を示すことになります.
似たような統計処理に,「1サンプルのt検定」があります.
「そっちの方が知りたかった」という場合もあるかもしれません.遥か昔に記事にしていますので,こちらもどうぞ.
あまり知られていないt検定


では,計算に必要なデータを算出しましょう.
まず,平均値と分散です.以下のようにエクセル関数で簡単に出せます.
平均値はAVERAGE関数,分散はVAR関数です.参照セルはこのようにします.

平均値は32.6,分散は39.8となりました.

これで準備は終わり.あとはここから統計量を出していきます.
ウィキペディアの記事にも算出式が載っています.「スミルノフ・グラブス検定」というやつです.
外れ値(wikipedia)
それを,この例データを使って算出してみましょう.
D列11行目にT値を算出しています.

D列11行目には,以下のように記入されています.
=(B10-B11)/SQRT(B12)

つまり,Iさんのデータ「48」を全体の平均値により引き算して差を求め,それを分散の平方根で割るという計算をします.
するとこうなります↓

これはT値ですので,エクセル関数の一つ「TDIST」を使えば,p値を出すことができます.
自由度は「N数−2」を使います.
この例のN数は9ですので,「7」ということです.尾部は両側検定なので「2」にします.
D列12行目にp値を算出しました.このようにします↓
D列12行目には,以下のように記入されています.
=TDIST(D11,7,2)

結果は以下の通り↓

「p=0.04」ですから,5%水準での有意性が認められたことになります.
つまり,Iさんの測定値「48」は,外れ値や異常値として処理しても文句がつきにくいということです.

【注意】この方法には注意点があります.
今回のように数値が平均値より大きい場合にはそれで良いのですが,小さい場合には以下のようにしなければなりません.
例えば,Iさんのデータが48ではなく,「15」ということで,小さすぎるのではないか? という場合は・・・.

さっきの算出式のままだと以下のようになります↓

「おい! エラーになるじゃないか!」って焦ります.
なので,以下のように平均値との差をみる引き算の部分を,逆にしなければなりません.
このように,上述したものと違い,B10とB11を入れ替えています.
=(B11-B10)/SQRT(B12)

そうすれば,以下のようにきちんと算出されます.

どれを「外れ値」として扱うか迷っていて,探索的に作業したい,もしくは一括算出してしまいたいと計画しているのであれば,ABS関数を使えばOKです.
こんな風にします↓

=ABS(B10-B11)/SQRT(B12)

ABS関数は絶対値を算出してくれますので,上記のようなエラーは発生しなくなります.


関連記事
Excelで多重比較まとめ
ExcelでTukey法による多重比較
ノンパラメトリック検定で多重比較したいとき
ノンパラメトリック版Tukey法による多重比較「Steel-Dwass法」
エクセルExcelでの簡単統計(対応のあるt検定と多重比較)

ちょっとした統計処理上のエクセル小技はこちら
エクセルで相関係数のp値を出す
エクセル散布図で相関関係・相関係数を確認する便利な方法
エクセルで大量のデータを等分割して統計処理したいとき
エクセルで大量のデータを処理したいとき
エクセルだけで統計処理する卒論・ゼミ論用アンケート調査のオススメ方法 part1
点数・得点を段階評価するためのエクセルシートの作成

その他,こういう怪しいブログ記事よりも,ちゃんと勉強になる書籍もご紹介しておきます.
詳しくは,
独学で統計処理作業をスキルアップさせるための本
を御覧ください.