想让excel下拉菜单跟着源数据自动更新，根本不用反复点开「数据验证」改来源，只要先做一列能自动溢出的候选清单，再让下拉菜单直接引用这列就行。本次演示用的是microsoft 365版excel，下面就用「品类」和「仓库」两个字段举例子，改完之后新增品类或者仓库，下拉选项会自动同步更新。
这套方法的核心公式是 =SORT(UNIQUE(FILTER(A3:A20,A3:A20"")))，数据验证的来源直接填 =$G$3# 就行。前半段公式会自动生成干净的无重复候选项，后面加的 # 是用来引用整块自动溢出的区域。
第一步
先把原始数据放在连续的单元格区域里，比如A列放「品类」，B列放「仓库」。源数据里有重复值也没关系，后面公式会自动去重；但尽量别在这堆数据里混合并单元格或者空标题，不然做数据验证的时候很容易选错范围。
第二步
找个右侧的空白区域输入去重公式，示例里我们把主下拉的候选项放在G3单元格，直接输 =SORT(UNIQUE(FILTER(A3:A20,A3:A20""))) 就行。输完不用往下拖动单元格，Excel会自动把结果溢出填充到下面的空位。
第三步
选中要放下拉菜单的单元格，比如D3。点开顶部「数据」选项卡，找到「数据验证」，允许的条件选「序列」，来源那里填 =$G$3#。千万别直接写死成 $G$3:$G$5，不然之后新增品类还要手动改范围，白忙活。
第四步
回到最开始的源数据区域，直接往A列加新品类、往B列加新仓库就行。因为候选清单是公式生成的，G3往下的溢出区域会自动变长；数据验证引用的是 $G$3#，下拉菜单自然就同步拿到新的选项了。
公式语法拆开看
这条主下拉公式里的每个部分作用都很明确：
<code>=SORT(UNIQUE(FILTER(A3:A20,A3:A20"")))</code>
| 函数或符号 | 语法 | 作用 |
|---|---|---|
FILTER |
FILTER(array, include, [if_empty]) |
从指定区域筛出符合条件的数据。示例里 A3:A20"" 表示排除空白。 |
UNIQUE |
UNIQUE(array, [by_col], [exactly_once]) |
把重复项压成唯一值，适合做下拉候选清单。 |
SORT |
SORT(array, [sort_index], [sort_order], [by_col]) |
把候选项按顺序排好，列表更容易找。 |
# |
起始单元格# |
引用动态数组的整块溢出区域，例如 $G$3# 会跟着 G3 的结果自动伸缩。 |
要是想做二级联动，比如D3选“芒果”后，E3只显示芒果对应仓库，可以在H3输入公式：
<code>=SORT(UNIQUE(FILTER(B3:B20,A3:A20=D3)))</code>
之后把E3的数据验证来源设为 =$H$3# 就行。D3选的品类变了，E3的仓库候选项也会跟着自动切换。
常见错误怎么处理
遇到 #SPILL! 报错，基本就是公式要输出结果的溢出区域被其他内容占了，把G3下方或者H3下方的单元格内容清空就好。#CALC! 报错大多是因为FILTER没找到符合条件的结果，你可以给公式补个第三参数，比如写成 =FILTER(B3:B20,A3:A20=D3,"无匹配")，但补的“无匹配”也会变成下拉候选项，正式录入表格的时候更建议你先排查源数据有没有问题。
要是下拉菜单死活不更新，先去检查数据验证的来源是不是写成了固定范围。要自动伸缩必须写 =$G$3# 或者 =$H$3#；要是写死成 =$G$3:$G$5，新增的项目肯定不会自己跑到下拉选项里。
旧版 Excel 可以怎么替代
如果你用的是WPS或者更老版本的Excel，先确认下软件支不支持 FILTER、UNIQUE 这些函数和动态数组特性。没有这些功能的话，可以用名称管理器配合 OFFSET 来做自动扩展范围：
<code>=OFFSET($A$3,0,0,COUNTA($A:$A)-2,1)</code>
| 参数 | 含义 |
|---|---|
reference |
起始单元格，这里是 $A$3。 |
rows |
向下偏移行数，这里为 0。 |
cols |
向右偏移列数，这里为 0。 |
height |
返回区域高度，COUNTA($A:$A)-2 用来扣掉标题等非数据行。 |
width |
返回区域宽度，这里只取 1 列。 |
OFFSET 是跟着工作表计算自动刷新的，要是你的表格数据量很大，很可能拖慢整个文件的运行速度。只要你用的是支持动态数组的Microsoft 365版本，优先用 FILTER + UNIQUE + # 的方案，公式逻辑更直白，也不容易把空白项带进下拉菜单里。
