Excel数据中的错误之异常值——万一敲错了小数点位置怎么办?

Excel数据中的错误之异常值——万一敲错了小数点位置怎么办?

首页休闲益智千万别敲错更新时间:2024-07-28

我们,让Excel变简单

相信很多同学都很了解,我们处理的数据中隐藏着很多错误,这些错误会导致我们后续的分析中产生错误的结果,后果很严重。今天我给大家介绍的就是其中一种数据错误:点错了小数点位置。

比如,命名是123.45,结果你就是输入成为1234.5,结果相差10倍。

当然,这种错误不一定是因为手误,敲错了小数点位置造成的,还可能是由于使用了不同单位造成的。比如,其余数据都是人民币,结果有一个数据是使用美元计算的,从数值上就相差了6倍。

在统计上,这样的数值叫做“异常值”,意思是异常大或者异常小的值。在很多情况下,我们需要把这些值找出来,并且在统计中把它们剔除出去。

如何找到异常值

我们先直接来看在Excel中如何发现异常值。假设我们的数据如下图:

在C列中,所有的数量都是1000-3000之间,但是C8中的值是18382.9,比其他值大了一个数量级。这个值就被称为异常值。

当然,我们不能依靠肉眼发现这样的异常值。我们可以使用公式来实现:

=ABS(C3-AVERAGE($C$3:$C$18))/STDEV($C$3:$C$18)

在D列添加辅助列,在D3中输入上面的公式:

填充整列后,得到结果:

我们发现,对于C8中的那个异常值,D8公式的计算结果也明显大于其他的计算结果。

在统计上,我们一般把这个公式的计算结果大于3的都称为异常值。所以,只要进行筛选,就可以把异常值都找出来了。

公式的解释

这个方法是基于正态分布的原理(关于正态分布的详细介绍及应用,我们在以后专门为大家详细介绍)。在现实世界中,大部分的数据是符合正态分布的。而正态分布的一个特征就是超过99%的数值与均值的差小于3倍的标准差。所以凡是与均值的差大于3倍的标准差的那些数值就可以被称为异常值。

我们的公式就是计算这个差的。

公式:AVERAGE($C$3:$C$18)是计算平均值的。

公式:C3-AVERAGE($C$3:$C$18)是计算数值与平均值的差。

公式:ABS(C3-AVERAGE($C$3:$C$18))是计算这个差的绝对值(因为差有正负)

公式:STDEV($C$3:$C$18)是计算标准差的。(标准差的概念我们在以后详细介绍)

公式:ABS(C3-AVERAGE($C$3:$C$18))/STDEV($C$3:$C$18)的计算结果就是这个差与标准差的倍数。

总结

这个方法基本上可以找出数据中的异常值。如果真有点错小数点的这种错误,一定会被找出来。

不过需要强调的是,异常值并不一定是错误值。我们还需要仔细分析异常值的产生原因。有一些就是真实的数值(比如,某个地区就是销量特别高)。对于那些明显是错误的异常值,可以修改或者删除。对于真实的异常值,也需要在后续的分析中把它们与其他数值分开来进行分析,这样才能得到数据的真相。

END

关注ExcelEasy

关于Excel的一切问题,你都可以在这里找到答案

查看全文
大家还看了
也许喜欢
更多游戏

Copyright © 2024 妖气游戏网 www.17u1u.com All Rights Reserved