哈嘍！小伙伴們大家好！上期我們主要講了用系列做下拉菜單確保數(shù)據(jù)規(guī)范的操作。今天我們主要講數(shù)據(jù)驗(yàn)證的其他操作，如數(shù)字區(qū)間設(shè)置、身份證雙重驗(yàn)證、輸入提示等，讓小伙伴一次學(xué)個(gè)通透！

還是原來(lái)的配方，還是原來(lái)的表！

一、設(shè)置數(shù)值區(qū)間驗(yàn)證

我們的評(píng)分范圍是0-10，并且容許小數(shù)，如何才能確保輸入的分值在0-10中呢？

步驟：

①  選中需要設(shè)置規(guī)則的區(qū)域“G2:G8”

②  點(diǎn)擊“數(shù)據(jù)有效性”按鈕，進(jìn)入“設(shè)置”選卡

③  在“允許”欄中，點(diǎn)擊“小數(shù)”

④  “數(shù)據(jù)”欄選擇“介于”，“最小值”和“最大值”一欄分別輸入“0”“10”，點(diǎn)擊“確定”

提示：如果不允許出現(xiàn)小數(shù)，則“允許”欄只能選擇“整數(shù)”。

設(shè)置后如果在該區(qū)域輸入“11”，將直接跳出錯(cuò)誤提示框。（想讓提示框的文字變得溫柔，可以查看Excel小白的數(shù)據(jù)驗(yàn)證課①。）

二、設(shè)置身份證號(hào)碼位數(shù)和非重復(fù)驗(yàn)證

1.單純號(hào)碼位數(shù)驗(yàn)證

總有粗心大意的人，在輸入身份證號(hào)碼時(shí)要么多一位要么少一位。我們應(yīng)該如何限制呢？

步驟：

①      選中需要設(shè)置規(guī)則的區(qū)域“E2:E8”

②      點(diǎn)擊“數(shù)據(jù)有效性”按鈕，進(jìn)入“設(shè)置”選卡

③      在“允許”欄中，選擇“文本長(zhǎng)度”

④      “數(shù)據(jù)”欄選擇“等于”，“長(zhǎng)度”一欄輸入“18”，最后點(diǎn)擊確定

確定后，當(dāng)輸入號(hào)碼位數(shù)多了或少了，就會(huì)彈出提示框。

2.單純非重復(fù)驗(yàn)證

身份證號(hào)碼是唯一的，因此輸入時(shí)必須確保號(hào)碼不能與前面的號(hào)碼重復(fù)。

單一的非重復(fù)驗(yàn)證，可以自定義公式進(jìn)行驗(yàn)證。公式=countif(e:e,e2)=1

3.位數(shù)和非重復(fù)雙重驗(yàn)證

把位數(shù)和非重復(fù)驗(yàn)證一起使用，同樣需要自定義公式。

位數(shù)驗(yàn)證的公式=len(e2)=18

非重復(fù)驗(yàn)證公式= countif(e:e,e2)=1

把兩者用and函數(shù)結(jié)合起來(lái)，即可實(shí)現(xiàn)位數(shù)和非重復(fù)雙重驗(yàn)證。

=and(len(e2)=18, countif(e:e,e2)=1)

三、日期驗(yàn)證和格式統(tǒng)一

假定入職日期我們需要按“1998-1-14”的方式統(tǒng)一錄入，不能出現(xiàn)“1998年1月14”“1998.1.14”“1998/1/14”等形式；另外日期的范圍需要限制在1950年到2002年之間。

步驟：

①      選中需要設(shè)置規(guī)則的區(qū)域“F2:F8”，按Ctrl+1設(shè)置日期格式為“2012-03-14”

②      點(diǎn)擊“數(shù)據(jù)有效性”按鈕，進(jìn)入“設(shè)置”選卡

③      在“允許”欄中，選擇“日期”

④      “數(shù)據(jù)”欄選擇“介于”，開(kāi)始日期設(shè)置為1950-1-1，結(jié)束日期設(shè)置為2002-12-31

確定后EXCEL只接受以“2001-4-12”“1-4-12”“2001/4/12”“1/4/12”“2001年4月12日”“1年4月12日”的方式錄入日期，錄入后日期統(tǒng)一顯示為2001-4-12的樣式。如果錄入格式不對(duì)，以及錄入日期不在規(guī)定的范圍內(nèi)，則會(huì)彈出錯(cuò)誤提示。

四、錄入前的用戶(hù)提示

利用數(shù)據(jù)驗(yàn)證不但可以在數(shù)據(jù)錄入后驗(yàn)證其是否符合設(shè)置的規(guī)則，也可以在錄入前提示用戶(hù)該怎么做。譬如當(dāng)用戶(hù)在身份證號(hào)單元格上單擊鼠標(biāo)時(shí)就彈出提示“請(qǐng)輸入18位號(hào)碼；最后一位是字母的話(huà)，必須是大寫(xiě)的X”。

步驟：

①    選中需要設(shè)置的區(qū)域“E2:E8”

②    點(diǎn)擊“數(shù)據(jù)驗(yàn)證”按鈕，進(jìn)入“輸入信息”界面，勾選“選定單元格時(shí)顯示輸入信息”

③    在“標(biāo)題”欄和“輸入信息”欄，分別錄入相關(guān)提示，點(diǎn)擊“確定”

設(shè)置后我們選中“身份證號(hào)”下的單元格，即可看到提示內(nèi)容：

講了這么多關(guān)于數(shù)據(jù)驗(yàn)證的應(yīng)用,小編還要偷偷告訴小伙伴其中的一個(gè)BUG！

不知道小伙伴有沒(méi)有發(fā)現(xiàn),數(shù)據(jù)有效性只對(duì)設(shè)置后錄入的數(shù)據(jù)有用，設(shè)置前錄入的數(shù)據(jù)不受規(guī)則限制：你設(shè)置或者不設(shè)置，我就在那里，不改不變~~~

那我們?cè)鯓硬拍芤谎壅页鲈O(shè)置前錄入有誤的數(shù)據(jù)呢？其實(shí)很簡(jiǎn)單～

五、圈釋無(wú)效數(shù)據(jù)

利用“圈釋無(wú)效數(shù)據(jù)”功能可以把數(shù)據(jù)驗(yàn)證設(shè)置前錄入的錯(cuò)誤數(shù)據(jù)找出來(lái)。下面以考核得分為例介紹圈釋無(wú)效數(shù)據(jù)。

步驟：

①      首先選中G2:G8單擊數(shù)據(jù)驗(yàn)證按鈕，在“設(shè)置”選卡中將“允許”設(shè)置為“任意值”，然后確定，取消前面的數(shù)據(jù)驗(yàn)證設(shè)置

②      在G2:G8中隨意輸入一些數(shù)據(jù)，有大于10的，有小于10的

③      選中G2:G8，設(shè)置數(shù)據(jù)驗(yàn)證，規(guī)則為0-10的整數(shù)

④      確定后，點(diǎn)擊“數(shù)據(jù)驗(yàn)證”下拉箭頭（是點(diǎn)箭頭哈！千萬(wàn)不要點(diǎn)“數(shù)據(jù)驗(yàn)證”哦～），選擇 “圈釋無(wú)效數(shù)據(jù)”命令

此時(shí)，不符合規(guī)定的數(shù)據(jù)，都被畫(huà)上了紅圈圈。

按規(guī)則修改圓圈中數(shù)字之后，紅圈就會(huì)消失。

提示：如果想取消圈釋?zhuān)苯訂螕簟皵?shù)據(jù)驗(yàn)證”下拉箭頭，選擇“清除驗(yàn)證標(biāo)識(shí)圈”命令即可。

好了，關(guān)于數(shù)據(jù)驗(yàn)證的內(nèi)容就講到這里啦～小伙伴們可要勤加練習(xí)哦～

本文配套的練習(xí)課件請(qǐng)加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

數(shù)據(jù)有效性技巧１《數(shù)據(jù)有效性只能引用一列數(shù)據(jù)？但他這樣用1000列也行！》

數(shù)據(jù)有效性技巧２《 3個(gè)小招讓數(shù)據(jù)有效性更高效》

數(shù)據(jù)有效性基礎(chǔ)1《Excel小白的數(shù)據(jù)驗(yàn)證課①用下拉菜單錄入的那些事兒》