在【Excel系列16】数据有效性应用概览①一文中，我们系统地分析了数据有效性的基本功能、有效性条件的允许类别、设置数据有效性的提示信息、数据有效性条件的复制和更改等内容。在概述这些内容的基础上，今天我们通过实例来分析数据有效性的应用方法。
数据之道
一、实例①：只能输入整数
如下图所示的人事基本信息表，包含工号、姓名、性别、年龄、职称、基本工资等内容，那么如何在【年龄】项所在的列中限制只能输入整数呢？
人事基本信息表
具体操作如下图所示，几个关键点：（1）使用公式进行数据有效性的设置（2）【允许】类别中选择【自定义】（3）公式设置为=D2=INT(D2)，设置完毕后即实现只能输入整数。
只能输入整数
☆ 数据有效性中的自定义公式使用的工作原理
I. 自定义公式的作用在于判断输入的数值是否符合逻辑要求，如果符合要求，则允许输入
II. 当同时选中多个单元格批量设置有效性公式时，在公式中只需要以相对引用的方式来引用当前活动单元格地址即可。
在本例中INT()函数，功能为向下取整到最接近的整数，如INT(8.2)=8；
设置D2=INT(D2)，则输入的值必然为整数，否则不成立。
除了INT函数，MOD函数也可以实现
=MOD(D2,1)=0, 余数公式
在此基础上，我们可以对这个问题进行扩展，比如只能输入正整数
=(D2=INT(D2))*(D2>0)
只能输入三位以下的正整数
=(D2=INT(D2))*(D2>0)*(D2<100)或
=(D2=INT(D2))*(D2>0)*(LEN(D2)<3)
只能输入三位以下的正整数
二、实例②：只能输入文本
在上图的人事基本信息表中，如何在【职称】项所在的列中限制只能输入文本呢？
具体操作方法如下图所示，原理是一致的：采用自定义公式的方式，使用ISTEXT()函数，它的功能就是用来判断其中的参数是否为文本。
只能输入文本
除了ISTEXT函数，其他方式也可以实现：
=E2=E2&"" 强制转换为文本后是否还成立,或
=TEXT(E2,"@")=E2 文本转换函数TEXT
三、实例③：只能输入数值
在上图的人事基本信息表中，如何在【基本工资】项所在的列中限制只能输入数值呢？
具体操作方法如下图所示，采用自定义公式的方式，使用ISNUMBER()函数，它的功能就是用来判断其中的参数是否为数值。
只能输入数值
四、实例④：限制输入重复值
如果要在人事基本信息表中【姓名】项所在的列中限制姓名重复输入，怎么做呢？
具体操作方法：选中单元格数据区域——数据有效性——自定义公式——COUNTIF函数
限制重复值输入
COUNTIF（range，criteria），条件计数函数
range表示要计算其中非空单元格数目的区域，criteria表示统计条件
=COUNTIF($B$2:$B$5,B4)=1，表示单元格内的值出现次数为一次
注：数据范围绝对引用，数据单元格地址相对引用；大家可以想想为什么这么用~
☆ 思考题
如何输入18位数的身份证号且不重复
下一讲将会给大家继续【数据有效性应用实例③】，主要研究下拉菜单、二级下拉菜单的设置使用方法。
也请大家关注文集Excel常用技巧，与大家一起共同成长学习。
如果你觉得不错或者有用，希望大家能点个喜欢♡
网友评论