在数据分析中,异常值是指那些数值明显高于或低于数据集中其他数值的值。 当使用 Excel 进行数据分析时,这些异常值可能会对结果产生扭曲影响。 举例来说,数据集的平均值可能会因为异常值的存在而无法真实反映数据的分布情况。 Excel 提供了一些实用的函数来帮助我们管理这些异常值。接下来,我们一起深入了解。
一个简单的示例
在下面的示例图片中,我们可以很容易地识别出异常值:Eric 的值为 2,而 Ryan 的值为 173。 在这样的小型数据集中,我们可以手动识别并处理这些异常值。
然而,当数据集变得庞大时,情况就复杂多了。 能够识别异常值并将它们排除在统计计算之外就显得至关重要。 这正是本文将要讨论的核心内容。
如何在数据中定位异常值
为了在数据集中找到异常值,我们可以遵循以下步骤:
首先,计算第一四分位数(Q1)和第三四分位数(Q3)(我们将在后面详细解释)。
其次,计算四分位距(IQR)(我们也将详细说明)。
第三,确定数据集的上限和下限。
最后,使用这些边界来识别异常数据点。
在下面的图片中,右侧的单元格范围将用于存储计算出的这些值。
现在,让我们开始逐步操作。
第一步:计算四分位数
如果我们将数据分成四个相等的部分,每一部分都称为四分位数。 数据集中最低的 25% 的数值构成第一四分位数,接下来的 25% 构成第二四分位数,以此类推。 这一步至关重要,因为最常用的异常值定义是:数据点低于第一四分位数(Q1)1.5 倍的四分位距(IQR),或高于第三四分位数(Q3)1.5 倍的四分位距。 因此,为了确定这些值,我们必须首先计算出四分位数。
Excel 提供了 QUARTILE 函数来计算四分位数。 这个函数需要两个参数:数组和 quart。
=QUARTILE(array, quart)
数组是指你要评估的数据范围。 Quart 是一个数字,代表你想要返回的四分位数(例如,1 代表第一个四分位数,2 代表第二个四分位数,以此类推)。
特别提示:在 Excel 2010 中,微软发布了 QUARTILE.INC 和 QUARTILE.EXC 函数,作为对 QUARTILE 函数的改进。 然而,为了在多个 Excel 版本中保持兼容性,使用 QUARTILE 函数仍然更为稳妥。
现在,让我们回到示例表格。
要计算第一四分位数(Q1),我们可以在单元格 F2 中输入以下公式:
=QUARTILE(B2:B14,1)
当你输入公式时,Excel 会提供 quart 参数的选项列表。
要计算第三四分位数(Q3),我们可以在单元格 F3 中输入类似的公式,但使用数字 3 而不是 1:
=QUARTILE(B2:B14,3)
现在,我们已经得到了四分位数的数据。
第二步:计算四分位距
四分位距(IQR)是指数据集中间 50% 的数值范围。 它的计算方法是用第三四分位数(Q3)减去第一四分位数(Q1)。
在单元格 F4 中,我们使用一个简单的公式:
=F3-F2
现在,我们可以看到计算出来的四分位距。
第三步:确定下限和上限
下限和上限是我们用于判断数据是否为异常值的最小值和最大值。 任何低于下限或高于上限的值都被认为是异常值。
我们通过将 IQR 值乘以 1.5,然后从第一四分位数(Q1)中减去该值,来计算单元格 F5 中的下限:
=F2-(1.5*F4)
请注意:此公式中的括号不是必需的,因为乘法部分会在减法部分之前计算,但它们可以使公式更易于阅读。
为了计算单元格 F6 中的上限,我们再次将 IQR 乘以 1.5,但这次将其添加到第三四分位数(Q3):
=F3+(1.5*F4)
第四步:识别异常值
现在,我们已经准备好了所有基础数据,可以开始识别异常值了——那些低于下限或高于上限的数据点。
我们将使用 OR 函数 来执行此逻辑判断,并通过在单元格 C2 中输入以下公式来显示符合这些标准的值:
=OR(B2<$F$5,B2>$F$6)
然后,我们将这个公式复制到 C3-C14 单元格中。 TRUE 值表示异常值,如你所见,我们的数据中存在两个异常值。
计算平均值时排除异常值
使用 QUARTILE 函数可以帮助我们计算出 IQR,并依据最常用的异常值定义来识别异常值。 然而,当我们需要计算一系列值的平均值,并同时排除异常值时,有一个更快速便捷的函数可供使用。 这种方法虽然不像之前那样明确地识别异常值,但它为我们提供了灵活地处理我们认为可能属于异常值部分的能力。
这个函数叫做 TRIMMEAN,其语法如下:
=TRIMMEAN(array, percent)
数组是指您要计算平均值的数据范围。 百分比是指要从数据集的顶部和底部排除的数据点的百分比(您可以输入百分比值或小数)。
在我们的示例中,我们在单元格 D3 中输入以下公式,以计算平均值并排除 20% 的异常值:
=TRIMMEAN(B2:B14, 0.2)
综上所述,我们介绍了两种不同的处理异常值的方法。 无论你是需要识别它们以满足报告需求,还是将它们从平均值等计算中排除,Excel 都能提供强大的功能来满足您的需求。