您现在的位置:诗歌书籍 > 当代诗歌

Excel中实现中国式排名的两种方法(函数及数据透视表)

时间:2019-06-28 11:15   编辑:本站


Excel中实现中国式排名的两种方法(函数及数据透视表)

  我们都知道Excel中有一个排序的函数RANK,它可以求某一个数值在某一区域内的排名,其使用方法也非常简单,如要对某班的考试成绩进行排名,如下:I3单元格的函数为:=RANK(H3,$H$3:$H$21,0),使用起来十分方便。 小贴士:RANK函数的语法=RANK(排序数字,区域,排序方式)。 但是细心的朋友可能会发现,该排名不符合我们中国人的排名方式,当出现相同的成绩时,下一个人的排名对自动加上了相同人数数减一,如图中的名次3、6、16都是空缺的,但是在中国人的排名习惯中,无论出现几个第2名,下一个人依然是第3名,即并列的排名不占用名次。 对上述成绩表,中国式的排名结果应该如下所示:下面介绍实现以上排名方式的两种方法:一、函数公式法I3单元格的函数为:=SUMPRODUCT((H$3:H$21$H3)/COUNTIF(H$3:H$21,H$3:H$21))1,该公式为数组公式,需要按CtrlShiftEnter组合键结束。

公式解析:SUMPRODUCT是多条件求和函数,里面的参数“H$3:H$21$H3”返回的是一个数组在“H$3:H$21”区域内大于“$H3”数值的个数,后半部分“/COUNTIF(H$3:H$21,H$3:H$21)”可表达为“*1/COUNTIF(H$3:H$21,H$3:H$21)”,COUNTIF可以统计不重复值的个数,实现踢除重复值后的成绩排名。

另外,还可以使用FREQUENCY函数,则I3单元格的函数为:=SUM(--(FREQUENCY(H$3:H$21,IF(H$3:H$21=$H3,H$3:H$21))0))。

小贴士:FREQUENCY函数的含义是以一列垂直数组返回一组数据的频率分布,其语法为:=FREQUENCY(data_array,bins_array)Data_array是一组数值,然后根据Bins_array中对data_array中的数值进行分组的情况,统计频率。 “IF(H$3:H$21=$H3,H$3:H$21)”得到的结果为数组,其中大于等于H3的为原值,其余的显示为FALSE。 FREQUENCY函数统计出IF的结果在H$3:H$21中的分布频率,如果频率大于0,结果为TRUE,否则结果为FALSE。

“--”的作用是将文本型、逻辑型的数值转换位数字型数值,这个在函数中经常用到,大家稍微留心一下。 以上的公式都比较复杂,建议大家使用“公式”菜单下的“公式求值”功能查看一下各个步骤的结果,以加强理解。 二、数据透视表在“插入”菜单下选择“数据透视表”,如图:字段设置如下:然后在求和项2中右击,选择“值显示方式”为“降序排列”。 这样排名结果就出来了,可以将排名结果复制回原来的表格。

以上两种方式,相对来说数据透视表的方式要容易理解,推荐大家使用。 展开剩余内容。