Excel VBA 如何编写同时支持逻辑运算符和通配符的类SUMIF UDF

核心思路 #

原生Like运算符仅能处理通配符匹配场景，要兼容>20、<=100这类带逻辑运算符的条件，需要先对条件字符串做前缀检测：

1. 若条件以合法逻辑运算符开头，拆分出运算符和阈值后用Evaluate方法计算逻辑结果
2. 若条件无逻辑运算符前缀，直接走Like通配符匹配逻辑，和原生SUMIF行为对齐

关键注意点 #

• 逻辑运算符检测要按长度从长到短匹配：先检测>=、<=、<>这类双字符运算符，再检测>、<、=这类单字符运算符，避免双字符运算符被截断识别
• 数值类型要单独处理，避免字符串形式的数字出现"100" > "20"返回False的比较误差

完整可运行代码 #

' 条件匹配核心工具函数：支持逻辑运算符和通配符匹配
Private Function IsMatch(ByVal checkValue As Variant, ByVal condition As String) As Boolean
    ' 双字符运算符放前面，避免匹配截断
    Dim operators As Variant
    operators = Array(">=", "<=", "<>", ">", "<", "=")
    Dim op As Variant
    Dim opLength As Integer
    Dim threshold As String
    Dim evalExpression As String
    
    ' 遍历检测运算符前缀
    For Each op In operators
        opLength = Len(op)
        If Left(condition, opLength) = op Then
            threshold = Mid(condition, opLength + 1)
            ' 数值类型单独拼接表达式，避免字符串比较误差
            If IsNumeric(checkValue) And IsNumeric(threshold) Then
                evalExpression = CDbl(checkValue) & op & CDbl(threshold)
            Else
                ' 字符串类型转义双引号后拼接表达式
                evalExpression = """" & Replace(checkValue, """", """""") & """" & op & """" & Replace(threshold, """", """""") & """"
            End If
            IsMatch = Evaluate(evalExpression)
            Exit Function
        End If
    Next op
    
    ' 无匹配运算符时走通配符匹配
    IsMatch = checkValue Like condition
End Function

' 自定义SUMIF入口函数
Function CustomSUMIF(ByVal checkRange As Range, Optional ByVal condition As String = "*", Optional ByVal sumRange As Range = Nothing) As Double
    Dim i As Long
    Dim sumTotal As Double
    ' 未指定求和区域时默认使用检查区域
    If sumRange Is Nothing Then Set sumRange = checkRange
    ' 对齐求和区域和检查区域的大小
    Set sumRange = sumRange.Resize(checkRange.Rows.Count, checkRange.Columns.Count)
    
    sumTotal = 0
    For i = 1 To checkRange.Cells.Count
        If IsMatch(checkRange.Cells(i).Value, condition) Then
            If IsNumeric(sumRange.Cells(i).Value) Then
                sumTotal = sumTotal + sumRange.Cells(i).Value
            End If
        End If
    Next i
    CustomSUMIF = sumTotal
End Function

使用示例 #

和原生SUMIF调用规则完全一致：

• 逻辑运算符场景：=CustomSUMIF(A1:A10,">20") 计算A1到A10中大于20的数值之和
• 通配符匹配场景：=CustomSUMIF(B1:B10,"*北京*",C1:C10) 计算B列包含“北京”字样的行对应的C列数值之和

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