Box Cox変換をエクセルでやる方法【正規性検定③】
観測データを正規性のあるデータに変換するBox Cox変換の概要を分かり易く説明し、そのやり方をエクセル上でステップバイステップで実演しています。これにより、Box Cox変換を深く理解する事ができます。
（動画時間：9:12）
- ダウンロード ←これをクリックして「QQプロット、AD検定、Box Cox 変換」の学習用ファイルをダウンロードできます。
観測データに正規性が無い時にどうするか？
こんにちは、リーンシグマ、ブラックベルトのマイク根上です。
業務改善コンサルをしています。
今回がこの正規性検定シリーズ最後の動画で、
正規分布に従っていない観測データを
正規性のあるデータに変換するBox Cox変換の基本を話し、
それを実際にエクセル上でやる事で理解を深めて頂く内容になります。
前々回（第一話）の動画で僕の視聴者さんが製造業の工程で
観測データを使い管理図を作ろうとされてました。
その動画ではその正規性を見るためにQQプロットを作成して
直線にならなかったのです。
⇒「QQプロットのエクセルでのやり方：正規性の確認ができる【正規性検定①】」
第二話でアンダーソン・ダーリング検定を実施し、
計算されたP値から、改めて、数値的に正規性が無いのを確認しました。
⇒「アンダーソン・ダーリング検定をエクセルでやる方法、Ｐ値を求められる正規性の検定 【正規性検定②】」
そうなると、次に何ができるでしょう？
それは観測データをもっと加えて、再度正規性の分析をするか、
今日のテーマのデータ変換技術を使って観測データを
正規性を持たせたデータに変換して、
管理図を作成する事です。
Box Cox変換で正規性のあるデータに変換する。
データ変換の技術はいくつかあるのですが、
今日はエクセルでもできて、実務でも使える
Box Cox変換を採用します。
これはBoxさんとCoxさんが1964年に発表した技法です。
下図が、Box Cox変換式で、
その式内ｘに観測データを入れて変換します。
しかし、その為に、この変換したデータの分布と
標準正規分布の累積分布が限りなく近くなる
定数ラムダを見つける必要があります。
ラムダはその式内の漢字の「入」に似た記号の事です。
こう聞かれても良く分からないですね。
実際にこの作業をエクセルでやってみたら
理解が進むと思います。
エクセル上でBox Cox変換の実演をする。
観測データをいつも正の値になる様にする
前回の動画と同じ観測データを使います。（下図内、B列）
エクセル関数を使ってデータ数と最小値を求めます（C列）。
上図内上部にさっきのBox Cox変換式を再掲してますが、
その中のｘは各観測データで、この式では、
観測データが0かマイナスの時はエラーになってしまいます。
ですのでこのままではプラスの観測データにしか使えません。
Box Cox変換は負のデータには使えないと書いている日本語の情報が多いですが、
実際にはデータを全て正の値に補正すれば使えます。
⇒「参照ページ：Charles Zaiontz, Box-Cox Normal Transformation」
それをD列でやっていて、
観測データの最小値が0以下の時、
「各観測データに１－最小値を加える」事で
全ての数値をプラスに補正できるのです。
セルD7内
=B7+IF($C$8<=0,1-$C$8,0)
=観測データ+IF(最小値＜=0, 1-最小値, 0)
小さい順に並べ替えをし、標準正規分布の累積分布を出す。
次に、各データの連番と、
このSMALL関数を使って補正データを
SMALL関数を使って小さい順に並べ替えます。（下図内、G列）
セルG7内
=SMALL(D:D,E7)
=SMALL(配列, 順位)
そしてこのNORM.S.INV関数でデータ数、連番を使ってH列に
標準正規分布の累積分布を出します。
セルH7内
=NORM.S.INV((E7-0.5)/$C$5)
=NORM.S.INV(確率)
累積分布の説明はQQプロットの動画で
詳しくやりましたのでそちらをご覧下さい。
⇒「QQプロットのエクセルでのやり方：正規性の確認ができる【正規性検定①】」
そしてこの結果の分布が下図のグラフの様になり、
正規分布を縦に積み上げていった分布になります。
要は正規分布の別の形です。
Box Cox変換式を理解し、それをエクセルの方式でセルに入力する。
次に、小さい順に並んだ観測データを
さっきのBox Coxの数式で変換するのです。（下図内、I列）
まず重要ポイントで最適なラムダは最終的には定数になり、
後で求めますので、セルK6を参照する事にしています。
そして、そのラムダが０かそうじゃないかでIF文で数式を変えて、
式内のｘは各観測データになり、エクセルだと下の式になります。
この式を下にコピーしますので、ラムダのセルK6は全て絶対参照にします。
セルI7内
=IF($K$6=0,LN(G7),(G7^$K$6-1)/$K$6)
=IF(ラムダ = 0, LN(観測値), (観測値^ラムダ-1)/ラムダ)
（LN関数は自然対数をを求めます。）
エクセルアドインの「ソルバー」を使って最適なλ（ラムダ）を求め、変換データを取得する。
そして次にどうするか、H列の累積分布と
今作った I 列のBox Cox変換後の値の分布が
限りなく近くなるラムダを求めるという事です。
因みに、ラムダが０の時の変換後の分布は
今のところ下図のグラフの様になります。
そして、この二列が近くなるのを
CORREL関数で相関係数を求めて（上図内、セルK7）、
これが1に一番近づくラムダを求めるのです。
このラムダのセルK6に自分で値を１回１回入れて、
それがこの相関係数を最大にするかを確かめるのは大変だし無理です。
そこでメニューバーの「データ」で、
「分析」内の「ソルバー」を使います。
ソルバーについては前に動画を作りましたので詳しくはそちらをご覧下さい。
⇒「【エクセルのソルバーの使い方】最適解を瞬時に求める、無名の超便利機能」
今までソルバーを使ったことが無い方は
そこにソルバーが出てきません。
次の作業をすると、ソルバーが使える様になります。
- メニューバーの「ファイル」
- 画面左下の「オプション」
- 次の画面の左ペインで「アドイン」
- 管理で「Excel アドイン」の状態で「設定」
- 次の小画面で「ソルバー アドイン」にチェックをつけて「OK」
実際に「データ」タブからソルバー機能を使ってみましょう。
上図画面の右側がソルバーの操作画面です。
操作画面上から次の操作をします。
- 「目的セルの設定」：相関関数のセル「K7」を選ぶ。
- 「目標値」：相関関数が最大になるラムダを求めるので、「最大値」を選ぶ。
- 「変数セルの変更」：ラムダのセル「K6」を選ぶ。
- 「制約条件の対象」：ラムダは「-５から+５の間」の条件があるのでそれを追加する。
- 「制約のない変数を非負数にする」：ラムダはマイナスになる事もあるので、チェックがないようにする。
最後に「解決」をクリックすると数秒後に
ソルバーが完了したメッセージ画面が出てきます。
今回はラムダのセルK6に「-5」となり、
I 列のBox Cox変換後のデータが確定しました。
しかし、この列の順番は小さい順に変えた順番で、
管理図を作るには、最初の観測データの順番の
Box Cox変換値が必要です。
L列に同じBox Cox変換の式を入れて、
各データの参照を最初の順番であるD列にして、
最初の観測データの順番で
データ変換したデータとなるのです。
セルL7内
=IF($K$6=0,LN(D7),(D7^$K$6-1)/$K$6)
=IF(ラムダ = 0, LN(最初の観測値), (最初の観測値^ラムダ-1)/ラムダ)
再度QQプロットとアンダーソン・ダーリング検定を実施して変換データの正規性を確認する。
これでBox Cox変換したデータを取得できましたが、
直ぐに管理図で使うのではなく、
はたして本当に正規分布に従う
データになったのか確認したいです。
そこでこの変換データを使って、
再度QQプロットとアンダーソン・ダーリング検定で
正規性を確認してみます。
上図内のグラフが変換データで作成したのQQプロットです。
しかし、あまり一直線になっていません。
このシートをスクロールダウンすると
アンダーソン・ダーリング検定のP値があり、
そのP値がと0.0084で、
0.05より低いので帰無仮説を棄却して
「正規分布に従わない」結果となってしまいました。
ここまで説明をしてきてなんですが、こういう事も起こりえます。
次の対策としては観測データ数を増やした方が良いでしょう。
今回３つの記事で観測データの正規性検定とデータ変換を
エクセルでやる方法をお見せしました。
実務ではやはり、統計ソフトを使った方が良いですが、
エクセルで一度やって頂くとその背後にある概念を
とても理解する事ができます。
次のリンクから学習用として今回使ったエクセルファイルを
ダウンロードできますので数式などを確認して頂けたらと思います。
- ダウンロード ←これをクリックして「QQプロット、AD検定、Box Cox 変換」の学習用ファイルをダウンロードできます。
＜＜ 正規性検定シリーズ ＞＞
