ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[原创] EXCEL花式排名公式

[复制链接]

TA的精华主题

TA的得分主题

发表于 2017-8-17 14:55 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 libby_ye 于 2017-8-17 15:11 编辑

通常,报表都少不了排名,比如销售业绩排名、学生分数排名等等。今天分享一些EXCEL里花样百出的排名公式。

EXCEL里常规的排名函数RANK,它可以称为西式排名法,与之相对的是“中国式排名”。除此之外,还有限定条件的排名,有单个条件也有多个条件。

一、RANK
公式写法:=RANK(要排名的数字,一组数字,降序or升序)
公式说明:返回某数字在一列数字中相对于其他数值的大小排名。第3个参数若为0或忽略表示降序,非零值表示升序。

例1:A1的分数在年级的排名
=RANK(C2,$C$2:$C$10)
例1.jpg

二、SUMPRODUCT+COUNTIF
中国式排名:无论有几个并列第2名,之后的排名仍是第3名,即并列排名不占用名次。

例如:对数列{3,2,2,1}从大到小进行排名,按国际通行的惯例是{第一名,第二名,第二名,第四名},比如奥运会某场比赛出现并列银牌,则不发铜牌。而按中国式排名,结果是{第一名,第二名,第二名,第三名}

例2:A1的分数在年级按中国式排名
=SUMPRODUCT(($C$2:$C$10>C2)/COUNTIF($C$2:$C$10,$C$2:$C$10))+1
例2.jpg

公式解读:此例中SUMPRODUCT筛选出大于90的个数,COUNTIF统计各个分数出现的次数。
例22.jpg

三、数据透视表法
EXCEL 2010及以上版本可用数据透视表快递实现中国式排名,无需使用公式。
数透1.jpg
数透2.jpg


四、SUMPRODUCT条件排名
利用SUMPRODUCT多条件计数
=SUMPRODUCT((条件1)*(条件2)*(条件3)*…*(条件n))

例3:A1的分数在自己自己班里的排名
(PS:就算表中数据未按班级排序也同样能实现)
=SUMPRODUCT(($A$2:$A$10=A2)*($C$2:$C$10>C2))+1

例3.jpg


还有很多其他“花样”的排名方法,欢迎留言,共同进步^_^

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-8-25 16:55 | 显示全部楼层
本帖最后由 libby_ye 于 2017-8-26 16:25 编辑

(中国式排名数据透视表法补充个GIF详细步骤)

欢迎补充更多方法O(∩_∩)O

12楼Maredo“中国式排名”方法:
=SUM(--(FREQUENCY(C$2:C$10,IF(C$2:C$10>=C2,C$2:C$10))>0)),三键结束。


数据透视表法(中国式排名)GIF.zip

1.97 MB, 下载次数: 15

TA的精华主题

TA的得分主题

发表于 2017-8-17 15:06 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
厉害,顶礼膜拜中,很多还不会

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-8-17 15:15 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2017-8-17 15:27 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
谢谢分享!

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-8-17 16:25 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2017-8-24 09:19 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2017-8-24 09:32 | 显示全部楼层
第三种,使用数据透视表的方法2013版EXCEL好像不适用啊

TA的精华主题

TA的得分主题

发表于 2017-8-24 09:34 | 显示全部楼层
第二种方法的话,感觉用了sumproduct就没必要再用countif啦

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-8-25 15:44 | 显示全部楼层
晴天的小猪25 发表于 2017-8-24 09:34
第二种方法的话,感觉用了sumproduct就没必要再用countif啦

可以分享一下你的公式么?中国式排名哈

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-8-25 15:44 | 显示全部楼层
晴天的小猪25 发表于 2017-8-24 09:32
第三种,使用数据透视表的方法2013版EXCEL好像不适用啊

可以哦,我用的也是2013版
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-29 03:22 , Processed in 0.051395 second(s), 13 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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