Excel: 外れ値を除いた平均を計算する方法

Excel で外れ値を除外しながら平均を計算するには、次の 2 つの方法があります。

1. 平均を計算し、TRIMEAN を使用して外れ値を除外します。

2. 平均を計算し、四分位範囲を使用して外れ値を除外します。

Excel で次のデータ セットを使用して、両方の方法の使用方法を説明します。

方法 1: 平均を計算し、TRIMEAN を使用して外れ値を除外する

Excel のTRIMMEAN関数を使用すると、データ セットの上位と下位から一定の割合の観測値を除外しながら、値の範囲の平均を計算できます。

たとえば、次の式を使用して、観測値の合計 20% (上位 10% と下位 10%) を除いた列 A の平均値を計算できます。

 =TRIMMEAN( A2:A16 , 20%)

データセットには 15 個の値があるため、10% は 1.5 に等しく、これは 1 に四捨五入されます。 したがって、この式は、最小値と最大値を除き、範囲内の値の平均を計算します。

外れ値を除いた平均は58.30769であることがわかります。

方法 2: 平均を計算し、四分位範囲を使用して外れ値を除外する

四分位範囲(IQR) は、データセット内の 75 パーセンタイル (Q3) と 25 パーセンタイル (Q1) の差です。値の平均 50% の分布を測定します。

観測値が第 3 四分位 (Q3) より上の四分位範囲の 1.5 倍、または第 1 四分位 (Q1) より下の四分位範囲の 1.5 倍である場合、その観測値を外れ値として定義できます。

次の式を使用して、Excel でデータセットの四分位範囲を計算できます。

 =QUARTILE( A2:A16,3 )-QUARTILE( A2:A16,1 )

次のスクリーンショットは、この式の使用方法を示しています。

次に、次の式を使用して IQR を使用して外れ値を特定し、データセット内の外れ値である値に「1」を割り当てることができます。

 =IF(OR( A2 <QUARTILE( $A$2:$A$16 ,1)-1.5* $B$18 , A2 >QUARTILE( $A$2:$A$16 ,3)+1.5* $B$18 ),1, 0)

次のスクリーンショットは、この式の使用方法を示しています。

このデータセットでは、1 つの値 ( 164 ) だけが外れ値であることがわかります。

最後に、次の式を使用して、データセット内の外れ値ではないすべての値の平均を計算できます。

 =AVERAGEIF( B2:B16 , 0, A2:A16 )

次のスクリーンショットは、この式の使用方法を示しています。

外れ値を除いた平均は55.42857であることがわかります。

追加リソース

次のチュートリアルでは、Excel で他の一般的なタスクを実行する方法について説明します。

Excel で四分位範囲 (IQR) を計算する方法
Excelでグループごとの平均を計算する方法
Excel で加重移動平均を計算する方法