如何将Excel COUNTIFS公式转换为Power Query实现以提升计算性能

实现逻辑说明 #

原Excel公式性能差的核心原因是逐行扫描全列统计，Power Query中可以通过按客户分组预统计消费区间的方式实现相同逻辑，计算效率提升非常明显，同时支持本月、上月消费标记的批量生成。

操作步骤 #

• 提取基准日期参数：读取Sheet1中B2、B3的日期，计算出本月、上月的起止日期区间
• 导入消费记录数据源，确保Date列格式为日期类型
• 按CustomerID分组，统计每个客户是否存在落在本月、上月区间的消费记录
• 将统计结果合并回原消费表，转换为1/0的标记值

完整M代码示例 #

let
    // 1. 读取基准日期并计算区间
    取Sheet1数据 = Excel.CurrentWorkbook(){[Name="Sheet1"]}[Content],
    基准起始日期 = Date.From(取Sheet1数据{1}[Column2]), // B2单元格对应行索引1，列索引2
    基准结束日期 = Date.From(取Sheet1数据{2}[Column2]), // B3单元格对应行索引2，列索引2
    本月起始 = Date.StartOfMonth(基准起始日期),
    本月结束 = Date.EndOfMonth(基准结束日期),
    上月起始 = Date.AddMonths(本月起始, -1),
    上月结束 = Date.AddMonths(本月结束, -1),

    // 2. 导入消费记录表（替换为你自己的表名即可）
    消费记录 = Excel.CurrentWorkbook(){[Name="你的消费表名称"]}[Content],
    转日期格式 = Table.TransformColumnTypes(消费记录,{{"Date", type date}}),

    // 3. 按客户ID分组统计消费情况
    客户消费统计 = Table.Group(转日期格式, {"CustomerID"}, {
        ("本月是否消费", each List.AnyTrue(List.Transform([Date], (d) => d >= 本月起始 and d <= 本月结束)), type logical),
        ("上月是否消费", each List.AnyTrue(List.Transform([Date], (d) => d >= 上月起始 and d <= 上月结束)), type logical)
    }),

    // 4. 合并统计结果到原表，生成最终标记
    合并统计 = Table.NestedJoin(转日期格式, {"CustomerID"}, 客户消费统计, {"CustomerID"}, "统计项", JoinKind.LeftOuter),
    展开统计项 = Table.ExpandTableColumn(合并统计, "统计项", {"本月是否消费", "上月是否消费"}, {"本月是否消费", "上月是否消费"}),
    生成本月标记 = Table.AddColumn(展开统计项, "ThisMonth", each if [本月是否消费] then 1 else 0, Int64.Type),
    生成上月标记 = Table.AddColumn(生成本月标记, "LastMonth", each if [上月是否消费] then 1 else 0, Int64.Type)
in
    生成上月标记

验证说明 #

按照你给出的示例数据，基准日期设为2021年9月时，生成的ThisMonth和LastMonth结果和你提供的示例完全匹配。

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