Excel中经常需要使用到自动求和公式,自动求和具体该如何操作呢?接下来小编举例简单的例子告诉大家excel设置自动求和公式的方法。excel设置自动求和公式的方法设置自动求和公式步骤1:将鼠标放在要求和数据区域的紧挨着的下面一个单元格,如下图的D10单元格,然后点击【开始】-【自动求和】按钮就可以自动求D......
说说Excel中的函数排名
1.什么是排名?
排名不等同于排序(虽然通过排序可以得出排名),排名指的是按照一定的方式确定一组数据的名次,比如将成绩进行排名,得出的结果是第1名、第二名……
排名和排序的概念,很多人分不清楚,容易导致不在一个频道上,所以希望大家首先搞明白两者的区别。
2.如何进行排名?
普通排名
1.1 通过排序进行排名
这个很好理解,想要获得一组数据的排名,可以首先将数据进行排序,然后标上序号即可。
这种排名属于比较低级的方法,有两个问题它无法跨越:
①如果数据有重复,无法识别重复
②如果数据有更新,无法动态排名
因此有一个高级的玩法——使用Rank函数进行排名。
1.2 通过Rank函数排名
rank函数是排名函数,最常用的是求某一个数值在某一区域内的排名。
rank函数语法形式:rank(number,ref,[order])
number :需要求排名的那个数值;
ref :排名的参照数值区域;
order:为0或1,0的情况默认不用输入,得到的就是从大到小的排名,对应的输入1是逆序排名。
同样是上面的案例,我们来使用Rank函数来进行排名。
让我们定格最终的排名效果,发现有两个第五名,但是没有第六名。
这是因为排名的数据中,有两个数据重复了,在Rank的世界里,重复的排名也会占用一个名次,因此没有第六名了,直接就调到了第七名,这就是普通是排名。
中国式排名
如下图的样子这就是中国式排名:并列第五,然后会出现第六名。
这种情况,使用用rank函数就无法实现,需要用到COUNTIF和SUMPRODUCT函数嵌套。
在C2单元格输入函数,=SUMPRODUCT((B2<=B$2:B$9)/COUNTIF(B$2:B$9,B$2:B$9)),然后用力将行数向下复制。
一大坨函数,肯定看不懂是什么鬼东西吧!
函数作用分析:
=SUMPRODUCT((B2<=B$2:B$9)/COUNTIF(B$2:B$9,B$2:B$9))
这个公式是两个常用函数的嵌套。
1. 函数关键部分是 COUNTIF(B$2:B$9,B$2:B$9)
COUNTIF函数的语法规则如下:
countif(range,criteria)
参数:range 要计算其中非空单元格数目的区域
参数:criteria 以数字、表达式或文本形式定义的条件
说白了就是条件计数,在区域rang中求满足Criteria条件的单元格的个数。
大家常用的情况,比较简单,一般是这样的:
上述例子,在D2单元格写入公式=COUNTIF(B2:B9,B2)
意思就是说,求在B2:B9这个区域中,等于B2单元格数值的单元格个数是几?
显然,在这个区域中,等于88的单元格只有B2自身,因此结果为1.
而如果Criteria参数是一个数组区域,那么将返回一个数组结果。
即:COUNTIF(B$2:B$9,B$2:B$9)的含义,(下面是重点)
就是分别以第2参数B$2:B$9区域中的8个单元格为条件,每次查找第1参数B$2:B$9区域中=B$2 或B$3 或B$4…… 或B$9的元素数……
返回一个数组结果是:{1,1,1,1,2,1,1,2}
即,区域中每个元素的重复次数
↑请把这句话读三遍↑
2,解读1/COUNTIF(B$2:B$9,B$2:B$9)
将函数求得的数组结果,作为分母,被1除,会出现什么结果呢?
例如:COUNTIF(B$2:B$9,B$2:B$9)= {1,1,1,1,2,1,1,2}时,
1/COUNTIF(C$3:C$9,C$3:C$9) 计算就是 = {1,1,1,1,0.5,1,1,0.5}
这一步是小学数学水平,应该不难。
3,SUMPRODUCT函数
这个函数被誉为计算全能王,有限的篇幅里,我只能讲最核心的知识。
直接上结论,大家记住结论即可,以后有机会详细讲解。
SUMPRODUCT函数的万能公式为:
=SUMPRODUCT((条件1)*(条件2)*……*求和区域)
可以实现单一条件求和、多条件求和。
因此,在这个案例中,SUMPRODUCT函数括号内的这一坨,最终实现的功能就是按照某一个条件求和。
①先来说求和
将1/COUNTIF(C$3:C$9,C$3:C$9) 得出的结果 {1,1,1,1,0.5,1,1,0.5}进行求和,你就会惊奇地发现:
总和=【区域中不重复元素的个数!】
其实原理很简单:比如案例中89重复了两次,那么得出的数组中,两个89分别对应的位置都是0.5,两个0.5相加等于1,相当于只被计算了一次。
以此推广,如果某个数据重复了N次,那么它对应的COUNTIF()结果=n,而其1/COUNTIF()结果=1/n,因为一共有n个元素(因为重复了N次),因此它们的个数总和=n*(1/n)=1
…………
所以,=SUMPRODUCT((1/COUNTIF(B$2:B$9,B$2:B$9))啰嗦了这么久,其实就是计算B$2:B$9区域中不重复元素的个数。
这一步相当于中学数学知识,对大家来说也应该没有问题。
②附加条件的求和
因为要进行从大到小的顺序排名,因此我们需要统计大于等于这个数的个数。
想一想,为什么?
(比如,对于排名第一的数,大于等于它的只有它自己,排名第二的数,大于等于它的只有第一和它自己……所以,想要求一个数在一组数中的排名,计算出这组数中大于等于这个数的个数即可)
所以要加上一个附加条件:(B2<=B$2:B$9),而根据SUMPRODUCT函数的万能公式,这个条件需要与求和区域进行相乘。
因此,最后的合成公式就是:
=SUMPRODUCT((B2<=B$2:B$9)/COUNTIF(B$2:B$9,B$2:B$9))
最终公式的含义是:以 符合(B2<=B$2:B$9)为条件,统计区域中不重复元素的个数最后就得到了【中国式排名】的结果。
标签: RANK函数rank函数怎么用rank函数的使用方法rank函数的用法rank排名函数excel函数
相关文章