无需VBA：基于两个动态命名范围的Excel自动更新数据验证列表

核心思路 #

借助Excel 365/2021支持的动态数组函数，把两个动态命名范围合并成连续的单列/行引用，完全满足数据验证的规则要求，同时实现内容自动更新。

具体实现步骤 #

1. 创建合并后的动态命名范围
   打开「公式」选项卡→「定义名称」，新建名为listAll的命名范围，输入公式：

   =TOCOL(CHOOSE({1,2},list1,list2),3)
   

   • 公式说明：
     • CHOOSE({1,2},list1,list2)：将list1和list2组合成一个二维数组
     • TOCOL(...,3)：把二维数组转换为单列，同时自动忽略空值（避免列表出现无效空项）
     • 若需要去重，可嵌套UNIQUE函数：=UNIQUE(TOCOL(CHOOSE({1,2},list1,list2),3))

2. 配置数据验证
   选中需要设置数据验证的单元格/区域→「数据」选项卡→「数据验证」：

   • 允许：选择「序列」
   • 来源：输入=listAll
   • 按需勾选「提供下拉箭头」等辅助选项

方案优势 #

• 动态更新：list1或list2新增、修改内容时，listAll会自动同步更新，无需手动调整
• 合规性：TOCOL输出的是连续单列数组，完美符合数据验证「列表源必须是分隔列表，或对单行或列的引用」的规则

简化替代方案（无需去重场景） #

如果不需要对合并后的列表去重，可直接用更简洁的堆叠函数：

• 若list1、list2是列范围：=VSTACK(list1,list2)
• 若list1、list2是行范围：=HSTACK(list1,list2)

内容的提问来源于stack exchange，提问作者eli-k