计算表中动态（溢出）数组元素的出现次数

问：

我正在使用新的动态结果函数，如 unique、filter、sort、A6# 等。我喜欢它们的大小适合他们返回的物品数量。

我在 Excel 表格中有一个大型数据集（数百列和数千行）。我已使用 unique/filter 函数将满足条件的一列的唯一值提取到动态数组中，没有问题。

现在，我想以动态方式计算每个返回值的出现次数，以便返回值的范围与唯一函数动态返回的唯一值的数量成比例。我可以在过滤函数周围使用计数函数一次计算一个值，但结果不是动态的，需要我手动将函数复制/删除到正确数量的单元格中。

总之，我正在寻找一种方法来计算表列中满足条件且等于动态数组中所有值的项目。结果维度应与动态数组的维度一致。

我从表格中的一些示例数据开始：

（颜色类型成本标准 红色 1 1 TRUE 蓝色 2 2 FALSE 红色 1 3 TRUE 黄色 1 2 TRUE 橙色 1 3 FALSE 红色 2 1 TRUE 橙色 2 2 错误 黄色 1 3 TRUE 橙色 2 2 错误 蓝色 1 1 TRUE 红色 1 3 TRUE 蓝色 2 2 FALSE 黄色 2 1 TRUE 红色 2 3 错误 红色 2 1 错误）

所需的输出是一个包含两列的区域，即条件为真的唯一颜色，以及出现该颜色且条件为真的出现次数。

生成第一列很简单：

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

在此示例中，该函数返回一个三行单列： 红 黄色 蓝 使用返回单个单元格的公式计算该范围中单个成员的出现次数很简单：

=COUNT(FILTER(Table1[Cost],Table1[Criteria]*(Table1[Color]=H10))) 

我使用了 Cost，因为它是数字并且与 Count 配合得更好，而 H10 是“Red”上方动态数组中的单元格之一。

这个函数准确地计算了我想要的东西，但它不会随着返回到上表的颜色数量的变化而缩小和扩展，所以我必须进入并将公式删除/复制到适当的范围。由于我的用户会不断更改条件列，因此通常会留下不同长度的两列。

接下来，我将此公式转换为LET版本，该版本给出了相同的结果。

=LET(lookup,H10,in,Table1[Color],cnt,Table1[Cost],crit,Table1[Criteria],
COUNT(FILTER(cnt,crit*(lookup=in))))

然后，我将公式 （H10） 中对颜色的引用替换为包含它的动态数组 （H10#），结果是值为 0 的单个单元格。

然后我删除了计数函数，只留下过滤器函数，它返回了 NA。因此，filter 函数不处理 H10# 引用，并返回单个值 NA。

我决定我需要编写一个可以处理动态引用的计数函数，我最终得到了：

Public Function DynamicCount(SearchIn, LookFor As Variant As Variant
Dim arr As Variant
Dim idx As Long

    ReDim arr(1 To LookFor.Rows.Count, 1 To 1)
        
    For idx = LBound(arr, 1) To UBound(arr, 1)
        arr(idx, 1) = Application.WorksheetFunction.CountIf(SearchIn, LookFor(idx, 1))
    Next idx
    
    DynamicCount = arr
    
End Function

我首先在没有标准的情况下测试了这一点，只需输入：

=DynamicCount(Table1[Color],H10#)

这有效，从单个单元格返回以下溢出：

6 3 3

然后，我尝试使用以下方法应用标准：

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

这返回了一个值错误！我不确定这是怎么回事。我只评估了过滤器部分：

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

这按预期返回了 8 种颜色的溢出列表。 在稍微玩了一下 vba 代码之后，我认为问题是过滤器函数正在将范围转换为变体数组。不知道如何与他们合作。做了一些 msgbox 调试，当 SearchIn 参数是 Variant（而不是范围）时，它似乎在下一行崩溃。

arr(idx, 1) = Application.WorksheetFunction.CountIf(SearchIn, LookFor(idx, 1))

任何帮助将不胜感激，无论是访问变体数组还是解决问题的其他方法。一定有更简单的方法...... 谢谢