agwebrunner.com

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

一个简单的例子

在下图中，离群值很容易发现-分配给Eric的值为2和分配给Ryan的值为173。在这样的数据集中，手动找出并处理这些异常值非常容易。

在更大的数据集中，情况并非如此。能够识别异常值并将其从统计计算中删除非常重要-这就是我们将在本文中探讨的方法。

如何在数据中查找离群值

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

1. 计算第一四分位数和第三四分位数（我们稍后将讨论它们）。
2. 评估四分位间距（我们还将在更下方进行解释）。
3. 返回我们数据范围的上限和下限。
4. 使用这些界限来标识偏远的数据点。

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

让我们开始吧。

第一步：计算四分位数

如果将数据划分为四分之一，那么这些集合中的每一个都称为四分位数。范围中数字的最低25％构成第一四分位数，接下来的25％构成第二四分位数，依此类推。我们首先采取这一步骤，因为离群值的最广泛使用的定义是一个数据点位于第一个四分位数以下1.5个四分位数范围（IQR）以上，而位于第三个四分位数以上的1.5个四分位数范围。要确定这些值，我们首先必须弄清楚四分位数是多少。

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

= QUARTILE（数组，夸脱）

这 大批 是您要评估的值的范围。和 夸脱 是代表您希望返回的四分位数的数字（例如，第一个四分位数为1，第二个四分位数为2，依此类推）。

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

让我们返回示例表。

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

= QUARTILE（B2：B14,1）

输入公式时，Excel为夸脱参数提供选项列表。

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

= QUARTILE（B2：B14,3）

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

第二步：评估四分位间距

四分位数范围（或IQR）是数据中值的中间50％。计算为第一四分位数和第三四分位数之间的差。

我们将在单元格F4中使用一个简单的公式，从第三个四分位数中减去第一个四分位数：

= F3-F2

现在，我们可以看到显示了四分位间距。

第三步：返回上下限

下限和上限是我们要使用的数据范围的最小和最大值。任何小于或大于这些绑定值的值都是离群值。

我们将IQR值乘以1.5，然后从Q1数据点中减去该值，以计算单元格F5中的下限：

= F2-（1.5 * F4）

笔记： 该公式中的括号不是必需的，因为乘法部分将在减法部分之前进行计算，但是它们确实使该公式更易于阅读。

要计算单元格F6中的上限，我们将IQR再次乘以1.5，但这一次 添加 到第3季度数据点：

= F3 +（1.5 * F4）

第四步：找出异常值

现在，我们已经完成了所有基础数​​据的设置，现在是时候确定离群数据点了-那些低于下限值或高于上限值的数据点。

我们将使用OR功能执行此逻辑测试，并通过在单元格C2中输入以下公式来显示符合这些条件的值：

= OR（B2 $ F $ 6）

然后，我们将该值复制到C3-C14单元格中。 TRUE值表示异常值，如您所见，我们的数据中有两个。

计算均值时忽略异常值

使用QUARTILE函数，我们可以计算IQR并使用最广泛使用的离群值定义。但是，在计算一系列值的平均平均值而忽略离群值时，可以使用更快捷，更轻松的功能。这种技术不会像以前那样识别异常值，但是它将使我们能够灵活对待可能会视为异常值的部分。

我们需要的函数称为TRIMMEAN，您可以在下面看到其语法：

= TRIMMEAN（数组，百分比）

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

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

= TRIMMEAN（B2：B14，20％）

那里有两个不同的功能可以处理异常值。无论您是要为某些报告需求确定它们还是将它们从诸如平均值之类的计算中排除，Excel都可以满足您的需求。