如何發現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)的計算結果就是這個差與標準差的倍數。

這個方法基本上可以找出數據中的異常值。如果真有點錯小數點的這種錯誤，一定會被找出來。

不過需要強調的是，異常值並不一定是錯誤值。我們還需要仔細分析異常值的產生原因。有一些就是真實的數值（比如，某個地區就是銷量特別高）。對於那些明顯是錯誤的異常值，可以修改或者刪除。對於真實的異常值，也需要在後續的分析中把它們與其他數值分開來進行分析，這樣才能得到數據的真相。