利用數(shù)據(jù)有效性對(duì)數(shù)據(jù)的輸入做些限定工作以防人為錯(cuò)誤（當(dāng)輸入的數(shù)據(jù)違反的有效性規(guī)則,系統(tǒng)會(huì)用它來(lái)提示出錯(cuò)）

Excel具有強(qiáng)大的制表功能，給我們的工作帶來(lái)了方便，但是在數(shù)據(jù)錄入的過(guò)程中也容易出錯(cuò)，因?yàn)镋xcel默認(rèn)是可以輸入任何數(shù)據(jù)的。有時(shí)候?yàn)榱朔乐谷藶榈腻e(cuò)誤，我們需要對(duì)數(shù)據(jù)的輸入做些限定工作，如限定身份證號(hào)的輸入位數(shù)，限定人的性別只有男和女兩種等等。這需要借助Excel的“數(shù)據(jù)有效性”功能。

“數(shù)據(jù)有效性”功能位于“數(shù)據(jù)”功能區(qū)的“數(shù)據(jù)工具”部分。

數(shù)據(jù)有效性設(shè)置

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

圖1

圖2

示例1：限定身份證號(hào)的位數(shù)

在圖 1中，限制身份證號(hào)輸入的位數(shù)。

操作步驟：

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

2） 點(diǎn)選“數(shù)據(jù)—>數(shù)據(jù)工具—>數(shù)據(jù)有效性—>數(shù)據(jù)有效性…”命令，打開(kāi)“數(shù)據(jù)有效性”設(shè)置對(duì)話框，如圖 3所示。

圖3

3） 在圖 3中“有效性條件”選擇“文本長(zhǎng)度”，運(yùn)算符選擇“等于”，長(zhǎng)度設(shè)置為“18”。

注：

在圖 3中有4個(gè)選項(xiàng)卡：設(shè)置、輸入信息、出錯(cuò)警告、輸入法模式。“設(shè)置”是用來(lái)設(shè)置數(shù)據(jù)輸入限定條件的，默認(rèn)是“任何值”。可以設(shè)置的“有效性條件”有：整數(shù)、小數(shù)、序列、日期、時(shí)間、文本長(zhǎng)度和自定義。

4） 然后轉(zhuǎn)到“輸入信息”選項(xiàng)卡，如圖 4所示，在圖 4的標(biāo)題部分輸入“輸入身份證號(hào)”，輸入信息部分輸入“請(qǐng)輸入18位的二代身份證號(hào)”。

圖4

5） 轉(zhuǎn)到“出錯(cuò)警告”選項(xiàng)卡，如圖 5所示，在其中標(biāo)題部分輸入“輸入錯(cuò)誤”，錯(cuò)誤信息輸入“您輸入的身份證號(hào)碼非法”。

圖5

6） “輸入法模式”選項(xiàng)卡不做修改，即保持輸入模式不變，然后單擊“確定”，回到Excel工作表。此時(shí)可以看到當(dāng)光標(biāo)在C列任何一個(gè)單元格時(shí)，系統(tǒng)都會(huì)顯示：“輸入身份證號(hào) ?請(qǐng)輸入18位的二代身份證號(hào)”，這就是我們剛才在“數(shù)據(jù)有效性”對(duì)話框的“輸入信息”部分輸入的內(nèi)容。

圖6

7） 隨意地在C列單元格中輸入數(shù)據(jù)，然后按Enter回車(chē)鍵確認(rèn)。如果你輸入的是18位（如果輸入的全是數(shù)字，請(qǐng)記住在其前面加“’”，將數(shù)字轉(zhuǎn)換為文本），那么系統(tǒng)不會(huì)有任何提示；否則，系統(tǒng)會(huì)提示錯(cuò)誤信息，如圖 7所示，錯(cuò)誤信息是我們剛才在“數(shù)據(jù)有效性”對(duì)話框的“出錯(cuò)報(bào)警”部分輸入的內(nèi)容。

圖7

%小提示：

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

%原理解析：

在Excel默認(rèn)的情況下，之所以單元格能夠輸入任何類(lèi)型的數(shù)據(jù)，就是因?yàn)樵诖恕皵?shù)據(jù)有效性”設(shè)置中，默認(rèn)允許的是“任何值”。

示例2：限定性別的輸入

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

操作步驟：

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

2） 同樣打開(kāi)“數(shù)據(jù)有效性”對(duì)話框（點(diǎn)選“數(shù)據(jù)—>數(shù)據(jù)工具—>數(shù)據(jù)有效性—>數(shù)據(jù)有效性…”命令），在“設(shè)置”部分設(shè)置有效性條件為“序列”，來(lái)源設(shè)為“男,女”如圖 8所示。

圖8

%注意：

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

3） 設(shè)置完成后，點(diǎn)擊“確定”回到Excel工作表。

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

圖9

%小提示：

也可以直接在D列的單元格中輸入數(shù)據(jù)，但是只能輸入“男”或者“女”，否則Excel就會(huì)報(bào)錯(cuò)。

示例3：限定所在地區(qū)

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

這里有兩種方法來(lái)實(shí)現(xiàn)這一目標(biāo)。方法一是直接通過(guò)引用條件限定區(qū)域；方法二是借助定義域名來(lái)設(shè)置引用區(qū)域。

方法一操作步驟：

1） 選定所在地區(qū)列（E列）。

2） 同樣打開(kāi)“數(shù)據(jù)有效性”對(duì)話框（點(diǎn)選“數(shù)據(jù)—>數(shù)據(jù)工具—>數(shù)據(jù)有效性—>數(shù)據(jù)有效性…”命令），在“設(shè)置”部分設(shè)置有效性條件為“序列”，“來(lái)源”部分通過(guò)點(diǎn)開(kāi)右側(cè)的

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

圖10

3） 設(shè)置完成后，點(diǎn)擊“確定”回到Excel工作表。此時(shí)會(huì)發(fā)現(xiàn)所在地區(qū)已經(jīng)限定了輸入下拉框。

圖11

%小提示：

在Excel2003及以前版本中，借助是無(wú)法跨工作表選擇數(shù)據(jù)的，因此本方法在Excel2003中無(wú)法實(shí)現(xiàn)。

方法二操作步驟：

1） 選中“地區(qū)表”A列中已有地區(qū)數(shù)據(jù)的單元格，然后在名稱(chēng)框中輸入“所屬地區(qū)”（圖 12中紅框所示，即給這些地區(qū)起個(gè)域名叫“所屬地區(qū)”）。

圖12

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

3） 同樣打開(kāi)“數(shù)據(jù)有效性”對(duì)話框（點(diǎn)選“數(shù)據(jù)—>數(shù)據(jù)工具—>數(shù)據(jù)有效性—>數(shù)據(jù)有效性…”命令），在“設(shè)置”部分設(shè)置有效性條件為“序列”，“來(lái)源”輸入“=所屬地區(qū)”，如圖 13所示。

圖13

4） 設(shè)置完成后，點(diǎn)擊“確定”回到Excel工作表。此時(shí)會(huì)發(fā)現(xiàn)所在地區(qū)已經(jīng)限定了輸入下拉框。

%小提示：如何去掉數(shù)據(jù)有效性設(shè)置

直接在“數(shù)據(jù)有效性”對(duì)話框中將“有效性條件”設(shè)置為允許“任何值”即可。

2. 圈釋無(wú)效數(shù)據(jù)

在前面介紹過(guò)，“數(shù)據(jù)有效性”設(shè)置只對(duì)設(shè)置后輸入數(shù)據(jù)的單元格有效，對(duì)于設(shè)置前已經(jīng)輸入的單元格是不會(huì)檢驗(yàn)的，那么如何檢驗(yàn)先前輸入的數(shù)據(jù)是否有效，或者已有的數(shù)據(jù)表是否符合我們的要求？這就可以借助于“圈釋無(wú)效數(shù)據(jù)”功能來(lái)實(shí)現(xiàn)。

以圖 1所示的例子為例，在前面已經(jīng)設(shè)定了身份證號(hào)、性別和所屬地區(qū)的數(shù)據(jù)有效性規(guī)則。現(xiàn)在可以用“圈釋無(wú)效數(shù)據(jù)”來(lái)找出不合格的數(shù)據(jù)：選中數(shù)據(jù)區(qū)域，然后點(diǎn)選“數(shù)據(jù)—>數(shù)據(jù)工具—>數(shù)據(jù)有效性—>圈釋無(wú)效數(shù)據(jù)”命令，將得到如圖 14所示的結(jié)果。

圖14

因?yàn)樵谇懊嬖O(shè)置身份證號(hào)位數(shù)、性別和所在地區(qū)時(shí)對(duì)C1、D1和E1單元格都包含進(jìn)去了，所以最后Excel會(huì)檢驗(yàn)出這三個(gè)單元格數(shù)據(jù)無(wú)效。

%注意

圈釋無(wú)效數(shù)據(jù)必須先設(shè)定了數(shù)據(jù)有效性條件。

%小提示：如何清除無(wú)效數(shù)據(jù)標(biāo)識(shí)圈

點(diǎn)選“數(shù)據(jù)—>數(shù)據(jù)工具—>數(shù)據(jù)有效性—>清除無(wú)效無(wú)效數(shù)據(jù)標(biāo)識(shí)圈”命令。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶(hù)投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。