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

andysky

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

---

1.假设数据见下表

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

andysky

公式一：

=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, 下载次数: 179)

2006-11-28 11:37 上传

点击文件名下载附件
[分享]关于函数排名的认知

gouweicao78

公式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编辑过]

cdyh2

不错，谢谢分享

chenhuifang

呵呵，我看不懂啊

星语心愿

在这儿作个记号。我转到我的博客了。谢谢。如果楼主不答应，我再删除了呀。

lumike

这么好的帖子,收下了

dj-1621

谢谢分享

aptwag-1

durtitdoo;igh'
jp

wf320722

又学了一招