“数据有效性”几点妙用

在Excel中，利用“数据有效性”不但能够限制数值输入位数、限定数值输入范围及避免数据重复输入等，而且还能够轻松圈出指定数据。

限制数值的可输入位数

实例描述：小王是办公室的文秘，她需要录入单位职工的基本信息。由于身份证号码多达15或18位，有时多输一位，有时少输一位，输入起来非常容易出错。如果对单元格进行限制，当输入的位数不对时，就给出提示，岂不是很好？其实可以利用设置“数据有效性”就可以达到上述要求的。

首先，为了让输入的身份证号码能正确显示出来，需要输入身份证号码的单元格区域（如E3至E305）请将单元格设置为文本格式。然后，选择“数据→有效性”菜单，打开“数据有效性”对话框，在“设置”选项卡的有效条件“允许”处选择“自定义”，在出现的“公式”下面的方框中输入公式“=OR(LEN(E3)=15,LEN(E3)=18)”，再选择“出错警告”选项卡并设置一个“出错警告”，确定返回。这样，但输入的号码位数不正确时，系统会给出提示（图1）。

图1

避免重复数据的输入

实例描述：小王在手工录入职工信息时，职工的身份证号码都是唯一的。由于需要录入的职工信息有300多条，很容易造成视觉疲劳，看错行，从而造成数据的重复录入。为了解决上述问题，可以通过“数据有效性”来防止数据的重复输入。

选中需要输入身份证号码的单元格区域(如E3至E305)，同样选择“数据→有效性”菜单，打开“数据有效性”对话框，在“设置”选项卡的有效条件“允许”处选择“自定义”，然后在下面“公式”方框中可将公式修改为:=AND(COUNTIF(E:E,E3)=1，OR(LEN(E3)=15,LEN(E3)=18))，其中COUNTIF(E:E,E3)=1就是为了防止数据重复录入的公式，确定返回。

以后在上述单元格中无论输入了重复的身份证号码还是录入位数发生错误时，系统会弹出提示对话框，并拒绝接受输入的号码（图2）。

图2

限定输入数值的范围

实例描述：小郑是教务处的干事，需要输入学生的实验考查成绩，考查成绩通常介于0—20之间的整数。她对照成绩单用数字小键盘在Excel登记表录入成绩时，盲打但有时出错，输入的数值超过20。为了防止出错，可以利用“数据有效性”来设置一个输入范围。

首先，选中需要录入成绩的单元格区域（如C2:C1422），选择“数据→有效性”菜单，打开“数据有效性”对话框，在“设置”选项卡的有效条件“允许”处选择“整数”，在数据处选择“介于”，然后在下面分别输入最小值（“0”）和最大值（“20”），切换到“输入信息”选项卡，设置提示信息；切换到“出错警告”选项卡，设置出错提示及处理方法，全部设置完成后，确定返回。以后再输入成绩时，当输入的成绩不在0—20之间时，系统会给出相应的提示，并拒绝接受录入的数值。如果不进行“输入信息”和“出错警告”设置，不影响此功能的使用（图3）。

图3

圈出指定数据

实例描述：小李想把本班学生数学成绩不及格的学生在Excel表格中用红圈突出显示出来，以方便查阅。要想实现这样的要求，用Excel的数据有效性就能够轻松完成。

图4

首先，选中数学成绩所在的单元格（如C2:C62），选择“数据→有效性”菜单，在弹出的“数据有效性”对话框中，在“设置”选项卡的有效条件“允许”处选择“整数”，在数据处选择“介于”，然后在下面分别输入最小值（“60”）和最大值（“100”），点击确定；然后，选择“工具→公式审核→显示公式审核工具栏”菜单，在弹出的公式审核工具栏中单击“圈释无效数据”按钮，这样数学成绩在60分以下的就被圈上了一个红圈，起到了突出显示的作用（图4）。

返回网站首页