Excel如何利用資料有效性限制輸入資料（Excel表格限定可以輸入該單元格的數值怎麼辦）
編按：哈嘍！小夥伴們大家好！上期我們主要講了用系列做下拉選單確保資料規範的操作。今天我們主要講資料驗證的其他操作，如數字區間設定、身份證雙重驗證、輸入提示等，讓小夥伴一次學個通透！
還是原來的配方，還是原來的表！
一、設定數值區間驗證
我們的評分範圍是0-10，並且容許小數，如何才能確保輸入的分值在0-10中呢？
步驟：
① 選中需要設定規則的區域“G2:G8”
② 點選“資料有效性”按鈕，進入“設定”選卡
③ 在“允許”欄中，點選“小數”
④ “資料”欄選擇“介於”，“最小值”和“最大值”一欄分別輸入“0”“10”，點選“確定”
提示：如果不允許出現小數，則“允許”欄只能選擇“整數”。
設定後如果在該區域輸入“11”，將直接跳出錯誤提示框。（想讓提示框的文字變得溫柔，可以檢視Excel小白的資料驗證課①用下拉選單錄入的那些事兒。）
二、設定身份證號碼位數和非重複驗證
1.單純號碼位數驗證
總有粗心大意的人，在輸入身份證號碼時要麼多一位要麼少一位。我們應該如何限制呢？
步驟：
① 選中需要設定規則的區域“E2:E8”
② 點選“資料有效性”按鈕，進入“設定”選卡
③ 在“允許”欄中，選擇“文字長度”
④ “資料”欄選擇“等於”，“長度”一欄輸入“18”，最後點選確定
確定後，當輸入號碼位數多了或少了，就會彈出提示框。
2.單純非重複驗證
身份證號碼是唯一的，因此輸入時必須確保號碼不能與前面的號碼重複。
單一的非重複驗證，可以自定義公式進行驗證。公式=countif(e:e,e2)=1
3.位數和非重複雙重驗證
把位數和非重複驗證一起使用，同樣需要自定義公式。
位數驗證的公式=len(e2)=18
非重複驗證公式= countif(e:e,e2)=1
把兩者用and函式結合起來，即可實現位數和非重複雙重驗證。
=and(len(e2)=18, countif(e:e,e2)=1)
三、日期驗證和格式統一
假定入職日期我們需要按“1998-1-14”的方式統一錄入，不能出現“1998年1月14”“1998.1.14”“1998/1/14”等形式；另外日期的範圍需要限制在1950年到2002年之間。
步驟：
① 選中需要設定規則的區域“F2:F8”，按Ctrl 1設定日期格式為“2012-03-14”
② 點選“資料有效性”按鈕，進入“設定”選卡
③ 在“允許”欄中，選擇“日期”
④ “資料”欄選擇“介於”，開始日期設定為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日”的方式錄入日期，錄入後日期統一顯示為2001-4-12的樣式。如果錄入格式不對，以及錄入日期不在規定的範圍內，則會彈出錯誤提示。
四、錄入前的使用者提示
利用資料驗證不但可以在資料錄入後驗證其是否符合設定的規則，也可以在錄入前提示使用者該怎麼做。譬如當使用者在身份證號單元格上單擊滑鼠時就彈出提示“請輸入18位號碼；最後一位是字母的話，必須是大寫的X”。
步驟：
① 選中需要設定的區域“E2:E8”
② 點選“資料驗證”按鈕，進入“輸入資訊”介面，勾選“選定單元格時顯示輸入資訊”
③ 在“標題”欄和“輸入資訊”欄，分別錄入相關提示，點選“確定”
設定後我們選中“身份證號”下的單元格，即可看到提示內容：
講了這麼多關於資料驗證的應用,小編還要偷偷告訴小夥伴其中的一個BUG！
不知道小夥伴有沒有發現,資料有效性只對設定後錄入的資料有用，設定前錄入的資料不受規則限制：你設定或者不設定，我就在那裡，不改不變~~~
那我們怎樣才能一眼找出設定前錄入有誤的資料呢？其實很簡單～
五、圈釋無效資料
利用“圈釋無效資料”功能可以把資料驗證設定前錄入的錯誤資料找出來。下面以考核得分為例介紹圈釋無效資料。
步驟：
① 首先選中G2:G8單擊資料驗證按鈕，在“設定”選卡中將“允許”設定為“任意值”，然後確定，取消前面的資料驗證設定
② 在G2:G8中隨意輸入一些資料，有大於10的，有小於10的
③ 選中G2:G8，設定資料驗證，規則為0-10的整數
④ 確定後，點選“資料驗證”下拉箭頭（是點箭頭哈！千萬不要點“資料驗證”哦～），選擇 “圈釋無效資料”命令。
此時，不符合規定的資料，都被畫上了紅圈圈。
按規則修改圓圈中數字之後，紅圈就會消失。
提示：如果想取消圈釋，直接單擊“資料驗證”下拉箭頭，選擇“清除驗證標識圈”命令即可。
好了，關於資料驗證的內容就講到這裡啦～小夥伴們可要勤加練習哦～
＊＊＊＊部落窩教育－excel資料驗證技巧＊＊＊＊
原創：壹仟伍佰萬/部落窩教育（未經同意，請勿轉載）
更多教程：部落窩教育（www.itblw.com）
微信公眾號：exceljiaocheng