Excel数据验证下拉列表用UNIQUE+FILTER公式报错，求非间接引用解法

问题本质 #

Excel的数据验证功能不支持直接识别动态数组公式（比如UNIQUE+FILTER）返回的溢出结果——哪怕你把公式定义成名称，它依然会判定为错误，因为数据验证要求源是可直接引用的连续单元格区域或者结构化的文本序列数组，而动态数组的溢出结果在名称中是以“内存数组”形式存在，不符合数据验证的源格式要求。

无需间接引用单元格的可行解法 #

以下几种方法都不需要把公式结果放到单元格里再引用，直接修改名称管理器里的公式即可：

方法1：用LET+INDEX构建可识别的垂直数组（推荐，适配所有无特殊字符的场景）

修改名称formula_XXX的公式为：

=LET(filtered,UNIQUE(FILTER(T_raw_data[A],T_raw_data[B]="XXX")),INDEX(filtered,SEQUENCE(ROWS(filtered))))

原理：先用LET把过滤后的唯一值存为临时变量filtered，再用INDEX+SEQUENCE把内存数组转换成数据验证能识别的垂直序列数组，完美适配动态结果的长度变化。

方法2：用TEXTJOIN+TEXTSPLIT转成文本序列（适合内容无分隔符的场景）

如果你的A列内容里不包含逗号（可换成其他不冲突的分隔符），可以用这个方法：

=TEXTSPLIT(TEXTJOIN(",",TRUE,UNIQUE(FILTER(T_raw_data[A],T_raw_data[B]="XXX"))),",")

原理：先把过滤结果用TEXTJOIN拼接成逗号分隔的文本，再用TEXTSPLIT拆分成数组，数据验证可以识别这个拆分后的数组。

方法3：用OFFSET+COUNTA动态定位区域（需确保结果无空值）

如果你的过滤结果里不会出现空值，可以用OFFSET动态生成引用区域：

=OFFSET(T_raw_data[[#Headers],[A]],1,0,COUNTA(UNIQUE(FILTER(T_raw_data[A],T_raw_data[B]="XXX"))),1)

原理：用COUNTA获取过滤结果的行数，再用OFFSET从A列第一行数据开始，生成对应行数的连续区域引用。

注意事项 #

• 以上方法均需要Excel 365/2021及以上版本，因为用到了LET、SEQUENCE、TEXTSPLIT等新函数。
• 如果使用方法2，一定要确保分隔符（比如逗号）不会出现在A列的内容里，否则会导致下拉选项拆分错误。

内容的提问来源于stack exchange，提问作者AlexisPa