【エクセル】連続データの異常値をアラート化する方法

大学の化学実験レポートで何かと書いたことが、実を結んだ瞬間でした。（本人・談）

感覚値ありき、やめませんか？

リスティングに限らず、業務に長けていると直感的な判断ができるようになりますよね？

「なんか数字が変だな・・・違和感がある」

――　いわゆる「職人」の領域です。

厨二病的にはテンションが上がる話なのですが、
残念ながら「あなた」にしかわからない「感覚値ありき」の世界は、
組織の中では求められてません。

もしも、「あなた」の直感ありきの重要な案件が、
「あなた」が倒れたり、動けなくなったりして
「あなた」以外の担当がつくことになったらどうなるでしょう？

サービス品質が、ガタ落ちするかもしれないです。
クライアントからの信頼を失うかもしれません。
そうあってはならないのです。

すべてはリスクヘッジのため、「直感」を標準化・平準化することで、
サービス品質を保つ。

その上で、
感覚値を使った判断が必要なのではないでしょうか？

私は、誰でもわかるように、誰でもできるように

言うなれば、
――　小学生でもわかるように。

と言い換えてます。
幼稚園はちと無理があったのでｗ

感覚値を可視化するための準備

タイトルのように連続データで異常値があった場合の発見方法について書きます。

私が運用しているリスティング(検索連動型)広告は、
日々のデータを積み重ねるだけじゃなく、
異常値をより早く発見して対処する必要性があるため、
感覚値に頼るよりも、より誰でも対応できる必要がありました。

そのために考えついた方法です。
（統計学をちゃんと勉強すればすぐに気付くんでしょうけどね。。。orz）

標準偏差、はずれ値、移動平均。
この３つを使います。

標準偏差

統計値や確率変数の散らばり具合（ばらつき）を表す数値のひとつ。

Yahoo!知恵袋にわかりやすい回答がありました。
・標準偏差の意味をとってもわかりやすく教えてください。

ちなみに、
Wikipediaはこちら。

外れ値

外れ値かどうか検定したい標本について、偏差を不偏標準偏差で割った検定統計量

を求め（x1 は標本値、μ は平均、σ は標準偏差）、この値（両側検定をする場合はこの絶対値）が有意点より大きいかどうかで検定する。
簡単な方法では、2または3を有意点とする。つまり、μ ± 2–3 σ の外なら外れ値とする。

Wikipediaより引用したのですが・・・非常にわかりにくい！

・・・ということで、
超ざっくりいうと、
友達の偏差値が50前後がやたら多いときの、偏差値＝「80」という値とかのことです。
ほんとざっくりですがｗ

移動平均

正確にいうと単純移動平均です。
（※本来は、加重移動平均のほうが向いています。詳しくはWikipediaをご参照を）

私は５日、もしくは７日で設定しています。
例）５日の移動平均値＝（P1＋P2＋P3＋P4＋P5）／５

感覚値の可視化 on エクセル

エクセルは統計処理がちょっと苦手なのですが、
統計学で使用するようなデータではないので数式をそのまま使います。

数式

上にも書いた外れ値の検定の式を使います。

この式を、移動平均を５日（母集団を５）として、
エクセルの関数を書くと・・・

τ(タウ)＝ABS（対象セル - AVERAGE(対象セルを含む５個手前のセルまで)／STDEVP(AVERAGEと同じ範囲)

ABSは絶対値を返す数式で、負の値がでないように。
STDEVPは標準偏差を求める数式で、分母をn-1として算出。
AVRAGEは言わずもがなですね。

具体的な設定

実際にエクセルでどのように設定したらいいか？、
私は実数データの下のセルに、
検定の数式を入れてτ値を算出します。

※データ母数が大きいコスト/表示回数/クリック数だけに絞っています。

条件書式を使ってアラートに

τ値がいくつ以上であれば、アラート(セル色を変える)にしたらいいのか？
いろいろ試した結果、「1.5」がおおよそアラートとして有効な数値でした。

条件付き書式で、1.5以上(文字色：赤)であればセル色を変える(白→紫)　という設定をすると・・・

紫のセルが外れ値(異常値)として表示されます。

作るデータは環境に応じて

リスティング(検索連動型)広告では、
広告の運用者の属している組織の違いで、
インハンス(自社運用)とエージェンシー(代理店運用)に分かれます。

インハウスであれば、キャンペーン単位
エージェンシーであれば、アカウント(クライアント,プロジェクト)単位

まずは、
それぞれの単位から作ってみてはいかがでしょうか？

―（追記）―
うちでは、APIを使って日報などのカスタマイズレポートを自動的に取得しています。
でも、APIはなんだか怖い！
でも、レポート作成などに充てるリソース不足を解消したい！
という方に、
エクセルVBAベースで、効率化のお手伝いをさせて頂いております。
ご興味のある方は、FBもしくは右下のお問い合わせボタンから申し付けくださいませ。