代码之家 › 专栏 › 技术社区 › Tmdean

具有类似SUMIF标准功能的Excel VBA自定义函数

excel-vba vba excel

Tmdean · 技术社区 · 16 年前

在Excel中,我在VBA中编写了一个自定义函数,它需要采用一个条件字符串和条件范围,就像内置的 SUMIF 功能。Excel是否公开了在其API的任何地方测试条件字符串的功能,或者我必须自己编写它?

如果相关的话,我正在写一个“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

4 回复 | 直到 16 年前

devuxer 16 年前

如果你愿意的话,你可以只用正则公式来完成整个工作。

请参阅:

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

或

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

不过,您需要稍微修改公式,以涵盖场景中的“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.

记住要将其作为数组公式输入(粘贴公式并按ctrl-shift-enter而不是只按enter)。

也就是说,我认为您的vba公式也是一个很好的解决方案(可能比每次需要这种类型的计数时创建一个怪物数组公式更容易使用)。

更新

如果你解释清楚,我真的不认为有内置的“标准分析器”,但我认为增强你的公式以涵盖不同的可能标准并不太困难。这样,你的 CountUniquesIf 公式真的会像人们想象的那样。具体来说,您可以做一些分析,检查所有可能的运算符(除了“=”,“>”,“>=”,“<”,“<=”?)可以在值之前加前缀。

jtolle 16 年前

基于清晰的注释,我认为对您来说最容易做的事情是传入一个布尔值数组,您可以使用工作表中的数组公式获得这些值,然后测试它们。

也就是说,不是在范围和条件(如b2:b15和“>0”)中传递,而是传递b2:b15>0的结果,该结果将是一个数组或布尔值。那么你在你的功能测试中

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

一切都应该按你想要的方式进行。请记住将对UDF的调用作为数组公式输入。

可以使用application.evaluation和字符串,但是这样做有很多限制,而且在这种情况下,使用数组公式进行标准测试似乎更简单

Dick Kusleika 16 年前

非VBA方式:

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

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

对于VBA,考虑使用应用程序对象的评估方法

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

klausnrooster 16 年前

我刚要进去,Excel就崩溃了,因为我换了Vista。但明天我会在办公室看这个。如果WorksheetFunction.sumif上的“wrapper”不起作用,您可能会成功地将其发布在这里。不过,有几个问题/建议:即使可以,使用变量名(如“range”或“cell”)也会令人困惑。在处理重复项时,如何在dict中保留唯一值并不是很明显。您是否反对将其分解为两个函数:一个函数返回满足条件的值数组,另一个函数计算该数组的唯一成员?你测试过你的脚本的cruder版本吗?“condrange(index)”语法对我来说很奇怪。也许它很好,我从来没有用过(除了数组之类的,不是范围)。如果已经有条件,为什么需要测试空单元格或零长度字符串?条件是否可以表示为regexp?[现在你有两个问题'哈哈哈…]你能举出标准和一些虚拟数据的例子吗?是否尝试录制数据\筛选器\高级筛选器菜单序列?它有一个列表范围、条件范围和唯一值选项。必须有一种方法来“包装”您的函数,只要它符合您的标准。如果您的条件不太复杂,请查看“like”运算符的帮助。希望其中一些对您有所帮助。我看你习惯了一种更具表现力的语言。