如何实现Excel单元格下拉框随已选值动态排除重复选项？

前提准备 #

先把所有可选值（6、8、10、12、16、20、25）放到一个单独的工作表区域，比如Sheet2!A1:A7，并给这个区域定义名称AllValues（选中区域后，在编辑栏左侧的名称框输入即可）。

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

1. 定义动态名称

   • 打开「公式」选项卡 → 「名称管理器」→ 「新建」
     • 名称：UsedValues
     • 引用位置：=Sheet1!$A$1:$A$10（替换成你实际放下拉列表的单元格范围，比如A列1到10行）
     • 点击确定保存
   • 再新建一个名称：
     • 名称：AvailableValues
     • 引用位置：=FILTER(AllValues, ISNA(MATCH(AllValues, UsedValues, 0)))
     • 这个公式会自动过滤掉已经被选中的值，返回剩余可选值

2. 设置数据验证

   • 选中需要设置下拉的所有单元格（比如Sheet1!A1:A10）
   • 打开「数据」选项卡 → 「数据验证」
     • 允许：选择「序列」
     • 来源：输入=AvailableValues
     • 勾选「忽略空值」和「提供下拉箭头」，点击确定

这样设置后，任意单元格选中一个值，其他所有单元格的下拉列表都会自动排除该值；修改已选单元格的内容，所有下拉列表也会同步更新，确保每个值只能被选一次。

方法二：适用于旧版Excel（无动态数组函数） #

1. 添加辅助列

   • 在存放可选值的工作表（比如Sheet2）的B列，B1单元格输入公式：

     =IF(ISNA(MATCH(A1, Sheet1!$A$1:$A$10, 0)), A1, "")
     

   • 把公式下拉到B7（和可选值的行数一致），这列会自动隐藏已被选中的值，只显示剩余可选值

2. 定义动态名称

   • 打开「名称管理器」→ 「新建」：
     • 名称：AvailableValues
     • 引用位置：=OFFSET(Sheet2!$B$1, 0, 0, COUNTA(Sheet2!$B:$B), 1)
     • 这个公式会自动根据辅助列的非空单元格数量，动态生成可选值范围

3. 设置数据验证

   • 和方法一的步骤2一致，选中目标单元格后，数据验证的来源输入=AvailableValues

注意事项 #

• 确保UsedValues的范围完全覆盖所有需要设置下拉的单元格，避免出现已选值未被过滤的情况
• 旧版Excel的方法需要保证辅助列没有额外的空值干扰，否则会影响下拉列表的显示

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