龙空技术网

Excel各类排名,你是否知道?

安夏Anxia 117

前言:

如今大家对“排名法的计算公式”大约比较关怀,同学们都需要分析一些“排名法的计算公式”的相关资讯。那么小编在网摘上搜集了一些关于“排名法的计算公式””的相关内容,希望兄弟们能喜欢,各位老铁们一起来了解一下吧!

排名有很多种,中式排名,美式排名,小组排名等等。今天就来讲一下。

一、排序法

Excel排序会改变表格中数据的上下位置顺序,排名时一般不建议使用。

二、公式法

Excel原始数据位置不变的情况下,需要计算排名时,可使用Rank/Sumproduct/Countif等来进行排名。公式法并不会改变表格中数据的上下位置顺序。

(一)Rank函数-美式排名

Rank是最常用的排名函数,也是美式排名的函数。

美式排名:若有相同的值出现,对应的名次都会显示为同一个值。然后下一个名次会直接跳过中间存在的数值。(例:第一名,第二名,第二名,第四名)

功能:求某一个数值在某一区域内的排名。

语法:=Rank(number,ref,[order])

公式:=RANK(排名的单元格,排名区域,[排序方式])

排序方式:1为升序,0或省略为降序。

三个参数:

number单元格内必须为数字ref排名区域必须要绝对引用order可省略,省略为降序排列

实例一:按照同学成绩进行排名。

降序公式:=RANK(D2,$D$2:$D$8,0)

升序公式:=RANK(D2,$D$2:$D$8,1)

实例二:按照同学成绩进行多区域排名。

输入公式:=RANK(D2,($D$2:$D$8,$J$2:$J$8))

通用公式:=RANK(目标,(所属区域1,所属区域2),升序/降序)

【注意】第二个参数使用英文逗号","连接将两个区域连接起来。

实例三:按照同学成绩进行跨表排名。

输入公式:=RANK(D2,排名1:排名2!$D$2:$D$8)

注意事项:

1、只能对数字进行排序

2、Rank函数排名,若数值相同,则排名相同,后续排名则出现断裂,排名+1。

(二)Sumproduct函数-中式排名

中式排名:有相同的数字或并列名次,排名不会断开,而是连续的。(例:第一名,第二名,第二名,第三名)

1、全部排名

中式排名:Sumproduct+Countif 函数

公式一:=Sumproduct((目标区域>目标)*(1/Countif(目标区域,目标区域)))+1(降序)

=Sumproduct((目标>目标区域)*(1/Countif(目标区域,目标区域)))+1(升序)

公式二:=Sumproduct((目标区域>=目标)*(1/Countif(目标区域,目标区域)))

公式三:=Sumproduct((目标区域>目标)/Countif(目标区域,目标区域))+1 (降序)

=Sumproduct((目标>目标区域)/Countif(目标区域,目标区域))+1(升序)

公式四:=Sumproduct((目标区域>=目标)/Countif(目标区域,目标区域))

2、分组排名

(1)美式分组

按照班级进行排名:

=SUMPRODUCT(($B$2:$B$8=B2)*($D$2:$D$8>D2)*1)+1

=SUMPRODUCT(--($B$2:$B$8=B2)*($D$2:$D$8>D2))+1

通用公式:

公式一:=Sumproduct((条件区域=条件)*(目标区域>目标)*1)+1

公式二:=Sumproduct(--(条件区域=条件)*(目标区域>目标))+1

※ 文本数字,逻辑值转化为数值时,前面需要用--或N来转化。

(2)中式分组

按照班级进行排名:

=SUMPRODUCT(($B$2:$B$8=B2)*($D$2:$D$8>D2)/COUNTIFS($B$2:$B$8,$B$2:$B$8,$D$2:$D$8,$D$2:$D$8))+1

通用公式:

公式一:=SUMPRODUCT((条件区域=条件)*(排名区域>排名目标)/COUNTIFS(条件区域,条件区域,目标区域,目标区域))+1

公式二:=SUMPRODUCT((条件区域=条件)*(排名区域>=排名目标)/COUNTIFS(条件区域,条件区域,目标区域,目标区域))

(三)Countif函数

输入公式:=COUNTIF($D$2:$D$8,">"&D2)+1

美式排名&中式排名

美式排名:出现相同数据时,并列的数据也占用名次。{第一名,第二名,第二名,第四名}

中式排名:出现相同数据时,并列数据的不占用名次。{第一名,第二名,第二名,第三名}

标签: #排名法的计算公式