作者: andysky
報表錄入人員每天面對大量數據錄入，難保不出現一次疏忽。不管數據重要性如何，報表錯誤總會給工作帶來負面影響。 那么，除了錄入人員自身輸入時目測外還有更簡潔高效之法么？ 答案是肯定的。wps表格的“數據有效性”功能可以為您提供便利，有效阻止無效輸入，從而提升數據的準確性和制表速度。 “數據有效性”功能眾多，限于篇幅，專為您講解以下內容，大家可以舉一反三，解決更多類似問題。
1.控制成績表只能輸入數值
2.限制手機號只能輸入阿拉拍數字
3.限制指定位數只能是數字
4.限制用戶有輸入字母
5.限制輸入電話號碼和手機
6.根據前單元格字符決定是否輸入
7.控制輸入1---10000之間的質數
注：為了方便您學習，請先下載本文中所需的ET文檔。
一:控制成績表只能輸入數值
大家知道,學生成績是用數字表示的,且一般在0-100之間。那么只要掌握這個規律進對之進行相應的限制則成績錄入時則可防范出錯（例如輸入小數點變成了逗號不利用匯總或者數據超過100分等等）。
步驟1.先看一個簡易的成績表（見圖1），先選中成績區B2：B11，打開菜單“數據”|“有效性”。
圖1 成績表 步驟2.在設置“數據有效性”|“條件”|“允許”處選擇“自定義”；在公式處輸入=ISNUMBER(B2)*AND(B2<=100,B2>=0)（見圖2）。
公式含義：ISNUMBER(B2)表示必須是數字，AND(B2<=100,B2>=0)表示數據必須在0-100之間；兩個條件中用*號連接表示必須同時滿足兩個條件。
圖2 數據有效性 步驟3.在出錯警告選項卡之樣式選擇“停止”，“錯誤信息”處輸入“ 你輸入了非數字或者不在0--100范圍中，請重新輸入?！?，點確定。
測試：在B2：B11區域輸入大于100或者小于0或者“ABC”等等數據看，系統將彈出提示并阻止您的輸入。從而確保成績錄入的范圍正確性（見圖3）。
圖3 錯誤提示 二:限制手機號只能輸入阿拉拍數字
某單元格用于存放手機號碼，為了防范輸入錯誤，同樣可以利用數據有效性進行相應的約束。手機號碼的特點是:每一個字符都是阿拉伯數字，不包括小數點,這與成績分數不同；位數為11位。對手機號每個字符都進行檢測，需要用到數組運算，而WPS2005表格的數據有效性公式中不支持數組運算，所以不能像前例一樣直接在有效性公式窗口輸入公式。而是借助輔助單元格，同時打開迭代計算來達到目的。
步驟1.打開菜單“工具”|“選項”|“重新計算”，按以下方式設置（見圖4）。
圖4 輸入設置 步驟2.本例手機號碼存于C8單元格，則將D8做為輔助單元格格，在其中輸入公式： =AND(NOT(ISERROR(FIND(MID(C8,ROW(INDIRECT("1:"&LEN(C8))),1),"0123456789"))),LEN(C8)=11)
公式含義： NOT(ISERROR(FIND(MID(C8,ROW(INDIRECT("1:"&LEN(C8))),1),"0123456789")))表示每一位字符必須是阿拉伯數字；LEN(C8)=11表示必須為11位。當然也可自己再加條件，例如字符“13”開始之類。
步驟3.開啟菜單“數據”|“有效性”|“有效性條件”，在“允許”處選擇“自定義”；在公式處輸入“=D8=TRUE”，并在出錯警告選項卡輸入信息“你輸入的不是阿拉伯數字或者不是11位,請重新輸入!”
測試：在單元格中輸入一個錯誤的號碼“I3512345566”，已被系統阻止（見圖5）。
圖5 錯誤提示 三：限制指定位數只能是數字
與前兩例不同，本例可以自定義從某位數開始某位數結束限制為數字。
先看實例（見圖6），起始位和結束位單元格可以隨意定義，只要結束位不小于起始位即可。目的是設置完后手機型號單元格的指定位數只能是阿拉伯數字，否則阻止輸入。
圖6 實例 步驟1.仍然開啟迭代計算
步驟2.手機型號下面單元格做為輔助單元格，輸入公式：=OR(ISERROR(FIND(MID(B6,ROW(INDIRECT(C6&":"&D6)),1),"0123456789")))=FALSE
公式含義：利用數組運算查找指定字符是否位于“0123456789”，有一個在范圍之外則返回邏輯值FALSE.
步驟3.開啟菜單“數據”|“有效性”|“有效性條件”，在“允許”處選擇“自定義”；在公式處輸入=B7=TRUE；出錯警告處之樣式選擇停止，再輸入提示信息：“你輸入的數據指定位數不是數字,請重新輸入”。
測試：在單元格輸入“諾基亞-831”，系統立即阻止（見圖7）。
圖7 系統阻止 四：限制用戶有輸入字母
在單元格中輸入英文單詞時，也可以用數據有效性進行限制。
步驟1.仍然開啟迭代計算
步驟2.本例限制目標單元格為D3，以D4單元格為輔助，輸入公式： =COUNT(MATCH(CODE(UPPER(MID(D3,ROW(INDIRECT("1:"&LEN(D3))),1))),ROW(INDIRECT("65:90")),))=LEN(D3),見圖8。
公式含義：利用數組運算逐一對單元格字符轉換成ANSII字符集之數字代碼，并計算其個數，再與單元格字符長度進行比較，若相同則表示符合要求。
圖8 單詞練習 步驟3.開啟菜單“數據”|“有效性”|“條件”，在“允許”處選擇“自定義”，在公式窗口輸入=D4=TRUE，關添加阻止信息。
測試：在D3輸入“l0ve”(次字符為數字0)，系統立即阻止輸入。
五：限制輸入電話號碼和手機號
在一個電話簿中，可以存放電話號碼和手機號碼，格式分別為0756-1234567和13512345678.利用數據有效性仍然可以有效性的進行檢測，這兩種格式以外的數據阻止輸入。先看看工作表數據（見圖9）。
圖9 工作表數據 步驟1.選中B2：B11，將之單元格格式設為“文本”。
步驟2.開啟菜單“數據”|“有效性”|“有效性條件”，在“允許”處選擇“自定義”；在公式處輸入：=OR((LEN(B2)=11)*ISNUMBER(--B2)*LEFT(B2,2)=13,(LEN(B2)=12)*(MID(B2,5,1)="-"))
公式含義：(LEN(B2)=11)*ISNUMBER(--B2)*LEFT(B2,2)=13用于限制手機號碼：(LEN(B2)=11)表示必須是11位，ISNUMBER(--B2)表示必須是數字，LEFT(B2,2)表示以13開頭（可以自行修改）；(LEN(B2)=12)*(MID(B2,5,1)="-")用于限制電話號碼必須是12位，且第5位是“-”。
步驟3.設置出錯時之警告信息：“你輸入的不是正確的手機或者電話號碼,請重新輸入!”。
測試：在區域任意單元格輸入10位數字1351234567試試，立即被系統阻止（見10）。
圖10 系統阻止 六：根據前單元格字符決定是否輸入
說明:為了表示對少數民族學生的優待,根據學校提供的學生學習期間操行分進行高考加分.范圍在1到20分之間，漢族學生不能加分。用數據有效性對此類事件也可以進行有效性檢測。
先看看單元格數據（見圖11）。
圖11 數據表格 步驟1.選中D2：D11，打開菜單“數據”|“有效性”|“有交性條件”，選擇自定義。
步驟2.在公式處輸入=(C2<>"漢族")*(D2>=1)*(D2<=20)。
公式含義：前單元格非漢族且大于等于1、小于等于20.
步驟3.在出錯警告處輸入信息“該生非少數民族或者加分不在1-20分以內.請重新輸入?！?
測試：在漢族學生后面輸入任意字符或者在其它民族學生之加分單元格格輸入21，系統立即阻止輸入（見圖12）。
圖12 系統阻止
七：控制輸入1---10000之間的質數
學校常常需要計算質數（質數即只能被除1和自身整除之數字）。
本例則限制單元格只能輸入1---10000之間之數字且必須是質數。
步驟1.本例中限制對象為A2，選中單元格A2（見圖13）。
圖13 輸入表格 步驟2.打開菜單“數據”|“有效性”|“有交性條件”，選擇自定義。在公式處輸入： =AND(B2<>1,B2<=10000,OR(B2<4,PRODUCT(MOD(B2,ROW(INDIRECT("2:"&INT(B2^0.5)))))))
步驟3.在出錯警告處樣在式選擇停止，輸入信息：“您輸入的不是質數或者超過10000，請重新輸入！”
測試：在B2輸入1、4、10001等等數據時，系統立即阻止輸入。
結語：數據有效性有一個強大的工具，將它配合函數公式能產生很大的作用，除上述數據控制外，還具有以下功能：
限制指定數值大小的整數；
限制指定字符長度的整數；
限制指定大小的小數；
產生下拉菜單；
限制指定范圍的日期和時間；
限制指定長度字符；
限制輸入指定姓氏之人名；
限制輸入指定省下所屬市名；
達成選擇時提示；等等等等。
其中最大功能在于自定義允許條件為自定義，它可以配合函數產生無窮的變化，達成您各種需求。數據有效性也有它自身限制，使用時需要注意。即它只自限制手動輸入字符，無法防范粘貼數據。所以對需要限制輸入字符之單元格只能手動輸入，否則會刪除有效性信息。
