【Excel系列17】数据有效性应用实例②

       在【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常用技巧，与大家一起共同成长学习。

       如果你觉得不错或者有用，希望大家能点个喜欢♡