有效数据外含有额外数据_Excel｜应用数据有效性规范数据录入

【问题】

EXCEL输入数据时，经常会输入不规范或者无效的数据，对数据的统计工作带来很大的麻烦。

数据验证能够建立特定的规则，限制单元格可以输入的内容，从而规范数据输入，提高数据统计与分析效率。

数据验证，在EXCEL2010及以前的版本中称为“数据有效性”。

一、规范性别输入

利用数据验证输入性别，不仅数据规范，而且快速。

【数据】菜单中，选择【数据验证】，在【数据验证】窗口中选择【设置】选项卡中，允许【序列】，数据来源中选择“男，女”，如下动图：

设置了【数据验证】后，输入性别是只需选择“男”或“女”就可以了。

特别注意：

1、序列来源中，“男”与“女”两个字之间一定是“英文状态”的逗号，即半角逗号；

2、只要对一个单元格设置了数据验证，鼠标拖动单元格右下角填充柄，进行填充，即可将“数据验证”填充到其他单元格，

二、限定输入内容

很多时候，输入要求只在某些特定值内选择，比如，图 1‑82所示的等级，只有优秀、良好、合格、不合格四个等级，所有姓名对应的等级必须出自这四个其中之一，而再无其他，这个时候，可以利用数据验证规范等级的输入。

在【数据验证】窗口中选择【设置】选项卡中，允许【序列】，数据来源中选择四个等级所在的L2:L5区域，如下动图：

设置了【数据验证】后，输入等级时只要选择其中之一就可以了。

当然，输入的内容也可以用第一种输入性别的方法以序列的方式直接写入来源。

三、限定数字范围

在【数据验证】窗口中选择【设置】选项卡中，允许【整数】，设置数据“介于”，可以限定输入数字的范围。

1、 静态限制输入数值范围：

直接输入最大值与最小值，如下动图：

2、动态限制输入数值范围：

这种动态限定数据的方法，设置了数据验证以后，可以通过修改最小值与最大值单元格的数值，动态调整数据允许输入的范围，如下动图：

四、限定文本长度

在【数据验证】窗口中选择【设置】选项卡中，允许【文本长度】为11，如下动图：

五、限制输入重复信息

【数据】——【数据验证】：验证条件选择自定义，输入公式：“=COUNTIF(H:H,H1)=1”，可以禁止输入重复数值。

公式的含义是：H列中，H1单元格的内容只出现1次。如果H列中H1单元格内容出现次数超过1，则禁止输入，如下动图：

六、限定身份证号码

【数据】——【数据验证】：验证条件选择自定义，输入公式：“=AND(LEN(H1)=18,COUNTIF(H:H,H1&"*")=1)”，如下动图：

其中：

LEN(H1)=18：含义是H1单元格内容的长度为18位；

COUNTIF(H:H,H1&"*")=1：在H列中H1单元格内容只出现1次，也就是不能重复出现；

AND(LEN(H1)=18,COUNTIF(H:H,H1&"*")=1)：同时满足内容长度为18位、不能重复出现两个条件。

七、限制输入空格

【数据】——【数据验证】：验证条件选择自定义，输入公式：

“=ISERR(FIND(