Excel：导入带公式的多动态数组用于数据验证并避免重叠

方案1：Excel动态数组公式+自定义名称（无宏） #

适合Excel 365/2021及以上版本，纯公式实现：

• 假设你的结构：
  • 主表Sheet1：A列为每行的筛选条件，B列为需要设置数据验证的列
  • 数据源表Sheet2：A列为分类（对应Sheet1的筛选条件），B列为可选值
• 步骤：
  1. 点击「公式」选项卡 → 「定义名称」，创建名为RowDynamicList的名称，公式输入：

     =UNIQUE(FILTER(Sheet2!$B:$B, (Sheet2!$A:$A=INDIRECT("A"&ROW()))*(ISNA(MATCH(Sheet2!$B:$B, $B$1:INDIRECT("B"&ROW()-1), 0))), ""))
     

     公式逻辑：
     • 先根据当前行A列的筛选条件，从数据源过滤出对应选项
     • 再排除当前行上方已选中的B列内容，避免选项重叠
  2. 选中Sheet1的B列（或目标行范围），点击「数据验证」→ 选择「序列」，在「来源」中输入=RowDynamicList，勾选「提供下拉箭头」。

方案2：VBA自定义函数+数据验证 #

适合兼容旧版Excel，或需要更灵活逻辑的场景：

• 按Alt+F11打开VBA编辑器，插入模块，粘贴以下代码：

  Function GetFilteredUniqueList(filterVal As String, usedVals As Range) As Variant
      Dim sourceData As Variant
      Dim result() As String
      Dim i As Long, count As Long
      Dim wsSource As Worksheet
      
      Set wsSource = ThisWorkbook.Sheets("Sheet2")
      sourceData = wsSource.Range("A1:B" & wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row).Value
      count = 0
      
      ' 遍历数据源，筛选符合条件且未被使用的选项
      For i = 1 To UBound(sourceData)
          If sourceData(i, 1) = filterVal Then
              If usedVals Is Nothing Then
                  count = count + 1
                  ReDim Preserve result(1 To count)
                  result(count) = sourceData(i, 2)
              Else
                  If IsError(Application.Match(sourceData(i, 2), usedVals, 0)) Then
                      count = count + 1
                      ReDim Preserve result(1 To count)
                      result(count) = sourceData(i, 2)
                  End If
              End If
          End If
      Next i
      
      If count > 0 Then
          GetFilteredUniqueList = result
      Else
          GetFilteredUniqueList = Array("无可用选项")
      End If
  End Function
  

• 回到Excel，给Sheet1的B列设置数据验证：
  • 选择「序列」，第一行来源输入=GetFilteredUniqueList(A1, $B$1:B0)，后续行依次调整为=GetFilteredUniqueList(A2, $B$1:B1)，以此类推。

方案3：Power Query生成专属列表（适合大规模数据） #

如果数据源量大，用Power Query预处理后再引用：

1. 导入数据源到Power Query，按分类分组，生成每个分类的唯一选项列表
2. 添加自定义列，通过合并查询关联主表已选数据，排除已被选中的对应分类选项
3. 将处理后的每个分类列表加载到隐藏工作表的对应列
4. 主表每行的数据验证直接引用隐藏工作表中对应分类的已过滤列表

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