Excel Home
全球知名的Excel资源网站之一

数据验证,这些典型应用请收好

1、限制年龄范围

如果希望员工年龄的区间是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列有多少个非空单元格,下拉菜单中就显示多少行。

练习文件在此:
http://caiyun.feixin.10086.cn/dl/1B5CvatoKe8xw
提取码:poYq

赞(48)
未经允许不得转载:ExcelHome » 数据验证,这些典型应用请收好
分享到

关于我们联系我们
本站特聘法律顾问:李志群律师   沪ICP备11019229号-2

沪公网安备 31011702000001号

征信