如何控制excel单元格只能输入规定长度和防止重复录入?

网友投稿 871 2022-10-28

如何控制excel单元格只能输入规定长度和防止重复录入?

问题:如何控制单元格只能输入规定长度和防止重复录入?

解答:利用公式数据有效性搞定!

实际上这个问题涉及二个问题,一是控制录入的长度,二是控制不允许重复录入。

首先搞定录入长度的控制的。具体操作方法如下:

赶紧测试一下,录入不是11位就报错。

但这样显示报错太不人性化了。所以改进一下,在之前数据有效性的设置界面里,切换置“出错警告”(下图3处)。写上出错警告信息即可。(下图4处)

如果录入的人未输入到11位,就会弹如下报错!

如何控制excel单元格只能输入规定长度和防止重复录入?

进去后发现糟糕,这不是之前设置过的限制11位录入的设置吗?难道修改11位录入的限制?也就说数据有效性不能像Excel的条件格式一样可以在同一数据区域设置多个条件格式。也就说数据有效性在一个区域只能设置一次。这就麻烦了,如何能保证这样双重的数据有效性呢?

所以改变思路,双重数据有效性必须要利用公式。首先牛闪闪将设置可以搞定位数控制,用公式来代替。具体方法如下:

在“数据验证”设置界面,选择允许“自定义—等于”,在公式中输入=LEN(E4)=11

公式很容易理解,len为单元格长度函数,判断起始单元格E4单元的长度,只有等于11才允许录入。否者报错。(下图6处)

好!大家先把这个公式给记下来放在一边,接着再用公式解决的思路,解决重复的问题。利用countif函数判断手机号码所在的区域的数值,如果每个手机号在该区域的号码个数只有一个,则允许录入。

所以限制重复录入的公式写成这样。

=COUNTIF($E$4:$E$23,E4)=1

判断每个录入的手机号在该区域的个数只有一个,才允许录入。(下图7处)

测试一下!果然可以,顺道把出错警告加一句,且唯一。

从前面的讲解中,大家可以了解到,用len函数解决11位限制录入。用countif函数解决限制重复录入。但这两个条件都要满足怎么办?所以and函数出场。把前面两个函数用and函数合并一下。公式的位置可以写成这样:

=AND(LEN(E4)=11,COUNTIF($E$4:$E$23,E4)=1)

满足当单元格位数等于11位,且不重复,才允许录入。(下图8处)

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:如何快速知道excel单元格是否为文本还是数值格式?
下一篇:excel如何快速统计出某一分类的最大值?
相关文章