Power Query 中实现移动加权平均的详细指南​

在数据分析和处理中，移动加权平均是一种非常有用的计算方法，它能够根据不同数据的权重，动态地计算平均值，帮助我们更准确地分析数据趋势和特征。Power Query 作为 Excel 中强大的数据处理工具，为我们实现移动加权平均提供了便捷的途径。下面将详细介绍如何在 Power Query 中完成移动加权平均的计算。​

一、移动加权平均的概念​

移动加权平均，是指每次进货的成本加上原有库存存货的成本，除以每次进货数量加上原有库存存货的数量，据以计算加权平均单位成本，作为在下次进货前计算各次发出存货成本依据的一种方法。在 Power Query 的场景下，我们可以将其拓展应用到各类数值型数据的分析中，通过设定合适的权重和移动窗口，动态计算数据的加权平均值，以便更好地观察数据的变化趋势，剔除随机波动的影响。​

二、数据准备​

假设我们有一份销售数据，包含 “日期”“产品名称”“销售数量”“销售单价” 等列，我们需要根据销售数量作为权重，计算销售单价的移动加权平均。首先，在 Excel 中打开 “数据” 选项卡，点击 “获取数据”，选择数据的来源（如 CSV 文件、数据库等），将数据导入到 Power Query 编辑器中。​

三、Power Query 操作步骤​

（一）分组排序​

在 Power Query 编辑器中，我们需要先对数据按照 “产品名称” 和 “日期” 进行分组和排序。选中 “产品名称” 和 “日期” 列，点击 “开始” 选项卡中的 “分组依据” 按钮。在弹出的 “分组依据” 对话框中，选择 “添加聚合”，将 “销售数量” 和 “销售单价” 分别进行求和聚合，得到每个产品在不同日期的总销售数量和总销售金额。完成分组聚合后，确保数据按照 “产品名称” 和 “日期” 的顺序排列，以便后续计算移动加权平均。对应的 M 代码如下：​

let
    Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],​
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"日期", type date}, {"产品名称", type text}, {"销售数量", Int64.Type}, {"销售单价", type number}}),​
    #"Grouped Rows" = Table.Group(#"Changed Type", {"产品名称", "日期"}, {​
        {"总销售数量", each List.Sum([销售数量])},​
        {"总销售金额", each List.Sum(List.Transform([销售数量], each _ * [销售单价]))}​
    }),​
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"产品名称", Order.Ascending}, {"日期", Order.Ascending}})​
    in
    #"Sorted Rows"​

​

（二）计算移动加权平均​

接下来，我们使用自定义函数来计算移动加权平均。在 Power Query 编辑器的 “添加列” 选项卡中，点击 “自定义列”。在弹出的 “自定义列” 对话框中，输入以下 M 代码来定义一个计算移动加权平均的函数：

(rows, windowSize) =>
let
    filteredRows = Table.FirstN(rows, windowSize),​
    totalWeight = List.Sum(filteredRows[总销售数量]),​
    weightedSum = List.Sum(List.Transform(filteredRows, each [总销售金额] / [总销售数量] * [总销售数量]))
    in​
    weightedSum / totalWeight​

​ 上述代码定义了一个函数，它接受两个参数：rows（表示一组数据行）和windowSize（表示移动窗口的大小）。函数内部首先筛选出指定窗口大小的数据行，然后计算这些数据行的总权重（总销售数量之和）以及加权总和（销售单价乘以销售数量之和），最后返回移动加权平均值。​ 定义好函数后，在 “自定义列” 对话框中，输入调用该函数的表达式来计算移动加权平均列。假设我们的移动窗口大小为 3，表达式如下：​

= Table.AddColumn(#"Sorted Rows", "移动加权平均", each #"移动加权平均函数"([_], 3))​

​ 其中"移动加权平均函数"是我们刚刚定义的函数名称，3表示移动窗口大小，[_]表示当前行所在的分组数据。​

（三）数据整理与输出​

完成移动加权平均列的计算后，我们可以根据需要对数据进行进一步的整理，如删除不需要的列、更改数据类型等。最后，点击 “开始” 选项卡中的 “关闭并上载” 按钮，将处理好的数据加载回 Excel 工作表中，以便进行后续的分析和可视化操作。​

四、注意事项​

移动窗口大小的选择：移动窗口大小的设置直接影响移动加权平均的结果。窗口过小，可能无法有效平滑数据波动；窗口过大，则可能导致数据滞后，无法及时反映数据的变化趋势。需要根据具体的数据特点和分析目的来合理选择窗口大小。​

数据完整性和准确性：在进行移动加权平均计算前，要确保数据的完整性和准确性。缺失值或错误数据可能会导致计算结果出现偏差，影响分析结论。如果存在缺失值，可以使用 Power Query 的 “填充” 功能进行处理；对于错误数据，需要进行修正或删除。​

性能问题：当数据量较大时，移动加权平均的计算可能会消耗较多的系统资源和时间。可以考虑对数据进行适当的筛选和聚合，减少不必要的计算量，提高计算效率。​

通过以上步骤，我们就可以在 Power Query 中实现移动加权平均的计算，利用这一强大的数据处理功能，更好地挖掘数据背后的信息，为数据分析和决策提供有力支持。如果你在实际操作过程中遇到其他问题或有进一步的需求，欢迎随时交流。

推荐学习书籍 《CDA一级教材》适合CDA一级考生备考，也适合业务及数据分析岗位的从业者提升自我。完整电子版已上线CDA网校，累计已有10万+在读~ 免费加入阅读：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0