減少錯(cuò)誤，Excel數(shù)據(jù)校驗(yàn)有辦法

白領(lǐng)在做Excel表格時(shí)，最容易出現(xiàn)的錯(cuò)誤，就是數(shù)據(jù)輸入錯(cuò)誤?？扇瞬皇菣C(jī)器，尤其是面對(duì)枯燥，且沒有什么意義的數(shù)據(jù)時(shí)，輸入錯(cuò)誤、缺位或多位，是最容易出現(xiàn)的問題。那么，如何減少輸入錯(cuò)誤的問題呢？對(duì)數(shù)據(jù)進(jìn)行校驗(yàn)是個(gè)不錯(cuò)的辦法。

使用數(shù)據(jù)有效性進(jìn)行校驗(yàn)

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

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

自定義校驗(yàn)

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

同時(shí)，在自定義公式中，還可使用多條件設(shè)置，如在身份證的數(shù)據(jù)驗(yàn)證中，輸入的公式為“=AND COUNTIF（D：D，D1）=1，OR LEN（（D1=15）， LEN（D1=18））”（其中，D為當(dāng)前列，D1為當(dāng)前單元格），除了可以校驗(yàn)數(shù)據(jù)的唯一性，還可對(duì)數(shù)據(jù)的位長(zhǎng)進(jìn)行檢測(cè)，如數(shù)據(jù)不是15位（對(duì)應(yīng)老版身份證）和18位，則說明數(shù)據(jù)錯(cuò)誤。

身份證校驗(yàn)

像身份證這種一大串無(wú)意義的數(shù)字，輸入起來(lái)是很容易出錯(cuò)的。更糟糕的是，還有一些人會(huì)出于各種目的，隨便報(bào)出一串?dāng)?shù)字作為身份證號(hào)碼，那么如何避免這樣的問題呢？

實(shí)際上，我們的身份證號(hào)碼本身就自帶校驗(yàn)機(jī)制，其最后一位是校驗(yàn)碼，而這一校驗(yàn)碼是通過將身份證號(hào)碼前17位數(shù)分別乘以不同的系數(shù)：從第一位到第十七位的系數(shù)分別為：7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 。隨后，將這17位數(shù)字和系數(shù)相乘的結(jié)果相加后再除以11，看余數(shù)是多少;由于余數(shù)只可能有0 1 2 3 4 5 6 7 8 9 10這11個(gè)數(shù)字，此時(shí)，再做一個(gè)變換，分別對(duì)應(yīng)的最后一位身份證的號(hào)碼為1 0 X 9 8 7 6 5 4 3 2，知道這一原理，我們只要在Excel中，使用函數(shù)來(lái)代替這一計(jì)算，就可以進(jìn)行簡(jiǎn)單的校驗(yàn)。這里直接給出公式“=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），“校驗(yàn)正確”，“校驗(yàn)錯(cuò)誤”）”，其中，A1為身份證輸入的單元格，此時(shí)不能通過Excel的數(shù)據(jù)有效性校驗(yàn)來(lái)實(shí)現(xiàn)，而是要新開一列，輸入這一公式后，再下拉，讓這一公式在所在列中應(yīng)用，當(dāng)用戶輸入的身份證號(hào)碼符合正確編碼規(guī)則時(shí)，此列會(huì)顯示驗(yàn)證正確，而一旦不符合這一規(guī)則，就會(huì)顯示驗(yàn)證錯(cuò)誤。

這樣就可以在相當(dāng)程度上避免輸入錯(cuò)誤，還可以消除假身份證號(hào)碼的影響。當(dāng)然，這樣的校驗(yàn)還比較粗略，對(duì)于“精致”的假身份證號(hào)碼無(wú)效，當(dāng)要求較高時(shí)，則需要通過其他手段進(jìn)行驗(yàn)證。