輸入數據的時候，如果Excel能提醒你對錯，那要比等匯總出錯了後再返回頭找錯誤要省事得多。這要靠給Excel添加自動審查。許多設計好的Excel工作表往往不止是供給一個人操作和錄入，由於使用者對表內單元格要求輸入的內容熟悉程度不同，往往難免發生這樣那樣的輸入差錯。默認情況下Excel系統對用戶輸入的數據不具備對錯識別功能。如果我們能給Excel裝上一雙“火眼金睛”，讓它自動識別用戶輸入的數據的合法性，錯誤的發生就會降低到最低程度。本文將通過一些實際的操作案例講述自動審查數據的實現方法。
身份證位數輸入的檢查
身份證輸入在Excel表格處理中常常遇到，而且也很容易出錯。實際工作中我們常常見到某個人的身份證少了一位或多了一位的情況，有時很難判斷哪一位出了問題。如果在輸入時系統能提醒，這樣的錯誤一定不會發生。
身份證號碼只能是15位和18位，我們可以通過設置讓Excel具備這方面的審核能力。
假如Excel工作表B列錄入的是身份證號碼，B2是輸入身份證號碼的起始單元格。可以將該列全部選中，單擊“數據”菜單下的“有效性”命令。打開“設置”選項卡（圖1），在“允許”下拉列表中選擇“自定義”，然後在“公式”框中輸入“=AND(COUNTIF(B:B,B2)=1,OR(LEN(B2)=15,LEN(B2)=18))”。該公式檢查B列中與當前數據完全相同的單元格個數，如果返回的結果等於1，則“=COUNTIF(B:B,B2)=1”返回結果TRUE，否則返回結果FALSE。接下來“=OR(LEN(B2)=15,LEN(B2)=18)”中的LEN函數檢查輸入B列B2、B3等單元格的數據長度是否等於15或18，只要三個LEN函數中有一個滿足條件，OR函數就會返回TRUE。當COUNTIF和OR函數同時返回TRUE時，AND函數才能返回TRUE，Excel允許用戶輸入數據。如果單元格中的數據發生了重復，或者輸入的長度不等於15或18，AND函數就會返回FALSE，Excel立刻予以制止（圖2）。
小提示：從這裡例子我們可以觸類旁通，如果輸入B列的是一個其他固定長度的數據（如8位），那麼，只需將上述公式修改為“=AND(COUNTIF(B:B,B2)=1,LEN(B2)=8))”就可以了。
編號前綴正確性自動檢查
有時我們會對來自不同地區的人群或產品信息賦予不同的地區號前綴。為了避免錄入這種類型的數據時候發生輸入不在編號范圍內數據的差錯，可以設置對數據的前綴進行自動檢查檢查。
假如三個地區的產品編號前綴分別為012xxx、017xxx和019xxx，可以按上面介紹的方法選中數據所在的列（假設F1是輸入數據的起始單元格），然後打開“數據有效性”對話框的“設置”選項卡。在“允許”下拉列表中選擇“自定義”，然後在“公式”框中輸入“=OR(LEFT(F1,3)="012",LEFT(F1,3)="017",LEFT(F1,3)="019")”。
上述功能設置完畢以後，只要錄入的考號前綴不是012、017或019，立即出示警示提示。
上述公式的工作原理是這樣的：LEFT從當前單元格（例如F1）中取出三個字符，看它們是否等於012、017或019，只要三個LEFT函數關系中有一個成立，OR函數就會返回TRUE，即允許用戶輸入，否則立即提示超出范圍。
如果輸入更多數量的數據前綴，只須在公式中增加類似“LEFT(F1,3)="012"”這樣的語句就可以了。
限定在有效范圍內輸入數據
錄入的數據一般都有一個合理范圍，但工作表並不知道數據是否超限。例如，在錄入成績時，要求只能輸入規定長度的數據，且大小不能超過一定范圍。如限定只能錄入整數，且小於等於100。但你錄入213這樣的分數系統也不會認為你錯，這時可以用如下方法限定條件。
假設D2是數據輸入的起始單元格，可以單擊“數據”菜單下的“有效性”命令，打開對話框的“設置”選項卡。在“允許”下拉列表中選擇“自定義”，然後在“公式”框內輸入“=AND(D2<=100,OR(LEN(D2)=1,LEN(D2)=2,LEN(D2)=3))”。該公式中的LEN函數檢查輸入D列D2、D3等單元格的數據長度是否等於1、2或3，只要滿足其中任意一個條件，OR函數就會返回TRUE。而“D2<=100”檢查輸入的數據是否小於等於100，當上面兩者同時滿足時AND函數返回TRUE，Excel允許用戶完成輸入。如果單元格中的數據長度不等於1、2或3，或者輸入的數據大於100，AND函數就會返回FALSE，Excel制止用戶輸入數據。
自動判斷無效記錄並制止輸入
對工資表之類的工作表來說，後邊一列數據的存在價值與主要關鍵字有密切關系。例如，如果一條含有“房補”“獎金”的記錄裡面沒有“工號”和“姓名”，“房補”“獎金”就變得沒有意義。我們可以讓工作表對這類記錄自動作出判斷。
假設工作表B列存儲“工號”、C列存儲“姓名”，當B列中的某一單元格為空值時，其右邊的單元格不允許輸入“姓名”。Excel可以采取如下措施加以制止。
打開“數據有效性”對話框的“設置”選項卡，在“允許”下拉列表中選擇“自定義”，然後在“公式”框內輸入“=COUNTA(B2)=1”。“確定”以後如果B列中的某一單元格為空，那麼它右邊的單元格就不能輸入數據，即使輸入了數據Excel也會出面加以制止，直到在B列輸入有效數據才能操作。
輸入順序錯誤的自動提醒
某些情況下，我們對數據錄入的順序有嚴格的要求，比如按日期排列處理，前面的任務沒有處理完，不允許後面的任務，必須按照由小到大的順序錄入，一旦出現輸入違規應當立即停止輸入。如何實現？
選中將要錄入數據的區域或列（如D列），再按上面介紹的方法打開對話框。在“允許”下拉列表中選擇“日期”，在“數據”下拉列表中選擇“大於或等於”，最後在“開始日期”框內輸入“=MAX($D$1:$D1)"（圖3）。
關閉對話框後，如果從D1單元格錄入的數據是“升序”的，Excel就不會警示，否則就會提示操作非法。
如果日期必須按由大到小的順序錄入，只須在“數據”下拉列表中選擇“小於或等於”，在“開始日期”框內輸入“==MINA($D$1:$D1)”（“$D$1”是第一個數據所在的單元格）就可以了（圖4）。
重復數據帶顏色自動提醒
有時候，同一項目的輸入中如果出現重復的數據，可能預示著輸入有錯誤。比如，正在輸入的領獎人姓名上面已經輸入過了，這時可以讓系統用紅色顯示來提醒輸入者判斷。比如，C列出現數據重復，則以紅色提醒用戶注意。
實現方法為：單擊“格式”菜單下的“條件格式”打開對話框，在“條件1”下拉菜單中選擇“公式”，在對話框內輸入公式“=COUNTIF(C:C,C2)<>1”（圖5）。然後單擊“格式”按鈕打開對話框，在其中選擇一種合適的“提醒”方式（例如選擇紅色字體）。此後只要COUNTIF函數在C列中發現與當前單元格相同的數據，即“=COUNTIF(C:C,C2)<>1”成立（為“真”），則當前單元格中的數據就會自動以紅色字體顯示。
本文來自於【系統之家】 www.xp85.com
