如下图所示的工作表中A列用于存放职工工号。由于姓名允许重复,但工号不允许重复,因此要求在A输入数据时,Excel自动检查是否重复,如果重复则弹出提示信息,要求用户重新输入。

Excel 如何禁止输入重复值-Excel22

解题步骤

限制某区域不能输入重复值宜用数据验证,具体步骤如下。

1.选择A2:A10区域。

2.单击功能区的“数据”→“数据验证”,弹出“数据验证”对话框。

3.将对话框中“允许”下拉列表中的“任何值”修改为“自定义”。

4.在公式框中输入以下公式,操作界面如图3.14所示。

Excel 如何禁止输入重复值-Excel22

5.打开“数据验证”对话框的“出错警告”选项卡,“样式”保持“停止”不变,将“标题”设置为“友情提示”,将“错误信息”设置为“当前值在A2:A10区域已经存在,请重新录入”,设置界面如图3.15所示。

Excel 如何禁止输入重复值-Excel22

图3.14 使用公式限制A2∶A10只能输入唯一值

Excel 如何禁止输入重复值-Excel22

图3.15 设置出错警告

6.单击“数据验证”对话框中的“确定”按钮返回工作表界面,在A2:A3单元格输入工号34和35,然后在A4单元格再次输入工号34,当单击Enter键后,Excel会弹出图3.16所示的提示框,必须单击“重试”按钮,然后输入其他不重复的值,否则无法输入成功。

Excel 如何禁止输入重复值-Excel22

图3.16 输入重复值时的错误提示

知识扩展

1.COUNTIF函数属于Excel的统计函数,用于计算区域中符合某个条件的数据个数。例如,等于100的单元格数量、大于999的单元格数量、包含“合格”二字的单元格数量,或者计算不等于某个单元格的单元格数量。本例采用COUNTIF函数计算A2:A10区域中值等于当前单元格的值的单元格数量。

2.COUNTIF函数的语法如下:

COUNTIF(range,criteria)

它包含两个必选参数,第一参数代表要对其进行计算的区域,第二参数代表要计算的条件。假设要计算A1:A10区域中大于500的单元格数量,那么宜采用以下公式:

=COUNTIF(A1:A10,">500")

假设要计算A1:A10区域中等于500的单元格数量,那么宜采用以下公式:

=COUNTIF(A1:A10,500)

当第二参数是数字时可以不用等号也不加双引号。

假设要计算A1:A10区域中包含“螺丝”的单元格数量,那么宜采用以下公式:

=COUNTIF(A1:A10,"*螺丝*")

第二参数“*螺丝*”表示包含“螺丝”,前后的“*”代表任意长度的任意字符,即以任意字符开始、任意字符结尾、中间包含“螺丝”二字的字符串。公式的运算效果如图3.17所示。

Excel 如何禁止输入重复值-Excel22

图3.17 计算A1∶A10区域中包含“螺丝”的单元格数量

3.本例中公式“=COUNTIF($A$2:$A$10,A2)=1”代表A2:A10区域中任意单元格的值只允许出现一次,如果重复则会弹出提示对话框警告用户。

4.数据验证仅对设置后手工输入数据有效,如果先输入字符后设置数据验证,那么 Excel不会提示不符合规则的数据。

5.数据验证对公式也没有限制,限制A1单元格只能输入1~10之间的数值,当在A1单元格手工输入11时会禁止输入,但是A1单元格通过公式引用A2单元格的值,在A2单元格中输入11时,A1单元格也会产生11,而且不产生任何提示。

请在封面中缝指定的QQ群下载文件和视频教材,从而方便练习,同时验证本案例中的技巧与操作结果。