分组排名与中国式排名

一、排名的两种套路：

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
    中式

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

四、练习：

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