概述

數(shù)據(jù)驗(yàn)證，以前叫做數(shù)據(jù)有效性，它的作用是規(guī)范和限制Excel中輸入的數(shù)據(jù)。

什么時(shí)候使用數(shù)據(jù)驗(yàn)證

當(dāng)你希望限制使用者在Excel中只能輸入滿足你要求的數(shù)據(jù)時(shí)，就可以使用數(shù)據(jù)驗(yàn)證：例如：

單元格中只能填寫整數(shù)

單元格中只能填寫某個(gè)區(qū)間的數(shù)值

單元格中只能輸入日期

單元格中只能輸入固定長(zhǎng)度的文本

單元格中只能輸入以特定字符串開頭的文本

單元格中的值只能從一個(gè)列表中選擇

......如何使用數(shù)據(jù)驗(yàn)證

使用數(shù)據(jù)驗(yàn)證非常簡(jiǎn)單，只要在數(shù)據(jù)選項(xiàng)卡中點(diǎn)擊“數(shù)據(jù)驗(yàn)證”，然后創(chuàng)建適當(dāng)?shù)囊?guī)則就可以了。

規(guī)則的創(chuàng)建在允許框中。缺省情況下，選項(xiàng)是允許任何值，也就是沒(méi)有任何數(shù)據(jù)驗(yàn)證的限制：

這里面是所有的可以設(shè)置的選項(xiàng)。

例如，如果你要限制單元格只能輸入0-1000之間的整數(shù)，可以選擇整數(shù)，然后設(shè)置最大，最小值：

這里的“介于”，可以用其他比較選項(xiàng)代替：

點(diǎn)擊確定，完成設(shè)置。

一旦輸入的不滿足條件（非整數(shù)，或者超出了范圍），就會(huì)返回提示信息：

這樣就實(shí)現(xiàn)了限制輸入的目的。常見的數(shù)據(jù)驗(yàn)證規(guī)則

一般的限制數(shù)據(jù)格式的輸入經(jīng)常見于財(cái)務(wù)管理，比如報(bào)銷單中限制填寫的數(shù)值范圍。在某些需要用戶提交的表單中，也經(jīng)常會(huì)限制日期格式，或者只能填寫整數(shù)（比如產(chǎn)品的訂購(gòu)數(shù)量），順便還可以限制日期或數(shù)值的范圍。

除此之外，最常見的條件格式是下拉列表。這種規(guī)則用到的是“序列”：

設(shè)置完成后，點(diǎn)擊該單元格，就會(huì)出現(xiàn)下拉列表：

你只能選擇其中的某個(gè)值（或者什么也不選擇）。當(dāng)然，你也可以手工輸入，但是如果輸入的值不是列表中的值，就會(huì)彈出提示信息：

關(guān)于下拉列表的數(shù)據(jù)驗(yàn)證，有很多應(yīng)用的場(chǎng)景，也有很多技巧，請(qǐng)參見：

Excel實(shí)用小技巧-這個(gè)數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）怎么 實(shí)現(xiàn)的

級(jí)聯(lián)下拉列表又來(lái)了，這次是動(dòng)態(tài)的

更多的數(shù)據(jù)驗(yàn)證是通過(guò)“自定義”實(shí)現(xiàn)的。例如，如果我們要求只能輸入以“ABC”開頭的10個(gè)字符的文本，就必須使用自定義：

所謂自定義實(shí)際上就是使用一個(gè)公式，這個(gè)公式需要返回一個(gè)邏輯值：TRUE或者FALSE。只有當(dāng)該公式返回TRUE時(shí)，輸入的才是合法的，否則，輸入就是非法的。

還有很多類似的應(yīng)用，具體請(qǐng)參見：

不許重復(fù)輸入數(shù)據(jù)-數(shù)據(jù)驗(yàn)證

報(bào)銷費(fèi)用必須符合預(yù)算限制-數(shù)據(jù)驗(yàn)證

輸入滿足一定格式要求的數(shù)據(jù)，例如合同編號(hào)-數(shù)據(jù)驗(yàn)證

9個(gè)經(jīng)常使用的數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）場(chǎng)景

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

設(shè)置了數(shù)據(jù)驗(yàn)證以后，在單元格中以后輸入的數(shù)據(jù)就會(huì)被數(shù)據(jù)驗(yàn)證規(guī)則限制。但是，如果在設(shè)置數(shù)據(jù)驗(yàn)證之前，單元格中已經(jīng)有的數(shù)據(jù)不會(huì)被清除，盡管他們違反了規(guī)則：

其中的71.4，85，2.6，9.6，都不滿足數(shù)據(jù)驗(yàn)證的規(guī)則。

我們可以在數(shù)據(jù)驗(yàn)證命令組中，使用圈釋無(wú)效數(shù)據(jù)，

即可以將不符合規(guī)則的數(shù)據(jù)圈出來(lái)：

需要提醒的是，這個(gè)圈釋只對(duì)當(dāng)前打開的文件有效，一旦將文件關(guān)閉，下一次打開，紅色的圓圈就消失了！數(shù)據(jù)驗(yàn)證的最佳實(shí)踐

使用數(shù)據(jù)驗(yàn)證，建議一定要提示信息和出錯(cuò)警告。

提示信息

在數(shù)據(jù)驗(yàn)證對(duì)話框的第二頁(yè)，輸入提示信息。

這樣，一旦選中該單元格準(zhǔn)備輸入數(shù)據(jù)時(shí)，就會(huì)出現(xiàn)提示：

出錯(cuò)警告

在數(shù)據(jù)驗(yàn)證對(duì)話框的第三頁(yè)，輸入出錯(cuò)后的警告信息。

這樣，當(dāng)輸入的數(shù)據(jù)不滿足限制時(shí)，就會(huì)彈出警告信息：

當(dāng)然，還可以直接在單元格上方放置提示信息：

通過(guò)這樣的方式，可以幫助使用者輸入正確的信息，并且在輸入錯(cuò)誤后也可以快速發(fā)現(xiàn)問(wèn)題所在。

重要的提示

不要依賴數(shù)據(jù)驗(yàn)證！

數(shù)據(jù)驗(yàn)證可以幫助你規(guī)范使用者輸入的數(shù)據(jù)格式和內(nèi)容。但是，不要認(rèn)為只要你設(shè)置了數(shù)據(jù)驗(yàn)證，那么輸入的數(shù)據(jù)就都符合你的要求！

因?yàn)閿?shù)據(jù)驗(yàn)證很容易就會(huì)被繞過(guò)去！

比如，你可以從另外的區(qū)域復(fù)制數(shù)據(jù)，然后粘貼到設(shè)置了數(shù)據(jù)驗(yàn)證的區(qū)域。即使數(shù)據(jù)不滿足數(shù)據(jù)驗(yàn)證的條件，也可以成功粘貼。如何快速發(fā)現(xiàn)設(shè)置了數(shù)據(jù)驗(yàn)證的單元格

可以通過(guò)“定位”功能快速發(fā)現(xiàn)所有設(shè)置了數(shù)據(jù)驗(yàn)證的單元格。

使用快捷鍵Ctrl+G，打開定位對(duì)話框，點(diǎn)擊定位條件，點(diǎn)擊數(shù)據(jù)驗(yàn)證：

點(diǎn)擊確定后，即可定位所有設(shè)置了數(shù)據(jù)驗(yàn)證的單元格。

Excel+Power Query+Power Pivot+Power BI

Power Excel 知識(shí)庫(kù)    按照以下方式進(jìn)入知識(shí)庫(kù)學(xué)習(xí)Excel函數(shù)   底部菜單：知識(shí)庫(kù)->Excel函數(shù)

自定義函數(shù)  底部菜單：知識(shí)庫(kù)->自定義函數(shù)Excel如何做  底部菜單：知識(shí)庫(kù)->Excel如何做

面授培訓(xùn)  底部菜單：培訓(xùn)學(xué)習(xí)->面授培訓(xùn)

也可以在歷史文章中學(xué)習(xí)Excel，Power Query，Power Pivot，Power BI，Power Automate各種技巧。