Excel创建依赖作物下拉列表的品种二级联动下拉菜单

适用Office 365/2021（支持动态数组）的方案 #

1. 定义动态筛选名称 #

• 点击「公式」选项卡 → 「定义名称」
• 名称输入 DynamicVarieties，引用位置粘贴以下公式：

  =FILTER(Varieties!$B:$B, Varieties!$A:$A=Report!$D$1, "无匹配品种")
  

  该公式会自动匹配Varieties表A列与Report!D1一致的记录，提取对应B列的品种作为动态选项。

2. 设置数据验证 #

• 选中Report表中需要添加品种下拉的单元格（例如E1）
• 点击「数据」选项卡 → 「数据验证」
• 在对话框中：
  • 允许类型选择「序列」
  • 来源框输入 =DynamicVarieties
  • 勾选「提供下拉箭头」，按需启用「忽略空值」

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

由于旧版不支持动态数组，需用OFFSET+MATCH+COUNTIF组合定义动态范围：

1. 先定义名称MatchCount，引用位置：

   =COUNTIF(Varieties!$A:$A, Report!$D$1)
   

2. 再定义名称DynamicVarieties，引用位置：

   =OFFSET(Varieties!$B$1, MATCH(Report!$D$1, Varieties!$A:$A, 0)-1, 0, MatchCount, 1)
   

3. 重复「设置数据验证」步骤，来源输入=DynamicVarieties

关键注意点 #

• 建议将Varieties表转为结构化表格（「插入」→「表格」），避免空行干扰，且数据新增时范围自动扩展
• 若Report!D1未选择作物，动态数组方案会显示「无匹配品种」，旧版方案需手动处理空值逻辑

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