工作中我們會(huì)遇到這樣的情況：綜合部門的小伙伴會(huì)發(fā)放表格讓其他部門填寫，有的時(shí)候，交上來(lái)的表格會(huì)非常混亂，這個(gè)時(shí)候，如果對(duì)單元格進(jìn)行一些特別的設(shè)置，就會(huì)在一定程度上避免這些問(wèn)題。

今天我們就圍繞這個(gè)話題，講講Excel中【數(shù)據(jù)驗(yàn)證】方面的問(wèn)題（較早版本又名：數(shù)據(jù)有效性），分享一些辦公“老鳥”的經(jīng)驗(yàn)，相信你會(huì)有所收獲。

下面，我們來(lái)詳細(xì)講解6種實(shí)用方法：

1、限制單元格輸入重復(fù)值

步驟：選中需要進(jìn)行設(shè)置的單元格（本案例 B5：B6），數(shù)據(jù)選項(xiàng)卡，數(shù)據(jù)驗(yàn)證，自定義，輸入公式=countif(B:B,B5)=1，確定。

關(guān)鍵步驟截圖：

這里先說(shuō)一下countif函數(shù)，這個(gè)函數(shù)的作用是：用于統(tǒng)計(jì)滿足某個(gè)條件的單元格的數(shù)量。

語(yǔ)法可以理解為=COUNTIF(要檢查哪些區(qū)域? 要查找哪些內(nèi)容?)，具體到本案例就是countif（統(tǒng)計(jì)B列中，B5的值），翻譯一下就是統(tǒng)計(jì)B5單元格在B列中出現(xiàn)的次數(shù)，所以countif(B:B,B5)=1就是限定B5單元格的值只能在B列出現(xiàn)1次，即唯一性，如果不滿足唯一性就提示錯(cuò)誤。

2、限制單元格輸入非法值（黑名單）

上圖演示的是，進(jìn)入輸入黑名單中的姓名：小張。（如果需要限定多個(gè)名字，在J列按順序向下寫）

操作方法同第一步，公式為=countif(J:J,B5)=0，注意J列是黑名單所在的列。

關(guān)鍵步驟截圖如下：

按照前面所講的內(nèi)容，countif(J:J,B5)=0就是限定B5單元格的值只能在J列（黑名單）出現(xiàn)0次，即不能出現(xiàn)，也就是說(shuō)禁止輸入，如果不滿足就提示錯(cuò)誤。

限制黑名單輸入的實(shí)際應(yīng)用案例參考：

1分鐘，從1萬(wàn)個(gè)人員中刪除“黑名單”，這個(gè)技能只有0.01%的人會(huì)

重要概念提示：

細(xì)心的小伙伴們可能會(huì)有個(gè)疑問(wèn)：為什么我們選中了2個(gè)（或多個(gè)）單元格，為啥只在公式中寫入了B5，B6去哪里了？

寫入的B5是我們選中區(qū)域的活動(dòng)單元格，請(qǐng)注意我們選擇區(qū)域的時(shí)候是從B5開始框選到B6的，默認(rèn)情況下B5就是活動(dòng)單元格，如果先選擇的B6，B6就是活動(dòng)單元格，公式中寫入B6。活動(dòng)單元格的概念在條件格式中也會(huì)經(jīng)常遇到，一定要記住哦。

為了幫助大家更好地理解這一概念，我們做個(gè)活動(dòng)單元格的演示：

上圖中，我們用鼠標(biāo)框選了B5：B15區(qū)域，注意看圖中左上角鼠標(biāo)指示的名稱框的位置，這里顯示的就是活動(dòng)單元格的位置。隨著我們一次一次按Tab鍵（鍵盤左側(cè)），活動(dòng)單元格也發(fā)生了變化。默認(rèn)情況下，單個(gè)連續(xù)區(qū)域中，先選擇的就是活動(dòng)單元格。對(duì)于多個(gè)不連續(xù)區(qū)域的默認(rèn)單元格，自己嘗試一下吧。

3、限制文本長(zhǎng)度的輸入

上圖演示的是，將學(xué)生的學(xué)籍號(hào)限定在4位或5位數(shù)，如果輸入的位數(shù)不是限定范圍，則提示錯(cuò)誤。

步驟：選中需要進(jìn)行設(shè)置的單元格（本案例 C5：C6），數(shù)據(jù)選項(xiàng)卡，數(shù)據(jù)驗(yàn)證，文本長(zhǎng)度，介于，最小值輸入4，最大值輸入5，確定。如果不是4位或5位的文本長(zhǎng)度就會(huì)提示錯(cuò)誤，見上圖演示。

關(guān)鍵步驟截圖：

4、限制單元格輸入不合規(guī)定的成績(jī)

上圖演示的是，我們將成績(jī)限定在0-100范圍內(nèi)，允許輸入小數(shù)和整數(shù)。

步驟：選中需要進(jìn)行設(shè)置的單元格（本案例 D5：D6），數(shù)據(jù)選項(xiàng)卡，數(shù)據(jù)驗(yàn)證，小數(shù)，介于，最小值輸入0，最大值輸入100，確定。

關(guān)鍵步驟截圖：

注意：如果成績(jī)只允許輸入整數(shù)，要將小數(shù)改為整數(shù)。最小值和最大值根據(jù)實(shí)際需要進(jìn)行調(diào)整，比如0至150.

5、限制日期段的輸入

上圖演示的是，只能在【錄入日期】中，輸入今天及之前的日期。上圖中輸入了2019-5-28，超出了當(dāng)前日期（2019-5-19），就會(huì)提示錯(cuò)誤。

步驟：選中需要進(jìn)行設(shè)置的單元格（本案例 F5：F6），數(shù)據(jù)選項(xiàng)卡，數(shù)據(jù)驗(yàn)證，日期，小于或等于，結(jié)束日期中輸入=today()，確定。

關(guān)鍵步驟截圖：

如果想限定2019-1-1至當(dāng)天的日期，操作參考下圖（today函數(shù)表示引用當(dāng)前日期）。如果要限定2018年1月1日至2019年12月1日，則應(yīng)在開始日期中輸入2019-1-1，在結(jié)束日期中輸入2019-12-1，以此類推。

6、下拉菜單制作

下拉菜單的制作是我們經(jīng)常遇到的問(wèn)題，應(yīng)用范圍極廣，還可以配合函數(shù)公式、圖表進(jìn)行操作，達(dá)到動(dòng)態(tài)圖表演示的效果。

上圖演示的是，在姓名列，只允許輸入張森、黎明、王五這三個(gè)名字了。實(shí)際工作中根據(jù)需要，設(shè)定需要經(jīng)常輸入的信息。

步驟：選中需要進(jìn)行設(shè)置的單元格（本案例 B5：B6），數(shù)據(jù)選項(xiàng)卡，數(shù)據(jù)驗(yàn)證，小數(shù)序列，在來(lái)源中輸入張森,黎明,王五，確定。

關(guān)鍵步驟截圖：

其他制作下拉菜單的方法和實(shí)際應(yīng)用案例：

Excel數(shù)據(jù)規(guī)范化處理——下拉菜單制作

只需輸入姓名就能調(diào)用員工所有信息——這Excel技能越早知道越好

利用下拉菜單制作的動(dòng)態(tài)演示效果示例：

特別需要強(qiáng)調(diào)的是：

1、在Excel中，我們輸入的標(biāo)點(diǎn)符號(hào)全部應(yīng)在英文半角狀態(tài)下輸入，否則無(wú)法達(dá)到我們演示的效果。

2、盡管數(shù)據(jù)驗(yàn)證的方法很強(qiáng)大，它也有自身的不足，如：從其他表格或區(qū)域中復(fù)制粘貼進(jìn)來(lái)的數(shù)據(jù)，可破除數(shù)據(jù)驗(yàn)證的效果。（請(qǐng)自行驗(yàn)證，這里不再演示。）

今天你學(xué)會(huì)了嗎？希望你能舉一反三、靈活掌握。

你還有其他的方法嗎？歡迎在留言區(qū)和我們一起討論交流。

也歡迎寫下你的疑問(wèn)，我們?cè)僮鲞M(jìn)一步解答。

Excel辦公精英，希望你每天都有收獲。