目的
・工場勤務の方はよくデータ解析をExcelで行うと思います。データを採取し、散布図にすると異常値が含まれていることがよくありますが、異常データを消したりしていませんか？
理由が分からない異常データが出るのは何か原因がある可能性があるため、削除せずそのまま残しておくべきですが、グラフの近似式を見たい場合には値がおかしくなってしまいます。これに対して今回紹介する方法を用いれば、異常値をグラフに表示しつつ、近似式は正しい値を出すことが出来るようになりますので皆さんの業務に活かして頂ければ幸いです。
異常値が含まれる近似式の誤差
| 開度【％】 | 測定値【ppm】 |
| 90 100 100 100 90 20 20 30 20 50 70 | 90 104 130 120 91 45 30 5 25 55 75 |
上記の散布図は右の表データ「開度vs測定値」を基に作成しています。散布図中の赤丸は異常値を表しています。このときの散布図の近似式はy = 1.0903x + 1.6061となります。
近似線を見ると開度90-100当たりの値が近似式だと上目に外れていますので、誤差が発生していることがわかりますね。
これを異常値のデータを残したまま、近似式をもう少し正確に算出したいので次項のように変更します。
修正した表とグラフ
| 開度 | 測定値 | 外れ値 |
| 90 100 100 100 90 20 20 30 20 50 70 | 90 104 91 30 25 55 75 | 130 120 45 5 |
修正方法
右図の表データに列「外れ値」を追加し、異常値データを列「外れ値」側にデータを転記します。その後、列「測定値」側の異常値データを削除する。
散布図のデータ選択にて「外れ値」を追加することで赤点で表示されます。こうすることにより、異常値は列「測定値」から省かれますので、より正確に近似式が引くことが出来ます。グラフの近似線を見ても、修正前よりデータ上に引かれていることが分かると思います。
近似式はy = 0.9265x + 8.9068と修正前と値が変わりました。これにより開度から測定値を正確に算出することが出来るようになります。
Excel画面上のデータ表変更箇所
Excelでは以下のように列を増やして修正をしてください。
修正前
修正後
(超重要)２つのデータの相関関係を数値化
・２つのデータ間の相関関係は具体的な数値で表すことが出来ます。それを相関係数というもので表すことがExcelではできます。相関係数は-1~+1で表され、＋１に近づくほど相関関係があることを表します。以下の関数を使うことで相関関係を求めます。
CORREL関数＝CORREL(データ①範囲,データ②範囲)
上記の修正前Excelシート上で相関係数を求めると
=CORREL(B4:B14,C4:C14)＝0.936809
修正後Excelシート上で相関係数を求めると
=CORREL(B4:B14,C4:C14)＝0.997666
となり、修正後のデータの方が相関がある状態になったことが分かります。
コメント