Excelで外れ値関数を使用する方法

データを分析しているとき、外れ値は結果を歪め、信頼性を低下させる可能性があります。 たとえば、1つ以上の値がデータの大部分よりも大幅に高い場合、平均が高くなり、データ全体が反映されない可能性があります。 したがって、これらの範囲外のデータポイントを削除することは、信頼できる統計計算を実行する上で重要なステップです。 専用の外れ値関数がない場合、Excelで外れ値テストを実行する最も簡単な方法は、 外れ値を特定するための四分位範囲ですが、Trimmean関数を使用して同じものを近似することもできます 結果。

四分位範囲のデータは、箱ひげ図の「箱」でカバーされる範囲、またはそれ以上です。 正確には、データの最初の四分位数の値を3番目の四分位数の値から引いた結果 四分位数。 Excelには、データの四分位数を計算するための組み込み機能が含まれています。 予備のセルを見つけて「= QUARTILE（[データ範囲]、[四分位数]）」と入力し、セルの範囲を入力します 「[データ範囲]」と表示されているデータと、「[四分位数]」と表示されている必要な四分位数 番号]。"

たとえば、セルA2からA101にデータがあり、最初の四分位数の値が必要な場合は、「= QUARTILE（A2：A101,1）」と入力して値を検索します。 引数の最初の部分では、マウスで関連するセルを強調表示できますが、コンマの後に、必要な四分位数を書き込む必要があります。 同じデータを持つ3番目の四分位数については、「= QUARTILE（A2：A101,3）」と入力して結果を取得します。

別の空のセルを使用して、3番目の四分位セルの値から最初の四分位セルの値を減算します。 最初の四分位数がセルC2にあり、3番目の四分位数がセルD2にある場合は、「= D2-C2」と入力して結果を取得します。 これは四分位範囲です。

外れ値を見つけるために、外れ値の式で四分位範囲を使用できるようになりました。これは、データの上限が 3番目の四分位数に四分位範囲の1.5倍を加えたもので、下限は最初の四分位数から四分位範囲の1.5倍を引いた値です。 範囲。

最初の四分位値がC2にある場合、3番目の四分位値はセルD2にあり、四分位範囲は セルE2で、「= C2-（1.5 * E2）」と入力して下限を検索し、「= D2 +（1.5 * E2）」と入力して上限を検索します。 制限。 一般に、「= [第1四分位] –（1.5 * [四分位範囲]）」と入力して下限を見つけ、「= [第3四分位] +（1.5 * [四分位範囲]）」と入力して上限を見つけます。

下限を下回るもの、または上限を超えるものはすべて外れ値です。

Excelで外れ値のテストを終了するには、論理「OR」関数を使用して、データクラスのどの値が効率的な方法で外れ値であるかを識別します。 「= OR（[データセル]> [上限]、[データセル] $ F $ 2、A2

A2の値が上限を上回っている、または下限を下回っている場合は、「TRUE」と表示され、値が外れ値であることを示します。 数式が表示されているセルの右下隅をクリックすると、この数式を下にドラッグできます。 最後のデータセルの横で終了するように下にドラッグして、それぞれで同じ計算を実行します データポイント。

外れ値のフォーマットを変更する場合は、データを強調表示し、[ホーム]タブの[スタイル]セクションの[条件付きフォーマット]に移動することもできます。 [新しいルール]を選択し、[数式を使用してフォーマットするセルを決定する]オプションを強調表示します。 前の段落と同じ式を入力し、[フォーマット]オプションをクリックして、外れ値の一意のフォーマットを選択します。

「Trimmean」関数は、外れ値を識別するためのより簡単な方法です。 「[datarange]」の代わりにデータを含むセルの範囲と小数を使用して関数を使用するには、「= TRIMMEAN（[data range]、[proportion totrimm]）」と入力します 「[トリミングする割合]」と表示されている場所でトリミングする割合。 これにより、上部と下部の極値が削除され、それらに基づいて平均が計算されます。 残り。 したがって、10パーセントをトリミングすると、平均を計算する前に、上位5パーセントと下位5パーセントが削除されます。

データがA2からA101まで実行され、値の極端な5％をトリミングする場合は、「= TRIMMEAN（A2：A101,0.05）」と入力して調整された平均を求めます。 代わりに「= TRIMMEAN（A2：A101,0.15）」と書くことで15パーセントをトリミングできます。