Excel如何基于角色表实现角色-姓名二级联动动态下拉列表

前置准备 #

首先将你现有的两张表转换为Excel结构化表（选中数据区域按快捷键Ctrl+T，勾选「表包含标题」后确认）：

• 角色表名称为Roles，唯一列列名为「Role」，内容为Project Manager、Designer、Developer三类角色
• 人员信息表名称为Staff，包含「Name」「Role」两列，存储人员与对应角色的映射关系

第一步：创建静态角色下拉菜单 #

• 选中你要放置角色选择下拉的单元格（示例为A2）
• 点击顶部菜单栏「数据」→「数据验证」，允许类型选择「序列」
• 「来源」输入框填写公式=Roles[Role]，点击确定即可完成第一个静态下拉的配置

第二步：创建动态关联姓名下拉菜单 #

适用Excel 365/2021及以上版本（推荐方案） #

• 选中你要放置姓名选择下拉的单元格（示例为B2）
• 同样打开「数据验证」窗口，允许类型选择「序列」
• 「来源」输入框填写公式：
  =FILTER(Staff[Name],Staff[Role]=A2,"无匹配人员")
• 点击确定即可完成级联配置，当A2选中不同角色时，B2的下拉选项会自动筛选对应角色的人员

兼容Excel 2019及更早版本方案 #

旧版Excel无FILTER函数，可通过定义动态名称实现：

1. 按快捷键Ctrl+F3打开名称管理器，点击「新建」
2. 名称设置为FilteredNames，引用位置填写公式（注意替换为你自己的人员表实际列位置，示例中人员表Name列在A列、Role列在B列）：
   =OFFSET(Staff!$A$1,MATCH(Sheet1!A2,Staff!$B:$B,0)-1,0,COUNTIF(Staff!$B:$B,Sheet1!A2),1)
3. 回到姓名下拉单元格的「数据验证」设置，「来源」输入=FilteredNames，点击确定即可

注意事项 #

• 若需要给多行配置级联下拉，选中整列对应区域设置数据验证即可，公式中的单元格引用（如A2）不要加$锁行号，保证相对引用生效
• 若下拉出现空白/匹配错误，检查两张表的Role字段内容是否完全一致，排除多余空格、大小写差异问题

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