Excel Power Query实现各公司产品相邻年度利润对比

核心逻辑 #

以公司+产品为匹配维度，定位每条记录对应主体在前一年度的利润数据，自动适配不同公司起始统计年份不一致、部分产品非连续年度销售的场景，无匹配记录时返回空值避免错配。

具体操作步骤 #

• 1. 数据预处理
     将原始数据导入Power Query编辑器，校验4个字段的类型配置：Company设为文本类型、Product设为文本类型、Profit设为数值类型、Year设为整数类型。
• 2. 基础排序
     对全表按优先级排序：先按Company升序，再按Product升序，最后按Year升序，保证同主体的年度记录按时间顺序排列。
• 3. 添加上一年度利润列
     可根据数据量选择对应实现方式：
  • 小数据量（<10万行）：直接添加自定义列，逐行匹配上一年记录，M公式如下，注意将公式里的排序后的步骤名替换为你当前查询里上一步排序的实际步骤名称：

= let
curr_company = [Company],
curr_product = [Product],
curr_year = [Year],
match_last = Table.SelectRows(排序后的步骤名, (x) => x[Company] = curr_company and x[Product] = curr_product and x[Year] = curr_year - 1)
in
if Table.RowCount(match_last) = 1 then match_last{0}[Profit] else null

• 大数据量（≥10万行）：用分组+索引偏移的方式提升计算性能，核心M代码如下：

= Table.Group(排序后的数据, {"Company", "Product"}, {
    {"明细", (t) =>
        let
            sort_by_year = Table.Sort(t,{"Year", Order.Ascending}),
            add_index = Table.AddIndexColumn(sort_by_year, "idx", 0, 1, Int64.Type),
            add_last_profit = Table.AddColumn(add_index, "上一年度利润", (r) => try add_index[r[idx]-1][Profit] otherwise null)
        in
            add_last_profit
    }
}),
// 后续操作：展开「明细」列，保留需要的字段即可

• 4. 扩展计算（可选）
     基于上一步得到的上一年度利润列，可按需新增计算字段：
  • 年度利润差值：= [Profit] - [上一年度利润]
  • 年度利润同比增长率：= if [上一年度利润] = null then null else ([Profit] - [上一年度利润])/[上一年度利润]，将该列格式设置为百分比即可。

结果校验参考 #

基于提供的样例数据，计算结果符合预期：

• 公司X的Shampoo产品：2020年利润30无上一年匹配值，2021年利润40匹配到上一年值30、差值10，2022年利润34匹配到上一年值40、差值-6
• 公司Y的Coffee产品：2018年利润25无上一年匹配值，2019年利润30匹配到上一年值25、差值5，2020年利润20匹配到上一年值30、差值-10
• 仅在单一年度出现的产品（如X公司2020年的Soap、Y公司2021年的Switch），上一年度利润列返回空值，无跨主体错配问题。

内容的提问来源于stack exchange，提问作者Smith Dwayne