想从这个函数基础学起的看上一篇文章。 SUMPRODUCT是EXCEL里的一个高阶函数,作用非常强大,想从EXCEL小白到大牛,这个函数必须掌握。 5、多条件计数 公式套路: (1)=SUMPRODUCT((区域1=条件1)*(区域2=条件2)*......*(区域N=条件N)) (2)=SUMPRODUCT((区域1=条件1)*1,(区域2=条件2)*1,…,(区域N=条件N)*1) (1)或(2)两种形式都可以。 现有一公司2016年各销售员每次的销售额,需计算销售员杨过销售额大于10万的次数。 6、多列联动条件判断计数 现有一公司2016年各销售员实际销售额与计算销售额,需计算实际销售额小于计划销售额的次数,即没有完成任务的次数。 公式=SUMPRODUCT((E2:E16<> 7、多条件求和 公式套路: (1)=SUMPRODUCT((区域1=条件1)*(区域2=条件2)*......*(区域N=条件N)*汇总区域) (2)=SUMPRODUCT((区域1=条件1)*(区域2=条件2)*…*(区域N=条件N),汇总区域) (1)或(2)两种形式都可以。 现有一公司2016年各销售员每次的销售额,需计算销售员杨过在河南地区的总销售额。 公式=SUMPRODUCT((B2:B16='河南')*(D2:D16='杨过')*E2:E16) 8、隔列求和 现有一公司2016年各部门各月借贷额度,需计算2016年各月借贷总额。 借方公式:=SUMPRODUCT((MOD(COLUMN($B$2:$I$2),2)=0)*B3:I3) 贷方公式:=SUMPRODUCT((MOD(COLUMN($B$2:$I$2),2)=1)*B3:I3) 9、统计不重复项个数 剔除重复值再数据处理中非常常见,COUNTIF通过数组运算得到一个数组结果,即区域中每个单元格在整列中所出现的次数,将这个数组求其倒数,然后求和就可以得到唯一值的总个数。 公式=SUMPRODUCT(1/COUNTIF($E$2:$E$16,$E$2:$E$16)) 10、中国式排名 可能有Excel基础的都知道排序用RANK函数就能搞定,可是在使用RANK函数进行排名时,出现相同名次,其后的排名数字会自动向后移位。 在我们的生活中还存在着另一种排名方式,它的特点是相同名次不影响后续的排名名次,无论有几个第一名存在,后面的名次始终还是第二名。 =SUMPRODUCT(($E$2:$E$16>=E2)*(1/COUNTIF($E$2:$E$16,$E$2:$E$16))) |
|