自己動手找出Excel中的“非法數(shù)據(jù)”

平淡

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

異常格式一件清理

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

細心的朋友可能發(fā)現(xiàn)B4數(shù)據(jù)有些問題，切換到B4單元格，可以看到其數(shù)據(jù)類型是“文本”，默認情況下文本數(shù)據(jù)是不參與計算的，所以導致上述求和結果出錯。這是典型的異常數(shù)據(jù)，解決的方法自然是將文本數(shù)據(jù)轉換為常規(guī)數(shù)據(jù)即可。不過如果需要統(tǒng)計的數(shù)據(jù)很多，僅靠單純?nèi)庋凼呛茈y發(fā)現(xiàn)異常數(shù)據(jù)，而且單元格中格式多種多樣，如何快速清除單元格中的眾多格式？

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

錯誤數(shù)據(jù)一目了然

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

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

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

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

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

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

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

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

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