本节要点:Power Query-中国式排名
何谓“中国式排名”呢?
在排名计算方法中,中国人的习惯是,无论有几个并列第2名,之后的排名仍应该是第3名,即并列排名不占用名次。
例如:对数列{3,2,2,1}从大到小进行排名,按国际通行的惯例是{第一名,第二名,第二名,第四名},
比如奥运会某场比赛出现并列银牌,则不发铜牌,
而按中国式排名,结果是{第一名,第二名,第二名,第三名}。
下面通过一个案例来简单的说明如何通过Power Query来做这个“中国式排名”。
从上图可以看出,我们想要的结果是库里第一名,陶波依和张大杰是并列第二名,而休闲海则是第三名(要是美式排名,则是第四名)。
我们首先来分析一下,如何进行简单快速的对比,得出名次。
我们首先将所有数据列的数据复制出来,删除重复行。
然后依次将每一行的数据同上面的结果相比较。
如果小于等于上面的,则为“TRUE”,否则为“FALSE”。
最后统计“TRUE”的数量,则为名次。
例如上图中的休闲海小朋友,统计出来三个“TRUE”,则为第三名。
下面我们就在Power Query里面实现这个逻辑。
准备好数据,上面一个表“排名”则是数据源,下面一个表“不重复”则是根据数据源删除了重复项的表。
下面要在“排名”表里面进行扩展,每一行都要扩展8行记录,为什么呢?
因为上面不重复的有8条记录啊,哈哈!
这里用之前学到的扩展列技术。
但是这里有一个问题,假如我们的数据在增加,要是有10个不重复怎么办呢,还是得需要函数来动态辅助。
复制这个“值计数”的代码,进行替换。
“{1..List.NonNullCount(不重复[分数])}”
下面如何将“不重复”表里面的记录挪到“排名”表里面进行比较呢?这个简单了,其实就是在“不重复”表里面加一列索引,然后合并即可。
我们这里有一个小窍门,就是在“排名”表里面加一列索引列,“从1”开始的索引,这样一来,这个表的顺序就不容易被打乱。
展开分数一列即可,开始进行比较。
下面我们对于上面的结果做一个计数就可以得到排名了。
为了一步到位,我们可以对“分数”列取平均值,依旧是原来的分数,下面统计1的个数,即名次。
很不幸,发生了错误。
Expression.Error: 无法将运算符 - 应用于类型 Text 和 Text。
详细信息:
Operator=-
Left=1
Right=1
这是由于我们“求和'一列是文本类型,改成数值型即可。
这样就可以了,然后改一下前后缀。
上载到当前工作簿,为了不在刷新的时候自动调整格式,建议大家在属性里面去掉”调整列宽“。
新增一个小家伙,我们看看名次会不会进行刷新?
妥妥的啊,中国式排名!