ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 Office知识技巧免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 5028|回复: 12

[分享]关于函数排名的认知

[复制链接]

TA的精华主题

TA的得分主题

发表于 2006-11-28 11:28 | 显示全部楼层 |阅读模式

***对于排名***。
大家知道
EXCEL中有一个专用函数RANK
但它是外国式的排名
不符合中国人的习惯
今天我给大家介绍一下中国式的排名公式

***对于排名***。
大家知道
EXCEL中有一个专用函数RANK
但它是外国式的排名
不符合中国人的习惯
今天我给大家介绍一下中国式的排名公式


1.假设数据见下表


[此贴子已经被sahelo于2006-12-23 8:49:47编辑过]

[分享]关于函数排名的认知

[分享]关于函数排名的认知

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-28 11:38 | 显示全部楼层

公式一:

=RANK(B2,$B$2:$B$21,0)

rank函数语法为RANK(number,ref,order),Number 为需要找到排位的数字。Ref  为数字列表数组或对数字列表的引用。ref 中的非数值型参数将被忽略。Order   为一数字,指明排位的方式。如果 order 为 0(零)或省略,Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。order 不为零,Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。 常规排名都用这种函数,

公式二:

=SUM(IF($B$2:$B$21<=B2,"",1/(COUNTIF($B$2:B$21,B$2:B$21))))+1

首先用IF函数排除大于目标值之范围,再用1/countif(data,data)对剩下范围求出不重复值个数,那么目标值则排名在这个"不重复值个数"之下一位,故后面有"+1".(提示:1/countif(data,data)是求不重复值的标准公式)

公式三:

=SUM(IF(ISNUMBER((IF(IF($B$2:$B$21<=B2,0,FREQUENCY($B$2:B$21,B$2:B$21))>0,1))),1))+1

本公式用三个IF嵌套,逐步排除小于目标值,求出大于目标值之个数,再+1即排名,关键函数——利用了FREQUENCY求出频率分布值.其中第一个IF作用是将小于目标值之范围频率分布值用0替代;第二个IF则求出频率分布值大于0之个数——等同于公式二中IF作用,第三个IF则排除其中的错误值,最后用SUM加总并+1求出最后排名

公式四:

=SUM(IF(IF($B$2:$B$21<=B2,0,MATCH($B$2:B$21,B$2:B$21,)=ROW($B$2:B$21)-1),1))+1

本公式用二个IF嵌套,也是逐步排除小于目标值之数据,再找出大于目标值之个数+1则为排名.但本公式关键在于MATCH()函数与ROW()函数.首先用IF排除掉小于目标值之数据,再用MATCH()=ROW()求出目标值在单元格之排位与所有行号是否相等,最后将求出的值的个数+1得到排名.

公式五:

=SUM(($B$2:$B$21>B2)*(MATCH($B$2:B$21,B$2:B$21,)=ROW($1:$20)))+1

此公式是公式四的简化(省掉两个IF),原理一致不再说明.

公式六:

=SUM(--ISNUMBER(IF((($B$2:$B$21>B2)*((FREQUENCY($B$2:B$21,B$2:B$21))>0)),1)))+1

此公式是公式三的简化(少了两个IF),原理一致不再说明.


bGjLyIoq.rar (108.26 KB, 下载次数: 174)

TA的精华主题

TA的得分主题

发表于 2006-11-28 11:58 | 显示全部楼层

公式2

=SUM(IF($B$2:$B$21>B2,1/COUNTIF($B$2:B$21,B$2:B$21)))+1——原来的Countif多了括号,“<=”改为“>”

公式4,改为:

=SUM(--IF(MATCH(B$2:B$21,B$2:B$21,0)=ROW(B$2:B$21)-1,B$2:B$21>B2))+1

公式6,改为:

=SUM(--(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+1  或者

=SUM(--(FREQUENCY(IF(B$2:B$21>B2,B$2:B$21),B$2:B$21)>0))+1

以上3个“不重复”个数统计的原理是:

1/Countif将每个数据出现的个数作为分母,则总和为1,比如100分出现2次,则Countif为2,1/Countif为1/2,2个1/2加起来就是1。前面再套上IF条件,求条件不重复值的个数。——注意,可能出现因Excel浮点运算造成误差。

特点:1、Coutif(Data,Data)函数第一参数必须是对单元格区域的引用,不能为内存数组。2、Data可以是多行多列

IF(Match=Row法,是利用Match精确查找每个数据在数据区第一次出现的位置,并与其对应的行号位置比较,是否一致,只有第一次出现的位置才会一致,所以以此计算不重复。

特点:1、Match(Data,Data,0),Data可以是内存数组和单元格区域的引用。2、Data必须是单行或单列

IF(Frequency法,利用Frequency对数值的分布产生比数据多1行的数组来进行,因此不能简单用(Frequency>0)*(B$2:B$21>B2)这种连乘法(行数不同),这也是楼主公式5要加ISNUMBER的原因。但如果用到IF(Frequency,B$2:B$21>B2)则因为后一个返回的区域本身就把Frequency多出来的那一行去掉了,不必再加Isnumber。

特点:1、Data可以是数组和单元格区域的引用。2、Data必须为数值,忽略文本。3、Data可以是多行多列。

[此贴子已经被作者于2006-11-28 13:26:22编辑过]

TA的精华主题

TA的得分主题

发表于 2006-11-28 12:49 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2006-12-21 20:26 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2007-6-26 18:31 | 显示全部楼层
在这儿作个记号。我转到我的博客了。谢谢。如果楼主不答应,我再删除了呀。

TA的精华主题

TA的得分主题

发表于 2007-7-18 21:46 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2007-7-18 21:52 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-4-15 13:20 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-9-27 08:31 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,高效办公专列,每天发车

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

GMT+8, 2020-6-1 07:24 , Processed in 0.073835 second(s), 13 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

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

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

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