如何设置Excel Data Validation列表排除已使用的选项？

方法一：适用于Excel 365/2021及以上版本（支持FILTER函数） #

1. 定义动态名称

   • 点击「公式」选项卡 → 「名称管理器」→ 「新建」
   • 名称设为UnusedItems（可自定义）
   • 「引用位置」输入公式：

     =FILTER($A:$A,ISNA(MATCH($A:$A,$B:$B,0)),"")
     

     公式逻辑：用MATCH匹配A列值在B列的位置，ISNA筛选出未匹配到的（即未被选中的），最后用FILTER提取这些值。
   • 点击「确定」保存名称

2. 设置数据验证

   • 选中B列需要添加下拉列表的单元格范围（比如B1:B10）
   • 点击「数据」选项卡 → 「数据验证」
   • 在弹窗中，「允许」选择「序列」，「来源」输入=UnusedItems
   • 勾选「提供下拉箭头」，点击「确定」

方法二：适用于旧版Excel（不支持FILTER函数） #

1. 定义动态名称

   • 同样打开「名称管理器」新建名称UnusedItems
   • 「引用位置」输入数组公式：

     =INDEX($A:$A,SMALL(IF(COUNTIF($B:$B,$A:$A)=0,ROW($A:$A)),ROW(INDIRECT("1:"&COUNTA($A:$A)-COUNTIF($B:$B,"<>""")))))
     

     输入完成后按Ctrl+Shift+Enter确认（旧版数组公式必须用这个组合键）
   • 保存名称

2. 设置数据验证

   • 步骤同方法一，来源输入=UnusedItems即可

注意事项 #

• 确保A列数据无重复值，否则下拉列表可能出现重复的未选项
• B列已选值删除后，对应选项会自动回到下拉列表中

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