Excel单单元格下拉菜单设置：学校管理状态流转限制及不可重复选需求

一、单单元格下拉列表（已选字段不可重复） #

场景：多单元格共享下拉源，已选选项无法重复选择 #

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

1. 在空白工作表（如Sheet2）的A1:A[N]区域输入所有基础下拉选项。
2. 定义动态名称：
   • 点击「公式」→「定义名称」，命名为UniqueDropdown，引用位置输入：

     =FILTER(Sheet2!$A$1:$A$5,COUNTIF(Sheet1!$B$2:$B$10,Sheet2!$A$1:$A$5)=0)
     

     替换Sheet1!$B$2:$B$10为你的目标下拉区域，Sheet2!$A$1:$A$5为基础选项范围。
3. 选中目标区域，打开「数据验证」：
   • 允许类型选「序列」，来源输入=UniqueDropdown
   • 勾选「忽略空值」和「提供下拉箭头」，在「出错警告」设置停止样式，提示“该选项已被使用”。

单个单元格选后自动排除该选项（用VBA） #

如果是单个单元格，选过某选项后下拉列表自动移除该选项：

1. 右键目标工作表标签→「查看代码」，粘贴以下VBA代码：

   Private Sub Worksheet_Change(ByVal Target As Range)
       Dim targetCell As Range, sourceRange As Range
       Set targetCell = Me.Range("B2") ' 替换为你的目标单元格
       Set sourceRange = Sheet2.Range("A1:A5") ' 替换为基础选项范围
       
       If Not Intersect(Target, targetCell) Is Nothing Then
           Dim filteredArr As Variant
           filteredArr = Filter(Application.Transpose(sourceRange.Value), Target.Value, False, vbTextCompare)
           
           With targetCell.Validation
               .Delete
               If UBound(filteredArr) >= 0 Then
                   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(filteredArr, ",")
               End If
               .IgnoreBlank = True
               .InCellDropdown = True
           End With
       End If
   End Sub
   

2. 保存文件为.xlsm格式，启用宏即可生效。

二、学生状态流转限制（严格按顺序） #

针对状态序列：入围 → 注册入学 → 通过考核 → 获得认证 → 成功就业，实现仅能向后流转，禁止跳过/回退：

1. 在Sheet2的C1:C5按顺序输入状态文本。
2. 定义两个名称：
   • 名称CurrentState：引用位置=Sheet1!$C2（替换Sheet1!$C2为状态列的第一个数据单元格）
   • 名称AllowedStates：引用位置输入：

     =OFFSET(Sheet2!$C$1,MATCH(CurrentState,Sheet2!$C$1:$C$5,0)+1,0,5-MATCH(CurrentState,Sheet2!$C$1:$C$5,0),1)
     

     该公式会根据当前状态，自动列出下一个及之后的所有合法状态。
3. 选中状态列的目标区域（如Sheet1的C2:C100），打开「数据验证」：
   • 允许类型选「序列」，来源输入=AllowedStates
   • 勾选「忽略空值」和「提供下拉箭头」
   • 切换到「出错警告」，样式选「停止」，输入提示文本：“只能选择当前状态之后的流转步骤，禁止回退或跳过”
4. 补充：如果允许保留当前状态（可重复选择），将AllowedStates的公式修改为：

   =OFFSET(Sheet2!$C$1,MATCH(CurrentState,Sheet2!$C$1:$C$5,0),0,5-MATCH(CurrentState,Sheet2!$C$1:$C$5,0)+1,1)
   

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