如何动态统计Excel中符合条件的溢出数组元素出现次数

方案一：纯Excel公式（无需VBA） #

利用新版Excel的BYROW函数可直接实现动态计数，完美适配唯一值列的长度变化：

假设你的唯一值列公式（放在H10）是：

=UNIQUE(FILTER(Table1[Color],Table1[Criteria]))

在相邻单元格输入以下计数公式，它会自动溢出匹配唯一值的数量：

=BYROW(H10#, LAMBDA(x, COUNT(FILTER(Table1[Cost], Table1[Criteria]*(Table1[Color]=x)))))

原理说明 #

• BYROW(H10#, LAMBDA(x, ...))：遍历动态数组H10#的每一个元素x
• 对每个x，执行COUNT(FILTER(...))统计符合条件的记录数，结果自动组合成动态数组，无需手动调整范围

方案二：修改VBA自定义函数 #

针对原VBA函数无法处理FILTER返回的变体数组问题，修改函数如下，使其兼容Range和数组两种输入类型：

Public Function DynamicCount(SearchIn As Variant, LookFor As Variant) As Variant
    Dim searchArr As Variant
    Dim lookArr As Variant
    Dim resultArr As Variant
    Dim i As Long, j As Long
    Dim count As Long
    
    ' 统一将输入转换为数组（处理Range或FILTER返回的数组）
    If TypeName(SearchIn) = "Range" Then
        searchArr = SearchIn.Value
    Else
        searchArr = SearchIn
    End If
    
    If TypeName(LookFor) = "Range" Then
        lookArr = LookFor.Value
    Else
        lookArr = LookFor
    End If
    
    ' 初始化结果数组，匹配查找值的行数
    ReDim resultArr(1 To UBound(lookArr, 1), 1 To 1)
    
    ' 逐个统计每个查找值的出现次数
    For i = 1 To UBound(lookArr, 1)
        count = 0
        For j = 1 To UBound(searchArr, 1)
            If searchArr(j, 1) = lookArr(i, 1) Then
                count = count + 1
            End If
        Next j
        resultArr(i, 1) = count
    Next i
    
    DynamicCount = resultArr
End Function

使用示例 #

输入公式后会自动溢出结果：

=DynamicCount(FILTER(Table1[Color],Table1[Criteria]),H10#)

改进点 #

• 不再依赖CountIf，改为手动遍历数组统计，避免了Range与数组输入的兼容性问题
• 自动处理Range或动态数组类型的输入参数

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