Excel技术 | 数据有效性6：阻止用户输入特定数据

下面的示例演示了在单元格中设置数据有效性，阻止用户在单元格输入特定的数据。

阻止用户输入重复值

有时候，我们要避免单元格中有相同的数据，譬如学生学号、序号等。下图所示的工作表中，在单元格区域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)