如何在 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 的处理过程:
- SUMPRODUCT 将 85 乘以 20%,78 乘以 30%,92 乘以 50%。
- 它将这三个乘积相加:17 + 23.4 + 46 = 86.4。
- SUM 将权重相加:20% + 30% + 50% = 100%。
- 公式将 86.4 除以 100%,得出 86.4。
当权重相加等于 100% 时,除法不会改变任何结果。但还是保留它。如果以后有人更改了权重,导致总和变为 90%,该公式仍然能给出正确的结果。
微软列出了两条与此相关的 SUMPRODUCT 规则。范围“必须具有相同的维度”,否则函数会返回 #VALUE! 错误。并且 SUMPRODUCT“将非数值型的数组元素视为 0”。空白或文本权重会默默地被计为零,因此请检查您的数据。
微软还警告不要使用整列引用。它表示,为了获得最佳性能,SUMPRODUCT“不应与整列引用一起使用”。请使用 B2:B500,而不是 B:B。
方法 2:辅助列
有些人更喜欢看到每个步骤。辅助列显示了每个乘积,这使得加权平均值易于检查和解释。
- 在 D 列中,输入一个表头,例如 数值 × 权重。
- 在 D2 中,输入
=B2*C2并向下复制。 - 在最后一行下方,使用
=SUM(D2:D4)对 D 列求和。 - 使用
=SUM(C2:C4)对权重求和。 - 用第一个总和除以第二个总和。
在成绩示例中,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。这是存货的真实平均成本,也是用于计算利润率或重新订货计划的数据。
请注意,这里的权重是件数,而不是百分比。这没关系。除以权重之和可以处理任何类型的权重。
数据透视表中的加权平均值
数据透视表可以快速汇总大型表格,但其内置选项不包括加权平均值。数据透视表中的“平均值”选项是普通平均值。这里有一个简单的解决方法。
- 在源数据表中,添加一个将数值乘以权重的辅助列,例如
=B2*C2。将其命名为 Product。 - 刷新数据透视表,以便它能看到新列。
- 选择数据透视表,然后选择数据透视表分析 > 字段、项目和集 > 计算字段。
- 命名该字段,例如 Weighted price,并输入公式
=Product/Units。 - 将新字段添加到值区域。
这之所以有效,是因为计算字段除以的是总计,而不是单行。对于每个组,它用乘积之和除以权重之和。这正是正确的数学计算方法。
使用您需要的数字格式格式化新字段,例如保留两位小数。现在,数据透视表会为您拖入行中的每个地区、产品或月份显示正确的数据。
使用百分比计算加权平均值
许多读者询问如何处理以百分比表示的权重。有两种情况。
权重相加等于 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 个文件”。
步骤 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。
平均值和加权平均值有什么区别?
普通平均值对每个数值一视同仁。加权平均值赋予每个数值一个权重,因此更重要的数值对结果的影响更大。