Excel 16中自定义Subtotal函数：通过VBA实现动态SUMPRODUCT统计

一、直接使用VBA自定义函数（最简方案） #

这个函数会自动忽略筛选隐藏的行，计算指定区域可见单元格的非空唯一值数量，效果和Subtotal完全一致：

1. 按Alt + F11打开VBA编辑器
2. 右键点击当前工作簿，选择「插入→模块」
3. 粘贴以下代码：

Function SUBTOTAL_UNIQUE_COUNT(rng As Range) As Long
    Dim cell As Range
    Dim uniqueVals As New Collection
    ' 遍历可见单元格，收集唯一非空值
    On Error Resume Next ' 重复项添加时触发错误，直接跳过
    For Each cell In rng.SpecialCells(xlCellTypeVisible)
        If Trim(cell.Value) <> "" Then ' 排除空文本内容
            uniqueVals.Add cell.Value, Key:=CStr(cell.Value)
        End If
    Next cell
    On Error GoTo 0 ' 恢复默认错误处理机制
    SUBTOTAL_UNIQUE_COUNT = uniqueVals.Count
End Function

4. 返回Excel界面，在目标单元格输入公式：
   =SUBTOTAL_UNIQUE_COUNT(B2:B2340)
   筛选数据时，公式会自动更新结果，只统计可见行的非空去重值。

二、将自定义函数纳入Subtotal选项（进阶方案） #

如果要让这个统计功能出现在「数据→分类汇总」的函数下拉列表中，可通过VBA自定义Excel界面实现：

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

' 注册自定义Subtotal函数选项
Sub RegisterCustomSubtotal()
    Dim cmdBar As CommandBar
    Dim cmdCtrl As CommandBarControl
    ' 定位分类汇总对话框的函数下拉列表
    Set cmdBar = Application.CommandBars("Subtotal").FindControl(ID:=1788).CommandBar
    ' 添加自定义选项到下拉列表
    Set cmdCtrl = cmdBar.Controls.Add(Type:=msoControlButton, Before:=12)
    cmdCtrl.Caption = "非空去重计数"
    cmdCtrl.OnAction = "CustomSubtotalAction"
End Sub

' 自定义Subtotal的执行逻辑
Sub CustomSubtotalAction()
    Dim rng As Range
    Dim lastRow As Long
    ' 获取当前选中区域或工作表已用数据区域
    If TypeName(Selection) = "Range" Then
        Set rng = Selection
    Else
        Set rng = ActiveSheet.UsedRange
    End If
    lastRow = rng.Rows(rng.Rows.Count).Row
    ' 在数据下方插入分类汇总行并写入公式
    ActiveSheet.Range(lastRow + 1, rng.Column).Value = "分类汇总"
    ActiveSheet.Range(lastRow + 1, rng.Column + 1).Formula = "=SUBTOTAL_UNIQUE_COUNT(" & rng.Address & ")"
    ' 可选：设置汇总行格式
    ActiveSheet.Range(lastRow + 1, rng.Column).Resize(1, 2).Font.Bold = True
End Sub

2. 将工作簿保存为「Excel启用宏的工作簿（.xlsm）」格式
3. 打开工作簿时启用宏，运行RegisterCustomSubtotal宏，此时打开「分类汇总」对话框，函数下拉列表会新增「非空去重计数」选项。

注意：该自定义选项仅在当前工作簿生效，关闭后重新打开需再次运行注册宏；若需永久生效，可将代码放入个人宏工作簿（Personal.xlsb）中。

关键说明 #

• SUBTOTAL_UNIQUE_COUNT函数通过SpecialCells(xlCellTypeVisible)精准定位可见单元格，完全适配筛选场景，和Subtotal忽略隐藏行的逻辑一致。
• 进阶方案本质是通过VBA给内置分类汇总对话框添加自定义按钮，点击后自动插入我们的自定义函数公式，满足在单元格直接显示结果的需求。

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