excel 数据验证基本设置技巧

Excel教程 2021-10-04 08:39:46

数据验证可以规范用户的文本及数字输入格式，如只能输入指定区间的数值、只能输入文本数据、限制输入空格、限制输入重复值等。设置了数据验证条件后，对符合条件的数据允许输入，对不符合条件的数据则禁止输入。因此，利用此设置可以实现数据的正确性和有效性的检查，避免输入错误的数据。

另外，还可以设置输入提示信息。即对选中单元格的数据输入做出提醒；同时也能设置出错警告信息，即当输入错误时弹出警告信息。本文主要介绍数据验证基本设置的十个技巧：

数据验证基本设置技巧

选中单元格时给出输入提示

本例要求在列表中输入“一中高考成绩”，为了提醒录入者输入正确范围的数据，可以设置当选中该区域的单元格时自动显示提示文字。

具体步骤为：选中B3:E10单元格区域 - 在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 切换至“输入信息”选项卡 - 确认勾选“选定单元格时显示输入信息” - 在“标题”文本框中输入“请输入分数” - 在“输入信息”文本框中输入“语数英分数在0-150之间！理综分数在0-300之间！” - 单击“确定”按钮完成设置。当选中B3:E10区域中的任意单元格时，会显示输入的提示文字。

设置输入数据大小的区间

除了设置输入提示之外，还可以设置当输入的数字不在某个特定区间内时自动弹出输入错误提示框，以提示重新输入符合数据范围的数字。以理综成绩为例，我们可以设置允许区间为0-300，当输入的数据不在这个区间时则会弹出输入错误提示框。

具体步骤为：选中E3:E10单元格区域 - 在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 选择“设置”选项卡（默认）- 在“验证条件”栏下设置“允许”条件为“整数”，“数据”条件为“介于”，“最小值”和“最大值”分别为“0”和“300” - 单击“确定”按钮完成设置。当在E3:E10区域中的任意单元格中输入不在0-300之间的数值时就会弹出错误提示框，提示重新输入符合要求的数据。

输入数据不超过某个值

本例中需要设置“语文/数学/英语”的成绩不能超过150.

具体步骤为：选中B3:D10单元格区域 - 在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 选择“设置”选项卡（默认）- 在“验证条件”栏下设置“允许”条件为“整数”，“数据”条件为“小于或等于”，“最大值”为“150” - 单击“确定”按钮完成设置。当在B3:D10区域中的任意单元格中输入大于150的数值时就会弹出错误提示框，提示重新输入符合要求的数据。

限定单元格只能输入日期

本例表格中要求在“订购日期”列中只能输入日期数据，并且日期只能介于2019/1/1-2019/12/31之间。

具体步骤为：选中A3:A13单元格区域 - 在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 选择“设置”选项卡（默认）- 在“验证条件”栏下设置“允许”条件为“日期”，“数据”条件为“介于”，“开始日期”和“结束日期”分别为“2019/1/1”和“2019/12/31” - 单击“确定”按钮完成设置。当在A3:A13区域中的任意单元格中输入格式不相同的日期（如：20190909）或者不在设定区间内的日期（如：2018/8/8）时就会弹出错误提示框，提示重新输入符合要求的数据。

限定输入数据的长度

本例表格统计人员出生日期，为了统一格式，减少输入错误，可以设置验证条件使文本长度必需为8位。

具体步骤为：选中B2:B9单元格区域 - 在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 选择“设置”选项卡（默认）- 在“验证条件”栏下设置“允许”条件为“文本长度”，“数据”条件为“等于”，“长度”为“8” - 单击“确定”按钮完成设置。当在B2:B9区域中的任意单元格中输入长度不等于8的文本时就会弹出错误提示框，提示重新输入符合要求的数据。

建立可选择输入的列表序列

当我们需要记录大量数据用于后期数据分析，而这些数据内容又是固定几类的情况下，如果我们不设定数据验证，输入时不但费时费力，还很容易出现输入错误，有空格等问题，后期数据统计分析也会随之不准确。而如果我们使用数据验证“列表”功能限定单元格输入内容时，只需要通过下拉列表进行选择就可以完成快速输入，既提高了工作效率，又保证了数据的准确性。

具体步骤为：选中需要设置数据有效性的列（C列）- 在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 - 选择“设置”选项卡（默认）- 在“验证条件”栏下设置“允许”条件为“序列”，单击“来源”文本框右侧的“拾取器”按钮，返回数据表并选择之前建立好的序列（G3:G7） - 再次单击“拾取器”按钮，返回“数据验证”对话框 - 单击“确定”按钮完成设置。此时我们选中C列任意单元格并点击右侧出现的下拉按钮，便可从下拉列表中选择所要输入的内容以快速准确地完成输入。

将数据验证条件复制到其他工作表

本例需要在“将数据验证条件复制到其他工作表”工作表中设置与“选中单元格时给出输入提示”工作表相同的数据验证，这里可以直接复制数据验证，而不需要重新在新工作表中逐步设置数据验证。

具体步骤为：在“选中单元格时给出输入提示”工作表中复制带有数据验证的区域 - 在“将数据验证条件复制到其他工作表”工作表中选择需要复制数据验证设置的区域的第一个单元格（B3） - 点击鼠标右键 - 选择“选择性粘贴”，打开“选择性粘贴”对话框 - 选择“验证” - 点击“确定”按钮完成数据验证设置的复制。

设置出错警告内容

本例要求在列表中输入“一中高考成绩”，为了提醒录入者输入正确范围的数据，可以设置当输入数据不正确时弹出警告对话框，以提醒录入者重新输入正确数据。例如录入者输入的理综成绩大于300，则要求弹出输入错误对话框，对话框标题为“认真点！！！”，错误信息为：“亲，写错了，再认真检查一下吧！！！”

具体步骤为：选中E列 - 在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框 -选择“设置”选项卡（默认）- 在“验证条件”栏下设置“允许”条件为“整数”，“数据”条件为“介于”，“最小值”和“最大值”分别为“0”和“300”- 切换至“出错警告”选项卡 - 确认勾选“输入无效数据时显示出错警告” - 在“标题”文本框中输入“认真点！！！” - 在“错误信息”文本框中输入“亲，写错了，再认真检查一下吧！！！” - 单击“确定”按钮完成设置。当在E列任意单元格中输入不在0-300之间的数据时，便会弹出输入错误对话框。

圈释无效数据

“圈释无效数据”功能是指将不符合条件的数据以红色圆圈圈释出来。要使用该功能，必需先输入数据，然后针对已有的数据来设置数据验证条件，再将不满足条件的数据圈释起来。例如本例将“语文数学英语”大于150的无效数据圈释起来。

具体步骤为：选中已输入“语文数学英语”成绩的区域（B3:D10）- 按前面介绍的方法设置数据验证有效区间为：0-300 - 选中待圈释无效数据区域（B3:D10）- 在“数据”选项卡的“数据工具”组中单击“数据验证”右侧下拉按钮 - 在展开的下拉列表中选择“圈释无效数据”命令，即可将不符合数据验证条件的单元格数据圈释出来（本例无效数据为C5和B9）