为什么要用数据验证
在Excel表格中多人协作录入数据时最常遇到的问题是数据不统一不规范。同一列中有人填待处理有人填未处理还有人填处理中，后续做数据分析时这些不规范的名称会导致筛选不准确和统计结果有偏差。数据验证功能可以设定单元格允许输入的数据范围或列表选项，当录入人试图输入不符合规则的内容时自动弹出错误提示阻止输入。通过数据验证加下拉列表的方式可以确保所有数据按照预定义的规范录入，大幅提高数据的质量和后续分析的准确性。
创建下拉列表
选中需要设置下拉列表的单元格区域点击数据选项卡数据验证数据验证。在设置选项卡中允许选择序列在来源输入框中输入所有可选项用英文逗号隔开如待处理,进行中,已完成,暂停,已取消。点击确定后单元格右侧出现下拉箭头点击可以选择预设选项。如果可选项较多可以在工作表的一个区域中列出所有选项然后在来源框中用框选的方式引用该区域的数据来源。这种方法便于后续新增或修改选项不需要逐个修改单元格的数据验证设置。
限制数值范围
选中要限制数值的单元格数据验证允许选整数或小数数据选介于输入最小值和最大值。例如限制录入年龄在18到65岁之间或限制百分比在0到100之间。当录入人输入超出范围的值时会自动拒绝。还可以设置整列的数值符合特定条件如大于某单元格的值等。这个功能在填写问卷数据或制作员工信息表时特别有用可以从源头上防止数据异常的录入。
限制文本长度
数据验证允许选文本长度数据选等于输入精确的字符数如18位的身份证号或11位的手机号码。也可以设置介于某范围如密码长度在8到16位之间。这种限制在录入关键信息字段时非常实用，可以有效防止录入时少输入或多输入字符导致数据格式出错。特别是处理财务数据和身份信息时长度限制是保证数据格式一致性的关键手段。
自定义公式验证
在数据验证允许中选择自定义然后输入公式来实现更复杂的验证规则。例如要保证B列的日期始终大于A列的日期可以输入=B1>A1。要禁止重复录入同一个订单号可以用公式=COUNTIF(A$1:A$100,A1)=1。要限制录入内容以指定字符开头可以用公式=LEFT(A1,2)="编号"。自定义公式使数据验证的功能大大扩展可以满足各种复杂的数据校验场景。
输入提示和错误警告
数据验证对话框中的输入信息选项卡可以设置在选中单元格时显示提示文字帮助录入人理解需要输入什么内容。出错警告选项卡可以设置在输入非法数据时的提示样式：停止会强制阻止输入警告允许选择视为无效继续但建议修改信息只是提示。合理设置提示信息可以减少录入人的疑惑降低无效输入的频率。建议设置详细的提示内容如请输入正确的日期格式YYYY-MM-DD。
圈释无效数据
如果表格中已经存在不符合数据验证规则的无效数据Excel可以将其标记出来。点击数据验证圈释无效数据所有不符合规则的单元格会被红色的椭圆圈出方便定位和修改。修改完合规后点击清除验证标识圈即可去掉红色标记。这个功能对已有的老数据进行合规性检查非常高效可以快速发现并修正历史数据中的错误。
使用数据验证时还有一些进阶技巧值得掌握。如果需要在多个工作表或不同区域使用相同的数据验证设置可以先在一个区域设置好然后使用格式刷复制到其他区域。当数据验证的选项比较多时可以将选项列表放在一个单独的工作表中并使用名称管理器为列表区域定义名称，然后在数据验证来源框中直接引用该名称方便管理。对于需要逐行设置不同验证规则的表格建议先设置好规则再锁定工作表防止他人修改验证设置。数据验证和条件格式可以配合使用：数据验证控制输入内容条件格式根据输入的内容自动改变颜色实现输入即格式化的效果。