如何（以及为什么）在Excel中使用异常值函数

离群值是显着高于或低于数据中大多数值的值。使用 Excel 分析数据时，异常值可能会扭曲结果。例如，数据集的平均值可能真正反映您的值。Excel 提供了一些有用的函数来帮助管理异常值，让我们来看看。

一个简单的例子

在下图中，异常值相当容易发现——分配给 Eric 的值为 2，分配给 Ryan 的值为 173。在这样的数据集中，很容易手动发现和处理这些异常值。

在更大的数据集中，情况并非如此。能够识别异常值并将它们从统计计算中删除是很重要的——这就是我们将在本文中讨论的内容。

如何在数据中查找异常值

要查找数据集中的异常值，我们使用以下步骤：

计算第 1 和第 3 个四分位数（稍后我们将讨论这些四分位数）。评估四分位数范围（我们还将进一步解释这些）。返回我们数据范围的上限和下限。使用这些界限来识别外围数据点。

下图中显示的数据集右侧的单元格范围将用于存储这些值。

让我们开始吧。

第一步：计算四分位数

如果将数据分成四等份，则每组都称为四分位数。范围内最低 25% 的数字构成第一个四分位数，接下来的 25% 构成第二个四分位数，依此类推。我们首先采取这一步骤，因为最广泛使用的异常值定义是一个数据点，该数据点低于第 1 个四分位数超过 1.5 个四分位距 (IQR)，高于第 3 个四分位数 1.5 个四分位距。要确定这些值，我们首先必须弄清楚四分位数是多少。

Excel 提供了一个 QUARTILE 函数来计算四分位数。它需要两条信息：数组和夸脱。云表格

=QUARTILE（数组，夸脱）

该数组是您正在评估的值的范围。夸脱是一个代表您希望返回的四分位数的数字（例如，1 表示第一个四分位数，2 表示第二个四分位数，依此类推）。

注意：在 Excel 2010 中，Microsoft 发布了 QUARTILE.INC 和 QUARTILE.EXC 函数作为对 QUARTILE 函数的改进。跨多个 Excel 版本工作时，QUARTILE 更向后兼容。

让我们回到我们的示例表。

要计算第一个四分位数，我们可以在单元格 F2 中使用以下公式。

=四分位数（B2：B14,1）

当您输入公式时，Excel 会提供 quart 参数的选项列表。

要计算第三个四分位数，我们可以在单元格 F3 中输入与前一个类似的公式，但使用三而不是一。

=四分位数（B2：B14,3）

现在，我们已经在单元格中显示了四分位数数据点。

第二步：评估四分位距

四分位距（或 IQR）是数据中值的中间 50%。它被计算为第一个四分位数和第三个四分位数之间的差值。

我们将在单元格 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（数组，百分比）

该数组是您要平均的值的范围。百分比是要从数据集的顶部和底部排除的数据点的百分比（您可以将其输入为百分比或十进制值）。

我们在示例中将以下公式输入到单元格 D3 中，以计算平均值并排除 20% 的异常值。

=TRIMMEAN(B2:B14, 20%)

有两种不同的函数来处理异常值。无论您是想为某些报告需求识别它们还是将它们从平均值等计算中排除，Excel 都具有满足您需求的功能。

最后，教大家一个简单拖拽创建表单和生成报表的方法。使用简道云表单在线创建“云表格”，通过简单拖拽，即可制作出一个个柱形图、折线图、饼图等图表，1分钟实现数据可视化分析！非常方便~