利用数据有效性数据的输入做些限定工作以防人为错误(数据有效性可以保证一定不会输入错误数据吗)

网友投稿 831 2022-06-01

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小时内删除侵权内容。

上一篇:迷你图是什么东东有哪些作用如何使用迷你图(迷你图的作用)
下一篇:怎么显示文档修改标记(如何显示文档修改标记)
相关文章