数据验证：从源头拦住错误录入

表为啥总是脏的：事后修不如事前挡

做表的人都有过这样的经历：好不容易收齐 30 个人交来的表格，一打开血压就上去了。部门名称有人写 华东，有人写 华东区，还有人写 华东大区；金额栏里混着 五千、5000、5000.0 三种写法；日期栏有 10/26、2026-10-26、十月二十六号；最离谱的还有把备注打到金额列里的。等数据收上来再清洗，花的功夫是录入的十倍，而且清洗过程自己也会引入新错误。

数据验证（老版本里叫有效性）就是解决这个问题的功能：在单元格被输入之前定好规矩，不符合规矩的直接拦下来。它相当于给表格的每一列装了个门卫，只放符合条件的数据进门。设置它花不了五分钟，却能省下几小时的返工，是所有资深做表人都会提前布防的功能。

哪些情况值得设验证？凡是这列数据有明确格式或取值范围的，都值得：只能是数字且在某范围内（年龄 1 到 120）；只能从固定列表里选（部门、状态、审批结论）；必须是某固定长度的文本（工号 6 位、身份证号 18 位）；日期不能晚于今天。反过来，纯文本备注、自由填写的意见建议，就不要硬套验证，那只会让填表人痛苦不堪，平衡是关键。

一个重要的观念必须提前说破：数据验证只对手动输入有效，对复制粘贴和从外部导入的数据是无效的，门卫只看门内的人。这个限制不知道的话会误以为设了验证就万事大吉，结果导一批数据进来照样翻车。所以数据验证要配合其它手段一起用：模板里明确禁止整片粘贴（通过保护工作表）、或干脆用在线表单收集数据、或收上来后用圈释无效数据再体检一遍。单靠它防不住所有情况。

验证生效以后，单元格已经有了门卫，但旧数据怎么办？验证规则只在此之后的输入上起作用，已经躺在里面的数据不会被追溯检查。所以正确的部署顺序是：先清理一轮现有数据，再设验证规则，最后发出模板。顺序反了，规则形同虚设。这一点在做收集模板时尤其关键，等于先修路再通车。

从下拉列表开始：最直观的验证

最常用的数据验证是下拉列表。场景很典型：状态列只能填 待审核、审核中、已完成、已驳回 四个值之一，谁也别自由发挥。选中这一列，点数据选项卡里的数据验证，在允许下拉里选序列，来源框里输入这几个词，用英文逗号分隔，确定。之后这一列每个单元格右边都有个小三角，点开就是四个选项，鼠标点一下即可，再也写不错。

来源输入框里，逗号必须是英文的，中文逗号会被当成词组的一部分——待审核，审核中 会变成一个超长选项。序列太长的时候（比如全国省份、全部产品型号），更推荐把选项写在工作表某个辅助列里，一个格子一个值，然后来源框点一下去框选那个区域。这样维护起来改一处全表生效，也不用担心逗号问题。选项区域可以放在单独的工作表里隐藏起来保持界面干净。

数字范围的设置做财务表最常用。报销金额列设小数、大于或等于 0、小于 100000，就不怕有人把负数或者天文数字填进来，也能顺手拦住把 小数点打成逗号 的手误。日期列设日期、小于或等于今天（用 TODAY 函数作上界），防止有人随手填出未来的记录。这些边界值都写在验证对话框里，一眼能看清，以后改规则也方便，不必翻公式。

除了规则本身，还有两个细节值得花一分钟设置。一是勾选 提供下拉箭头（默认勾上的）让用户知道这里有列表；二是取消勾选 忽略空值——如果允许留空就保留勾选，想强制必填就取消勾选，这样空单元格也会被当作违规圈出来。

下拉列表还有一种进阶玩法：级联下拉。比如 A 列选省份、B 列只能选该省的城市。做法是给省份建一个序列，给每个省份的城市各建一列，然后 B 列的自定义验证里写 INDIRECT 函数引用 A 列选中的名字作为区域名。级联下拉听起来复杂，但一次配置能省下无数次手动核对，做地区报表、产品三级分类的人值得花半小时学下来。做完之后在出错警告里写一句 B 列选项随 A 列变化，填表的人立刻明白联动关系，减少填错填空的概率。级联做得多了，甚至能给整个公司搭出一套结构化的数据入口，那时候你会真正体会到数据验证这个名字的分量。

出错警告和输入提示：告诉填表人为什么

验证拦下数据的时候，默认会弹一个提示框。很多人只设了允许条件，没改提示内容，填表人看到默认的那句 输入值非法 一头雾水，不知道自己做错了什么，也不知道正确做法是什么。正确的做法是在数据验证对话框的出错警告标签里写清楚：标题写 金额超出范围，错误信息写 单笔报销请填写 0 到 100000 之间的金额，超大金额请拆单或联系财务。填错的人立刻明白该怎么办，减少来回沟通。

第二个实用标签是输入信息。选中单元格时会浮出一句提示，相当于表格自带的填表说明。金额列提示 请填写含税金额，单位元，保留两位小数；姓名列提示 请填写真实姓名，不要用昵称。这个提示不强制，但对减少误解非常好使，尤其表格要发给公司外的人填时，比附一段邮件说明靠谱——文档总会丢，提示就在格子上。

出错警告还有级别之分：停止 是硬拦，填错就进不去，必须改；警告 和 信息 是软提醒，填表人可以选择仍然继续。什么时候用软提醒？数据大概率异常但存在合理特例时。比如年龄超过 120 极不可能但理论上有可能，用警告级别，既提醒又不阻碍极端情况录入。三个级别的区别在弹窗的按钮：停止只有重试和取消，警告多了一个 是，信息则只有一个确定。

给合作方做表还有个建议：把验证规则和提示文字写得像一段对话。比如标题写 日期填早了，正文写 报名日期不能早于今天，请检查是否填成了去年的日期。带点人情味的提示，比冷冰冰的 输入值非法 更让人愿意配合改，收集数据的摩擦成本会低很多。

有两个工具性细节值得知道。圈释无效数据这个按钮在数据验证下拉菜单里，点一下会把所有不符合本列规则的数据用红圈圈出来——适合对已经存在的旧数据做体检，一圈下去哪些是历史遗留问题一目了然。清除验证也在同一菜单，选中区域点一下移除全部规则，改模板时常用。两个功能像验证的体检和重启，配合起来用。

自定义验证公式：应对复杂规则

内置条件不够用的时候，允许里选自定义，就能写公式做判断。公式返回 TRUE 就让进，FALSE 就拦。这套机制强大，但需要一点公式基础，可以从几个现成配方练起，熟练了再自由组合。

经典配方一：身份证号必须 18 位。自定义公式用 LEN 判断长度等于 18。选中整列后规则会以首行为基准套用到每一行，所以写第一个格子的地址即可，不用加美元符号（这里规则区域整体就是一列，公式里引用的是被验证的格子本身）。位数不对的输入当场被拦，配合出错警告提示身份证号必须为 18 位。

经典配方二：必须填写不能留空。判断公式就是单元格不等于空文本。这个规则配在关键字段上，能防止漏录关键字。绕开它的办法也简单——填个空格就行，所以严谨的写法是判断去除空格后的内容长度是否为零，用 TRIM 包一层。表格落到爱偷懒的人手里，各种技巧都见得到。

经典配方三：日期不能晚于今天。虽然日期验证内置了时间运算符，但写成自定义公式更直观：判断单元格小于等于 TODAY()，而且 TODAY 是动态的，明天打开表规则自动延后一天。这类动态规则做报名表、签到表、合同到期提醒表非常方便。

自定义验证的坑主要是引用方式。规则公式设置后，会以选中区域的第一个格子为基准套用到整个区域，如果写成绝对引用（行列都钉死），所有行都只检查第一个格子，规则就整体失效了。把握不准的时候，设完规则随便填几行试试，比推导半天可靠——让 Excel 自己告诉你对不对。

最后给一贴务实建议：常用的规则值得存成一个小规则库。把每个验证的允许类型、边界、提示语记在自己的文档里，下次新做表直接照抄，几分钟布防完毕。验证规则配置这件事 单次成本不高但重复发生，沉淀下来就是个人效率，同事来问的时候还能直接发过去。

这套防错体系可以再往大想一步：验证拦录入、条件格式盯异常、文本函数洗旧数据，三者构成 从源头到善后 的完整闭环，分别对应录入、展示、清理三个阶段。本篇讲的是第一道闸门，也是性价比最高的一道——因为它在错误发生之前出手。一份不设防的表格，最后一定是谁填得最随意谁的样子留下；一份设好验证的表格，哪怕交给完全不懂 Excel 的人，也填不坏。这笔账怎么算都划算。

相关篇章

数据验证把住了录入关，但表格里已经存在的错误数据怎么办？那就要靠文本函数做批量清洗和提取。如果验证规则里需要判断条件，IF 和多条件判断里的思路可以直接搬过来用。填错数据的根源往往在类型没分清，搭配单元格和数据类型看更全面。

常见问题

设了数据验证，为什么粘贴数据进来还是能成功？

数据验证只对手动输入触发，复制粘贴和外部导入完全绕过它，这是功能设计如此。防护办法有三个：一是在收集环节就上表单工具而不是直接发 Excel 模板；二是对收上来的表用圈释无效数据体检一遍，红圈处手动纠正；三是把关键字段的整列保护起来禁止填写，只开特定输入列。第三个做法较重，按需采用。

下拉列表选项太多，来源框写不下怎么办？

不要在来源框里堆长字符串。把选项写在一个辅助区域，每个选项占一格，来源框改为框选该区域。辅助区域可以放在单独的工作表里。如果选项会变动，维护辅助区域即可，验证自动跟着更新。注意别把辅助区域整列删了，删了之后下拉就空了，这是协作事故的常见来源。

验证规则不小心删不掉或改不了？

可能这张工作表被保护了，保护状态下数据验证的修改是被禁止的。到审阅选项卡检查撤销工作表保护是否可点，需要密码就找设密码的人。另一种情况是选错了区域：规则是跟着区域走的，选中没设过规则的格子当然改不到，可以先点数据验证看看当前选区的规则状态。

怎么把一列的验证规则复制到另一列？

两种办法。一是用选择性粘贴：复制设好规则的格子，目标处右键选择性粘贴，勾选验证。二是用格式刷，格式刷同时把格式和验证规则都刷过去。批量套用时选中整列再操作更省事，注意原有的规则会被覆盖而不是叠加，覆盖之前先确认旧规则可以丢弃。规则如果经常要复用，把它存在专属的模板工作簿里，新建表时整列复制过去，比从头配置省心得多。

圈释无效数据为什么圈出来一些我认为没问题的？

圈释的依据是本列当前设置的验证规则，觉得没问题但规则上不符，说明规则和业务实际有出入。比如规则写整数介于 1 到 100，但实际允许 0 存在，那么 0 被圈出来就是规则的问题。这时候该改的是规则而不是数据，改完规则红圈会自动消失，重新点一次圈释即可刷新。