具有类似 sumif 的条件功能的 Excel VBA 自定义函数

Excel VBA custom function with sumif-like criteria functionality

提问by Tmdean

In Excel, I'm writing a custom function in VBA that needs to take a criteria string and criteria range like the built-in SUMIFfunction. Does Excel expose the functionality to test a criteria string anywhere in its API or do I have to write it myself?

在 Excel 中，我正在 VBA 中编写一个自定义函数，它需要像内置SUMIF函数一样采用条件字符串和条件范围。Excel 是否公开了在其 API 中的任何位置测试条件字符串的功能，还是我必须自己编写？

In case it's relevant, I'm writing a "CountUniquesIf" formula, that counts the unique values in a range if they meet a criterion. This is what I have so far.

如果相关，我正在编写一个“CountUniquesIf”公式，如果它们符合标准，则计算范围内的唯一值。这是我到目前为止。

Function CountUniquesIf(CondRange As Range, Criteria As String, _
    Range As Range) As Long

    Static dict As New Scripting.Dictionary
    Dim index As Long

    index = 1
    For Each Cell In Range.Cells
        If CondRange(index).Value = Criteria And Cell.Value <> "" Then
            dict(Cell.Value) = Empty
        End If
        index = index + 1
    Next Cell

    CountUniquesIf = dict.Count
    dict.RemoveAll
End Function

采纳答案by devuxer

You can actually do the whole thing with just regular formulas if you want.

如果你愿意，你实际上可以只用常规公式来完成整个事情。

Please see:

请参见：

http://www.officearticles.com/excel/count_unique_values_in_microsoft_excel.htm

http://www.officearticles.com/excel/count_unique_values_in_microsoft_excel.htm

or

或者

http://office.microsoft.com/en-us/excel/HP030561181033.aspx

http://office.microsoft.com/en-us/excel/HP030561181033.aspx

You will need to modify the formula slightly, though, to cover the "if" part of your scenario:

但是，您需要稍微修改公式以涵盖场景的“if”部分：

=SUM(IF(FREQUENCY(IF((LEN(A1:A15)>0)*(B1:B15=D4),MATCH(A1:A15,A1:A15,0),""), IF((LEN(A1:A15)>0)*(B1:B15=D4),MATCH(A1:A15,A1:A15,0),""))>0,1))

Where A1:A15 is your Range, B1:B15 is your CondRange, and D4 is your Criterion.

Remember to enter this as an array formula (paste the formula and press Ctrl-Shift-Enter instead of just Enter).

请记住将此作为数组公式输入（粘贴公式并按 Ctrl-Shift-Enter 而不是仅按 Enter）。

That said, I think your VBA formula is a good solution too (probably more user-friendly than creating a monster array formula every time you need this type of count).

也就是说，我认为你的 VBA 公式也是一个很好的解决方案（可能比每次需要这种类型的计数时创建一个怪物数组公式更方便用户）。

Update

更新

Given your clarification, I really don't think there's a built-in "criterion analyzer", but I don't think it would be too difficult to enhance your formula to cover the different possible criteria. That way, your CountUniquesIfformula will really do what people think it does. Specifically, you could do a little parsing that checks for all the possible operators (is there anything besides "=", ">", ">=", "<", "<="?) that could be prefixed before the value.

鉴于您的澄清，我真的不认为有一个内置的“标准分析器”，但我认为增强您的公式以涵盖不同的可能标准并不太难。这样，您的CountUniquesIf公式将真正按照人们的想法行事。具体来说，您可以进行一些解析以检查所有可能的运算符（除了“=”、“>”、“>=”、“<”、“<=”之外，还有其他任何东西吗？）可以在值之前添加前缀.

回答by jtolle

Based on the clarifying comment, I think the easiest thing for you to do is to pass in an array of boolean values you get from using an array formula in the sheet, and then just test those.

根据澄清评论，我认为对您来说最简单的事情是传入您在工作表中使用数组公式获得的布尔值数组，然后测试它们。

That is, instead of passing in a range and a criterion, like b2:b15 and ">0", pass in the result of b2:b15>0, which will be an array or booleans. Then your test in your function can just be

也就是说，不是传入一个范围和一个标准，比如 b2:b15 和 ">0"，而是传入 b2:b15>0 的结果，这将是一个数组或布尔值。那么你在你的函数中的测试就可以是

If CondRange(index).Value And Cell.Value <> "" Then

If CondRange(index).Value And Cell.Value <> "" Then

and everything should work the way you want. Remember to enter the call to your UDF as an array formula.

一切都应该按照你想要的方式工作。请记住以数组公式的形式输入对 UDF 的调用。

It is possible to use Application.Evaluate with strings, but there are lots of limitations to doing it that way, and it seems like using an array formula to do your criteria test would be simpler in this case

可以将 Application.Evaluate 与字符串一起使用，但这样做有很多限制，在这种情况下，使用数组公式进行标准测试似乎更简单

回答by Dick Kusleika

Non-VBA way:

非VBA方式：

{=SUM(1/COUNTIF($A:$A01,$A:$A01)*(LEFT(A2:A1001)="C"))}

That will count uniques in A2:A1001 that start with "C"

这将计算 A2:A1001 中以“C”开头的唯一值

For VBA, consider using the Evaluate method of the Application Object

对于 VBA，考虑使用 Application Object 的 Evaluate 方法

If Asc(Left(Criteria, 1)) >= 60 And Asc(Left(Criteria, 1)) <= 62 Then
    bPass = Application.Evaluate(CondRange(Index).Value & Criteria)
Else
    bPass = CondRange(Index).Value = Criteria
End If

回答by klausnrooster

I'm about to turn in and excel crashes on me since I switched to vista. But I'll look at this in my office tomorrow. You might succeed with a 'wrapper' on worksheetfunction.SumIf If that wont' work for you post it here. A few questions/suggestion though: even if it will work, using a variable name like 'Range' or 'Cell' can be confusing. It's not immediately apparent how you are keeping the unique values in dict while disposing of the duplicates. Would you be opposed to factoring this into 2 functions; one to return an array of values meeting the criteria, and another to count the unique members of that array? Have you tested cruder versions of your script? The 'CondRange(index)' syntax looks odd to me. Maybe it's fine and I just never used it (except on arrays and the like, not ranges). Why would you need to test for empty cells or zero-length strings if you already have a criteria? Is the criteria expressible as a regexp? ['Now you have 2 problems' hahaha..] Can you give examples of criteria and some dummy data? Did you try recording a Data\Filter\Advanced Filter menu sequence? It has a list range, criteria range, and unique values only option. There has to be a way to 'wrap' that in your function as long as it behaves well with your criteria. If your criteria isn't too complex, look at the help on the "Like" operator. Hopefully some of this is helpful to you. I see you are used to a more expressive language.

自从我切换到 vista 以来，我即将上交并且 excel 崩溃了。但我明天会在我的办公室看看这个。您可能会在 worksheetfunction.SumIf 上使用“包装器”取得成功，如果这对您不起作用，请在此处发布。不过有几个问题/建议：即使它会起作用，使用像“Range”或“Cell”这样的变量名也会令人困惑。在处理重复项时如何在 dict 中保留唯一值并不是很明显。您是否会反对将其分解为 2 个函数？一个返回满足条件的值数组，另一个计算该数组的唯一成员？您是否测试过较粗略的脚本版本？'CondRange(index)' 语法对我来说看起来很奇怪。也许它很好，我只是从未使用过它（除了数组等，而不是范围）。如果您已经有了标准，为什么还需要测试空单元格或零长度字符串？标准是否可以表示为正则表达式？['现在你有两个问题'哈哈哈..] 你能举出标准和一些虚拟数据的例子吗？您是否尝试记录 Data\Filter\Advanced Filter 菜单序列？它有一个列表范围、条件范围和唯一值选项。只要它符合您的标准，就必须有一种方法将其“包装”在您的函数中。如果您的条件不是太复杂，请查看“Like”运算符的帮助。希望其中一些对您有所帮助。我看到你习惯了一种更具表现力的语言。] 你能举例说明标准和一些虚拟数据吗？您是否尝试记录 Data\Filter\Advanced Filter 菜单序列？它有一个列表范围、条件范围和唯一值选项。只要它符合您的标准，就必须有一种方法将其“包装”在您的函数中。如果您的条件不是太复杂，请查看有关“Like”运算符的帮助。希望其中一些对您有所帮助。我看到你习惯了一种更具表现力的语言。] 你能举例说明标准和一些虚拟数据吗？您是否尝试记录 Data\Filter\Advanced Filter 菜单序列？它有一个列表范围、条件范围和唯一值选项。只要它符合您的标准，就必须有一种方法将其“包装”在您的函数中。如果您的条件不是太复杂，请查看有关“Like”运算符的帮助。希望其中一些对您有所帮助。我看到你习惯了一种更具表现力的语言。希望其中一些对您有所帮助。我看到你习惯了一种更具表现力的语言。希望其中一些对您有所帮助。我看到你习惯了一种更具表现力的语言。