[Excel][VBA]ホテリングのT2理論で異常値を検出する

統計学 VBA

のっぴきならぬ事情により、python などで計算を行う事が出来ない人もいるかもしれません。そのような人の為に、エクセルだけで完結するコンテンツを作ります。
今回の記事では、ホテリングのT2理論によって、異常値を検出する処理をVBAで行います。


エクセルファイルやVBAファイルはgithubに置いておくので好きに使ってください。
https://github.com/msamunetogetoge

問題設定

日々蓄積されていくようなデータがあるとします。データはn次元で横ベクトルの形でまとまっているとします。1
データの中から、変なデータを見つけるのが目標です。
手法の特性上、1つのデータの次元が大きくなると、変なデータの判定が緩くなるので、見る必要が無い次元は積極的に削除した方が良いです。

データの形
データの形

ホテリングのT2理論

ホテリングのT2理論については、解説記事を書いているので、詳しい計算などはそちらをご覧ください。また、pythonでの実装も書いているので、エクセルにこだわる必要が無い人は、そちらを読んでみてください。

最も簡単な異常検知の手法-ホテリングのT2 理論

マハラノビス距離を使った異常検知

データが1次元(1列)の時のホテリングのT2理論は以下のように行います。

[ホテリングの\(T2\)理論の計算(1次元)]

  1. 最尤推定で、平均値\( \mu \) と分散 \( \sigma ^2 \)を推定する。
  2. 全てのデータ\(x \) に対して \( F\)統計量
    $$\begin{eqnarray}
    \alpha _x = \left( \frac{x-\mu }{\sigma } \right) ^2
    \end{eqnarray}$$
    を計算する 。\( \alpha _x \sim \chi _{1}^2 \)
  3. 各データに対して、
    $$\begin{eqnarray}
    \int _{\alpha } ^{\infty} \chi ^{2} du =0.95
    \end{eqnarray}$$
    となる\( \alpha \)を求める。
  4. \( \alpha > \alpha _x \)なら、xは異常値と判断する。

データが2次元以上の時は、以下のように計算します。計算される量は、マハラノビス距離とも呼ばれています。

[ホテリングの\(T2\)理論の計算(多次元)]

  1. 最尤推定で、平均値\( \mu \) と分散 \( \Sigma \)を推定する。
  2. 全てのデータ\(x \) に対して
    $$\begin{eqnarray}
    \alpha _x = (x-\mu ) \Sigma ^{-1} (x-\mu) ^T
    \end{eqnarray}$$
    を計算する 。\( \alpha _x \sim \chi _{M}^2 \)とする。
  3. 各データに対して、
    $$\begin{eqnarray}
    \int _{\alpha } ^{\infty} \chi _{M} ^{2} du =0.95
    \end{eqnarray}$$
    となる\(\alpha \)を求める。
  4. \( \alpha > \alpha _x \)なら、xは異常値と判断する。

VBAで実装

VBAで上の計算を実装します。2
初めてVBAで処理を書いたので、VBAらしいコードかは分かりません。取りあえずコードの全文を載せ、要所の解説をします。大事、と言っている部分を変更すると、動作がおかしくなります。3

初めのDim ~~は変数の定義です。必要かは良く分かりません。
次に、Set ~でワークシートの名前を指定しています。これは大事です。

次に、異常値を検出したら赤字を使いたいので、一度ワークシート内の文字の色の設定を初期化し、データの情報4 を取得しています。これも大事です。

次に、共分散を計算しています。行列だけの計算 5 では上手く動かせなかったので、成分毎に計算しました。また、S_inv に共分散の逆行列を格納しています。これはもちろん大事です。

最後にカイ二乗統計量を計算して、95%点を超えるかどうか判定しています。大事です。その後に、dataシートに入っているデータをコピーしてresultシートにも張り付けています。
最後は結果を見やすくするためだけの処理です。6

マクロの使用方法

excelファイルに、データをコピー&ペーストして使う事を想定しています。
使い方は以下のようになります。
1.データは以下の写真のように成形して、dataシートに貼り付ける。

データの整形
データの整形

2.マクロ7を実行する

データを正しく成形していれば、result シートに判定結果が出力されます。

実行結果

この記事が有用だと思ったら、pythonやRで同じことをしてみてください。8
あえてVBAでやる必要は、今の時代全くないです。

まとめ

  • ホテリングのT2理論を簡単に解説した
  • 問題設定の説明をした
  • VBAで実装した
  • 使い方を簡単に説明した
  1. 横方向に項目が並んでいて、セルを上下に移動すると、別のデータになっているイメージです。
  2. pythonは可読性が高く、numpy が偉大だという事に気付かされました。
  3. M>1でしか動作確認して無いので、変な事になるかもしれません。
  4. データの大きさなど
  5. worksheetfunctionのmmlutで計算するという事です。無頓着に変数を使うと、配列[だったりそうじゃなかったりして動きませんでした。
  6. pythonのnumpy みたいにvbaを動かすコツがあれば教えて欲しいです。例えば、共分散行列の計算を、成分毎にやらない方法など知りたいです。
  7. Hotellingという名前で記憶されているはず
  8. python での実装は、ブログの中にあります。
タイトルとURLをコピーしました