Excelで外れ値関数を使用する方法（および理由）

外れ値は、データ内のほとんどの値よりも大幅に高いまたは低い値です。Excelを使用してデータを分析する場合、外れ値によって結果が歪む可能性があります。たとえば、データセットの平均は本当にあなたの値を反映しているかもしれません。Excelには、外れ値の管理に役立ついくつかの便利な関数が用意されているので、見てみましょう。

簡単な例

下の画像では、外れ値を簡単に見つけることができます。エリックに割り当てられた2つの値と、ライアンに割り当てられた173の値です。このようなデータセットでは、これらの外れ値を手動で見つけて処理するのは簡単です。

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

データ内の外れ値を見つける方法

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

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

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

始めましょう。

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

データを4分の1に分割すると、それらの各セットは四分位数と呼ばれます。範囲内の数値の下位25％が第1四分位数を構成し、次の25％が第2四分位数を構成します。外れ値の最も広く使用されている定義は、第1四分位より1.5四分位範囲（IQR）を超え、第3四分位より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はクォート引数のオプションのリストを提供します。

3番目の四分位数を計算するには、セルF3に前の数式と同様の数式を入力できますが、1ではなく3を使用します。

= QUARTILE（B2：B14,3）

これで、セルに四分位数のデータポイントが表示されました。

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

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

セルF4に、3番目の四分位数から1番目の四分位数を引く簡単な数式を使用します。

= F3-F2

これで、四分位範囲が表示されます。

ステップ3：下界と上界を返す

下限と上限は、使用するデータ範囲の最小値と最大値です。これらのバインドされた値よりも小さいまたは大きい値はすべて外れ値です。

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

= F2-（1.5 * F4）

注：この数式の括弧は、乗算部分が減算部分の前に計算されるため必要ありませんが、数式が読みやすくなります。

セルF6の上限を計算するために、IQRに1.5を再度乗算しますが、今回はそれをQ3データポイントに追加します。

= F3 +（1.5 * F4）

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

基礎となるすべてのデータが設定されたので、次は、範囲外のデータポイント（下限値よりも低いデータポイントまたは上限値よりも高いデータポイント）を特定します。

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

= OR（B2 $ F $ 6）

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

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

QUARTILE関数を使用して、IQRを計算し、最も広く使用されている外れ値の定義を処理します。ただし、値の範囲の平均を計算し、外れ値を無視する場合は、より速く簡単に使用できる関数があります。この手法では、以前のように外れ値を特定することはできませんが、外れ値の部分と見なす可能性のあるものに柔軟に対応できます。

必要な関数はTRIMMEANと呼ばれ、その構文を以下に示します。

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

配列は、あなたが平均にしたい値の範囲です。％は、データセット（あなたは割合または小数の値として入力することができます）の上部と下部から除外するデータポイントの割合です。

この例のセルD3に以下の数式を入力して、平均を計算し、外れ値の20％を除外しました。

= TRIMMEAN（B2：B14、20％）

外れ値を処理するための2つの異なる関数があります。一部のレポートニーズでそれらを識別したい場合でも、平均などの計算から除外したい場合でも、Excelにはニーズに合う機能があります。