函数介绍:
SUMPRODUCT(array1,array2,array3, ...)
参数说明:将数组(array)间对应的元素相乘,并返回乘积之和。(SUM是求和的意思,PRODUCT是相乘的意思,所以函数就是相乘之后再求和。学好英语很重要!)
公式=SUMPRODUCT(B2:B14,C2:C14)
实际上就是=B2*C2 B3*C3 ... B14*C14,分别用数量与单价相乘,然后再计和。
侠之大者,往往不拘小节。SUMPRODUCT也具有这条品格。
就算的数据中有“不祥”这种非计算类的数据,SUMPRODUCT也会自动将其视为0,继续执行其他数据计算(非常任性有木有?)
-----------------------------------------
下面介绍一下SUMPRODUCT面对数据条件统计时的基础技能
1.条件求和基本公式
初级函数:
SUMIF(条件查找区域,条件,求和区域)
SUMIFS(求和区域,条件查找区域1,条件1,条件查找区域2,条件2...)
高级函数:
SUMPRODUCT((条件查找区域1=条件1)*(条件查找区域2=条件2)*...*(条件查找区域n=条件n)*(求和区域))
2.条件计数基本公式
初级函数:
COUNTIF(计数区域,计数条件)
COUNTIFS(计数区域1,条件1,计数区域2,条件2...)
高级函数:
SUMPRODUCT((计数区域1=条件1)*(计数区域2=条件2)*...*(计数区域n=条件n))
-----------------------------------------
有了基础技能,我们来看看高级函数的风采:
1.统计不重复项个数
公式=SUMPRODUCT((1/COUNTIF(A2:A14,A2:A14))*1)
公式含义是:COUNTIF依次返回一组数值,分别是A2、A3...A14各自的个数,即{5,5,2,2,5,4,4,1,4,5,4,1},被1除后,变为{ 1/5,1/5,1/2,1/5,1/2,1/5,1/4,1/4,1,1/4,1/5,1/4,1}再求和后得出不重复项5。
2.联合多列判断
公式=SUMPRODUCT((B2:B14>C2:C14)*1)
公式含义是:分别比较B2与C2,B3与C3...B14与C14之间的大小,如果大于,就返回1,如果不大于就返回0,最后计和。
3.隔列求和
公式含义是:COLUMN(B2:G2)返回{2,3,4,5,6,7},加1后变为{3,4,5,6,7,8}。用MOD函数除以3取余数后得到{0,1,2,0,1,2},等于0的只有第1列和第4列,对应B列和E列,即1月和4月。
-----------------------------------------
以上这三种情况,初级函数是无法做到的,感觉到高级函数的威力了吧?
但是也要说明一下,SUMPRODUCT由于是数组计算函数,所以计算效率上没有普通函数高(技能冷却时间较长),所以一般的小问题,不建议使用高级函数(总不能天天用大炮打蚊子呀)。
有了今天的高级函数装备,小伙伴们是不是距离EXCEL高手又进了一步呢?相信在表哥的陪伴下,小伙伴们变成EXCEL高手的那一天一定会更快到来的!
财会干货 l 税法资讯 l 办公教案 l 职业发展
98%白领都关注的公众号
领导说了!
你点一下下面框框的内容
小编工资就涨5毛
联系客服