如何在Excel中为可变大小列表创建自适应依赖下拉菜单

一、现有工作表结构 #

我有5个独立的Excel工作表，结构如下：

1. 工作表1（健康食物录入表） #

支持按类别录入健康食物，格式：

2. 工作表2（不健康食物录入表） #

与工作表1格式完全相同，用于录入不健康食物。

3. 工作表3（健康食物整理辅助表，不可编辑） #

通过函数自动生成，将工作表1的类别与对应食物整理为横向结构，示例：

4. 工作表4（不健康食物整理辅助表，不可编辑） #

与工作表3格式完全相同，自动整理工作表2的内容。

5. 工作表5（采购日志表） #

记录采购信息，格式：

二、需求说明 #

已完成「分类」列的下拉菜单，需要实现以下联动逻辑：

• 当「分类」选择「健康」时，「食物类别」下拉自动加载工作表1的所有食物类别；选择「不健康」时加载工作表2的食物类别
• 选择具体「食物类别」后，「食物」下拉自动加载对应类别的食物
• 所有下拉菜单需随工作表1、2的内容更新自动适配

三、实现步骤 #

1. 定义动态名称抓取食物类别 #

打开「公式」选项卡 → 点击「名称管理器」，创建两个动态名称：

• 名称：健康食物类别，引用位置：

  =OFFSET(工作表1!$A$1,0,0,COUNTA(工作表1!$A:$A),1)
  

  这个公式会自动统计工作表1中A列的非空单元格数量，实现类别列表的动态更新。
• 名称：不健康食物类别，引用位置：

  =OFFSET(工作表2!$A$1,0,0,COUNTA(工作表2!$A:$A),1)
  

2. 设置「食物类别」列的依赖下拉 #

选中工作表5中「食物类别」列的目标单元格区域（如D2:D1000）：

1. 打开「数据」选项卡 → 点击「数据验证」
2. 在「允许」下拉菜单中选择「序列」
3. 在「来源」框中输入公式：

   =IF(C2="健康",健康食物类别,IF(C2="不健康",不健康食物类别,""))
   

4. 勾选「忽略空值」和「提供下拉箭头」，点击「确定」

3. 定义动态名称抓取对应食物列表 #

回到「名称管理器」，创建名称对应食物列表，引用位置：

=IF(工作表5!$C2="健康",OFFSET(INDEX(工作表3!$A:$X,MATCH(工作表5!$D2,工作表3!$A:$A,0)),0,1,1,COUNTA(INDEX(工作表3!$A:$X,MATCH(工作表5!$D2,工作表3!$A:$A,0),0))-1),IF(工作表5!$C2="不健康",OFFSET(INDEX(工作表4!$A:$X,MATCH(工作表5!$D2,工作表4!$A:$A,0)),0,1,1,COUNTA(INDEX(工作表4!$A:$X,MATCH(工作表5!$D2,工作表4!$A:$A,0),0))-1),""))

该公式会根据「分类」和「食物类别」的选择，从对应辅助表中抓取横向排列的食物项，自动适配内容更新。

4. 设置「食物」列的依赖下拉 #

选中工作表5中「食物」列的目标单元格区域（如E2:E1000）：

1. 打开「数据」选项卡 → 点击「数据验证」
2. 在「允许」下拉菜单中选择「序列」
3. 在「来源」框中输入：

   =对应食物列表
   

4. 勾选「忽略空值」和「提供下拉箭头」，点击「确定」

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