當前位置: 妍妍網 > 辦公

數據有效性,9種典型用法請收好

2024-03-27辦公

小夥伴們好啊,今天咱們說說數據驗證(數據有效性)的幾種典型用法:

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函式就參照多少行。

好了,今天咱們的內容就是這些吧,祝大家一天好心情!

今天的練習檔在此:

http://caiyun.feixin.10086.cn/dl/1B5CvatoKe8xw

提取碼:poYq

圖文制作: 祝洪忠