忽略 #DIV/0 的加权调和平均值

Question 1

我同意前面答案中的分析，但我对新功能并不那么着迷，而且我只在必要时使用它们，所以我更喜欢传统的解决方案。我建议使用以下公式：

=MMULT(--(data<>0),weights)/MMULT(IF(data<>0,1/data,0),weights)

其中data和weights代表适当的范围。

Answer

我同意前面答案中的分析，但我对新功能并不那么着迷，而且我只在必要时使用它们，所以我更喜欢传统的解决方案。我建议使用以下公式：

=MMULT(--(data<>0),weights)/MMULT(IF(data<>0,1/data,0),weights)

其中data和weights代表适当的范围。

Question 2

我可能有一个解决您问题的方法，尽管我不确定它在数学上是否正确。如果是这样的话，请随意评论我的帖子。

如您所知，您的问题来自数据集中的 0 值。一种解决方法是从计算中删除这些值。

但这会产生另一个问题：代表权重的数组大小不会相同，因此SUMPRODUCT会失败。

因此，如果数据集中的权重对应项为零，那么您也需要删除一些权重。

幸运的是，使用数组公式，这很容易做到！您需要以下函数来执行此操作（我建议查看 MS 文档以了解它们的工作原理）：

LET作为一般函数，能够设置可在公式中重复使用的变量
HSTACK水平组合两个数组
FILTER用于过滤数组（可能使用另一个数组）
TAKE并可DROP用于截断数组

以下是内联形式的公式：

=LET(x, B5:B7, y, D3:F3, xy, HSTACK(x, TRANSPOSE(y)), xy_filtered, FILTER(xy, DROP(xy,, 1)<>0), x_filtered, TAKE(xy_filtered,, 1), y_filtered, DROP(xy_filtered,, 1), PRODUCT(SUM(x_filtered),1/SUMPRODUCT(x_filtered,1/y_filtered)))

以下是用户友好形式的公式：

=LET(
x, B5:B7,
y, D3:F3,
xy, HSTACK(x, TRANSPOSE(y)),
xy_filtered, FILTER(xy, DROP(xy,, 1)<>0),
x_filtered, TAKE(xy_filtered,, 1),
y_filtered, DROP(xy_filtered,, 1),
PRODUCT(SUM(x_filtered),1/SUMPRODUCT(x_filtered,1/y_filtered))
)"

下面是它的工作原理：

这LET是主函数，用于定义计算中使用的局部变量。为了清晰起见，有些是临时变量。
x和y分别是重量范围（垂直）和数据集范围（水平）。您需要用自己的范围替换它们。
xy是由权重列和数据集转置组成的2列矩阵。现在权重和数据集值并排在同一个矩阵中。
xy_filtered是相同的矩阵，但是为零的行y被过滤掉了。
DROP(xy,,1)用于删除矩阵x中的列xy（即删除一行和一列）。换句话说，它代表y。
相反，TAKE(xy,,1)用于保留矩阵x中的列xy（即保留所有行和 1 列）
由于您的矩阵xy_filtered现在没有任何 0 值，因此您可以使用和函数将其拆分回x_filtered和变量。y_filteredDROPTAKE
函数的最后一个参数LET始终是您返回的内容。因此，我只是添加了您提供的函数，但删除了现在不必要的语句，并用和变量IF替换了您提供的范围。x_filteredy_filtered

以下是仅包含 3 个值的示例。我的公式（绿色）返回的值与您的（黄色）相同：

现在如果我们添加一个 0：

Answer