excel数据超过范围怎么设置（如何限制Excel单元格输入范围）

很多“表哥”、“表姐”抱怨：为啥做个表格这么难？

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

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

这么好用的功能，为啥不用呢？

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

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

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

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

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

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

技术看板

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

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

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

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

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

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

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

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

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

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

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

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

技术看板

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

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

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

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

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

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

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

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

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

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

技术看板

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

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

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

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

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

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

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