超级促销周Claude Skills——20% 折扣
Tips

如何在 Excel 中计算加权平均值:2026 年的 3 种简单方法

Powerdrill Bloom·
如何在 Excel 中计算加权平均值:2026 年的 3 种简单方法

要在 Excel 中计算加权平均值,请将每个数值乘以其权重,然后将结果相加,再除以权重之和。最快的公式是 =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4),其中 B 列存放数值,C 列存放权重。如果您的权重相加已经等于 100%,那么仅使用 SUMPRODUCT 就能得出答案。

本指南将解释什么是加权平均值以及何时使用它。然后,它将介绍在 Excel 中计算加权平均值的三种方法:结合 SUM 的 SUMPRODUCT、辅助列以及带条件的 SUMPRODUCT。每种方法都包含一个实际示例。函数详情源自微软支持页面,阅读日期为 2026 年 11 月 17 日。

什么是加权平均值

普通平均值对每个数值一视同仁。微软将 AVERAGE 函数描述为返回“参数的平均值(算术平均值)”。每个数字只计算一次。

加权平均值赋予某些数值比其他数值更大的影响力。每个数值都有一个权重,权重较大的数值会将结果拉向它们。

公式很简单:

加权平均值 = (数值 1 × 权重 1 + 数值 2 × 权重 2 + …) ÷ (权重 1 + 权重 2 + …)

这里有一个简单的例子。一门课程的成绩由家庭作业、期中考试和期末考试组成。期末考试占成绩的一半,因此普通平均值会得出错误的结果。

Component Score Weight
家庭作业 85 20%
期中考试 78 30%
期末考试 92 50%

这三个分数的普通平均值是 85。加权结果是 86.4,因为较高的期末考试分数占了最大的权重。这种差异正是加权平均值的重要意义所在。

何时使用加权平均值

只要某些项目比其他项目更重要,就应该使用加权平均值。常见情况包括:

  • 成绩。 考试、项目和家庭作业占有不同的权重。
  • 价格。 在不同规模的订单中支付的平均价格。
  • 投资组合。 不同价值持仓的平均回报率。
  • 调查。 根据群体规模或样本权重进行加权的回复。
  • 库存。 以不同价格购买的存货的加权平均成本。
  • KPI。 由多个具有不同重要性的指标构建的分数。

权重可以来自不同的地方。有时它们是由规则设定的,例如教学大纲规定期末考试占 50%。有时它们来自数据本身,例如售出的件数或每项持仓的价值。在这两种情况下,计算方法都是相同的。

如果每个项目都同样重要,那么普通平均值就足够了。如果某个项目显然应该在结果中占有更大的发言权,你就需要权重。

设置您的数据

在编写任何公式之前,请将数据放入一个简单的表格中。每个项目使用一行,并包含以下列:

Column What goes in it
A 项目名称,例如家庭作业
B 数值,例如分数
C 权重,以数字或百分比表示

权重可以是百分比(例如 20%),也可以是普通数字(例如售出件数)。只要您除以权重之和,这两种方式都适用于以下公式。

两次检查可以避免大多数错误。首先,确保同一行中的每个数值都有一个权重。其次,保持数值和权重范围的大小相同,否则公式会返回错误。

将范围转换为 Excel 表格也有所帮助。选择该范围,然后选择开始 > 套用表格格式。这样,新行就会扩展表格,并且使用表格引用的公式会自动获取它们。

方法 1:SUMPRODUCT 和 SUM

这是在 Excel 中计算加权平均值的标准方法。它在一个公式中使用了两个函数。

微软表示,SUMPRODUCT“返回相应范围或数组的乘积之和”。通俗地说,它将每个数值乘以其权重,然后将结果相加。

语法为 =SUMPRODUCT(array1, [array2], [array3], ...)。在我们的成绩示例中,数值位于 B2:B4,权重位于 C2:C4。输入:

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

以下是 Excel 的处理过程:

  1. SUMPRODUCT 将 85 乘以 20%,78 乘以 30%,92 乘以 50%。
  2. 它将这三个乘积相加:17 + 23.4 + 46 = 86.4。
  3. SUM 将权重相加:20% + 30% + 50% = 100%。
  4. 公式将 86.4 除以 100%,得出 86.4。

当权重相加等于 100% 时,除法不会改变任何结果。但还是保留它。如果以后有人更改了权重,导致总和变为 90%,该公式仍然能给出正确的结果。

微软列出了两条与此相关的 SUMPRODUCT 规则。范围“必须具有相同的维度”,否则函数会返回 #VALUE! 错误。并且 SUMPRODUCT“将非数值型的数组元素视为 0”。空白或文本权重会默默地被计为零,因此请检查您的数据。

微软还警告不要使用整列引用。它表示,为了获得最佳性能,SUMPRODUCT“不应与整列引用一起使用”。请使用 B2:B500,而不是 B:B。

方法 2:辅助列

有些人更喜欢看到每个步骤。辅助列显示了每个乘积,这使得加权平均值易于检查和解释。

  1. 在 D 列中,输入一个表头,例如 数值 × 权重。
  2. 在 D2 中,输入 =B2*C2 并向下复制。
  3. 在最后一行下方,使用 =SUM(D2:D4) 对 D 列求和。
  4. 使用 =SUM(C2:C4) 对权重求和。
  5. 用第一个总和除以第二个总和。

在成绩示例中,D 列显示 17、23.4 和 46。它们相加等于 86.4。权重相加等于 100%,因此加权平均值是 86.4。

这与 SUMPRODUCT 在一个步骤中进行的数学计算相同。微软自己的页面显示,像 =C2*D2+C3*D3+C4*D4+C5*D5 这样更长的公式会给出与 SUMPRODUCT“相同的结果”。

在教学、审计或任何需要他人理解逻辑的工作表中使用辅助列。当您想要一个紧凑的工作表时,请使用方法 1。

方法 3:带条件的 SUMPRODUCT

有时您只需要对部分数据进行计算。例如,您可能想要某个地区的平均支付价格,并按件数进行加权。

SUMPRODUCT 可以在公式内部测试条件。微软的示例使用 =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9) 计算在某个地区售出的樱桃数量。比较会返回 TRUE 或 FALSE,在相乘时它们会起到 1 或 0 的作用。

在这里应用相同的思路。假设 A 列存放地区,B 列存放价格,C 列存放件数。要获取东部(East)地区的加权平均价格:

=SUMPRODUCT((A2:A100="East")*B2:B100*C2:C100)/SUMIF(A2:A100,"East",C2:C100)

上半部分将价格乘以件数,但仅针对东部(East)行。下半部分使用 SUMIF 仅对东部(East)行的件数求和。微软解释说,SUMIF 对“满足您指定条件的范围内的值”进行求和。

将地区名称放入单元格中,而不是直接输入“East”。这样您就可以通过更改单元格来查看每个地区,而无需编辑公式。

对于两个条件(例如地区和月份),请在顶部添加第二个比较。在底部使用 SUMIFS 代替 SUMIF。

实际示例:加权平均价格

成绩是经典的例子。在工作中,价格同样常见。这里有一个采购示例。

一家商店以不同的价格三次购买了同一种产品。价格为单价。

Order Units Price per unit Units × price
订单 1 10 4.00 40.00
订单 2 25 3.20 80.00
订单 3 15 5.00 75.00
总计 50 195.00

这三个价格的普通平均值是 4.07。这个数字具有误导性,因为大多数件数来自最便宜的订单。

加权平均值使用件数作为权重:

=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)

上半部分是 195。下半部分是 50。加权价格为每件 3.90。这是存货的真实平均成本,也是用于计算利润率或重新订货计划的数据。

请注意,这里的权重是件数,而不是百分比。这没关系。除以权重之和可以处理任何类型的权重。

数据透视表中的加权平均值

数据透视表可以快速汇总大型表格,但其内置选项不包括加权平均值。数据透视表中的“平均值”选项是普通平均值。这里有一个简单的解决方法。

  1. 在源数据表中,添加一个将数值乘以权重的辅助列,例如 =B2*C2。将其命名为 Product。
  2. 刷新数据透视表,以便它能看到新列。
  3. 选择数据透视表,然后选择数据透视表分析 > 字段、项目和集 > 计算字段。
  4. 命名该字段,例如 Weighted price,并输入公式 =Product/Units。
  5. 将新字段添加到值区域。

这之所以有效,是因为计算字段除以的是总计,而不是单行。对于每个组,它用乘积之和除以权重之和。这正是正确的数学计算方法。

使用您需要的数字格式格式化新字段,例如保留两位小数。现在,数据透视表会为您拖入行中的每个地区、产品或月份显示正确的数据。

使用百分比计算加权平均值

许多读者询问如何处理以百分比表示的权重。有两种情况。

权重相加等于 100%。 您可以直接使用 =SUMPRODUCT(B2:B4,C2:C4),而无需进行除法。结果已经是正确的。尽管如此,除以 SUM(C2:C4) 毫无成本,并且可以防止四舍五入或输入错误。

权重相加不等于 100%。 务必除以权重之和。否则结果会偏高或偏低。

将权重单元格格式化为百分比。选择它们,然后从开始选项卡上的数字格式列表中选择百分比。在格式化为百分比的单元格中输入 20,Excel 会将其存储为 0.2。

一个常见的错误是混合样式,例如在第一行输入 20%,在下一行输入 30。这样 Excel 会将第二个权重视为 3,000%。请保持每个权重格式一致。

使用 AI 在 Excel 中计算加权平均值

上述公式适用于单个表格。当您的数据分布在许多行或多个文件中时,设置范围会很费时间。AI 工具可以读取文件并为您进行计算。

以下是使用 Powerdrill Bloom 的三个步骤。

步骤 1:上传您的 Excel 文件

上传包含您的数值和权重的工作簿或 CSV。 Excel Calculations 页面要求您确保数据“格式正确,以便进行顺利的计算”。

您可以一次上传多个文件。该页面指出,您可以“一次向数据集中上传最多 10 个文件”。

上传 Excel 文件以使用 Powerdrill Bloom 计算加权平均值

步骤 2:用自然语言请求计算加权平均值

输入您需要的计算。例如:“按地区计算每件商品的平均价格,并按售出件数进行加权。”

该页面指出,该工具“将识别并应用正确的公式”。让它显示所使用的公式,以便您可以在自己的工作表中重新构建它。

步骤 3:检查结果并重复使用公式

将结果与对一个组进行的快速手动检查进行对比。然后将公式复制到您的工作簿中,或导出汇总表。

该页面指出,该工具将“根据您的文本输入快速生成准确的结果”。将公式保留在工作表中,以便在数据更改时更新结果。

在重复使用公式之前检查加权平均值结果

要避免的常见错误

这些是在 Excel 中计算加权平均值时最常出现的问题。

  • 使用 AVERAGE。 AVERAGE 给每个数值相同的权重。请改用 SUMPRODUCT 和 SUM。
  • 未除以权重。 除非权重相加正好等于 100%,否则请除以它们的总和。
  • 范围不匹配。 SUMPRODUCT 需要大小相同的范围,否则会返回 #VALUE!。
  • 空白权重。 空白权重会被计为零,这会在没有警告的情况下丢弃该行。
  • 混合百分比格式。 保持每个权重均为百分比或均为数字。
  • 整列引用。 为了提高速度,请使用有界范围,例如 B2:B500。
  • 硬编码条件。 将条件放入单元格中,以便公式易于重复使用。

对于其他常见的计算,请参阅我们关于如何在 Excel 中制作数据透视表以及如何在 Excel 中进行 ABC 分析的指南。

当您的数值和权重存在于大型导出文件中时,请尝试使用 Powerdrill Bloom,只需一句话即可请求计算加权平均值。

常见问题解答

在 Excel 中计算加权平均值的公式是什么?

使用 =SUMPRODUCT(values,weights)/SUM(weights)。例如, =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) 将每个数值乘以其权重,将结果相加,然后除以总权重。

我可以在不使用 SUMPRODUCT 的情况下计算加权平均值吗?

可以。添加一个将每个数值乘以其权重的辅助列。然后用该列的总和除以权重的总和。

如何在 Excel 中使用百分比计算加权平均值?

如果百分比相加等于 100%,则数值和权重的 SUMPRODUCT 即可得出答案。如果不是,请除以权重之和。

如何计算带条件的加权平均值?

在 SUMPRODUCT 内部将一个条件(例如 (A2:A100="East"))与数值 and 权重相乘。然后除以相同条件下权重的 SUMIF。

平均值和加权平均值有什么区别?

普通平均值对每个数值一视同仁。加权平均值赋予每个数值一个权重,因此更重要的数值对结果的影响更大。

来源: 微软支持,SUMPRODUCT 函数 · 微软支持,AVERAGE 函数 · 微软支持,SUMIF 函数。