用VBA实现Excel函数03：SUMIF

2020-07-28 10:18:45 浏览数 (1)

1、函数的理解：

SUMIF，条件求和函数，是一个常用的数据统计公式。SUMIF函数原理可以分步理解：

代码语言：javascript复制
SUMIF(range, criteria, [sum_range])
  • 先是用第2个参数criteria与第1个参数range里的数据逐一做对比，返回FALSE或者TRUE
  • 根据返回的FALSE或者TRUE，再用IF函数提取第3个参数sum_range的数字
  • 最后再用SUM函数汇总

这就是1个标准的SUMIF函数使用方法。

2、代码实现

有了上面的理解，完成一个标准的SUMIF函数功能应该是比较简单的：

代码语言：javascript复制
Sub TestMySumIf()
    Dim rangeValus() As Variant
    Dim sum_range() As Variant
    
    rangeValus = Range("B2:B16").Value
    sum_range = Range("C2:c16").Value
    Debug.Print MySumIf(rangeValus, "赵四", sum_range)
End Sub
'输出：6192

Function MySumIf(rangeValus As Variant, criteria As Variant, sum_range As Variant) As Double
    Dim i As Long
    Dim dSum As Long
    
    For i = LBound(rangeValus, 1) To UBound(rangeValus, 1)
        If rangeValus(i, 1) = criteria Then
            dSum = dSum   VBA.Val(sum_range(i, 1))
        End If
    Next
    
    MySumIf = dSum
End Function

3、代码完善

SUMIF函数的参数提示上可以看出，第3个参数是在“[]”内的，首先我们也完成这个可省略的功能，省略的情况下，其实第1和第3参数相当于是同一个了，所以我们只要知道第3个参数sum_range没有传递的情况下，直接让3个参数sum_range等于第1个参数range就可以了：

代码语言：javascript复制
Sub TestMySumIf()
    Dim rangeValus() As Variant
    Dim sum_range() As Variant
    
    rangeValus = Range("C2:C16").Value
    Debug.Print MySumIf(rangeValus, 6192)
End Sub

Function MySumIf(rangeValus As Variant, criteria As Variant, Optional sum_range As Variant) As Double
    If VBA.IsMissing(sum_range) Then
        sum_range = rangeValus
    End If
    
    Dim i As Long
    Dim dSum As Long
    
    For i = LBound(rangeValus, 1) To UBound(rangeValus, 1)
        If rangeValus(i, 1) = criteria Then
            dSum = dSum   VBA.Val(sum_range(i, 1))
        End If
    Next
    
    MySumIf = dSum
End Function

IsMissing函数就是用来判断这种可选参数在调用时，是否提供了这个参数的传递。

SUMIF函数的第2个参数criteria输入形式是非常多样的，除了前面看到的输入的是条件本身之外，它还可以输入比较符：

代码语言：javascript复制
>  大于
<  小于
>=  大于或等于
<=  小于或等于

要实现这个功能，我们就需要用到VBA里的字符串操作了：

代码语言：javascript复制
Sub TestMySumIf()
    Dim rangeValus() As Variant
    Dim sum_range() As Variant
    
    rangeValus = Range("C2:C16").Value
    Debug.Print MySumIf(rangeValus, ">6666")
End Sub
'输出：607857

Function MySumIf(rangeValus As Variant, criteria As Variant, Optional sum_range As Variant) As Double
    If VBA.IsMissing(sum_range) Then
        sum_range = rangeValus
    End If
    
    '分离比较符和条件
    Dim strcp As String
    strcp = VBA.CStr(criteria)
    If VBA.Left(strcp, 2) = "<=" Then
        strcp = "<="
    ElseIf VBA.Left(strcp, 2) = ">=" Then
        strcp = ">="
    ElseIf VBA.Left(strcp, 1) = "<" Then
        strcp = "<"
    ElseIf VBA.Left(strcp, 1) = ">" Then
        strcp = ">"
    Else
        strcp = ""
    End If
    criteria = VBA.Mid(criteria, VBA.Len(strcp)   1)
    
    '如果数字前面带了比较符，criteria传入的是文本，而数字会小于文本的数字
    If VBA.IsNumeric(criteria) Then
        criteria = VBA.Val(criteria)
    End If
    
    Dim i As Long
    Dim dSum As Long
    
    For i = LBound(rangeValus, 1) To UBound(rangeValus, 1)
        '根据比较符来使用具体比较方法
        Select Case strcp
            Case ">="
            If rangeValus(i, 1) >= criteria Then
                dSum = dSum   VBA.Val(sum_range(i, 1))
            End If
            
            Case "<="
            If rangeValus(i, 1) <= criteria Then
                dSum = dSum   VBA.Val(sum_range(i, 1))
            End If
            
            Case "<"
            If rangeValus(i, 1) < criteria Then
                dSum = dSum   VBA.Val(sum_range(i, 1))
            End If
            
            Case ">"
            If rangeValus(i, 1) > criteria Then
                dSum = dSum   VBA.Val(sum_range(i, 1))
            End If
            
            Case Else
            If rangeValus(i, 1) = criteria Then
                dSum = dSum   VBA.Val(sum_range(i, 1))
            End If
        End Select
    Next
    
    MySumIf = dSum
End Function

Left，Mid，Len都是String类型的基础操作函数，在VBA中用的会比较多，用法比较简单，具体用法可以查看官方文档。

4、小结

实现1个简单的SUMIF函数，还非常的不完善，主要学习：

  • 可选参数是否传递的判断IsMissing函数。
  • 字符串的处理函数。
vba 编程算法

0 人点赞