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