无需辅助工作表实现带条件过滤的Google Sheets数据验证下拉列表

实现方案 #

适用Excel 365/2021及以上版本 #

无需额外辅助区域，直接用动态数组公式绑定数据验证即可：

• 选中O5单元格，点击顶部菜单栏「数据」→「数据验证」
• 验证条件的「允许」选项选择「序列」
• 在「来源」输入框填入公式：
  =FILTER(A2:A30,B2:B30>=10,"无符合条件选项")
• 勾选「提供下拉箭头」和「忽略空值」，点击确定即可
  该方案会自动实时过滤A列中对应B列数值≥10的选项，B列数值变动时下拉列表会自动同步更新。

适用2019及更早的旧版本Excel #

旧版本无FILTER动态数组函数，用偏移+数组组合公式实现：

• 同样打开O5的「数据验证」设置面板，「允许」选择「序列」
• 「来源」输入框填入公式：
  =OFFSET(A1,SMALL(IF(B2:B30>=10,ROW(A2:A30)-1,99999),ROW(INDIRECT("1:"&COUNTIF(B2:B30,">=10")))),)&""
• 输入完成后按下Ctrl+Shift+Enter触发数组公式生效，再勾选「提供下拉箭头」「忽略空值」，点击确定即可
  如果后续B列数值更新后下拉列表未同步刷新，按F9键强制重算工作表即可。

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