在Excel 365中基于X标记表格创建依赖下拉列表

前置操作 #

1. 把数据源转成结构化表格：选中数据区域按Ctrl+T，勾选“我的表格有标题”，确定后在「表格工具-设计」里把表格命名为FruitColorTable——这样表格会自动扩展行列，适配后续的动态变化。
2. 先搞定水果源下拉：在目标单元格（比如A2）设置数据验证，选「序列」，来源填FruitColorTable[水果]，水果列表会随表格自动更新。

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

支持动态数组的版本直接一步到位，适配动态行列：

1. 选中要加依赖下拉的单元格（比如B2），打开「数据验证」（「数据」选项卡→数据验证）。

2. 类型选「序列」，在「来源」里输入公式：

   =FILTER(FruitColorTable[#Headers], INDEX(FruitColorTable, MATCH($A2, FruitColorTable[水果], 0), COLUMN(FruitColorTable[#Headers])>1)="X")
   

   公式逻辑：

   • FruitColorTable[#Headers]：取所有列标题
   • INDEX(...)：定位到选中水果对应的行，取出除水果列外的所有单元格
   • 筛选出值为X的单元格对应的列标题，作为下拉选项

3. 确定后，选A2的水果，B2下拉就会自动显示对应颜色，表格增减行列也会同步更新选项。

方案2：旧版Excel（无动态数组） #

如果用Excel 2019及更早版本，按以下步骤来：

1. 找个空白列（比如D列）生成颜色列表：
   在D2输入数组公式（输完按Ctrl+Shift+Enter确认）：

   =INDEX(FruitColorTable[#Headers], SMALL(IF(INDEX(FruitColorTable, MATCH($A2, FruitColorTable[水果], 0), 2:COLUMNS(FruitColorTable))="X", COLUMN(FruitColorTable[#Headers])-1), ROWS($1:1)))
   

   下拉D2直到出现#NUM!，前面的就是匹配的颜色。

2. 建动态命名范围：

   • 「公式」选项卡→「名称管理器」→「新建」
   • 名称填DynamicColorList，引用位置输入：

     =Sheet1!$D$2:INDEX(Sheet1!$D:$D, MATCH("#NUM!", Sheet1!$D:$D, 0)-1)
     

     把Sheet1换成你的工作表名。

3. 设置依赖下拉：
   选中目标单元格（比如B2），数据验证选「序列」，来源填=DynamicColorList，确定即可。

注意：旧版方案中，数据源更新后需要重新选中D列的数组公式，按Ctrl+Shift+Enter刷新，但结构化表格的行列扩展仍会自动适配。

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