如何生成当"In Scope"为"Yes"时"Task"列的Excel数据验证列表？

嘿，我完全懂你的需求——你想从表格里筛选出所有"In Scope"列值为"Yes"的"Task"项，做成数据验证的下拉列表，但之前用INDEX函数只能拿到单个匹配值对吧？这是因为INDEX默认只返回指定位置的单个结果，要生成完整的动态列表，得用不同的组合方法，下面分两种Excel版本给你解决方案：

方法1：适用于Excel 365/2021（支持动态数组） #

这是最简单的方案，直接用FILTER函数就能一键生成动态列表：

1. 假设你的表格是结构化表格（插入→表格），名称为Table1，其中"Task"列是Table1[Task]，"In Scope"列是Table1[In Scope]
2. 打开数据验证对话框（数据→数据验证），在「允许」下拉选「序列」
3. 在「来源」框中输入以下公式：

=FILTER(Table1[Task], Table1[In Scope]="Yes")

4. 确定后，下拉列表就会自动包含所有"In Scope=Yes"的Task，而且当表格里的"In Scope"状态变化时，列表会自动更新，完全不用手动调整！

方法2：适用于旧版Excel（不支持动态数组） #

旧版Excel没有动态数组函数，我们需要通过定义名称+数组公式来实现：

1. 点击「公式→定义名称」，在弹出的对话框中：
   • 名称：输入ValidTasks（自定义名称，方便识别）
   • 引用位置：输入以下数组公式（输入完成后按Ctrl+Shift+Enter确认，不要直接按回车）：

   =INDEX(Table1[Task], SMALL(IF(Table1[In Scope]="Yes", ROW(Table1[Task])-ROW(Table1[#Headers])), ROW(INDIRECT("1:"&COUNTIF(Table1[In Scope],"Yes")))))
   

2. 打开数据验证对话框，「允许」选「序列」，「来源」框中输入=ValidTasks
3. 确定后就能得到符合条件的Task下拉列表了

关键原理说明 #

• 对于方法1：FILTER函数会自动筛选出符合Table1[In Scope]="Yes"的所有Task值，动态数组特性会让结果自动溢出到下方单元格，数据验证能直接识别这个溢出区域
• 对于方法2：IF函数先标记所有"In Scope=Yes"的行位置，SMALL按顺序提取这些行号，INDEX对应取出Task值，COUNTIF统计符合条件的总数量，确保列表包含所有匹配项

内容的提问来源于stack exchange，提问作者Kiran Ramchandra Parab