減少錯誤，Excel數據校驗有辦法

白領在做Excel表格時，最容易出現的錯誤，就是數據輸入錯誤。可人不是機器，尤其是面對枯燥，且沒有什么意義的數據時，輸入錯誤、缺位或多位，是最容易出現的問題。那么，如何減少輸入錯誤的問題呢？對數據進行校驗是個不錯的辦法。

使用數據有效性進行校驗

其實，很多數據本身就有天然的限制，身高13米、體重650kg、年齡200歲，這樣的人是不可能存在的，因此在Excel表格中輸入數據時，加入一些限制條件，就可以將不少輸入錯誤，如常見的多輸入一位或少輸入一位等排除在外。

其實Excel已經為用戶準備了數據有效性的校驗方式，只要打開“菜單→數據→數據有效性”，就可以設置對輸入數據進行有效性驗證（圖1）。用戶可以根據需求，在整數、小數、序列、日期、時間、文本長度和自定義中，設置不同的校驗條件，如輸入數據的范圍，數據要小于或大于某個特定數據（圖2），或者是不能出現某些數據等。同時還可以在出錯警示中，設定輸入超范圍后的提示信息，在合理設置后，就可以在很大程度上避免少輸入小數點，或多/少輸入一位等常見的輸入錯誤。

自定義校驗

當然，現成的校驗條件，在很多時候是無法滿足需求的，因此Excel還提供了自定義校驗選項。在輸入數據時，用戶常犯的一個錯誤就是看錯行，這樣會在兩行輸入一樣的數據，同時一些數據是有唯一性的，如身份證、工號、駕駛證號等，這樣用戶就可以通過驗證這一數據在這一列中是否唯一的，以避免輸入錯誤和重復輸入，其方法同樣是打開“菜單→數據→數據有效性”，選擇自定義數據（圖3）。隨后，在公式中輸入“=COUNTIF（D：D，D1）=1”（其中，D為當前列，D1為當前單元格），點擊確定后，拖動單元格下方的小點，讓這一公式在所在列中應用。

同時，在自定義公式中，還可使用多條件設置，如在身份證的數據驗證中，輸入的公式為“=AND COUNTIF（D：D，D1）=1，OR LEN（（D1=15）， LEN（D1=18））”（其中，D為當前列，D1為當前單元格），除了可以校驗數據的唯一性，還可對數據的位長進行檢測，如數據不是15位（對應老版身份證）和18位，則說明數據錯誤。

身份證校驗

像身份證這種一大串無意義的數字，輸入起來是很容易出錯的。更糟糕的是，還有一些人會出于各種目的，隨便報出一串數字作為身份證號碼，那么如何避免這樣的問題呢？

實際上，我們的身份證號碼本身就自帶校驗機制，其最后一位是校驗碼，而這一校驗碼是通過將身份證號碼前17位數分別乘以不同的系數：從第一位到第十七位的系數分別為：7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 。隨后，將這17位數字和系數相乘的結果相加后再除以11，看余數是多少;由于余數只可能有0 1 2 3 4 5 6 7 8 9 10這11個數字，此時，再做一個變換，分別對應的最后一位身份證的號碼為1 0 X 9 8 7 6 5 4 3 2，知道這一原理，我們只要在Excel中，使用函數來代替這一計算，就可以進行簡單的校驗。這里直接給出公式“=IF（RIGHT（A1）=TEXT（CHOOSE（MOD（SUMPRODUCT（（——MID（A1，COLUMN（A1：Q1），1））*{7，9，10，5，8，4，2，1，6，3，7，9 ，10，5，8，4，2}），11）+1，1，0，“X”，9，8，7，6，5，4，3，2），0），“校驗正確”，“校驗錯誤”）”，其中，A1為身份證輸入的單元格，此時不能通過Excel的數據有效性校驗來實現，而是要新開一列，輸入這一公式后，再下拉，讓這一公式在所在列中應用，當用戶輸入的身份證號碼符合正確編碼規則時，此列會顯示驗證正確，而一旦不符合這一規則，就會顯示驗證錯誤。

這樣就可以在相當程度上避免輸入錯誤，還可以消除假身份證號碼的影響。當然，這樣的校驗還比較粗略，對于“精致”的假身份證號碼無效，當要求較高時，則需要通過其他手段進行驗證。