Excelで統計的外れ値を強調表示する方法

データ分析を実行するときは、通常、値が中央データポイント（中央値）の周りに集まっていると想定します。ただし、値のいくつかが中心点から離れすぎている場合があります。これらの値は外れ値と呼ばれます（期待される範囲外にあります）。外れ値は、統計分析を歪め、データに関する誤った結論または誤解を招く結論につながる可能性があります。

いくつかの簡単な数式と条件付き書式を使用して、データの外れ値を強調表示できます。

外れ値を特定する最初のステップは、範囲の統計的中心を特定することです。この正確な特定を行うには、最初に1番目と3番目の四分位数を見つけることから始めます。四分位数は、各グループは、データの25％を構成すると、4つの等しいグループにデータセットの統計的分割です。コレクションの上位25％は第1四分位数と見なされ、下位25％は第4四分位数と見なされます。

Excelでは、QUARTILE関数を使用して四分位値を簡単に取得できます。この関数には、データの範囲と必要な四分位数の2つの引数が必要です。

示されている例では、セルE3とE4の値は、範囲B3：B20のデータの1番目と3番目の四分位数です。

これらの2つの四分位数を使用すると、1番目の四分位数から3番目の四分位数を引くことにより、データセットの統計的な50パーセントを計算できます。この統計的な50％は、四分位範囲（IQR）と呼ばれます。図9-18は、セルE5のIQRを示しています。

ここで問題となるのは、値が中央の50％からどれだけ離れていても、「合理的な」値と見なすことができるかということです。統計学者は一般に、IQR * 1.5を使用して妥当な上下のフェンスを確立できることに同意しています。

下側のフェンスは、第1四分位数– IQR * 1.5に等しくなります。

上部のフェンスは、第3四分位数+ IQR * 1.5に等しくなります。

ご覧のとおり、セルE7とE8は、最終的な上部フェンスと下部フェンスを計算します。上部フェンスよりも大きい値または下部フェンスよりも小さい値は、外れ値と見なされます。

この時点で、条件付き書式ルールは簡単に実装できます。

この基本的なフォーマットルールを作成するには、次の手順に従います。

ターゲット範囲のデータセル（この例ではセルB3：B20）を選択し、Excelリボンの[ホーム]タブをクリックして、[条件付き書式]→[新しいルール]を選択します。

これにより、[新しい書式設定ルール]ダイアログボックスが開きます。

ダイアログボックスの上部にあるリストボックスで、[数式を使用してフォーマットするセルを決定する]オプションをクリックします。

この選択により、指定した式に基づいて値が評価されます。特定の値がTRUEと評価された場合、条件付き書式がそのセルに適用されます。

数式入力ボックスに、ここに表示されている数式を入力します。

OR関数を使用して、ターゲットセル（B3）の値を、セル$ E $ 7および$ E $ 8にそれぞれある上部フェンスと下部フェンスの両方と比較することに注意してください。ターゲットセルが上部フェンスよりも大きいか、下部フェンスよりも小さい場合、それは外れ値と見なされるため、TRUEと評価され、条件付き書式がトリガーされます。

= OR（B3 <$ e $ 8、b3> $ E $ 7）

数式では、ターゲットセル（B3）の絶対参照ドル記号（$）を除外していることに注意してください。セル参照を入力する代わりにセルB3をクリックすると、Excelは自動的にセル参照を絶対にします。各セルの値に基づいてこの書式設定ルールを適用するにはExcelが必要なため、ターゲットセルに絶対参照ドル記号を含めないことが重要です。

[フォーマット]ボタンをクリックします。

これにより、[セルの書式設定]ダイアログボックスが開きます。このダイアログボックスには、ターゲットセルのフォント、境界線、および塗りつぶしを書式設定するための完全なオプションがあります。書式設定オプションの選択が完了したら、[OK]ボタンをクリックして変更を確認し、[新しい書式設定ルール]ダイアログボックスに戻ります。

[新しい書式設定ルール]ダイアログボックスに戻り、[OK]ボタンをクリックして書式設定ルールを確認します。

条件付き書式ルールを編集する必要がある場合は、書式設定された範囲内の任意のデータセルにカーソルを置き、[ホーム]タブに移動して、[条件付き書式]→[ルールの管理]を選択します。これにより、[条件付き書式ルールマネージャー]ダイアログボックスが開きます。編集するルールをクリックしてから、[ルールの編集]ボタンをクリックします。