この記事では「エクセルで異常値を除いた平均と標準偏差（外れ値：上下カット）を求める方法【関数：上下カットの平均】」について解説していきます。
方法としては、下記内容を使うといいです。
・TRIMMEAN関数
・フィルタを設定し、SUBTOTAL関数により処理する
それでは、サンプルデータを用いて解説しましょう。
エクセルに異常値を除いた平均値を求める方法【外れ値：関数：上下カットの平均】
下記サンプルはある数値データの一部です。
お気づきになったかもしれませんが、明らかに場違いな値が存在しています。
今回のお題は「場違いな値を除外した平均値を求める」であります。
計算結果を出力したいセルを1つ選び、「=TRIMMEAN(B1:B10, 0.2)」 と入力します。
なお、今回はセル参照及び数値入力を行っているので、”(ダブルクォーテーション)で括ってしまうと文字列として扱われ、正しい計算が行われません。
TRIMMEAN関数について解説します。
当関数は、指定した割合に相当する上位及び下位の値を含めない平均値を求める際に使用し、
と入力します。
各引数には
・配列(B1:B10): 計算対象の範囲
・ 割合(0.2): 計算しないデータの割合
をそれぞれ指定します。
今回、データは10件なので、計算対象外となるデータは2件、すなわちD07とD10が相当します。
ENTERを押すと、計算結果が出力されます。
Ctrlキーを押しながら、セルをクリックすると、飛び飛びのセルを選択することができます。
これで、今回の計算が想定通りであることを確認しておきましょう。
エクセルに異常値を除いた標準偏差を求める方法【外れ値：関数：上下カットの標準偏差】
今度は外れ値を除去した標準偏差を求める場合を考えてみましょう。
まず、フィルタを設定する必要があります。
下記のように、タイトル行を追加します。
次にリボンより「データ」–「フィルタ」をクリックします。
間違いがなければ、タイトルの右の角に▼マークが表示されます。
▼マークをクリックすると、このようなドロップダウンが開くので、「数値フィルター」–「指定の範囲内」と進みます。
下記ポップアップにて正常値の上限及び下限を確定させます。
ここでは、100~1000を正常値と仮定してみましょう。
なお、「AND」と「OR」が正しく選択できていないケースをよく見かけますので注意が必要です。
「A<X<B」であればAND、「X<A, B<X」であればORが正解です。
OKを押し、正常値のみが抽出されていれば問題ありません。
出力したいセルに「=SUBTOTAL(7,B2:B11)」 と入力します。
”(ダブルクォーテーション)で”括る/括らない”のルールについては前章をご確認ください。
SUBTOTAL関数について解説します。
当関数は、フィルタ機能で抽出された値に対する計算を行う際に使用し、
と入力します。
各引数には
・集計方法(7): 合計、平均など、どのような計算を行うのかを番号で指定する、標準偏差に相当するのは7。
・参照(B2:B11): 計算対象の範囲
をそれぞれ指定します。
ENTERを押すと、正常値のみの標準偏差が計算されます。
まとめ エクセルで異常値（外れ値）を除いた平均と標準偏差（外れ値：上下カット）を求める方法【TRIMMEAN関数等：フィルタ】
この記事では「エクセルで異常値（外れ値）を除いた平均と標準偏差（外れ値：上下カット）を求める方法【TRIMMEAN関数等：フィルタ】」について解説しました。
異常なデータは絶えず発生していることは確かですが、それらを速やかに除去する方法が確立していれば、いかなる時も的確な集計と分析ができるはずです。
この記事がよりスマートなデータ処理を行うための助けとなるのであれば幸いです。
コメント