Excel程序內(nèi)置的驗(yàn)證條件可以解決一些常見的數(shù)據(jù)輸入限制問題，但若想更加靈活地控制數(shù)據(jù)輸入，則需要用公式設(shè)置驗(yàn)證條件。只要你對函數(shù)有足夠的了解，則可以設(shè)計出非常靈活的驗(yàn)證條件，如“限制輸入空格”“限制輸入重復(fù)值”等。本文將用以下示例說明如何應(yīng)用公式設(shè)置數(shù)據(jù)驗(yàn)證條件。

?

一、避免輸入重復(fù)值

用戶在進(jìn)行數(shù)據(jù)錄入時，由于錄入數(shù)據(jù)量較大，可能會發(fā)生重復(fù)錄入的情況。比如在錄入學(xué)生姓名制作成績報表時，為了防止數(shù)據(jù)的錄入重復(fù)，可以設(shè)置當(dāng)數(shù)據(jù)錄入重復(fù)時給出提示或者禁止錄入。

具體步驟為：選中A2:A9單元格區(qū)域，在“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)工具”組中單擊“數(shù)據(jù)驗(yàn)證”按鈕，打開“數(shù)據(jù)驗(yàn)證”對話框 - 選擇“設(shè)置”標(biāo)簽，在“驗(yàn)證條件”欄下設(shè)置“允許”條件為“自定義”，在“公式”欄下的文本框內(nèi)輸入公式“=COUNTIF(A:A,A2)<2” -="">

注意：公式“=COUNTIF(A:A,A2)<>

?

二、限制單元格輸入的產(chǎn)品價格必需包含兩位小數(shù)

本例記錄了蔬菜的單價信息，為了規(guī)范單價的錄入（需要保留兩位小數(shù)），可以使用自定義公式，當(dāng)錄入不是兩位小數(shù)的數(shù)據(jù)時自動彈出提示框。

具體步驟為：選中B2:B20單元格區(qū)域，在“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)工具”組中單擊“數(shù)據(jù)驗(yàn)證”按鈕，打開“數(shù)據(jù)驗(yàn)證”對話框 - 選擇“設(shè)置”標(biāo)簽，在“驗(yàn)證條件”欄下設(shè)置“允許”條件為“自定義”，在“公式”欄下的文本框內(nèi)輸入公式“=LEFT(RIGHT(B2,3),1)="."” - 單擊“確定”按鈕完成設(shè)置。當(dāng)輸入的小數(shù)位數(shù)不是兩位數(shù)時就會自動彈出提示框，重新輸入正確的小數(shù)位數(shù)（兩位小數(shù)）即可。

注意：公式“=LEFT(RIGHT(B2,3),1)="."”，首先使用RIGHT函數(shù)從B2單元格中數(shù)據(jù)的右側(cè)提取3個字符，接著使用LEFT函數(shù)從上步結(jié)果的左側(cè)提取1個字符，判斷其是否是小數(shù)點(diǎn)“.”,如果是則滿足條件，不是則不滿足條件。

?

三、限制輸入空格

手工輸入數(shù)據(jù)時，經(jīng)常會有意或無意地輸入一些空格，這些數(shù)據(jù)如果只是用于查看，有無空格并無大礙，但如果要用于統(tǒng)計查找，如“北京”和“北 京”，則會被當(dāng)作兩個完全不同的對象，這時的空格則為數(shù)據(jù)分析帶來困擾。為了規(guī)范數(shù)據(jù)的錄入，則可以使用數(shù)據(jù)驗(yàn)證限制空格的錄入，一旦有空格錄入就會彈出提示框。

具體步驟為：選中B3:B13單元格區(qū)域，在“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)工具”組中單擊“數(shù)據(jù)驗(yàn)證”按鈕，打開“數(shù)據(jù)驗(yàn)證”對話框 - 選擇“設(shè)置”標(biāo)簽，在“驗(yàn)證條件”欄下設(shè)置“允許”條件為“自定義”，在“公式”欄下的文本框內(nèi)輸入公式“=ISERROR(FIND(" ",B3))” - 單擊“確定”按鈕完成設(shè)置。當(dāng)輸入的文本中間有空格時就會自動彈出提示框，重新輸入正確的文本（不包含空格）即可。

注意：公式“=ISERROR(FIND(" ",B3))”，首先用FIND函數(shù)在B3單元格中查找空格的位置，如果找到返回位置值，如果未找到則返回的是一個錯誤值，然后用ISERROR函數(shù)判斷值是否為錯誤值，如果是則返回TRUE,不是則返回FALSE。本例中當(dāng)結(jié)果為“TRUE”時則說明未找到空格，此時允許輸入，否則則不允許輸入。

?

四、限定單元格內(nèi)必需包括指定內(nèi)容

例如某產(chǎn)品規(guī)格都是以“OMD”開頭的，要求在輸入產(chǎn)品規(guī)格時，只要不是以“OMD”開頭的就自動彈出錯誤提示框，并提示如何才能正確輸入數(shù)據(jù)。

具體步驟為：選中C3:C13單元格區(qū)域，在“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)工具”組中單擊“數(shù)據(jù)驗(yàn)證”按鈕，打開“數(shù)據(jù)驗(yàn)證”對話框 - 選擇“設(shè)置”標(biāo)簽，在“驗(yàn)證條件”欄下設(shè)置“允許”條件為“自定義”，在“公式”欄下的文本框內(nèi)輸入公式“=ISNUMBER(SEARCH("OMD?",C3))” - 切換至“出錯警告”標(biāo)簽，在“輸入無效數(shù)據(jù)時顯示下列出錯警告”欄下的“樣式”下拉列表中選擇“警告”，再分別設(shè)置“標(biāo)題”和“錯誤信息”內(nèi)容即可 - 單擊“確定”按鈕完成設(shè)置。當(dāng)輸入錯誤的產(chǎn)品規(guī)格時，會彈出錯誤警告提示框。

注意：ISNUMBER函數(shù)用于判斷引用的參數(shù)或指定單元格中的值是否為數(shù)字。SEARCH函數(shù)用來返回指定的字符串在原始字符串中首次出現(xiàn)的位置。公式“=ISNUMBER(SEARCH("OMD?",C3))”用于在C3單元格中查找“OMD”，找到后返回其位置，位置值是數(shù)字，所以外層的ISNUMBER函數(shù)的判斷結(jié)果即為真（允許輸入）；如果找不到，SEARCH函數(shù)返回錯誤值，外層的ISNUMBER函數(shù)的判斷結(jié)果即為假（不允許輸入）。

?

五、禁止出庫數(shù)量大于庫存數(shù)

月末要編輯產(chǎn)品庫存表，其中已記錄了上月的結(jié)余量和本月的入庫量。當(dāng)產(chǎn)品要出庫時，顯然出庫量應(yīng)該小于庫存量。為了保證可以及時發(fā)現(xiàn)錯誤，需要設(shè)置數(shù)據(jù)驗(yàn)證，禁止輸入的出庫量大于庫存量。

具體步驟為：選中 E2:E12單元格區(qū)域，在“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)工具”組中單擊“數(shù)據(jù)驗(yàn)證”按鈕，打開“數(shù)據(jù)驗(yàn)證”對話框 - 選擇“設(shè)置”標(biāo)簽，在“驗(yàn)證條件”欄下設(shè)置“允許”條件為“自定義”，在“公式”欄下的文本框內(nèi)輸入公式“=C2+D2>E2” - 切換至“出錯警告”標(biāo)簽，在“輸入無效數(shù)據(jù)時顯示下列出錯警告”欄下的“樣式”下拉列表中選擇“停止”，再設(shè)置“錯誤信息”內(nèi)容 - 單擊“確定”按鈕完成設(shè)置。當(dāng)在E2:E12中輸入的出庫數(shù)量小于庫存數(shù)時允許輸入，當(dāng)在E2:E12中輸入的出庫數(shù)量大于庫存數(shù)時（上月結(jié)余與本月入庫之和），系統(tǒng)則會彈出錯誤警告提示框。

?

六、用有效性設(shè)置保護(hù)公式

我們知道通過設(shè)置工作表保護(hù)的操作可以實(shí)現(xiàn)對表格中公式的保護(hù)，同樣，為了保護(hù)公式結(jié)構(gòu)不被修改，也可以使用數(shù)據(jù)驗(yàn)證功能巧妙實(shí)現(xiàn)。

具體步驟為：打開包含公式的表格后，在“開始”選項(xiàng)卡的“編輯”組中單擊“查找和選擇”下拉按鈕，在打開的下拉列表中選擇“公式”，即可選中表格中所有包含公式的單元格 - 保持單元格的選中狀態(tài)，在“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)工具”組中單擊“數(shù)據(jù)驗(yàn)證”按鈕，打開數(shù)據(jù)驗(yàn)證對話框 - 選擇“設(shè)置”標(biāo)簽，在“驗(yàn)證條件”欄下設(shè)置“允許”條件為“自定義”，在“公式”欄下的文本框內(nèi)輸入0 - 切換至“出錯警告”標(biāo)簽，在“輸入無效數(shù)據(jù)時顯示下列出錯警告”欄下的“樣式”下拉列表中選擇“警告”，再分別設(shè)置“標(biāo)題”和“錯誤信息”內(nèi)容即可 - 單擊“確定”按鈕完成設(shè)置。當(dāng)對含有公式的單元格進(jìn)行編輯修改時，就會彈出警告提示框。