首发于 office高效率操作
excel函数技巧:看看按条件排名要如何进行?

excel函数技巧:看看按条件排名要如何进行?

编按:哈喽,大家好!说到将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。

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



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

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



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

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



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



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

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

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

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


****部落窝教育-SUMPRODUCT函数应用技巧****

原创:老菜鸟/部落窝教育(未经同意,请勿转载)

更多教程:部落窝教育( itblw.com

微信公众号:exceljiaocheng

天下网TXWEB十堰seo按天扣费报价榆林营销网站建设报价伊春市网页设计价格钦州市网站开发报价广元市营销网站建设临汾网页设计多少钱阳泉网站搭建哪家专业日照seo按天计费多少钱宣城seo排名哪家专业漳州市网站开发报价云浮网站开发价格遂宁市网页制作哪家好营口阿里店铺运营推荐平凉网络推广哪家专业咸阳网站开发哪家好合肥网页制作推荐台州seo排名报价白银seo按天扣费濮阳市关键词排名哪家好汕头市seo优化公司山南市企业网站改版珠海网页制作哪家专业晋城优化价格福州网站设计哪家专业肇庆市营销网站建设哪家好大庆市网站开发哪家专业包头市品牌网站设计多少钱邯郸市网站定制公司贵港市网络推广价格阳江市企业网站设计报价香港通过《维护国家安全条例》两大学生合买彩票中奖一人不认账让美丽中国“从细节出发”19岁小伙救下5人后溺亡 多方发声卫健委通报少年有偿捐血浆16次猝死汪小菲曝离婚始末何赛飞追着代拍打雅江山火三名扑火人员牺牲系谣言男子被猫抓伤后确诊“猫抓病”周杰伦一审败诉网易中国拥有亿元资产的家庭达13.3万户315晚会后胖东来又人满为患了高校汽车撞人致3死16伤 司机系学生张家界的山上“长”满了韩国人?张立群任西安交通大学校长手机成瘾是影响睡眠质量重要因素网友洛杉矶偶遇贾玲“重生之我在北大当嫡校长”单亲妈妈陷入热恋 14岁儿子报警倪萍分享减重40斤方法杨倩无缘巴黎奥运考生莫言也上北大硕士复试名单了许家印被限制高消费奥巴马现身唐宁街 黑色着装引猜测专访95后高颜值猪保姆男孩8年未见母亲被告知被遗忘七年后宇文玥被薅头发捞上岸郑州一火锅店爆改成麻辣烫店西双版纳热带植物园回应蜉蝣大爆发沉迷短剧的人就像掉进了杀猪盘当地回应沈阳致3死车祸车主疑毒驾开除党籍5年后 原水城县长再被查凯特王妃现身!外出购物视频曝光初中生遭15人围殴自卫刺伤3人判无罪事业单位女子向同事水杯投不明物质男子被流浪猫绊倒 投喂者赔24万外国人感慨凌晨的中国很安全路边卖淀粉肠阿姨主动出示声明书胖东来员工每周单休无小长假王树国卸任西安交大校长 师生送别小米汽车超级工厂正式揭幕黑马情侣提车了妈妈回应孩子在校撞护栏坠楼校方回应护栏损坏小学生课间坠楼房客欠租失踪 房东直发愁专家建议不必谈骨泥色变老人退休金被冒领16年 金额超20万西藏招商引资投资者子女可当地高考特朗普无法缴纳4.54亿美元罚金浙江一高校内汽车冲撞行人 多人受伤

天下网TXWEB XML地图 TXT地图 虚拟主机 SEO 网站制作 网站优化