非365版Excel VBA Filter函数实现下拉验证问题咨询

功能可行性 #

该功能可在所有非365标准版Excel（含2013/2016/2019/2021批量授权版）中实现，无需依赖365独占的动态数组函数。
直接选中筛选状态的区域作为数据验证源无法生效，Excel默认会读取区域内所有单元格内容，不会自动排除筛选隐藏的行。

生效要求 #

要让下拉验证规则正常识别筛选后的数据，传入验证规则的数据源必须满足以下要求：

• 为连续无空值的一维垂直区域或一维常量数组，不能包含隐藏行内容，不能存在多层嵌套结构
• 不能返回365专属的动态数组溢出结果，非365版本不支持该类结果解析，会直接触发验证报错
• 数组/区域内所有值必须为文本、数字等基础数据类型，不能夹带错误值、对象类值

通用无VBA实现步骤 #

1. 在原始数据源旁新增1列辅助列，在辅助列首行（与数据源第一个数据行对齐）输入以下公式，下拉填充至数据源最后一行，标记行的显示状态：

   =SUBTOTAL(103, [@数据源对应列的表头名])
   

   处于筛选显示状态的行会返回1，被筛选/手动隐藏的行会返回0。
2. 按Ctrl+F3调出名称管理器，新建自定义名称（例如命名为FilteredDropList），引用位置填入以下公式：

   =OFFSET(数据源表!$A$2,0,0,COUNTIF(数据源表!$B:$B,1),1)
   

   公式中$A$2替换为数据源第一个有效值所在单元格，$B:$B替换为刚才创建的辅助列所在整列，该公式会自动定位所有可见行，生成连续的垂直数据区域。
3. 选中需要设置下拉验证的单元格区域，打开数据验证设置面板，允许类型选择「序列」，来源填入=FilteredDropList，按需勾选「提供下拉箭头」后保存即可。
4. 每次调整筛选条件后按F9触发重算，下拉列表就会自动同步当前筛选后的可见值。

下拉验证设置界面参考

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