Excel数据清洗之十三 异常值

什么是异常值

异常值是一个统计学上的名词，指的是样本中的一些数值明显偏离其余数值。比如，

上图中我们统计了人群中一个样本的身高数据。从图上看，很明显，大部分数据都是以厘米为单位，只有第6个数据是以米为单位。这样我们在分析数据时就会产生较大的偏差，比如算平均身高。

异常值是怎么产生的

异常值的产生有多种原因。最常见的是下面几个：

1. 多个来源的数据单位不同
   例如，多人统计的身高数据，有的人单位用厘米，有的人单位用米。多个分公司提交的报表，有的销售额用人民币，有的销售额用美元。销量统计上，有的用“件”，有的用“箱”

2. 不同时期和场景做的报表合并在一起，但是量级不同
   例如，饮料销量统计中，一份报表用的是毫升，另外一份用的是升。

3. 手工录入造成的手误
   例如，小数点位置放错了，或者多写（少写）一个0

4. 数据中正常产生的。
   例如，统计某个班的同学毕业2年的工资，大部分人是10000以下，只有一个同学因为进了自己家的工资做了高管，工资是50000。尽管是异常值，但是确实真实的数据。

上面这些不同的异常值来源中，最危险的是手误，因为不容易察觉。

第四个来源告诉我们，异常值不一定是错误，有些异常值是数据中真实存在的。

清洗异常值

首先我们需要发现异常值。

异常值有一个比较常用且在Excel中比较容易实现的方法。这个方法中异常值是这么定义的：

一组数值中与平均值的差超过3倍标准差的数值我们称为异常值。

这里有一个“标准差”的概念，很多人可能会比较陌生。不过没关系，你只要知道Excel中有一个函数：STDEV是用来计算一组数据的标准差就可以了。

这样，我们就可以在数据中添加一个辅助列，使用下面的公式：

=ABS(D3-AVERAGE($D$3:$D$14))/STDEV($D$3:$D$14)

这个公式中AVERAGE是计算身高列的平均值，然后计算D3和这个平均值的差，ABS是计算这个差的绝对值，然后除以身高列的标准差STDEV，这样我们就计算出每一个身高与平均值相差的标准差的倍数

填充到整个辅助列，

可以看到，第六个身高数据明显符合异常值的定义。

如果数据比较多，可以筛选辅助列中所有大于等于3的数据，

一旦找到了异常值，接下来需要做两个判断：

1. 异常值是否是错误值
   有一些异常值可以看作错误值。比如我们这个身高数据中的异常值，明显是单位不一致造成的。而前面举的班级同学工资的调查数据中，异常值就是数据的本来面目

2. 如何处理异常值
   对于单位或量级不一致造成的异常值，以及手误造成的异常值，当然需要修改过来。对于数据中本来就有的异常值，还是要看情况，如果要计算平均值或者趋势分析等，就要删掉这些异常值，而如果是汇总求和，那么可以保留这些数据，因为并不会对结果造成偏差，删掉它们反而不能客观反映数据的真实情况。

总结及其他

很多人不注意异常值的清洗。实际上，如果是多个来源的数据，或者是手工录入的数据，产生异常值还是很普遍的。如果数据量不是特别多，通过筛选就可以发现（比如量级的不同造成的异常值）。有时也可以通过简单的MAX和MIN函数来发现这些异常值。如果数据量较多，就可以使用本文介绍的方法。

如果你的数据比较复杂，这个任务可能很艰巨，你可以关注下面的公众号：ExcelEasy寻找更多方法和技巧，或者使用我们的数据清洗服务，让我们来帮助你完成这个工作。