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&, resultFor Each w In weighti = i + 1: wrr(i) = wNextFor Each d In dataj = j + 1: drr(j) = dNextIf i <> j Then WS = "Error": Exit Functionn = i: ReDim arr(1 To n): ReDim brr(1 To n): i = 0: j = 0If mode = False Thenm = WorksheetFunction.sum(wrr)For i = 1 To n '数据整理，将data中非数值部分转为0If WorksheetFunction.IsNumber(drr(i)) = True Thenj = j + 1: arr(j) = 1 / m * wrr(i): brr(j) = drr(i)Elsej = j + 1: arr(j) = 1 / m * wrr(i): brr(j) = 0End IfNextElseFor i = 1 To n '数据整理，忽略data中非数值部分If WorksheetFunction.IsNumber(drr(i)) = True Thenj = j + 1: arr(j) = wrr(i): brr(j) = drr(i): m = m + arr(j)End IfNextFor i = 1 To j '重新分配权重arr(i) = 1 / m * arr(i)NextEnd IfFor i = 1 To jresult = result + arr(i) * brr(i)NextWS = resultEnd Function
举例
mode参数为0时，即为F列计算结果，b1:e1权重和为100%，相当于忽略data非数值部分，其余部分权重*分值，最后求和
mode参数为1时，即为G列计算结果，忽略data非数值部分，其余部分权重重新按100%分配后再*分值，最后求和
