返回目录:excel表格制作
数据分级排序的原理其实就是分级排名与加权:
如果就是左侧数据源生成右侧的分级排序,使用数据透视表就可以实现,数据透视表排序中有其他排序:
在紧凑布局的透视表中,需要将要排序的字段都设置一遍其他排序:数量降序排序
当然有的时候我们拿到的数据就是未排序的数据,也可以把分组求和的行去掉变成标准数据源的格式,然后再重复上面的步骤就可以实现分级排序。
公式法
分级排名然后将排名加权相加得到一个序号,然后再排序就可以了
我们用计数加一的排名方法,计算排名:
三级排名:
=(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
实现的方法与过程与公式法相近,分别筛选出来,排序,添加索引,然后三级的索引用合并查询放到源表中,加权求和,求和后的列排序:
然后删除不需要的列,加载结果就可以了:
推荐使用透视表或者Power Query来处理这个排序问题,透视表是从数据源整理做起,Power Query把所有的查询过程都隐藏起来,直接输出结果。这两种方法都比公式法要简单些。