奔牛网>office>Excel应用>>sumproduct用于多条件排名公式计算及多条件中国式排名计算
2017年08月10日

sumproduct用于多条件排名公式计算及多条件中国式排名计算

我们在EXCEL运用中经常需要根据多个条件对数据进行排名计算,这个时候就需要用到SUMPRODUCT

这里正好遇到一个需求:需要多条件,按同店名,同品类,对销售量进行排名。
情况如下图,这里就可以用在M2输入公式:=SUMPRODUCT(($A$2:$A$2001=A2)*($D$2:$D$2001=D2)*($K$2:$K$2001>K2))+1
标注红色的就是三个条件,第一个是同店名,第二个是同品类,第三个是销量大于本格
这样算出来的排名我们叫正常排名,就是排序是类似122456669这样的,重复的会跳过
如果需要中国式排名继续往下看




中国式排名就是类似12234456778这样的排序方式
在多条件情况下使用中国式排名需要用到辅助列,而且数据需要事先按多条件的顺序排列好,比如在这个案例中,我们事先必须先按店铺、品类、销量进行排序


然后构造辅助列,需要两个辅助列,第一个辅助列我这里命名为过程1,使用COUNTIF计算是否有重复销量存在,或者也可以计算M列有没有重复排名存在,一个道理,没有重复就写0,有重复就写重复的次数,在N2输入公式:=IF(COUNTIFS(A:A,A2,D:D,D2,M:M,M2)=1,0,COUNTIFS(A:A,A2,D:D,D2,M:M,M2))
然后还需要一列辅助用于判断是同店同品类,这里放在O,在O2,输入=A2&D2





然后P列用于显示中国式排名,在P2输入公式:=IF(O2<>O1,M2,IF(AND(O2=O1,N2<N1),M2-N1+1,M2))

下拉,最终结果就和上图一样了,目前只想到这个方法,如果您有更好的办法可以在奔牛网下面的评论栏留言,谢谢!
顶(0)
踩(0)
最新评论