Excel技巧：限制下拉菜单选项为未被使用的ID

方法1：动态数组公式 + 数据验证（Excel 365/2021及以上版本） #

这是最简洁的实现方式，利用动态数组自动更新的特性：

• 找一个空白列（比如M列），在M1单元格输入公式：

  =IFERROR(FILTER(K1:K10, L1:L10=0), "无可用ID")
  

  这个公式会自动筛选出L列标记为0的对应K列ID，当L列的状态标记变更时，M列的结果会实时更新。
• 选中A列需要设置下拉菜单的单元格，点击「数据」选项卡 → 「数据验证」：
  • 允许类型选择「序列」
  • 来源框输入 =$M$1#（#是动态数组的溢出范围引用，会自动包含所有筛选出的ID）
  • 可选：勾选「忽略空值」，并在「出错警告」中设置「停止」样式，防止用户手动输入无效ID。

方法2：定义动态名称 + 数据验证（兼容旧版Excel） #

如果使用的是Excel 2019及更早版本，无法用动态数组，可以用定义名称的方式实现：

• 点击「公式」选项卡 → 「定义名称」：
  • 名称设为 AvailableIDs
  • 引用位置输入公式：

    =OFFSET($K$1,0,0,COUNTIF($L$1:$L$10,0),1)
    

    原理：COUNTIF($L$1:$L$10,0)统计未使用ID的数量，OFFSET以此为高度截取K列的对应区域，当L列标记变更时，统计结果更新，名称的引用范围自动调整。
• 选中A列目标单元格，打开「数据验证」：
  • 允许类型选「序列」
  • 来源框输入 =AvailableIDs
  • 同样可勾选「忽略空值」和设置出错警告。

关键注意事项 #

• 确保K列的ID和L列的状态标记是一一对应的（同一行）
• 如果所有ID都已使用，方法1的公式会显示「无可用ID」，方法2的下拉菜单会为空，可根据需求调整提示内容

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