Excelで異常値関数を使用する方法（およびその理由）

異常値は、データ内のほとんどの値よりも大幅に高いまたは低い値です。 Excelを使用してデータを分析すると、外れ値によって結果がゆがめられる可能性があります。たとえば、データセットの平均値は本当にあなたの価値を反映するかもしれません。 Excelには異常値の管理に役立つ便利な機能がいくつか用意されています。.

簡単な例

下の画像では、異常値は、Ericに割り当てられた2の値とRyanに割り当てられた173の値を見つけるのがかなり簡単です。このようなデータセットでは、これらの異常値を手動で見つけて処理するのは簡単です。.

より大きなデータセットでは、そうではありません。異常値を特定して統計計算から除外できることが重要です。この記事では、その方法を説明します。.

データから外れ値を見つける方法

データセット内の外れ値を見つけるには、次の手順に従います。

第1四分位数と第3四分位数を計算します（これらについて少し説明します）。.
四分位範囲を評価します（これらについてもう少し詳しく説明します）。.
データ範囲の上限と下限を返す.
これらの範囲を使用して範囲外のデータポイントを識別します。.

下の画像に表示されているデータセットの右側にあるセル範囲は、これらの値を格納するために使用されます。.

始めましょう.

ステップ1：四分位数を計算する

データを4分の1に分割すると、これらの各セットは4分位数と呼ばれます。範囲内の数字のうち、最も低い25％が第1四分位数を構成し、次の25％が第2四分位数を構成します。最も一般的に使用されている異常値の定義は、第1四分位数より下の1.5四分位範囲（IQR）、および第三四分位数より上の1.5四分位範囲のデータポイントであるためです。それらの値を決定するために、我々は最初に四分位数が何であるかを理解しなければなりません.

Excelには四分位数を計算するためのQUARTILE関数があります。配列とクォートという2つの情報が必要です。.

= QUARTILE（配列、クォート）

の アレイ 評価している値の範囲です。そしてその クォート 戻りたい四分位数を表す数値です（例：1に対して1）。^セント四分位数、2番目の四分位数のための2、など）.

注意： Excel 2010では、MicrosoftはQUARTILE関数の改良としてQUARTILE.INCおよびQUARTILE.EXC関数をリリースしました。 QUARTILEは、複数のバージョンのExcelにまたがって作業する場合、より下位互換性があります。.

例の表に戻りましょう.

1を計算する^セント四分位数セルF2に次の数式を使用できます。.

= QUARTILE（B2：B14、1）

数式を入力すると、Excelはquart引数のオプションのリストを提供します。.

3を計算する^第二四分位数、前の式のようにセルF3に入力できますが、1の代わりに3を使用します。.

= QUARTILE（B2：B14、3）

これで、四分位データ点がセルに表示されました。.

ステップ2：四分位範囲を評価する

四分位範囲（またはIQR）は、データ内の値の中央50％です。第1四分位値と第3四分位値の差として計算されます。.

セルF4に単純な式を使用して、1を減算します。^セント 3から四分位数^第二四分位数：

= F3-F2

これで、四分位範囲が表示されました。.

ステップ3：下限と上限を元に戻す

下限と上限は、使用したいデータ範囲の最小値と最大値です。これらの境界値よりも小さいまたは大きい値はすべて異常値です。.

セルF5の下限値を計算するには、IQR値に1.5を掛けてからQ1のデータポイントから減算します。

= F 2  - （1.5 * F 4）

注意： 乗算部は減算部の前に計算するので、この式の括弧は必要ありませんが、式を読みやすくします。.

セルF6の上限を計算するために、IQRにもう一度1.5を掛けますが、今回は 追加する 第3四半期のデータポイントへ。

= F 3 +（1.5 * F 4）

ステップ4：外れ値を特定する

基礎となるデータがすべて設定されたので、今度は、外側のデータポイント、つまり下限値より低いか上限値より高いデータポイントを特定します。.

OR関数を使用してこの論理テストを実行し、セルC2に次の式を入力してこれらの基準を満たす値を表示します。

= OR（B2 $ F $ 6）

その値をC3-C14セルにコピーします。 TRUE値は異常値を示します。ご覧のとおり、データには2つあります。.

平均平均を計算するときに外れ値を無視する

QUARTILE関数を使用すると、IQRを計算し、最も広く使用されている外れ値の定義を使用して作業できます。ただし、ある範囲の値の平均値を計算して外れ値を無視する場合は、より早くより簡単に使える機能があります。この手法は以前のように異常値を識別しませんが、異常値の部分を考慮して柔軟に対応できるようになります。.

私たちが必要とする関数はTRIMMEANと呼ばれており、あなたはそれのための構文を以下で見ることができます：

= TRIMMEAN（配列、パーセント）

の アレイ 平均化したい値の範囲です。の パーセント データセットの上下から除外するデータポイントの割合です（割合または10進数で入力できます）。.

この例では、セルD3に次の式を入力して平均を計算し、20％の異常値を除外しました。.

=トリミング（B2：B14、20％）

異常値を処理するための2つの異なる関数があります。いくつかのレポート作成ニーズでそれらを識別するか、平均などの計算から除外するかにかかわらず、Excelにはニーズに合わせた機能があります。.