分组排名与中国式排名

一、排名的两种套路:

1、排序后加索引

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    排序 = Table.Sort(源,{"金额", 1}),
    索引 = Table.AddIndexColumn(排序, "排名", 1, 1)
in
    索引

非常简单,动动小手鼠标点点就出来了,完全不需要自己写公式。但问题是经过排序后已经不是原来的顺序了,如果想还原成原来的顺序实现上图的效果,就需要在排序前后分别加一次索引,最后按照排序前的索引升序排列恢复原来的顺序,比较繁琐,那么看第二种方法。

2、筛选后计数+1

打个比方,上图中第1行的9694,筛选出表中所有>9694的行,只剩下9911比它大,计数=1,再+1即为排名。这个套路并不陌生,在工作表函数中的sumproduct和DAX中的earlier都有类似的用法。
先来个简单的写法:

let
    rank = (x)=>Table.RowCount(Table.SelectRows(源,each [金额]>x))+1,
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    排名 = Table.AddColumn(源, "排名", each rank([金额]))
in
    排名

先创建一个自定义函数,筛选表中>x变量的行,然后对行计数再+1得到排名。创建自定义函数和引用自定义函数这两步可以合起来写成一步:

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    排名 = Table.AddColumn(源, "排名", each Table.RowCount(Table.SelectRows(源,(x)=>x[金额]>[金额]))+1)
in
    排名

此方法的计算原理是逐行扫描,筛选出比它大的行然后计数,所以在数据量非常大的情况下效率会比较慢,但是比起第一种方法会更灵活,能够满足更加复杂的需求。
 

二、分组排名:


和刚才不同的是,现在要求根据"国家"和"品牌"两个条件来对各自组内进行排名。

1、分组后排序加索引

虽然多了个条件但本质还是一样,分组排名,那就先分组再排名嘛,但同样会破坏原来的顺序:

let
    源= Excel.CurrentWorkbook(){[Name="表2"]}[Content],
    分组 = Table.Group(源, {"国家", "品牌"}, {"a", each Table.AddIndexColumn(Table.Sort(_,{"金额",1}),"排名",1,1)}),
    展开 = Table.ExpandTableColumn(分组, "a", {"金额", "排名"})
in
    展开

2、筛选后计数+1

现在就会发现,这种方法在遇到多条件时逻辑会更加清晰,管你有多少个条件往后加就是了:

let
    源= Excel.CurrentWorkbook(){[Name="表2"]}[Content],
    排名 = Table.AddColumn(源, "排名", each Table.RowCount(Table.SelectRows(源,(x)=>x[国家]=[国家] and x[品牌]=[品牌] and x[金额]>[金额]))+1)
in
    排名

 

三、中国式排名:


所谓中国式排名,和前面的美式排名的区别主要在重复值上。比如一次考试中前三名分数分别为98,98,96,按照美式排名应该分别为1,1,3,但是按中国人的习惯排名都是连续的,比96大的只有98,所以中国式排名就是1,1,2。
按照前面介绍的第一种方法的话,就是先去重,然后排序加索引,再合并查询。但还是比较繁琐,我们看另二种方法:

let
    源 = Excel.CurrentWorkbook(){[Name="表3"]}[Content],
    中式 = Table.AddColumn(源, "中式", each Table.RowCount(Table.SelectRows(Table.Distinct(源),(x)=>x[金额]>[金额]))+1)
in
    中式

实际上还是一个意思,只是对筛选表多进行了一个去重的预处理。
 

四、练习:

以上介绍了分组排名、中国式排名的方法,那么请结合两者,写出分组的中国式排名,附件在下方。

附件

3 Replies to “分组排名与中国式排名”

  1. let
    源 = Excel.CurrentWorkbook(){[Name="表4"]}[Content],
    分组中国式排名 = Table.AddColumn(源, "排名", each Table.RowCount(Table.SelectRows(Table.Distinct(源),(a)=>_[国家]=a[国家] and _[品牌]=a[品牌] and _[金额]>a[金额]))+1)
    in
    分组中国式排名
    我本来是先先的分组,然后再处理,框架感觉没问题,但each改成的变量会很多也非常绕,无奈又尝试先新建列再写 ,感觉完全没压力了

  2. 1、在无重复数据情况下,加索引效率超高,10万行数据秒刷新, Table.SelectRows()就慢了很多
    2、在有重复数据情况下,如果加索引式的美国排名,感觉比较难处理,思维比较混乱

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注