在Excel中计算加权平均值的4种方法

如果您要分析数据以获得切实可行的见解，那么就必须做到精确。在 Excel 中计算加权平均值。这项技能可以提高您见解的准确性。

除了简单的平均值之外，Excel 中的加权平均值还会考虑不同数据点的权重，从而提供更细致的视角。无论您是解读市场趋势、学生表现还是项目评估，掌握此 Excel 功能都会带来翻天覆地的变化。

这篇有关加权平均数的文章可以帮助您了解加权平均数的机制，阐明概念和实际实施。

通过本全面的 Excel 教程，提升您的分析能力并让您的数字讲述更有意义的故事。

什么是加权平均值？

加权平均值是一种统计方法，根据各个数据点的重要性或相关性为其分配不同级别的重要性或“权重”。

这种方法考虑了每个数据点对最终平均值的不同影响。较大的权重会放大某些值的影响，而较小的权重会降低其影响。

这种技术通常用于金融、评分系统和数据分析等不同领域。通过加入权重，加权平均值可以更准确地表示整体数据集，提供符合现实场景和优先事项的见解。

请参阅下文，了解可以使用加权平均值的地方：

与算术平均值相比，此统计函数可以更准确地洞察数据趋势。
加权平均计算通过考虑不同的资本贡献来帮助进行财务分析。
在合伙企业中，您可以根据所有合伙人对企业的个人贡献公平地分配利润。
在评分制度中，它通过考虑作业和考试权重来确保公平性。
在市场研究中，它体现了各个细分市场不同的影响。
根据正在执行的任务的加权平均值而不是遵循算术平均值来分配项目资源很容易且有效。

在 Excel 中计算加权平均值的原因

如果使用正确的函数或数据分析工具，您可以在不到一秒的时间内获得 Excel 中大型数据集表的加权平均值。
此外，您可以使用高级 Excel 工具（如 Excel VBA、Power Query、Office Scripts 等）自动化计算过程。
Excel 可帮助您减少使用纸笔手动计算数据集加权平均值可能产生的人为错误。
您可以轻松地在观众面前展示您的研究结果，或者通过 Microsoft OneDrive、SharePoint 等链接共享来分享结果。
此外，您可以使用 Excel 中的图表、图形或条件格式来直观地显示您的发现。

现在您已准备好在 Excel 中计算加权平均值，请按照下面提到的简便快捷的方法进行操作：

在 Excel 中使用加权平均公式

Excel 中没有任何专门的加权平均公式，例如算术平均公式： 平均的。但是，您可以使用公式 乘积和 在 Excel 中获取加权平均值。

有时，您还需要使用和公式 乘积和 得到加权平均值。

当权重加起来为 100 时的情况

假设您的数据集包含值和这些值的相应权重。权重总和为 100。那么，您只需使用 乘积和 公式。请参阅以下场景和执行此操作的步骤：

在Excel中计算加权平均值的4种方法

推荐：在Microsoft Excel中计算反正弦的5种方法

假设您的企业从五个不同的来源以不同的利率获得资金。从这些来源使用的资金量带有权重百分比。参见上例。

要在此处计算加权平均值，请在需要此值的单元格中输入以下公式：

=SUMPRODUCT(B2:B6,D2:D6)

现在，点击 进入 以十进制数形式获取上述数据集的加权平均值。

要将小数值转换为百分比，请使用以下公式：

=B8*100

在上面的代码中，你将小数值乘以 100 获得百分比加权平均值。

以下是您需要修改上述公式以使其在您的工作表中发挥作用的方法：

单元格范围 B2:B6 表示所用资本的成本或利息。请根据您自己的工作表更改此单元格范围。
相似地， D2:D6 代表每个资金来源的相应权重。您还需要更改此范围。

请记住，您至少要输入两个数组。第一个数组应该是要计算加权平均值的值范围。第二个数组应该是总计的权重或百分比 100。

权重加起来不等于 100 的情况

如果你使用 1 到 10 或 1 到 5 这样的权重尺度来表示链接值的优先级，那么你需要将 乘积和 值和或权重。然后，您就会得到准确的加权平均值。

上面的数据集显示了一名高中生在不同课程上取得的成绩。然而，当学校要评分时，他们会根据课程的重要性、复杂性等因素赋予不同的课程权重。

然后计算加权平均值来确定最终成绩。

在您选择的单元格中输入以下公式并点击 进入 找出学生获得的加权平均分：

=(SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6))

由于分配给课程的权重总和不等于 100，你需要除以 乘积和 经过和重量。

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

如果你想通过 数据透视表，你可以计算 Power Pivot 然后将其导入 数据透视表。具体操作如下：

选择包含输入值的数据集并单击 Power Pivot 标签。
点击 添加到模型 按钮里面表格块将数据集包含在 Power Pivot 工具。
您现在应该可以在里面看到您的数据集 Power Pivot 作为各种列。

单击最后一列 添加列 并将其重命名为 加权分数（或值）。
选择新创建的列中的第一个单元格，然后单击公式栏。
输入以下公式

=Table1[Test Score]*Table1[Weights (1 to 10)]

到目前为止，您已经将给定的值与它们各自的权重值相乘。

现在，你还需要总结重量和 加权分数（值） 列概述如下：

选择正下方的一个单元格 值区域分隔符 在重量柱子。
输入以下公式：

Sum of Weights (1 to 10):=SUM([Weights (1 to 10)])

同样，选择 电源枢轴分配器 在 加权分数（值） 列并输入以下公式：

Sum of Weighted Score:=SUM([Weighted Score])

按下 进入 在上述两种情况下， Power Pivot 计算选定列的总和。

现在，只需选择 值区域分隔符 并在 Excel 中输入此公式来获取加权平均值：

Measure 1:=[Sum of Weighted Score]/[Sum of Weights (1 to 10)]

点击文件菜单上的 Power Pivot 工具和打击节省并关闭该工具。

到目前为止， Power Pivot，您将给定值与相关权重相乘以获得加权值。然后，您创建了权重和加权值列的单独总和值。

通过将加权值的总和除以权重的总和，可以得到加权平均值。

所有上述公式都仅使用数据集的列标题。因此，请使用工作表中的匹配列修改上述公式，以使这些公式适合您。

要在数据透视表中可视化加权平均值，请按照以下步骤操作：

转到源数据工作表。
点击插入选项卡并选择 数据透视表 下拉式菜单。
在那里，选择 来自数据模型。
在 数据模型中的数据透视表 对话框中，单击 现有工作表，然后选择好的。

现在你应该看到 数据透视表 工作表上的框。在框的右侧，您还应该看到 数据透视表字段 控制板。

在那里，点击 表格1 展开其内容并勾选复选框 措施 1 可视化您的加权平均值 数据透视表。

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

如果您对 Excel VBA 编码有一点了解，则应该使用以下 VBA 脚本来自动执行计算加权平均值的整个过程。下面是使用代码的步骤：

按 Alt + F11 推出 Excel VBA 编辑器。
点击插入菜单栏上的按钮。
选择模块选项。
在空白处模块，复制并粘贴此脚本：

Sub CalculateWeightedAverage()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim totalWeightedScore As Double
    Dim totalWeight As Double
    Dim weightedAverage As Double
    
    Set ws = ThisWorkbook.Sheets("Sheet3")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    totalWeightedScore = 0
    totalWeight = 0
    
    For i = 2 To lastRow
        totalWeightedScore = totalWeightedScore + ws.Cells(i, "B").Value * ws.Cells(i, "C").Value
        totalWeight = totalWeight + ws.Cells(i, "C").Value
    Next i
    
    If totalWeight  0 Then
        weightedAverage = totalWeightedScore / totalWeight
    Else
        weightedAverage = 0
    End If
    
    ws.Cells(8, "B").Value = weightedAverage
End Sub

打节省按钮。
关闭 Excel VBA 编辑器。

现在，是时候运行脚本来自动计算所需的值了。方法如下：

按 Alt + F8 推出宏对话框。
选择 计算加权平均值 宏。
点击跑步执行代码。
您应该看到工作表上指定单元格中的值。

要修改代码以使其在您的工作表上运行，请按照以下说明操作：

改名 Sheet3 实际的工作表名称。
将所有值放入 B栏以及各自的权重 C 栏。
如果您想使用自己的列，请更改 B栏到值列的列标题。对 C 栏，它应该是给定值的权重。
修改 ws.Cells(8, "B").Value 代码元素到你想要加权平均值的实际单元格地址，例如 ws.Cells(10, "F").Value。

使用 Office 脚本在 Excel 中计算加权平均值

如果你拥有 Microsoft 365 商业标准版或更好的订阅，则可以使用 办公室脚本 在 Web 版 Excel 和适用于 Microsoft 365 桌面应用程序的 Excel 上自动执行任务。在此处找到您需要遵循的脚本和步骤：

点击 自动化 选项卡上的 Excel 功能区 菜单。
打 新脚本 按钮里面 脚本工具 堵塞。
A 代码编辑器 将在右侧打开。
复制并粘贴以下内容 办公室脚本 通过替换现有代码块来修改代码：

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range B8 on selectedSheet
	selectedSheet.getRange("B8").setFormulaLocal("=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)");
	// Set number format for range B8 on selectedSheet
	selectedSheet.getRange("B8").setNumberFormatLocal("0.00");
}

点击 保存脚本 按钮保存代码。
打跑步执行脚本。
Excel 将计算加权平均值并将其放入单元格中 B8。

修改上述 Office 脚本代码的方法如下：

B8 是加权平均输出的目的地，因此如果需要，请将其更改为另一个单元格地址。
B2:B6 单元格引用表示给定分数或其他类型的值的权重值。因此，您还需要将其更改为引用工作表数据。
C2:C6 表示加权平均计算给出的分数或值。因此，请更改与您的数据集相匹配的单元格引用。

结论

现在，您已经了解了在 Excel 中计算加权平均值的所有最佳和最简单的方法。如果您的源数据集很小且易于管理，则可以选择 乘积和 公式来计算该值。

如果你的数据集很大，你必须选择 Excel VBA 或者 办公室脚本。

最后，如果你正在开发 数据透视表 并且需要轻松计算加权平均值，请使用基于 Power Pivot 的方法。

在Excel中计算加权平均值的4种方法