您的位置: 首页 > EXCEL技巧 > Excel函数 >

对多个相同数值返回平均排位的公式

时间:2013-12-13 整理:docExcel.net

在Excel中对区域中的数值进行排位时要用到RANK函数,RANK函数对于并列排位的处理方法是取其最小位次。例如对于一列数值“9,2,1,2,6,2,7,3,7”中有两个“7”,按照从大到小的顺序排位时这两个数都返回“2”,即并列第二位,同时第三大的数字“6”返回“4”。有时可能不需要对多个相同数值进行这样的排位,而是要取其平均排位。例如上列数值中的“7”所占的位次为“2”和“3”(因为比“7”小的最大数“6”的位次为“4”),取其平均值为“(2+3)/2=2.5”,即两个相同的数值“7”的排位都为“2.5”。又如该数列中包含3个“2”,其所占的位次为“6”、“7”和“8”,则数值“2”的排位为“(6+7+8)/3=7”。要实现这样的排位,可以用下面的一些公式,假如上列数值在A2:A10区域中。

方法一:添加修正因素

按从大到小的顺序排位时,在B2单元格中输入公式,然后向下拖动填充柄填充公式:

=RANK(A2,$A$2:$A$10)+(COUNT($A$2:$A$10)+1-RANK($A2,$A$2:$A$10,0)-RANK($A2,$A$2:$A$10,1))/2

如果按照从小到大的顺序排位,则上述公式改为:

=RANK(A2,$A$2:$A$10,1)+(COUNT($A$2:$A$10)+1-RANK($A2,$A$2:$A$10,0)-RANK($A2,$A$2:$A$10,1))/2

其中公式的后面部分:

(COUNT($A$2:$A$10)+1-RANK($A2,$A$2:$A$10,0)-RANK($A2,$A$2:$A$10,1))/2

即为修正因素。无论是按从大到小的顺序排位还是按从小到大的顺序排位,都无需修改该修正因素。

方法二:数组公式

按从大到小的顺序排位时,在C2单元格中输入数组公式:

=SUM(--(A2<=$A$2:$A$10))-(SUM(--(A2=$A$2:$A$10))-1)/2

按从小到大的顺序排位时,则上述公式改为:

=SUM(--(A2>=$A$2:$A$10))-(SUM(--(A2=$A$2:$A$10))-1)/2

上述两个公式为数组公式,公式输入完毕后按Ctrl+Shift+Enter结束。

方法三:RANK.AVG函数

RANK.AVG函数是Excel 2010中新增的排位函数,用该函数可以对多个相同数值返回其平均排位。在Excel 2010中与早期版本中的RANK函数等价的是RANK.EQ函数,而RANK函数则被归入到兼容性函数类别,保留该函数是为了保持与Excel早期版本的兼容性。

RANK.AVG函数的语法与RANK函数类似,按从大到小的顺序(降序)排位时,在D2单元格中输入公式:

=RANK.AVG(A2,$A$2:$A$10,0)

其最后的参数“0”可省略。

按从小到大的顺序(升序)排位时,则上述公式改为:

=RANK.AVG(A2,$A$2:$A$10,1)

处理学生成绩Excel实例 问题:处理学生成绩Excel实例
回答:...,它的语法为: RANK(number,ref,order) 个中number为需要找到排位的数字。 Ref为包括一组数字的数组或引用。Ref中的非数值型参数将被忽略。 Order为一数字,指明排位的。 ·假
Excel表格中制作专业的数据分析、统计之——排位 问题:Excel表格中制作专业的数据分析、统计之——排位
回答:...Excel表格的基本操作,Excel“数据分析”中的“排位与百分比排位”可以使这个工作简化,直接输出报表。 注:本功能需要使用Excel扩展功能,如果您的Excel尚未安装数据分析,请依次选择“工具”-“加载宏&...
Excel 中函数Rank的用法 问题:Excel 中函数Rank的用法
回答:...为 “1” 公式中的"A1"就是参数NUMBE,它表示需要排位的数字。 "A1:A10" 为参数REF,可以是数组或也可以是单元格的引用,表示排名次的范围。 "0"就是参数Order,其值是可选的,即可以填也可以为空。 如果ord...
排位统计函数实例操作 25 问题:排位统计函数实例操作 25
回答:
为什么用公式“收入—支出+余额”计算的数值在 问题:为什么用公式“收入—支出+余额”计算的数值在Excel2010中没有显示
回答:你公式里D4和E4都是数值但是F3是文字,你这怎么可以算的出数值
电子表格单元格设置好公式,如何在不输入数值 问题:电子表格单元格设置好公式,如何在不输入数值时,如何设置才不显示VID/0!
回答:此错误叫做被0除!可以在你原来的公式上,加一层错误处理:=IFERROR(原公式,0)意思是:如果原公式正常就按原公式处理,出错就按0处理。
相关推荐: