下拉菜单本身很简单:数据→数据验证→序列。但选了“山东”之后,第二列还给出全国的市,就露馅了。
二级联动要解决的是“第二个下拉的源随第一个变”。目前有两种成熟做法,各有取舍。
一、先把源数据摆好
联动的前提是数据分两列:A 列省份、B 列城市,同一省份的城市要挨在一起(或者干脆一行一个省份、下面列城市)。
如果数据是一行一个省、横向排城市,那就要用“转置”后的结构,否则 INDIRECT 找不到对应区域。
二、做法一:名称管理器 + INDIRECT
第一步,选中某个省对应的城市区域(比如 C2:C8),公式→定义名称,名称写“山东”(必须和下拉里出现的文字完全一致)。
第二步,一级下拉用数据验证序列,源写成省份列表;二级下拉的源写 =INDIRECT(A2),A2 是一级单元格。
这样选“山东”时,INDIRECT(“山东”) 会解析成那个名称对应的区域,下拉里就只剩山东的城市。
- 选中城市区域→公式→定义名称→输入省份名;
- 每个省重复一次,名称不能重名;
- 二级单元格→数据验证→序列→=INDIRECT(上一级单元格);
- 勾选“出错警告”,防止手输错值。

三、INDIRECT 的三个硬限制
名称不能带空格、括号、斜杠,像“黑龙江(黑)”这种就直接报错。解决办法是先做一张对照表,用规范名称,显示时再换。
省份改了名字要同步改名称管理器,否则 #REF!。
INDIRECT 是易失函数,表大时(几千个联动单元格)会明显拖慢重算。
四、做法二:表格 + 结构化引用
把源数据选中按 Ctrl+T 转成表格,命名为“省市表”。一级用数据验证序列引用表格列,二级用公式取对应块:=OFFSET(省市表[[#标题],[城市]],MATCH(G2,省市表[省份],0)-1,0,COUNTIF(省市表[省份],G2),1)。
好处是新增省份只管往表里加行,不用建名称,公式自动跟着走。代价是公式长,且要求同省数据连续。
五、常见报错对照
按现象查原因,比反复重建下拉快。
| 现象 | 原因 | 处理 |
|---|---|---|
| 二级下拉空白 | 名称不存在或拼写不一致 | 核对名称管理器里的名字 |
| 提示 #REF! | 名称被删或改了 | 重建名称并改公式 |
| 下拉里出现重复项 | 源区域有重复值 | 用 UNIQUE 生成唯一列表 |
| 新增省份后没联动 | 名称未新增 | 补建名称或用方案二 |
六、什么时候别用下拉
数据超过几百行、还要求模糊搜索的,数据验证的序列下拉体验很差(不能搜、只能滚)。这种情况更适合用“表格 + 筛选”或者直接做一张查询页。
另外,下拉只防输入错误,不防粘贴。粘贴进来的脏数据照样会破坏联动,重要表格建议保护工作表,只放开输入单元格。
常见问题(FAQ)
INDIRECT 引用的名称能不能跨工作表?
可以。名称是工作簿级的,任何工作表都能用;但引用的区域要保持固定,插入删除行后要检查。
一级下拉改了,二级不跟着变怎么办?
二级公式里的引用要指向一级单元格本身,而不是某个固定值。检查 =INDIRECT(A2) 里的 A2 是不是相对引用。
数据验证的下拉能不能显示提示文字?
能。数据验证对话框的“输入信息”页签里填标题和提示,选中单元格就会浮出说明。
写在最后
省份少、结构稳定,用名称管理器 + INDIRECT 最快;结构常变、要加数据,用表格 + 结构化引用更省心。
两种做法都要求源数据规整,源表乱了,公式再对也联动不出来。


