如何基于Excel主表自动创建子集named range并去除下拉菜单空白项？

提供两种可落地的方案，按需选择：

方案1：动态公式命名区域（适用Excel 365/2021及以上版本，无需代码） #

• 第一步：选中原数据全量区域，按下Ctrl+T转换为结构化表，勾选「表包含标题」，在「表设计」选项卡中将表名修改为分类主表
• 第二步：打开「公式」选项卡→「名称管理器」，点击「新建」，为每个分类创建动态命名区域：
  • 名称栏输入分类名（比如A）
  • 引用位置输入公式：=FILTER(分类主表[Field2],分类主表[Field1]="A")
  • 确认后保存即可
• 优势：主表新增/修改/删除对应分类的数据时，命名区域会自动同步更新，下拉菜单不会出现空白项

方案2：VBA批量自动生成（适配全Excel版本，适合分类多的场景） #

直接运行以下VBA脚本即可一次性生成所有分类对应的命名区域，后续主表更新后重新运行一次即可同步所有命名范围：

Sub 批量创建分类命名区域()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim uniqueCats As Collection
    Dim cat As Variant
    Dim rng As Range
    Dim formulaStr As String
    ' 调整为你的主表所在工作表名
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' 调整为你创建的结构化表名，提前把原数据转成Ctrl+T的结构化表
    Set tbl = ws.ListObjects("分类主表")
    Set uniqueCats = New Collection
    ' 遍历Field1列获取所有唯一分类名
    On Error Resume Next
    For Each rng In tbl.ListColumns("Field1").DataBodyRange
        If rng.Value <> "" Then
            uniqueCats.Add rng.Value, CStr(rng.Value)
        End If
    Next rng
    On Error GoTo 0
    ' 删除旧的同名命名区域
    For Each nm In ThisWorkbook.Names
        For Each cat In uniqueCats
            If nm.Name = cat Then nm.Delete
        Next cat
    Next nm
    ' 为每个分类创建动态命名区域
    For Each cat In uniqueCats
        ' 处理命名区域不允许的特殊字符，可按需扩展
        safeCatName = Replace(Replace(Replace(CStr(cat), " ", "_"), "-", "_"), "/", "_")
        formulaStr = "=FILTER(分类主表[Field2],分类主表[Field1]=""" & cat & """)"
        ' 低版本Excel没有FILTER的话，把上面的formulaStr替换成下面这行：
        ' formulaStr = "=OFFSET(分类主表[Field2],MATCH(""" & cat & """,分类主表[Field1],0)-1,0,COUNTIF(分类主表[Field1],""" & cat & """),1)"
        ThisWorkbook.Names.Add Name:=safeCatName, RefersTo:=formulaStr
    Next cat
    MsgBox "共生成" & uniqueCats.Count & "个命名区域"
End Sub

使用方法： #

• 按下Alt+F11打开VBA编辑器，右键点击左侧工程列表中的你的工作簿→「插入」→「模块」
• 将上述代码粘贴到模块窗口中，修改代码里的工作表名、表名匹配你的实际设置
• 按下F5运行即可

数据验证调用方法 #

选中需要设置下拉的单元格，打开「数据」选项卡→「数据验证」，允许类型选择「序列」，来源输入=+对应的分类命名区域名即可，比如要调用A分类的选项就输入=A

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