excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)

很多"表哥"、"表姐"抱怨:为啥做个表格这么难?

不是输入的身份证号码位数不对,就是输入的数据重复……各种问题层次不穷。

我想说:为什么不同数据验证呢?它不仅可以将输入的数字限制在指定范围内,也可以限制文本的字符数,还可以将日期或时间限制在某一时间范围之外,甚至可以将数据限制在列表中的预定义项范围内,对于复杂的设置也可以通过自定义完成。

这么好用的功能,为啥不用呢?

今天我们分享的就是如何对数据验证的条件进行设置。

1.设置单元格内小数的输入范围

在Excel工作表中编辑内容时,为了确保数值中小数输入的准确性,可以设置单元格中小数的输入范围。

例如,在新进员工考核表中需要设置各项评判标准的分数取值范围,要求只能输入1~10之间的数值,具体操作步骤如下。

Step 01 执行数据验证操作。选择要设置数值输入范围的B3:E13单元格区域,单击【数据】选项卡【数据工具】组中的【数据验证】按钮,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(1)

Step 02 设置小数验证条件。打开【数据验证】对话框,在【允许】下拉列表中选择【小数】选项,在【数据】下拉列表中选择【介于】选项,在【最小值】参数框中输入单元格允许输入的最小限度值【1】,在【最大值】参数框中输入单元格允许输入的最大限度值【10】,单击【确定】按钮,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(2)

Step 03 验证设置的条件。完成对所选区域数据输入范围的设置。在该区域输入范围外的数据时,将打开提示对话框,如下图所示,单击【取消】按钮或【关闭】按钮后输入的不符合范围的数据会自动消失。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(3)

技术看板

当在设置了数据有效性的单元格中输入无效数据时,在打开的提示对话框中,单击【重试】按钮可返回工作表中重新输入,单击【取消】按钮将取消输入内容的操作,单击【帮助】按钮可打开【Excel 帮助】窗口。

Step 04 输入正确数据后的效果。在表格区域中可输入大于1小于10之间带小数的数值或整数数值,效果如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(4)

2.设置单元格内整数的输入范围

在Excel中编辑表格内容时,某些情况下(如在设置年龄数据时)还需要设置整数的取值范围。其设置方法与小数取值范围的设置方法基本相同。

例如,在劳动合同签订统计表中将签订的年限设置为整数,且始终小于等于5,具体操作步骤如下。

Step 01 选择单元格区域。选择要设置数值输入范围的 F3:F10单元格区域,单击【数据验证】按钮 ,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(5)

Step 02 设置数据验证条件。打开【数据验证】对话框,在【允许】下拉列表中选择【整数】选项,在【数据】下拉列表中选择【小于或等于】选项,在【最大值】参数框中输入单元格允许输入的最小限度值【5】,单击【确定】按钮,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(6)

Step 03 验证设置的条件。返回工作表编辑区,在设置的单元格中输入大于6的整数或任意小数时,都会打开错误提示对话框,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(7)

3.设置单元格文本的输入长度

在表格中输入的文本或数字字符数较多时,为了保证输入的字符数刚刚合适,可以通过数据验证功能限制单元格中输入的字符长度。当输入的字符数超过或低于设置的长度时,系统将进行提示。

例如,在劳动合同签订统计表中将身份证号码的输入长度限制为18个字符,具体操作步骤如下。

Step 01 执行数据验证操作。选择要设置文本长度的D3:D10单元格区域,单击【数据验证】按钮,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(8)

Step 02 设置文本长度限制条件。打开【数据验证】对话框,在【允许】下拉列表中选择【文本长度】选项,在【数据】下拉列表中选择【等于】选项,在【长度】参数框中输入单元格允许输入的文本长度值【18】,单击【确定】按钮,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(9)

Step 03 验证设置的条件。此时,如果在 D3 单元格中输入了低于或超出限制长度范围的文本,则按【Enter】键确认后将打开提示对话框提示输入错误,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(10)

技术看板

输入身份证号码时,如果是通过英文状态的单引号【'】将输入的身份证号码转换为文本型数值时,那么,【'】将不计入限制的文本长度中。

4.设置单元格中准确的日期范围

在工作表中输入日期时,为了保证输入的日期格式是正确且有效的,可以通过设置数据验证的方法对日期的有效性条件进行设置。

例如,在劳动合同签订统计表中将合同签订的日期限定在2019年1月1日至2019年1

月31 日,具体操作步骤如下。

Step 01 选择设置区域。A选择要设置日 期 范 围 的 E3:E10 单 元 格 区 域, B单击【数据验证】按钮 ,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(11)

Step 02 设置验证条件。打开【数据验证】对话框,在【允许】下拉列表中选择【日期】选项,在【数据】下拉列表中选择【介于】选项,在【开始日期】参数框中输入单元格允许输入的最早日期【2019-1-1】,在【结束日期】参数框中输入单元格允许输入的最晚日期【2019-1-31】,单击【确定】按钮,如下图所示,即可限制该单元格只能输入 2019-1-1到 2019-1-31 之间的日期数据。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(12)

5.制作单元格选择序列

在Excel中,可以通过设置数据有效性的方法为单元格设置选择序列,这样在输入数据时就无须手动输入了,只需单击单元格右侧的下拉按钮,从弹出的下拉列表中选择所需的内容即可快速完成输入。

例如,为劳动合同签订统计表中的部门列单元格设置选择序列,具体操作步骤如下。

Step 01 选择设置区域。选择要设置输入序列的 C3:C10 单元格区域,单击【数据验证】按钮 ,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(13)

Step 02 设置验证条件。打开【数据验证】对话框,在【允许】下拉列表中选择【序列】选项,在【来源】参数框中输入该单元格允许输入的各种数据,且各数据之间用半角的逗号【,】隔开,这里输入【销售部,行政部,人事部,财务部,生产部】,单击【确定】按钮,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(14)

技术看板

设置序列的数据有效性时,可以先在表格空白单元格中输入要引用的序列,然后在【数据验证】对话框中的【来源】参数框中通过引用单元格来设置序列。

Step 03 查看序列效果。经过以上操作后,单击工作表中设置了序列的单元格时,单元格右侧将显示一个下拉按钮。单击该按钮,在弹出的下拉列表中提供了该单元格允许输入的序列,如下图所示,用户从中选择所需的内容即可快速输入数据。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(15)

6.自定义验证条件

遇到需要设置复杂数据有效性的情况时,就要结合公式来进行设置了。

例如,在劳动合同签订统计表中,为了保证输入的员工编号是唯一的,可以通过公式来进行限制,具体操作步骤如下。

Step 01 选择设置区域。选择要设置自定义数据验证的A3:A10单元格区域,单击【数据验证】按钮,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(16)

Step 02 设置自定义条件。打开【数据验证】对话框,在【允许】下拉列表中选择【自定义】选项,在【公式】参数框中输入【=COUNTIF($A$3:$A$10,A3)=1】,单击【确定】按钮,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(17)

Step 03 验证设置的条件。返回工作表编辑区,当在A3:A10单元格区域中输入重复的员工编号时,就会打开错误提示对话框,如下图所示。

excel中取消单元格数据验证限制(Excel:如何通过设置数据验证条件)(18)

与小站一起,每天进步一点点。

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页