下面的示例演示了在单元格中设置数据有效性,阻止用户在单元格输入特定的数据。
阻止用户输入重复值
有时候,我们要避免单元格中有相同的数据,譬如学生学号、序号等。下图所示的工作表中,在单元格区域A2:A15设置数据有效性,阻止用户在该区域输入重复值。在“数据有效性”对话框的“允许”下拉框中选择“自定义”,在“公式”中输入:
=COUNTIF($A$2:$A$15,A2)<=1
注:可以使用名称代替单元格区域。
阻止用户在多列中输入重复值
使用数据有效性,可以避免用户在一行的多列中输入与前面行相同的值。如下图所示的工作表,有三列数据,要避免用户在某一行的3列中输入的数据与前面的行中数据相同,可以选取相应的单元格区域,本例中为单元格区域C2:C10,在“数据有效性”对话框中选取“允许”下拉框中的“自定义”,在“公式”中输入:
=COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,B2,$C$2:$C$10,C2)<=1
要求输入不重复的数据且限定数据位数
如下图所示,要求用户在单元格区域中输入6位数的学号且不能重复输入。选择单元格区域A2:A10,在“数据有效性”对话框中,选取“允许”下拉框中的“自定义”,在“公式”中输入:
=AND(ISNUMBER(A2),LEN(A2)=6,COUNTIF($A$2:$A$10,A2)<=1)
限制单元格区域中数值的总和
如下图所示的工作表,要求检测的5项值的总和不能超过10。选择单元格区域B2:B6,在“数据有效性”对话框中,选取“允许”下拉框中的“自定义”,在“公式”框中输入:
=SUM($B$2:$B$6)<=10
阻止用户在开头和结尾处输入空格
为避免用户在数据的开头或者结尾输入空格,可以设置数据有效性如下:选取单元格区域,如本例中的单元格A1,在“数据有效性”对话框中选取“允许”下拉框中的“自定义”,在“公式”框中输入:
=A1=TRIM(A1)
阻止用户在数据中输入空格
下面的数据有效性设置将阻止用户在输入数据时输入空格,包括开头和结尾的空格。选取相应的单元格区域,如A1,在“数据有效性”对话框中,选取“允许”下拉框中的“自定义”,在“公式”框中输入:
=A1=SUBSTITUTE(A1,'','')
或者输入公式:
=LEN(A1)=LEN(SUBSTITUTE(A1,'',''))
或者输入公式:
=ISERROR(FIND('',A1))
阻止用户输入周末日期
下面的数据有效性设置将阻止用户在指定单元格中输入周末的日期。选取相应的单元格区域,如A1,在“数据有效性”对话框中,选取“允许”下拉框中的“自定义”,在“公式”框中输入:
=AND(WEEKDAY(A1)<>1,WEEKDAY(A1)<>7)
对前面关于数据有效性的文章有兴趣的朋友,可以点击下面的链接:
如果您对本文介绍的内容有什么建议或好的示例,欢迎发送邮件给我:xhdsxfjy@163.com
也可以在本文下面发表留言,留下您的足迹。
本文属原创文章,转载请联系我或者注明出处。
关注《完美Excel》微信公众账号:
联系客服