如何用Power Query对表格分组数据进行高效密集排名

可视化操作步骤 #

1. 将原始数据导入Power Query编辑器：点击「数据」选项卡→「从表格/区域」，选中目标数据表格。
2. 按班级分组：选中「班级」列，点击「转换」选项卡→「分组依据」，设置规则：
   • 分组依据：班级
   • 新列名：分组数据
   • 操作：所有行
3. 生成组内密集排名：点击分组数据列右侧的展开箭头，选择「添加自定义列」，输入公式：

   Table.AddRankColumn([分组数据], "临时排名", {"分数", Order.Descending}, RankKind.Dense)
   

4. 展开数据：点击新生成的自定义列的展开箭头，勾选「分数」「编号」「临时排名」列。
5. 格式化排名文本：添加自定义列，输入公式：

   "第" & Text.From([临时排名]) & "名"
   

6. 清理列：重命名该自定义列为「排名」，删除临时排名和分组数据列，调整列顺序后关闭并上载数据。

直接编写M代码实现 #

若习惯直接操作M代码，可替换Power Query编辑器内的代码为以下内容（注意将源替换为你的实际数据源）：

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    按班级分组 = Table.Group(源, {"班级"}, {{"分组数据", each _, type table [分数=number, 编号=number, 班级=text, 排名=text]}}),
    添加密集排名 = Table.AddColumn(按班级分组, "带排名数据", each Table.AddRankColumn([分组数据], "临时排名", {"分数", Order.Descending}, RankKind.Dense)),
    展开结果 = Table.ExpandTableColumn(添加密集排名, "带排名数据", {"分数", "编号", "临时排名"}, {"分数", "编号", "临时排名"}),
    格式化排名 = Table.AddColumn(展开结果, "排名", each "第" & Text.From([临时排名]) & "名"),
    清理冗余列 = Table.RemoveColumns(格式化排名,{"临时排名", "分组数据"}),
    调整列顺序 = Table.ReorderColumns(清理冗余列,{"分数", "编号", "班级", "排名"})
in
    调整列顺序

关键说明 #

• Table.AddRankColumn函数搭配RankKind.Dense参数是原生的密集排名实现，无需手动去重或循环计算，内存占用远低于第三方繁琐方法
• {"分数", Order.Descending}参数确保分数越高排名越靠前（第1名对应最高分）
• 分组操作保证排名仅在对应班级内部生效，不会跨组干扰

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