Excel：如何计算排除异常值的平均值

在 Excel 中，有两种方法可以计算平均值并排除异常值：

1.计算平均值并使用TRIMMEAN排除异常值

2. 计算平均值并使用四分位数范围排除异常值

我们将在 Excel 中使用以下数据集来说明如何使用这两种方法：

方法一：计算平均值并使用TRIMMEAN排除异常值

Excel中的TRIMMEAN函数可用于计算一系列值的平均值，同时从数据集的顶部和底部排除一定百分比的观察值。

例如，我们可以使用以下公式计算 A 列中排除总共 20% 的观测值（前 10% 和后 10%）的平均值：

 =TRIMMEAN( A2:A16 , 20%)

由于我们的数据集中有 15 个值，10% 等于 1.5，四舍五入为 1。所以这个公式将计算范围内值的平均值，同时排除最小值和最大值：

排除异常值后，平均值为58.30769 。

方法2：计算平均值并使用四分位数范围排除异常值

四分位距(IQR) 是数据集中第 75 个百分位数 (Q3) 和第 25 个百分位数 (Q1) 之间的差值。它测量平均 50% 值的分布。

如果某个观测值是第三个四分位 (Q3) 上方四分位距的 1.5 倍或第一个四分位 (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)

以下屏幕截图显示了如何使用此公式：

我们发现只有一个值——164——在这个数据集中被证明是异常值。

最后，我们可以使用以下公式计算数据集中所有非异常值的平均值：

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

以下屏幕截图显示了如何使用此公式：

排除异常值后，平均值为55.42857 。

其他资源

以下教程说明如何在 Excel 中执行其他常见任务：

如何在 Excel 中计算四分位距 (IQR)
Excel中如何计算分组的平均值
如何在 Excel 中计算加权移动平均线