我们用计数加一的排名方法,计算排名:
三级排名:
=(SUMPRODUCT((数据!$D2<数据!$D$2:$D$51)*(数据!$B$2:$B$51="")*1) 1)*(数据!$B2="")
=(SUMPRODUCT((数据!$D2<数据!$D$2:$D$51)*(数据!$C$2:$C$51="")*1) 1)*(数据!$C$2:$C$51="")*(数据!$B$2:$B$51<>"")
=(SUMPRODUCT((数据!$D2<数据!$D$2:$D$51)*(数据!$C$2:$C$51<>"")*1) 1)*(数据!$C$2:$C$51<>"")*(数据!$B$2:$B$51<>"")
然后向下填充省与市两级的排名,用SUMIFS:
最后加权求和:
为什么要省*10000,市*100,主要看总排名中有多少,2位数参与排名,那么为了分级准确,就要大于99,否则重叠,导致排名错乱。最后这一列升序排序就是我们要的结果了。
Power Query实现的方法与过程与公式法相近,分别筛选出来,排序,添加索引,然后三级的索引用合并查询放到源表中,加权求和,求和后的列排序: