Excel VBA加权平均值如果自定义函数

Question

我正在尝试编写一个自定义函数，它将检查一个范围内的 2 个标准，然后对它找到的任何匹配项进行加权平均。我的代码在下面，它没有正确解释我的标准。它在我的标准上显示#Value。任何帮助将不胜感激!谢谢 :D

https://drive.google.com/file/d/0B6aefGNRPaHiRlR4a3Vvc3RNamM/edit?usp=sharing

Function WeightedAverageIf(Range As Range, Citeria1 As String, Column1 As Integer, Criteria2 As String, Column2 As Integer, Column3 As Integer, Column4 As Integer)
     lr = Range.Cells(Rows.Count, 1).End(xlUp).Row
     Dim num1 As Integer, per1 As Integer, num2 As Integer, per2 As Integer, counter As Integer
     coutner = 0
     For x = 1 To lr
        If counter = 0 Then
           If Range.Cells(x, Column1) = Criteria1 And Range.Cells(x, Column2) = Criteria2 Then num1 = Cells(x, Column3) And per1 = Range.Cells(x, Column4) And counter = counter + 1
           End If
        If counter > 0 Then
           If Range.Cells(x, Column1) = Criteria1 And Cells(x, Column2) = Criteria2 Then num2 = Range.Cells(x, Column3) And per2 = Range.Cells(x, Column4) And counter = counter + 1
           per1 = (((per1 * num1) + (per2 * num2)) / (num1 + num2))
           num1 = (num1 + num2)
        End If
     Next x
     WeightedAverageIf = per1
End Function

Answer 1

谢谢你们的帮助，你们为我指明了正确的方向!我不得不添加一些检查并绕过 if 语句，但我有一个应该适用于任何人的最终产品，所以我决定发布完成的代码。

这是只有一个匹配条件的代码。

Function WeightedAverageIf(Rng As Range, Find1 As String, Find1Column As Integer, WeightColumn As Integer, AVGColumn As Integer)
    lr = Rng.Rows.Count
    Dim num1 As Double, per1 As Double, num2 As Double, per2 As Double, counter As Integer
    coutner = 0

    For x = 1 To lr

        If counter > 0 Then
            If Rng.Cells(x, WeightColumn) = 0 Or Rng.Cells(x, WeightColumn) = "" Or IsNumeric(Rng.Cells(x, AVGColumn)) = False Or IsNumeric(Rng.Cells(x, WeightColumn)) = False Then GoTo skipif
            If Rng.Cells(x, Find1Column) = Find1 Then
                num2 = Rng.Cells(x, WeightColumn)
                per2 = Rng.Cells(x, AVGColumn)
                counter = counter + 1
            End If

            per1 = (((per1 * num1) + (per2 * num2)) / (num1 + num2))
            num1 = (num1 + num2)
        End If

        If counter = 0 Then
            If Rng.Cells(x, WeightColumn) = 0 Or Rng.Cells(x, WeightColumn) = "" Or IsNumeric(Rng.Cells(x, AVGColumn)) = False Or IsNumeric(Rng.Cells(x, WeightColumn)) = False Then GoTo skipif
            If Rng.Cells(x, Find1Column) = Find1 Then
                num1 = Rng.Cells(x, WeightColumn)
                per1 = Rng.Cells(x, AVGColumn)
                counter = counter + 1
            End If
        End If
skipif:
    Next x
    If counter = 1 Then
    WeightedAverageIf = per1
    ElseIf counter = 0 Then
    WeightedAverageIf = 0
    Else
    WeightedAverageIf = per1
    End If
End Function

这是具有 2 个匹配条件的代码。

Function WeightedAverageIfs(Rng As Range, Find1 As String, Find1Column As Integer, Find2 As String, FindColumn2 As Integer, WeightColumn As Integer, AVGColumn As Integer)
    lr = Rng.Cells(Rows.Count, 1).End(xlUp).Row
    Dim num1 As Double, per1 As Double, num2 As Double, per2 As Double, counter As Integer
    coutner = 0

    For x = 1 To lr

        If counter > 0 Then
            If Rng.Cells(x, WeightColumn) = 0 Or Rng.Cells(x, WeightColumn) = "" Or IsNumeric(Rng.Cells(x, AVGColumn)) = False Or IsNumeric(Rng.Cells(x, WeightColumn)) = False Then GoTo skipif
            If Rng.Cells(x, Find1Column) = Find1 And Rng.Cells(x, FindColumn2) = Find2 Then
                num2 = Rng.Cells(x, WeightColumn)
                per2 = Rng.Cells(x, AVGColumn)
                counter = counter + 1
            End If

            per1 = (((per1 * num1) + (per2 * num2)) / (num1 + num2))
            num1 = (num1 + num2)
        End If

        If counter = 0 Then
            If Rng.Cells(x, WeightColumn) = 0 Or Rng.Cells(x, WeightColumn) = "" Or IsNumeric(Rng.Cells(x, AVGColumn)) = False Or IsNumeric(Rng.Cells(x, WeightColumn)) = False Then GoTo skipif
            If Rng.Cells(x, Find1Column) = Find1 And Rng.Cells(x, FindColumn2) = Find2 Then
                num1 = Rng.Cells(x, WeightColumn)
                per1 = Rng.Cells(x, AVGColumn)
                counter = counter + 1
            End If
        End If
skipif:
    Next x
    If counter = 1 Then
    WeightedAverageIfs = per1
    ElseIf counter = 0 Then
    WeightedAverageIfs = 0
    Else
    WeightedAverageIfs = per1
    End If
End Function

我希望这将有助于 future 的人们。对于不熟悉 excel 和 VBA 的任何人，您都需要打开开发者控制台。插入一个新模块并粘贴上面的代码段之一。完成此操作后，您只需键入带有参数的函数 =WeightedAverageIf("您的数据范围","您需要在该范围内匹配的内容","该范围内的哪一列是搜索到的数据","具有权重的列的平均数”，“具有平均数的列”)