☰

示例数据：

tag-icon • tag-icon microsoft-excel worksheet-function
示例数据：

我有两个范围，RangeA 和 RangeB。我主要想执行以下操作：

=SUMPRODUCT(RangeA, 1/RangeB) / TotalItems

获得平均百分比。但是，两个范围可能都散布着空白单元格，并且我只想在它们都不为空时包含 RangeA 和 RangeB 中的相应单元格。

我尝试过以下变化：

=SUMPRODUCT((RangeA<>"")*RangeA, 1/((RangeB<>"")*RangeB)) / TotalItems

但到目前为止，我尝试过的所有方法都SUMPRODUCT给出了“#DIV/0！”错误，因为它最终只是将空白转换为零（请注意，使用RangeB<>0会产生相同的结果）。

我可以轻松使用以下自定义函数：

Function AvgPerc(Range1 As Range, Range2 As Range) As Single
    Dim ind As Integer, inVal As Single, outVal As Single, _
        Perc As Double, TotPerc As Double, valCount As Integer
    For ind = 1 To Range1.Rows.Count
        If IsNumeric(Range1.Item(ind, 1)) And IsNumeric(Range2.Item(ind, 1)) Then
            inVal = Range1.Item(ind, 1)
            outVal = Range2.Item(ind, 1)
            If inVal <> 0 And outVal <> 0 Then
                Perc = CDbl(outVal) / CDbl(inVal)
                TotPerc = TotPerc + Perc
                valCount = valCount + 1
            End If
        End If
    Next ind
    If valCount = 0 Then
        AvgPerc = 0
    Else
        AvgPerc = TotPerc / valCount
    End If
End Function

但是，这要求将工作簿保存为启用宏的函数，而会有人使用此工作簿但未启用宏，我不想要求他们为这种简单的事情启用宏（此外，这会给以后开始使用工作簿的人带来困惑，因为我可能无法解释发生了什么）。

最终，我想我可能能够使用命名范围来执行此操作而无需使用宏，但我不确定如何从命名范围中删除“#DIV / 0！”结果，而且我确实需要复制上面自定义函数中的所有行为。

示例数据：

示例数据

请注意，理想情况下，我应该能够在每列的底部包含空行，以便人们以后可以添加更多数据。

答案1

您使用的方法（(RangeB<>"")*RangeB)）将空值转换为0，当它是除数时显然也会出现同样的错误。

在这种情况下可以使用两种方法：

  • =SUM(IFERROR(A2:A5/B2:B5,0))
  • =SUMPRODUCT(A2:A5,IF(B2:B5=0,0,1/B2:B5))

两者都是数组公式，需要用++CTRL完成SHIFTENTER

答案2

如果SUMPRODUCT()出现问题，不要使用它。

在C2进入：

=IF(B2="","",A2/B2)

并复制下来。然后在另一个单元格中：

=AVERAGE(C2:C9999)

在此处输入图片描述

您可以避免辅助列与数组公式：

=SUM(IFERROR(A1:A100/B1:B100,""))/COUNT(B2:B100)

或者

=AVERAGE((IFERROR(A2:A101/B2:B101,"")))

数组公式Ctrl必须使用++而不是仅仅使用键Shift来输入。EnterEnter

相关内容