经典公式解析-中式排名!

今天我们来说点谈一下排名的问题,把经典的中式排名公式剖析一下。
我们先来看一下中式排名和美式排名的区别:
简单来说,就是美式相同的排名会占位,比如这里的美式没有第三名,
直接第四名,也就是说,他没有真正的并列的意思
中式排名,并列第二,后面应该是第三名,并列不占位。
美式排名1:=RANK(B2,$B$2:$B$7)
美式排名2:=SUMPRODUCT(N($B$2:$B$7>B2))+1

中式排名:  =SUMPRODUCT(($B$2:$B$7>=B2)/(COUNTIF($B$2:$B$7,$B$2:$B$7)))

美式2

你可能会发现,美式排名2的公式跟中式比较,
中国排名多出了一个COUNTIF其他基本一样。
具体我们还是来庖丁解牛:
解析1:$B$2:$B$7>$B2 到底是什么意思?
B2=3,B3=4 我们使用B3>B2,大家肯定可以明白,就是他们两个比较一下大小。
那么这里只是把B3 替换成了换成了多个单元格,
结果也由一个结果变成了多个结果。
想要知道自己的排名,必须和参与排名的全部数据比较一次,看看有多少比自己大的,也就是多少是TRUE的,来确定自己的排名。
拿75举例,其他同理
按下F9,我们可以看到结果是多个值
这里,75是最大的,所以只有他自己一个返回TRUE,其他都是FALSE
{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
普通公式看一下,就可以看出,我们是把每个数值都和75比较了一次
这个是针对75的,但是我们其实每个数值都要和$B$2:$B$7中的全部数据比较一下大小,才能知道自己的位置
有多少个TRUE,就说明自己排名第几
在EXCEL中,参与四则运算都会让TRUE和FALSE 进行转换
TRUE转为1,FALSE转为0,那么我们只需要SUM一下,
看看结果就知道排名了
到这里你可能就已经明白美式排名了
解析2:COUNTIF($B$2:$B$7,$B$2:$B$7) 何解?
COUNTIF($B$2:$B$7,$B$2:$B$7)难点在于是数组公式,很多新手搞不懂是怎么计算,其实比较简单,可以拆解成
COUNTIF($B$2:$B$7,$B$2)=1
COUNTIF($B$2:$B$7,$B$3)=2
COUNTIF($B$2:$B$7,$B$4)=2
……
COUNTIF($B$2:$B$7,$B$7)=1
也就是我们把一般的一次性把每个值都计算了一遍,自己在数据中一共出现了几次,结果也是对应的6个结果,对应数组结果如下
也即是71出现了两次,其他都是1次
和下方的普通公式无异,只是我们每个单元格都需要这样去结算所以使用数组
结合解析1和解析2,我们来看看。
75的第一名到底是怎么计算出来的

TRUE是1,FALSE是0,所以结果合计1,也就是第一名
66的第三名是怎么计算出来的?
计算都是一样的,我们就不讲了,看一下比较大小,有3个大于等于66的
同时可以看到有两个71在其中,也就变成了0.5+0.5 =1,没有占据第三名,合计也就是3,排名第三
解析3:SUMPRODUCT的用处?
我们通过小案例来看,1-6分别乘以10,再求和
SUMPRODUCT的作用就是先让他们分别对应相乘,然后再相加
A1:A6*B1:B6 结算结果:{10;20;30;40;50;60}
=SUMPRODUCT({10;20;30;40;50;60}) = 210
最后我们要计算每个数据的排名,就需要把它装到一起
($B$2:$B$7>=B2)/(COUNTIF($B$2:$B$7,$B$2:$B$7))
如果相同,我们就把她变成多少分之一,保证合计是1,不多占位
最后我们使用SUMPRODUCT计算出来的多组数据进行求和,得到的结果就是排名

小结

数组函数,对于新手来说,比较难以理解,但确实函数水平的分水岭,如果想成为一名函数高手,那么数组这关必须过,排名这个公式本身并不复杂,我们只要学会去分解,一切问题就迎刃而解
(0)

相关推荐

  • countif求美式排名和中式排名

    关于countif的基础用法,之前已经说过了,如果不清楚请搜索历史文章,输入关键字countif进行搜索.今天说一下用countif解决排名的问题,排名有美式排名和中式排名. -01- 具体应用 1. ...

  • Office 365函数新世界 :计算不重复数

    数据去重复是工作表函数长久以来的痛点之一,为了实现这个功能,前辈们煞费苦心,钻研各种套路,但最终成型的公式要么复杂要么效率低下,所以一旦有人询问大量数据动态去重复的问题,会函数的那人往往脸一拉,手一抬 ...

  • 手把手教你,学会排名

    中国式排名 使用RANK类函数排名时,如果出现并列的情况,并列者将占用名次,因此会导致部分名次出现空缺.而按 照中国人习惯的排名计算方法,无论有几个并列第1名,之后的排名仍然是第2名,即并列排名不占用 ...

  • 给跪了!被排名折磨哭的我,真后悔没早点学会这4个函数……

    本文作者:小花 本文审核:小爽 本文编辑:竺兰 生活中处处有排名,成绩要排名,公司要排名,连个人绩效也要排名. 有人甚至说,优胜劣汰法则的最好应用就是排名. 今日,小花就带大家一起来学习,Excel ...

  • EXCEL经典公式解析-中式排名!

    今天我们来说点谈一下排名的问题,把经典的中式排名公式剖析一下. 我们先来看一下中式排名和美式排名的区别: 简单来说,就是美式相同的排名会占位,比如这里的美式没有第三名, 直接第四名,也就是说,他没有真 ...

  • 5年经典!解析本田实用通勤踏板车e影110,电喷供油,油耗不到2升

    在日常生活当中,踏板摩托车一直发挥着非常重要的作用,不仅可以满足日常通勤代步.上街买菜,还可以解决汽车拥堵和停车位的问题.随着经济实力的提升,国内的踏板摩托车车型种类也是越来越多. 在2015年的时候 ...

  • 中考数学几何模型之【半角模型】经典例题解析

    定义 半角模型是指:从正方形的一个顶点引出夹角为45°的两条射线,并连结它们与该顶点的两对边的交点构成的基本平面几何模型. 由于两射线的夹角是正方形一个内角的一半,故名半角模型,又称"角含半 ...

  • 初中数学:相似三角形13大知识点 6大常考经典例题解析

    初中数学:相似三角形13大知识点 6大常考经典例题解析

  • APEC曝光公式解析

    APEC曝光公式解析

  • 900句最美古诗词经典名句解析(二)

    101.寒雪梅中尽,春风柳上归 ○唐·李白<宫中行乐词八首> 102.东风随春归,发我枝上花 ○唐·李白<落日忆山中> 103.繁枝容易纷纷落,嫩蕊商量细细开 ○唐·杜甫< ...

  • 900句最美古诗词经典名句解析(三)

    201.斜阳外,寒鸦数点,流水绕孤村 斜阳:夕阳.寒鸦:寒风中的飞鸦. ○宋·秦观<满庭芳> 202.牧童归去横牛背,短笛无腔信口吹 横:横卧.腔:调. ○宋·雷震<村晚>:' ...

  • 900句最美古诗词经典名句解析(四)

    301.爱人者,人恒爱之:敬人者,人恒敬之 恒:经常. ○<孟子·离娄下> 302.人皆可以为尧舜 尧舜:传说中的上古两位圣君,此处指圣人.贤人. ○<孟子·告子下> 303. ...

  • 900句最美古诗词经典名句解析(五)

    401.喜怒不形于色 不形于色:不在脸色上表现出来. ○<北齐书·王琳传> 402.怒发冲冠,凭栏处,潇潇雨歇 凭栏处:倚栏而处的时候.潇潇:雨声. ○宋·岳飞<满江红> 40 ...