作者:乔山办公网日期:
返回目录:excel表格制作
问题:数据中的0值不参与排名
首先RANK函数是没有办法实现了,如果数值都是正值还好,0值排名最后,如果是正值负值都有,那就很麻烦了,今天介绍两种简单的排名方法,其实是一种,可以用两个函数来实现。
这种排名方法,我们在学习Power Query排名的时候也用到过,就是:
计数+1法:统计比数值本身大的数值的个数然后加一
COUNTIFS
其实大部分排名问题都可以用这个方法来处理,说到计数,首先就是COUNTIFS,我们来看公式:
=IF(A2=0,"",COUNTIFS($A$2:$A$12,">"&A2,$A$2:$A$12,"<>0")+1)
用了两个条件:
- ">"&A2
- "<>0"
首先是大于10的数值列中没有,结果就是0,0+1=1,10的排名就是1。
然后在外侧嵌套一层IF,来排除0值。
SUMPRODUCT
SUMPRODUCT和SUM函数都能进行排名,如果用SUM就是数组公式,要使用CTRL+SHIFT+ENTER三键,用SUMPRODUCT就能省略三键。
=IF(A2=0,"",SUMPRODUCT(($A$2:$A$12<>0)*($A$2:$A$12>A2)*1)+1)
同样也是两个条件:
- ($A$2:$A$12<>0)
- ($A$2:$A$12>A2)
*号连接是表示两个条件同时成立,*1是把逻辑值转换成数值。
同样的写法也可以换成SUM,我们来测试一下:
不同的就是外侧的大括号,是通过三键来生成的
不管用那个函数来实现,这个排名的原理就是计数+1
更多函数知识,请订阅函数专栏: