利用數據有效性對數據的輸入做些限定工作以防人為錯誤（數據有效性可以保證一定不會輸入錯誤數據嗎）

Excel具有強大的制表功能，給我們的工作帶來了方便，但是在數據錄入的過程中也容易出錯，因為Excel默認是可以輸入任何數據的。有時候為了防止人為的錯誤，我們需要對數據的輸入做些限定工作，如限定身份證號的輸入位數，限定人的性別只有男和女兩種等等。這需要借助Excel的“數據有效性”功能。

“數據有效性”功能位于“數據”功能區的“數據工具”部分。

數據有效性設置

現有一個Excel工作簿如圖 1所示。其中“通訊錄”放在工作表1（通訊錄表）中，所有的地區放在工作表2（地區表）中，如圖 2所示。現需要在用戶輸入數據時限定輸入的條件和范圍，如身份證號只能輸入18位的二代身份證號，性別只能輸入“男”或者“女”，所在地區只能從“地區表”中獲取。

圖1

圖2

示例1：限定身份證號的位數

在圖 1中，限制身份證號輸入的位數。

操作步驟：

1） 選定用來輸入身份證的單元格或列（圖 1中的C列）。

2） 點選“數據—>數據工具—>數據有效性—>數據有效性…”命令，打開“數據有效性”設置對話框，如圖 3所示。

圖3

3） 在圖 3中“有效性條件”選擇“文本長度”，運算符選擇“等于”，長度設置為“18”。

注：

在圖 3中有4個選項卡：設置、輸入信息、出錯警告、輸入法模式。“設置”是用來設置數據輸入限定條件的，默認是“任何值”。可以設置的“有效性條件”有：整數、小數、序列、日期、時間、文本長度和自定義。

4） 然后轉到“輸入信息”選項卡，如圖 4所示，在圖 4的標題部分輸入“輸入身份證號”，輸入信息部分輸入“請輸入18位的二代身份證號”。

圖4

5） 轉到“出錯警告”選項卡，如圖 5所示，在其中標題部分輸入“輸入錯誤”，錯誤信息輸入“您輸入的身份證號碼非法”。

圖5

6） “輸入法模式”選項卡不做修改，即保持輸入模式不變，然后單擊“確定”，回到Excel工作表。此時可以看到當光標在C列任何一個單元格時，系統都會顯示：“輸入身份證號 ?請輸入18位的二代身份證號”，這就是我們剛才在“數據有效性”對話框的“輸入信息”部分輸入的內容。

圖6

7） 隨意地在C列單元格中輸入數據，然后按Enter回車鍵確認。如果你輸入的是18位（如果輸入的全是數字，請記住在其前面加“’”，將數字轉換為文本），那么系統不會有任何提示；否則，系統會提示錯誤信息，如圖 7所示，錯誤信息是我們剛才在“數據有效性”對話框的“出錯報警”部分輸入的內容。

圖7

%小提示：

“數據有效性”設置只會對設置以后再輸入的單元格有效，對于已經輸入了的單元格，Excel不會重新檢查其是否合法。因此，一定要在輸入之前設置單元格輸入數據的有效性。

%原理解析：

在Excel默認的情況下，之所以單元格能夠輸入任何類型的數據，就是因為在此“數據有效性”設置中，默認允許的是“任何值”。

示例2：限定性別的輸入

在圖 1中，限制性別的輸入只能是“男”或者“女”。

操作步驟：

1） 選定性別列（D列）。

2） 同樣打開“數據有效性”對話框（點選“數據—>數據工具—>數據有效性—>數據有效性…”命令），在“設置”部分設置有效性條件為“序列”，來源設為“男,女”如圖 8所示。

圖8

%注意：

“男”和“女”序列之間的“,”必須為英文的逗號，不能用中文的逗號。

3） 設置完成后，點擊“確定”回到Excel工作表。

4） 回到Excel工作表后，可以看到，在D列任意一個單元格的右側會多出來一個下拉框，里面包含“男”和“女”，可以在里面選擇輸入，如圖 9所示。

圖9

%小提示：

也可以直接在D列的單元格中輸入數據，但是只能輸入“男”或者“女”，否則Excel就會報錯。

示例3：限定所在地區

在圖 1中，限制“所在地區”的輸入只能從“地區表”中獲取。

這里有兩種方法來實現這一目標。方法一是直接通過引用條件限定區域；方法二是借助定義域名來設置引用區域。

方法一操作步驟：

1） 選定所在地區列（E列）。

2） 同樣打開“數據有效性”對話框（點選“數據—>數據工具—>數據有效性—>數據有效性…”命令），在“設置”部分設置有效性條件為“序列”，“來源”部分通過點開右側的

，然后選中“地區表”的A列，如圖 10所示。

圖10

3） 設置完成后，點擊“確定”回到Excel工作表。此時會發現所在地區已經限定了輸入下拉框。

圖11

%小提示：

在Excel2003及以前版本中，借助是無法跨工作表選擇數據的，因此本方法在Excel2003中無法實現。

方法二操作步驟：

1） 選中“地區表”A列中已有地區數據的單元格，然后在名稱框中輸入“所屬地區”（圖 12中紅框所示，即給這些地區起個域名叫“所屬地區”）。

圖12

2） 回到“通訊錄”工作表，選定所在地區列（E列）。

3） 同樣打開“數據有效性”對話框（點選“數據—>數據工具—>數據有效性—>數據有效性…”命令），在“設置”部分設置有效性條件為“序列”，“來源”輸入“=所屬地區”，如圖 13所示。

圖13

4） 設置完成后，點擊“確定”回到Excel工作表。此時會發現所在地區已經限定了輸入下拉框。

%小提示：如何去掉數據有效性設置

直接在“數據有效性”對話框中將“有效性條件”設置為允許“任何值”即可。

2. 圈釋無效數據

在前面介紹過，“數據有效性”設置只對設置后輸入數據的單元格有效，對于設置前已經輸入的單元格是不會檢驗的，那么如何檢驗先前輸入的數據是否有效，或者已有的數據表是否符合我們的要求？這就可以借助于“圈釋無效數據”功能來實現。

以圖 1所示的例子為例，在前面已經設定了身份證號、性別和所屬地區的數據有效性規則。現在可以用“圈釋無效數據”來找出不合格的數據：選中數據區域，然后點選“數據—>數據工具—>數據有效性—>圈釋無效數據”命令，將得到如圖 14所示的結果。

圖14

因為在前面設置身份證號位數、性別和所在地區時對C1、D1和E1單元格都包含進去了，所以最后Excel會檢驗出這三個單元格數據無效。

%注意

圈釋無效數據必須先設定了數據有效性條件。

%小提示：如何清除無效數據標識圈

點選“數據—>數據工具—>數據有效性—>清除無效無效數據標識圈”命令。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。