Excel数据有效性应用常见五大案例，讲解透彻，请收藏好~~

➤ 案例1：只能输入不重复的值。

之前我们分享的条件格式大家还记得吗？把表格里重复的值突显出来。今天我们再分享一招，重复值禁止第二次输入。

搭档函数：条件统计函数Countif

步骤：先选中A列，然后点击数据->数据验证（2013版之前的叫作数据有效性），然后在自定义输入框中输入=Countif(A:A,A1)=1，意思是判断A列中A1的个数是否为1，我们只允许出现1个。

当在A列中的数据第二次出现时，会自动弹出对话框进行提醒，这样，我们就不会重复录入数据了。

➤ 案例2：强制序时录入

就是强制按从小到大或从大到小的顺序录入数据。

搭档函数：最大值函数Max

要强制序时录入，首先我们就要先判断目前已记录的数据最大值是哪一个，这样录入时才能自动比较大小。

步骤：先选中A列，然后点击数据->数据验证（2013版之前的叫作数据有效性），然后在允许中选择“日期”，然后数据中选择“大于或等于”，输入框中输入=max($A1:A$2)。然后按确定，这样A列中输入的日期必须大于或等于已有的日期才能通过。

➤ 案例3：只允许输入数字

搭档函数：数字函数ISNumber,检测一个值是否是数值。

步骤：先选中A列，然后点击数据->数据验证（2013版之前的叫作数据有效性），然后在自定义输入框中输入=ISNumber(A1)=true，按确定。

➤ 案例4：只允许输入某个特征的数据

搭档函数：OR或函数，Left左截取函数。

以下案例是说单元格内只允许输入“李”开头或“王”开头的数据，问你怎么办？

步骤：先选中A列，然后点击数据->数据验证（2013版之前的叫作数据有效性），然后在自定义输入框中输入=OR(LEFT(A2,1)="李",LEFT(A2,1)="王")，按确定。

➤ 案例5：身份证号长度验证

设置身份证号长度的验证，你觉得要怎么下手比较好？

有经验的人会发现我们的身份证号有两个特点：

1、身份证位数（是否为15位或18位）

2、日期是否合法（主要是判断月份是否在1-12之间，日期是否超出当月的天数等）

所以我们的函数就是要解决这两个问题，判断的函数比较多，比较长。在公式输入框中输入=AND(OR(LEN(A1)=15,LEN(A1)=18),1*TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"))

搭档函数：TEXT文本格式函数，MID中间截取函数，LEN文本长度函数,OR或函数,AND与函数。

这一串这么长的函数嵌套什么意思呢？我们来分解一下：

首先，我们来解决身份证位数问题，判断是否是15位还是18位。

在这里我们用OR(LEN(A1)=15,LEN(A1)=18),意思是要输入的数字必须满足15位或18位都可以，这个没问题吧？

其次，我们知道身份证号里包含了我们的出生日期，这个格式是否合理。在这里我们就要把身份证号中的出生日期提取出来，判断它的格式是否满足日期格式。由于身份证号有15位和18位，如果是15位的话，年份里是没有19的，所以我们要考虑到这一点。

我们用1*TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")来表示。

若LEN（A1）=15为真，即身份证号为15位，则(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2)的运算=1*19&MID（A1,7,6+0*2）=1*19&MID（A1,7,6）,MID(A1,7,6)表示从身份证号的第7位开始，截取6位，这6位的位置刚好是出生年月日的位置，由于15位的身份证号是没有19的，所以前面用&连接19。
若LEN（A1）=15为假，即身份证号为18位，则(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2)的运算=0*19&MID（A1,7,6+1*2）=0*19&MID（A1,7,8）,MID(A1,7,8)表示从身份证号的第7位开始，截取8位，这8位的位置刚好是出生年月日的位置。
最后使用TEXT函数把这8位数转换成日期格式，再用1*TEXT（）强制转换成数值，这样才能判断真假，非0的数值都是返回TRUE。

最后，我们要以上两个条件同时满足才算数据是符合要求的，所以用AND连接起来。

- END -

重要的事情说三遍，今晚8点Excel公开课，不要错过哦！

今晚8点Excel公开课

加入QQ群：518462671，下载今晚课堂所需课件，跟老师一起练习！

分享到： QQ空间新浪微博腾讯微博人人网