編按：

哈嘍，大家好！今天小E特意為大家總結(jié)了數(shù)據(jù)有效性的7個(gè)實(shí)用案例，全面剖析了數(shù)據(jù)有效性的使用方法。不管是各種驗(yàn)證條件的應(yīng)用，還是出錯(cuò)信息和出錯(cuò)警告的設(shè)置?？赐赀@篇文章，大家都可以輕松掌握！

為了保證表格中輸入的數(shù)據(jù)都是有效的，可以提前設(shè)置單元格的數(shù)據(jù)驗(yàn)證功能。通過(guò)數(shù)據(jù)驗(yàn)證設(shè)置數(shù)據(jù)有效性后，不僅可以減少輸入錯(cuò)誤的概率，保證數(shù)據(jù)的準(zhǔn)確性，還可以圈釋無(wú)效數(shù)據(jù)。下面一起來(lái)學(xué)習(xí)下。

一、數(shù)據(jù)驗(yàn)證的驗(yàn)證條件

在向工作表中輸入數(shù)據(jù)時(shí)，為了防止輸入錯(cuò)誤的數(shù)據(jù)，可以為單元格設(shè)置有效的數(shù)據(jù)范圍，這樣可以極大地減少數(shù)據(jù)處理中的操作復(fù)雜性。

選中要設(shè)置數(shù)據(jù)有效性的單元格，用鼠標(biāo)點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡下“數(shù)據(jù)工具”選項(xiàng)組中的“數(shù)據(jù)驗(yàn)證”按鈕。在彈出的“數(shù)據(jù)驗(yàn)證”對(duì)話框中，選擇“設(shè)置”，在“允許”的下拉列表中選擇合適的數(shù)據(jù)格式類型。

數(shù)據(jù)格式類型分別有：任何值、整數(shù)、小數(shù)、序列、日期、時(shí)間、文本長(zhǎng)度、自定義。其中大部分都比較好理解，下面，筆者和大家一起熟悉一下。

1.數(shù)據(jù)格式為“序列”。

序列條件是指為有效性數(shù)據(jù)制定一個(gè)序列，序列的內(nèi)容可以是單元格引用或公式，也可以是手動(dòng)輸入的內(nèi)容。

【例1】在性別欄輸入男女。

選擇B列單元格區(qū)域，選擇“數(shù)據(jù)驗(yàn)證”按鈕。在彈出的“數(shù)據(jù)驗(yàn)證”對(duì)話框中，選擇“允許”下拉列表中的“序列”選項(xiàng)，在“來(lái)源”文本框中輸入“男,女”。最后，用鼠標(biāo)點(diǎn)擊“確定”按鈕即可。

如此，返回工作表中后，用鼠標(biāo)點(diǎn)擊設(shè)置了有效驗(yàn)證條件的任意單元格，在單元格右側(cè)都會(huì)出現(xiàn)一個(gè)下拉按鈕，點(diǎn)擊按鈕即可選擇指定的序列內(nèi)容。

注意：

①輸入序列內(nèi)容時(shí)，以半角的逗號(hào)隔開不同的內(nèi)容項(xiàng)。

②如果勾選“提供下拉箭頭”復(fù)選框，被設(shè)置的單元格右側(cè)會(huì)出現(xiàn)下拉箭頭，用鼠標(biāo)點(diǎn)擊該按鈕，序列內(nèi)容將出現(xiàn)在下拉列表中。（本案例中已勾選“提供下拉箭頭”復(fù)選框。）

2.數(shù)據(jù)格式格式為“文本長(zhǎng)度”。

“文本長(zhǎng)度”條件，將數(shù)據(jù)輸入限制為指定長(zhǎng)度的文本。

【例2】輸入手機(jī)號(hào)碼。

同上，在彈出的“數(shù)據(jù)驗(yàn)證”對(duì)話框中，選擇“允許”下拉菜單中的 “文本長(zhǎng)度”選項(xiàng)，在“數(shù)據(jù)”文本框中選擇“等于”，在長(zhǎng)度欄輸入“11”。最后，用鼠標(biāo)點(diǎn)擊“確定”按鈕即可。

如此，當(dāng)輸入的文本長(zhǎng)度不等于11時(shí)，會(huì)提示“此值與此單元格定義的數(shù)據(jù)驗(yàn)證限制不匹配”。

3.數(shù)據(jù)格式為“自定義”。

使用自定義類型時(shí)，允許用戶使用自定義公式、表達(dá)式或引用其它單元格的計(jì)算值，來(lái)判定輸入數(shù)據(jù)的有效性。

【例3】判斷車牌輸入是否正確。

如下圖所示，要求A列的車牌號(hào)必須輸入以漢字開頭，且總長(zhǎng)度為7位的內(nèi)容。輸入錯(cuò)誤就禁止輸入。

數(shù)據(jù)有效性公式：“=AND(LENB(LEFT(B2))=2,LEN(B2)=7)”。

注意：

漢字占用2個(gè)字節(jié)，數(shù)字和字母占用1個(gè)。

【例4】每行輸入完成才能輸入下一行

如下圖，當(dāng)在excel表格的A:D輸入時(shí)，只有上一行的四列都輸入數(shù)據(jù)，才能在下一行中輸入內(nèi)容；否則就無(wú)法輸入并提示錯(cuò)誤信息。

操作：

選取A2:D100（寫D100是為了能保證所有數(shù)據(jù)行數(shù)被包括到其中，讀者也可以依據(jù)實(shí)際數(shù)據(jù)來(lái)設(shè)置），用鼠標(biāo)依次點(diǎn)擊“數(shù)據(jù)”、“數(shù)據(jù)驗(yàn)證”、“允許”，選擇“自定義”，在來(lái)源框中輸入以下公式：“=COUNTA($A1:$D1)=4”。

公式說(shuō)明：

COUNTA()函數(shù)可以統(tǒng)計(jì)非空單元格個(gè)數(shù)。$A1:$D1添加$是把范圍固定在A:D列。

【例5】庫(kù)存表中有才能出庫(kù)

如下圖所示，左邊為庫(kù)存表，要求在右邊出庫(kù)表中設(shè)置限制。即當(dāng)出庫(kù)量大于庫(kù)存量時(shí),則禁止在出庫(kù)表中輸入內(nèi)容，并提示“此值與此單元格定義的數(shù)據(jù)驗(yàn)證限制不匹配”。

操作:

用鼠標(biāo)依次點(diǎn)擊“數(shù)據(jù)”、“數(shù)據(jù)驗(yàn)證”、“允許”，選擇“自定義”，在來(lái)源框中輸入以下公式：“=E3<=VLOOKUP(D3,A:B,2,0)”,就可以限制E列中商品的數(shù)量為小于B列中該商品對(duì)應(yīng)的數(shù)量。

小結(jié)：

在數(shù)據(jù)有效性中使有公式，可以完成復(fù)雜的判斷和輸入限制。對(duì)于規(guī)范表格的數(shù)據(jù)輸入非常有幫助。

二、設(shè)置出錯(cuò)信息和出錯(cuò)警告提示

1.設(shè)置輸入前的提示信息

用戶輸入數(shù)據(jù)前，如果能夠提示輸入什么樣的數(shù)據(jù)是符合要求的，那么出錯(cuò)率就會(huì)大大下降。

【例6】輸入學(xué)號(hào)前，提示用戶應(yīng)該輸入8位數(shù)的學(xué)號(hào)。

如下圖，當(dāng)鼠標(biāo)點(diǎn)擊B2:B7單元格區(qū)域的任意單元格時(shí)，就會(huì)有提示信息。

操作：

選中B2:B7單元格區(qū)域，選擇“數(shù)據(jù)驗(yàn)證”按鈕。在彈出的“數(shù)據(jù)驗(yàn)證”對(duì)話框中，選擇“輸入信息”，在“標(biāo)題”欄中輸入“學(xué)號(hào)”，在“輸入信息”文本框中輸入“應(yīng)輸入8位數(shù)的學(xué)號(hào)”。最后，用鼠標(biāo)點(diǎn)擊“確定”按鈕即可。

2.設(shè)置輸入錯(cuò)誤時(shí)的警告信息

用戶可以通過(guò)設(shè)置，在輸入的數(shù)據(jù)不符合要求時(shí)，彈出警告信息。

同樣以【例6】（輸入8位數(shù)的學(xué)號(hào)）為例子，介紹該功能。

步驟1：

同上，在彈出的“數(shù)據(jù)驗(yàn)證”對(duì)話框中，點(diǎn)擊“設(shè)置”，在“允許”的下拉列表中選擇“文本長(zhǎng)度”，在“數(shù)據(jù)”下拉列表中選擇“等于”，在“長(zhǎng)度”文本框中輸入“8”。最后，用鼠標(biāo)單擊“確定”按鈕即可。

步驟2：

選擇“出錯(cuò)警告”選項(xiàng)卡，在“樣式”下拉列表中選擇“警告”，在“標(biāo)題”中輸入“輸入錯(cuò)誤”，在“錯(cuò)誤信息”中輸入“警告信息”，用鼠標(biāo)點(diǎn)擊“確定”按鈕即可。

返回工作表后，當(dāng)輸入不符合要求的數(shù)字時(shí)，會(huì)提示如下的警告信息。

3.圈定無(wú)效數(shù)據(jù)

是指系統(tǒng)自動(dòng)的將不符合要求的數(shù)據(jù)用紅色的圈標(biāo)注出來(lái)，以便查找和修改。

【例7】將學(xué)生出生時(shí)間較早的日期標(biāo)識(shí)出來(lái)。

步驟1：

選中C2:C7單元格區(qū)域，選擇“數(shù)據(jù)驗(yàn)證”按鈕。在彈出的“數(shù)據(jù)驗(yàn)證”對(duì)話框中，選擇“設(shè)置”，在“允許”的下拉列表中選擇“日期”，在“開始日期”欄中輸入“1986-1-1”，在“結(jié)束日期”欄中輸入“1995-1-1”。最后，用鼠標(biāo)點(diǎn)擊“確定”按鈕即可。

步驟2：

選擇C2:C7單元格區(qū)域，選擇“數(shù)據(jù)驗(yàn)證”按鈕，在右側(cè)的下拉按鈕中選擇“圈示無(wú)效數(shù)據(jù)”。此時(shí)，區(qū)域中的無(wú)數(shù)數(shù)據(jù)就會(huì)以橢圓標(biāo)注出來(lái)了。

如果想清除數(shù)圈示，只需要在“數(shù)據(jù)驗(yàn)證”按鈕的下拉菜單中選擇“清除驗(yàn)證標(biāo)識(shí)圈”，紅色的標(biāo)識(shí)圈就會(huì)自動(dòng)消除了，很簡(jiǎn)單，這里就不做演示了。

OK，今天大家通過(guò)很多例子系統(tǒng)的學(xué)習(xí)了數(shù)據(jù)有效性，這樣不僅可以減少輸入錯(cuò)誤的概率，保證數(shù)據(jù)的準(zhǔn)確性，提高工作效率，還可以圈示無(wú)效數(shù)據(jù)。小伙伴們，還有別的什么想法，歡迎到公眾號(hào)中留言。

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

數(shù)據(jù)有效性只能引用一列數(shù)據(jù)？但他這樣用1000列也行！

3個(gè)小招讓數(shù)據(jù)有效性更高效

最簡(jiǎn)單的多級(jí)下拉菜單制作方法，不需要定義名稱

更高效的搜索式下拉菜單，你一定要懂！

版權(quán)申明：

本文作者賦春風(fēng)；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。