概括

要计算加权平均值，可以使用基于 SUMPRODUCT函数和SUM 函数的公式。在所示示例中，G5 单元格中的公式向下复制为：

=SUMPRODUCT(weights,C5:E5)/SUM(weights)

其中weights是命名区域I5:K5。由于公式向下复制，因此它返回 G 列中显示的加权平均值。

通用公式

=SUMPRODUCT(weights,values)/SUM(weights)

解释

在这个例子中，目标是 使用指定区域 (I5:K5) 中的权重和 C 列到 E 列中的分数，计算表格中每个姓名的加权平均分。加权平均值（也称为加权均值）是一种权重不同的平均值。换句话说，某些值具有更高的“权重”。我们可以通过将要计算平均值的值乘以其对应的权重，然后将结果之和除以权重之和来计算加权平均值。在 Excel 中，这可以用以下通用公式表示，其中权重和值是单元格区域：

=SUMPRODUCT(weights,values)/SUM(weights)

这个公式的核心是 SUMPRODUCT 函数。简而言之，SUMPRODUCT 函数将多个区域或数组相乘，并返回乘积之和。这听起来可能很枯燥，但 SUMPRODUCT 函数功能极其强大，广泛应用于各种实用公式中。

工作表示例

在所示工作表中，C 列到 E 列显示了 3 次测试的分数，权重显示在名为 weights (I5:K5) 的区域中。单元格 G5 中的公式为：

=SUMPRODUCT(weights,C5:E5)/SUM(weights)

首先观察左侧，我们使用SUMPRODUCT 函数将权重乘以相应的分数，然后将结果相加：

=SUMPRODUCT(weights,C5:E5) // returns  88.25

SUMPRODUCT 函数将两个数组中对应的元素相乘，然后返回乘积之和。我们可以用 G5 单元格来可视化这个运算，如下所示：

=SUMPRODUCT({0.25,0.25,0.5},{90,83,90})
=SUMPRODUCT({22.5,20.75,45})
=88.25

然后将结果除以权重之和：

=88.25/SUM(weights)
=88.25/SUM({0.25,0.25,0.5})
=88.25/1
=88.25

注意：计算加权平均值时，通常会赋予权重，使其之和为 1。如上所示，当权重之和为 1 时，分母变为 1，对公式没有影响。但是，权重之和不一定必须为 1，上述公式的通用形式旨在处理这两种情况。以下示例展示了权重之和不为 1 的情况。

由于公式向下复制到 G 列，命名区域I5:K5 的权重保持不变，因为它相当于绝对引用。但是，C5:E5 中的分数是 相对引用，因此会随着每一行的更新而变化。结果是列表中每个名称的加权平均值，如图所示。为了便于理解，F 列中的平均值是使用AVERAGE 函数正常计算的：

=AVERAGE(C5:E5)

重量总和不为 1

在上面的例子中，权重被设置为总和为 1，因此除数为 1，最终结果就是 SUMPRODUCT 函数返回的值。然而，该公式的一个优点是权重总和不必为 1。例如，我们可以将前两次测试的权重设为 1，将最后一次测试的权重设为 2（因为最后一次测试的重要性是前两次测试的两倍），加权平均值仍然相同：

在单元格 G5 中，公式的计算方法如下：

=SUMPRODUCT(weights,C5:E5)/SUM(weights)
=SUMPRODUCT({1,1,2},{90,83,90})/SUM(1,1,2)
=SUMPRODUCT({90,83,180})/SUM(1,1,2)
=353/4
=88.25

注意：上面花括号 {} 中的值是 数组，可以直接映射到 Excel 中的区域。

转换权重

SUMPRODUCT 函数要求数组维度必须兼容才能正常运行。例如，如果数据位于水平数组中，则权重也应位于水平数组中。如果维度不兼容，SUMPRODUCT 将返回 #VALUE 错误。为避免此问题，您可能需要转置权重以匹配数据。在下面的示例中，权重与上面的原始示例相同，但它们列在垂直范围内：

在这种情况下，要使用相同的公式计算加权平均值，我们需要使用TRANSPOSE 函数将权重“翻转”成一个水平数组，如下所示：

=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)

TRANSPOSE 操作完成后，垂直数组变为：

=TRANSPOSE({0.25;0.25;0.5}) // vertical array

变成如下所示的水平数组：

={0.25,0.25,0.5} // horizontal array

注意，分号现在变成了逗号，表示水平排列。从这一点开始，公式的求解方法与之前解释的相同。