讓Excel幫你自動審查數(shù)據(jù)

清風(fēng)

許多設(shè)計好的Excel工作表往往不止是供給一個人操作和錄入，由于使用者對表內(nèi)單元格要求輸入的內(nèi)容熟悉程度不同，往往難免發(fā)生這樣那樣的輸入差錯。默認(rèn)情況下Excel系統(tǒng)對用戶輸入的數(shù)據(jù)不具備對錯識別功能。

如果我們能給Excel裝上一雙“火眼金睛”，讓它自動識別用戶輸入的數(shù)據(jù)的合法性，錯誤的發(fā)生就會降低到最低程度。本文將通過一些實際的操作案例講述自動審查數(shù)據(jù)的實現(xiàn)方法。

身份證位數(shù)輸入的檢查

身份證輸入在Excel表格處理中常常遇到，而且也很容易出錯。實際工作中我們常常見到某個人的身份證少了一位或多了一位的情況，有時很難判斷哪一位出了問題。如果在輸入時系統(tǒng)能提醒，這樣的錯誤一定不會發(fā)生。

身份證號碼只能是15位或18位，我們可以通過設(shè)置讓Excel具備這方面的審核能力。

假如Excel工作表B列錄入的是身份證號碼，B2是輸入身份證號碼的起始單元格。需要將該列全部選中，單擊“數(shù)據(jù)”菜單下的“有效性”命令。打開“設(shè)置”選項卡（圖1），在“允許”下拉列表中選擇“自定義”，然后在“公式”框中輸入“=AND（COUNTIF（B：B，B2）=1，OR（LEN（B2）=15，LEN（B2）=18））”。

該公式檢查B列中與當(dāng)前數(shù)據(jù)完全相同的單元格個數(shù)，如果返回的結(jié)果等于1，則“=COUNTIF（B：B，B2）=1”返回結(jié)果TRUE，否則返回結(jié)果FALSE。接下來“=OR（LEN（B2）=15，LEN（B2）=18）”中的LEN函數(shù)檢查輸入B列B2、B3等單元格的數(shù)據(jù)長度是否等于15或18，只要三個LEN函數(shù)中有一個滿足條件，OR函數(shù)就會返回TRUE。當(dāng)COUNTIF和OR函數(shù)同時返回TRUE時，AND函數(shù)才能返回TRUE，Excel允許用戶輸入數(shù)據(jù)。如果單元格中的數(shù)據(jù)發(fā)生了重復(fù)，或者輸入的長度不等于15或18，AND函數(shù)就會返回FALSE，Excel立刻予以制止（圖2）。

編號前綴正確性自動檢查

有時我們會對來自不同地區(qū)的人群或產(chǎn)品信息賦予不同的地區(qū)號前綴。為了避免錄入這種類型的數(shù)據(jù)時發(fā)生輸入不在編號范圍內(nèi)數(shù)據(jù)的差錯，可以設(shè)置對數(shù)據(jù)的前綴進(jìn)行自動檢查。

假如三個地區(qū)的產(chǎn)品編號前綴分別為012xxx、017xxx和019xxx，可以按上面介紹的方法選中數(shù)據(jù)所在的列（假設(shè)F1是輸入數(shù)據(jù)的起始單元格），然后打開“數(shù)據(jù)有效性”對話框的“設(shè)置”選項卡。在“允許”下拉列表中選擇“自定義”，然后在“公式”框中輸入“=OR（LEFT（F1，3）="012"，LEFT（F1，3）="017"，LEFT（F1，3）="019"）”。

上述功能設(shè)置完畢以后，只要錄入的考號前綴不是012、017或019，立即顯示警示提示。

上述公式的工作原理是這樣的：LEFT從當(dāng)前單元格（例如F1）中取出三個字符，看它們是否等于012、017或019，只要三個LEFT函數(shù)關(guān)系中有一個成立，OR函數(shù)就會返回TRUE，即允許用戶輸入，否則立即提示超出范圍。

如果輸入更多數(shù)量的數(shù)據(jù)前綴，只需在公式中增加類似“LEFT（F1，3）="012"”這樣的語句就可以了。

限定在有效范圍內(nèi)輸入數(shù)據(jù)

錄入的數(shù)據(jù)一般都有一個合理范圍，但工作表并不知道數(shù)據(jù)是否超限。例如，在錄入成績時，要求只能輸入規(guī)定長度的數(shù)據(jù)，且大小不能超過一定范圍，例如限定只能錄入整數(shù)，且小于等于100。但你錄入213這樣的分?jǐn)?shù)。系統(tǒng)也不會認(rèn)為你錯，這時可以用如下方法限定條件。

假設(shè)D2是數(shù)據(jù)輸入的起始單元格，可以單擊“數(shù)據(jù)”菜單下的“有效性”命令，打開對話框的“設(shè)置”選項卡。在“允許”下拉列表中選擇“自定義”，然后在“公式”框內(nèi)輸入“=AND（D2<=100，OR（LEN（D2）=1，LEN（D2）=2，LEN（D2）=3））”。

該公式中的LEN函數(shù)檢查輸入D列D2、D3等單元格的數(shù)據(jù)長度是否等于1、2或3，只要滿足其中任意一個條件，OR函數(shù)就會返回TRUE。而“D2<=100”檢查輸入的數(shù)據(jù)是否小于等于100，當(dāng)上面兩者同時滿足時AND函數(shù)返回TRUE，Excel允許用戶完成輸入。如果單元格中的數(shù)據(jù)長度不等于1、2或3，或者輸入的數(shù)據(jù)大于100，AND函數(shù)就會返回FALSE，Excel會制止用戶輸入數(shù)據(jù)。

自動判斷無效記錄并制止輸入

對工資表之類的工作表來說，后邊一列數(shù)據(jù)的存在價值與主要關(guān)鍵字有密切關(guān)系。例如，如果一條含有“房補”“獎金”的記錄里面沒有“工號”和“姓名”，“房補”“獎金”就變得沒有意義。我們可以讓工作表對這類記錄自動作出判斷。

假設(shè)工作表B列存儲“工號”、C列存儲“姓名”，當(dāng)B列中的某一單元格為空值時，其右邊的單元格不允許輸入“姓名”。利用Excel可以采取如下措施加以制止。

打開“數(shù)據(jù)有效性”對話框的“設(shè)置”選項卡，在“允許”下拉列表中選擇“自定義”，然后在“公式”框內(nèi)輸入“=COUNTA（B2）=1”。確定以后如果B列中的某一單元格為空，那么它右邊的單元格就不能輸入數(shù)據(jù)，即使輸入了數(shù)據(jù)Excel也會出面加以制止，直到在B列輸入有效數(shù)據(jù)才能操作。

輸入順序錯誤的自動提醒

某些情況下，我們對數(shù)據(jù)錄入的順序有嚴(yán)格的要求，比如按日期排列處理，前面的任務(wù)沒有處理完，不允許后面的任務(wù)，必須按照由小到大的順序錄入，一旦出現(xiàn)輸入違規(guī)應(yīng)當(dāng)立即停止輸入。如何實現(xiàn)？

選中將要錄入數(shù)據(jù)的區(qū)域或列（如D列），再按上面介紹的方法打開對話框。在“允許”下拉列表中選擇“日期”，在“數(shù)據(jù)”下拉列表中選擇“大于或等于”，最后在“開始日期”框內(nèi)輸入“=MAX（$D$1：$D1）”（圖3）。

關(guān)閉對話框后，如果從D1單元格錄入的數(shù)據(jù)是“升序”的，Excel就不會警示，否則就會提示操作非法。

如果日期必須按由大到小的順序錄入，只需在“數(shù)據(jù)”下拉列表中選擇“小于或等于”，在“開始日期”框內(nèi)輸入“==MINA（$D$1：$D1）”（“$D$1”是第一個數(shù)據(jù)所在的單元格）就可以了（圖4）。

重復(fù)數(shù)據(jù)帶顏色自動提醒

有時候，同一項目的輸入中如果出現(xiàn)重復(fù)的數(shù)據(jù)，可能預(yù)示著輸入有錯誤。

比如，正在輸入的領(lǐng)獎人姓名上面已經(jīng)輸入過了，這時可以讓系統(tǒng)用紅色顯示來提醒輸入者判斷。比如，C列出現(xiàn)數(shù)據(jù)重復(fù)，則以紅色提醒用戶注意。

實現(xiàn)方法為：單擊“格式”菜單下的“條件格式”打開對話框，在“條件1”下拉菜單中選擇“公式”，在對話框內(nèi)輸入公式“=COUNTIF（C：C，C2）<>1”（圖5）。然后單擊“格式”按鈕打開對話框，在其中選擇一種合適的“提醒”方式（例如選擇紅色字體）。此后只要COUNTIF函數(shù)在C列中發(fā)現(xiàn)與當(dāng)前單元格相同的數(shù)據(jù)，即“=COUNTIF（C：C，C2）<>1”成立（為“真”），則當(dāng)前單元格中的數(shù)據(jù)就會自動以紅色字體顯示。