Excel创建合并双列表的Define Name并用于数据验证的问题

问题核心 #

你用公式合并两个列表后，Excel将结果识别为单个值而非一维数组，这是因为普通文本合并（比如&连接）返回的是字符串，不符合数据验证要求的「单行/列引用或数组序列」规则。

可行解决方案 #

方案1：动态数组公式（Excel 365/2021及以上） #

直接用TOCOL函数将两个列表合并为一维数组，定义名称时输入公式：

=TOCOL(Sheet1!A1:A5,Sheet2!B1:B3,TRUE)

• 把Sheet1!A1:A5和Sheet2!B1:B3替换成你的目标列表区域
• TRUE参数自动忽略空值，不需要可改为FALSE

定义完成后，数据验证的「序列」源直接输入=你的名称即可，Excel会自动识别为合法数组序列。

方案2：兼容旧版Excel的数组公式 #

如果用Excel 2019及更早版本，用以下数组公式生成合并序列（输入后按Ctrl+Shift+Enter确认）：

=IF(ROW(INDIRECT("1:"&COUNTA(A1:A5)+COUNTA(B1:B3)))<=COUNTA(A1:A5),INDEX(A1:A5,ROW(INDIRECT("1:"&COUNTA(A1:A5)+COUNTA(B1:B3)))),INDEX(B1:B3,ROW(INDIRECT("1:"&COUNTA(A1:A5)+COUNTA(B1:B3)))-COUNTA(A1:A5)))

公式会按顺序拼接两个非空列表的内容，生成连续一维数组，数据验证可直接调用。

方案3：辅助列拼接（最直观） #

不想用复杂公式的话，直接插入辅助列：

• 第一行输入=A1，下拉到第一个列表末尾
• 下一行输入=B1，下拉到第二个列表末尾
• 选中辅助列非空区域，定义名称
• 数据验证源直接引用该名称

关键注意事项 #

• 数据验证要求的序列必须是连续单行/列引用，或返回一维数组的动态公式，不能是文本连接生成的长字符串
• 定义名称前，可在工作表单元格测试公式，确认返回的是多行/列序列而非单个值

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