巧用数据验证,快速制作多级动态下拉菜单,提高数据有效性
Excel的最大特点就是数据的分析与处理,但分析与处理的前提是数据,而且是正确有效的数据,如果数据源中的数据都是无效的,其分析和处理的结果肯定是不准确的,或者是无法分析的。所以,保证正确有效的数据,是数据处理和分析的前提,此时,可以用【数据验证】来完成。
在Excel2013及更高版本中,【数据验证】,在前期的版本中,称之为【数据有效性】,但功能是相同的。
一、数据验证:数值范围。
目的:将销售员的年龄控制在18-60岁之间。
方法:
1、选定目标单元格,【数据】-【数据验证】。
2、选择【允许】中的【整数】,【数据】中的【介于】,【最小值】中输入:18,【最大值】中输入:60并【确定】。
解读:
1、【数据】中除了【介于】之外,还有【未介于】、【等于】、【不等于】、【大于】、【小于】、【大于或等于】、【小于或等于】,可以根据实际需要选择。
2、如果数值类型为小数,其用法和【整数】的用法相同。
二、数据验证:下拉列表。
目的:选择政治面貌。
方法:
1、选定目标单元格,【数据】-【数据验证】。
2、选择【允许】中的【序列】,单击【来源】右侧的箭头,选择需要显示的内容,并单击箭头返回。
3、【确定】。
解读:
【序列】中的【来源】,除了选择外,还可以手动输入,如本示例中的输入内容为:中共党员,中共预备党员,共青团员,群众 ,每个内容之间用英文逗号(,)分割即可。
三、数据验证:文本长度。
目的:正确输入手机号码。
方法:
1、选定目标单元格,【数据】-【数据验证】。
2、选择【允许】中的【文本长度】,【数据】中的【等于】,在【长度】中输入:11并【确定】。
解读:
1、“11”为手机号码的长度,如果为身份证号码,则为:18,灵活应用。
2、【数据】中除了【等于】外,还有【介于】、【未介于】、【不等于】、【大于】、【小于】、【大于或等于】、【小于或等于】,可以根据实际需要选择。
四、数据验证:自定义。
目的:禁止输入重复值。
方法:
1、选定目标单元格,【数据】-【数据验证】。
2、选择【允许】中的【自定义】,并在【公式】中输入:=COUNTIF(B:B,B3)=1并【确定】。
解读:
Countif函数的作用为单条件计数,语法结构为:=Countif(条件范围,条件),公式=COUNTIF(B:B,B3)=1中,计算指定单元格的值在B列中的个数,如果>1就会提示错误。
五、数据验证:圈释无效数据。
目的:将无效的数据用红色椭圆圈圈起来。
方法:
1、选择已经有数据的目标单元格,【数据】-【数据验证】。
2、选择【允许】中的【序列】,单击【来源】右侧的箭头,选择需要显示的内容,并单击箭头返回。
3、【确定】。
4、【数据验证】-【圈释无效数据】。
解读:
如果数据不符合下拉列表中的值,则会被标识。
六、数据验证:不能超过指定值。
目的:各部门的预算金额不能超过10000元。
方法:
1、选定目标单元格,【数据】-【数据验证】。
2、选择【允许】中的【自定义】,在【公式】中输入:=SUM($C$3:$C$7)