excel vba vbnet条件求和求平均应用示例

红框内是需要求取的数值：D列要根据A列月份求I列月份均值。

E列月度考核奖金额度=当月总奖金/当月总系数*个人系数之和

首先考虑到通用性，先定义两个小函数：

Function 以A列为条件对B列求和(arr(,) As Object, A As Integer, 条件 As Object, B As Integer)
        Dim t As Double
        For i = LBound(arr, 1) To UBound(arr, 1)
            If arr(i, A) = 条件 Then t = t + arr(i, B)
        Next
        Return t
    End Function
    Function 以A列为条件对B列求平均(arr(,) As Object, A As Integer, 条件 As Object, B As Integer)
        Dim t As Double
        Dim k As Integer = 0
        For i = LBound(arr, 1) To UBound(arr, 1)
            If arr(i, A) = 条件 Then
                k = k + 1
                t = t + arr(i, B)
            End If
        Next
        Return t / k
    End Function

以上两个函数可以收藏到模块库中，下次使用简单调用即可。

   Dim xlapp = New excel
        Dim ws = xlapp.activesheet()
        Dim arr = ws.Getgrid(2, "A")‘获取以A2为左上角的数组
        Dim 当月总奖金, 当月总系数
        For i = 1 To UBound(arr, 1)
            ws.cells(i + 1, "D") = 以A列为条件对B列求平均(arr, 1, arr(i, 1), 3)
            当月总奖金 = 以A列为条件对B列求和(arr, 1, arr(i, 1), 2)
            当月总系数 = 以A列为条件对B列求和(arr, 1, arr(i, 1), 3)
            ws.cells(i + 1, "E") = 当月总奖金 / 当月总系数 * arr(i, 3)
        Next

        xlapp.quit()
        '探索3000原创代码