Excel如何分组排序
之前有同学发了一个这个问题，如下图所示，左边是原始数据表，标有底色的B/G/H列是要按照右表中的要求进行公式计算的。
如要求所示，B列排名是按照某片区下某等级之间的5月业绩环比分组排名，G列“是否各片区等级前两名”要求排除环比负值，H列“给予奖金”是根据等级及排名分配多少奖金，最后要得到的如下表：
排名
先来看排名，这个排名是一个典型的分组排名，思路有很多，我们说几种。
countifs
首先是countifs多条件计数，限定多个条件进行分组，再判断业绩是否大于当前值，这是用这个函数的思路。
在B2单元格输入公式“=COUNTIFS(C:C,C2,D:D,D2,F:F,">"&F2)”，这个公式有3个限制条件，C列里为A等级，D列里为东北片区，F列里大于当前值F2的有多少个，结果是3个，意思是A等级的东北片区里有3条记录的业绩环比大于-40
根据前面我们知道，B2这条记录的排名应该是4，因此要给这个公式后面加1，下拉以后得到如下结果。
Sumproduct
这是countifs多条件计数，还可以用sumproduct函数，这个函数的作用是返回相应的数组或乘积的和，参数就是一个一个的数组或区域。
在A2单元格输入公式“=SUMPRODUCT((2:25=C2)(2:25=D2)(2:25>F2))”，第一个区域2:25=C2返回的是一个Ture/False构成的数组区域，用来判断C2是否在2:25的组别中，同理2:25=D2判断D2是否在2:25也就是片区的组别里，2:25>F2是用来判断当前值F2是否大于所在分组的业绩环比值，最后得到的结果是3，意思是当前分组下，有3个人的业绩环比是大于-40%的。
同理，在这个公式后面加1，得到排名，结果同countifs一样，其实原理也差不多。
G列是否各片区前两名，要求排除业绩环比负值的，这个很简单，就用if函数判断就可以，注意这里还使用了and逻辑函数，意思是同时满足这两个条件。在G2单元格中输入公式“=IF(AND((B2<3),(F2>0)),"是","")”
最后H列奖金，限制条件是A等级的前两名奖金300，B等级的前两名奖金200，C等级的前两名奖金100。这个用if函数嵌套就可以，当然还有更简洁的实现方法。看大家怎么想了。这里if函数嵌套公式是，“=IF(G2="是",IF(C2="A",300,IF(C2="B",200,IF(C2="C",100))),"")”。
猜你喜欢:
@ 作者：可乐
@ 公众号/知乎专栏/头条/简书：可乐的数据分析之路
@加个人微信：data_cola，备注：进群，拉你入 可乐的数据分析群 和各行各业的小伙伴交流探讨数据分析相关内容