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

還是原來的配方，還是原來的表！

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

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

步驟：

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

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

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

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

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

設置后如果在該區(qū)域輸入“11”，將直接跳出錯誤提示框。（想讓提示框的文字變得溫柔，可以查看Excel小白的數(shù)據(jù)驗證課①用下拉菜單錄入的那些事兒。）

二、設置身份證號碼位數(shù)和非重復驗證

1.單純號碼位數(shù)驗證

總有粗心大意的人，在輸入身份證號碼時要么多一位要么少一位。我們應該如何限制呢？

步驟：

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

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

③      在“允許”欄中，選擇“文本長度”

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

確定后，當輸入號碼位數(shù)多了或少了，就會彈出提示框。

2.單純非重復驗證

身份證號碼是唯一的，因此輸入時必須確保號碼不能與前面的號碼重復。

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

3.位數(shù)和非重復雙重驗證

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

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

非重復驗證公式= countif(e:e,e2)=1

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

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

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

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

步驟：

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

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

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

④      “數(shù)據(jù)”欄選擇“介于”，開始日期設置為1950-1-1，結束日期設置為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的樣式。如果錄入格式不對，以及錄入日期不在規(guī)定的范圍內，則會彈出錯誤提示。

四、錄入前的用戶提示

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

步驟：

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

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

③    在“標題”欄和“輸入信息”欄，分別錄入相關提示，點擊“確定”

設置后我們選中“身份證號”下的單元格，即可看到提示內容：

講了這么多關于數(shù)據(jù)驗證的應用,小編還要偷偷告訴小伙伴其中的一個BUG！

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

那我們怎樣才能一眼找出設置前錄入有誤的數(shù)據(jù)呢？其實很簡單～

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

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

步驟：

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

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

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

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

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

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

提示：如果想取消圈釋，直接單擊“數(shù)據(jù)驗證”下拉箭頭，選擇“清除驗證標識圈”命令即可。

好了，關于數(shù)據(jù)驗證的內容就講到這里啦～小伙伴們可要勤加練習哦～