利用数据有效性对数据的输入做些限定工作以防人为错误（当输入的数据违反的有效性规则,系统会用它来提示出错）

Excel具有强大的制表功能，给我们的工作带来了方便，但是在数据录入的过程中也容易出错，因为Excel默认是可以输入任何数据的。有时候为了防止人为的错误，我们需要对数据的输入做些限定工作，如限定身份证号的输入位数，限定人的性别只有男和女两种等等。这需要借助Excel的“数据有效性”功能。

“数据有效性”功能位于“数据”功能区的“数据工具”部分。

数据有效性设置

现有一个Excel工作簿如图 1所示。其中“通讯录”放在工作表1（通讯录表）中，所有的地区放在工作表2（地区表）中，如图 2所示。现需要在用户输入数据时限定输入的条件和范围，如身份证号只能输入18位的二代身份证号，性别只能输入“男”或者“女”，所在地区只能从“地区表”中获取。

图1

图2

示例1：限定身份证号的位数

在图 1中，限制身份证号输入的位数。

操作步骤：

1） 选定用来输入身份证的单元格或列（图 1中的C列）。

2） 点选“数据—>数据工具—>数据有效性—>数据有效性…”命令，打开“数据有效性”设置对话框，如图 3所示。

图3

3） 在图 3中“有效性条件”选择“文本长度”，运算符选择“等于”，长度设置为“18”。

注：

在图 3中有4个选项卡：设置、输入信息、出错警告、输入法模式。“设置”是用来设置数据输入限定条件的，默认是“任何值”。可以设置的“有效性条件”有：整数、小数、序列、日期、时间、文本长度和自定义。

4） 然后转到“输入信息”选项卡，如图 4所示，在图 4的标题部分输入“输入身份证号”，输入信息部分输入“请输入18位的二代身份证号”。

图4

5） 转到“出错警告”选项卡，如图 5所示，在其中标题部分输入“输入错误”，错误信息输入“您输入的身份证号码非法”。

图5

6） “输入法模式”选项卡不做修改，即保持输入模式不变，然后单击“确定”，回到Excel工作表。此时可以看到当光标在C列任何一个单元格时，系统都会显示：“输入身份证号  请输入18位的二代身份证号”，这就是我们刚才在“数据有效性”对话框的“输入信息”部分输入的内容。

图6

7） 随意地在C列单元格中输入数据，然后按Enter回车键确认。如果你输入的是18位（如果输入的全是数字，请记住在其前面加“’”，将数字转换为文本），那么系统不会有任何提示；否则，系统会提示错误信息，如图 7所示，错误信息是我们刚才在“数据有效性”对话框的“出错报警”部分输入的内容。

图7

%小提示：

“数据有效性”设置只会对设置以后再输入的单元格有效，对于已经输入了的单元格，Excel不会重新检查其是否合法。因此，一定要在输入之前设置单元格输入数据的有效性。

%原理解析：

在Excel默认的情况下，之所以单元格能够输入任何类型的数据，就是因为在此“数据有效性”设置中，默认允许的是“任何值”。

示例2：限定性别的输入

在图 1中，限制性别的输入只能是“男”或者“女”。

操作步骤：

1） 选定性别列（D列）。

2） 同样打开“数据有效性”对话框（点选“数据—>数据工具—>数据有效性—>数据有效性…”命令），在“设置”部分设置有效性条件为“序列”，来源设为“男,女”如图 8所示。

图8

%注意：

“男”和“女”序列之间的“,”必须为英文的逗号，不能用中文的逗号。

3） 设置完成后，点击“确定”回到Excel工作表。

4） 回到Excel工作表后，可以看到，在D列任意一个单元格的右侧会多出来一个下拉框，里面包含“男”和“女”，可以在里面选择输入，如图 9所示。

图9

%小提示：

也可以直接在D列的单元格中输入数据，但是只能输入“男”或者“女”，否则Excel就会报错。

示例3：限定所在地区

在图 1中，限制“所在地区”的输入只能从“地区表”中获取。

这里有两种方法来实现这一目标。方法一是直接通过引用条件限定区域；方法二是借助定义域名来设置引用区域。

方法一操作步骤：

1） 选定所在地区列（E列）。

2） 同样打开“数据有效性”对话框（点选“数据—>数据工具—>数据有效性—>数据有效性…”命令），在“设置”部分设置有效性条件为“序列”，“来源”部分通过点开右侧的

，然后选中“地区表”的A列，如图 10所示。

图10

3） 设置完成后，点击“确定”回到Excel工作表。此时会发现所在地区已经限定了输入下拉框。

图11

%小提示：

在Excel2003及以前版本中，借助是无法跨工作表选择数据的，因此本方法在Excel2003中无法实现。

方法二操作步骤：

1） 选中“地区表”A列中已有地区数据的单元格，然后在名称框中输入“所属地区”（图 12中红框所示，即给这些地区起个域名叫“所属地区”）。

图12

2） 回到“通讯录”工作表，选定所在地区列（E列）。

3） 同样打开“数据有效性”对话框（点选“数据—>数据工具—>数据有效性—>数据有效性…”命令），在“设置”部分设置有效性条件为“序列”，“来源”输入“=所属地区”，如图 13所示。

图13

4） 设置完成后，点击“确定”回到Excel工作表。此时会发现所在地区已经限定了输入下拉框。

%小提示：如何去掉数据有效性设置

直接在“数据有效性”对话框中将“有效性条件”设置为允许“任何值”即可。

2. 圈释无效数据

在前面介绍过，“数据有效性”设置只对设置后输入数据的单元格有效，对于设置前已经输入的单元格是不会检验的，那么如何检验先前输入的数据是否有效，或者已有的数据表是否符合我们的要求？这就可以借助于“圈释无效数据”功能来实现。

以图 1所示的例子为例，在前面已经设定了身份证号、性别和所属地区的数据有效性规则。现在可以用“圈释无效数据”来找出不合格的数据：选中数据区域，然后点选“数据—>数据工具—>数据有效性—>圈释无效数据”命令，将得到如图 14所示的结果。

图14

因为在前面设置身份证号位数、性别和所在地区时对C1、D1和E1单元格都包含进去了，所以最后Excel会检验出这三个单元格数据无效。

%注意

圈释无效数据必须先设定了数据有效性条件。

%小提示：如何清除无效数据标识圈

点选“数据—>数据工具—>数据有效性—>清除无效无效数据标识圈”命令。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。