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

猜你喜欢:

学习SQL：MySQL必知必会

如何处理偏态数据？

数据分析应关注AARRR模型的哪些指标

泰坦尼克号数据分析

深入浅出数据分析

@ 作者：可乐
@ 公众号/知乎专栏/头条/简书：可乐的数据分析之路
@加个人微信：data_cola，备注：进群，拉你入 可乐的数据分析群 和各行各业的小伙伴交流探讨数据分析相关内容