如何制作仅显示对应食材可选产品的Excel动态下拉菜单？

前提假设 #

• 工作表1：食谱，结构示例：

  食谱名称	食材	选择对应产品
  戚风蛋糕	面粉	（下拉菜单）
  戚风蛋糕	鸡蛋	（下拉菜单）

• 工作表2：食材产品，结构示例（同一食材对应多行产品）：

  食材名称	供应商	产品名称
  面粉	甲供应商	高筋面粉
  面粉	乙供应商	低筋面粉
  鸡蛋	甲供应商	土鸡蛋
  鸡蛋	丙供应商	普通鸡蛋

方法1：Excel 365/2021（推荐，用动态数组函数） #

步骤1：给食谱表的「食材」列加基础下拉 #

• 选中食谱表的B列（食材列，从B2开始），点击「数据」选项卡 →「数据验证」→ 选择「序列」，来源输入=UNIQUE(食材产品!$A$2:$A$100)（自动提取食材产品表中所有不重复的食材名称）。

步骤2：给「选择对应产品」列加依赖下拉 #

• 选中食谱表的C2单元格，点击「数据验证」→「序列」，来源输入公式：

  =FILTER(食材产品!$C$2:$C$100,食材产品!$A$2:$A$100=食谱!$B2)
  

• 选中C2单元格，鼠标移到单元格右下角，双击填充柄，把公式应用到整列。这样每一行的下拉菜单只会显示对应食材的可用产品。

方法2：兼容旧版Excel（用INDEX+SMALL+IF数组公式） #

步骤1：定义动态名称 #

1. 按Ctrl+F3打开名称管理器，新建名称食材列表，引用位置：

   =OFFSET(食材产品!$A$2,0,0,COUNTA(食材产品!$A:$A)-1,1)
   

   （自动获取食材产品表中所有非空的食材名称，数据更新时会自动同步）

2. 新建名称对应产品，引用位置：

   =INDEX(食材产品!$C:$C,SMALL(IF(食材产品!$A:$A=食谱!$B2,ROW(食材产品!$A:$A)-1,""),ROW(INDIRECT("1:"&COUNTIF(食材产品!$A:$A,食谱!$B2)))))
   

   输入完成后按Ctrl+Shift+Enter确认（旧版Excel数组公式需此组合键触发）。

步骤2：设置数据验证 #

• 给食谱表的B列（食材列）设置数据验证，来源选择=食材列表。
• 选中食谱表的C2单元格，设置数据验证→序列，来源输入=对应产品，再将设置填充到整列即可。

注意事项 #

• 若食材产品表是「同一食材的产品在同一行多列」的结构（比如A列食材，B、C列是不同供应商产品），可调整产品下拉的公式为：

  =OFFSET(食材产品!$B$1,MATCH(食谱!$B2,食材产品!$A:$A,0),0,1,COUNTA(OFFSET(食材产品!$B$1,MATCH(食谱!$B2,食材产品!$A:$A,0),0,1,100)))
  

• 确保两个表中的食材名称完全匹配（大小写、空格都要一致），否则会出现匹配失败的情况。

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