編按：

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

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

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

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

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

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

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

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

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

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

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

注意：

①輸入序列內(nèi)容時，以半角的逗號隔開不同的內(nèi)容項。

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

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

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

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

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

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

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

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

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

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

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

注意：

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

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

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

操作：

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

公式說明：

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

【例5】庫存表中有才能出庫

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

操作:

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

小結(jié)：

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

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

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

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

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

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

操作：

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

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

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

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

步驟1：

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

步驟2：

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

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

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

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

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

步驟1：

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

步驟2：

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

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

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

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

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

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

相關(guān)推薦：

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

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

最簡單的多級下拉菜單制作方法，不需要定義名稱

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

版權(quán)申明：

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