ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 22939|回复: 17

[分享] 中国式排名-针对并列情况 五种方法

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2013-8-3 11:05 | 显示全部楼层 |阅读模式
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
Excel中排名,大家习惯分为美式排名和中国式排名。  美式排名通常使用RANK函数,用法比较简单,可以参考文章:rank函数实例讲解
  什么是excel中国式排名呢?咱们中国人的习惯,比如无论有几个并列第3名,之后的排名仍应该是第4名,即并列排名不占用名次,这就是中国式排名。
  下面这个案例,也是IT部落窝论坛的excel中国式排名练习题,语文成绩有两个72分,并列第一名,随后的66分为第二名……在C2单元格写公式,完成中国式排名。
  中国式排名,其实就是对一个数在一组数据中排名第几的统计,重复数排名相同,比它小的最大数的排名只低一级。通常可以使用countif,frequency和SUMPRODUCT等函数结合实现。
  下面提供几种相关的中国式排名解法:
  中国式排名解法一:
  在C2单元格输入公式,=SUM(IF($B$2:$B$6>B2,1/COUNTIF($B$2:$B$6,$B$2:$B$6)))+1,然后按ctrl+shift+enter三键结束。下拉复制公式即可得出其余的排名。

  公式解释:
  COUNTIF($B$2:$B$6,$B$2:$B$6)部分:这是一个数组运算用法,它的运算过程是:
  COUNTIF($B$2:$B$6,B2)
  COUNTIF($B$2:$B$6,B3)
  COUNTIF($B$2:$B$6,B4)
  ……
  分别统计B2、B3、B4单元格在B2:B6区域中出现的次数。得到结果为:1,1,1,2,2。其中“1”代表此单元格中的内容在B2:B6区域中只出现一次,即没有重复;“2”代表此单元格中的内容在B2:B6区域里重复2次。这一步的操作,可以得到数据是否有重复和以及重复的次数。
  1/COUNTIF($B$2:$B$6,$B$2:$B$6)部分:
  在公式编辑栏选中这部分公式,按F9键查看运算结果为:{1;1;1;0.5;0.5}。
  IF($B$2:$B$6>B2,……)部分:
  IF第一参数:$B$2:$B$6>B2的结果是:{FALSE;TRUE;FALSE;TRUE;TRUE},意思是B2单元格中的内容分别和B2:B6区域内的各个单元格内容进行大小比较。
  “IF($B$2:$B$6>B2,1/COUNTIF($B$2:$B$6,$B$2:$B$6))”,这里IF省略了第三参数,因此当得到FALSE时,此时将返回结果“FALSE”,当得到TRUE时,此时将返回对应的结果,得到的结果是{FALSE;1;FALSE;0.5;0.5}。
  接着SUM函数对IF函数内的结果进行加总,得到结果“3”。为什么还要再加上“1”呢?原因是IF函数内的测试条件是“>”,对于“B2:B6区域”里的最大值“72”而言,得到的结果是{FALSE;FALSE;FALSE;FALSE;FALSE},那么SUM函数计算得到的值就是“0”,显然排名第0位,不符合常识,因此要额外加上“1”。
  这个中国式排名公式的核心部分就是:“1/COUNTIF($B$2:$B$6,$B$2:$B$6)”,目的是避免重复计算相同项。

  中国式排名解法二:
  C2单元格输入公式:=SUMPRODUCT(($B$2:$B$6>=B2)/COUNTIF($B$2:$B$6,$B$2:$B$6)),下拉复制即可。
  这个公式的思路也是不重复计数。具体的公式解释,请参看文章:http://www.ittribalwo.com/show.asp?id=1257

  中国式排名解法三:
  使用FREQUENCY 函数的数组解法完成。B2公式为:=SUM(--(FREQUENCY(B$2:B$6,IF(B$2:B$6>=B2,B$2:B$6))>0)),三键结束。

  中国式排名解法四:
  =SUMPRODUCT((B$2:B$6>B2)*(1/(COUNTIF(B$2:B$6,B$2:B$6))))+1

  中国式排名解法五:
  =SUM(--IF($B$2:$B$6>=B2,MATCH($B$2:$B$6,$B$2:$B$6,)=ROW($2:$6)-1))

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-8-3 11:08 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
嗯,分享好!!谢谢!!!

TA的精华主题

TA的得分主题

发表于 2013-8-3 11:11 | 显示全部楼层
谢谢,整理的很好,如果能把表格上传,让我们配合公式一起使用就能更好的学习了

TA的精华主题

TA的得分主题

发表于 2013-8-3 11:26 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
又出来个部落窝,感谢分享,支持一下     

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-8-3 14:35 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
dajiahaoxinku12 发表于 2013-8-3 11:26
又出来个部落窝,感谢分享,支持一下

谢谢鼓励,一定努力在这取经!!用2 3 4楼大侠致敬!!!

TA的精华主题

TA的得分主题

发表于 2013-8-3 14:38 | 显示全部楼层
取经人 发表于 2013-8-3 14:35
谢谢鼓励,一定努力在这取经!!用2 3 4楼大侠致敬!!!

兄弟,估计以后你要成名人了!你取来的东西都是绝招啊

TA的精华主题

TA的得分主题

发表于 2013-8-3 14:52 | 显示全部楼层
整理的好,方便学习。如果再来个练习文件就更好啦

TA的精华主题

TA的得分主题

发表于 2013-8-5 16:44 | 显示全部楼层
这个方法不错,谢谢楼主资源共享!!

TA的精华主题

TA的得分主题

发表于 2015-7-17 16:38 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2015-9-14 11:00 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
学习一下!!!!!
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-12-23 22:52 , Processed in 0.031889 second(s), 10 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表