自己動手找出Excel中的“非法數據”

平淡

在日常工作中我們經常要對ExceI中的數據進行統計，但是由于設置或者輸入時使用格式錯誤，或者粗心大意重復輸入數據，這類看似正確的“非法數據”會導致在后續統計中出現統計錯誤，而這些錯誤僅靠常規的方法很難發現。那么該如何避免非法數據的輸入，并且快速找出這些非法數據并進行正確統計呢？

異常格式一件清理

在日常的報表中，由于對單元格格式進行多個設置，這樣在進行匯總的時候就容易出錯。比如下面的一份報表，B列是產品的銷售額，但是使用求和公式計算后卻發現總數是錯誤（圖1）。

細心的朋友可能發現B4數據有些問題，切換到B4單元格，可以看到其數據類型是“文本”，默認情況下文本數據是不參與計算的，所以導致上述求和結果出錯。這是典型的異常數據，解決的方法自然是將文本數據轉換為常規數據即可。不過如果需要統計的數據很多，僅靠單純肉眼是很難發現異常數據，而且單元格中格式多種多樣，如何快速清除單元格中的眾多格式？

這里可以使用CLEAN函數進行數據格式的刪除，CLEAN是默認清除所有的非打印字符，包括可見的和不可見的，還能清除單元格的設置。只要你覺得ExceI數據有問題，那么就可以使用這個函數進行清除。新建工作表2，將A列的數據復制，在B1處輸入函數“=CLEAN（Sheetl1！B1）”，然后往下填充公式，這樣原來工作表中B列單元格里的各種格式都會被自動清除，現在再進行數據統計就不會出錯了（圖2）。

錯誤數據一目了然

對于部門比較多的公司，常規的子報表都是通過各部門統計人員自行統計，在日常統計輸入中，除了單元格格式設置紊亂外，還出現重復、日期錯誤等常見的錯誤，這些錯誤導致的非法數據也很容易導致最終統計出錯。比如下面的報表，使用求和公式統計時看上去并沒有出現錯誤（圖3）。

不過實際上上述表格中的2016/11/31數據是錯誤的（因為11月并沒有31日），這樣在單純的數據統計上不會出現錯誤，但是在一些匯總如在透視表中使用“季度分組”查看時，此時會出現“選定的區域不能分組”的錯誤提示，因為數據里有不規范的日期格式。對于類似這種固定格式數據，我們可以通過Excel的數據驗證快速找出來。

選中當前工作表的數據列，單擊“數據一數據驗證”，在彈出的窗口中，驗證條件選擇：允許“日期”，數據介于“2016/0I/01→2016/12/31”之間（圖4）。

這樣返回數據列，單擊“數據一數據驗證一圈釋無效數據”，Excel會快速將無效日期格式使用紅圈標識，只要按提示進行正確的更改即可（圖5）。

當然在出現錯誤的時候再來查找什么地方有錯誤，這會給我們的工作帶來很多的麻煩。其實在日常工作中類似的異常數據還有很多，比如在統計員工發放津貼表格中，如果重復輸入某個員工的姓名，會導致最終統計的數據出錯，而這類“軟錯誤”很難在報表中找出來。為了避免這類數據輸錯，我們可以先使用數據驗證將特定列的數據做好提前驗證。比如統計員工姓名，這里要求不能輸入重復姓名。先選中員工姓名列，同上打開數據驗證設置窗口，允許選擇“自定義”，在公式欄輸入“=COUntif（A：A，$A2）=1”（圖6）。

切換到“出錯警告”，按提示設置好出錯樣式、標題和錯誤信息等內容，這樣方便統計人員在輸入時看到警告提示（圖7）。

這樣以后統計人員在輸入員工姓名的時候，如果再次輸入已經輸過的員工姓名，Excel就會彈出出錯提示，用戶無法輸入重復姓名數據（圖8）。

使用數據驗證可以很好地防止用戶輸入錯誤數據，這里主要是要根據實際輸入數據設置好驗證條件。比如要驗證身份證號碼（要求15位或18位），則在公式框中輸入：=or（Ien（$B2）=15，len（$B2）=18），限定輸入本月日期，則起始日期輸入公式=DATE（YEAR（TODAY（）），MONTH（TODAY（）），1），結束日期輸入=DATE（YEAR（TODAY（）），MONTH（TODAY（））+1，0）。由于這里是應用DATE函數設置日期，無論是哪個月份都可以自動對應，再也不會出現上述類似“2016/11/31”的錯誤。

總之在日常工作中，我們會遇到各種各樣“非法數據”的困擾，只要我們掌握好Excel提供的工具，就可以有效地避免這類數據的出現，同時在出現錯誤時可以快速找出錯誤數據。