二维码 购物车
部落窝在线教育欢迎您!

在excel中,按条件进行排名,竟是如此简单!

 

作者:老菜鸟来源:部落窝教育发布时间:2019-10-15 15:40:02点击:650

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!说到将excel中的数据进行排名,大家首先想到就是rank函数,但如果说要按条件对数据进行排名呢?小伙伴们是不是一下子就蒙圈了,似乎还没有听说过按条件进行排名的函数。那么今天,老菜鸟就给大家分享一个在excel中按条件进行排名的公式套路,一起来看看吧!

 

Excel的函数中,有按条件求和的SUMIF,有按条件求平均值的AVERAGEIF,也有按条件计数的COUNTIF,最新版本中甚至有了按条件求最大值的MAXIFS函数和按条件求最小值的MINIFS函数。可是唯独没有可以按条件排名次的函数。

 

但是按条件排名次这类问题平时又的确会遇到,例如下面这个问题就是其中的一类典型代表:

 

 

我们都知道使用RANK函数可以得到一个数字在一组数字中的排名,在这个例子中的总排名就是用了公式=RANK(C2,$C$2:$C$19)得到的。

 

但是如果要得到每个门店在区域内的销售排名该怎么办,难道要在每一个区域中分别使用RANK函数进行排名吗?

 

虽然这也是一个思路,但是效率之低可想而知,其实在Excel的函数中,是有一个可以实现按条件排名次的函数,它就是SUMPRODUCT

 

在正式介绍按条件排名次的公式套路之前,让我们先来理一理按条件排名的运算原理。

 

10004这个门店为例,区域内排名是2,总排名是10,如图所示:

 

 

它的区域排名之所以是2,很容易理解,因为在同一个销售区域(条件)中,只有六个数,在这六个数字中,大于56.55的只有1个数就是79.72,因此它在区域内的排名就是2

 

其他名次的计算原理也是一样的,这样想来,实现按条件排名其实包含了两个过程:条件的判断和大小的判断。

 

把这两个过程用公式写出来就是:$A$2:$A$19=A2$C$2:$C$19>C2,可以结合实例来理解这两部分。

 

首先看第一个,$A$2:$A$19=A2会得到一组逻辑值:

 

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

 

 

从这个结果中可以看出,与要统计的门店在同一个区域的数据都是TRUE

 

$C$2:$C$19>C2同样也会得到一组逻辑值:

 

{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

 

 

这个结果表示销售额大于要统计门店时也会得到TRUE

 

现在的问题是如何将这两个部分合并起来,因为这是对一个数据同时进行的两个判断,所以将两组逻辑值相乘,来看看得到了什么结果:

 

 

图中的这一组由01构成的数据,是($A$2:$A$19=A2)*($C$2:$C$19>C2)计算得到的结果,表示10001这个门店所在的区域中,销售额高于14.46的有4个门店(41),只需要对这个结果求和,基本上就实现了排名的目的,因此公式套路也就有了:

 

=SUMPRODUCT(($A$2:$A$19=A2)*($C$2:$C$19>C2))

 

 

不过这样得到的结果有个问题,名次是从0开始的,要解决也很简单,有两个方法。

 

方法1:直接在公式后加1,结果如图所示。

 

 

方法2:将大于号改成大于等于,结果如图所示。

 

 

这两个方法,通常情况下并没有什么区别,使用哪个公式都可以。

 

以上是针对一个条件进行排名的公式,如果条件是两个或者更多,将公式套路进行扩展就行:

 

=SUMPRODUCT((条件区域1=条件1)* (条件区域2=条件2)* (数据区域>数据))

 

具体示例就不列举了,相信大家理解了公式的原理以后,结合具体问题去自己套用是完全没问题的。

 

本文配套的练习课件请加入QQ群:109723835下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

数据透视表搞定excel排名数据透视表送温暖来了:嘿,鼠标拖两下一次搞定业绩统计和排名!

多条件排名多条件查找排名第一人的方案等你来完善!

中国式排名统计中国式排名的三种方法,一定有一个适合你