原創作者： 祝洪忠 轉自：Excel之家ExcelHome

Excel中有一位免費的小秘書，專門對咱們輸入的數據進行驗證審核的，你知道她藏在哪兒嗎？

有小夥伴可能已經知道了，這就是默默無聞的“數據驗證”，曾用名“數據有效性”。

如果咱們提前設置好了允許錄入的規則，當輸入不符合條件的數據時，Excel就會彈出對話框，阻止我們錄入非法數據。

接下來咱們就說說數據驗證的幾種典型用法：

1、限制年齡範圍

因為員工年齡不會小于18歲，也不會大于60歲，因此輸入員年齡的區間應該是18~60之間的整數。通過設置數據驗證，可以限制輸入數據的區間範圍。

2、限制輸入重複數據

在數據驗證中，如果公式結果等于TRUE或是不等于0的任意數值，Excel允許錄入，否則Excel将拒絕錄入。

選中A2:A10，設置數據驗證，自定義公式為：

=COUNTIF(A:A,A2)=1

其中的A2，是所選區域的活動單元格。

3、圈釋無效數據

對于已經輸入的内容，也可以先設置好數據驗證規則，然後使用圈釋無效數據功能，方便地查找出不符合要求的數據。

4、各項預算不能超過總預算

如下圖所示，是某人的育兒計劃表，從幼兒園到結婚計劃預算180萬元，要求各分項預算之和不能超過總預算。

選中B2:B7單元格區域，數據→數據驗證→自定義，輸入以下公式。

=SUM($B$2:$B$7)<=$D$2

設置完成後，B列各分項之和超過D2單元格的預算，就會彈出錯誤提示。

5、根據其他列内容限制輸入

如下圖所示，是某公司員工信息調查表，D列的配偶姓名填寫時，要求C列的婚否一項中必須為“是”，否則禁止錄入。

選中D2:D6單元格區域，數據→數據驗證→自定義，輸入以下公式。

=C2="是"

6、限制錄入周末日期

如下圖所示，是某人的工作計劃表，B列的拟定日期填寫時，要求不能錄入周末日期。

選中B2:B6單元格區域，數據→數據驗證→自定義，輸入以下公式。

=WEEKDAY(B2,2)<6

WEEKDAY(B2,2) ，根據B2單元格的日期，返回對應的星期。第二參數使用2，用數字1~7來表示周一到周日。WEEKDAY(B2,2)<6，就是限定錄入日期小于周六了。

7、制作下拉菜單

8、動态擴展的下拉菜單

如下圖所示，要根據A列的對照表，在D列生成下拉菜單，要求能随着A列數據的增減，下拉菜單中的内容也會自動調整。

選中要輸入内容的D2:D10單元格區域，數據→數據驗證→序列，輸入以下公式。

=OFFSET($A$2,0,0,COUNTA($A:$A)-1)

公式表示以A2作為基點，向下偏移0行，向右偏移0列，新引用的行數為COUNTA函數統計到的A列非空單元格個數，結果-1，是因為A1是表頭，計數要去掉。

這樣就是A列有多少個非空單元格，下拉菜單中就顯示多少行。

9、動态二級下拉菜單

如下圖所示，A、B列是客戶城市和縣區的對照表，在D列已經生成一級下拉菜單，要求在E列生成二級下拉菜單，要求能随着D列所選不同的一級菜單，E列下拉菜單中的内容也會自動調整。

選中要輸入内容的E2:E6單元格區域，數據→數據驗證→序列，輸入以下公式。

=OFFSET($B$1,MATCH($D2,$A$2:$A$16,0),0,COUNTIF($A:$A,$D2))

公式表示以B1為基點，以MATCH函數得到的城市首次出現的位置作為向下偏移的行數。

向右偏移的列數為0。

新引用的行數為COUNTIF($A:$A,$D2)的計算結果。

COUNTIF($A:$A,$D2)的作用是，根據D列以及菜單中的城市名在A列統計有多少個與之相同的城市個數。有多少個城市名，OFFSET函數就引用多少行。

好了，今天咱們的内容就是這些吧，祝大家一天好心情！

圖文制作：祝洪忠