Excel小技巧:「数据验证」 + 三级联动下拉,从根上杜绝乱填

你给公司各部门发下去一张费用报销单，"部门"那一列收回来看了一眼，差点没背过气去：

销售部、销售一部、销售 1 部、Sales、营销部、销售部（华东）……

同一个部门，六种写法。等你要做汇总透视表的时候，这六个就成了六行独立数据，合计全错。

你只能一张一张改，改完还得祈祷下个月别再有人乱填。

问题的根源不在同事不认真，在你的表没有"边界"。今天这期教你用 Excel 自带的「数据验证」功能，把别人能填什么、不能填什么，从源头框死。

进阶部分教你做联动下拉菜单：选了"华东"，城市列就只出现华东的城市。

五分钟做一个基础下拉菜单

第一步：选中要控制的单元格区域。 比如 C2:C200（部门列）。

第二步：打开数据验证。 数据 → 数据验证（老版本叫"数据有效性"）。

第三步：设置"允许"为"序列"。

弹出的对话框里，“允许"下拉框选"序列”（序列就是列表的意思），这时候下面会出现"来源"输入框。

来源有三种写法，按需选：

写法一：直接手打。 在来源框里输入：

销售部,市场部,财务部,人事部,技术部

注意用英文逗号分隔，中文逗号会被当成一个整体。部门少于十几个、不常变动时，这是最快的方式。

写法二：引用一个区域。 点来源框右边的小箭头，去别的 Sheet 选中部门列表区域（比如 $I$2:$I$8）。列表项目多、可能变动的场景用这种。Excel 2013 及以后版本支持直接跨 Sheet 引用，不用再绕道命名区域。

写法三：引用名称。 来源写 $部门列表（前面带个 $）。好处是：列表区域转成"表格对象"后（前期学的 Ctrl+T），新增部门会自动进列表，下拉菜单不用重设。这是项目多又常变时的最优解。

第四步：确认"出错警告"的样式。 这一步最多人忽略，但它决定你的防错到底有没有效。

对话框第三个选项卡"出错警告"，样式有三个：

• 停止
   — 别人填了不在列表里的值，Excel 直接拒绝，输入无效，光标退回
• 警告
   — 弹窗警告，但对方点"是"就能强行填进去
• 信息
   — 只是提示一下，填什么都能过

想真正防住乱填，必须选"停止"。 选了警告或信息，等于门上挂了个"请勿入内"的牌子但没锁。

“标题"和"错误信息"两栏自己写句人话，比如"请从下拉列表选择部门名称”——同事看到这种提示，比你事后追着问"你这个销售1部到底是哪个"要省心得多。

进阶：三级联动下拉菜单

基础下拉只能解决"一列"的问题。但现实里字段之间有依赖关系：先选大区，再选省份，再选城市。选了华东却出现"深圳"，那这张表照样废。

实现原理一句话：把每一组的候选名单存成一个"名称"，然后用 INDIRECT 函数按上一级选中的文字去找对应名称。

看不懂没关系，跟着做一遍就通了。

第一步：建候选名单

在工作表空白区域（或者单独开一个 Sheet 叫"基础数据"），按列排好：

第一行是大区名，下面每列是这个大区包含的省份。

关键点：第一行的文字必须和你在下拉菜单里选的大区完全一致。

第二步：批量创建名称（别一个个手动建）

选中整个 G1:J5 区域，然后：公式 → 根据所选内容创建 → 只勾选"首行" → 确定。

Excel 会自动创建四个名称：名称"华东"指向 $G$2:$G$5，名称"华北"指向 $H$2:$H$5，以此类推。

这一步是批量操作，四个名称一秒建好。如果只有 20 个大区，你也不用点 20 次"名称管理器"。

第三步：设置第一级（大区）

选中大区列区域（比如 A2:A200），数据 → 数据验证 → 序列，来源写：

=$G$1:$J$1

或者手打 华东,华北,华南,西南 都行。

第四步：设置第二级（省份）

选中省列区域（B2:B200），数据 → 数据验证 → 序列，来源写：

=INDIRECT(A2)

INDIRECT 的作用是"把文字当引用地址用"。A2 里填了"华东"，INDIRECT(A2) 就等于名称"华东"所指向的区域 $G$2:$G$5，下拉列表就出现了上海、江苏、浙江、安徽。

注意这里写的是 A2（不带 $），不是 $A$2。 因为你希望这一行看自己左边的单元格：B2 看 A2，B3 看 A3。如果写成 $A$2，那所有行的省份下拉都是同一个大区的，第 3 行选了华北，点开省份却全是华东的省。

设完之后，把 B2 的验证格式往下刷（复制 B2 → 选中 B3:B200 → 选择性粘贴 → 验证）。

第五步：第三级（城市）

同样的套路，只是要把候选名单做得更细：为每一个省建一个名称，里面装这个省的市。

比如名称"江苏"指向 $K$2:$K$5（南京、苏州、无锡、常州），名称"广东"指向 $L$2:$L$6……

然后 C2:C200 的数据验证来源写：

=INDIRECT(B2)

三级联动就成了。

但这里有个现实问题必须提醒你： 全国 34 个省级行政区、近 300 个地级市，用这套"命名区域"方案意味着你要建 300 多个名称，维护成本很高。

所以：两级联动（大区→省、部门→子部门、项目→阶段）用命名区域最顺手；三级以上，或者候选名单很长，建议换成下面这个方案。

更省心的方案：长表 + FILTER（365 专属）

把候选关系存成一张"三列长表"，而不是一堆列：

维护起来比命名区域简单太多——新增一条关系，加一行就行，不用建名称。

然后在一个辅助单元格（比如 M2）里写：

=UNIQUE(FILTER($J$2:$J$2000, $I$2:$I$2000=$A$2))

它会把符合当前所选大区的所有省份筛出来去重，自动溢出成一列。

数据验证的来源框里引用这个溢出区域，用 365 的动态引用符号 #：

=$M$2#

下拉列表就只包含当前大区对应的省份了。

这套方案适合"单条录入表单"（一张表填一条记录，比如报销单、信息登记表）。 如果是"500 行每行独立选"的批量录入表，还是命名区域 + INDIRECT 更稳，因为 FILTER 只能有一处辅助结果，没法给 500 行各算一份。

一个烦人的遗留问题：改了大区，省份没清空

这是联动下拉最反直觉的地方：A2 从"华东"改成"华南"之后，B2 里原本填的"江苏"还留在那儿。

数据验证只管"输入的时候合不合法"，不管"上游变了要重算下游"。所以你手里会出现"华南-江苏"这种脏数据。

三种应对方式，按成本从低到高：

方式一：靠提示。 在出错警告信息里写一句"更换大区后请重新选择省份"，再给 B 列配一个条件格式规则（前期学过）检查"省份是否属于当前大区"，不属于就标红。人工看到红色自己改。

方式二：用公式辅助检查。 在 E2 加一列校验：

=IF(OR(A2="",B2=""),"", IF(ISNUMBER(MATCH(B2, INDIRECT(A2), 0)), "√", "省份与大区不匹配"))

MATCH 去当前大区的省份名单里找 B2，找不到就报不匹配。这样每次收表先看 E 列，一秒定位脏数据。

数据验证的其他四个高价值用法

下拉菜单只是"序列"这一种。把"允许"换成别的，用途一下就打开了：

用法一：限制只能填日期，而且不能是过去。 允许选"日期"，数据选"大于或等于"，值写 =TODAY()。填计划完成时间、报销日期时特别好用。

用法二：限制必填（防重复填写）。 允许选"自定义"，公式写：

=COUNTIF($A$2:A2, A2)=1

意思是"从区域第一个格数到当前格，这个值只能出现一次"。往下刷之后，谁填了重复的工号、重复的手机号，Excel 直接拒绝。

用法三：限制文本长度。 允许选"文本长度"→"小于或等于"→ 20。防止有人在一个单元格里写小作文。

用法四：数值区间防呆。 允许选"小数"→"介于"→ 0 到 100。填百分比、评分、完成度时，别人输 150 就会被拦下。

五个容易踩的坑

第一，"序列"来源里必须用英文逗号。 手打列表时用了中文全角逗号"，"，Excel 会把整串当成一个选项，下拉框里出现一条超长又奇怪的内容。

第二，名称必须和父单元格内容完全对应。 INDIRECT(A2) 能不能出下拉，取决于 A2 里的文字能不能变成一个合法的名称。如果大区叫"华东区 “（尾部有空格），或者名字里有连字符"销售-一部”，或者以数字开头"1部"，名称就建不出来或匹配不上，下拉箭头会直接消失。遇到这种带特殊字符的项目，用 SUBSTITUTE 先清洗一层再交给 INDIRECT。

第三，INDIRECT 是易失性函数。 第012期讲 TODAY 时提过，易失性函数每次有任何操作都要重算。200 行 × 2 列都用 INDIRECT，大表会变得明显卡顿。这是命名区域方案的固有代价，数据量特别大时要掂量。

第四，出错警告的样式别选错。 选了"警告"或"信息"，对方点一下就能强行填进去，你的验证等于白设。想真锁死必须选"停止"。

第五，下拉箭头不见了先查父级。 如果子级下拉突然消失，九成是父级那一格空着、或者填的内容没有对应名称。父级为空时，INDIRECT(“”) 会返回错误，Excel 会弹一句"目前对此项的源求值结果为一个错误，是否继续?"——这时候点"是"也不会出下拉，正确做法是先填对父级。

今天的总结

防住乱填，两步走：

第一步，数据验证 → 序列 → 来源引用列表区域 → 出错警告改成"停止"。 这是基础门槛，五分钟能做完一列。

第二步，有依赖关系的字段做联动。 两级联动用"命名区域 + INDIRECT(上一级单元格)“，批量建名称用"公式 → 根据所选内容创建 → 首行”；三级以上或名单很长的，改成 365 的 FILTER + UNIQUE + # 溢出引用。

工具只是手段，真正值钱的是这个思路：一张表应该由设计者定义"什么是合法的"，而不是靠每个填写人的自觉。

把下拉菜单做上，你就再也不用在月底花两小时统一"销售1部"和"销售一部"了。

——E晓分享，共同进步——