Excel技巧:利用數據有效性控制輸入(詳細教程)

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

「數據有效性」功能位於「數據」功能區的「數據工具」部分。

數據有效性設置

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

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖1

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖2

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

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

操作步驟:

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

2) 點選「數據—>數據工具—>數據有效性—>數據有效性…」命令,打開「數據有效性」設置對話框,如圖 3所示。

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖3

3) 在圖 3中「有效性條件」選擇「文本長度」,運算符選擇「等於」,長度設置為「18」。

註:

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

4) 然後轉到「輸入信息」選項卡,如圖 4所示,在圖 4的標題部分輸入「輸入身份證號」,輸入信息部分輸入「請輸入18位的二代身份證號」。

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖4

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

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖5

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

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖6

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

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖7

%小提示:

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

%原理解析:

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

示例2:限定性別的輸入

在圖 1中,限制性別的輸入只能是「男」或者「女」。

操作步驟:

1) 選定性別列(D列)。

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

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖8

%注意:

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

3) 設置完成後,點擊「確定」回到Excel工作表。

4) 回到Excel工作表後,可以看到,在D列任意一個單元格的右側會多出來一個下拉框,裡麵包含「男」和「女」,可以在裡面選擇輸入,如圖 9所示。

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖9

%小提示:

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

示例3:限定所在地區

在圖 1中,限制「所在地區」的輸入只能從「地區表」中獲取。

這裡有兩種方法來做到這一目標。方法一是直接通過引用條件限定區域;方法二是借助定義域名來設置引用區域。

方法一操作步驟:

1) 選定所在地區列(E列)。

2) 同樣打開「數據有效性」對話框(點選「數據—>數據工具—>數據有效性—>數據有效性…」命令),在「設置」部分設置有效性條件為「序列」,「來源」部分通過點開右側的

Excel技巧:利用數據有效性控制輸入(詳細教程)

,然後選中「地區表」的A列,如圖 10所示。

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖10

3) 設置完成後,點擊「確定」回到Excel工作表。此時會發現所在地區已經限定了輸入下拉框。

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖11

%小提示:

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

方法二操作步驟:

1) 選中「地區表」A列中已有地區數據的單元格,然後在名稱框中輸入「所屬地區」(圖 12中紅框所示,即給這些地區起個域名叫「所屬地區」)。

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖12

2) 回到「通訊錄」工作表,選定所在地區列(E列)。

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

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖13

4) 設置完成後,點擊「確定」回到Excel工作表。此時會發現所在地區已經限定了輸入下拉框。

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

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

2. 圈釋無效數據

在前面介紹過,「數據有效性」設置只對設置後輸入數據的單元格有效,對於設置前已經輸入的單元格是不會檢驗的,那麼如何檢驗先前輸入的數據是否有效,或者已有的數據表是否符合我們的要求?這就可以借助於「圈釋無效數據」功能來做到。

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

Excel技巧:利用數據有效性控制輸入(詳細教程)

圖14

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

%注意

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

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

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

Excel技巧:利用數據有效性控制輸入(詳細教程)

學習更多實用Excel教程,請關注公眾號:zhichangcdb,回復Excel