Excel·VBA加权求和函数

Function WS(ByVal weight, ByVal data, Optional mode As Boolean = 1)
    '加权求和函数WS(权重数组, 数值数组, 模式)，weight和data可以为数组、range
    'mode为0时将data中非数值部分转为0；为1时忽略data非数值部分，并重新分配权重数组
    Dim wrr(1 To 1000), drr(1 To 1000), arr, brr, i&, j&, m, n&, result
    For Each w In weight
        i = i + 1: wrr(i) = w
    Next
    For Each d In data
        j = j + 1: drr(j) = d
    Next
    If i <> j Then WS = "Error": Exit Function
    n = i: ReDim arr(1 To n): ReDim brr(1 To n): i = 0: j = 0
    If mode = False Then
        m = WorksheetFunction.sum(wrr)
        For i = 1 To n  '数据整理，将data中非数值部分转为0
            If WorksheetFunction.IsNumber(drr(i)) = True Then
                j = j + 1: arr(j) = 1 / m * wrr(i): brr(j) = drr(i)
            Else
                j = j + 1: arr(j) = 1 / m * wrr(i): brr(j) = 0
            End If
        Next
    Else
        For i = 1 To n  '数据整理，忽略data中非数值部分
            If WorksheetFunction.IsNumber(drr(i)) = True Then
                j = j + 1: arr(j) = wrr(i): brr(j) = drr(i): m = m + arr(j)
            End If
        Next
        For i = 1 To j  '重新分配权重
            arr(i) = 1 / m * arr(i)
        Next
    End If
    For i = 1 To j
        result = result + arr(i) * brr(i)
    Next
    WS = result
End Function

举例

mode参数为0时，即为F列计算结果，b1:e1权重和为100%，相当于忽略data非数值部分，其余部分权重*分值，最后求和

mode参数为1时，即为G列计算结果，忽略data非数值部分，其余部分权重重新按100%分配后再*分值，最后求和