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((C2:C25=C2)(D2:D25=D2)(F2:F25>F2))”，第一个区域C2:C25=C2返回的是一个Ture/False构成的数组区域，用来判断C2是否在C2:C25的组别中，同理D2:D25=D2判断D2是否在D2:D25也就是片区的组别里，F2:F25>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))),"")”。
更多相关文章
- 为什么说 Python 内置函数并不是万能的？
- 给你的Excel增加正则处理函数，简直如虎添翼
- Python 之父为什么嫌弃 lambda 匿名函数？
- Python 函数为什么会默认返回 None？
- Python 为什么没有 main 函数？为什么我不推荐写 main 函数？
- Excel公式太复杂？我花一晚上做了个格式化工具
- 学编程这么久，还傻傻分不清什么是方法（method），什么是函数（function）？
- 秒懂！图解四个实用的Pandas函数！
- 一个真实问题，搞定三个冷门pandas函数