返回目录:excel表格制作
结果是实现了功能要求,但是百有点复杂,度
增加一列辅助列F,用于分数保存。
红色字体要输入公知式:
F2 =LARGE(B:B,1)
F3 =LARGE(IF(B:B=F2,0,B:B),1)
F4 =LARGE(IF((B:B=F2)+(B:B=F3),0,B:B),1)
G2 =INDEX($A:$A,SMALL(IF($B:$B=$F2,ROW($B:$B),1048576),COLUMN(A1)))
然后右拉道下拉
这些公式都是数组内公式,都要同时容按CTRL+SHIFT+ENTER
F6 = LARGE(C:C,1)
F7 = LARGE(IF(C:C=F6,0,C:C),1)
F8 = LARGE(IF((C:C=F6)+(C:C=F7),0,C:C),1)
G6 =INDEX($A:$A,SMALL(IF($C:$C=$F6,ROW($C:$C),1048576),COLUMN(A5)))
然后右拉下拉
这些公式都是数组公式,都要同时按CTRL+SHIFT+ENTER
在Excel利用Rank可以对数字进行排名,但是对于重复的数字会使用相同的序号却分别占用不同copy的排名位置;而实际中往往需要的是相同数字的排名相同,且重复值排名后不影响后续数百字的排名。
方法1、在Excel帮助中提供了RanK的说明,以及对这个情况的处理,如图;但是方法比较麻烦,很少用到;现在要讲的是利用方法2来实现。
方法2、可以通过下面步骤来实现重复值排度名:
通用的公式是:
=SUMPRODUCT ( (排位区域>某个单元格)/COUNTIF(排位区域,排位区域))+1
公式中,排位区域 是一样的,相当问于RANK( number , ref , [order] )中的ref;
某个单元格 相当于RANK( number , ref , [order] )中的number。
如图:在A2单元格输入
=SUMPRODUCT(($A$2:$A$10>A2)/COUNTIF($A$2:$A$10,$A$2:$A$10))+1,回车。鼠标移动到A2单元格右下角,出现黑色小十字后,点击拖动一答直往下拉填充公式至A10,就可以完成排名了。
1.先将A列按升zhidao序或降序排序
2.B:C列输入公式如下
B2输入公式
=TEXT(SUMPRODUCT(1/COUNTIF(A2:$A$2,A2:$A$2)),"000")
C2输入公式
=COUNTIF(B2:$B$2,B2)
选择回B2:C2单元格,按住填充答柄往下拉(或双击填充柄)
这样写可以说没问题,rank第三参数可以省略(默认就是百0按照降序排列,如果是1按升序排列)。但是要注意的一点是,排名中外有别,所度以根据你个人需求是需要哪一种排序方式
具体点说就是,一般西方排名在出现相同分数时要占用名次,比如说共5个人中出现2个第三问名,第2个第三名占用第四名的名次,最后5个人的名次依次为12335;而一般中式排名在出现相同分数时则不占用名次,还是同样的例子,共5个人中出现2个第答三名,第2个第三名不占用第四名的名次,回最后5个人的名次依次为12334
西式排名答函数rank
假设A1:A100是源数据区域,B列为名次,=RANK(H24,$H$24:$H$39),向下拖动填充
西式排名函数countif
假设A1:A100是源数据区域,B列为名次,=COUNTIF($H$24:$H$39,">"&H24)+1,向下拖动填充
中式排名函数sumproduct+countif
假设A1:A100是源数据区域,B列为名次,=SUMPRODUCT(($H$24:$H$39>H24)*(1/(COUNTIF($H$24:$H$39,$H$24:$H$39))))+1,向下拖动填充