Excel如何分组排序

电商运营 2 年前 0

之前有同学发了一个这个问题，如下图所示，左边是原始数据表，标有底色的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((B20)),\”是\”,\”\”)”

最后H列奖金，限制条件是A等级的前两名奖金300，B等级的前两名奖金200，C等级的前两名奖金100。这个用if函数嵌套就可以，当然还有更简洁的实现方法。看大家怎么想了。这里if函数嵌套公式是，“=IF(G2=\”是\”,IF(C2=\”A\”,300,IF(C2=\”B\”,200,IF(C2=\”C\”,100))),\”\”)”。